Hello, this is Mark from Tradinformed.com And welcome to this video on How to Improve
Your Trading Strategies using a Monte Carlo Simulator
OK, what I'm going to show is how you can create, for yourself, using Microsoft Excel,
a type of Monte Carlo simulator that will rearrange and randomise the results
of a backtest, with the intention of checking the robustness of your maximum drawdown
OK, so all you need to do to start this is an actual backtest that has been carried out,
and we have the results of this shown in this column AD
So I'll start a brand new page here, the only thing I've included on here is the number
I'm going to assign each of our trades a number And I'll put in heading here, Original Trades
Original Equity, and our Original Maximum Drawdown
So for our original trades, we are just going to link to this spreadsheet, so we're going
to put in = I'm going to link to this column, this cell
here Press enter, I'm going to remove these $ signs
so what we can do is just double-click on the bottom right hand corner of the cell,
click on calculate and Excel will include the cells here in this column
I don't need this spreadsheet so I'm going to close it
OK, so our original equity we are going to start off with $1000 and when we get trades,
positive and negative trades, we're going to add to this
I'm going to copy this down again by double-clicking on the bottom right hand corner of the cell
Next in this column I'm going to calculate our maximum drawdown for the original backtest
I'm going to do this by the formula = this cell/max which for Excel stands for the highest
Correct this to C2:C2 and I'm going to make the 1st one of these fixed using F4
I'm going to put all of this in brackets and I'll take 1-
And then I'm going to double-click and copy it down to all the cells below
OK, so the next element I'm going to introduce in the spreadsheet is the 1st part of the
Monte Carlo Simulator And we going to need an element of randomness
So we're going to use the Excel formula RAND() And this automatically generates a random
number between 0-1 with 15 digits after the decimal point
I can copy this down again, like that and in the next cell I'm going to use another
Excel formula called RANK Click on here, do a , and highlight the entire
range of random numbers Close the formula and go back into it a press
F4 to make this fixed And press enter
So now if I click on the calculate button, we have a new order for our trades
OK, so now the next thing we need to have in here is our New Trades
New Equity and our New Maximum Drawdown To calculate our new trades, or the position
of our new trades using our random rank function, I'm going to use another Excel function called
VLOOKUP I'm going to ask it to look up this value
here, I'm going to ask it to look it up in this array here.
which is columns A and B I'm going to ask it to check in the 2nd of
these columns Before I copy this down I want to use F4 again
to make this a fixed array Now I can click on calculate and Excel has
automatically moved our new trades in the relative position of the new randomised order
OK, so our new equity we're going to calculate in the same way as our original equity
So I'm going to press Control C to move that over here and double-click
Our new maximum drawdown, again I'm going to cheat and use this function here
Double-click to copy the formulas down to all the cells below
OK, so here what I'm going to do is I'm going to calculate our profit, our profit is going
to stay the same in both the original and the new version
And this is because we're keeping all the same trades, this particularly Monte Carlo
simulator is only randomising and rearranging the order
So, we're going to do this by the formula SUM, this range here
Enter, then we're going to calculate our original maximum drawdown here
Using the MAX function again and use this range
And we're going to have our new maximum drawdown =MAX and it will be this range
And once I press calculate we will get a new value here
And every time I press calculate or in fact enter something new, if I go onto Formulas
and click calculate We'll get a new value for our maximum drawdown
We can show this visually quite easily as well
By going down here, pressing Insert, and then we have a line graph
Showing our original equity chart If I just copy this, paste it here, click
in it, and drag it over to our new drawdown And we have a visual representation of our
new drawdown And again if I go onto Formulas, Calculate
We can see that the graph of our drawdown changes
1 thing you want to do when you're using a Monte Carlo simulator is test multiples of
different scenarios So, we could easily do this manually, we could
just click on this But, what we would prefer to do is run a number
of scenarios automatically So what I've done to speed things up a bit
in this spreadsheet is use a recorded macro And I'm going to go on to Macros, and click
run And what it is going to do on the screen now
is do 20 versions and we can see it updating there and updating on the right hand side
in real time The different equity curves for the different
scenarios So while, my computer is chugging through
these different scenarios, I can say that if you are interested in using Excel to backtest
trading strategies, I do have an eBook course available in the Amazon Kindle Bookstore and
there is a link on the screen if you would like more information about this course
So there, I have used a Macro, I'm not going to go through it in this spreadsheet but it
is very easy to record a Macro doing a simple task like this going through and recording
the data each scenario So what I do want to do below this is work
out a few metrics based on what the data is telling us
First of all the average We can see that out of these 20 scenarios
our average drawdown is 5.33% Which compares to our original maximum drawdown
which was 3.75% So we can see straight away that our original
strategy may have been, just through chance, may have been suggesting that this strategy
has a lower drawdown that it would do in the future
We can use another formula, say the MAX function and this shows us that out of these 20 scenarios
the maximum, or the highest drawdown that was experienced in all of them was 8.76%
which is more than double our original drawdown, which again could be signficant
you can do whatever metrics you like to ensure to your own satisfaction that you have a robust
trading strategy you may want to look at a 85 or 85 percentile
you may want to look at the standard deviation you may of course want to do a lot more than
20 runs and you are welcome to do that so I hope you found this spreadsheet helpful
and I hope you found this video a useful guide in how to create your own Monte Carlo simulator
and if you would like more information about using Excel to backtest trading strategies
and about the financial markets in general please go to www.Tradinformed.com