def load_data(self, assets): contlist = {} exp_dates = {} dbconfig = self.config.get('dbconfig', dbaccess.hist_dbconfig) cnx = dbaccess.connect(**dbconfig) for i, prod in enumerate(assets): cont_mth, exch = dbaccess.prod_main_cont_exch(prod) self.contlist[prod], _ = misc.contract_range(prod, exch, cont_mth, self.start_date, self.end_date) self.exp_dates[prod] = [misc.contract_expiry(cont) for cont in contlist[prod]] edates = [ misc.day_shift(d, self.config['rollrule']) for d in exp_dates[prod] ] sdates = [ misc.day_shift(d, self.sim_period) for d in exp_dates[prod] ] self.data_store[prod] = {} for cont, sd, ed in zip(contlist[prod], sdates, edates): if self.sim_freq == 'd': tmp_df = dbaccess.load_daily_data_to_df(cnx, 'fut_min', cont, sd, ed) else: minid_start = 1500 minid_end = 2114 if prod in misc.night_session_markets: minid_start = 300 tmp_df = dbaccess.load_min_data_to_df(cnx, 'fut_min', cont, sd, ed, minid_start, minid_end) misc.cleanup_mindata(tmp_df, prod) tmp_df['contract'] = cont self.data_store[prod][cont] = tmp_df cnx.close()
def load_data(self, assets): contlist = {} exp_dates = {} dbconfig = self.config.get('dbconfig', dbaccess.hist_dbconfig) cnx = dbaccess.connect(**dbconfig) for i, prod in enumerate(assets): cont_mth, exch = dbaccess.prod_main_cont_exch(prod) self.contlist[prod], _ = misc.contract_range( prod, exch, cont_mth, self.start_date, self.end_date) self.exp_dates[prod] = [ misc.contract_expiry(cont) for cont in contlist[prod] ] edates = [ misc.day_shift(d, self.config['rollrule']) for d in exp_dates[prod] ] sdates = [ misc.day_shift(d, self.sim_period) for d in exp_dates[prod] ] self.data_store[prod] = {} for cont, sd, ed in zip(contlist[prod], sdates, edates): if self.sim_freq == 'd': tmp_df = dbaccess.load_daily_data_to_df( cnx, 'fut_min', cont, sd, ed) else: minid_start = 1500 minid_end = 2114 if prod in misc.night_session_markets: minid_start = 300 tmp_df = dbaccess.load_min_data_to_df( cnx, 'fut_min', cont, sd, ed, minid_start, minid_end) misc.cleanup_mindata(tmp_df, prod) tmp_df['contract'] = cont self.data_store[prod][cont] = tmp_df cnx.close()
def cont_expiry_list(prodcode, start_date, end_date, roll_rule='-0d'): cont_mth, exch = dbaccess.prod_main_cont_exch(prodcode) contlist, tenor_list = contract_range(prodcode, exch, cont_mth, start_date, day_shift(end_date, '12m')) exp_dates = [ day_shift(contract_expiry(cont), roll_rule) for cont in contlist ] return contlist, exp_dates, tenor_list
def hist_cso_by_product(prodcode, start_d, end_d, periods = 24, tenor = '-1w', max_spd = 2, writeDB = False, mode = 'n'): cont_mth, exch = dbaccess.prod_main_cont_exch(prodcode) contlist, _ = contract_range(prodcode, exch, cont_mth, start_d, end_d) exp_dates = [get_opt_expiry(cont, inst2contmth(cont)) for cont in contlist] if mode == 'n': xs_func = 'bachelier_delta_to_strike' pricer_func = 'bsopt.BSFwdNormal' delta_func = 'bsopt.BSFwdNormalDelta' else: xs_func = 'bs_delta_to_strike' pricer_func = 'bsopt.BSOpt' delta_func = 'bsopt.BSDelta' data = {'is_dtime': True, 'data_column': 'close', 'data_freq': '30min', 'xs': [0.5, 0.25, 0.75], 'xs_names': ['atm', 'v25', 'v75'], 'xs_func': xs_func, 'rehedge_period': 1, 'term_tenor': tenor, 'database': 'hist_data' } option_input = {'otype': True, 'ir': 0.0, 'end_vol': 0.0, 'ref_vol': 0.5, 'pricer_func': pricer_func, 'delta_func': delta_func, 'is_dtime': data['is_dtime'], } freq = data['data_freq'] dbconfig = copy.deepcopy(**dbaccess.hist_dbconfig) dbconfig['database'] = data['database'] cnx = dbaccess.connect(**dbconfig) for cont, expiry in zip(contlist, exp_dates): expiry_d = expiry.date() if expiry_d > end_d: break p_str = '-' + str(int(tenor[1:-1]) * periods) + tenor[-1] d_start = day_shift(expiry_d, p_str) if freq == 'd': df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', cont, d_start, expiry_d, index_col = None) else: mdf = dbaccess.load_min_data_to_df(cnx, 'fut_min', cont, d_start, expiry_d, minid_start=300, minid_end=2115, index_col = None) mdf = cleanup_mindata(mdf, prodcode, index_col = None) mdf['bar_id'] = dh.bar_conv_func2(mdf['min_id']) df = dh.conv_ohlc_freq(mdf, freq, bar_func=dh.bar_conv_func2, extra_cols=['bar_id'], index_col = None) cnx.close() option_input['expiry'] = expiry data['dataframe'] = df vol_df = realized_termstruct(option_input, data) print cont, expiry_d, vol_df
def nearby(prodcode, n, start_date, end_date, roll_rule, freq, need_shift=False, database='C:/Users/j291414/Desktop/market_data.db'): if start_date > end_date: return None cont_mth, exch = dbaccess.prod_main_cont_exch(prodcode) contlist = contract_range(prodcode, exch, cont_mth, start_date, day_shift(end_date, roll_rule[1:])) exp_dates = [day_shift(contract_expiry(cont), roll_rule) for cont in contlist] # print contlist, exp_dates sdate = start_date is_new = True # dbconf = copy.deepcopy(dbaccess.dbconfig) # dbconf['database'] = database # cnx = dbaccess.connect(**dbconf) cnx = sqlite3.connect(database) for idx, exp in enumerate(exp_dates): if exp < start_date: continue elif sdate > end_date: break nb_cont = contlist[idx + n - 1] if freq == 'd': new_df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', nb_cont, sdate, min(exp, end_date)) else: minid_start = 1500 minid_end = 2114 if prodcode in night_session_markets: minid_start = 300 new_df = dbaccess.load_min_data_to_df(cnx, 'fut_min', nb_cont, sdate, min(exp, end_date), minid_start, minid_end) if len(new_df.shape) == 0: continue nn = new_df.shape[0] if nn > 0: new_df['contract'] = pd.Series([nb_cont] * nn, index=new_df.index) else: continue if is_new: df = new_df is_new = False else: if need_shift: if isinstance(df.index[-1], datetime.datetime): last_date = df.index[-1].date() else: last_date = df.index[-1] tmp_df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', nb_cont, last_date, last_date) shift = tmp_df['close'][-1] - df['close'][-1] for ticker in ['open', 'high', 'low', 'close']: df[ticker] = df[ticker] + shift df = df.append(new_df) sdate = min(exp, end_date) + datetime.timedelta(days=1) cnx.close() return df
def filter_main_cont(sdate, filter=False): insts, prods = dbaccess.load_alive_cont(sdate) if not filter: return insts main_cont = {} for pc in prods: main_cont[pc], exch = dbaccess.prod_main_cont_exch(pc) main_insts = [] for inst in insts: pc = inst2product(inst) mth = int(inst[-2:]) if mth in main_cont[pc]: main_insts.append(inst) return main_insts
def save_hist_data(start_date, end_date, index_list = [], product_codes=[], spot_list = [], min_bar = False): conn = dbaccess.connect(**dbaccess.dbconfig) tday = datetime.date.today() for symbol, cmd_idx, desc in index_list: df = load_symbol(symbol, ['open', 'high', 'low', 'close', 'volume'], start_date, end_date) if len(df)> 0: df['instID'] = cmd_idx exch = symbol.split('.')[-1] df['exch'] = exch print "saving daily data for instID = %s with number of data pts = %s" % (cmd_idx, len(df)) df.to_sql('fut_daily', conn, 'sqlite', if_exists='append', index=False) for prodcode in product_codes: cont_mth, exch = dbaccess.prod_main_cont_exch(prodcode) cont_list, _ = misc.contract_range(prodcode, exch, cont_mth, start_date, end_date) exp_dates = [misc.contract_expiry(cont) for cont in cont_list] exch2wind_dict = dict([ (v, k) for k, v in wind_exch_map.iteritems( )]) for cont, exp in zip(cont_list, exp_dates): if exp >= start_date: ex = exch2wind_dict[exch] symbol = cont + '.' + ex ddf = load_symbol(symbol, ['open', 'high', 'low', 'close', 'volume', 'oi'], max(exp - datetime.timedelta(days = 400), start_date), min(exp, tday), freq = 'd') if len(ddf) > 0: print "saving daily data for instID = %s with number of data pts = %s" % (cont, len(ddf)) ddf['instID'] = cont ddf['exch'] = exch ddf.to_sql('fut_daily', conn, 'sqlite', if_exists='append', index=False) if min_bar: mdf = load_symbol(symbol, ['open', 'high', 'low', 'close', 'volume', 'oi'], max(exp - datetime.timedelta(days = 400),start_date), min(exp, tday), freq='m') if len(mdf) > 0: print "saving min data for instID = %s with number of data pts = %s" % (cont, len(mdf)) mdf['instID'] = cont mdf['exch'] = exch mdf = process_min_id(mdf) mdf.to_sql('fut_min', conn, 'sqlite', if_exists='append', index=False) for symbol, spotID, desc in spot_list: df = load_symbol(symbol, ['close'], start_date, end_date) if len(df)> 0: df['spotID'] = spotID print "saving daily data for spotID = %s with number of data pts = %s" % (spotID, len(df)) df.to_sql('spot_daily', conn, 'sqlite', if_exists='append', index=False)
def hist_cso_by_product(prodcode, start_d, end_d, periods=24, tenor='-1w', max_spd=2, writeDB=False, mode='n'): cont_mth, exch = dbaccess.prod_main_cont_exch(prodcode) contlist = contract_range(prodcode, exch, cont_mth, start_d, end_d) exp_dates = [get_opt_expiry(cont, inst2contmth(cont)) for cont in contlist] if mode == 'n': xs_func = 'bachelier_delta_to_strike' pricer_func = 'bsopt.BSFwdNormal' delta_func = 'bsopt.BSFwdNormalDelta' else: xs_func = 'bs_delta_to_strike' pricer_func = 'bsopt.BSOpt' delta_func = 'bsopt.BSDelta' data = { 'is_dtime': True, 'data_column': 'close', 'data_freq': '30min', 'xs': [0.5, 0.25, 0.75], 'xs_names': ['atm', 'v25', 'v75'], 'xs_func': xs_func, 'rehedge_period': 1, 'term_tenor': tenor, 'database': 'hist_data' } option_input = { 'otype': True, 'ir': 0.0, 'end_vol': 0.0, 'ref_vol': 0.5, 'pricer_func': pricer_func, 'delta_func': delta_func, 'is_dtime': data['is_dtime'], } freq = data['data_freq'] dbconfig = copy.deepcopy(**dbaccess.hist_dbconfig) dbconfig['database'] = data['database'] cnx = dbaccess.connect(**dbconfig) for cont, expiry in zip(contlist, exp_dates): expiry_d = expiry.date() if expiry_d > end_d: break p_str = '-' + str(int(tenor[1:-1]) * periods) + tenor[-1] d_start = day_shift(expiry_d, p_str) if freq == 'd': df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', cont, d_start, expiry_d, index_col=None) else: mdf = dbaccess.load_min_data_to_df(cnx, 'fut_min', cont, d_start, expiry_d, minid_start=300, minid_end=2115, index_col=None) mdf = cleanup_mindata(mdf, prodcode, index_col=None) mdf['bar_id'] = dh.bar_conv_func2(mdf['min_id']) df = dh.conv_ohlc_freq(mdf, freq, bar_func=dh.bar_conv_func2, extra_cols=['bar_id'], index_col=None) cnx.close() option_input['expiry'] = expiry data['dataframe'] = df vol_df = realized_termstruct(option_input, data) print cont, expiry_d, vol_df
def cont_expiry_list(prodcode, start_date, end_date, roll_rule = '-0d'): cont_mth, exch = dbaccess.prod_main_cont_exch(prodcode) contlist, tenor_list = contract_range(prodcode, exch, cont_mth, start_date, day_shift(end_date, '12m')) exp_dates = [day_shift(contract_expiry(cont), roll_rule) for cont in contlist] return contlist, exp_dates, tenor_list