It tells you that in 95% of the scenarios your results will be between x1 and x2 $. I have been using a similar toy in excel. It will not do exactly what you want but you can take some ideas from it. You can change the code for example a little to include 100 equiprobable trades instead of 20 trades with corresponding probabilities. Anyway the code is this (I could attach the spreadsheet but some people may feel uneasy to download a spreadsheet with code in it, though they could disable macros for the first time and inspect the code then enable macros again.) Code: '*********************************** '* Sort the numbers * '*********************************** Sub Sort(n As Variant, arr() As Double) Dim Temp As Double Dim i As Long Dim j As Long For j = 2 To n Temp = arr(j) For i = j - 1 To 1 Step -1 If (arr(i) <= Temp) Then GoTo 10 arr(i + 1) = arr(i) Next i i = 0 10 arr(i + 1) = Temp Next j End Sub '********************************************* '* Construct Historgram Distribution * '********************************************* Sub Hist(n As Variant, M As Long, start As Double, Right As Double, arr() As Double) Dim i As Long, j As Long, Find As Long Dim Length As Double ReDim breaks(M) As Single ReDim freq(M) As Single For i = 1 To M freq(i) = 0 Next i Length = (Right - start) / M For i = 1 To M breaks(i) = start + Length * i Next i For i = 1 To n If (arr(i) <= breaks(1)) Then freq(1) = freq(1) + 1 If (arr(i) >= breaks(M - 1)) Then freq(M) = freq(M) + 1 For j = 2 To M - 1 If (arr(i) > breaks(j - 1) And arr(i) <= breaks(j)) Then freq(j) = freq(j) + 1 Next j Next i For i = 1 To M Cells(i + 1, 9) = breaks(i) Cells(i + 1, 10) = freq(i) Next i End Sub '******************************************************* ' Simulate iteration trials with k trades each * '******************************************************* Sub MonteCarlo() Dim Iteration As Long Iteration = Cells(23, 4) 'number of trials ReDim TP(Iteration) As Double 'bankroll at the end of each trial Dim k As Long k = Cells(24, 4) 'number of trades at each trial initbankroll = Cells(25, 4) 'starting bankroll sbankroll = 0 'average bankroll at the end of each trial lnsbankrol = 0 'average ln bankroll at the end of each trial Dim pay(20) 'trade payoff Dim prob(20) 'payoff's probability of occurence Dim cprob(20) 'cummulative probability of payoffs cprob(0) = 0 SUMmaxdrawdown = 0 For ct1 = 1 To 20 pay(ct1) = Cells(ct1 + 2, 3) prob(ct1) = Cells(ct1 + 2, 4) cprob(ct1) = cprob(ct1 - 1) + prob(ct1) Next ct1 'check if probability sum is 100% If cprob(20) < 0.999 Or cprob(20) > 1.001 Then MsgBox "Payoff Probabilities must sum 100%" Exit Sub End If 'kelly f .1% precission For ct2 = 0.001 To 1 Step 0.001 ss = 0 For ct1 = 1 To 20 ss = ss + prob(ct1) * Log(1 + ct2 * pay(ct1)) Next ct1 If ss > maxss Then ff = ct2 maxss = ss End If Next ct2 'growth rates For ct2 = 1 To 20 nflag = 0 ss = 0 For ct1 = 1 To 20 If ct2 * ff / 10 * pay(ct1) > -1 Then ss = ss + prob(ct1) * Log(1 + ct2 * ff / 10 * pay(ct1)) Else nflag = 1 End If Next ct1 Cells(ct2 + 2, 17) = ff / 10 * ct2 If nflag = 0 Then Cells(ct2 + 2, 18) = ss Else Cells(ct2 + 2, 18) = "" End If Next ct2 ' expectancy and variance For ct1 = 1 To 20 s1 = s1 + prob(ct1) * pay(ct1) s2 = s2 + prob(ct1) * (pay(ct1)) ^ 2 Next ct1 If Cells(26, 4) = "" Then f = ff Else f = Cells(26, 4) 'betting fraction End If variance = s2 - s1 ^ 2 'trials loop For ct1 = 1 To Iteration bankroll = initbankroll 'bankroll at the begining of each trial maxeq = initbankroll 'maxDD statistics mineq = initbankroll maxdrawdown = 0 ' trades loop For ct2 = 1 To k randnum = Rnd trade = 1 'witch trade payoff will be used start: If randnum <= cprob(trade) Then bankroll = bankroll + f * bankroll * pay(trade) GoTo estart: End If trade = trade + 1 GoTo start: estart: If bankroll > maxeq Then drawdown = (maxeq - mineq) / maxeq If drawdown > maxdrawdown Then maxdrawdown = drawdown maxeq = bankroll mineq = bankroll End If If bankroll < mineq Then mineq = bankroll End If Next ct2 drawdown = (maxeq - mineq) / maxeq If drawdown > maxdrawdown Then maxdrawdown = drawdown SUMmaxdrawdown = SUMmaxdrawdown + maxdrawdown sbankroll = sbankroll + bankroll lnsbankroll = lnsbankroll + Log(bankroll / initbankroll) TP(ct1) = bankroll Next ct1 Call Sort(Iteration, TP) Call Hist(Iteration, 40, TP(1), TP(Iteration), TP) For i = 1 To 20 Cells(i + 3, 6) = 1 - (0.05 * i) Cells(i + 3, 7) = TP(Int(Iteration / 20 * i)) Next i Cells(3, 6) = "Close to 100%" Cells(13, 6) = "50%" Cells(23, 6) = "Close to 0%" Cells(3, 7) = TP(1) Cells(24, 7) = lnsbankroll / Iteration / k 'Growth Rate Cells(25, 7) = sbankroll / Iteration 'average bankroll Cells(26, 7) = s1 'expectancy Cells(27, 7) = variance ' variance Cells(28, 7) = ff 'kelly f Cells(29, 7) = SUMmaxdrawdown / Iteration End Sub
I posted an example spreadsheet in another thread that you might find useful: http://www.elitetrader.com/vb/showthread.php?s=&postid=1246313#post1246313 I generated the replications using Excel's Data Table functionality. (It's a very cool and powerful feature useful in lots of situations and you might want to look up the help file if you're not familiar with it)
one last question, so without having Tradestation or whatever software spit out 10,000 equity curves of randomly generated trades, can i make it spit out 1 equity curve of randomly generated trades and re-sample those trades 10,000 times? would that be the same thing as having Tradestation spit out the 10,000 by itself? shane