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

[Insert->Pivot Table]

Sort Data in Table

[Pivot Table Tools -> Options -> Summarize value by -> Sum]

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”:

[Home -> Styles -> 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.