def create_by_order_info(order_info: OrderInfo): direction, action, instrument_id = order_info.direction, order_info.action, order_info.instrument_id order_price, order_vol, order_id = order_info.order_price, order_info.order_vol, order_info.order_id order_date, order_time, order_millisec = order_info.order_date, order_info.order_time, order_info.order_millisec stg_run_id = order_info.stg_run_id # TODO: 以后还可以增加滑点,成交比例等 # instrument_info = Config.instrument_info_dic[instrument_id] # multiple = instrument_info['VolumeMultiple'] # margin_ratio = instrument_info['LongMarginRatio'] multiple, margin_ratio = 1, 1 margin = order_vol * order_price * multiple * margin_ratio commission = 0 trade_info = TradeInfo(stg_run_id=stg_run_id, order_id=order_id, trade_date=order_date, trade_time=order_time, trade_millisec=order_millisec, direction=direction, action=action, instrument_id=instrument_id, order_price=order_price, order_vol=order_vol, trade_price=order_price, trade_vol=order_vol, margin=margin, commission=commission ) if UPDATE_OR_INSERT_PER_ACTION: with with_db_session(engine_abat, expire_on_commit=False) as session: session.add(trade_info) session.commit() return trade_info
def create_by_trade_info(trade_info: TradeInfo): direction, action, instrument_id = trade_info.direction, trade_info.action, trade_info.instrument_id trade_price, trade_vol, trade_id = trade_info.trade_price, trade_info.trade_vol, trade_info.trade_id trade_date, trade_time, trade_millisec = trade_info.trade_date, trade_info.trade_time, trade_info.trade_millisec stg_run_id = trade_info.stg_run_id if action == int(Action.Close): raise ValueError('trade_info.action 不能为 close') pos_status_info = PosStatusInfo(stg_run_id=stg_run_id, trade_id=trade_id, trade_date=trade_date, trade_time=trade_time, trade_millisec=trade_millisec, direction=direction, instrument_id=instrument_id, position=trade_vol, avg_price=trade_price, cur_price=trade_price, margin=0, margin_chg=0, floating_pl=0, floating_pl_chg=0, floating_pl_cum=0, ) if UPDATE_OR_INSERT_PER_ACTION: # 更新最新持仓纪录 with with_db_session(engine_abat, expire_on_commit=False) as session: session.add(pos_status_info) session.commit() return pos_status_info
def __init__(self, symbol_list=None): super().__init__() self.symbol_list = symbol_list self._mutex = threading.Lock() self._last_check_datetime = datetime.now() - timedelta(minutes=1) self.interval_timedelta = timedelta(seconds=15) self.symbol_target_position_dic = {} # 设定相应周期的事件驱动句柄 接收的参数类型 self._on_period_event_dic[PeriodType.Tick].param_type = dict # 记录合约最近一次执行操作的时间 self.symbol_last_deal_datetime = {} # 记录合约最近一个发送买卖请求的时间 self.instrument_lastest_order_datetime_dic = {} # 目前由于交易是异步执行,在尚未记录每一笔订单的情况下,时间太短可能会导致仓位与请求但出现不同步现象,导致下单过多的问题 self.timedelta_between_deal = timedelta(seconds=3) self.min_order_vol = 0.1 self.symbol_latest_price_dic = defaultdict(float) self.weight = 1 if not DEBUG else 0.2 # 默认仓位权重 self.stop_loss_rate = -0.03 # 初始化 symbol 基本信息 with with_db_session(engine_md) as session: symbol_info_list = session.query(SymbolPair).filter( func.concat(SymbolPair.base_currency, SymbolPair.quote_currency).in_(symbol_list)).all() self.symbol_info_dic = {symbol.base_currency+symbol.quote_currency: symbol for symbol in symbol_info_list} self.logger.info('接受订单文件目录:%s', self._folder_path) self.load_feedback_file()
def connect(self): with with_db_session(engine_md) as session: data = session.query(SymbolPair).all() self.symbol_currency_dic = { f'{sym.base_currency}{sym.quote_currency}': sym.base_currency for sym in data }
def create(stg_run_id, init_cash: int, md: dict): """ 根据 md 及 初始化资金 创建对象,默认日期为当前md数据-1天 :param stg_run_id: :param init_cash: :param md: :return: """ trade_date = str_2_date(md['ActionDay']) - timedelta(days=1) trade_time = pd_timedelta_2_timedelta(md['ActionTime']) trade_millisec = int(md.setdefault('ActionMillisec', 0)) trade_price = float(md['close']) acc_status_info = AccountStatusInfo(stg_run_id=stg_run_id, trade_date=trade_date, trade_time=trade_time, trade_millisec=trade_millisec, available_cash=init_cash, balance_tot=init_cash, ) if UPDATE_OR_INSERT_PER_ACTION: # 更新最新持仓纪录 with with_db_session(engine_abat, expire_on_commit=False) as session: session.add(acc_status_info) session.commit() return acc_status_info
def init(alter_table=False): BaseModel.metadata.create_all(engine_md) if alter_table: with with_db_session(engine=engine_md) as session: for table_name, _ in BaseModel.metadata.tables.items(): sql_str = f"show table status from {Config.DB_SCHEMA_MD} where name=:table_name" row_data = session.execute(sql_str, params={ 'table_name': table_name }).first() if row_data is None: continue if row_data[1].lower() == 'myisam': continue logger.info('修改 %s 表引擎为 MyISAM', table_name) sql_str = "ALTER TABLE %s ENGINE = MyISAM" % table_name session.execute(sql_str) # This is an issue https://www.mail-archive.com/[email protected]/msg19744.html session.execute( f"ALTER TABLE {SymbolPair.__tablename__} CHANGE COLUMN `id` `id` INT(11) NULL AUTO_INCREMENT" ) session.commit() # This is an issue https://www.mail-archive.com/[email protected]/msg19744.html session.execute( f"ALTER TABLE {MDTick.__tablename__} CHANGE COLUMN `id` `id` INT(11) NULL AUTO_INCREMENT" ) session.commit() logger.info("所有表结构建立完成")
def create_by_trade_info(trade_info: TradeInfo): direction, action, instrument_id = trade_info.direction, trade_info.action, trade_info.instrument_id trade_price, trade_vol, trade_id = trade_info.trade_price, trade_info.trade_vol, trade_info.trade_id trade_date, trade_time, trade_millisec = trade_info.trade_date, trade_info.trade_time, trade_info.trade_millisec stg_run_id = trade_info.stg_run_id if action == int(Action.Close): raise ValueError('trade_info.action 不能为 close') pos_status_info = PosStatusInfo(stg_run_id=stg_run_id, trade_id=trade_id, trade_date=trade_date, trade_time=trade_time, trade_millisec=trade_millisec, direction=direction, instrument_id=instrument_id, position=trade_vol, avg_price=trade_price, cur_price=trade_price, margin=0, margin_chg=0, floating_pl=0, floating_pl_chg=0, floating_pl_cum=0, ) if UPDATE_OR_INSERT_PER_ACTION: # 更新最新持仓纪录 with with_db_session(engine_abat, expire_on_commit=False) as session: session.add(pos_status_info) session.commit() return pos_status_info
def create_by_order_info(order_info: OrderInfo): direction, action, instrument_id = order_info.direction, order_info.action, order_info.instrument_id order_price, order_vol, order_id = order_info.order_price, order_info.order_vol, order_info.order_id order_date, order_time, order_millisec = order_info.order_date, order_info.order_time, order_info.order_millisec stg_run_id = order_info.stg_run_id # TODO: 以后还可以增加滑点,成交比例等 # instrument_info = Config.instrument_info_dic[instrument_id] # multiple = instrument_info['VolumeMultiple'] # margin_ratio = instrument_info['LongMarginRatio'] multiple, margin_ratio = 1, 1 margin = order_vol * order_price * multiple * margin_ratio commission = 0 trade_info = TradeInfo(stg_run_id=stg_run_id, order_id=order_id, trade_date=order_date, trade_time=order_time, trade_millisec=order_millisec, direction=direction, action=action, instrument_id=instrument_id, order_price=order_price, order_vol=order_vol, trade_price=order_price, trade_vol=order_vol, margin=margin, commission=commission ) if UPDATE_OR_INSERT_PER_ACTION: with with_db_session(engine_abat, expire_on_commit=False) as session: session.add(trade_info) session.commit() return trade_info
def create(stg_run_id, init_cash: int, md: dict): """ 根据 md 及 初始化资金 创建对象,默认日期为当前md数据-1天 :param stg_run_id: :param init_cash: :param md: :return: """ trade_date = str_2_date(md['ActionDay']) - timedelta(days=1) trade_time = pd_timedelta_2_timedelta(md['ActionTime']) trade_millisec = int(md.setdefault('ActionMillisec', 0)) trade_price = float(md['close']) acc_status_info = AccountStatusInfo(stg_run_id=stg_run_id, trade_date=trade_date, trade_time=trade_time, trade_millisec=trade_millisec, available_cash=init_cash, balance_tot=init_cash, ) if UPDATE_OR_INSERT_PER_ACTION: # 更新最新持仓纪录 with with_db_session(engine_abat, expire_on_commit=False) as session: session.add(acc_status_info) session.commit() return acc_status_info
def get_account_balance(stg_run_id): """ 获取 account_info 账户走势数据 :param stg_run_id: :return: """ with with_db_session(engine_abat) as session: sql_str = str( session.query( func.concat( AccountStatusInfo.trade_date, ' ', AccountStatusInfo.trade_time).label('trade_datetime'), AccountStatusInfo.available_cash.label('available_cash'), AccountStatusInfo.curr_margin.label('curr_margin'), AccountStatusInfo.balance_tot.label('balance_tot')).filter( AccountStatusInfo.stg_run_id == stg_run_id).order_by( AccountStatusInfo.trade_date, AccountStatusInfo.trade_time)) # sql_str = """SELECT concat(trade_date, " ", trade_time) trade_datetime, available_cash, curr_margin, balance_tot # FROM account_status_info where stg_run_id=%s order by trade_date, trade_time""" data_df = pd.read_sql(sql_str, engine_abat, params=[' ', stg_run_id]) data_df["return_rate"] = (data_df["balance_tot"].pct_change().fillna(0) + 1).cumprod() data_df = data_df.set_index("trade_datetime") return data_df
def init(): from abat.backend import engine_abat BaseModel.metadata.create_all(engine_abat) with with_db_session(engine_abat) as session: for table_name, _ in BaseModel.metadata.tables.items(): sql_str = "ALTER TABLE %s ENGINE = MyISAM" % table_name session.execute(sql_str) print("所有表结构建立完成")
def remove_order_info(stg_run_id: int): """ 仅作为调试工具使用,删除指定 stg_run_id 相关的 order_info :param stg_run_id: :return: """ with with_db_session(engine_abat) as session: session.execute('DELETE FROM order_info WHERE stg_run_id=:stg_run_id', {'stg_run_id': stg_run_id}) session.commit()
def release(self): try: with with_db_session(engine_abat) as session: session.add_all(self.order_info_list) session.add_all(self.trade_info_list) session.add_all(self.pos_status_info_dic.values()) session.add_all(self.account_info_list) session.commit() except: self.logger.exception("release exception")
def remove_order_info(stg_run_id: int): """ 仅作为调试工具使用,删除指定 stg_run_id 相关的 order_info :param stg_run_id: :return: """ with with_db_session(engine_abat) as session: session.execute('DELETE FROM order_info WHERE stg_run_id=:stg_run_id', {'stg_run_id': stg_run_id}) session.commit()
def update_by_pos_status_info(self, pos_status_info_dic, md: dict): """ 根据 持仓列表更新账户信息 :param pos_status_info_dic: :return: """ account_status_info = self.create_by_self() # 上一次更新日期、时间 # trade_date_last, trade_time_last, trade_millisec_last = \ # account_status_info.trade_date, account_status_info.trade_time, account_status_info.trade_millisec # 更新日期、时间 trade_date = md['ActionDay'] trade_time = pd_timedelta_2_timedelta(md['ActionTime']) trade_millisec = int(md.setdefault('ActionMillisec', 0)) available_cash_chg = 0 curr_margin = 0 close_profit = 0 position_profit = 0 floating_pl_chg = 0 margin_chg = 0 floating_pl_cum = 0 for instrument_id, pos_status_info in pos_status_info_dic.items(): curr_margin += pos_status_info.margin if pos_status_info.position == 0: close_profit += pos_status_info.floating_pl else: position_profit += pos_status_info.floating_pl floating_pl_chg += pos_status_info.floating_pl_chg margin_chg += pos_status_info.margin_chg floating_pl_cum += pos_status_info.floating_pl_cum available_cash_chg = floating_pl_chg - margin_chg account_status_info.curr_margin = curr_margin # # 对于同一时间,平仓后又开仓的情况,不能将close_profit重置为0 # if trade_date == trade_date_last and trade_time == trade_time_last and trade_millisec == trade_millisec_last: # account_status_info.close_profit += close_profit # else: # 一个单位时段只允许一次,不需要考虑上面的情况 account_status_info.close_profit = close_profit account_status_info.position_profit = position_profit account_status_info.available_cash += available_cash_chg account_status_info.floating_pl_cum = floating_pl_cum account_status_info.balance_tot = account_status_info.available_cash + curr_margin account_status_info.trade_date = trade_date account_status_info.trade_time = trade_time account_status_info.trade_millisec = trade_millisec if UPDATE_OR_INSERT_PER_ACTION: # 更新最新持仓纪录 with with_db_session(engine_abat, expire_on_commit=False) as session: session.add(account_status_info) session.commit() return account_status_info
def update_by_pos_status_info(self, pos_status_info_dic, md: dict): """ 根据 持仓列表更新账户信息 :param pos_status_info_dic: :return: """ account_status_info = self.create_by_self() # 上一次更新日期、时间 # trade_date_last, trade_time_last, trade_millisec_last = \ # account_status_info.trade_date, account_status_info.trade_time, account_status_info.trade_millisec # 更新日期、时间 trade_date = md['ActionDay'] trade_time = pd_timedelta_2_timedelta(md['ActionTime']) trade_millisec = int(md.setdefault('ActionMillisec', 0)) available_cash_chg = 0 curr_margin = 0 close_profit = 0 position_profit = 0 floating_pl_chg = 0 margin_chg = 0 floating_pl_cum = 0 for instrument_id, pos_status_info in pos_status_info_dic.items(): curr_margin += pos_status_info.margin if pos_status_info.position == 0: close_profit += pos_status_info.floating_pl else: position_profit += pos_status_info.floating_pl floating_pl_chg += pos_status_info.floating_pl_chg margin_chg += pos_status_info.margin_chg floating_pl_cum += pos_status_info.floating_pl_cum available_cash_chg = floating_pl_chg - margin_chg account_status_info.curr_margin = curr_margin # # 对于同一时间,平仓后又开仓的情况,不能将close_profit重置为0 # if trade_date == trade_date_last and trade_time == trade_time_last and trade_millisec == trade_millisec_last: # account_status_info.close_profit += close_profit # else: # 一个单位时段只允许一次,不需要考虑上面的情况 account_status_info.close_profit = close_profit account_status_info.position_profit = position_profit account_status_info.available_cash += available_cash_chg account_status_info.floating_pl_cum = floating_pl_cum account_status_info.balance_tot = account_status_info.available_cash + curr_margin account_status_info.trade_date = trade_date account_status_info.trade_time = trade_time account_status_info.trade_millisec = trade_millisec if UPDATE_OR_INSERT_PER_ACTION: # 更新最新持仓纪录 with with_db_session(engine_abat, expire_on_commit=False) as session: session.add(account_status_info) session.commit() return account_status_info
def _update_by_pos_status_info(self) -> AccountStatusInfo: """根据 持仓列表更新账户信息""" pos_status_info_dic, md = self._pos_status_info_dic, self.curr_md account_status_info = self._account_status_info.create_by_self() # 上一次更新日期、时间 # trade_date_last, trade_time_last, trade_millisec_last = \ # account_status_info.trade_date, account_status_info.trade_time, account_status_info.trade_millisec # 更新日期、时间 trade_date = md['ts_start'].date() trade_time = md['ts_start'].time() trade_millisec = 0 available_cash_chg = 0 curr_margin = 0 close_profit = 0 position_profit = 0 floating_pl_chg = 0 margin_chg = 0 floating_pl_cum = 0 for instrument_id, pos_status_info in pos_status_info_dic.items(): curr_margin += pos_status_info.margin if pos_status_info.position == 0: close_profit += pos_status_info.floating_pl else: position_profit += pos_status_info.floating_pl floating_pl_chg += pos_status_info.floating_pl_chg margin_chg += pos_status_info.margin_chg floating_pl_cum += pos_status_info.floating_pl_cum available_cash_chg = floating_pl_chg - margin_chg account_status_info.curr_margin = curr_margin # # 对于同一时间,平仓后又开仓的情况,不能将close_profit重置为0 # if trade_date == trade_date_last and trade_time == trade_time_last and trade_millisec == trade_millisec_last: # account_status_info.close_profit += close_profit # else: # 一个单位时段只允许一次,不需要考虑上面的情况 account_status_info.close_profit = close_profit account_status_info.position_profit = position_profit account_status_info.available_cash += available_cash_chg account_status_info.floating_pl_cum = floating_pl_cum account_status_info.balance_tot = account_status_info.available_cash + curr_margin account_status_info.trade_date = trade_date account_status_info.trade_time = trade_time account_status_info.trade_millisec = trade_millisec if Config.UPDATE_OR_INSERT_PER_ACTION: # 更新最新持仓纪录 with with_db_session(engine_abat, expire_on_commit=False) as session: session.add(account_status_info) session.commit() return account_status_info
def connect(self): with with_db_session(engine_md) as session: data = session.query(SymbolPair).all() self.symbol_currency_dic = { f'{sym.base_currency}{sym.quote_currency}': sym.base_currency for sym in data } self.symbol_precision_dic = { f'{sym.base_currency}{sym.quote_currency}': (int(sym.price_precision), int(sym.amount_precision)) for sym in data }
def stg_run_ending(self): """ 处理策略结束相关事项 释放策略资源 更新策略执行信息 :return: """ self.stg_base.release() # 更新数据库 td_to 字段 with with_db_session(engine_abat) as session: session.query(StgRunInfo).filter(StgRunInfo.stg_run_id == self.stg_run_id).update( {StgRunInfo.dt_to: datetime.now()}) # sql_str = StgRunInfo.update().where( # StgRunInfo.c.stg_run_id == self.stg_run_id).values(dt_to=datetime.now()) # session.execute(sql_str) session.commit()
def _update_pos_status_info_by_md(self, pos_status_info_last) -> PosStatusInfo: """创建新的对象,根据 trade_info 更新相关信息""" md = self.curr_md trade_date = md['ts_start'].date() trade_time = md['ts_start'].time() trade_millisec = 0 trade_price = float(md['close']) instrument_id = md['pair'] pos_status_info = pos_status_info_last.create_by_self() pos_status_info.cur_price = trade_price pos_status_info.trade_date = trade_date pos_status_info.trade_time = trade_time pos_status_info.trade_millisec = trade_millisec # 计算 floating_pl margin # instrument_info = Config.instrument_info_dic[instrument_id] # multiple = instrument_info['VolumeMultiple'] # margin_ratio = instrument_info['LongMarginRatio'] multiple, margin_ratio = 1, 1 position = pos_status_info.position cur_price = pos_status_info.cur_price avg_price = pos_status_info.avg_price pos_status_info.margin = position * cur_price * multiple * margin_ratio pos_status_info.margin_chg = pos_status_info.margin - pos_status_info_last.margin if pos_status_info.direction == Direction.Long: pos_status_info.floating_pl = (cur_price - avg_price) * position * multiple else: pos_status_info.floating_pl = (avg_price - cur_price) * position * multiple pos_status_info.floating_pl_chg = pos_status_info.floating_pl - pos_status_info_last.floating_pl pos_status_info.floating_pl_cum += pos_status_info.floating_pl_chg if Config.UPDATE_OR_INSERT_PER_ACTION: # 更新最新持仓纪录 with with_db_session(engine_abat, expire_on_commit=False) as session: session.add(pos_status_info) session.commit() return pos_status_info
def _create_account_status_info(self) -> AccountStatusInfo: stg_run_id, init_cash, md = self.stg_run_id, self.init_cash, self.curr_md trade_date = md['ts_start'].date() trade_time = md['ts_start'].time() trade_millisec = 0 # trade_price = float(self.curr_md['close']) acc_status_info = AccountStatusInfo( stg_run_id=stg_run_id, trade_date=trade_date, trade_time=trade_time, trade_millisec=trade_millisec, available_cash=init_cash, balance_tot=init_cash, ) if Config.UPDATE_OR_INSERT_PER_ACTION: # 更新最新持仓纪录 with with_db_session(engine_abat, expire_on_commit=False) as session: session.add(acc_status_info) session.commit() return acc_status_info
def update_by_md(self, md: dict): """ 创建新的对象,根据 trade_info 更新相关信息 :param md: :return: """ trade_date = md['ActionDay'] trade_time = pd_timedelta_2_timedelta(md['ActionTime']) trade_millisec = int(md.setdefault('ActionMillisec', 0)) trade_price = float(md['close']) instrument_id = md['InstrumentID'] pos_status_info = self.create_by_self() pos_status_info.cur_price = trade_price pos_status_info.trade_date = trade_date pos_status_info.trade_time = trade_time pos_status_info.trade_millisec = trade_millisec # 计算 floating_pl margin # instrument_info = Config.instrument_info_dic[instrument_id] # multiple = instrument_info['VolumeMultiple'] # margin_ratio = instrument_info['LongMarginRatio'] multiple, margin_ratio = 1, 1 position = pos_status_info.position cur_price = pos_status_info.cur_price avg_price = pos_status_info.avg_price pos_status_info.margin = position * cur_price * multiple * margin_ratio pos_status_info.margin_chg = pos_status_info.margin - self.margin if pos_status_info.direction == Direction.Long: pos_status_info.floating_pl = (cur_price - avg_price) * position * multiple else: pos_status_info.floating_pl = (avg_price - cur_price) * position * multiple pos_status_info.floating_pl_chg = pos_status_info.floating_pl - self.floating_pl pos_status_info.floating_pl_cum += pos_status_info.floating_pl_chg if UPDATE_OR_INSERT_PER_ACTION: # 更新最新持仓纪录 with with_db_session(engine_abat, expire_on_commit=False) as session: session.add(pos_status_info) session.commit() return pos_status_info
def update_by_md(self, md: dict): """ 创建新的对象,根据 trade_info 更新相关信息 :param md: :return: """ trade_date = md['ActionDay'] trade_time = pd_timedelta_2_timedelta(md['ActionTime']) trade_millisec = int(md.setdefault('ActionMillisec', 0)) trade_price = float(md['close']) instrument_id = md['InstrumentID'] pos_status_info = self.create_by_self() pos_status_info.cur_price = trade_price pos_status_info.trade_date = trade_date pos_status_info.trade_time = trade_time pos_status_info.trade_millisec = trade_millisec # 计算 floating_pl margin # instrument_info = Config.instrument_info_dic[instrument_id] # multiple = instrument_info['VolumeMultiple'] # margin_ratio = instrument_info['LongMarginRatio'] multiple, margin_ratio = 1, 1 position = pos_status_info.position cur_price = pos_status_info.cur_price avg_price = pos_status_info.avg_price pos_status_info.margin = position * cur_price * multiple * margin_ratio pos_status_info.margin_chg = pos_status_info.margin - self.margin if pos_status_info.direction == Direction.Long: pos_status_info.floating_pl = (cur_price - avg_price) * position * multiple else: pos_status_info.floating_pl = (avg_price - cur_price) * position * multiple pos_status_info.floating_pl_chg = pos_status_info.floating_pl - self.floating_pl pos_status_info.floating_pl_cum += pos_status_info.floating_pl_chg if UPDATE_OR_INSERT_PER_ACTION: # 更新最新持仓纪录 with with_db_session(engine_abat, expire_on_commit=False) as session: session.add(pos_status_info) session.commit() return pos_status_info
def _save_order_info(self, instrument_id, price: float, vol: int, direction: Direction, action: Action): order_date = self.curr_md['ts_start'].date() order_info = OrderInfo(stg_run_id=self.stg_run_id, order_date=order_date, order_time=self.curr_md['ts_start'].time(), order_millisec=0, direction=int(direction), action=int(action), instrument_id=instrument_id, order_price=float(price), order_vol=int(vol)) if False: # 暂时不用 with with_db_session(engine_abat, expire_on_commit=False) as session: session.add(order_info) session.commit() self.order_info_list.append(order_info) self._order_info_dic.setdefault(instrument_id, []).append(order_info) # 更新成交信息 # Order_2_Deal 模式:下单即成交 if self.trade_mode == BacktestTradeMode.Order_2_Deal: self._save_trade_info(order_info)
def get_account_balance(stg_run_id): """ 获取 account_info 账户走势数据 :param stg_run_id: :return: """ with with_db_session(engine_abat) as session: sql_str = str( session.query( func.concat(AccountStatusInfo.trade_date, ' ', AccountStatusInfo.trade_time).label('trade_datetime'), AccountStatusInfo.available_cash.label('available_cash'), AccountStatusInfo.curr_margin.label('curr_margin'), AccountStatusInfo.balance_tot.label('balance_tot') ).filter(AccountStatusInfo.stg_run_id == stg_run_id).order_by( AccountStatusInfo.trade_date, AccountStatusInfo.trade_time ) ) # sql_str = """SELECT concat(trade_date, " ", trade_time) trade_datetime, available_cash, curr_margin, balance_tot # FROM account_status_info where stg_run_id=%s order by trade_date, trade_time""" data_df = pd.read_sql(sql_str, engine_abat, params=[' ', stg_run_id]) data_df["return_rate"] = (data_df["balance_tot"].pct_change().fillna(0) + 1).cumprod() data_df = data_df.set_index("trade_datetime") return data_df
def init(alter_table=False): BaseModel.metadata.create_all(engine_md) if alter_table: with with_db_session(engine=engine_md) as session: for table_name, _ in BaseModel.metadata.tables.items(): sql_str = f"show table status from {Config.DB_SCHEMA_MD} where name=:table_name" row_data = session.execute(sql_str, params={'table_name': table_name}).first() if row_data is None: continue if row_data[1].lower() == 'myisam': continue logger.info('修改 %s 表引擎为 MyISAM', table_name) sql_str = "ALTER TABLE %s ENGINE = MyISAM" % table_name session.execute(sql_str) # This is an issue https://www.mail-archive.com/[email protected]/msg19744.html session.execute(f"ALTER TABLE {SymbolPair.__tablename__} CHANGE COLUMN `id` `id` INT(11) NULL AUTO_INCREMENT") session.commit() # This is an issue https://www.mail-archive.com/[email protected]/msg19744.html session.execute(f"ALTER TABLE {MDTick.__tablename__} CHANGE COLUMN `id` `id` INT(11) NULL AUTO_INCREMENT") session.commit() logger.info("所有表结构建立完成")
account_status_info.close_profit = close_profit account_status_info.position_profit = position_profit account_status_info.available_cash += available_cash_chg account_status_info.floating_pl_cum = floating_pl_cum account_status_info.balance_tot = account_status_info.available_cash + curr_margin account_status_info.trade_date = trade_date account_status_info.trade_time = trade_time account_status_info.trade_millisec = trade_millisec if UPDATE_OR_INSERT_PER_ACTION: # 更新最新持仓纪录 with with_db_session(engine_abat, expire_on_commit=False) as session: session.add(account_status_info) session.commit() return account_status_info if __name__ == "__main__": from abat.backend import engine_abat BaseModel.metadata.create_all(engine_abat) with with_db_session(engine_abat) as session: for table_name, _ in BaseModel.metadata.tables.items(): sql_str = "ALTER TABLE %s ENGINE = MyISAM" % table_name session.execute(sql_str) print("所有表结构建立完成") # 创建user表,继承metadata类 # Engine使用Schama Type创建一个特定的结构对象 # stg_info_table = Table("stg_info", metadata, autoload=True)
def factory(stg_class_obj: StgBase.__class__, strategy_params, md_agent_params_list, run_mode: RunMode, **run_mode_params): """ 建立策略对象 建立数据库相应记录信息 根据运行模式(实时、回测):选择相应的md_agent以及trade_agent :param stg_class_obj: 策略类型 StgBase 的子类 :param strategy_params: 策略参数 :param md_agent_params_list: 行情代理(md_agent)参数,支持同时订阅多周期、多品种, 例如:同时订阅 [ethusdt, eosusdt] 1min 行情、[btcusdt, ethbtc] tick 行情 :param run_mode: 运行模式 RunMode.Realtime 或 RunMode.Backtest :param run_mode_params: 运行参数,回测模式下:运行起止时间,实时行情下:加载定时器等设置 :return: 策略执行对象实力 """ stg_run_info = StgRunInfo(stg_name=stg_class_obj.__name__, # '{.__name__}'.format(stg_class_obj) dt_from=datetime.now(), dt_to=None, stg_params=json.dumps(strategy_params), md_agent_params_list=json.dumps(md_agent_params_list), run_mode=int(run_mode), run_mode_params=json.dumps(run_mode_params)) with with_db_session(engine_abat) as session: session.add(stg_run_info) session.commit() stg_run_id = stg_run_info.stg_run_id # 设置运行模式:回测模式,实时模式。初始化交易接口 # if run_mode == RunMode.Backtest: # trade_agent = BacktestTraderAgent(stg_run_id, run_mode_params) # elif run_mode == RunMode.Realtime: # trade_agent = RealTimeTraderAgent(stg_run_id, run_mode_params) # else: # raise ValueError('run_mode %d error' % run_mode) trade_agent_class = trader_agent_class_dic[run_mode] # 初始化策略实体,传入参数 stg_base = stg_class_obj(**strategy_params) # 设置策略交易接口 trade_agent,这里不适用参数传递的方式而使用属性赋值, # 因为stg子类被继承后,参数主要用于设置策略所需各种参数使用 stg_base.trade_agent = trade_agent_class(stg_run_id, run_mode_params) # 对不同周期设置相应的md_agent # 初始化各个周期的 md_agent md_period_agent_dic = {} for md_agent_param in md_agent_params_list: period = md_agent_param['md_period'] md_agent = MdAgentBase.factory(run_mode, **md_agent_param) md_period_agent_dic[period] = md_agent # 对各个周期分别加载历史数据,设置对应 handler # 通过 md_agent 加载各个周期的历史数据 md_df = md_agent.load_history() if md_df is None: StgHandlerBase.logger.warning('加载 %s 历史数据为 None', period) continue if isinstance(md_df, dict): his_df_dic = md_df md_df = his_df_dic['md_df'] else: warnings.warn('load_history 返回 df 数据格式即将废弃,请更新成 dict', DeprecationWarning) context = {ContextKey.instrument_id_list: list(md_agent.instrument_id_set)} stg_base.load_md_period_df(period, md_df, context) StgHandlerBase.logger.debug('加载 %s 历史数据 %s 条', period, 'None' if md_df is None else str(md_df.shape[0])) # 初始化 StgHandlerBase 实例 if run_mode == RunMode.Realtime: stg_handler = StgHandlerRealtime(stg_run_id=stg_run_id, stg_base=stg_base, md_period_agent_dic=md_period_agent_dic, **run_mode_params) elif run_mode == RunMode.Backtest: stg_handler = StgHandlerBacktest(stg_run_id=stg_run_id, stg_base=stg_base, md_period_agent_dic=md_period_agent_dic, **run_mode_params) else: raise ValueError('run_mode %d error' % run_mode) StgHandlerBase.logger.debug('初始化 %r 完成', stg_handler) return stg_handler
def load_history(self, date_from=None, date_to=None, load_md_count=None) -> (pd.DataFrame, dict): """ 从mysql中加载历史数据 实时行情推送时进行合并后供数据分析使用 :param date_from: None代表沿用类的 init_md_date_from 属性 :param date_to: None代表沿用类的 init_md_date_from 属性 :param load_md_count: 0 代表不限制,None代表沿用类的 init_load_md_count 属性,其他数字代表相应的最大加载条数 :return: md_df 或者 ret_data { 'md_df': md_df, 'datetime_key': 'ts_start', 'date_key': **, 'time_key': **, 'microseconds_key': ** } """ # 如果 init_md_date_from 以及 init_md_date_to 为空,则不加载历史数据 if self.init_md_date_from is None and self.init_md_date_to is None: ret_data = {'md_df': None, 'datetime_key': 'ts_start'} return ret_data if self.md_period == PeriodType.Tick: # sql_str = """SELECT * FROM md_tick # WHERE InstrumentID IN (%s) %s # ORDER BY ActionDay DESC, ActionTime DESC, ActionMillisec DESC %s""" raise ValueError("暂不支持 tick 级回测") elif self.md_period == PeriodType.Min1: # 将sql 语句形势改成由 sqlalchemy 进行sql 拼装方式 # sql_str = """select * from md_min_1 # where InstrumentID in ('j1801') and tradingday>='2017-08-14' # order by ActionDay, ActionTime, ActionMillisec limit 200""" # sql_str = """SELECT * FROM md_min_1 # WHERE InstrumentID IN (%s) %s # ORDER BY ActionDay DESC, ActionTime DESC %s""" with with_db_session(engine_md) as session: query = session.query( MDMin1.symbol.label('pair'), MDMin1.ts_start.label('ts_start'), MDMin1.open.label('open'), MDMin1.high.label('high'), MDMin1.low.label('low'), MDMin1.close.label('close'), MDMin1.vol.label('vol'), MDMin1.amount.label('amount'), MDMin1.count.label('count')).filter( MDMin1.symbol.in_(self.instrument_id_set)).order_by( MDMin1.ts_start.desc()) # 设置参数 params = list(self.instrument_id_set) # date_from 起始日期 if date_from is None: date_from = self.init_md_date_from if date_from is not None: # qry_str_date_from = " and tradingday>='%s'" % date_from query = query.filter(MDMin1.ts_start >= date_from) params.append(date_from) # date_to 截止日期 if date_to is None: date_to = self.init_md_date_to if date_to is not None: # qry_str_date_to = " and tradingday<='%s'" % date_to query = query.filter(MDMin1.ts_start <= date_to) params.append(date_to) # load_limit 最大记录数 if load_md_count is None: load_md_count = self.init_load_md_count if load_md_count is not None and load_md_count > 0: qry_str_limit = " limit %d" % load_md_count query = query.limite(load_md_count) params.append(load_md_count) sql_str = str(query) else: raise ValueError('%s error' % self.md_period) # 合约列表 # qry_str_inst_list = "'" + "', '".join(self.instrument_id_set) + "'" # 拼接sql # qry_sql_str = sql_str % (qry_str_inst_list, qry_str_date_from + qry_str_date_to, qry_str_limit) # 加载历史数据 md_df = pd.read_sql(sql_str, engine_md, params=params) # self.md_df = md_df ret_data = {'md_df': md_df, 'datetime_key': 'ts_start'} return ret_data
def update_by_trade_info(self, trade_info: TradeInfo): """ 创建新的对象,根据 trade_info 更新相关信息 :param trade_info: :return: """ # 复制前一个持仓状态 pos_status_info = self.create_by_self() direction, action, instrument_id = trade_info.direction, trade_info.action, trade_info.instrument_id trade_price, trade_vol, trade_id = trade_info.trade_price, trade_info.trade_vol, trade_info.trade_id trade_date, trade_time, trade_millisec = trade_info.trade_date, trade_info.trade_time, trade_info.trade_millisec # 获取合约信息 # instrument_info = Config.instrument_info_dic[instrument_id] # multiple = instrument_info['VolumeMultiple'] # margin_ratio = instrument_info['LongMarginRatio'] multiple, margin_ratio = 1, 1 # 计算仓位、方向、平均价格 pos_direction, position, avg_price = pos_status_info.direction, pos_status_info.position, pos_status_info.avg_price if pos_direction == direction: if action == Action.Open: # 方向相同:开仓:加仓; pos_status_info.avg_price = (position * avg_price + trade_price * trade_vol) / (position + trade_vol) pos_status_info.position = position + trade_vol else: # 方向相同:关仓:减仓; if trade_vol > position: raise ValueError("当前持仓%d,平仓%d,错误" % (position, trade_vol)) elif trade_vol == position: # 清仓前计算浮动收益 # 未清仓的情况将在下面的代码中统一计算浮动收益 if pos_status_info.direction == Direction.Long: pos_status_info.floating_pl = (trade_price - avg_price) * position * multiple else: pos_status_info.floating_pl = (avg_price - trade_price) * position * multiple pos_status_info.avg_price = 0 pos_status_info.position = 0 else: pos_status_info.avg_price = (position * avg_price - trade_price * trade_vol) / ( position - trade_vol) pos_status_info.position = position - trade_vol elif position == 0: pos_status_info.avg_price = trade_price pos_status_info.position = trade_vol pos_status_info.direction = direction else: # 方向相反 raise ValueError("当前仓位:%s %d手,目标操作:%s %d手,请先平仓在开仓" % ( "多头" if pos_direction == Direction.Long else "空头", position, "多头" if direction == Direction.Long else "空头", trade_vol, )) # if position == trade_vol: # # 方向相反,量相同:清仓 # pos_status_info.avg_price = 0 # pos_status_info.position = 0 # else: # holding_amount = position * avg_price # trade_amount = trade_price * trade_vol # position_rest = position - trade_vol # avg_price = (holding_amount - trade_amount) / position_rest # if position > trade_vol: # # 减仓 # pos_status_info.avg_price = avg_price # pos_status_info.position = position_rest # else: # # 多空反手 # self.logger.warning("%s 持%s:%d -> %d 多空反手", self.instrument_id, # '多' if direction == int(Direction.Long) else '空', position, position_rest) # pos_status_info.avg_price = avg_price # pos_status_info.position = position_rest # pos_status_info.direction = Direction.Short if direction == int(Direction.Short) else Direction.Long # 设置其他属性 pos_status_info.cur_price = trade_price pos_status_info.trade_date = trade_date pos_status_info.trade_time = trade_time pos_status_info.trade_millisec = trade_millisec # 计算 floating_pl margin position = pos_status_info.position # cur_price = pos_status_info.cur_price avg_price = pos_status_info.avg_price pos_status_info.margin = position * trade_price * multiple * margin_ratio # 如果当前仓位不为 0 则计算浮动收益 if position > 0: if pos_status_info.direction == Direction.Long: pos_status_info.floating_pl = (trade_price - avg_price) * position * multiple else: pos_status_info.floating_pl = (avg_price - trade_price) * position * multiple # 如果前一状态仓位为 0 则不进行差值计算 if self.position == 0: pos_status_info.margin_chg = pos_status_info.margin pos_status_info.floating_pl_chg = pos_status_info.floating_pl else: pos_status_info.margin_chg = pos_status_info.margin - self.margin pos_status_info.floating_pl_chg = pos_status_info.floating_pl - self.floating_pl pos_status_info.floating_pl_cum += pos_status_info.floating_pl_chg if UPDATE_OR_INSERT_PER_ACTION: # 更新最新持仓纪录 with with_db_session(engine_abat, expire_on_commit=False) as session: session.add(pos_status_info) session.commit() return pos_status_info
def load_history(self, date_from=None, date_to=None, load_md_count=None)->(pd.DataFrame, dict): """ 从mysql中加载历史数据 实时行情推送时进行合并后供数据分析使用 :param date_from: None代表沿用类的 init_md_date_from 属性 :param date_to: None代表沿用类的 init_md_date_from 属性 :param load_md_count: 0 代表不限制,None代表沿用类的 init_load_md_count 属性,其他数字代表相应的最大加载条数 :return: md_df 或者 ret_data { 'md_df': md_df, 'datetime_key': 'ts_start', 'date_key': **, 'time_key': **, 'microseconds_key': ** } """ # 如果 init_md_date_from 以及 init_md_date_to 为空,则不加载历史数据 if self.init_md_date_from is None and self.init_md_date_to is None: ret_data = {'md_df': None, 'datetime_key': 'ts_start'} return ret_data if self.md_period == PeriodType.Tick: sql_str = """SELECT * FROM md_tick WHERE InstrumentID IN (%s) %s ORDER BY ActionDay DESC, ActionTime DESC, ActionMillisec DESC %s""" elif self.md_period == PeriodType.Min1: # 将sql 语句形势改成由 sqlalchemy 进行sql 拼装方式 # sql_str = """select * from md_min_1 # where InstrumentID in ('j1801') and tradingday>='2017-08-14' # order by ActionDay, ActionTime, ActionMillisec limit 200""" # sql_str = """SELECT * FROM md_min_1 # WHERE InstrumentID IN (%s) %s # ORDER BY ActionDay DESC, ActionTime DESC %s""" with with_db_session(engine_md) as session: query = session.query( MDMin1.pair.label('pair'), MDMin1.ts_start.label('ts_start'), MDMin1.open.label('open'), MDMin1.high.label('high'), MDMin1.low.label('low'), MDMin1.close.label('close'), MDMin1.vol.label('vol'), MDMin1.amount.label('amount'), MDMin1.count.label('count') ).filter( MDMin1.pair.in_(self.instrument_id_set) ).order_by(MDMin1.ts_start.desc()) # 设置参数 params = list(self.instrument_id_set) # date_from 起始日期 if date_from is None: date_from = self.init_md_date_from if date_from is not None: # qry_str_date_from = " and tradingday>='%s'" % date_from query = query.filter(MDMin1.ts_start >= date_from) params.append(date_from) # date_to 截止日期 if date_to is None: date_to = self.init_md_date_to if date_to is not None: # qry_str_date_to = " and tradingday<='%s'" % date_to query = query.filter(MDMin1.ts_start <= date_to) params.append(date_to) # load_limit 最大记录数 if load_md_count is None: load_md_count = self.init_load_md_count if load_md_count is not None and load_md_count > 0: qry_str_limit = " limit %d" % load_md_count query = query.limite(load_md_count) params.append(load_md_count) sql_str = str(query) else: raise ValueError('%s error' % self.md_period) # 合约列表 # qry_str_inst_list = "'" + "', '".join(self.instrument_id_set) + "'" # 拼接sql # qry_sql_str = sql_str % (qry_str_inst_list, qry_str_date_from + qry_str_date_to, qry_str_limit) # 加载历史数据 md_df = pd.read_sql(sql_str, engine_md, params=params) # self.md_df = md_df ret_data = {'md_df': md_df, 'datetime_key': 'ts_start'} return ret_data
session.query( func.concat(AccountStatusInfo.trade_date, ' ', AccountStatusInfo.trade_time).label('trade_datetime'), AccountStatusInfo.available_cash.label('available_cash'), AccountStatusInfo.curr_margin.label('curr_margin'), AccountStatusInfo.balance_tot.label('balance_tot') ).filter(AccountStatusInfo.stg_run_id == stg_run_id).order_by( AccountStatusInfo.trade_date, AccountStatusInfo.trade_time ) ) # sql_str = """SELECT concat(trade_date, " ", trade_time) trade_datetime, available_cash, curr_margin, balance_tot # FROM account_status_info where stg_run_id=%s order by trade_date, trade_time""" data_df = pd.read_sql(sql_str, engine_abat, params=[' ', stg_run_id]) data_df["return_rate"] = (data_df["balance_tot"].pct_change().fillna(0) + 1).cumprod() data_df = data_df.set_index("trade_datetime") return data_df if __name__ == "__main__": with with_db_session(engine_abat) as session: # stg_run_id = session.execute("select max(stg_run_id) from stg_run_info").fetchone()[0] stg_run_id = session.query(func.max(StgRunInfo.stg_run_id)).scalar() # stg_run_id = 2 data_df = get_account_balance(stg_run_id) logger.info("\n%s", data_df) data_df.plot(ylim=[min(data_df["available_cash"]), max(data_df["balance_tot"])]) data_df.plot(ylim=[min(data_df["curr_margin"]), max(data_df["curr_margin"])]) stat_df = return_risk_analysis(data_df[['return_rate']], freq=None) logger.info("\n%s", stat_df) plt.show()
def update_by_trade_info(self, trade_info: TradeInfo): """ 创建新的对象,根据 trade_info 更新相关信息 :param trade_info: :return: """ # 复制前一个持仓状态 pos_status_info = self.create_by_self() direction, action, instrument_id = trade_info.direction, trade_info.action, trade_info.instrument_id trade_price, trade_vol, trade_id = trade_info.trade_price, trade_info.trade_vol, trade_info.trade_id trade_date, trade_time, trade_millisec = trade_info.trade_date, trade_info.trade_time, trade_info.trade_millisec # 获取合约信息 # instrument_info = Config.instrument_info_dic[instrument_id] # multiple = instrument_info['VolumeMultiple'] # margin_ratio = instrument_info['LongMarginRatio'] multiple, margin_ratio = 1, 1 # 计算仓位、方向、平均价格 pos_direction, position, avg_price = pos_status_info.direction, pos_status_info.position, pos_status_info.avg_price if pos_direction == direction: if action == Action.Open: # 方向相同:开仓:加仓; pos_status_info.avg_price = (position * avg_price + trade_price * trade_vol) / (position + trade_vol) pos_status_info.position = position + trade_vol else: # 方向相同:关仓:减仓; if trade_vol > position: raise ValueError("当前持仓%d,平仓%d,错误" % (position, trade_vol)) elif trade_vol == position: # 清仓前计算浮动收益 # 未清仓的情况将在下面的代码中统一计算浮动收益 if pos_status_info.direction == Direction.Long: pos_status_info.floating_pl = (trade_price - avg_price) * position * multiple else: pos_status_info.floating_pl = (avg_price - trade_price) * position * multiple pos_status_info.avg_price = 0 pos_status_info.position = 0 else: pos_status_info.avg_price = (position * avg_price - trade_price * trade_vol) / ( position - trade_vol) pos_status_info.position = position - trade_vol elif position == 0: pos_status_info.avg_price = trade_price pos_status_info.position = trade_vol pos_status_info.direction = direction else: # 方向相反 raise ValueError("当前仓位:%s %d手,目标操作:%s %d手,请先平仓在开仓" % ( "多头" if pos_direction == Direction.Long else "空头", position, "多头" if direction == Direction.Long else "空头", trade_vol, )) # if position == trade_vol: # # 方向相反,量相同:清仓 # pos_status_info.avg_price = 0 # pos_status_info.position = 0 # else: # holding_amount = position * avg_price # trade_amount = trade_price * trade_vol # position_rest = position - trade_vol # avg_price = (holding_amount - trade_amount) / position_rest # if position > trade_vol: # # 减仓 # pos_status_info.avg_price = avg_price # pos_status_info.position = position_rest # else: # # 多空反手 # self.logger.warning("%s 持%s:%d -> %d 多空反手", self.instrument_id, # '多' if direction == int(Direction.Long) else '空', position, position_rest) # pos_status_info.avg_price = avg_price # pos_status_info.position = position_rest # pos_status_info.direction = Direction.Short if direction == int(Direction.Short) else Direction.Long # 设置其他属性 pos_status_info.cur_price = trade_price pos_status_info.trade_date = trade_date pos_status_info.trade_time = trade_time pos_status_info.trade_millisec = trade_millisec # 计算 floating_pl margin position = pos_status_info.position # cur_price = pos_status_info.cur_price avg_price = pos_status_info.avg_price pos_status_info.margin = position * trade_price * multiple * margin_ratio # 如果当前仓位不为 0 则计算浮动收益 if position > 0: if pos_status_info.direction == Direction.Long: pos_status_info.floating_pl = (trade_price - avg_price) * position * multiple else: pos_status_info.floating_pl = (avg_price - trade_price) * position * multiple # 如果前一状态仓位为 0 则不进行差值计算 if self.position == 0: pos_status_info.margin_chg = pos_status_info.margin pos_status_info.floating_pl_chg = pos_status_info.floating_pl else: pos_status_info.margin_chg = pos_status_info.margin - self.margin pos_status_info.floating_pl_chg = pos_status_info.floating_pl - self.floating_pl pos_status_info.floating_pl_cum += pos_status_info.floating_pl_chg if UPDATE_OR_INSERT_PER_ACTION: # 更新最新持仓纪录 with with_db_session(engine_abat, expire_on_commit=False) as session: session.add(pos_status_info) session.commit() return pos_status_info