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. 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. Before you start testing any strategy, you need a data set. 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.
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. You can now delete the columns that you're not going to use. I also sorted the data so that the oldest date was first and the latest date was at the bottom. 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. In columns D to H, I have place formulae to determine the return on a particular day. 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:. 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 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.
At the bottom of the weekday columns I have placed some summary functions. Notably the average and sum functions. These show us that during the most profitable day to implement this strategy was on a Tuesday and this was closely followed by a Wednesday. The objective of that test was to see if Expiry Fridays were generally bullish or bearish. 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. 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. Data Before you start testing any strategy, you need a data set.
Yahoo Finance In the Enter Symbol s field enter: I selected from 1 Jan to 31 Dec Scroll down to the bottom of the page and click Download To Spreadsheet Save the file with a name such as ibm. Preparing the data Open the file that you downloaded above using Excel. When I downloaded this file the top few lines looked like 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.
Entering the formulae The tricky part unless you're an Excel expert is working out the formulae to use. It looks like this: 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. Good luck and profitable strategy hunting!More...