def iv_at_the_money(dt_date, dt_yesterday, id_underlying, df_srf):
optionMetrics = dbt.OptionMetrics
query_sro = admin.session_metrics().query(optionMetrics.dt_date, optionMetrics.id_instrument,
optionMetrics.id_underlying,
optionMetrics.amt_strike,
optionMetrics.cd_option_type, optionMetrics.pct_implied_vol) \
.filter(optionMetrics.id_underlying == id_underlying).filter(optionMetrics.dt_date >= dt_yesterday) \
.filter(optionMetrics.dt_date <= dt_date)
df_sro = pd.read_sql(query_sro.statement, query_sro.session.bind)
dates = df_sro['dt_date'].unique()
dict_iv_call = {}
dict_iv_put = {}
for date in dates:
df0 = df_sro[df_sro['dt_date'] == date]
df1 = df0[(df0['cd_option_type'] == 'call')]
amt_settle = \
df_srf[(df_srf['dt_date'] == date) & (df_srf['id_instrument'] == id_underlying)]['amt_settlement'].values[0]
df1['diff'] = abs(df1['amt_strike'] - amt_settle)
df1 = df1.sort_values(by='diff', ascending=True)
k = df1.iloc[0]['amt_strike']
iv_call = df1.iloc[0]['pct_implied_vol'] * 100
iv_put = df0[(df0['cd_option_type'] == 'put') & (
df0['amt_strike'] == k)]['pct_implied_vol'].values[0] * 100
dict_iv_call.update({date: iv_call})
dict_iv_put.update({date: iv_put})
return dict_iv_call, dict_iv_put
def get_pciv_ratio(end_date):
table = admin.table_option_iv_by_moneyness()
query1 = admin.session_metrics().query(table.c.dt_date, table.c.pct_implies_vol) \
.filter(table.c.dt_date <= end_date).filter(table.c.id_underlying == 'index_50etf') \
.filter(table.c.cd_option_type == 'call').filter(table.c.cd_mdt == 'hp_8_1st')
df = pd.read_sql(query1.statement, query1.session.bind)
df = df.rename(columns={'pct_implies_vol': 'iv_call'})
query2 = admin.session_metrics().query(table.c.dt_date, table.c.pct_implies_vol) \
.filter(table.c.dt_date <= end_date).filter(table.c.id_underlying == 'index_50etf') \
.filter(table.c.cd_option_type == 'put').filter(table.c.cd_mdt == 'hp_8_1st')
df2 = pd.read_sql(query2.statement, query2.session.bind)
df['iv_put'] = df2['pct_implies_vol']
df['amt_close'] = (df['iv_put'] - df['iv_call']) / (
(df['iv_put'] + df['iv_call']) / 2)
# df['amt_close'] = (df['iv_put']-df['iv_call'])
return df
def get_index_ma(start_date, end_date, id_index):
Index_mkt = admin.table_moving_average()
query_etf = admin.session_metrics().query(Index_mkt.c.dt_date, Index_mkt.c.amt_close,
Index_mkt.c.id_instrument, Index_mkt.c.amt_value, Index_mkt.c.cd_period) \
.filter(Index_mkt.c.dt_date >= start_date).filter(Index_mkt.c.dt_date <= end_date) \
.filter(Index_mkt.c.id_instrument == id_index)
df_index = pd.read_sql(query_etf.statement, query_etf.session.bind)
return df_index
def df_iv_at_the_money(dt_date, dt_start, namecode, df_srf):
optionMetrics = dbt.OptionMetrics
query_sro = admin.session_metrics().query(optionMetrics.dt_date, optionMetrics.id_instrument,
optionMetrics.id_underlying,
optionMetrics.amt_strike,
optionMetrics.cd_option_type, optionMetrics.pct_implied_vol) \
.filter(optionMetrics.dt_date >= dt_start) \
.filter(optionMetrics.dt_date <= dt_date)\
.filter(optionMetrics.name_code == namecode)
df_sro = pd.read_sql(query_sro.statement, query_sro.session.bind)
dates = df_sro['dt_date'].unique()
dict_iv_call = []
dict_iv_put = []
for date in dates:
df_volume_groupby = get_volume_groupby_id_option(admin.table_options_mktdata(), namecode, dt_start=date,
dt_end=date). \
sort_values(by='total_trading_volume', ascending=False).reset_index(drop=True)
id_c1 = df_volume_groupby.loc[0, 'id_underlying']
df0 = df_sro[(df_sro['dt_date'] == date)
& (df_sro['id_underlying'] == id_c1)]
df1 = df0[(df0['cd_option_type'] == 'call')]
amt_settle = \
df_srf[(df_srf['dt_date'] == date) & (df_srf['id_instrument'] == id_c1)]['amt_settlement'].values[0]
df1['diff'] = abs(df1['amt_strike'] - amt_settle)
df1 = df1.sort_values(by='diff', ascending=True)
k = df1.iloc[0]['amt_strike']
iv_call_c1 = df1.iloc[0]['pct_implied_vol'] * 100
iv_put_c1 = df0[(df0['cd_option_type'] == 'put') & (
df0['amt_strike'] == k)]['pct_implied_vol'].values[0] * 100
id_c2 = df_volume_groupby.loc[1, 'id_underlying']
df0 = df_sro[(df_sro['dt_date'] == date)
& (df_sro['id_underlying'] == id_c2)]
df1 = df0[(df0['cd_option_type'] == 'call')]
amt_settle = \
df_srf[(df_srf['dt_date'] == date) & (df_srf['id_instrument'] == id_c1)]['amt_settlement'].values[0]
df1['diff'] = abs(df1['amt_strike'] - amt_settle)
df1 = df1.sort_values(by='diff', ascending=True)
k = df1.iloc[0]['amt_strike']
iv_call_c2 = df1.iloc[0]['pct_implied_vol'] * 100
iv_put_c2 = df0[(df0['cd_option_type'] == 'put') & (
df0['amt_strike'] == k)]['pct_implied_vol'].values[0] * 100
dict_iv_call.append({
'dt_date': date,
'iv_c1': iv_call_c1,
'iv_c2': iv_call_c2,
'underlying_c1': id_c1,
'underlying_c2': id_c2,
})
dict_iv_put.append({
'dt_date': date,
'iv_c1': iv_put_c1,
'iv_c2': iv_put_c2,
'underlying_c1': id_c1,
'underlying_c2': id_c2,
})
df_call = pd.DataFrame(dict_iv_call)
df_put = pd.DataFrame(dict_iv_put)
return df_call, df_put
def get_iv_by_moneyness(start_date,
end_date,
name_code,
nbr_moneyness=0,
cd_mdt_selection='hp_8_1st',
cd_atm_criterion='nearest_strike'):
table_iv = admin.table_implied_volatilities()
query = admin.session_metrics().query(table_iv).filter(table_iv.c.dt_date >= start_date) \
.filter(table_iv.c.dt_date <= end_date) \
.filter(table_iv.c.name_code == name_code) \
.filter(table_iv.c.nbr_moneyness == nbr_moneyness) \
.filter(table_iv.c.cd_mdt_selection == cd_mdt_selection) \
.filter(table_iv.c.cd_atm_criterion == cd_atm_criterion)
df = pd.read_sql(query.statement, query.session.bind)
return df
def df_iv_at_the_money_1(dt_date, dt_start, id_c1, df_srf):
optionMetrics = dbt.OptionMetrics
query_sro = admin.session_metrics().query(optionMetrics.dt_date, optionMetrics.id_instrument,
optionMetrics.id_underlying,
optionMetrics.amt_strike,
optionMetrics.cd_option_type, optionMetrics.pct_implied_vol) \
.filter(optionMetrics.dt_date >= dt_start) \
.filter(optionMetrics.dt_date <= dt_date)\
.filter(optionMetrics.id_underlying == id_c1)
df_sro = pd.read_sql(query_sro.statement, query_sro.session.bind)
dates = df_sro['dt_date'].unique()
dict_iv_call = []
dict_iv_put = []
for date in dates:
df0 = df_sro[df_sro['dt_date'] == date]
df1 = df0[(df0['cd_option_type'] == 'call')]
amt_settle = \
df_srf[(df_srf['dt_date'] == date) & (df_srf['id_instrument'] == id_c1)]['amt_settlement'].values[0]
df1['diff'] = abs(df1['amt_strike'] - amt_settle)
df1 = df1.sort_values(by='diff', ascending=True)
k = df1.iloc[0]['amt_strike']
iv_call = df1.iloc[0]['pct_implied_vol'] * 100
iv_put = df0[(df0['cd_option_type'] == 'put') & (
df0['amt_strike'] == k)]['pct_implied_vol'].values[0] * 100
dict_iv_call.append({
'dt_date': date,
'iv': iv_call,
'id_underlying': id_c1
})
dict_iv_put.append({
'dt_date': date,
'iv': iv_put,
'id_underlying': id_c1
})
df_call = pd.DataFrame(dict_iv_call)
df_put = pd.DataFrame(dict_iv_put)
return df_call, df_put
def hist_atm_ivs(evalDate, dt_last_week, w, nameCode, exchangeCode, df_future):
optionMetrics = dbt.OptionMetrics
options_table = dbt.Options
query_sro = admin.session_metrics().query(optionMetrics.dt_date,optionMetrics.id_instrument,optionMetrics.id_underlying,
optionMetrics.amt_strike,
optionMetrics.cd_option_type,optionMetrics.pct_implied_vol)\
.filter(optionMetrics.name_code == nameCode).filter(optionMetrics.dt_date >= dt_last_week)
query_mdt = admin.session_mktdata().query(options_table.id_instrument,options_table.id_underlying,options_table.dt_maturity)\
.filter(options_table.cd_exchange == exchangeCode)
df_srf = df_future
df_sro = pd.read_sql(query_sro.statement, query_sro.session.bind)
df_mdt = pd.read_sql(query_mdt.statement, query_mdt.session.bind)
df_iv_atm = pd.DataFrame()
dates = df_sro['dt_date'].unique()
for date in dates:
df0 = df_sro[df_sro['dt_date'] == date]
underlyings = df0['id_underlying'].unique()
months = []
for u in underlyings:
months.append(u[-4:])
months = sorted(months)
core = ['01', '05', '09']
underlyings_core = []
for m in months:
if m[-2:] in core:
underlyings_core.append(m)
core.remove(m[-2:])
for underlying in underlyings:
if underlying[-4:] not in underlyings_core: continue
df1 = df0[df0['cd_option_type'] == 'call']
df2 = df1[df1['id_underlying'] == underlying]
id_instrument = df2['id_instrument'].values[0]
amt_settle = df_srf[(df_srf['dt_date'] == date)
& (df_srf['id_instrument'] == underlying
)]['amt_settlement'].values[0]
try:
mdt = df_mdt[df_mdt['id_instrument'] ==
id_instrument]['dt_maturity'].values[0]
except:
m1 = int(underlying[-2:])
y1 = int(str(20) + underlying[-4:-2])
dt1 = datetime.date(y1, m1, 1)
mdt = w.tdaysoffset(-5, dt1, "Period=D").Data[0][0].date()
ttm = (mdt - date).days / 365.0
df2['diff'] = abs(df2['amt_strike'] - amt_settle)
df2 = df2.sort_values(by='diff', ascending=True)
df_atm = df2[0:1]
df_atm['ttm'] = ttm
df_iv_atm = df_iv_atm.append(df_atm, ignore_index=True)
df_iv_results = pd.DataFrame()
dates = df_sro['dt_date'].unique()
for idx_dt, date in enumerate(dates):
df0 = df_iv_atm[df_iv_atm['dt_date'] == date].reset_index()
df_iv_results.loc[idx_dt, 'dt_date'] = date
for i in range(2):
iv = df0.loc[i, 'pct_implied_vol']
if iv == 0.0: iv = np.nan
df_iv_results.loc[idx_dt, 'contract-' + str(i + 1)] = iv * 100
df_iv_results = df_iv_results.sort_values(by='dt_date', ascending=False)
df_iv_results = df_iv_results.dropna()
# core_ivs = df_iv_results['contract-1'].tolist()
# current_iv = core_ivs[0]
# p_75 = np.percentile(core_ivs,75)
# p_25 = np.percentile(core_ivs,25)
# p_mid = np.percentile(core_ivs,50)
# df_iv_results.loc[:,'75分位数（主力合约）'] = p_75
# df_iv_results.loc[:,'25分位数（主力合约）'] = p_25
# df_iv_results.loc[:,'中位数（主力合约）'] = p_mid
# print('hist atm ivs:')
# print('p_75 : ',p_75)
# print('p_25 : ',p_25)
# print('p_mid : ',p_mid)
# current_iv_pct = 0
# diff_min = 10000.0
# for i in range(0,100):
# p = np.percentile(core_ivs,i)
# diff = abs(p-current_iv)
# if diff < diff_min :
# diff_min = diff
# current_iv_pct = p
# print(current_iv_pct)
# f1, ax1 = plt.subplots()
#
# pu.plot_line(ax1, 0, df_iv_results['dt_date'], core_ivs, '隐含波动率', '日期', '(%)')
# pu.plot_line(ax1, 1, df_iv_results['dt_date'], [p_75]*len(core_ivs), '75分位数', '日期', '(%)')
# pu.plot_line(ax1, 2, df_iv_results['dt_date'], [p_25]*len(core_ivs), '25分位数', '日期', '(%)')
# pu.plot_line(ax1, 3, df_iv_results['dt_date'], [p_mid]*len(core_ivs), '中位数', '日期', '(%)')
#
# ax1.legend(bbox_to_anchor=(0., 1.02, 1., .202), loc=3,
# ncol=3, mode="expand", borderaxespad=0.,frameon=False)
# f1.set_size_inches((12,6))
# f1.savefig('../save_figure/'+nameCode+'_hist_atm_ivs_' + str(evalDate) + '.png', dpi=300, format='png')
df_iv_results.to_csv('../data/' + nameCode + '_hist_atm_ivs.csv')
# beg_date = datetime.date(2014, 6, 1)
date = datetime.date(2015, 1, 1)
index_mktdata = admin.table_indexes_mktdata()
ma_metrics = admin.table_moving_average()
# query_mkt = admin.session_mktdata().query(index_mktdata.c.dt_date,index_mktdata.c.id_instrument,
# index_mktdata.c.amt_close) \
# .filter(index_mktdata.c.datasource == 'wind')\
# .filter(index_mktdata.c.id_instrument == 'index_50etf')\
# .filter(index_mktdata.c.dt_date >= date)
# df_dataset = pd.read_sql_query(query_mkt.statement,query_mkt.session.bind)
query_mkt = admin.session_metrics().query(ma_metrics.c.dt_date,ma_metrics.c.id_instrument,
ma_metrics.c.amt_close) \
.filter(ma_metrics.c.cd_period == 'ma_3')\
.filter(ma_metrics.c.id_instrument == 'index_cvix')
# .filter(ma_metrics.c.dt_date >= date)
df_dataset = pd.read_sql_query(query_mkt.statement, query_mkt.session.bind)
print('s')
# xl = pd.ExcelFile('../data/VIX_daily.xlsx')
# df_dataset = xl.parse("Sheet1", header=None)
# df_dataset['dt_date']= df_dataset[0]
# df_dataset['amt_close']= df_dataset[1]
# df_dataset = df_dataset[['dt_date','amt_close']]
# df_dataset = df_dataset[df_dataset['dt_date']>datetime.date(2017,6,1)]
# df_dataset['id_instrument'] = 'index_cvix'
for p in [0.25, 0.5, 0.75]:
# ma = moving_average(df_dataset,n)