Back testing Pivot Points

How to Back Testing Pivot Points

Back testing pivot points can be a fairly straight forward endeavor if you restrict your parameters and it is accessible to anybody who has Excel (or any other spreadsheet program such as the free ones available in open office et al) and half a brain.

In fact, I don't believe that you need anything more than a computer, an internet connection and half a brain to back test pivot points.

Software

You need a spreadsheet program. Download open office's spreadsheet or use one that your already have.

Data

You need High, Low, Close data for whatever symbol you wish to test. If you are going to vary the pivot point formula to include the open price then you need that price as well.

Download the data that your are going to test from Yahoo Finance. Download it in CSV format so that it will be recognized by the spreadsheet application.

Set-up

Import the data into the spreadsheet. (You may just be able to open the data file if you saved it as a CSV file and work straight from there.) Arrange the data so that you have the dates in column A and the Open, High, Low, Close in column B, C, D, E respectfully. It is obviously important that the data is sorted by date with the oldest date at the top and latest at the bottom and it is also very important that no days are missing.

Save your spreadsheet frequently in case you make a mistake. Also save it with increasing numbers appended to it so that you can go back to a previous version.

To the right of the data area you are going to calculate the pivots, support and resistance values. Arrange these in a logical order. I usually have (from left to right) R3, R2...S2, S3. (You may also be including R4 etc. or using H/L levels as in Camarilla Pivots. Remember that the formula on each row that calculates the pivots should refer to the data from the row above it because the pivot values are calculated from the previous day.

To the right of the pivot calculations you are going to create a few columns of rules which will be the back testing area. You are going to use the =if(<condition>, <true>, <false>) command and compare the highs and lows (and perhaps close) of the current day to the pivots for that day.

The results returned by your =if(...) commands can be in points, dollars or a 1/0 if you are going to count number of times something happened.

Once you have set-up your first line with the pivot calculations and rules you obviously copy that line down to the bottom of your data.

Testing

Testing is done by moving to the bottom of the rules area and summing (sometimes averaging - depending on your rules) the data returned by the rules.

Once your sheet is set-up it's fairly easy to change it to test different ideas. There are two main approaches with this particular sheet setup. The first is to change the pivot formulae and then see the new results leaving the rules alone. For example you could change the Pivot Point calculation to be average of Open, High, Low and Close instead of just the High, Low, and Close.

The second major change is to leave the pivot formulae alone and change the rules.

Summaries

Think about the results that you're going to get out of the testing before you start. You need to keep a summary sheet for each change you make to the pivot formulae or rules and note down in a narration column which pivot formulae and rules you used for each result set.

On the summary sheet you can then use the sort function to see which formulae/rules produced the best set of results.