An Easy Way to Use Excel to Backtest a Trading Strategy – Part 1

Hello This is Mark from 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:

15 thoughts on “An Easy Way to Use Excel to Backtest a Trading Strategy – Part 1

  1. this is great. Mark, could you tell me how to measure the performance of allocation effects? for example, an indicator that increase and decreases the percentage of investment made?

  2. Thanks for the comment. There is a factor in the model for adjusting the percentage of our capital invested per trade. I think what you are looking for is for an optimisation function to compare these different percentages. If so we could optimise manually by changing the factor. This could be automated and I would be able to do another video showing how this could be added to the spreadsheet.

  3. One question Mark, shouldn't this EMA crossover strategy have more than one if statement? Because the only thing your If statement did is buy the security but never sell it.

  4. can someone tell me please Why for example u should period of 20 ? what if my data are monthly (from 2007 till 2015) ?

  5. Hi Mark,

    Can we backtest a strategy using Force Index ( by Dr. Alexander ) signals in Excel? Please advise.

  6. hi Mark
    i have watch this video many times and i still dont understand what information to get from it to help me know either to buy/short a trade. i am new to trading and my biggest problem is to extract the information i really need. please advise me.

  7. hello i cannot understand one thing how is it possible since you start with 100$ and then the index is going downwards your value to be the same? (although you are not buying, shouldnt your account value getting less?) and each time you buy do you add another 100$ ?

  8. Hi, So I have one question. Why did you use the first 19 numbers to create the base for EMA instead of using 20 numbers ?

  9. Hello Mark.
    I think its a great idea to work with excel. This make one undestand what is really doing.
    I am verey very new in this matter. What are you doing in the Buy Index colum? Buy in a week and selling the next week? Its the same that buy in the first period and sell in the last ? Thanks in advance

  10. When you round up the EMA from 0.095238 to 0.10, You're adding close to 5% error into the calculation. Wouldn't you be better off rounding to 0.095?

  11. Hi mark
    This makes no sense to me. You invest the same amount in both scenarios indicating in the first “buy index” scenario that you keep the initial $1000 in the market and it accumulates over time. In the “buy above ema” scenario you just seem to ignore any week where previous weeks close is below ema indicating that you just skip that week loss and reinvest the same amount the next time the”if” requirement is meet. I am probably missing something here but shouldn’t this back test take into consideration when to exit the position (ie when the “if” criteria is not meet) ? I would be super thankful for a reply on this one as I can’t get this to fit in my head 😉

  12. hi do you have any recommendations to where I can get accurate historical price data from please?

Leave a Reply

Your email address will not be published. Required fields are marked *