## Tuesday, November 27, 2012

### Excel data analysis 4.0 (Index Vol Trades)

Here we look at a multi-parameter index option trading logic, using MAD (Median Absolute Deviation) with linear Realized Volatility forecasts(Excel Data Analysis 3.3). Stemmed from Robust Statistics, I like MAD mainly due to the fact that financial data distributions are usually not Gaussian.

Theory
Mentioned in the VIX white paper, VXN is a weighted average of Out-of The-Money NDX option implied volatilities. We can then estimate that an edge gained on the VXN can be realized in units of vega. So in this case we will see about getting an edge in the next-day's change in the VXN.

Data
For this post we will use data off VXN (CBOE NASDAQ100 Volatility Index) and QQQ (Powershares NASDAQ100 Index ETF). QQQ daily prices would be needed for realized volatility and MAD. For this example we use End-of-Day values.
Data Range: 1/23/01 - 11/26/12

Once we get the data off Yahoo Finance, the first thing we need to do is get both series on the same page. Then we must match the dates.
What I do is to create a temporary column where it gives a 1 if the dates match for that row, and 0 if not. Then a quick scroll down would let us catch mismatches and delete the unmatched dates.

Once the dates are matched, we could delete the second "Date" column, and sort the data so that the latest data ends at the bottom. As mentioned earlier (Excel Data Analysis 1.0), I then inserted some rows near the top for basic statistics.

We first need the day to day changes of QQQ, here named "dQ" in column D. Again we use logged returns for this, 100* ln[X(t)/X(t-1)]  (why logged returns?). I multiplied it by 100 so we don't need to use the % symbol.

Since my data here starts at row 13, and I've decided to do a 20Day MAD, we will utilize a rolling 20row window off "dQ". We still need to multiply the value by Sqrt(252) for annualization.

1 way to use MAD(20) is to analyze the ratio, VXN/MAD, to estimate whether these options are cheap or expensive. I created "rto.M" in column F for this.

Fundamentally, we already know that as insurance, options are usually traded at a premium so to compensate option sellers for the risk. From the screenshot, we can see that VXN has been above 2x MAD roughly 50% of the time.

Next I added columns for the standard squared realized volatility "RV" and linear forecast "FcRV" explained in an earlier post (Excel Data Analysis 3.2). We end up with this:

Multi-parameter strategy set up

We will need separate sections for longs and shorts. Since the logic/formulas are the same, I will only do the shorts here. The basic idea is to derive signals, and match them with the following day's change in VXN.

Since we have 2 parameters, "rto.M" and "FcRV", we will need a 3rd column for the signals.

(FcRV), here we see if forecast volatility (FcRV) is greater than current RV by at least the threshold in cell K13. I used "0" for threshold so that we get a signal off this filter for every upside forecast. It gives a 1 for signal, blank for no signal.

(rto.M), here we see if the VXN/MAD (rto.M) value is above the threshold value in cell L13 so that we don't enter trades when options are too cheap. I used "1.6", the lower quartile value. It gives a 1 for signal, blank for no signal.

Signals, so basically this checks if the sum of the above signals are 2; if so a trade signal is given, if not it is blank. The "Count" cell in M10 gives the total number of signals here.

Deriving average returns, equity curve

Next we create a column for the future 1Day change in VXN, "dVXN". Note that in row 32, we used data from row33, therefore this data reaches down to 1 day before last, i.e. 2 trading days ago.

In column O then we could estimate our VXN edge, or units of Vega/Trade "Vega/T". We use the if() function, basically if a signal is given, we look at dVXN, if not, it's a blank.

For the equity curve, "Eq.Curve", it adds to its latest value by the Vega/T if a signal is given.

We can see that this edge offers the option trades an average of 0.17Vega / trade, before transaction costs.
With the signal count, it roughly gives a trade every 3 days or so.

I prepare to print the equity curve with the 2-D Line chart. Make sure you also have the date column highlighted, to have the date in the chart. To do this, you can highlight 1 column, then press Ctrl and click the header of the 2nd column. So here is the estimated Vega possibly earned cumulatively off the above signals BEFORE transaction costs.

Historical performance stats

Win%: This cell checks if there's a signal, if so, if it's a winner it gives a 1, if not a 0.

Wins: Here if the signal is a winner, we record it.

Losses: Here if the signal is a loser, we record it.

W/L Ratio: This gives the average Winner/Loser size ratio by taking the absolute value of the (average Winner)/(average Loser)

Let's see the stats

So the numbers are encouraging. However one must keep in mind that these estimates are before transaction costs and considerations of risks around liquidity, execution precision, and etc. Most of all, one must realize that the worst losing trades are never behind, but rather ahead of us in the future. Therefore to apply this logic for actual trading still requires work around effective risk and implementation shortfall management.

Feel free to contact me for questions, advise around excel modeling, trading.