The Easiest Back-Testing of Trading Strategies: MS Excel Pivot Table!

Authors

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:

Excel Sheet with added Month column

Finally, we add a group indicator: Decade

Group indicator for pivot table

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 p can easily be obtained from the log-returns r_i by p = \exp({\sum_i r_i})-1:

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.

4 Comments

Comments RSS
  1. 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.

    • Andreas Grau

      Yes, you are right! This is much better than duplicating the table. I will update this post asap.

      Thanks!

      Andreas

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s