Using the Cboe’s VIX futures historic data and interpolations/extrapolations for contracts that were not traded I developed a continuous time series for 7 months of VIX futures settlement values. I then used that data, plus treasury bill data to compute the indexes that underly the popular long and short volatility Exchange Traded Products (ETPs) in the USA.
This product includes two spreadsheets, one that includes the formulas to properly format the VIX futures data and to generate the total returns (TR) and excess returns (ER) volatility indexes such as SPVXSTR, SPVXSP, and SPVXMP. The other spreadsheet included with the product uses those volatility indexes to simulate the prices of popular volatility ETPs (e.g., VXX, 1.5X UVXY, TVIX, XIV (before termination), -0.5X SVXY, ZIV, 2X UVXY, -1X SVXY) starting in March 2004.
The VIX Futures spreadsheet includes the required futures and treasury data March 2004 through the most recent quarter. See this table for specific dates. The spreadsheet is setup such that the user can bring the spreadsheet up to the present date in a straightforward and well-documented process.
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.
The spreadsheet also generates the monthly settlement prices for VIX futures contracts from 2004 through January 2021.
Given the size and complexity of this product a reasonable amount of consulting time is bundled with the package. Please contact me at [email protected] for details.
If you purchase this product you will be directed to paypal where you can pay via your paypal account or a credit card. Please email me at [email protected] if you have problems, questions, or requests.
Greetings. Is there a way to pull live prices of the VIX Futures (all months) into a google sheet or excel?
Getting live prices is pricey, you’d have to contact the CBOE directly for that. The best I’ve found is =GOOGLEFINANCE(“INDEXCBOE:VXIND1”) which gives yesterday’s close of the next to expire future. ind2 and ind3 are available also for the further out months.
— Vance
Thanks Vance, much appreciated. Yes CBOE is @ $900/month, so out of range for me.
Do you know what the difference is between: INDEXCBOE:VXIND1 and INDEXCBOE:VW1VX?
Hi Jonny,
Interesting! I’m pretty sure this is a two minute volume weighted price (VWAP) for the front month VIX future. The Cboe uses this to smooth out pricing a bit, so a single trade that is high or low right at certain time doesn’t give a misleading value for what the future was generally trading at. Cboe quote URL https://www.cboe.com/delayed_quotes/vw1vx Also available for 2vx and 3vx, so 2nd and 3rd month futures. Googlefinance supports this in their gsheets package with =GOOGLEFINANCE(“INDEXCBOE:Vw1vx”)
Best Regards,
Vance
The article linked below asserts that XIV and SVXY would not have survived the 2010 flash crash nor the 2011 European crisis due to intra-day spikes in the VIX futures and the tripping of a termination event:
https://mktstk.com/2014/11/18/these-two-etfs-could-go-bankrupt-in-a-flash/
I was curious if you agree with that based on your simulated data. Of course, if someone is using the futures directly instead of a simulated instrument they avoid that issue but will need to have lots of available margin.
Hi Jony,
Reviewing my simulations it’s clear that XIV would not have terminated in the 18-Aug-2011 event–I show XIV at its worst was “only” down intraday by 22%. The writers of the post you mentioned probably ignored / weren’t aware that both the front and next to front futures need to be in the calculation of XIV’s value. My spreadsheet results are shown here: https://www.sixfigureinvesting.com/wp-content/uploads/2016/07/XIV-European-Crash.jpg
The 6-May-2010 Flash Crash was a much closer thing. My simulation shows a maximum drawdown of -79.3% of the previous close (termination is a possibility if it goes down -80% or more). However my simulation makes some assumptions that could change this number, for example I assume that both sets of futures involved have their low value at the same time. Another significant factor is how the intraday indicative value used to determine termination is calculated. I suspect it uses the midpoint between the bid/ask for its calculation, if so then the drawdown would have been significantly less. In high stress situations like this one the spreads get quite wide. My simulation shows the following data for that day: https://www.sixfigureinvesting.com/wp-content/uploads/2016/07/XIV-Flash-Crash.jpg
Reading the prospectus several other factors emerge:
1. The issuer is not obligated to terminate, it is at their discretion
2. If terminated the liquation is at the closing indicative value, not the value that triggered the event (the close could be higher or lower than the triggering indicative value)
3. The closing indicative value will not be less than zero
These points imply that even if the termination event occurred (which I judge as unlikely given the transient nature of the crash), the issuer would wait until end of day to close out their positions. Otherwise If they sold at the termination point and the market recovers then they would have to cough up a lot of money.
Incorporating this, even if XIV did terminate that day the holders would not have been wiped out, but rather would have experienced a -19% loss (because the market rebounded strongly).
Reviewing the SVXY prospectus there is no 80% termination trigger. The fund can be terminated at the issuer’s discretion, but no criteria are specified. Given that it is unlikely that SVXY would have terminated on 6-May-2010.
Vance