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

Kyle sn

| Reward Discussion

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

Kyle sn

Accepted Answer

Update Backtest

Notebook

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.

Derek Melchin

STAFF ,

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

Kyle sn

458 ,

This is exactly what I was looking for, thanks.

Kyle sn

Update Backtest

Notebook

To unlock posting to the community forums please complete at least 30% of Boot Camp.
You can continue your Boot Camp training progress from the terminal. We hope to see you in the community soon!