def download_tick_data( self, symbol: str, exchange: Exchange, start: datetime ) -> int: """ Query tick data from RQData. """ req = HistoryRequest( symbol=symbol, exchange=exchange, start=start, end=datetime.now() ) if not rqdata_client.inited: rqdata_client.init() data = rqdata_client.query_history(req) if data: database_manager.save_tick_data(data) return(len(data)) return 0
def move_df_to_mongodb(imported_data:pd.DataFrame,collection_name:str): ticks = [] start = None count = 0 utc_8 = timezone(timedelta(hours=8)) for row in imported_data.itertuples(): tick = TickData( symbol = row.symbol, exchange = row.exchange, datetime = row.datetime.replace(tzinfo=utc_8), #datetime = row.datetime, name = "TickDataName", volume = row.volume, open_interest = row.open_interest, turnover = row.turnover, last_price = row.last_price, last_volume = row.last_volume, last_amount = row.last_amount, limit_up = row.limit_up, limit_down = row.limit_down, open_price = row.open_price, high_price = row.high_price, low_price = row.low_price, pre_close = row.pre_close, bid_price_1 = row.bid_price_1, bid_price_2 = row.bid_price_2, bid_price_3 = row.bid_price_3, bid_price_4 = row.bid_price_4, bid_price_5 = row.bid_price_5, ask_price_1 = row.ask_price_1, ask_price_2 = row.ask_price_2, ask_price_3 = row.ask_price_3, ask_price_4 = row.ask_price_4, ask_price_5 = row.ask_price_5, bid_volume_1 = row.bid_volume_1, bid_volume_2 = row.bid_volume_2, bid_volume_3 = row.bid_volume_3, bid_volume_4 = row.bid_volume_4, bid_volume_5 = row.bid_volume_5, ask_volume_1 = row.ask_volume_1, ask_volume_2 = row.ask_volume_2, ask_volume_3 = row.ask_volume_3, ask_volume_4 = row.ask_volume_4, ask_volume_5 = row.ask_volume_5, gateway_name="DB", ) ticks.append(tick) # do some statistics count += 1 if not start: start = tick.datetime end = tick.datetime # insert into database database_manager.save_tick_data(ticks, collection_name) print(f'Insert Tick: {count} from {start} - {end}')
def csv_load(file): """ 读取csv文件内容,并写入到数据库中 """ with open(file, "r") as f: reader = csv.DictReader(f) ticks = [] start = None count = 0 for item in reader: # generate datetime # date = item["交易日"] # second = item["最后修改时间"] # millisecond = item["最后修改毫秒"] # standard_time = date + " " + second + "." + millisecond standard_time = item["datetime"] dt = datetime.strptime(standard_time, "%Y-%m-%d %H:%M:%S.%f") # temp = Exchange # for k, v in Exchange.__members__.items(): # if item["exchange"][0] == k: # temp = v # filter if dt.time() > time(15, 1) and dt.time() < time(20, 59): continue tick = TickData( symbol=item["symbol"], datetime=dt, # exchange=filter(lambda v: v for k, v in Exchange.__members__.items() if item["exchange"][0] == k), exchange=Exchange[item["exchange"]], last_price=float(item["last_price"]), volume=float(item["volume"]), bid_price_1=float(item["bid_price1"]), bid_volume_1=float(item["bid_volume1"]), ask_price_1=float(item["ask_price1"]), ask_volume_1=float(item["ask_volume1"]), gateway_name="DB", ) ticks.append(tick) # do some statistics count += 1 if not start: start = tick.datetime end = tick.datetime database_manager.save_tick_data(ticks) print("插入数据", start, "-", end, "总数量:", count)
def run(self): """""" while self.active: try: task = self.queue.get(timeout=1) task_type, data = task if task_type == "tick": database_manager.save_tick_data([data]) elif task_type == "bar": database_manager.save_bar_data([data]) except Empty: continue
def csv_load(file: str): """ 读取csv文件内容,并写入到数据库中 """ with open(file, "r") as f: reader = csv.DictReader(f) ticks = [] start = None count = 0 for item in reader: # generate datetime date = item["交易日"] second = item["最后修改时间"] millisecond = item["最后修改毫秒"] standard_time = date + " " + second + "." + millisecond dt = datetime.strptime(standard_time, "%Y%m%d %H:%M:%S.%f") # filter if time(15, 1) < dt.time() < time(20, 59): continue tick = TickData( symbol="RU88", datetime=dt, exchange=Exchange.SHFE, last_price=float(item["最新价"]), volume=float(item["持仓量"]), bid_price_1=float(item["申买价一"]), bid_volume_1=float(item["申买量一"]), ask_price_1=float(item["申卖价一"]), ask_volume_1=float(item["申卖量一"]), gateway_name="DB", ) ticks.append(tick) # do some statistics count += 1 if not start: start = tick.datetime end = tick.datetime database_manager.save_tick_data(ticks) print("插入数据", start, "-", end, "总数量:", count)
def run(self): """ 数据收集引擎,事件循环函数 :return: """ while self.active: try: # 从队列获取数据, 超时1秒 task = self.queue.get(timeout=1) # 类型, 数据 task_type, data = task if task_type == "tick": # tick 数据存数据库 database_manager.save_tick_data([data]) elif task_type == "bar": # bar 数据存数据库 database_manager.save_bar_data([data]) except Empty: continue
def run(self): """""" while self.active: try: task = self.queue.get(timeout=1) task_type, data = task if task_type == "tick": database_manager.save_tick_data([data]) elif task_type == "bar": database_manager.save_bar_data([data]) except Empty: continue except Exception: self.active = False info = sys.exc_info() event = Event(EVENT_RECORDER_EXCEPTION, info) self.event_engine.put(event)
def load_csv(file_path, main_instrument_only=False, job_queue: Queue = None): """ 根据文件名,选择不同的格式进行解析,并插入tick数据库,同时合成分钟及小时数据 :param file_path: :param main_instrument_only: 仅接收主力合约 :param job_queue: 是否使用任务队列 :return: """ _, file_name = os.path.split(file_path) # 大商所L1分笔行情 if file_name.startswith('MFL1_TAQ_'): labels = [ "Symbol", "ShortName", "SecurityID", "TradingDate", "TradingTime", "LastPrice", "HighPrice", "LowPrice", "TradeVolume", "TotalVolume", "LastVolume", "PreTotalPosition", "TotalPosition", "PrePositionChange", "TotalAmount", "TradeAmount", "PriceUpLimit", "PriceDownLimit", "PreSettlePrice", "PreClosePrice", "OpenPrice", "ClosePrice", "SettlePrice", "LifeLow", "LifeHigh", "AveragePrice01", "AveragePrice", "BidImplyQty", "AskImplyQty", "BuyOrSell", "SellPrice01", "BuyPrice01", "SellVolume01", "BuyVolume01", "SellPrice05", "SellPrice04", "SellPrice03", "SellPrice02", "BuyPrice02", "BuyPrice03", "BuyPriceO4", "BuyPrice05", "SellVolume05", "SellVolumeO4", "SellVolume03", "SellVolume02", "BuyVolume02", "BuyVolume03", "BuyVolume04", "BuyVolume05", "PreDelta", "Delta", "Change", "ChangeRatio", "Varieties", "ContinueSign", "Market", "UNIX", "OpenClose", "Amplitude", "VolRate", "OrderDiff", "OrderRate", "SellVOL", "BuyVOL", "PositionChange", "DeliverySettlePrice", ] else: raise ValueError(f"file_name='{file_name}' 目前不支持") ticks = [] start = None count = 0 symbol_idx = labels.index('Symbol') trading_time_idx = labels.index('TradingTime') volume_idx = labels.index('TradeVolume') open_interest_idx = labels.index('TotalPosition') last_price_idx = labels.index('LastPrice') upper_limit_price_idx = labels.index('PriceUpLimit') lower_limit_price_idx = labels.index('PriceDownLimit') open_price_idx = labels.index('OpenPrice') high_price_idx = labels.index('HighPrice') low_price_idx = labels.index('LowPrice') pre_close_idx = labels.index('PreClosePrice') bid_price1_idx = labels.index('BuyPrice01') bid_vol1_idx = labels.index('BuyVolume01') ask_price1_idx = labels.index('SellPrice01') ask_vol1_idx = labels.index('SellVolume01') exchange_idx = labels.index("Market") exchange = None with open(file_path, "r") as f: # , encoding='utf-8' reader = csv.reader(_.replace('\x00', '') for _ in f) for item in reader: # generate datetime dt = datetime.strptime(item[trading_time_idx], "%Y-%m-%d %H:%M:%S.%f").astimezone( timezone(timedelta(hours=8))) # filter 剔除9点钟以前的数据, 以及 15:00 ~ 21:00 之间的数据 if time(8, 0) <= dt.time() < time(9, 0) or time( 15, 1) <= dt.time() < time(21, 0): continue instrument_id = item[symbol_idx] if exchange is None: instrument_type, exchange = get_exchange(instrument_id) if exchange is None: try: exchange = getattr(Exchange, item[exchange_idx]) logger.warning( "当前品种 %s[%s] 不支持,需要更新交易所对照表后才可载入数据,使用数据中指定的交易所 %s", instrument_id, instrument_type, exchange) except AttributeError: logger.exception("当前品种 %s[%s] 不支持,需要更新交易所对照表后才可载入数据", instrument_id, instrument_type) break # 仅接收主力合约 if main_instrument_only and len(instrument_id) - len( instrument_type) == 4: break tick = TickData( symbol=instrument_id, datetime=dt, exchange=exchange, # Exchange.SHFE volume=float(item[volume_idx]), open_interest=float(item[open_interest_idx]), last_price=float(item[last_price_idx]), limit_up=float(item[upper_limit_price_idx]), limit_down=float(item[lower_limit_price_idx]), open_price=float(item[open_price_idx]), high_price=float(item[high_price_idx]), low_price=float(item[low_price_idx]), pre_close=float(item[pre_close_idx]), bid_price_1=float(item[bid_price1_idx]), bid_volume_1=float(item[bid_vol1_idx]), ask_price_1=float(item[ask_price1_idx]), ask_volume_1=float(item[ask_vol1_idx]), gateway_name="DB", ) ticks.append(tick) # do some statistics count += 1 if not start: start = tick.datetime if count == 0: return end = tick.datetime if job_queue is None: database_manager.save_tick_data(ticks) else: job_queue.put((database_manager.save_tick_data, ticks)) logger.info("插入 Tick 数据%s - %s 总数量:%d %s", start, end, count, '' if job_queue is None else '加入任务队列') for n, (minutes, interval) in enumerate(zip([1, 60], [Interval.MINUTE, Interval.HOUR]), start=1): df = pd.DataFrame( [[_.datetime, _.open_interest, _.last_price, _.volume] for _ in ticks], columns=['datetime', 'open_interest', 'last_price', 'volume']) interval_df = merge_df_2_minutes_bar(df, minutes) bars = [ BarData( gateway_name="DB", symbol=instrument_id, exchange=exchange, datetime=_['datetime'].to_pydatetime( ), # Timestamp 数据可以通过 .tz_localize('Asia/Shanghai') 增加时区信息 interval=interval, volume=_["volume"], open_interest=_["open_interest"], open_price=_["open_price"], high_price=_["high_price"], low_price=_["low_price"], close_price=_["close_price"], ) for key, _ in interval_df.iterrows() ] if job_queue is None: database_manager.save_bar_data(bars) else: job_queue.put((database_manager.save_bar_data, bars)) logger.info("插入 %s 数据%s - %s 总数量:%d %s", interval, start, end, len(bars), '' if job_queue is None else '加入任务队列')
high_price=tick_df['high_price'][i], low_price=tick_df['low_price'][i], pre_close=tick_df['pre_close'][i], bid_price_1=tick_df['bid_price_1'][i], bid_price_2=tick_df['bid_price_2'][i], bid_price_3=tick_df['bid_price_3'][i], bid_price_4=tick_df['bid_price_4'][i], bid_price_5=tick_df['bid_price_5'][i], ask_price_1=tick_df['ask_price_1'][i], ask_price_2=tick_df['ask_price_2'][i], ask_price_3=tick_df['ask_price_3'][i], ask_price_4=tick_df['ask_price_4'][i], ask_price_5=tick_df['ask_price_5'][i], bid_volume_1=tick_df['bid_volume_1'][i], bid_volume_2=tick_df['bid_volume_2'][i], bid_volume_3=tick_df['bid_volume_3'][i], bid_volume_4=tick_df['bid_volume_4'][i], bid_volume_5=tick_df['bid_volume_5'][i], ask_volume_1=tick_df['ask_volume_1'][i], ask_volume_2=tick_df['ask_volume_2'][i], ask_volume_3=tick_df['ask_volume_3'][i], ask_volume_4=tick_df['ask_volume_4'][i], ask_volume_5=tick_df['ask_volume_5'][i], ) count += 1 ticks.append(tick) if count > 1000: database_manager.save_tick_data(ticks) count = 0 ticks = []
def load_csv(file_path): """ 读取csv文件内容,并写入到数据库中 当前文件没有考虑夜盘数据的情况,待有夜盘数据后需要对 trade_date 进行一定的调整 """ ticks = [] start = None count = 0 exchange = None with open(file_path, "r") as f: # , encoding='utf-8' reader = csv.reader(f) for item in reader: localtime, instrument_id, trade_date, action_date, update_time, update_millisec, \ last_price, volume, high_price, low_price, open_price, close_price, \ avg_price, ask_price1, ask_vol1, bid_price1, bid_vol1, upper_limit_price, \ lower_limit_price, open_interest, trun_over, pre_close, pre_open_interest, pre_settlement_price = item # generate datetime standard_time = f"{trade_date} {update_time}.{update_millisec}" dt = datetime.strptime(standard_time, "%Y%m%d %H:%M:%S.%f") # filter if time(15, 1) < dt.time() < time(20, 59): continue if exchange is None: instrument_type, exchange = get_exchange(instrument_id) if exchange is None: logger.exception("当前品种 %s 不支持,需要更新交易所对照表后才可载入数据", instrument_id) break tick = TickData( symbol=instrument_id, datetime=dt, exchange=exchange, # Exchange.SHFE volume=float(volume), open_interest=float(open_interest), last_price=float(last_price), limit_up=float(upper_limit_price), limit_down=float(lower_limit_price), open_price=float(open_price), high_price=float(high_price), low_price=float(low_price), pre_close=float(pre_close), bid_price_1=float(bid_price1), bid_volume_1=float(bid_vol1), ask_price_1=float(ask_price1), ask_volume_1=float(ask_vol1), gateway_name="DB", ) ticks.append(tick) # do some statistics count += 1 if not start: start = tick.datetime if count == 0: return end = tick.datetime database_manager.save_tick_data(ticks) logger.info("插入 Tick 数据%s - %s 总数量:%d", start, end, count) for n, (minutes, interval) in enumerate(zip([1, 60], [Interval.MINUTE, Interval.HOUR]), start=1): df = pd.DataFrame( [[ _.datetime, _.open_interest, _.last_price, _.volume ] for _ in ticks], columns=[ 'datetime', 'open_interest', 'last_price', 'volume' ]) interval_df = merge_df_2_minutes_bar(df, minutes) bars = [BarData( gateway_name="DB", symbol=instrument_id, exchange=exchange, datetime=_['datetime'], interval=interval, volume=_["volume"], open_interest=_["open_interest"], open_price=_["open_price"], high_price=_["high_price"], low_price=_["low_price"], close_price=_["close_price"], ) for key, _ in interval_df.T.items()] database_manager.save_bar_data(bars) logger.info("插入 %s 数据%s - %s 总数量:%d", interval, start, end, len(bars))