Hello, this is Mark from TradInformed.com
Welcome to this video on how to back test a stock market trading strategy
Using excel. In this video. I'm going to show you how you can use excel to
develop, refine and optimize your own trading strategies
First I would like just to remind you that
if you liked this video please hit the like button, and if you want more videos like this on Excel and
testing trading strategies, please hit the subscribe button
So on the screen at the moment. I have two stock market indices
I have the Nasdaq 100 Tech Index and I have the S&P 500
largest
500 cap US stocks
Now I'm showing a particular
section of recent history here since 2000 the reason I've chosen this particular time period Is
Because we've got some ups and downs in the stock market
Historically this is what we can tend to expect, the markets have always tended to rise and these have been
spaced in with some significant declines, so the declines that we've had in recent years have been the
Dot-Com crash in 2001-2002 and the Financial crisis of 2008 and
2009
Now overall these markets have tended to rise, but one of the things that many of us use
Trading strategies to do is to smooth out our returns and try to reduce our draw downs
Even on the plan that we've got on the screen
we can see that the Nasdaq 100 fell by something like 80 percent in just a couple of years and
even the S&P 500 fell by over 50% in the
during the Financial crisis
So, the trading strategy that I'm going to demonstrate today tries to use both of these stock indices
Now it's a common thought you can find out there on the internet that
stocks are generally doing well when the Nasdaq is outperforming the S&P and
conversely if the S&P is
Outperforming the Nasdaq then we can expect stocks are not doing so well
But how can we turn this bit of received wisdom into a trading strategy?
Well one way that we can do this is we can use Excel to test our theory. Now
I've got here a TradInformed back test Model
And you can use these back test models to test all sorts of trading strategy you can change the data
You can change the time frame you can test a multitude of different technical indicators and price action
entries and exits
you combine that with long and short entries and
different ways, stop losses and
Profit Targets all of these are programmed in
Excel and use normal Excel formulas if you would like more information about these spreadsheets
There is a link on this on the screen at the moment and below this article
So please click on that if you want the more information
Now I'm going to demonstrate
How easy it is to use these spreadsheets and how we can test our theories, so I've already got the Nasdaq
information in here so the next thing we need is the S&P 500
So all I need to do is get my data
I'm just going to simply copy and paste this
directly in just making sure that the dates match up
And then we can see we've got the open,high, low, close and volume data from the S&P 500 as well
Now I want to compare these two markets and there are many ways that this can be done, but today
I'm just going to look at using an
exponential moving average
And we can calculate this very easily in Excel so first of all we just use the equals average
formula, so this gives us a
Simple moving average, and we need one of those before we can start the calculations for the EMA
Now the calculation we use
put in a couple of brackets to start. We take the most recent close data and we subtract from that the
most recent average previous period's average and then we multiply that by our EMA factor
press F4 to make that an absolute reference cell and then add to that the previous period's average
Make sure our brackets average out. So there we have the first of our EMA averages
If I just double click that it copies it down to all the cells below nice and quickly. Now
I said we're going to use a ratio of these two to get a ratio. All we're going to do is divide
Nasdaq by the S&P
Click enter, click double Click
And then we have all our formulas or calculations in there.
Now the thing I've got next to this is just a simple measure of the ratio. Now
We don't need this because you could just take it straight from the ratio
But one of the beauties about using excel, for all sorts of analysis, is it's very
Visual and there's often times where we want to check and look at the data
And we can easily see it here because we've used a color to indicate
when the ratio is rising
Indicating the Nasdaq is stronger and what it's falling indicating to the S&P is stronger
Now all we need to do is come up with some entry criteria
The simplest one of all is going to be when we have a changeover. When the ratio turns from pointing downwards
to pointing upwards, so I'm going to say when this is equal to one and the
Previous period Is equal to minus one
double click again and
Then we have the strategy programmed into it. Now
I've already put in a close you can see that here and the positions close when the ratio changes in the opposite direction
Okay, so we can see the results in the screen on the screen at the moment
We can see
this is the underlying market here is the Nasdaq because we're using that to open and close our trades
We can see we've got this huge decline here
Another still fairly significant decline here before the recent
run-up over the past what eight to nine years.
And we can see how this is
By comparison in this trading strategy graph we can see that
We have a general rise up.Although we shouldn't forget that there is some periods when we have fall
But mostly we're heading in the right direction. In particular, we're looking at the maximum drawdown
so we've got a maximum drawdown of
16.3 which is quite low
Over this period of time is quite a nice little drawdown
Which is suggesting that our theories. There might well be some validity to them
Now we've got once we've got it loaded into our spreadsheet
We can do all sorts of things we can check the ema. We can check different values of the ema, we can try a longer,
put it up to a hundred and we can see that we have fewer Changeovers, and we have a
relatively smoother rise up
We might want to use a profit target or we might want to get rid of it and set it to a very high level
And we see again this changes how the strategy operates. We might feel more comfortable with a stop-Loss
and we can see how
different values of stop-loss affect the strategy. It's easy to do this. We can do this with a click of the button
So I set this back here. We might want to show what
difference is, what difference leverage would make. At the moment we're using a one-one leverage
What about if we want to double that?
We can see that our net profit increases by more than double that amount but we also seeing a rise in the max drawdown
If I was to increase it to four times then we have
Quite a considerable net profit, but again at the risk of our drawdown increasing
Now we might want to go back to our strategy and see well what would happen if we used
a
Simple moving average as a filter? Or we can. I've got a twenty period SMA already programmed in here
So we could say,, take the close
And we always want the close to be greater than that SMA but before a trade is opened
And I just press enter, double click
And we can have a look how the strategy looks now
So you can see here that with some relatively simple
tools we can come up with quite a sophisticated model that can look at various different
situations and see what has performed best over the historical period
So I hope you enjoyed this video. I hope you found it useful and
if you would like more information about
trading the financial markets. About in particular using Excel to back test trading strategies please visit
TradInformed.com. There is a link on the screen, and if you want more videos like this
Please check the next video in this sequence: How to Back Test a Trading Strategy in
Excel