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