Hello this is Mark from Tradinfromed.com and welcome to this video on: a
Simple Way to Use Excel to Set up a Monte Carlo Test. In this video I'll be
using the example of a trading strategy to show you how we can set up a Monte
Carlo Simulation using Excel formulas and a VBA macro. Please remember if you
do like this video to hit the like button and also subscribe to this
channel for more videos like this. Now before we can do a Monte Carlo
simulation we need to have some background data and what I'm going to
use is the trades which is outputted from a back test that I've done of a
trading strategy and if I scroll across here you can see that I'm actually using
a back test spreadsheet this is a Tradinformed Backtest Model that can be
used to test all sorts of different trading strategies, different indicators
and different markets and timeframes and if you want more information about
Tradinformed Backtest Models there is a link on the screen at the moment OK so
as I was saying I'm using this column, this is our list of trades, and I'm going
to reorder them. The purpose for this is to work out the worst case scenario and
I'm going to be looking at the maximum drawdown as a proxy for our worst case
scenario now to begin with what we're going to do is I am going to order
the original trades using a number now I've just copied this number down to all
the rows below and then I am going to introduce an
Excel function generates a random number and again copy this down now Monte Carlo
methods are built on randomization and so this is how we're going to use
randomization in this example and the next thing that I want to do is to
translate this random number into a version of these numbers here and I'm
going to do that using the rank formula and what this is doing is basically
ranking each of these numbers, random numbers in the order that they appear in
the whole sequence. I'm going to use the dollar symbols to make this a fixed
range okay I'm going to double click this down and now what we have here is a
randomized trade order which is exactly the same as this number, these numbers
here but in a different order so it's nice to have these numbers in a
different order but what we want to do is to have the trades the trades over
here from column CA and the way I'm going to do that is to use my favorite
compound function in Excel which is Index Match whenever we're using index
match we always at the very start we put the range that we want to output in this
case this is the range of trades
then I'm gonna put a comma in here and I'm gonna put the match part of the
function and what I want to match is this new random trade order that we've
generated with the original trade order
once again I'm gonna use the $ symbol to make this a fixed reference
and finally I want to make it an exact match again I'm going to copy this down
and now what I have, is these trades moved over here except in a random order so
we're going to turn this into a capital our ongoing capital by using the sum
function which is the current value plus all the previous values starting with
a notional $100,000 now we can see that we have now populated this chart
here and also every time I make a keystroke I will change the order of the
trades and you may have seen this number over here changing so what we've got
here is this particular scenario the maximum drawdown for that particular
scenario now we can compare and contrast it with the maximum drawdown of my
original strategy I should say this is the drawdown function I am using which
is calculating 1 minus the current value divided by the maximum of all the
previous values so what we have here already as I said this is a simple way
what we have here already is a simple manual Monte Carlo simulator and I could
sit here and I could make 100 keystrokes and
I will have 100 different maximum drawdowns appear in this cell so we
could stop here but I want to show you how we can automate this process and we
do that using Visual Basic or VBA for Excel and I've already set up the code
for the VBA and a couple of simple inputs here firstly I'm going to test
tell it to do 10 iterations so go through 10 different random scenarios
and tell me what the worst drawdown of each of those is and I've got a button
here which I can click which will test my macro and we can
see that our strategy drawdown 16.9% what we would generally expect here is
that our Monte Carlo simulation has come up with a drawdown which is slightly
worse I can click that again and we find one that is actually slightly better I
can click that again and again what what I would generally expect to be the case
it's slightly worse now I can set this to 20 or 50 or a thousand or as many
iterations as you like so I put in some code for this macro here and I can show
you it's here now this is a simple way and I've got some pretty simple code
here now you could simply copy it from the screen if you like but I'll go over
how I've done it so I'm using here a simple counting system and first of
these of interest is here which is the number of iterations which must
correspond to this cell here back again and the next thing that I want to do is
always clear the value of this cell before we do anything
let's get rid of our old values and start afresh so again this range here
must always correspond with this cell here we use a loop which we use in all
sorts of programming every sort of programming really and we're going to do
until I start is equal to our number of iterations so until we've done ten
iterations what we're simply going to do is copy over each scenario at a time and
we're going to work out what I'm calling here the new DD which stands for the new
draw down and we're gonna then use a if statement an if statement to check
whether this particular scenario has a worse drawdown than all the other
scenarios that we have tested and if it does then it gets entered in this cell
here which you can see here is range CI 196 so if we happen to move this
particular cell all we need to do is match up this cell with the new value
it's going to loop and then the end of the macro so this is a nice simple macro
please remember that you can also get this spreadsheet with the Monte Carlo
simulator in it it is available in the Tradinformed shop and there is a link on
the screen if you want to check that out
so I hope you have enjoyed this video on Monte Carlo Simulations please remember
to subscribe to this channel for more videos like this also hit the like
button and comment below if you would like to know more about trading the
financial markets in particular back testing using Excel please go to
www.Tradinformed.com
For more infomation >> How to make designer skirt from old simple skirt ,makeover of old skirt ll new look of old skirt ll - Duration: 6:13. 
For more infomation >> Comment programmer votre télécommande CAME TOP 432 EE sur un récepteur SIMPLE UNICO? - Duration: 2:37. 
For more infomation >> Utilisez cette astuce simple pour nettoyer votre salle de bain. Elle est dix fois plus efficace - Duration: 3:37. 
For more infomation >> L'atelier Kumo et EDF Entreprises : une relation simple et efficace - EDF - Duration: 0:49. 
For more infomation >> Comment réduire son ventre avec un exercice simple et rapide - Duration: 7:16. 
For more infomation >> Jawad Bendaoud, "simple logeur" ou complice des terroristes ? Son procès démarre ce mercredi - Duration: 4:15. 
Không có nhận xét nào:
Đăng nhận xét