Hello, this is Mark from Tradinformed.com.
Welcome to this video, How to Use Excel to Backtest a Trading Strategy using an ATR Stop-Loss
This video is actually a follow-up to a video that I recorded, An Easy Way to Use Excel
to Backtest a Trading Strategy Part 2 For those of you that have watched this video
you will see that the spreadsheet on the screen is the same as the spreadsheet at the end
of that video What we've got is data, historical data, from
the S&P500 Index and we've backtested 2 strategies First of all, buy and hold the index.
And secondly, buy the index when it is above the exponential moving average
In this video, what we're going to do is we're going to look at a 3rd trading strategy, based
upon buy above the EMA And this is incorporating that strategy, but
incorporating a stop-loss using the ATR or Average True Range
OK, so the 1st thing I'm going to do is I'm going to unhide the columns showing the open,
high, low close data Format this top so the cells, like so
Also, I'm going to start putting in some borders so we can neaten it up and make a bit more
sense of the spreadsheet I'll put in some new columns here
These columns I'm going to use to calculate the average true range and the stop-loss
OK, the 1st one we are going to need is the High - Low
The High - Previous Close And the Low - Previous Close
Ok, so, the High - Low We go down a row and we put in the High - Previous
Close And then we put in the Low - Previous Close
Now for these 2 columns we actually use the absolute value which in Excel is ABS
And the reason we do this is that the ATR is always based on a positive number
OK.
We drag these cells down to all the cells below it
And we do this by double-clicking on the bottom right hand corner
OK, Now what we can do in this column is to calculate the True Range
The True Range is simply the highest or in Excel terms the MAX value out of these 3
Again we double click, copy these down All of the formulas on this video will be
available on the Tradinformed.com page associated with this video
OK.
Now we can calculate our ATR and I'm going to calculate the 10 period average true range
just using the simple average 2 decimal places and copy it down
Now, to calculate our stop-loss we are going to use the ATR but often we want to use a
multiple of the ATR I'm going to start off by putting my factor
or my multiple here, which is 1 to start off with
the ATR multiplied by the factor using the $ symbol to keep the factor the same throughout
And copy this down, I'm going to put here is the Maximum Weekly Drawdown
This will tell us whether our stop-loss will be activated in that particular week
This we calculate by =Low - Close In fact what we are going to do is express
this as a positive value so it matches up with the ATR
OK So there we've calculated the stop-loss and
we insert a new column here Again I'm going to put in some new borders
I'm going to call this strategy Buy Above EMA ATR Stop-Loss
Start our trading account at $1000 the same as the other strategies
And start it here OK.
Just to very quickly recap, the buy above the EMA strategy would buy the S&P Index when
it closed on a weekly basis above the EMA What we are going to do here is have exactly
the same strategy but we're going to say that if the maximum weekly drawdown exceeds our
stop-loss value, and the stop-loss is activated, we make a loss equal to our stop-loss
OK.
We're going to do this by using instead of 1 IF Function we are going to use a nested
IF Function, so we'll have 2 IF Functions The first part of this is exactly the same,
so we're going to say that If, first of all, the previous close is greater
than the EMA then, have another IF Function, then we look at whether or not our maximum
drawdown is greater than our stop-loss OK.
so if it is greater than our stop-loss, what we are going to do is we are going to have
the Close - the Stop-Loss Divided by the Close
I'm going to multiply the whole sum by the balance of our trading account
I'll put these in brackets so we are clear which part of the formula goes where
OK, so if we're going to hit our stop-loss, we'll do the same in the previous trading
strategy which is divide this weeks close by the previous week's close
Again, brackets in, and then we multiply again by our trading balance
And we close the IF Function, 2nd IF Function And if neither of these conditions are met,
we will do nothing And then close the 1st IF Function
So there we have it, for 1st week we double-click here
And we've copied it down to all the cells below
OK.
What we've calculated over here was the actual value of the trades
So I'm going to put in another column here Just copy and paste the name, again put in
some borders, And this is simply the current week - previous
week copy this down
And in our final set, here, we calculate our maximum drawdown
And again, what I need to do is copy and paste the formula across here, and then drag it
down OK.
Now I'm going to scroll all the way down to the bottom here
All I need to do it is highlight this whole row here and copy it across
And there we have the comparison of the 3 trading strategies
What I would like to do now is add in 1 other metric to compare the trading strategies
It is 1 that I particularly like actually Which is often called Profit Factor, or it
can also be called the Profit and Loss ratio This is the absolute value of the total of
wins divided by the total of losses Again, just copy it across here
OK.
what I would like to do now is to, we've got all our data
I'm going to move the results into another page of our spreadsheet
Put some borders around here I'm going to copy the names of the 3 strategies
here So there we have our 3 strategies, we can
see that, as expected the EMA-ATR strategy, returns a pretty similar net profit over the
60 odd years that this trading strategy was operational
But, interestingly, and what I particularly like, is the largest losing trade is less
than half of the trading strategy that didn't use a stop-loss and almost a third of the
buy-and-hold the index OK.
There's one final thing that I would like to do
And this is we might want to adjust our stop-loss So I'm going to put in a name, I'm going to
call it stop-loss factor I'm going to put it in the cell here and I'm
going to replace the, scroll to the top Our stop-loss factor here
Put this in as 1 And there we go.
So we can adjust our stop-loss factor from 1 to 1.5 times the ATR
You can see, the most significant thing is that it has increased
the size of the largest losing trade If I put it to 2 times the ATR, and again
our largest losing trade has unsurprisingly increased
So there we have it, we have another trading strategy, we've incorporated an Average True
Range stop-loss And, we've got 3 comparable trading strategies,
that we have quite easily backtested using Excel
For further information please look at the website www.Tradinformed.com