def pcr_etf_option(dt_start, dt_end, name_code, df_res): optionMkt = admin.table_options_mktdata() Index_mkt = admin.table_indexes_mktdata() query_pcr = admin.session_gc().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) df_pcr = pd.read_sql(query_pcr.statement, query_pcr.session.bind) query_etf = admin.session_gc().query(Index_mkt.c.dt_date, Index_mkt.c.amt_close, Index_mkt.c.amt_open, Index_mkt.c.id_instrument.label(c.Util.ID_UNDERLYING)) \ .filter(Index_mkt.c.dt_date >= dt_start).filter(Index_mkt.c.dt_date <= dt_end) \ .filter(Index_mkt.c.id_instrument == 'index_50etf') df_50etf = pd.read_sql(query_etf.statement, query_etf.session.bind) df = df_pcr.groupby(['dt_date', 'cd_option_type' ])['total_holding_volume', 'total_trading_volume'].sum().reset_index() 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, }) df_pcr = pd.DataFrame(pc_ratio) df_pcr = pd.merge(df_pcr, df_50etf[['dt_date', 'amt_close']], how='left', on=['dt_date'], suffixes=['', '_r']) df_pcr = df_pcr.sort_values(by='dt_date', ascending=False).reset_index(drop=True) df_res.loc[:, name_code + ':A:date'] = df_pcr['dt_date'] df_res.loc[:, name_code + ':B:tv_c'] = df_pcr['tv_c'] df_res.loc[:, name_code + ':C:tv_p'] = df_pcr['tv_p'] df_res.loc[:, name_code + ':E:date'] = df_pcr['dt_date'] df_res.loc[:, name_code + ':F:hv_c'] = df_pcr['hv_c'] df_res.loc[:, name_code + ':G:hv_p'] = df_pcr['hv_p'] df_res.loc[:, name_code + ':I:date'] = df_pcr['dt_date'] df_res.loc[:, name_code + ':J:tv_pcr'] = df_pcr['tv_pcr'] df_res.loc[:, name_code + ':K:hv_pcr'] = df_pcr['hv_pcr'] df_res.loc[:, name_code + ':L:amt_close'] = df_pcr['amt_close'] return df_res
def commodity_option_market_overview_by_month(start_date, end_date, name_code): optionMkt = admin.table_options_mktdata() futureMkt = admin.table_futures_mktdata() query = admin.session_gc().query(optionMkt.c.dt_date,optionMkt.c.id_underlying, func.sum(optionMkt.c.amt_trading_volume).label('option_trading_volume'), func.sum(optionMkt.c.amt_trading_value).label('option_trading_value') ) \ .filter(optionMkt.c.dt_date >= start_date) \ .filter(optionMkt.c.dt_date <= end_date) \ .filter(optionMkt.c.name_code == name_code) \ .group_by(optionMkt.c.dt_date, optionMkt.c.id_underlying) df_option_trading = pd.read_sql(query.statement, query.session.bind) query_future = admin.session_gc().query(futureMkt.c.dt_date,futureMkt.c.id_instrument, func.sum(futureMkt.c.amt_trading_volume).label('future_trading_volume') ) \ .filter(futureMkt.c.dt_date >= start_date) \ .filter(futureMkt.c.dt_date <= end_date) \ .filter(futureMkt.c.name_code == name_code) \ .group_by(futureMkt.c.dt_date, futureMkt.c.id_instrument) df_future_trading = pd.read_sql(query_future.statement, query_future.session.bind) query_option_holding = admin.session_gc().query(optionMkt.c.dt_date, optionMkt.c.id_underlying, func.sum(optionMkt.c.amt_holding_volume).label('option_holding_volume')) \ .filter(optionMkt.c.dt_date >= start_date) \ .filter(optionMkt.c.dt_date <= end_date) \ .filter(optionMkt.c.name_code == name_code)\ .group_by(optionMkt.c.dt_date, optionMkt.c.id_underlying) #每日日盘收盘持仓数据 df_option_holding = pd.read_sql(query_option_holding.statement, query_option_holding.session.bind) query_future_holding = admin.session_gc().query(futureMkt.c.dt_date,futureMkt.c.id_instrument, func.sum(futureMkt.c.amt_holding_volume).label('future_holding_volume')) \ .filter(futureMkt.c.dt_date >= start_date) \ .filter(futureMkt.c.dt_date <= end_date) \ .filter(futureMkt.c.name_code == name_code) \ .group_by(futureMkt.c.dt_date, futureMkt.c.id_instrument) #每日日盘收盘持仓数据 df_future_holding = pd.read_sql(query_future_holding.statement, query_future_holding.session.bind) # new_df = pd.merge(A_df, B_df, how='left', left_on=['A_c1', 'c2'], right_on=['B_c1', 'c2']) df_future = pd.merge(df_future_holding, df_future_trading, on=[c.Util.DT_DATE, c.Util.ID_INSTRUMENT]) df_option = pd.merge(df_option_holding, df_option_trading, on=[c.Util.DT_DATE, c.Util.ID_UNDERLYING]) df = pd.merge(df_option, df_future, left_on=[c.Util.DT_DATE, c.Util.ID_UNDERLYING], right_on=[c.Util.DT_DATE, c.Util.ID_INSTRUMENT]) # df = df.groupby([c.Util.DT_DATE,c.Util.ID_UNDERLYING,c.Util.ID_INSTRUMENT])['col3'].sum() return df
def commodity_option_market_overview(start_date, end_date, name_code): optionMkt = admin.table_options_mktdata() futureMkt = admin.table_futures_mktdata() query = admin.session_gc().query(optionMkt.c.dt_date, func.sum(optionMkt.c.amt_trading_volume).label('option_trading_volume'), func.sum(optionMkt.c.amt_trading_value).label('option_trading_value') ) \ .filter(optionMkt.c.dt_date >= start_date) \ .filter(optionMkt.c.dt_date <= end_date) \ .filter(optionMkt.c.name_code == name_code) \ .group_by(optionMkt.c.dt_date) df_option_trading = pd.read_sql(query.statement, query.session.bind) query_future = admin.session_gc().query(futureMkt.c.dt_date, func.sum(futureMkt.c.amt_trading_volume).label('future_trading_volume') ) \ .filter(futureMkt.c.dt_date >= start_date) \ .filter(futureMkt.c.dt_date <= end_date) \ .filter(futureMkt.c.name_code == name_code) \ .group_by(futureMkt.c.dt_date) df_future_trading = pd.read_sql(query_future.statement, query_future.session.bind) query_option_holding = admin.session_gc().query(optionMkt.c.dt_date, func.sum(optionMkt.c.amt_holding_volume).label('option_holding_volume')) \ .filter(optionMkt.c.dt_date >= start_date) \ .filter(optionMkt.c.dt_date <= end_date) \ .filter(optionMkt.c.name_code == name_code) \ .group_by(optionMkt.c.dt_date) #每日日盘收盘持仓数据 df_option_holding = pd.read_sql(query_option_holding.statement, query_option_holding.session.bind) query_future_holding = admin.session_gc().query(futureMkt.c.dt_date, func.sum(futureMkt.c.amt_holding_volume).label('future_holding_volume')) \ .filter(futureMkt.c.dt_date >= start_date) \ .filter(futureMkt.c.dt_date <= end_date) \ .filter(futureMkt.c.name_code == name_code) \ .group_by(futureMkt.c.dt_date) #每日日盘收盘持仓数据 df_future_holding = pd.read_sql(query_future_holding.statement, query_future_holding.session.bind) # df = pd.merge(df_option_trading,df_future_trading[[c.Util.DT_DATE,'future_trading_volume']],on=c.Util.DT_DATE) # df = pd.merge(df,df_option_holding,on=c.Util.DT_DATE) # df = pd.merge(df,df_future_holding,on=c.Util.DT_DATE) df_future = pd.merge(df_future_holding, df_future_trading, on=[c.Util.DT_DATE]) df_option = pd.merge(df_option_holding, df_option_trading, on=[c.Util.DT_DATE]) df = pd.merge(df_option, df_future, on=[c.Util.DT_DATE]) return df
def get_index_mktdata(start_date, end_date, id_index): Index_mkt = admin.table_indexes_mktdata() query_etf = admin.session_gc().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_dzqh_cf_minute(start_date, end_date, name_code): table_cf = admin.table_cf_minute() query = admin.session_gc().query(table_cf.c.dt_datetime, table_cf.c.id_instrument, table_cf.c.dt_date, table_cf.c.amt_open, table_cf.c.amt_close, table_cf.c.amt_trading_volume). \ filter( (table_cf.c.dt_date >= start_date) & (table_cf.c.dt_date <= end_date) & (table_cf.c.name_code == name_code)) df = pd.read_sql(query.statement, query.session.bind) df = df[df['id_instrument'].str.contains("_")] return df
def get_comoption_mktdata(start_date, end_date, name_code): # Future_mkt = dbt.FutureMkt table_mktdata = admin.table_futures_mktdata_gc() Option_mkt = admin.table_options_mktdata() options = dbt.Options query_mkt = admin.session_gc(). \ query(Option_mkt.c.dt_date, Option_mkt.c.id_instrument, Option_mkt.c.id_underlying, Option_mkt.c.code_instrument, Option_mkt.c.amt_close, Option_mkt.c.amt_open, Option_mkt.c.amt_settlement, Option_mkt.c.amt_last_settlement, Option_mkt.c.amt_trading_volume,Option_mkt.c.amt_trading_value, Option_mkt.c.pct_implied_vol, Option_mkt.c.amt_holding_volume, Option_mkt.c.amt_trading_volume ) \ .filter(Option_mkt.c.dt_date >= start_date).filter(Option_mkt.c.dt_date <= end_date) \ .filter(Option_mkt.c.name_code == name_code).filter(Option_mkt.c.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_gc(). \ query(table_mktdata.c.dt_date, table_mktdata.c.id_instrument.label(c.Util.ID_UNDERLYING), table_mktdata.c.amt_settlement.label(c.Util.AMT_UNDERLYING_CLOSE), table_mktdata.c.amt_open.label(c.Util.AMT_UNDERLYING_OPEN_PRICE)) \ .filter(table_mktdata.c.dt_date >= start_date).filter(table_mktdata.c.dt_date <= end_date) \ .filter(table_mktdata.c.name_code == name_code) 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_gc_future_c1_daily(start_date, end_date, name_code): table_cf = admin.table_futures_mktdata_gc() query = admin.session_gc().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_trading_volume). \ 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("_")] df = c.FutureUtil.get_futures_daily_c1(df) return df
def get_50option_mktdata(start_date, end_date): Index_mkt = dbt.IndexMkt Option_mkt = admin.table_options_mktdata() options = dbt.Options util = c.Util query_mkt = admin.session_gc().query(Option_mkt.c.dt_date, Option_mkt.c.id_instrument, Option_mkt.c.code_instrument, Option_mkt.c.amt_open, Option_mkt.c.amt_close, Option_mkt.c.amt_settlement, Option_mkt.c.amt_last_settlement,Option_mkt.c.amt_trading_value, Option_mkt.c.amt_trading_volume, Option_mkt.c.amt_holding_volume, Option_mkt.c.pct_implied_vol ) \ .filter(Option_mkt.c.dt_date >= start_date).filter(Option_mkt.c.dt_date <= end_date) \ .filter(Option_mkt.c.datasource == 'wind').filter(Option_mkt.c.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_gc().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_gc_future_mktdata(start_date, end_date, name_code): Futures = dbt.Futures table_mktdata = admin.table_futures_mktdata_gc() query_mkt = admin.session_gc().query(table_mktdata.c.dt_date, table_mktdata.c.id_instrument, table_mktdata.c.name_code, table_mktdata.c.amt_close, table_mktdata.c.amt_trading_volume,table_mktdata.c.amt_trading_value, table_mktdata.c.amt_settlement, table_mktdata.c.amt_last_close, table_mktdata.c.amt_last_settlement, table_mktdata.c.amt_open, table_mktdata.c.amt_high, table_mktdata.c.amt_low) \ .filter(table_mktdata.c.dt_date >= start_date) \ .filter(table_mktdata.c.dt_date <= end_date) \ .filter(table_mktdata.c.name_code == name_code) 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