I have spoken to a few people recently who wanted to get some examples of variance ratios in Excel form. Attached to the bottom of this post is one such example. First though, some general information as to what a variance ratio actually is. A variance ratio is a measure as to the randomness of a given time-series.

An example (see spreadsheet) is below for the stocks: CVX, XOM, and their 1 to -1 share weighted pair. First notice the scale, this dataset is back to the 1970’s so it is not so useful for a picture of the current marketplace (just an example). Secondly, notice the slopes of the ratios to each other. The spread has much less variance than the outrights so you notice an immediate and decay in system variance upon the first 20 data points. Secondly, notice the length of stability (almost constant slope) in the region thereafter. All of these computations are performed in the spreadsheet for your review along with the data used for this presentation.

Some empirical observations:

1. A concave, downward variance ratio indicates that the time-series tends to mean revert.

2. A convex, upward variance ratio indicates that the time series typically doesn’t mean revert.

There are several more advanced tests that we can speak of in the future like ADF but we’ll hold off for now.

I have created a Sourceforge repository for the variance ratio spreadsheet and will post all other Excel files there shortly. Please check out https://sourceforge.net/projects/autospreader/files/.

### Like this:

Like Loading...

*Related*

I don’ t think the variances are calculated correctly. It looks like you are including extra rows for calculating the average, e.g.:

=AVERAGE((E$3:E$10152-E4:E10153)^2)

This averages n extra terms where n is the period difference. Not to mention this looks like the “modified variance ratio”. Which is a better version to use when a series is trending.

http://www.ljmu.ac.uk/AFE/AFE_docs/VR_AND_REGR.PDF

Very true and nice catch; what can I say it was late when I posted that to the blog!

I’ve noticed that the spreadsheet includes those extra terms, did you post a correction to the spreadsheet?