def pcr(dt_start, dt_end, name_code, df_res):
optionMkt = admin.table_options_mktdata()
futureMkt = admin.table_futures_mktdata()
query_pcr = admin.session_mktdata().query(optionMkt.c.dt_date, optionMkt.c.cd_option_type,
optionMkt.c.id_underlying,
func.sum(optionMkt.c.amt_holding_volume).label('total_holding_volume'),
func.sum(optionMkt.c.amt_trading_volume).label('total_trading_volume')
) \
.filter(optionMkt.c.dt_date >= dt_start) \
.filter(optionMkt.c.dt_date <= dt_end) \
.filter(optionMkt.c.name_code == name_code) \
.group_by(optionMkt.c.cd_option_type, optionMkt.c.dt_date, optionMkt.c.id_underlying)
query_srf = admin.session_mktdata().query(futureMkt.c.dt_date, futureMkt.c.id_instrument,
futureMkt.c.amt_close, futureMkt.c.amt_trading_volume,
futureMkt.c.amt_settlement) \
.filter(futureMkt.c.dt_date >= dt_start) \
.filter(futureMkt.c.dt_date <= dt_end) \
.filter(futureMkt.c.name_code == name_code) \
.filter(futureMkt.c.flag_night != 1)
df_pcr = pd.read_sql(query_pcr.statement, query_pcr.session.bind)
df_srf = pd.read_sql(query_srf.statement, query_srf.session.bind)
# 按期权合约持仓量最大选取主力合约
df = df_pcr[df_pcr.groupby(['dt_date', 'cd_option_type'])['total_holding_volume'].transform(max) == df_pcr[
'total_holding_volume']]
df_call = df[df['cd_option_type'] == 'call'].reset_index()
df_put = df[df['cd_option_type'] == 'put'].reset_index()
pc_ratio = []
for idx, row in df_call.iterrows():
row_put = df_put[df_put['dt_date'] == row['dt_date']]
pcr_trading = row_put['total_trading_volume'].values[0] / row['total_trading_volume']
pcr_holding = row_put['total_holding_volume'].values[0] / row['total_holding_volume']
pc_ratio.append({'dt_date': row['dt_date'],
'tv_c': row['total_trading_volume'],
'tv_p': row_put['total_trading_volume'].values[0],
'hv_c': row['total_holding_volume'],
'hv_p': row_put['total_holding_volume'].values[0],
'tv_pcr': pcr_trading,
'hv_pcr': pcr_holding,
'id_instrument': row['id_underlying'],
})
df_pcr = pd.DataFrame(pc_ratio)
df_pcr = pd.merge(df_pcr, df_srf[['dt_date', 'id_instrument', 'amt_settlement']], how='left',
on=['dt_date', 'id_instrument'],
suffixes=['', '_r'])
df_pcr = df_pcr.sort_values(by='dt_date', ascending=False).reset_index(drop=True)
df_res.loc[:, 'A:date'] = df_pcr['dt_date']
df_res.loc[:, 'B:tv_c'] = df_pcr['tv_c']
df_res.loc[:, 'C:tv_p'] = df_pcr['tv_p']
df_res.loc[:, 'D'] = None
df_res.loc[:, 'E:date'] = df_pcr['dt_date']
df_res.loc[:, 'F:hv_c'] = df_pcr['hv_c']
df_res.loc[:, 'G:hv_p'] = df_pcr['hv_p']
df_res.loc[:, 'H'] = None
df_res.loc[:, 'I:date'] = df_pcr['dt_date']
df_res.loc[:, 'J:tv_pcr'] = df_pcr['tv_pcr']
df_res.loc[:, 'K:hv_pcr'] = df_pcr['hv_pcr']
df_res.loc[:, 'L:amt_settlement'] = df_pcr['amt_settlement']
df_res.loc[:, 'M'] = None
return df_res
def get_50option_intraday(start_date, end_date):
OptionIntra = admin.table_option_mktdata_intraday_gc()
query = admin.session_intraday().query(OptionIntra.c.dt_datetime,
OptionIntra.c.dt_date,
OptionIntra.c.id_instrument,
OptionIntra.c.amt_close,
OptionIntra.c.amt_trading_volume,
OptionIntra.c.amt_trading_value) \
.filter(OptionIntra.c.dt_date >= start_date).filter(OptionIntra.c.dt_date <= end_date)
df = pd.read_sql(query.statement, query.session.bind)
IndexIntra = admin.table_index_mktdata_intraday()
query1 = admin.session_intraday().query(IndexIntra.c.dt_datetime,
IndexIntra.c.dt_date,
IndexIntra.c.id_instrument,
IndexIntra.c.amt_close)\
.filter(IndexIntra.c.dt_date >= start_date).filter(IndexIntra.c.dt_date <= end_date)\
.filter(IndexIntra.c.id_instrument == c.Util.STR_INDEX_50ETF)
df_etf = pd.read_sql(query1.statement, query1.session.bind)
df_etf = df_etf[[c.Util.DT_DATETIME,c.Util.ID_INSTRUMENT, c.Util.AMT_CLOSE]]\
.rename(columns={c.Util.AMT_CLOSE: c.Util.AMT_UNDERLYING_CLOSE,c.Util.ID_INSTRUMENT:c.Util.ID_UNDERLYING})
df_option_metrics = df.join(df_etf.set_index(c.Util.DT_DATETIME),
how='left',
on=c.Util.DT_DATETIME)
options = dbt.Options
query_option = admin.session_mktdata().query(options.id_instrument, options.cd_option_type,
options.amt_strike, options.name_contract_month,
options.dt_maturity, options.nbr_multiplier) \
.filter(and_(options.dt_listed <= end_date, options.dt_maturity >= start_date))
df_contract = pd.read_sql(query_option.statement,
query_option.session.bind)
df_option_metrics = df_option_metrics.join(df_contract.set_index(
c.Util.ID_INSTRUMENT),
how='left',
on=c.Util.ID_INSTRUMENT)
return df_option_metrics
def get_50etf_mktdata(start_date, end_date):
Index_mkt = dbt.IndexMkt
query_etf = admin.session_mktdata().query(Index_mkt.dt_date, Index_mkt.amt_close, Index_mkt.id_instrument) \
.filter(Index_mkt.dt_date >= start_date).filter(Index_mkt.dt_date <= end_date) \
.filter(Index_mkt.id_instrument == 'index_50etf')
df = pd.read_sql(query_etf.statement, query_etf.session.bind)
return df
def get_future_c1_by_option_daily(start_date, end_date, name_code,
min_holding):
table_option_contracts = admin.table_option_contracts()
query = admin.session_mktdata().query(
table_option_contracts.c.id_underlying,
table_option_contracts.c.dt_maturity)
df_option_maturity = pd.read_sql(query.statement,
query.session.bind).drop_duplicates(
c.Util.DT_MATURITY)
for id_underlying in c.OptionFilter.dict_maturities.keys():
if id_underlying not in df_option_maturity[c.Util.ID_UNDERLYING]:
df_option_maturity = df_option_maturity.append(
{
c.Util.ID_UNDERLYING:
id_underlying,
c.Util.DT_MATURITY:
c.OptionFilter.dict_maturities[id_underlying]
},
ignore_index=True)
df_option_maturity['is_core'] = df_option_maturity[
c.Util.ID_UNDERLYING].apply(lambda x: True
if (x[-2:] in c.Util.MAIN_CONTRACT_159) and
(x.split('_')[0] == name_code) else False)
df_option_maturity = df_option_maturity[df_option_maturity['is_core']]
df_option_maturity[c.Util.DT_MATURITY] = df_option_maturity[
c.Util.DT_MATURITY].apply(
lambda x: x - datetime.timedelta(days=min_holding))
df_future = get_gc_future_mktdata(start_date, end_date, name_code)
df_future['id_core'] = df_future[c.Util.DT_DATE].apply(
lambda x: fun_get_c1_by_option(x, df_option_maturity))
df_future = df_future[df_future[c.Util.ID_INSTRUMENT] ==
df_future['id_core']].reset_index(drop=True)
return df_future
def get_50option_minute_with_underlying(start_date, end_date):
OptionIntra = admin.table_option_mktdata_intraday()
query1 = admin.session_intraday().query(OptionIntra.c.dt_datetime,
OptionIntra.c.dt_date,
OptionIntra.c.id_instrument,
OptionIntra.c.amt_close,
OptionIntra.c.amt_trading_volume,
OptionIntra.c.amt_trading_value) \
.filter(OptionIntra.c.dt_date >= start_date).filter(OptionIntra.c.dt_date <= end_date)
df_option = pd.read_sql(query1.statement, query1.session.bind)
IndexIntra = admin.table_index_mktdata_intraday()
query = admin.session_intraday().query(IndexIntra.c.dt_datetime,
IndexIntra.c.amt_close) \
.filter(IndexIntra.c.dt_date >= start_date) \
.filter(IndexIntra.c.dt_date <= end_date) \
.filter(IndexIntra.c.id_instrument == 'index_50etf')
df_index = pd.read_sql(query.statement, query.session.bind)
options = dbt.Options
query_option = admin.session_mktdata().query(options.id_instrument, options.cd_option_type, options.amt_strike,
options.dt_maturity, options.nbr_multiplier) \
.filter(and_(options.dt_listed <= end_date, options.dt_maturity >= start_date))
df_index = df_index.rename(
columns={'amt_close': c.Util.AMT_UNDERLYING_CLOSE})
df_option = df_option.join(df_index.set_index('dt_datetime'),
how='left',
on='dt_datetime')
df_contract = pd.read_sql(query_option.statement,
query_option.session.bind)
df_option = df_option.join(df_contract.set_index('id_instrument'),
how='left',
on='id_instrument')
return df_option
def get_index_mktdata(start_date, end_date, id_index):
Index_mkt = admin.table_indexes_mktdata()
query_etf = admin.session_mktdata().query(Index_mkt.c.dt_date, Index_mkt.c.amt_close, Index_mkt.c.amt_open,
Index_mkt.c.id_instrument, Index_mkt.c.amt_high, Index_mkt.c.amt_low) \
.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 get_mktdata_future(table_future, id_instrument, dt_start, dt_end):
query = admin.session_mktdata().query(table_future.c.dt_date, table_future.c.id_instrument,
table_future.c.amt_close, table_future.c.amt_trading_volume,
table_future.c.amt_settlement) \
.filter(table_future.c.dt_date >= dt_start).filter(table_future.c.dt_date <= dt_end) \
.filter(table_future.c.id_instrument == id_instrument) \
.filter(table_future.c.flag_night != 1)
df = pd.read_sql(query.statement, query.session.bind)
return df
def get_volume_groupby_id_future(table_future, namecode, dt_start, dt_end):
query = admin.session_mktdata().query(table_future.c.dt_date, table_future.c.id_instrument,
func.sum(table_future.c.amt_holding_volume).label('total_holding_volume'),
func.sum(table_future.c.amt_trading_volume).label('total_trading_volume')
) \
.filter(table_future.c.dt_date >= dt_start).filter(table_future.c.dt_date <= dt_end) \
.filter(table_future.c.name_code == namecode) \
.group_by(table_future.c.dt_date, table_future.c.id_instrument)
df = pd.read_sql(query.statement, query.session.bind)
return df
def get_mktdata_future_daily(start_date, end_date, name_code):
table_cf = admin.table_futures_mktdata()
table_contracts = admin.table_future_contracts()
query = admin.session_mktdata().query(table_cf.c.dt_date, table_cf.c.id_instrument,
table_cf.c.amt_open, table_cf.c.amt_close, table_cf.c.amt_trading_volume,
table_cf.c.amt_trading_value,table_cf.c.amt_trading_value). \
filter((table_cf.c.dt_date >= start_date) & (table_cf.c.dt_date <= end_date)). \
filter(table_cf.c.name_code == name_code)
df = pd.read_sql(query.statement, query.session.bind)
df = df[df['id_instrument'].str.contains("_")]
query_contracts = admin.session_mktdata().query(table_contracts.c.id_instrument, table_contracts.c.dt_maturity) \
.filter(table_contracts.c.name_code == name_code.upper())
df_contracts = pd.read_sql(query_contracts.statement,
query_contracts.session.bind)
df_contracts.loc[:, c.Util.ID_INSTRUMENT] = df_contracts[
c.Util.ID_INSTRUMENT].apply(lambda x: x.lower())
df = df.join(df_contracts.set_index(c.Util.ID_INSTRUMENT),
on=c.Util.ID_INSTRUMENT,
how='left')
return df
def get_volume_option(table_option, id_underlying, dt_start, dt_end):
query = admin.session_mktdata().query(table_option.c.dt_date, table_option.c.id_underlying,
table_option.c.amt_strike, table_option.c.cd_option_type,
func.sum(table_option.c.amt_holding_volume).label('total_holding_volume'),
func.sum(table_option.c.amt_trading_volume).label('total_trading_volume')
) \
.filter(table_option.c.dt_date >= dt_start).filter(table_option.c.dt_date <= dt_end) \
.filter(table_option.c.id_underlying == id_underlying) \
.group_by(table_option.c.dt_date, table_option.c.amt_strike, table_option.c.cd_option_type)
df = pd.read_sql(query.statement, query.session.bind)
return df
def get_comoption_mktdata(start_date, end_date, name_code):
Future_mkt = dbt.FutureMkt
Option_mkt = dbt.OptionMkt
options = dbt.Options
query_mkt = admin.session_mktdata(). \
query(Option_mkt.dt_date, Option_mkt.id_instrument, Option_mkt.id_underlying,
Option_mkt.code_instrument, Option_mkt.amt_close, Option_mkt.amt_open,
Option_mkt.amt_settlement,
Option_mkt.amt_last_settlement, Option_mkt.amt_trading_volume,
Option_mkt.pct_implied_vol, Option_mkt.amt_holding_volume,
Option_mkt.amt_trading_volume,
) \
.filter(Option_mkt.dt_date >= start_date).filter(Option_mkt.dt_date <= end_date) \
.filter(Option_mkt.name_code == name_code).filter(Option_mkt.flag_night != 1)
query_option = admin.session_mktdata(). \
query(options.id_instrument, options.cd_option_type, options.amt_strike, options.name_contract_month,
options.dt_maturity, options.nbr_multiplier) \
.filter(and_(options.dt_listed <= end_date, options.dt_maturity >= start_date))
query_srf = admin.session_mktdata(). \
query(Future_mkt.dt_date,
Future_mkt.id_instrument.label(c.Util.ID_UNDERLYING),
Future_mkt.amt_settlement.label(c.Util.AMT_UNDERLYING_CLOSE),
Future_mkt.amt_open.label(c.Util.AMT_UNDERLYING_OPEN_PRICE)) \
.filter(Future_mkt.dt_date >= start_date).filter(Future_mkt.dt_date <= end_date) \
.filter(Future_mkt.name_code == name_code).filter(Future_mkt.flag_night != 1)
df_srf = pd.read_sql(query_srf.statement, query_srf.session.bind)
df_mkt = pd.read_sql(query_mkt.statement, query_mkt.session.bind)
df_contract = pd.read_sql(query_option.statement,
query_option.session.bind)
df_option = df_mkt.join(df_contract.set_index('id_instrument'),
how='left',
on='id_instrument')
df_option_metrics = pd.merge(df_option,
df_srf,
how='left',
on=['dt_date', 'id_underlying'],
suffixes=['', '_r'])
return df_option_metrics
def get_mktdata_future_c1_daily(start_date, end_date, name_code):
table_cf = admin.table_futures_mktdata()
query = admin.session_mktdata().query(table_cf.c.dt_date, table_cf.c.id_instrument,
table_cf.c.amt_open, table_cf.c.amt_close, table_cf.c.amt_high,
table_cf.c.amt_low,table_cf.c.amt_holding_volume,
table_cf.c.amt_trading_volume,table_cf.c.amt_trading_value). \
filter((table_cf.c.dt_date >= start_date) & (table_cf.c.dt_date <= end_date)). \
filter(table_cf.c.name_code == name_code).filter(table_cf.c.flag_night != 1)
df = pd.read_sql(query.statement, query.session.bind)
df = df[df['id_instrument'].str.contains("_")]
df = FutureUtil.get_futures_daily_c1(df)
return df
def get_mktdata_future_c1(start_date, end_date, name_code):
table_f = admin.table_futures_mktdata()
query = admin.session_mktdata().query(table_f.c.dt_date, table_f.c.id_instrument,
table_f.c.amt_close, table_f.c.amt_trading_volume). \
filter((table_f.c.dt_date >= start_date) & (table_f.c.dt_date <= end_date)). \
filter(table_f.c.name_code == name_code)
df = pd.read_sql(query.statement, query.session.bind)
df = df[df['id_instrument'].str.contains("_")]
df = df.sort_values(by=['dt_date', 'amt_trading_volume'], ascending=False)
df_rs = df.drop_duplicates(subset=['dt_date']).sort_values(
by='dt_date', ascending=True).reset_index(drop=True)
return df_rs
def get_50option_mktdata(start_date, end_date):
Index_mkt = dbt.IndexMkt
Option_mkt = dbt.OptionMkt
options = dbt.Options
util = c.Util
query_mkt = admin.session_mktdata().query(Option_mkt.dt_date, Option_mkt.id_instrument,
Option_mkt.code_instrument,
Option_mkt.amt_open,
Option_mkt.amt_close, Option_mkt.amt_settlement,
Option_mkt.amt_last_settlement,
Option_mkt.amt_trading_volume, Option_mkt.amt_holding_volume,
Option_mkt.pct_implied_vol
) \
.filter(Option_mkt.dt_date >= start_date).filter(Option_mkt.dt_date <= end_date) \
.filter(Option_mkt.datasource == 'wind').filter(Option_mkt.name_code == '50etf')
query_option = admin.session_mktdata().query(options.id_instrument, options.cd_option_type,
options.amt_strike, options.name_contract_month,
options.dt_maturity, options.nbr_multiplier) \
.filter(and_(options.dt_listed <= end_date, options.dt_maturity >= start_date))
query_etf = admin.session_mktdata().query(Index_mkt.dt_date, Index_mkt.amt_close, Index_mkt.amt_open,
Index_mkt.id_instrument.label(util.ID_UNDERLYING)) \
.filter(Index_mkt.dt_date >= start_date).filter(Index_mkt.dt_date <= end_date) \
.filter(Index_mkt.id_instrument == 'index_50etf')
df_mkt = pd.read_sql(query_mkt.statement, query_mkt.session.bind)
df_contract = pd.read_sql(query_option.statement,
query_option.session.bind)
df_50etf = pd.read_sql(query_etf.statement, query_etf.session.bind).rename(
columns={
'amt_close': util.AMT_UNDERLYING_CLOSE,
'amt_open': util.AMT_UNDERLYING_OPEN_PRICE
})
df_option = df_mkt.join(df_contract.set_index('id_instrument'),
how='left',
on='id_instrument')
df_option_metrics = df_option.join(df_50etf.set_index('dt_date'),
how='left',
on='dt_date')
return df_option_metrics
def get_future_mktdata(start_date, end_date, name_code):
Futures_mkt = dbt.FutureMkt
Futures = dbt.Futures
query_mkt = admin.session_mktdata().query(Futures_mkt.dt_date, Futures_mkt.id_instrument, Futures_mkt.name_code,
Futures_mkt.amt_close, Futures_mkt.amt_trading_volume,Futures_mkt.amt_trading_value,
Futures_mkt.amt_settlement, Futures_mkt.amt_last_close,
Futures_mkt.amt_last_settlement, Futures_mkt.amt_open,
Futures_mkt.amt_high, Futures_mkt.amt_low) \
.filter(Futures_mkt.dt_date >= start_date) \
.filter(Futures_mkt.dt_date <= end_date) \
.filter(Futures_mkt.name_code == name_code) \
.filter(Futures_mkt.flag_night != 1)
query_c = admin.session_mktdata().query(Futures.dt_maturity, Futures.id_instrument) \
.filter(Futures.name_code == name_code)
df_mkt = pd.read_sql(query_mkt.statement, query_mkt.session.bind)
df_c = pd.read_sql(query_c.statement, query_c.session.bind)
if df_c.empty:
df = df_mkt
else:
df = df_mkt.join(df_c.set_index('id_instrument'),
how='left',
on='id_instrument')
return df
def get_eventsdata(start_date, end_date, flag_impact):
events = admin.table_events()
query = admin.session_mktdata().query(events.c.dt_date, events.c.id_event, events.c.name_event,
events.c.cd_occurrence,
events.c.dt_impact_beg,
events.c.cd_trade_direction, events.c.dt_test, events.c.dt_test2,
events.c.dt_impact_end, events.c.dt_vol_peak, events.c.cd_open_position_time,
events.c.cd_close_position_time) \
.filter(events.c.dt_date >= start_date) \
.filter(events.c.dt_date <= end_date) \
.filter(events.c.flag_impact == flag_impact) \
# .filter(events.c.cd_occurrence == 'e')
df_event = pd.read_sql(query.statement, query.session.bind)
return df_event
def trade_volume(dt_date, dt_last_week, w, nameCode, core_instrumentid):
pu = PlotUtil()
options_mkt = admin.table_options_mktdata()
evalDate = dt_date.strftime("%Y-%m-%d") # Set as Friday
plt.rcParams['font.sans-serif'] = ['STKaiti']
plt.rcParams.update({'font.size': 15})
"""当日成交持仓量 """
query_volume = admin.session_mktdata().query(options_mkt.c.dt_date,
options_mkt.c.cd_option_type,
options_mkt.c.amt_strike,
options_mkt.c.amt_holding_volume,
options_mkt.c.amt_trading_volume,
options_mkt.c.amt_close,
options_mkt.c.pct_implied_vol
) \
.filter(or_(options_mkt.c.dt_date == evalDate,options_mkt.c.dt_date == dt_last_week)) \
.filter(options_mkt.c.id_underlying == core_instrumentid)\
.filter(options_mkt.c.flag_night != 1)
df_2d = pd.read_sql(query_volume.statement, query_volume.session.bind)
df = df_2d[df_2d['dt_date'] == dt_date].reset_index()
df_lw = df_2d[df_2d['dt_date'] == dt_last_week].reset_index()
df_call = df[df['cd_option_type'] == 'call'].reset_index()
df_put = df[df['cd_option_type'] == 'put'].reset_index()
dflw_call = df_lw[df_lw['cd_option_type'] == 'call'].reset_index()
dflw_put = df_lw[df_lw['cd_option_type'] == 'put'].reset_index()
call_deltas = []
put_deltas = []
for idx, row in df_call.iterrows():
row_put = df_put.loc[idx]
strike = row['amt_strike']
rowlw_call = dflw_call[dflw_call['amt_strike'] == strike]
rowlw_put = dflw_put[dflw_put['amt_strike'] == strike]
last_holding_call = 0.0
last_holding_put = 0.0
try:
last_holding_call = rowlw_call['amt_holding_volume'].values[0]
except:
pass
try:
last_holding_put = rowlw_put['amt_holding_volume'].values[0]
except:
pass
call_delta = row['amt_holding_volume'] - last_holding_call
put_delta = row_put['amt_holding_volume'] - last_holding_put
call_deltas.append(call_delta)
put_deltas.append(put_delta)
if nameCode == 'sr':
wt = 25
else:
wt = 15
strikes = df_call['amt_strike'].tolist()
strikes1 = df_call['amt_strike'] + wt
holding_call = df_call['amt_holding_volume'].tolist()
holding_put = df_put['amt_holding_volume'].tolist()
trading_call = df_call['amt_trading_volume'].tolist()
trading_put = df_put['amt_trading_volume'].tolist()
df_results = pd.DataFrame({
'0 call iv':
df_call['pct_implied_vol'].tolist(),
'1 call delta_holding':
call_deltas,
'2 call holding':
df_call['amt_holding_volume'].tolist(),
'3 call trading':
df_call['amt_trading_volume'].tolist(),
'4 call price':
df_call['amt_close'].tolist(),
'5 strikes':
df_put['amt_strike'].tolist(),
'6 put price':
df_put['amt_close'].tolist(),
'7 put trading':
df_put['amt_trading_volume'].tolist(),
'8 put holding':
df_put['amt_holding_volume'].tolist(),
'9 put delta_holding':
put_deltas,
'91 put iv':
df_put['pct_implied_vol'].tolist()
})
df_results.to_csv('../data/' + nameCode + '_holdings_' + evalDate + '.csv')
ldgs = ['持仓量（看涨）', '持仓量（看跌）', '成交量（看涨）', '成交量（看跌）']
f3, ax3 = plt.subplots()
p1 = ax3.bar(strikes, holding_call, width=wt, color=pu.colors[0])
p2 = ax3.bar(strikes1, holding_put, width=wt, color=pu.colors[1])
p3, = ax3.plot(strikes,
trading_call,
color=pu.colors[2],
linestyle=pu.lines[2],
linewidth=2)
p4, = ax3.plot(strikes,
trading_put,
color=pu.colors[3],
linestyle=pu.lines[3],
linewidth=2)
ax3.legend([p1, p2, p3, p4],
ldgs,
bbox_to_anchor=(0., 1.02, 1., .102),
loc=3,
ncol=4,
mode="expand",
borderaxespad=0.,
frameon=False)
ax3.spines['top'].set_visible(False)
ax3.spines['right'].set_visible(False)
ax3.yaxis.set_ticks_position('left')
ax3.xaxis.set_ticks_position('bottom')
f3.set_size_inches((12, 8))
f3.savefig('../data/' + nameCode + '_holdings_' + evalDate + '.png',
dpi=300,
format='png',
bbox_inches='tight')
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')
pu = PlotUtil()
###########################################################################################
# engine2 = create_engine('mysql+pymysql://guest:[email protected]/mktdata', echo=False)
# metadata2 = MetaData(engine2)
# Session2 = sessionmaker(bind=engine2)
# sess2 = Session2()
futureMkt = dbt.FutureMkt
optionMkt = dbt.OptionMkt
futuremkt_table = dbt.FutureMkt
options_table = dbt.Options
query_pcr = admin.session_mktdata().query(optionMkt.dt_date, optionMkt.cd_option_type,optionMkt.id_underlying,
func.sum(optionMkt.amt_holding_volume).label('total_holding_volume'),
func.sum(optionMkt.amt_trading_volume).label('total_trading_volume')
) \
.filter(optionMkt.dt_date >= dt_start) \
.filter(optionMkt.name_code == namecode) \
.group_by(optionMkt.cd_option_type, optionMkt.dt_date,optionMkt.id_underlying)
query_srf = admin.session_mktdata().query(futureMkt.dt_date, futureMkt.id_instrument,
futureMkt.amt_close, futureMkt.amt_trading_volume,
futureMkt.amt_settlement) \
.filter(futureMkt.dt_date >= dt_start).filter(futureMkt.name_code == namecode)\
.filter(futureMkt.flag_night != 1)
df_srf = pd.read_sql(query_srf.statement, query_srf.session.bind)
df_pcr = pd.read_sql(query_pcr.statement, query_pcr.session.bind)
df_underlying_core = pcr(df_pcr)
hist_vol(df_underlying_core)
