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_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_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 == '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={'amt_close': c.Util.AMT_UNDERLYING_CLOSE}) df_option_metrics = df.join(df_etf.set_index('dt_datetime'), how='left', on='dt_datetime') return df_option_metrics
def get_index_intraday(start_date, end_date, id_index): Index = admin.table_index_mktdata_intraday() query = admin.session_intraday().query(Index.c.dt_datetime,Index.c.dt_date, Index.c.id_instrument, Index.c.amt_close, Index.c.amt_trading_volume, Index.c.amt_trading_value) \ .filter(Index.c.dt_datetime >= start_date).filter(Index.c.dt_datetime <= end_date) \ .filter(Index.c.id_instrument == id_index) df = pd.read_sql(query.statement, query.session.bind) return df