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)