Equity Ranking Backtest with Python/Pandas

I have been look at equities a bit of late, I am particularly interested in ranking a universe of equities for “low frequency” manual trading on a weekly or monthly basis.

Every period I would rank each name on a bunch of different factors, then invest in the highest ranked ones for that month.

I was initially working in R but the code grew unwieldy, and I wanted a second opinion on my approach so took the time to re implement it in python using Pandas.

Setup

For each symbol in our universe, we load the raw data and generate the information used for ranking. If we have 5 names, we end up with 5 dataframes.

Then we combine those dataframes into one big dataframe, and iterate through month by month, selecting the symbols that meet our ranking criteria. From those selected, we equally weight and sum the next period returns.

One thing that is really cool about the pandas dataframe is that it allows multiple rows with the same index.

This makes it easy to get the data for the month under consideration. We just pass the month to index function and get the subset of data for that month, e.g.

>>> df.ix['2015-02']

                 cpr       npr       avg   over  sym

Date                                               

2015-02-28  0.043302 -0.062449 -0.038914  False  DBC

2015-02-28 -0.025028  0.008524  0.006130   True  IEF

2015-02-28  0.056838 -0.014239  0.005434   True  VEU

2015-02-28 -0.037434  0.017171  0.015900   True  VNQ

2015-02-28  0.055832 -0.011697  0.009236   True  VTI

[5 rows x 5 columns]

>>> 

In this example there are 5 symbols, and we see the ranking information for February 2015.

Another option would be to use hierarchical indexing, with a sub-index for each month, but this way worked for my needs and I think is quite clean and simple.

If anyone knows an equivalent in R that is as clean and easy to work with for multiple time series I would love to hear about it. 

Code Notes

The demo code does a simple back test of the GTAA/Relative Strength trend following system using ETFs.  

I have stripped it down to the basics so hopefully it is easy to understand. Load the data, generate the dataframe with the info we want, make a combined data frame, then go through month by month.

The ranking is done by filtering out names under their 10 month moving average, then selecting the top n based on average 3 month return.

The “cpr” column is the current period return, and the “npr” column is the next period return, which is the return realized if we select a given security for that month.

The data is just ETF data from Yahoo, which I have put up here. Code is here.

I found Python For Data Analysis a very useful book is when working with pandas.

Does Trend Following Work?

I’m not sure how I came across it, but I have had Jez Liberty’s Au.Tra.Sy blog in my reader since around 2009.

Since then, he has tracked well-known trend following systems and reported monthly performance figures. These are things like moving average crossovers, Bollinger band breakouts and stuff like that.

The systems had a very good month in November, and a very good year, up ~45% YTD.

I thought I would take a look and how things have gone over the time it has been tracked. Helpfully, Jez posts annual summaries for each system and a composite average, which is what I used. You can find the links to specific posts in the script linked under the table below. N.B the first two years are the average of actual trend following funds not the generic strategies.

There are results for 6 years, from 2009 to 2014, using the YTD November figure for 2014. We end up with a series like this, starting from 100.

Source [R]

We can see that 4 of the 6 years ended under water. Over the same time there has been a huge surge in US equities. A simple buy and hold in 2009 would have more than doubled equity.  

The pain of losing

I have been following Jez posting results every month for 5 – 6 years now. When I think back to where I was in 2009 versus where I am today, five years is a really, really long time.

You can get historical data from 1920s and even further back in time. If you run a backtest over 80 – 90 years of data, a 3 – 5 year period of underperformance is barely noticeable on an equity curve.

Sometimes I see people get excited about their backtests even though they include these periods of poor performance. There is plenty of research and evidence that humans in general find it very hard to stick with a losing system.

How seriously would you take someone touting a system that is underwater after five years, but they assured you a 45% year was just around the corner?

Professional CTA

I believe the Au.Tra.Sy data is based on commodities and futures trading, so thought I would take a look at the Barclay CTA Index as a comparison. The results seem somewhat similar, it has struggled somewhat since 2010, though both the upside and downside returns are smaller. 

In the 29 years from 1980 to 2008 inclusive, there were only 3 down years in total, none of them consecutive.

In the period we are examining here, the 6 years from 2009 – 2014, four of them have been down years, including three down years in a row (2011 - 2013).

Proselytizing

In some ways, you can think of mechanical trend following as being long tail risk. Effectively it wants a big sustained move in either direction. If you think about the distribution of returns, the big moves that are “pay days” are out in the tails.

I don’t follow commodities vol at all, but apparently it has been low for several years, according to this article from September, 2014. It probably shouldn’t be a surprise that strategies dependent on big moves have not performed well in an environment of low volatility. 

I personally dislike these indicator-based trend following systems, even though I think they give entries as good as any. The problem is they are too slow to close positions and give back too much profit.

They only really make serious money when a megatrend eventuates, and these are relatively rare and IMO getting rarer. In the mean time they can get chopped around and experience significant drawdowns.

I don’t want to draw any final conclusions about classical trend following, but I know I would have a lot of trouble sticking to systems like these in practise, even though a long term backtest might look really nice.

However, if you understand the conditions under which your system does well or is likely to underperform, it is a lot easier to stick with it during periods of underperformance, which are inevitable over the long run.

It is worth spending the time thinking about the conditions in which your system does well and does poorly.