Excel Data Analysis 3.1 (Volatility Filtering)

So here we apply a realized volatility (RV) filter as a watered down, simple form of quantitative risk management, to hopefully lower the negative swings of an invested asset. Let’s use the REIT, IYR again for this example. 

Cumulative Returns

Let’s create a column in our spreadsheet for Cumulative Returns, Rcumu, where we add up all the day to day log returns. Excel formula:

To chart a buy and hold return of IYR without having to include quartile numbers, we can scroll all the way down, highlight cell “A10”, then hold Shift and left click cell “B3134”.  Then at the top go to Insert -> Line -> 2D lines. 

And we can see that return off a purely buy and hold scheme would have experienced extremely high volatility. 

Let’s see if we can reduce the risk with a RV filter. The idea is to make it so that no position is held if the RV becomes greater than a certain threshold. In the example worksheet, I’ve used the cell “I3” for the threshold, and used 50% as a starting point. Created a new column, Rcumu*, for the cumulative returns based on the volatility threshold. 

Excel formulas  

 

Now we can compare the cumulative returns between with and without the 50% volatility filter.

And we can see that for this asset, in the sample period, the volatility filter reduced the buy and hold unrealized swings (risk), and increased over all expected return over time.

Excel data analysis 2

I will go over using excel to back-test a very simple mean reversion pattern in this part. It will utilize the If() function.

About the data
DIA (SPDR Dow Jones Industrial Average) will be used for this example. We have the data going from 1/20/98 to 11/14/2012. We will look at the change between daily opening and closing prices, using logged returns: 
 dX(O,C) = ln(C/O)

Where 
dX(O,C) : change in price from open to close
O : Opening Price
C : Closing Price

Excel formula :
*The Count() function gives the number of rows of data we have, in this case 3,734.
and we end up seeing this: 

So we can see that the average price move from open to close has been pretty close to 0 where a few more ups days had occurred than down days.

Theory around mean reversion

Since we know that the average Open to Close moves have been about 0, then would an up day follow a down day, and vice versa? 

We want the spreadsheet to give each day's dX(O,C) if the previous dX(O,C) was negative, and -dX(O,C) (to similate a short position) if the previous dX(O,C) was positive. Two columns are created for the Longs-> MR(Longs), and Shorts-> MR(Shorts)
*MR stands for Mean Reversion

The If() function get this done.

 and we end up seeing this: 

We can see that for both Long and Short signals, a slight edge had existed. Whether this would have overcome transaction costs is however uncertain.