Hi all,
I'm a beginning trader and I'm currently working on developping a system that I want to trade. This thread, however, is not about system development. I found a spreadsheet with a MonteCarlo simulator a while back (I've actually seen it in a thread on this forum too). I have changed the original file quite a bit by now and I'd like to share it with you and start a thread to discuss it. Keep in mind that the file was made to tailor my needs... so people thinking about different systems might dislike some of the choices.
Originally, it was a simulation of a binary outcome. That is, fixed SL and TP. So the Loss was fixed and the Profit was fixed. This diden't really fit my trading style so I wanted to change this. The left hand side of the spreadsheet is was I managed to create so far but it has some major flaws. (The whole spreadsheet and the assumptions have flaws but I do think it can be of some use)
So right now, it requires some input (orange). They are very straightforward numbers you need to put in. It assumes a % of equity to risk per trade. True winrate and True R/R next are also straightforward I believe. Ofcourse, in reality, these values can never be known for certainty. However, we assume a historical backtest or results of paper trading can provide a measure of the 'True' values. I found an article a while back with something on Sample Size and Margin of Error. (http://www.traderslog.com/backtesttradingsystem/) I'm not an expert in statistics or anything and I am not too sure there is ever any certainty about these parameters (they can obviously also change over time) . Anyway, we have to assume. St Dev Trades will be discussed later.
The Grey box with Orange letters displays information about the generated sample based on the input variables. (Feel free to check the formula's .. I am not 100% sure everything is correct but if you find an error please let me know ! ) The sample is 500 trades. I randomly generate a Win or a Loss. When its L you loose 1R (= risk per trade * equity). Things are a bit different for wins. Remember, originally the spreadsheet was binary so I randomly generated an R multiple distribution. That's why we needed the input St Dev Trades. Anyway, it displays the cumulative equity chart above.
Clearly, there is some things wrong here. The loss is always 1R. The wins are randomly distributed which also seems flawed. One thing I could do here probably is let my randomly generated Rmultiple distribution decide on the W/L character of the trade and limit the distribution to 1R.
The right hand side of the spreadsheet tries to do the same but assumes a different distribution. Basically, I like to place a SL and let my profits run. This means that a normal distribution will never get those big wins that will occasionally happen. (So far, the normal distribution always gave 1R and never gave big R gains so both things probably understate the actual path of the system. The Rmultiple distribution on the right is based on an Fdistribution. This means we need 2 parameters (degree of freedom 1 and degree of freedom 2). Ideally they should perfectly represent the true Rmultiple distribution of my system. Unfortunatly, I think we will never get anything better than an approximation.
Df2 is actually the mean of the Fdistribution i think so its value is based on the 'expectancy' of my system. Df1 is something i chose randomly. I did look for a way to determine both parameters more accuratly. I was thinking in the direction of: Lossrate (%) of values under the cumulative distribution should be situated below 1. Or 10% of the values should be higher then an F value of X. Any thoughts on this?
So for now it's an Rmultiple distribution based on (expenctancy +1) as the mean and minus one in column 'O' for the actual values. 1R is the maximum loss because of my SL. The distribution occasionally gives very high numbers so I also tried to smooth this out a bit.
If you hit F9 a bunch of times you will see that the hitrate resembles the true hitrate quite well. this is clear because the expectancy of my system is the mean of the F disttribution minus 1. The problem lies with the R/R. It's obviously too high for the true value. This is unsurprising as i limited the actual distribution to values only higher than (1) .
So there is still some things here I would like to resolve and I'm hoping on some help in doing it. The spreadsheet basically allows me to grasp the expected performance of a system. In addition, it allows for selecting an appropriate % risked per trade given a some personal constraints of your choosing.
Finally, there is a macro included in the spreadsheet to run the left hand side a thousand times and copy the characteristics of the system (grey and orange text) over to a new tab. You can use it to plot histograms of the specific variables. The only thing it doesn't copy over is the actual Equity Curve. My coding is not advanced enough for this unfortunatly.
Any thoughts, suggestions, doubts, errors or remarks are welcome I hope somebody finds this useful.
(The macroenabled workbook is not uploadable, so this will not be available unfortunatly)
I'm a beginning trader and I'm currently working on developping a system that I want to trade. This thread, however, is not about system development. I found a spreadsheet with a MonteCarlo simulator a while back (I've actually seen it in a thread on this forum too). I have changed the original file quite a bit by now and I'd like to share it with you and start a thread to discuss it. Keep in mind that the file was made to tailor my needs... so people thinking about different systems might dislike some of the choices.
Originally, it was a simulation of a binary outcome. That is, fixed SL and TP. So the Loss was fixed and the Profit was fixed. This diden't really fit my trading style so I wanted to change this. The left hand side of the spreadsheet is was I managed to create so far but it has some major flaws. (The whole spreadsheet and the assumptions have flaws but I do think it can be of some use)
So right now, it requires some input (orange). They are very straightforward numbers you need to put in. It assumes a % of equity to risk per trade. True winrate and True R/R next are also straightforward I believe. Ofcourse, in reality, these values can never be known for certainty. However, we assume a historical backtest or results of paper trading can provide a measure of the 'True' values. I found an article a while back with something on Sample Size and Margin of Error. (http://www.traderslog.com/backtesttradingsystem/) I'm not an expert in statistics or anything and I am not too sure there is ever any certainty about these parameters (they can obviously also change over time) . Anyway, we have to assume. St Dev Trades will be discussed later.
The Grey box with Orange letters displays information about the generated sample based on the input variables. (Feel free to check the formula's .. I am not 100% sure everything is correct but if you find an error please let me know ! ) The sample is 500 trades. I randomly generate a Win or a Loss. When its L you loose 1R (= risk per trade * equity). Things are a bit different for wins. Remember, originally the spreadsheet was binary so I randomly generated an R multiple distribution. That's why we needed the input St Dev Trades. Anyway, it displays the cumulative equity chart above.
Clearly, there is some things wrong here. The loss is always 1R. The wins are randomly distributed which also seems flawed. One thing I could do here probably is let my randomly generated Rmultiple distribution decide on the W/L character of the trade and limit the distribution to 1R.
The right hand side of the spreadsheet tries to do the same but assumes a different distribution. Basically, I like to place a SL and let my profits run. This means that a normal distribution will never get those big wins that will occasionally happen. (So far, the normal distribution always gave 1R and never gave big R gains so both things probably understate the actual path of the system. The Rmultiple distribution on the right is based on an Fdistribution. This means we need 2 parameters (degree of freedom 1 and degree of freedom 2). Ideally they should perfectly represent the true Rmultiple distribution of my system. Unfortunatly, I think we will never get anything better than an approximation.
Df2 is actually the mean of the Fdistribution i think so its value is based on the 'expectancy' of my system. Df1 is something i chose randomly. I did look for a way to determine both parameters more accuratly. I was thinking in the direction of: Lossrate (%) of values under the cumulative distribution should be situated below 1. Or 10% of the values should be higher then an F value of X. Any thoughts on this?
So for now it's an Rmultiple distribution based on (expenctancy +1) as the mean and minus one in column 'O' for the actual values. 1R is the maximum loss because of my SL. The distribution occasionally gives very high numbers so I also tried to smooth this out a bit.
If you hit F9 a bunch of times you will see that the hitrate resembles the true hitrate quite well. this is clear because the expectancy of my system is the mean of the F disttribution minus 1. The problem lies with the R/R. It's obviously too high for the true value. This is unsurprising as i limited the actual distribution to values only higher than (1) .
So there is still some things here I would like to resolve and I'm hoping on some help in doing it. The spreadsheet basically allows me to grasp the expected performance of a system. In addition, it allows for selecting an appropriate % risked per trade given a some personal constraints of your choosing.
Finally, there is a macro included in the spreadsheet to run the left hand side a thousand times and copy the characteristics of the system (grey and orange text) over to a new tab. You can use it to plot histograms of the specific variables. The only thing it doesn't copy over is the actual Equity Curve. My coding is not advanced enough for this unfortunatly.
Any thoughts, suggestions, doubts, errors or remarks are welcome I hope somebody finds this useful.
(The macroenabled workbook is not uploadable, so this will not be available unfortunatly)
Attached File(s)
MonteCarlo Simulation Trading System VERSION 2.xlsx
220 KB

2,015 downloads