Discussion Forum
Back

Combining data sets in research -- Issue with VIX df index vs SPY df index

Hi,

I am trying to do some research using VIX and SPY data but I am having trouble combining the data into a single dataframe, particularly because the time index for VIX includes some sort of time in addition to the date, unlike SPY. I am importing the VIX data like so: 

vix = qb.AddData(CBOE, "VIX", Resolution.Daily).Symbol

which obviously differs from the "AddEquity()" method that I use for SPY so perhaps I should not be mixing methods like that.

What I am curious about is if I should be obtaining VIX data in a different way or if I need to change the multi-index dataframe into a single-index dataframe so that the two data sets use the same index. The latter makes me feel like I will be manipulating data in a way Quantconnect did not intend. Any advice would be appreciated.

EDIT: I cant figure out how to attach the research notebook so below is the code (sorry):

 

 

from QuantConnect.Data.Custom.CBOE import *
import matplotlib.pyplot as plt
import pandas as pd

qb = QuantBook()

vix = qb.AddData(CBOE, "VIX", Resolution.Daily).Symbol
vix_hist = qb.History([vix], 252)

spy = qb.AddEquity("SPY", Resolution.Daily).Symbol
spy_hist = qb.History([spy], 252)

df = qb.History(qb.Securities.Keys, timedelta(days=252), Resolution.Daily)
df['close'].unstack(level=0).tail()
 

Update Backtest



2 Comments
0

The material on this website is provided for informational purposes only and does not constitute an offer to sell, a solicitation to buy, or a recommendation or endorsement for any security or strategy, nor does it constitute an offer to provide investment advisory services by QuantConnect. In addition, the material offers no opinion with respect to the suitability of any security or specific investment. QuantConnect makes no guarantees as to the accuracy or completeness of the views expressed in the website. The views are subject to change, and may have become unreliable for various reasons, including changes in market conditions or economic circumstances. All investments involve risk, including loss of principal. You should consult with an investment professional before making any investment decisions.

Hi Kyle,

The reason the data doesn't align well is because the timestamp for the VIX is one hour ahead of the timestamp for SPY. We can resolve this, and do so with 1 less History call:

First, add the data feeds

vix = qb.AddData(CBOE, "VIX", Resolution.Daily).Symbol
spy = qb.AddEquity("SPY", Resolution.Daily).Symbol

Next, we get the historical closes

vix_close = qb.History([vix], 252).reset_index(level=0).drop(['symbol'], axis=1)['close']
spy_close = qb.History([spy], 252).reset_index(level=0).drop(['symbol'], axis=1)['close']

Now, we change the vix index

vix_close = vix_close.rename(lambda x: x.replace(minute=0))

And finish with concatenating the 2 Series together, dropping any NaN values, and renaming the columns.

df = pd.concat([vix_close, spy_close], axis=1).dropna()
df.columns = ['vix', 'spy']

See the attached research notebook for the full solution.

Best,
Derek

2

The material on this website is provided for informational purposes only and does not constitute an offer to sell, a solicitation to buy, or a recommendation or endorsement for any security or strategy, nor does it constitute an offer to provide investment advisory services by QuantConnect. In addition, the material offers no opinion with respect to the suitability of any security or specific investment. QuantConnect makes no guarantees as to the accuracy or completeness of the views expressed in the website. The views are subject to change, and may have become unreliable for various reasons, including changes in market conditions or economic circumstances. All investments involve risk, including loss of principal. You should consult with an investment professional before making any investment decisions.

This is exactly what I was looking for, thanks.

0
Update Backtest



0

The material on this website is provided for informational purposes only and does not constitute an offer to sell, a solicitation to buy, or a recommendation or endorsement for any security or strategy, nor does it constitute an offer to provide investment advisory services by QuantConnect. In addition, the material offers no opinion with respect to the suitability of any security or specific investment. QuantConnect makes no guarantees as to the accuracy or completeness of the views expressed in the website. The views are subject to change, and may have become unreliable for various reasons, including changes in market conditions or economic circumstances. All investments involve risk, including loss of principal. You should consult with an investment professional before making any investment decisions.

Loading...

This discussion is closed

Discussion Tags