I'm wondering if anyone knows how to use excel to generate multiple runs of random trades given a specific time series? can it even be done using excel? Are there special programs that are designed to do this? i know that wealth-lab has a Monte-Carlo lab add in that is able to do exactly this, does anyone know of any other stand-alone software packages that are able to do this? thanks, shanoballs
http://office.microsoft.com/en-us/excel/HA011118931033.aspx?pid=CL100570551033 http://office.microsoft.com/en-us/marketplace/EM011966861033.aspx These might help.
Monte Carlo simulations in Excel are straightforward: - use the RAND() function to generate randomness - use the Data Table functionality to generate multiple runs for any statistic that is calculated over random data
guys i really appreciate your comments, however i think i didn't communicate what I'm trying to do properly. i already know how to generate a random equity curve using a monte carlo function in excel. here is what the curves look like in excel, these are not using any actual price data, the curves are generated using a random number generated by the monte carlo function and are based on a specified win% and W:Loss ratio. <img src="http://img166.imageshack.us/img166/6494/curveszc2.jpg"<br> what i need to do is input actual price data like daily OHLC data for the ER2 and then have excel pull up random trades to generate a large number of equity curves, like 10,000 of them so i can generate a distribution curve for them. but i recently noticed the limitations in excel and i don't think this can be done using it. i have not found any software packages that allows this type of thing to be done so now I'm thinking about learning C# to make my own program to do this. i use Multicharts which uses easylanguage code, but to generate 10,000 random equity curves it would take a VERY long time and this would require that i do it manually (saving each run, etc). anyway, thanks for your comments, if there is anyone that have done this type of thing before, please throw me a bone or 2, again, any help is greatly appreciated. shane
One possible approach is resampling (bootstrapping): - Suppose you have 100 trades, each with return R. - You draw a random number N between 1 and 100 and take the Nth return. Repeat this 100x. - You now have a new set of trades drawn from the distribution of your original set. If you plot this, this would be one of the curves on your graph. - You now can repeat this as many times as you want to generate distinct plots. - For each curve you can calculate a summary statistic (e.g. expectancy, profit factor, max drawdown etc.) - Suppose you do 999 replications (curves). If you order the summary statistic of interest from hi-low and take #50 and #950, you obtain a 95% confidence interval All this is straightforward in Excel. If you want to do more advanced stuff, doing this in a statistical language (e.g. R) is way easier than C, unless you need to do it in realtime.
so like rearranging the trades that were generated by the actual strategy 100 or however many times huh? did i understand that correctly? if so, can you explain how the results would be interpreted? for example, after i order the summary statistic of interest from hi-low what will the 95% confidence interval tell me? I'm still learning statistics so forgive me if this is a stupid question, and thank you for your response. shane
no, it's not just rearranging (in that case statistics like the profit factor or expectancy wouldn't change with each new run, also the endpoints of all your curves would be equal). Remember, if you draw 100 random numbers between 1 and 100, it's likely that some are equal. So, each run (curve in the plot) will include some trades 2, 3,... etc. times and will simply leave others out. In theory, if you'd repeat this an infinite number of times, your highest curve would consist of 100 times your most profitable trade and your lowest curve would consist of 100 times your least profitable trade.
ok, let me try to explain the confidence interval very briefly. Suppose you calculate a statistic (e.g. expectancy) over all your trades up to today, and find it's 3.4753% Now you make one additional trade tomorrow. Guess what? Including the new trade it changed to 3.4780%. So, your statistic shows variability. The confidence interval is a way to measure/express variability: a narrower interval means less variability. Some people interpret the 95% confidence as something like "we can be 95% certain that the true value lies within the confidence interval". I think for trading the confidence interval is most useful however when comparing different strategies/systems. So when comparing alternative strategies you would look for those that have a high score of the statistic of interest and have a narrow confidence interval. The 95 % is arbitrary by the way, e.g. you can also take the 99% or 90% interval.
oooh, ok i understand, so i need to assign a number to the trades that were origionally generated, then have excel pull a number between the total number of trades, for example, in this sheet i have 93 trades that were generated by the strategy in the order listed at the left, so on the right, i have excel randomly pulling up a number between 1 and 93, so there will be duplicates and some trades will be missing, i understand what u mean, so now i just need a way to get the return to match with the number that is pulled right? any idea how i can go about doing this? would i have to use a macro? <br> <img src="http://img178.imageshack.us/img178/5750/tradelist2yd9.jpg" <br> thanks!