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.

What can we use the VIX for?

In part 1, we took a look at VIX and the relationship it had between historical volatility and realized volatility.

Continuing on, I thought I would take a look at next day returns and the VIX. There is a relationship between SPX and VIX in that when SPX drops, VIX typically rises. This leads the question: does a high VIX serve as a useful indicator of what might happen to SPX the next day?

A problem in answering this is quantifying what we mean by a "high" VIX. A VIX of 25 might be high if it has been under 20 for the last 3 months, but might be considered low if it has been around 30.

To deal with this I use a proportional measure based on its previous trading range. If it is at the top of its range, it gets a 1, and at the bottom it gets a 0. Anywhere in between it gets a number between 0 and 1.

I used data from 2000-2013 and a 252 period lookback, which is roughly 1 year, recording the next day return of SPX. I split this data into 5 groups, the first with relative VIX readings up to 0.2, the second between 0.2 and 0.4, and so on up to 1.0. Then I plotted the next day returns as a boxplot, giving the distribution of returns for a VIX reading in its relative quintile.

The box on the far left corresponds to the lowest relative VIX levels, while the box on the far right corresponds to the highest.

One thing that jumps out to me is that the mean of all these returns is more or less zero. Using this relative measure of VIX has no value as a predictor of next day returns, as whatever measure we use, a mean return of zero will see us end up flat (or in practice, probably down a bit). If there was a real edge, we would expect the mean return to be somewhere other than zero.

The second thing that I notice is the distribution of returns is a lot wider for the right most two boxes, which corresponds to relative VIX measure above 0.60, i.e. relatively higher volatility. Now this makes sense, as we would reasonably expect periods of higher volatility to have higher volatility.

It seems that high readings of VIX can serve as a useful predictor of higher periods of next day volatility, and for someone trying to implement a volatility filter, that could be quite useful.

Conclusion

To recap, we have seen that:

1) VIX is not very good at forecasting 30 day realized volatility.
2) Historical volatility can be a reasonable proxy for IV/VIX.
3) Higher periods of volatility do portend continued higher volatility, at least for the short term (1 day).

Note that 3 does contradict the mean reversion of volatility, because it is looking at the next day returns only. Over a longer period we would certainly expect it mean revert, otherwise we'd be experiencing some very, very bumpy rides ...

Also, I did similar analysis using RUT & RVX and NDX & VXN, both of which had very similar results. We will take a look at them in the next post where we use the findings from this post and the previous to implement a volatility filter.

Code for this post and the previous is up: here

Digging into the VIX

I wanted to revisit using some sort of volatility filter for systematic trading. In particular, if we are trading SPX, can we somehow use the VIX to produce better risk adjust returns? This is not about trading volatility, but more about using additional "out of band" data in our systems.

I thought I would take a look at the VIX, what is it, and can it help us?

In theory, the VIX is the market consensus of what future 30 day realized volatility of SPX will actually be, as derived from option prices of SPX. Anyone who watches or trades SPX has seen that a drop in SPX usually results a rise in VIX, or the IV in SPX option chains.

Occasionally this relationship breaks down, which can be a useful as an indicator in itself, but I had a sneaking suspicion that the VIX would have more in common with historical vol than what realized vol turns out to be over the next 30 calendar days. That is, the VIX is not particularly useful as a longer term forward looking indicator of volatility, at least no more than current historical volatility.

I did a bit of data gathering and calculated realized and historical volatility for SPX. I used 21 periods when calculating vols as the VIX covers 30 calendar days, which is roughly a month, and there are roughly 21 trading days in an average month.

First of all, we can take a look in hindsight at how well the VIX forecast of volatility matched the actual realized volatility over the next 21 days.

In this chart, the red line is what realized volatility turned out to be, going forward from a given day. It is impossible to know this in advance, so we are engaging in some serious data snooping, for educational purposes only. The teal line is the VIX. Below them is a plot of VIX - SPX realized vol, and flagrant abuse of gradients.

You can see that the VIX gives an estimate that is typically higher than what actually eventuated, a well-known phenomenon, which for convenience I will put in "The Volatility Risk Premium" basket.

There is one big divergence around the end of July, 2011, when the VIX was significantly underestimating what realized vol would actually turn out to be.  In August 2011 there was a large selloff and you can see the VIX jumped to a rather ebullient level.

If we moved the red line forward 21 periods, we would get historical volatility, that is, the actually volatility that occurred over the last 21 trading periods.

We can see the relationship is much closer. The VIX still generally reflects a premium over historical volatility, but big moves in the underlying SPX correlate to big moves in VIX/IV.

Relations

The correlations between these three measures, IV, RV and HV are worth taking a look at. This is using data from January 2000 up to about the end of January 2013:

There is a 91% correlation between historical volatility and VIX. This is higher than the correlation between HV and RV, and HV is really just RV shifted back 21 periods. It seems the relation between VIX and HV is something quite strong.

Now because these are all measures of very similar things, we might expect high correlation. Another metric we could use too look at the relationship is R-Squared.

We can see a relatively strong relationship between historical vol and IV/VIX, at 0.60. The relationship between IV and realized vol is looking a lot weaker.

Conclusion

From all of this, I concluded that the VIX is not a particularly useful indicator of 30 day future realized volatility. However, it is a reasonable estimate of what historical volatility was.

As it turns out, this is not particularly useful, as we can easily calculate historical volatility. It does mean that we can flip things around, and use historical volatility as a reasonable proxy for VIX or market expectations of future realized volatility.

This will turn out to be quite useful, as we may find ourselves trading instruments that do not have nice, readily available VIX equivalents, but that is a topic for a subsequent post.

So ends part 1. These posts turned out very long so I decided to split them up, part 2 should be available here.

The R code for this and the next post is up here.

Mebane Faber Tactical Asset Allocation in R

In 2006 Mebane Faber published a great piece of research detailing an asset allocation system that was both very easy to understand and implement, as well as carrying very respectable risk adjusted returns.

The details are available in his paper A Quantitative Approach to Tactical Asset Allocation and were further expanded on in his book The Ivy Portfolio both of which are must reads.

The short version is to use diversified asset classes, long only, and only long when the price is above the 10 month simple moving average (approx 200 day). The assets he tests are U.S. Stocks, International Stocks, U.S. Government Bonds, Commodities and Real Estate, accessible via ETFs.

A rotational extension can also be added by investing only in the top 1-3 asset classes showing some degree of relative strength, which is defined as the average of 3, 6 and 12 month returns. They must also be over the 10 month SMA to be candidates.

The system updates monthly at the end of the month, it is about as hands off as you can get for active management.

There is an ETF for those so inclined, GTAA, but I am experimenting with a put selling implementation, which I might start tracking here month to month. I wrote a small R script using quantmod to display the relevant information for given symbols, which should be available here: Tactical Asset Allocation R script

The output looks like this:


  Sym         R3m         R6m        R12m  Close     AvgRet OverMA
4 VNQ  0.09295631  0.22412597  0.08488552  63.65 0.13398927   TRUE
1 VTI  0.11671109  0.22466699  0.05037598  72.26 0.13058469   TRUE
2 VEU  0.10908623  0.13282091 -0.10915250  44.22 0.04425155   TRUE
5 DBC  0.07048208  0.11194076 -0.05767911  28.80 0.04158124   TRUE
3 IEF -0.02193049 -0.01718305  0.10473673 103.28 0.02187440   TRUE


Let me know if you have any comments or find any bugs.