Wednesday, November 28, 2012

Excel Data Analysis 4.1 (Day-of-the-week, OVX)

Here we look at the day to day change in OVX (CBOE Crude Oil ETF Volatility Index) with respect to day of the week. According to academic theory, theta decay progresses smoothly with respect to time; if that is true, then making theta over the weekends would be practically free money. Let's see if that is exactly what most of the USO traders are doing.

Data preparations
OVX daily data is available at wikiposit. I like the way the data is already sorted going from top to bottom. For excel analysis, we download the csv format.

Again, we create space at the top for sample statistics for a feel of the data (Excel Data Analysis 1.0). Then we can create a column for the change in OVX "dOVX", I don't think there's need for logged returns here since implied volatility is already a percentage value. So dOVX is simply the difference between the latest value and the previous day's, i.e. OVX(t) - OVX(t-1).

 and we get this:
So we can see that like VXN, OVX has an upside skew.

Day of the week test

Next we create a column "WeekDay" in D with the weekday() formula. Keep in mind the default value for Sunday is 1, so to keep the values more "user friendly" I had subtracted each cell by 1.

Then we can use the following columns (E - I) for each day of the week. We will label the headers of these columns by the day of the week, 1 - 5. Then we could enter the formula in a single cell, and copy/paste it into the rest of the cells all the way down.

Logic: we check if the current row's weekday matches the column header, if so, we record that day's dOVX, if not, the cell leaves it blank. The $D14 means as we copy/paste this, it'll keep this value in column D, and for E$1, it keeps these values in the top row (column headers) as we copy/paste them.

Once we copy/paste that cell all the way through to I741, we end up with this

So we can see that there is a pretty significant weekend effect here, that traders tend to sell on Fridays hoping to get that "free theta", and buying them back on Mondays.

A way to potentially exploit this would be to do the opposite, i.e. buy vol near the end of Fridays, and sell on Mondays. Is this an edge that is likely to persist into the future? I'd think so, since blind faith in academic finance is relatively strong in today's cultures. However, whether this edge would likely overcome transaction costs and other implementation shortfalls would require more analysis.

0 Reflections: