I'm trying to find/calculate the constant 30 day VVIX price from the following dataframe:
In this case, we would just insert a new row between the two closest date_diff for the new 30day estimated VVIX value by using the formula included in the picture above.
I know there's another way to approach this, using pandas.DataFrame.interpolate we would insert a np.nan value within the two rows and fill it using an interpolation method (which I'm not sure which one would be the best one to use here).
Data
import pandas as pd
import numpy as np
import urllib
dls = "https://cdn.cboe.com/resources/indices/documents/vixvixtermstructure.xls"
urllib.request.urlretrieve(dls, "test.xls")
df = pd.read_excel("test.xls", header=2)
There's some '.' in the data, I cleaned them up using the following (if there's a better way please let me know!)
df['VVIX'] = np.where(df['VVIX'] == '.', np.nan, df['VVIX'])
df['VVIX'] = df['VVIX'].ffill()
df = df.dropna() #drops first 2 rows containing NaN
df['date_diff'] = df['Expiration_Date'] - df['Date']