Hello This is Mark from Tradinformed.com
Welcome to this video: An Easy Way to use Excel to Backtest a Trading Strategy
The first thing we need if we're going to backtest the trading strategy is some historical data
And i'm using Yahoo Finance and I've opened up the S&P 500
I'm going to click on Historical Prices
My trading strategy here is a weekly strategy so I click on Weekly and then Get Prices
Now, we scroll down?
I'm going to click on the button: Download the Spreadsheet
Just going to open that up
Now, we have the data now available in Microsoft Excel.
Okay, the very first thing I'm going to do, is I'm going to rearrange the date order of our Data
And I do this by highlighting the Column A Sort and Filter - Oldest to Newest
Expand the selection
and there we go, we have all the data going all the way back to the 3rd of January 1950
I'm going to get rid of the Volume and Adjusted Close, because
we don't need it for this trading strategy and I'm also going to hide the Open High and Low columns
I'm going to put a column in here at the start to move this over I'm going to insert two new rows
for our title data
Because we've got a lot of data in this spreadsheet I'm going to freeze the first three rows
So that the titles will always be Visible and I do this by Clicking,
Highlighting row 4. Going to View, Freeze Panes and then Freeze Panes
OK, the trading strategy, we're going to test today uses an
Exponential Moving Average and we're going to use 20 period to start with
the first thing we need to calculate is the
multiplier. And the formula for this is straightforward
= 2 divided by the number of periods + 1
I'm going to put this to two decimal places, just for neatness. There, we can see the multiplier is approximately 0.1
The second thing you need when you're calculating an EMA is the Previous Day's data and
to start this off, we're going to use a
Simple Moving Average, or a Simple Average of the first 19
weeks of data
So i'm going to put that in this cell here
= Average
and highlight the data
Set this to two decimal places as, well and
there we have our first Moving Average and in the cell below I'm going to create our first EMA
The formula for this
is the Close
- Moving Average of the previous period
Multiplied by our Multiplier
And we're going to click into the
Function Bar and click F4 and this freezes this cell G3
Which means that our multiplier cell will remain constant
then we're going to add to this the previous day's Moving Average
And there we have our first EMA
We can easily apply this to all the subsequent weeks by just clicking on the square at the bottom.
of the cell here. Double clicking here. And there we can see now for every week, we have an EMA.
In order that we have something to compare our trading strategy to, I'm going to look at the
profit that would have been gained by
simply buying the S&P 500 Index in 1950 and holding it until the present day.
We put that in this column here, which I'm going to call
Buy Index
I'm going start buying the Index
on this date here, which Is 31st of July, 1950
I'm going to start off the trading account with $1000
the reason why we're starting the account on the 31st of July. Is that this is the first Buy of our trading strategy.
Okay, so i'm going to put in
our $1000 here
and on the next week
The formula we're going to use is
=
This Week's Close / by
Previous Week's Close
Multiplied by our trading account
Okay, we can see that over the course of that week, the Index increased by
0.77% and so our trading account increased correspondingly by
$7.72
Okay and again if we want to apply this to all the cells below, we just double click.
And there we have a running total of the balance of our trading account through the years.
Okay, the trading strategy that, we're going to test today is to
Buy the S&P 500 when the Price closes above the EMA in the Previous Week.
I'm going to use this column here and we're going to call it, Buy above EMA
We start with our same $1,000
Buy here on the 31st of July 1950 and in the week below, we're using
a function to create our trading strategy which Is an IF Function and so we type =IF
the Previous Week's
Close is Greater Than the EMA, Then
We divide this week's close
by
the previous week's close and then multiply that by our trading account balance
If Not, if the close is below the EMA, then, we do Nothing
And there we go
so on the first week of trading. They're obviously, both Trading
Strategy and Buying and Holding, are exactly the same value. If we double-click here
we now have a running tab of
the accounts operating the two different strategies
I'm going to scroll all the way down
to the present day
Okay, and we find out here
that the Buying and Holding the Index and
our Trading Strategy, Buying above the EMA, were in fact showed very similar results. You'd have
turned $1,000 Into $8,541
Buying Holding. And if you'd used the Buy above the 20-Period EMA.
You would have turned your trading account into $83,528
So there we have it. This is a simple way to use Excel to test a trading strategy.
For more Information please see my website:
www.tradinformed.com