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

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:

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

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.