**Before using specialized tools for back-testing I propose that one tries the MS Excel Pivot Table first. The pivot table tool is great for inspection, filtering and analyzing large data sets. In this article, I will present how to create a simple timing-based strategy and how to compute its historical performance.**

In the following, I will show, how to create an analysis like the previous post: “Sell in May and Go Away – Really?“.

## Step 1: Obtain the data

First, we need to get the data for the analysis. We turn to Yahoo to fetch the Dow-Jones Index (See List of Market Data Sources for other sources).

Somehow, Yahoo Finance hides the download button for the Dow-Jones Index. But, it is easy to guess the correct Link:

Yahoo Finance: Historical Dow-Jones Index daily from 1928.

Save this file to disk. Then, open it with MS Excel 2010 and we continue with the next step.

## Step 2: Add Columns for Performance and Indicator

Now, in this file, we add the log-return (Column “Return”) for each day in the time series:

Then, we add the indicator of the trading strategy – in this case just the month of the year:

Finally, we add a group indicator: Decade

## Step 3: Add Pivot Table

## Step 4: Conditional Formatting

In order to get an overview of the data in the pivot table, we format the values in “Percent Style” and by “Conditional Formatting”:

## Step 5: Compute actual performance

The sum of the log returns in the pivot table is a good indication for the performance of a trading strategy. But, the acutal performance can easily be obtained from the log-returns by :

Now, you are ready: Each cell contains the performance of buying the Dow-Jones Index at the beginning and selling it at the end of each months. Have fun with your own studies! You find a detailed study about the performances of the different months in the main indices here.

## Conclusion

Back-testing of simple trading strategies is easy using Excel pivot tables. While more advanced strategies usually require a more specialized software package (as we see in MACD Back-testing), five simple steps lead to in-depths insights of a timing based strategy. If the data series becomes large, one can perform the exact same steps using MS Power Pivot, a free MS Excel Add-in with Database Access.

## Christophe

Nice post. Im glad to land on this blog.

Let me suggest you this :

To see the actual performance IN the pivot table, just add a calculated field from the menu :

Options > Fields, Items, & Sets > Calculated Field…

Then label it “p” and type in the formula : “= EXP(Return)-1”

You can finally add this field to the values area, to get the “Sum of p” right in the table.