The CBOE posts historical volatility futures quotes, but not in a particularly helpful organization. Each expiration month has a separate spreadsheet, which currently adds up to > 100 different spreadsheets from May 2004 to now. Adding to the difficulties, there are four expiration months in the early years that didn’t exist at all that led to a lot of missing data and a reverse split along the way.
I have created a spreadsheet that integrates all of the data from March 2004 through the most recent quarter completed (see this table for specific dates), into a single master sheet and interpolates/extrapolates missing data for VIX Futures months that did not trade in the 2004 through 2008 timeframe. This spreadsheet makes it much easier to analyze topics like volatility contango/backwardation over time, computing the rolling averages used by almost all the existing volatility ETNs/ ETFs, and looking at term structures. The master spreadsheet for a small subset of dates is shown below.
The spreadsheet includes the required futures data. It is a straightforward task to incorporate data up until the present date. That process is documented in the spreadsheet itself.
A readme sheet with detailed instructions is included with the download. Email ([email protected]) and phone (970-481-7426) support is included with purchase. Go to the bottom portion of this post to buy this spreadsheet.
The default presentation is to compile the settlement prices but the spreadsheet is configurable to generate daily open, high, low, close, volume, open interest and EFP (Exchange For Physical) numbers.
I also generate the monthly settlement prices for VIX futures contracts back to 2004.
This spreadsheet does not include index calculation formulas (e.g., for SPVXSP, SPVXSTR, SPVXMP, SPVXMTR) but it contains all the information, including treasury bill yields required to do those calculation. I do offer for sale a VIX futures spreadsheet that includes those calculations. See this link for more information.
Vance, great site, thanks. I have purchased several products before. Is this file updated to Dec 2019, and if not, could it be, please?
Thanks and regards
Rod
Hi Rod,
I plan to update this file in the next week or so. If you are interested I offer a quarterly update service https://www.sixfigureinvesting.com/2011/02/quarterly-update-service/
Best Regards,
Vance
I know this is old, but have you updated the spreadsheet to be up to date?
Hi Peter, Currently I have it updated with data / expiration dates through March 2016. If you’re interested I can bring it up to the present–although then it’s up to you.
— Vance
Hi Vance,
After perusing your site (very good, btw, so I’ve recommended it to others) I notice that you have some specific spreadsheets that help with backtesting.
What makes sense for me is the one that is applicable to UVXY. Is “3 UVXY Simulation $30.00 SFI ETP backtest UVXY values E1 18Dec14 336.40KB” also updated to today’s data?
Thank you
Hi Peter, I’ve updated the UVXY file on the products tab of my site to be current up to 9-March-2016. If you purchase this I’ll do an update to bring it up to the current date and email it to you.
— vance
Hey Vance, Sorry for the (hopefully final) question: so this product will simulate, albeit very accurately, what UVXY would have closed at each day by providing a table of values back to 2004? I realized my worry about getting “up to date” info was moot as I obviously can get that for free at many sites (e.g. Yahoo). While a graph would be nice to look at, I’m much more interested in a table of values. I want to understand how UVXY would have behaved in period such as 2008 and 2009 (lengthy periods of almost consistent high vol).
TIA
Hi Peter, Yes, product provides simulated daily close since March 2004.
Best regards, vance
Hi Vince,
Great job all around. Just a comment on your spreadsheet regarding columns to interpolate M6 and M7 : For these two you interpolated vols using M4 and M5 but actually M8 is available (VXX2004). VXX2004 has had open interest since Mars 2004.
It would be preferable to use the interpolation formula that takes into account Ti+2 and Ti-1 rather than Ti-1 and Ti-2.
By using M4 and M5 to interpolate M6 and M7 you actually create a “fake contango” or an exaggerated contango effect. VXX is very much in backwardation so your M6 and M7 should actually trade at higher levels than M4 and M5.
I used Ti+2 and Ti-1 to interpolate M6, so M6 = f(M5, M8)
And I used Ti+1 and Ti-1 to interpolate M7, so M7 = f(M6, M8).
where M8 are real traded values.
If you want to check your first line will be
20.53
20.32
20.16
20.14
20.11
20.36 (M6)
20.62 (M7)
20.94 (M8 real point)
Great idea to interpolate VXJ2004 !! i have the same results here.
Hi Alex,
I discovered that the M8 data was available for interpolation after I had done the initial snapshot shown in the post and I forgot to update the graphic. My current results match the ones you posted. It’s great to have someone checking the math. Most people don’t know what the hell you’re doing…
Best Regards,
Vance
Fantastic.
Anyway interpolation is only what it is so there are several ways to go about it. But since you’ve been so diligent I just wanted to point out M8 and this case of having ti-1 and ti+2 repeats A LOT of times up to April 2008…so it could potentially impact your VXZ backtest significantly.
I’ll check the other maths but if you match Barclays you’re golden. And you’ve got the fees calcs right !