Backtesting A Basic ETF Rotation System in Excel – Free Download

Some Background:

Let’s face it, technology has made it possible to access a wide range of tools for developing, backtesting, and optimizing systems. However, a simple (but powerful) tool like Excel is a great way to validate a trading system. In this example we’re going to be keeping things really simple and backtesting a monthly ETF rotation system using 5 symbols that significantly outperforms buy and hold. All you need is Excel and an Internet connection. If you’re reading this, I’m guessing you have the Internet connection. This post assumes you have a basic understanding of Excel, but you don’t need to be an expert. You can either build the Excel file yourself by following the steps below or download it here.

Feel free to distribute the file, but please provide a link back to this post if you do.

ETF Rotation System Excel

Once you’re done with the steps below, you’ll be able to chart your results and get a picture of how the system really performs. The chart below is an example of a 5 ETF rotation system that ranks and buys the top ETF every month. That performance is compared to buying and holding all ETFs equally (without rebalancing) and holding the S&P 500 ($SPY). You’ll find the exact calculation in Excel, but for the 9 year period tested the ETF rotation system generated an annual return of 11.88% vs 7.75% for SPY and 7.18% for all held equally.

ETF.RotationSystemExcel

It’s worth noting that the way the Excel file is constructed does not lend itself well to scaling up. However, if you have some Visual Basic skills and a little creativity, you should be able to come up with a way to scale it.

Step 1: Choose Markets

Choosing markets is a critical decision for all systems. Since I have a strong preference for trend following trading, we’re going to use a diversified group of markets. That being said, you could also consider using stock market sectors (Technology, Healthcare, etc), various commodities, or something completely different. I wanted to use relatively uncorrelated markets with around 10 years of historical data so I’m going to be using the following ETFs:

SPY – S&P 500
GLD – Gold
IEF – 10 Year Treasury
IYR – US Real Estate
EEM – Emerging Markets
The image below shows the individual ETF returns over the test period:

ETFRotationMarketsExcel

Step 2: Collect and Consolidate Data

Once you’ve decided on the markets, you’ll need to fire up Excel and find a source for historical data. In the interest of frugality, let’s use free data from Yahoo Finance. Just a reminder that the construction of this particular spreadsheet doesn’t scale extremely well so fewer symbols will be easier to execute. That being said, with some creativity and skills in Visual Basic you could make the test more dynamic.

When you head over to Yahoo Finance, you can enter one of the symbols you’ve chosen and get a quote. On the quote page, click the link on the left for “Historical Data.” On the Historical Data page, select monthly data and submit. If you scroll to the bottom of the monthly data there is a link to download the data to a spreadsheet. Download monthly historical data for all of the symbols in your system.

Once you’ve downloaded the data, you need to consolidate everything into one spreadsheet. The data is likely to be in .csv format so you’ll want to get a .xlsx book going. Put the data for each symbol on a separate sheet and then create a new blank sheet for calculations.

Step 3: Calculate the One Month Return Using Adjusted Close

In this step we need to go through each sheet and calculate the one month (or one period) return for each ETF. I’d recommend going to each sheet and calculating the one period return in the column directly to the right of the adjusted close data.

One period return = (Prior price – current price)/prior price

In Excel speak, this is going to look something like =(B3-B4)/B4 where B4 is data from the prior month and B3 is the current month.

After you’ve gone through every sheet and calculated the one period return, we need to consolidate that data onto the blank sheet for calculations. When you consolidate copy and paste the adjusted close column and the one month return column. Note that you’ll also want to copy and paste a date column into Column A of your calculation sheet.

Step 4: Calculate the Percentage Change for Ranking the ETFs

At this point you should have an Excel workbook with 5 sheets for raw data and one sheet with two columns of data for each ETF. I’d recommend using column A for the date, B for adjusted close ETF1, C for one period percentage change, D for adjusted close ETF2, etc. If you have that format, you’re ready to proceed.

The next step is to calculate the rate of change for each ETF. In this example, we’re using a 5 month rate of change. I used 5 columns to the right of the existing data on the calculation sheet. In each column we need to calculate the 5 month return for an ETF. The return is very similar to the one period return calculated in Step 3 above, but we need to make it for 5 months instead.

In Excel the calculation should be something like =(B3-B8)/B8 where the data in cell B8 is from an earlier period. You’ll need to do the same calculation for each ETF.

Step 5: Rank the ETFs

Once you know the 5 month rate of change, we want to rank the ETFs. I used another 5 columns to the right of the 5 month change column to rank each ETF. The idea is that each ETF has a column and that column shows the ranking for the ETF. For example, the SPY column will display a 1 if SPY is the top performing ETF for the period.

The Excel formula we need to rank the data is =RANK(%Change Cell, Range of % Change Cells)

If the percentage change data for the period was held in cells L9 to P9, this would be: =RANK(L9,L9:P9)

The next column over for ranking would simply be =RANK(M9,L9:P9) because we’re ranking the percentage change in Column M in the same group of data (L9 to P9).

Step 6: Determine the Appropriate One Period Return

Once we’ve ranked the ETFs based on the 5 month return, we need to select the appropriate one period return. In order to do that, I created a column for the one period return and used a simple IF statement to select the correct return. Essentially what we’re trying to say is something like, if SPY is ranked #1, then the return is the number in the one period return column for SPY.

In Excel, the statement looks something like =IF(Q10=1,C9)

The Excel logic for an IF statement is =IF(Condition, Outcome if True, Outcome if False)

However, since we have 5 ETFs to go through, the formula ends up looking somewhat ugly with several nested IF statements. For an example of the nested IF statements, it’s best to refer to the spreadsheet data.

Step 7: Simulate Account Equity

Once we’ve created a column with IF statements that pulls in the appropriate one period return, it’s pretty easy to simulate account equity. We simply go to the oldest data on the sheet and input a starting equity amount. In this spreadsheet, I used $10,000 as the initial equity. With the $10,000 initial equity in place, you multiply the prior period equity by 1 + the current period return.

For example, let’s say we put 10,000 in cell Z117 and the one period return data lives in Column V. The account equity calculation for cell Z118 would be =(Z117*(1+V116))

You’ll need to copy or fill that formula all the way up the account equity column and then you’ll have a column that shows the value of your account when you buy the Top Performing ETF every month. At this point you’re effectively done with the basic calculations and you can go back and modify values or test different equity outcomes. In the workbook provided, there are also columns for a $10,000 investment in $SPY and a calculation for buying all ETFs equally.

Step 8: Analyze the Data

If you made it this far, you should have a spreadsheet that is calculating a bunch of numbers. The next step in the process is evaluating the numbers, making pretty charts, and forming opinions and conclusions about the data. I’m not going to delve into the details of charting in Excel, but there are two simple charts sitting inside the spreadsheet for you to play with and manipulate.

Recap:

Phew, that’s it! If that was more than you can handle, you can download a sample copy of the Excel workbook above and walk through the steps using the workbook as a guide. Understanding how the Excel file works is essential to creating extensions and testing different ideas. The system we tested above is very simple, but should give you a basic understanding of how to backtest an ETF rotation system in Excel.

– Dan from Theta Trend

About the Author System Trader Success Contributor

Contributing authors are active participants in the financial markets and fully engrossed in technical or quantitative analysis. They desire to share their stories, insights and discovers on System Trader Success and hope to make you a better system trader. Contact us if you would like to be a contributing author and share your message with the world.

  • Red says:

    Thank you for the post. However, after reviewing the spreadsheet logic I have to wonder if it is correct?

    Here is my concern, the performance that you base your decision upon may not coincide with the performance of implementing your decision.

    This is what I mean. The way that the data is set up, one will make their decision to buy the ETF at beginning of December based upon the performance of the ETF from the close of Oct to the close of Nov. However, to exercise the buy, you will most likely buy on the open of Dec and not the close of Nov, as implied in the spreadsheet performance calculations.

    I always like to think of it in terms of “implied” performance may not be an indication of “implemented” performance.

    Red

    • Dan says:

      Hi Red,

      The timing of the buys is an interesting thing. In reality, I trade a rotation system and do take positions on the last trading day of the month rather than waiting for the first day of the next month.

      One of the things I found when I was testing various rotation systems is that the CAGR decreased by up to a couple percentage points when you initiate positions on the first day of the next month. To me that suggests that the top performing markets tend to gap in the direction of the trend. Institutional buying perhaps?

      Overall, I’d say that the excel file above is more of an exercise in validating a trading idea in excel with the added bonus of seeing how the system performs. Thanks for the comment.

      Best,
      Dan from Theta Trend

  • Marco says:

    Thank you for the explanation. I’m a great fan of etfs rotation strategies, this is helpful also for non-experienced traders,
    https://nightlypatterns.wordpress.com/

    • Dan says:

      Hi Marco,

      Thanks for sharing your site, it looks really interesting and I’m going to spend some time reading through the information. Recognizing patters is something that I haven’t explored extensively, but is something that I have an interest in learning more about. Drop my site as well when you get a chance http://www.thetatrend.com and feel free to get in touch. Thanks again for sharing.

      Best,
      Dan

  • Mark says:

    The flat time on the first performance graph is approaching four years. Would you really advocate trading this? Also, can’t use of Yahoo! Finance’s “adjusted close” cause problems? When dividends are paid, all of the previous data changes. In backtesting, you are therefore going to use numbers that would not have been seen in real time.

  • WhatsintheName says:

    Is the data here normalized? As in you mentioned that each market is equally weighted, but one contract of each of these markets isn’t equal I believe. Correct me if I am wrong.
    Should there be a normalization factor such as the big point value?

  • >