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