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 pcr_commodity_option(dt_start, dt_end, name_code, df_res, min_holding):
optionMkt = admin.table_options_mktdata()
futureMkt = admin.table_futures_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)
df_srf = get_data.get_future_c1_by_option_daily(start_date, end_date,
name_code, min_holding)
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_srf[['dt_date', 'id_instrument', '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 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 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_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