def get_cont_data(asset, start_date, end_date, freq='1m', nearby=1, rollrule='-10b'): cnx = dbaccess.connect(**dbaccess.hist_dbconfig) if nearby == 0: mdf = dbaccess.load_min_data_to_df(cnx, 'fut_min', asset, start_date, end_date, minid_start=300, minid_end=2114, database='hist_data') mdf['contract'] = asset else: mdf = misc.nearby(asset, nearby, start_date, end_date, rollrule, 'm', need_shift=True, database='hist_data') mdf = backtest.cleanup_mindata(mdf, asset) xdf = dh.conv_ohlc_freq(mdf, freq, extra_cols=['contract'], bar_func=dh.bar_conv_func2) return xdf
def validate_db_data(tday, filter=False): all_insts = filter_main_cont(tday, filter) data_count = {} inst_list = {'min': [], 'daily': []} cnx = dbaccess.connect(**dbaccess.dbconfig) for instID in all_insts: df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', instID, tday, tday) if len(df) <= 0: inst_list['daily'].append(instID) elif (df.close[-1] == 0) or (df.high[-1] == 0) or (df.low[-1] == 0) or df.open[-1] == 0: inst_list['daily'].append(instID) df = dbaccess.load_min_data_to_df(cnx, 'fut_min', instID, tday, tday, minid_start=300, minid_end=2115, database='blueshale') if len(df) <= 100: output = instID + ':' + str(len(df)) inst_list['min'].append(output) elif df.min_id < 2055: output = instID + ': end earlier' inst_list['min'].append(output) cnx.close() print inst_list
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 load_data(self, idx): asset = self.sim_assets[idx] for prod in asset: if prod in self.data_store: continue ticker = prod if '$' not in ticker: ticker_sp = [ticker, 'spot'] else: ticker_sp = ticker.split('$') ticker = ticker_sp[0] postfix = '_daily' if self.sim_freq == 'm': postfix = '_min' dbtable = ticker_sp[-1] + postfix if ticker_sp[-1] in ['spot']: field_id = 'spotID' elif ticker_sp[-1] in ['ccy']: field_id = 'instID' if len(ticker_sp) > 2: nb = int(ticker_sp[1]) if len(ticker_sp) > 3: rollrule = ticker_sp[2] else: rollrule = '-1b' df = misc.nearby(ticker, nb, self.config['start_date'], self.config['end_date'], rollrule, self.sim_freq, need_shift=self.need_shift, database=self.config.get( 'dbconfig', dbaccess.dbconfig)['database']) else: cnx = dbaccess.connect( **self.config.get('dbconfig', dbaccess.dbconfig)) if self.sim_freq == 'd': df = dbaccess.load_daily_data_to_df( cnx, dbtable, ticker, self.config['start_date'], self.config['end_date'], index_col='date', field=field_id) else: minid_start = 1500 minid_end = 2114 if ticker in misc.night_session_markets: minid_start = 300 df = dbaccess.load_min_data_to_df( cnx, dbtable, ticker, self.config['start_date'], self.config['end_date'], minid_start, minid_end) df['contract'] = ticker if self.sim_freq == 'm': df = misc.cleanup_mindata(df, ticker) df.columns = [(prod, col) for col in df.columns] self.data_store[prod] = df
def spd_ratiovol_by_product(products, start_d, end_d, periods=12, tenor='-1m'): cont_mth, exch = dbaccess.prod_main_cont_exch(products) contlist = contract_range(products, exch, cont_mth, start_d, end_d) exp_dates = [get_opt_expiry(cont, inst2contmth(cont)) for cont in contlist] data = { 'is_dtime': True, 'data_column': 'close', 'data_freq': '30min', 'xs': [0.5, 0.25, 0.75], 'xs_names': ['atm', 'v25', 'v75'], 'xs_func': 'bs_delta_to_strike', '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': 'bsopt.BSOpt', 'delta_func': 'bsopt.BSDelta', 'is_dtime': data['is_dtime'], } freq = data['data_freq'] 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) cnx = dbaccess.connect(**dbaccess.dbconfig) 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, products, 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 get_cont_data(asset, start_date, end_date, freq = '1m', nearby = 1, rollrule = '-10b'): cnx = dbaccess.connect(**dbaccess.hist_dbconfig) if nearby == 0: mdf = dbaccess.load_min_data_to_df(cnx, 'fut_min', asset, start_date, end_date, minid_start = 300, minid_end = 2114, database = 'hist_data') mdf['contract'] = asset else: mdf = misc.nearby(asset, nearby, start_date, end_date, rollrule, 'm', need_shift=True, database = 'hist_data') mdf = misc.cleanup_mindata(mdf, asset) xdf = dh.conv_ohlc_freq(mdf, freq, extra_cols = ['contract'], bar_func = dh.bar_conv_func2) return xdf
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 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 = 1, start_date = None, end_date = None, roll_rule = '-20b', freq = 'd', need_shift=False, database = None): contlist, exp_dates, _ = cont_expiry_list(prodcode, start_date, end_date, roll_rule) sdate = start_date is_new = True dbconf = copy.deepcopy(dbaccess.dbconfig) if database: dbconf['database'] = database cnx = dbaccess.connect(**dbconf) 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 > 0: 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) if need_shift == 1: shift = tmp_df['close'][-1] - df['close'][-1] for ticker in ['open', 'high', 'low', 'close']: df[ticker] = df[ticker] + shift else: shift = float(tmp_df['close'][-1])/float(df['close'][-1]) for ticker in ['open', 'high', 'low', 'close']: df[ticker] = df[ticker] * shift df = df.append(new_df) sdate = exp + datetime.timedelta(days=1) return df
def rolling_hist_data(product, n, start_date, end_date, cont_roll, freq, win_roll='-20b', database='hist_data'): if start_date > end_date: return None cnx = dbaccess.connect(**dbaccess.dbconfig) cursor = cnx.cursor() stmt = "select exchange, contract from trade_products where product_code='{prod}' ".format( prod=product) cursor.execute(stmt) out = [(exchange, contract) for (exchange, contract) in cursor] exch = str(out[0][0]) cont = str(out[0][1]) cont_mth = [month_code_map[c] for c in cont] cnx.close() contlist = contract_range(product, exch, cont_mth, start_date, end_date) exp_dates = [ day_shift(contract_expiry(cont), cont_roll) for cont in contlist ] # print contlist, exp_dates sdate = start_date all_data = {} i = 0 dbconfig = copy.deepcopy(dbaccess.dbconfig) dbconfig['database'] = database cnx = dbaccess.connect(**dbconfig) 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': df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', nb_cont, day_shift(sdate, win_roll), min(exp, end_date)) else: df = dbaccess.load_min_data_to_df(cnx, 'fut_min', nb_cont, day_shift(sdate, win_roll), min(exp, end_date)) all_data[i] = {'contract': nb_cont, 'data': df} i += 1 sdate = min(exp, end_date) + datetime.timedelta(days=1) cnx.close() return all_data
def process_trade_date(cnx, db_table='fut_min', col_name='instID'): stmt = 'select distinct({colname}) from {dbtable}'.format(colname=col_name, dbtable=db_table) cursor = cnx.cursor() cursor.execute(stmt) inst_list = [] for line in cursor: inst_list.append(str(line[0])) for inst in inst_list: print "processing instID = %s" % inst df = dbaccess.load_min_data_to_df(cnx, db_table, inst, index_col=None) df['datetime'] = df['datetime'].apply( lambda x: datetime.datetime.strptime(x, "%Y-%m-%d %H:%M:%S")) df = process_min_id(df) df.to_sql('fut_min', cnx, 'sqlite', if_exists='append', index=False)
def load_data(self, idx): asset = self.sim_assets[idx] for prod in asset: if prod in self.data_store: continue ticker = prod if '$' not in ticker: ticker_sp = [ticker, 'spot'] else: ticker_sp = ticker.split('$') ticker = ticker_sp[0] postfix = '_daily' if self.sim_freq == 'm': postfix = '_min' dbtable = ticker_sp[-1] + postfix if ticker_sp[-1] in ['spot']: field_id = 'spotID' elif ticker_sp[-1] in ['ccy']: field_id = 'instID' if len(ticker_sp) > 2: nb = int(ticker_sp[1]) if len(ticker_sp) > 3: rollrule = ticker_sp[2] else: rollrule = '-1b' df = misc.nearby(ticker, nb, self.config['start_date'], self.config['end_date'], rollrule, self.sim_freq, need_shift = self.need_shift, database = self.config.get('dbconfig', dbaccess.dbconfig)['database']) else: cnx = dbaccess.connect(**self.config.get('dbconfig', dbaccess.dbconfig)) if self.sim_freq == 'd': df = dbaccess.load_daily_data_to_df(cnx, dbtable, ticker, self.config['start_date'], self.config['end_date'], index_col='date', field = field_id) else: minid_start = 1500 minid_end = 2114 if ticker in misc.night_session_markets: minid_start = 300 df = dbaccess.load_min_data_to_df(cnx, dbtable, ticker, self.config['start_date'], self.config['end_date'], minid_start, minid_end) df['contract'] = ticker if self.sim_freq == 'm': df = misc.cleanup_mindata(df, ticker) df.columns = [(prod, col) for col in df.columns] self.data_store[prod] = df
def validate_db_data(tday, filter = False): all_insts = misc.filter_main_cont(tday, filter) data_count = {} inst_list = {'min': [], 'daily': [] } cnx = dbaccess.connect(**dbaccess.dbconfig) for instID in all_insts: df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', instID, tday, tday) if len(df) <= 0: inst_list['daily'].append(instID) elif (df.close[-1] == 0) or (df.high[-1] == 0) or (df.low[-1] == 0) or df.open[-1] == 0: inst_list['daily'].append(instID) df = dbaccess.load_min_data_to_df(cnx, 'fut_min', instID, tday, tday, minid_start=300, minid_end=2115) if len(df) <= 100: output = instID + ':' + str(len(df)) inst_list['min'].append(output) elif df.min_id < 2055: output = instID + ': end earlier' inst_list['min'].append(output) print inst_list
def rolling_hist_data(product, n, start_date, end_date, cont_roll, freq, win_roll='-20b', database='hist_data'): if start_date > end_date: return None cnx = dbaccess.connect(**dbaccess.dbconfig) cursor = cnx.cursor() stmt = "select exchange, contract from trade_products where product_code='{prod}' ".format(prod=product) cursor.execute(stmt) out = [(exchange, contract) for (exchange, contract) in cursor] exch = str(out[0][0]) cont = str(out[0][1]) cont_mth = [month_code_map[c] for c in cont] cnx.close() contlist, _ = contract_range(product, exch, cont_mth, start_date, end_date) exp_dates = [day_shift(contract_expiry(cont), cont_roll) for cont in contlist] sdate = start_date all_data = {} i = 0 dbconfig = copy.deepcopy(dbaccess.dbconfig) dbconfig['database'] = database cnx = dbaccess.connect(**dbconfig) 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': df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', nb_cont, day_shift(sdate, win_roll), min(exp, end_date)) else: df = dbaccess.load_min_data_to_df(cnx, 'fut_min', nb_cont, day_shift(sdate, win_roll), min(exp, end_date)) all_data[i] = {'contract': nb_cont, 'data': df} i += 1 sdate = min(exp, end_date) + datetime.timedelta(days=1) cnx.close() return all_data
def prepare_data_env(self, inst, mid_day = True): if self.instruments[inst].ptype == instrument.ProductType.Option: return self.db_conn = dbaccess.connect(**dbaccess.dbconfig) if self.daily_data_days > 0 or mid_day: #self.logger.debug('Updating historical daily data for %s' % self.scur_day.strftime('%Y-%m-%d')) daily_start = workdays.workday(self.scur_day, -self.daily_data_days, CHN_Holidays) daily_end = self.scur_day ddf = dbaccess.load_daily_data_to_df(self.db_conn, 'fut_daily', inst, daily_start, daily_end, index_col = None) if len(ddf) > 0: self.instruments[inst].price = self.instruments[inst].mid_price = ddf['close'].iloc[-1] self.instruments[inst].last_update = 0 self.instruments[inst].prev_close = ddf['close'].iloc[-1] for fobj in self.day_data_func[inst]: ts = fobj.sfunc(ddf) if type(ts).__name__ == 'Series': if ts.name in ddf.columns: self.logger.warning('TimeSeries name %s is already in the columns for inst = %s' % (ts.name, inst)) ddf[ts.name]= ts elif type(ts).__name__ == 'DataFrame': for col_name in ts.columns: if col_name in ddf.columns: self.logger.warning('TimeSeries name %s is already in the columns for inst = %s' % (col_name, inst)) ddf[col_name] = ts[col_name] self.day_data[inst] = data_handler.DynamicRecArray(dataframe = ddf) if self.min_data_days > 0 or mid_day: #self.logger.debug('Updating historical min data for %s' % self.scur_day.strftime('%Y-%m-%d')) d_start = workdays.workday(self.scur_day, -self.min_data_days, CHN_Holidays) d_end = self.scur_day min_start = int(self.instruments[inst].start_tick_id/1000) min_end = int(self.instruments[inst].last_tick_id/1000)+1 mdf = dbaccess.load_min_data_to_df(self.db_conn, 'fut_min', inst, d_start, d_end, minid_start=min_start, minid_end=min_end, index_col = None) mdf = cleanup_mindata(mdf, self.instruments[inst].product, index_col = None) mdf['bar_id'] = self.conv_bar_id(mdf['min_id']) if len(mdf)>0: min_date = mdf['date'].iloc[-1] if (len(self.day_data[inst])==0) or (min_date > self.day_data[inst].data['date'][-1]): ddf = data_handler.conv_ohlc_freq(mdf, 'd', index_col = None) self.cur_day[inst]['open'] = float(ddf.open[-1]) self.cur_day[inst]['close'] = float(ddf.close[-1]) self.cur_day[inst]['high'] = float(ddf.high[-1]) self.cur_day[inst]['low'] = float(ddf.low[-1]) self.cur_day[inst]['volume'] = int(ddf.volume[-1]) self.cur_day[inst]['openInterest'] = int(ddf.openInterest[-1]) self.cur_min[inst]['datetime'] = pd.datetime(*mdf['datetime'].iloc[-1].timetuple()[0:-3]) self.cur_min[inst]['date'] = mdf['date'].iloc[-1] self.cur_min[inst]['open'] = float(mdf['open'].iloc[-1]) self.cur_min[inst]['close'] = float(mdf['close'].iloc[-1]) self.cur_min[inst]['high'] = float(mdf['high'].iloc[-1]) self.cur_min[inst]['low'] = float(mdf['low'].iloc[-1]) self.cur_min[inst]['volume'] = self.cur_day[inst]['volume'] self.cur_min[inst]['openInterest'] = self.cur_day[inst]['openInterest'] self.cur_min[inst]['min_id'] = int(mdf['min_id'].iloc[-1]) self.cur_min[inst]['bar_id'] = self.conv_bar_id(self.cur_min[inst]['min_id']) self.instruments[inst].price = self.instruments[inst].mid_price = float(mdf['close'].iloc[-1]) self.instruments[inst].last_update = 0 #self.logger.debug('inst=%s tick data loaded for date=%s' % (inst, min_date)) if 1 not in self.min_data_func[inst]: self.min_data[inst][1] = data_handler.DynamicRecArray(dataframe = mdf) for m in sorted(self.min_data_func[inst]): if m != 1: mdf_m = data_handler.conv_ohlc_freq(mdf, str(m)+'min', index_col = None, bar_func = self.conv_bar_id, extra_cols = ['bar_id']) else: mdf_m = mdf for fobj in self.min_data_func[inst][m]: ts = fobj.sfunc(mdf_m) if type(ts).__name__ == 'Series': if ts.name in mdf_m.columns: self.logger.warning('TimeSeries name %s is already in the columns for inst = %s' % (ts.name, inst)) mdf_m[ts.name]= ts elif type(ts).__name__ == 'DataFrame': for col_name in ts.columns: if col_name in mdf_m.columns: self.logger.warning('TimeSeries name %s is already in the columns for inst = %s' % (col_name, inst)) mdf_m[col_name] = ts[col_name] self.min_data[inst][m] = data_handler.DynamicRecArray(dataframe = mdf_m) #print inst, self.min_data[inst][m].data['date'][-1] < self.cur_min[inst]['date'] self.db_conn.close()
def prepare_data_env(self, inst, mid_day=True): if self.instruments[inst].ptype == instrument.ProductType.Option: return self.db_conn = dbaccess.connect(**dbaccess.dbconfig) if self.daily_data_days > 0 or mid_day: #self.logger.debug('Updating historical daily data for %s' % self.scur_day.strftime('%Y-%m-%d')) daily_start = workdays.workday(self.scur_day, -self.daily_data_days, CHN_Holidays) daily_end = self.scur_day ddf = dbaccess.load_daily_data_to_df(self.db_conn, 'fut_daily', inst, daily_start, daily_end, index_col=None) if len(ddf) > 0: self.instruments[inst].price = self.instruments[ inst].mid_price = ddf['close'].iloc[-1] self.instruments[inst].last_update = 0 self.instruments[inst].prev_close = ddf['close'].iloc[-1] for fobj in self.day_data_func[inst]: ts = fobj.sfunc(ddf) if type(ts).__name__ == 'Series': if ts.name in ddf.columns: self.logger.warning( 'TimeSeries name %s is already in the columns for inst = %s' % (ts.name, inst)) ddf[ts.name] = ts elif type(ts).__name__ == 'DataFrame': for col_name in ts.columns: if col_name in ddf.columns: self.logger.warning( 'TimeSeries name %s is already in the columns for inst = %s' % (col_name, inst)) ddf[col_name] = ts[col_name] self.day_data[inst] = data_handler.DynamicRecArray(dataframe=ddf) if self.min_data_days > 0 or mid_day: #self.logger.debug('Updating historical min data for %s' % self.scur_day.strftime('%Y-%m-%d')) d_start = workdays.workday(self.scur_day, -self.min_data_days, CHN_Holidays) d_end = self.scur_day min_start = int(self.instruments[inst].start_tick_id / 1000) min_end = int(self.instruments[inst].last_tick_id / 1000) + 1 mdf = dbaccess.load_min_data_to_df(self.db_conn, 'fut_min', inst, d_start, d_end, minid_start=min_start, minid_end=min_end, index_col=None) mdf = cleanup_mindata(mdf, self.instruments[inst].product, index_col=None) mdf['bar_id'] = self.conv_bar_id(mdf['min_id']) if len(mdf) > 0: min_date = mdf['date'].iloc[-1] if (len(self.day_data[inst]) == 0) or ( min_date > self.day_data[inst].data['date'][-1]): ddf = data_handler.conv_ohlc_freq(mdf, 'd', index_col=None) self.cur_day[inst]['open'] = float(ddf.open[-1]) self.cur_day[inst]['close'] = float(ddf.close[-1]) self.cur_day[inst]['high'] = float(ddf.high[-1]) self.cur_day[inst]['low'] = float(ddf.low[-1]) self.cur_day[inst]['volume'] = int(ddf.volume[-1]) self.cur_day[inst]['openInterest'] = int( ddf.openInterest[-1]) self.cur_min[inst]['datetime'] = pd.datetime( *mdf['datetime'].iloc[-1].timetuple()[0:-3]) self.cur_min[inst]['date'] = mdf['date'].iloc[-1] self.cur_min[inst]['open'] = float(mdf['open'].iloc[-1]) self.cur_min[inst]['close'] = float(mdf['close'].iloc[-1]) self.cur_min[inst]['high'] = float(mdf['high'].iloc[-1]) self.cur_min[inst]['low'] = float(mdf['low'].iloc[-1]) self.cur_min[inst]['volume'] = self.cur_day[inst]['volume'] self.cur_min[inst]['openInterest'] = self.cur_day[inst][ 'openInterest'] self.cur_min[inst]['min_id'] = int(mdf['min_id'].iloc[-1]) self.cur_min[inst]['bar_id'] = self.conv_bar_id( self.cur_min[inst]['min_id']) self.instruments[inst].price = self.instruments[ inst].mid_price = float(mdf['close'].iloc[-1]) self.instruments[inst].last_update = 0 #self.logger.debug('inst=%s tick data loaded for date=%s' % (inst, min_date)) if 1 not in self.min_data_func[inst]: self.min_data[inst][1] = data_handler.DynamicRecArray( dataframe=mdf) for m in sorted(self.min_data_func[inst]): if m != 1: mdf_m = data_handler.conv_ohlc_freq( mdf, str(m) + 'min', index_col=None, bar_func=self.conv_bar_id, extra_cols=['bar_id']) else: mdf_m = mdf for fobj in self.min_data_func[inst][m]: ts = fobj.sfunc(mdf_m) if type(ts).__name__ == 'Series': if ts.name in mdf_m.columns: self.logger.warning( 'TimeSeries name %s is already in the columns for inst = %s' % (ts.name, inst)) mdf_m[ts.name] = ts elif type(ts).__name__ == 'DataFrame': for col_name in ts.columns: if col_name in mdf_m.columns: self.logger.warning( 'TimeSeries name %s is already in the columns for inst = %s' % (col_name, inst)) mdf_m[col_name] = ts[col_name] self.min_data[inst][m] = data_handler.DynamicRecArray( dataframe=mdf_m) #print inst, self.min_data[inst][m].data['date'][-1] < self.cur_min[inst]['date'] self.db_conn.close()
def nearby(prodcode, n=1, start_date=None, end_date=None, roll_rule='-20b', freq='d', need_shift=False, database=None): contlist, exp_dates, _ = cont_expiry_list(prodcode, start_date, end_date, roll_rule) sdate = start_date is_new = True dbconf = copy.deepcopy(dbaccess.dbconfig) if database: dbconf['database'] = database cnx = dbaccess.connect(**dbconf) 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 > 0: 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) if need_shift == 1: shift = tmp_df['close'][-1] - df['close'][-1] for ticker in ['open', 'high', 'low', 'close']: df[ticker] = df[ticker] + shift else: shift = float(tmp_df['close'][-1]) / float(df['close'][-1]) for ticker in ['open', 'high', 'low', 'close']: df[ticker] = df[ticker] * shift df = df.append(new_df) sdate = exp + datetime.timedelta(days=1) return df