Using Excel to Back Test Trading Strategies

How to back test with Excel

I've done a fair amount of trading strategy back testing. I've used sophisticated programming languages and algorithms and I've also done it with pencil and paper. You do not need to be a rocket scientist or a programmer to back test many trading strategies. If you can operate a spreadsheet program such as Excel then you can back test many strategies.

Objective

The objective of this article is to show you how to back test a trading strategy using Excel and a publicly available source of data. This shouldn't cost you any more than the time it takes to do the test.

Data

Before you start testing any strategy, you need a data set. At minimum this is a series of date/times and prices. More realistically you need the date/time, open, high, low, close prices. You usually only need the time component of the data series if you are testing intraday trading strategies.

If you want to work along and learn how to back test with Excel while you're reading this then follow the steps that I outline in each section. We need to get some data for the symbol that we are going to back test.

• Go to: Yahoo Finance
• In the Enter Symbol(s) field enter: IBM and click GO
• Under Quotes on the left hand side click Historical Prices and enter the date ranges you want. I selected from 1 Jan 2004 to 31 Dec 2004
• Scroll down to the bottom of the page and click Download To Spreadsheet
• Save the file with a name (such as ibm.csv) and to a place that you can later find.

Preparing the data

Open the file (that you downloaded above) using Excel. Due to the dynamic nature of the internet, the instructions that you read above and the file that you open may have changed by the time that you read this.

When I downloaded this file the top few lines looked like this:

You can now delete the columns that you're not going to use. For the test that I'm about to do I will only use the date, open and close values so I have deleted the High, Low, Volume and Adj. Close.

I also sorted the data so that the oldest date was first and the latest date was at the bottom. Use the Data -> Sort menu options to do this.

Strategy

Instead of testing a strategy per se I'm going to attempt to find the day of the week which provided the best return if you followed a buy the open and sell the close strategy. Remember that this article is here to introduce you to how to use Excel to back test strategies. We may build on this going forward.

Here is the ibm.zip file which holds the spreadsheet with the data and formulae for this test.

My data now resides in columns A to C (Date, Open, Close). In columns D to H, I have place formulae to determine the return on a particular day.

Entering the formulae

The tricky part (unless you're an Excel expert) is working out the formulae to use. This is just a matter of practice and the more you practice the more formulae you'll discover and the more flexibility you'll have with your testing.

If you have downloaded the spreadsheet then take a look at the formula in cell D2. It looks like this:

=IF(WEEKDAY($A2,2)=D$1,$C2-$B2,"")

This formula is copied to all of the other cells in columns D to H (except the first row) and does not need to be adjusted once it has been copied. I'll briefly explain the formula.

The IF formula has a condition, true and false part. The condition is: "If the day of the week (converted to a number from 1 to 5 which matches Monday to Friday) is the same as the day of the week in the first row of this column (D$1) then..." The true part of the statement ($C2-$B2) simply gives us the value of the Close - Open. This indicates that we bought the Open and sold the Close and this is our profit/loss. The false part of the statement is a pair of double quotes (") which puts nothing in the cell if the day of the week is not matched.

The $ signs to the left of the column letter or row number locks the column or row so that when it's copied that part of the cell reference doesn't change. So here in our example, when the formula is copied, the reference to the date cell $A2 will change the row number if it's copied to a new row but the column will remain at column A.

You can nest the formulae and make exceptionally powerful rules and expressions.

The Results

At the bottom of the weekday columns I have placed some summary functions. Notably the average and sum functions. These show us that during 2004 the most profitable day to implement this strategy was on a Tuesday and this was closely followed by a Wednesday.

When I tested the Expiry Fridays - Bullish or Bearish? strategy and wrote that article I used a very similar approach with a spreadsheet and formulae like this. The objective of that test was to see if Expiry Fridays were generally bullish or bearish.

What Now?

Try it out. Download some data from Yahoo Finance, load it into Excel and try out the formulae and see what you can come up with. Post your questions in the forum.

Good luck and profitable strategy hunting!