def save(self, instance_id):
if self._mode != MODE_BACKTEST:
raise RuntimeError("You only can save data in backtest mode")
# 入库前保证属性没有被篡改
validate(instance=self, schema=indices_input)
for name in self:
if isinstance(self[name], int):
sql_param = (PARAM_TYPE_INTEGER, str(self[name]), instance_id, name)
elif isinstance(self[name], float):
sql_param = (PARAM_TYPE_FLOAT, str(self[name]), instance_id, name)
else:
sql_param = (PARAM_TYPE_STRING, self[name], instance_id, name)
conn = Conn(self._db_name)
one = conn.query_one(
"SELECT * FROM {} WHERE instance_id = ? AND indices_name = ?".format(self._table_name),
(instance_id, name)
)
if one:
conn.execute(
"UPDATE {} SET indices_type = ?, indices_value = ?"
" WHERE instance_id = ? AND indices_name = ?".format(self._table_name),
sql_param,
)
else:
conn.insert(
"INSERT INTO {} (indices_type, indices_value, instance_id, indices_name)"
" VALUES (?, ?, ?, ?)".format(self._table_name),
sql_param,
)
def __insert_asset_item(self, timestamp, datetime):
position_total, asset_total = self.__calculate_total(timestamp)
position_freeze, asset_freeze = self.__calculate_freeze(timestamp)
position_sub, asset_sub = self.__calculate_sub(timestamp)
conn = Conn(self._db_name)
one = conn.query_one(
"""SELECT * FROM {} WHERE exchange = ? AND settle_mode = ? AND settle_currency = ?
AND timestamp = ? AND backtest_id = ?""".format(
self._asset_table_name),
(self._exchange, self._settle_mode, self._settle_currency,
timestamp, self._backtest_id))
if one:
conn.execute(
"""
UPDATE {} SET asset_total = ?, asset_sub = ?, asset_freeze = ?,
position_total = ?, position_sub = ?, position_freeze = ? WHERE id = ?
""".format(self._asset_table_name),
(
asset_total,
asset_sub,
asset_freeze,
position_total,
position_sub,
position_freeze,
one["id"],
),
)
else:
conn.insert(
"""
INSERT INTO {} (exchange, settle_mode, settle_currency, backtest_id,
asset_total, asset_sub, asset_freeze,
position_total, position_sub, position_freeze,
timestamp, datetime) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""".format(self._asset_table_name),
(
self._exchange,
self._settle_mode,
self._settle_currency,
self._backtest_id,
asset_total,
asset_sub,
asset_freeze,
position_total,
position_sub,
position_freeze,
timestamp,
datetime,
),
)
one = conn.query_one(
"""SELECT * FROM {} WHERE exchange = ? AND settle_mode = ? AND settle_currency = ?
AND timestamp > ? AND backtest_id = ? ORDER BY timestamp LIMIT 1"""
.format(self._asset_table_name),
(self._exchange, self._settle_mode, self._settle_currency,
timestamp, self._backtest_id))
if one:
self.__insert_asset_item(one["timestamp"], one["datetime"])
def __update_instance(self):
conn = Conn(self._db_name)
orders = conn.query(
"SELECT * FROM {} WHERE instance_id = ? ORDER BY sequence".format(
self._table_name),
(self["instance_id"], ),
)
open_amount, open_fee = 0, 0.0
open_start_timestamp, open_finish_timestamp = 0, 0
open_start_datetime, open_finish_datetime = "", ""
open_type, open_place_type = ORDER_TYPE_OPEN_LONG, ""
liquidate_amount, liquidate_fee = 0, 0.0
liquidate_start_timestamp, liquidate_finish_timestamp = 0, 0
liquidate_start_datetime, liquidate_finish_datetime = "", ""
liquidate_type, liquidate_place_type = ORDER_TYPE_LIQUIDATE_LONG, ""
swap_times, swap_fee, swap_asset_pnl = 0, 0.0, 0
swap_contract = {
"open_amount": 0,
"open_sum": 0,
"open_avg_price": 0,
"liquidate_sum": 0,
"liquidate_amount": 0,
"liquidate_avg_price": 0,
}
for order in orders:
place_timestamp = order["place_timestamp"]
place_datetime = moment.get(order["place_timestamp"]).to(
"Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss")
if order["type"] in (
ORDER_TYPE_OPEN_LONG,
ORDER_TYPE_OPEN_SHORT,
) and order["place_type"] not in (
ORDER_PLACE_TYPE_L_SWAP,
ORDER_PLACE_TYPE_O_SWAP,
):
open_amount += order["deal_amount"]
open_fee += order["fee"]
open_type = order["type"]
open_place_type = order["place_type"]
if open_start_timestamp == 0:
open_start_timestamp = place_timestamp
open_start_datetime = place_datetime
open_finish_timestamp = place_timestamp
open_finish_datetime = place_datetime
if order["type"] in (
ORDER_TYPE_LIQUIDATE_LONG,
ORDER_TYPE_LIQUIDATE_SHORT,
) and order["place_type"] not in (
ORDER_PLACE_TYPE_L_SWAP,
ORDER_PLACE_TYPE_O_SWAP,
):
liquidate_amount += order["deal_amount"]
liquidate_fee += order["fee"]
liquidate_type = order["type"]
liquidate_place_type = order["place_type"]
if liquidate_start_timestamp == 0:
liquidate_start_timestamp = place_timestamp
liquidate_start_datetime = place_datetime
liquidate_finish_timestamp = place_timestamp
liquidate_finish_datetime = place_datetime
if order["place_type"] in (
ORDER_PLACE_TYPE_O_SWAP,
ORDER_PLACE_TYPE_L_SWAP,
):
swap_fee += order["fee"]
if order["type"] == ORDER_TYPE_OPEN_LONG:
swap_times += 1
swap_contract["open_amount"] += order["deal_amount"]
swap_contract["open_sum"] -= order["deal_amount"] * order[
"avg_price"]
swap_contract["open_avg_price"] = int(
-swap_contract["open_sum"] /
swap_contract["open_amount"])
elif order["type"] == ORDER_TYPE_OPEN_SHORT:
swap_times += 1
swap_contract["open_amount"] += order["deal_amount"]
swap_contract["open_sum"] += order["deal_amount"] * order[
"avg_price"]
swap_contract["open_avg_price"] = int(
swap_contract["open_sum"] /
swap_contract["open_amount"])
elif order["type"] == ORDER_TYPE_LIQUIDATE_LONG:
swap_contract["liquidate_amount"] += order["deal_amount"]
swap_contract["liquidate_sum"] += order[
"deal_amount"] * order["avg_price"]
swap_contract["liquidate_avg_price"] = int(
swap_contract["liquidate_sum"] /
swap_contract["liquidate_amount"])
elif order["type"] == ORDER_TYPE_LIQUIDATE_SHORT:
swap_contract["liquidate_amount"] += order["deal_amount"]
swap_contract["liquidate_sum"] -= order[
"deal_amount"] * order["avg_price"]
swap_contract["liquidate_avg_price"] = int(
-swap_contract["liquidate_sum"] /
swap_contract["liquidate_amount"])
else:
raise RuntimeError("can deal with the order type. ")
if open_amount != liquidate_amount:
return
# 不需要计算swap的情况。
if swap_contract["open_amount"] != swap_contract["liquidate_amount"]:
return
if swap_contract["open_amount"]:
swap_asset_pnl = (
swap_contract["open_sum"] +
swap_contract["liquidate_sum"]) * self["unit_amount"]
swap_asset_pnl = real_number(swap_asset_pnl)
swap_asset_pnl /= real_number(swap_contract["open_avg_price"])
swap_asset_pnl /= real_number(swap_contract["liquidate_avg_price"])
conn.execute(
"UPDATE future_instance_backtest SET open_fee = ?, open_type = ?, open_place_type = ?,"
" open_start_timestamp = ?, open_start_datetime = ?, open_finish_timestamp = ?, open_finish_datetime = ?, "
" liquidate_fee = ?, liquidate_type = ?, liquidate_place_type = ?,"
" liquidate_start_timestamp = ?, liquidate_start_datetime = ?,"
" liquidate_finish_timestamp = ?, liquidate_finish_datetime = ?,"
" swap_times = ?, swap_fee = ?, swap_asset_pnl = ? WHERE id = ?",
(
open_fee,
open_type,
open_place_type,
open_start_timestamp,
open_start_datetime,
open_finish_timestamp,
open_finish_datetime,
liquidate_fee,
liquidate_type,
liquidate_place_type,
liquidate_start_timestamp,
liquidate_start_datetime,
liquidate_finish_timestamp,
liquidate_finish_datetime,
swap_times,
swap_fee,
swap_asset_pnl,
self["instance_id"],
),
)
def save(self,
check: bool = False,
raw_order_data: str = None,
raw_market_data: str = None):
if check:
# 检验参数可用性
validate(instance=self, schema=future_order_init)
validate(instance=self, schema=future_order_save)
conn = Conn(self._db_name)
one = conn.query_one(
"SELECT id FROM {} WHERE instance_id = ? AND sequence = ?".format(
self._table_name),
(self["instance_id"], self["sequence"]),
)
if one:
conn.execute(
"UPDATE {} SET place_type = ?, `type` = ?, price = ?, amount = ?,"
" avg_price = ?, deal_amount = ?, status = ?, lever = ?, fee = ?,"
" symbol = ?, exchange = ?, contract_type = ?, unit_amount = ?, "
" place_timestamp = ?, place_datetime = ?, deal_timestamp = ?, deal_datetime = ?,"
" due_timestamp = ?, due_datetime = ?, swap_timestamp = ?, swap_datetime = ?,"
" cancel_timestamp = ?, cancel_datetime = ?, raw_order_data = ?, raw_market_data = ?"
" WHERE instance_id = ? AND sequence = ?".format(
self._table_name),
(
self["place_type"],
self["type"],
self["price"],
self["amount"],
self["avg_price"],
self["deal_amount"],
self["status"],
self["lever"],
self["fee"],
self["symbol"],
self["exchange"],
self["contract_type"],
self["unit_amount"],
self["place_timestamp"],
self["place_datetime"],
self["deal_timestamp"],
self["deal_datetime"],
self["due_timestamp"],
self["due_datetime"],
self["swap_timestamp"],
self["swap_datetime"],
self["cancel_timestamp"],
self["cancel_datetime"],
raw_order_data,
raw_market_data,
self["instance_id"],
self["sequence"],
),
)
else:
conn.insert(
"INSERT INTO {} (instance_id, sequence, place_type, `type`, price,"
" amount, avg_price, deal_amount, status, lever,"
" fee, symbol, exchange, contract_type, unit_amount,"
" place_timestamp, place_datetime, deal_timestamp, deal_datetime,"
" due_timestamp, due_datetime, swap_timestamp, swap_datetime,"
" cancel_timestamp, cancel_datetime, raw_order_data, raw_market_data) VALUES"
" (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
.format(self._table_name, ),
(
self["instance_id"],
self["sequence"],
self["place_type"],
self["type"],
self["price"],
self["amount"],
self["avg_price"],
self["deal_amount"],
self["status"],
self["lever"],
self["fee"],
self["symbol"],
self["exchange"],
self["contract_type"],
self["unit_amount"],
self["place_timestamp"],
self["place_datetime"],
self["deal_timestamp"],
self["deal_datetime"],
self["due_timestamp"],
self["due_datetime"],
self["swap_timestamp"],
self["swap_datetime"],
self["cancel_timestamp"],
self["cancel_datetime"],
raw_order_data,
raw_market_data,
),
)
self.__update_instance()
def save(self,
check: bool = False,
raw_order_data: str = None,
raw_market_data: str = None):
if check:
# 检验参数可用性
validate(instance=self, schema=order_input)
conn = Conn(self._db_name)
one = conn.query_one(
"SELECT id FROM {} WHERE instance_id = ? AND sequence = ?".format(
self._table_name),
(self["instance_id"], self["sequence"]),
)
if one:
conn.execute(
"UPDATE {} SET place_type = ?, `type` = ?, price = ?, amount = ?,"
" avg_price = ?, deal_amount = ?, status = ?, lever = ?, fee = ?,"
" symbol = ?, exchange = ?, unit_amount = ?, place_timestamp = ?, place_datetime = ?,"
" deal_timestamp = ?, deal_datetime = ?, swap_timestamp = ?, swap_datetime = ?,"
" cancel_timestamp = ?, cancel_datetime = ?, raw_order_data = ?, raw_market_data = ?"
" WHERE instance_id = ? AND sequence = ?".format(
self._table_name),
(
self["place_type"],
self["type"],
self["price"],
self["amount"],
self["avg_price"],
self["deal_amount"],
self["status"],
self["lever"],
self["fee"],
self["symbol"],
self["exchange"],
self["unit_amount"],
self["place_timestamp"],
self["place_datetime"],
self["deal_timestamp"],
self["deal_datetime"],
self["swap_timestamp"],
self["swap_datetime"],
self["cancel_timestamp"],
self["cancel_datetime"],
raw_order_data,
raw_market_data,
self["instance_id"],
self["sequence"],
),
)
else:
conn.insert(
"INSERT INTO {} (instance_id, sequence, place_type, `type`, price,"
" amount, avg_price, deal_amount, status, lever,"
" fee, symbol, exchange, unit_amount, place_timestamp, place_datetime, deal_timestamp, deal_datetime,"
" cancel_timestamp, cancel_datetime, raw_order_data, raw_market_data) VALUES"
" (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
.format(self._table_name, ),
(
self["instance_id"],
self["sequence"],
self["place_type"],
self["type"],
self["price"],
self["amount"],
self["avg_price"],
self["deal_amount"],
self["status"],
self["lever"],
self["fee"],
self["symbol"],
self["exchange"],
self["unit_amount"],
self["place_timestamp"],
self["place_datetime"],
self["deal_timestamp"],
self["deal_datetime"],
self["cancel_timestamp"],
self["cancel_datetime"],
raw_order_data,
raw_market_data,
),
)
orders = conn.query(
"SELECT * FROM {} WHERE instance_id = ? ORDER BY sequence".format(
self._table_name),
(self["instance_id"], ),
)
open_amount, open_fee = 0, 0.0
open_start_timestamp, open_finish_timestamp = 0, 0
open_start_datetime, open_finish_datetime = "", ""
open_type, open_place_type = ORDER_TYPE_OPEN_LONG, ""
liquidate_amount, liquidate_fee = 0, 0.0
liquidate_start_timestamp, liquidate_finish_timestamp = 0, 0
liquidate_start_datetime, liquidate_finish_datetime = "", ""
liquidate_type, liquidate_place_type = ORDER_TYPE_LIQUIDATE_LONG, ""
for order in orders:
place_timestamp = order["place_timestamp"]
place_datetime = moment.get(order["place_timestamp"]).to(
self.get("timezone")
or "Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss")
if order["type"] in (ORDER_TYPE_OPEN_LONG, ORDER_TYPE_OPEN_SHORT):
open_amount += order["deal_amount"]
open_fee += order["fee"]
open_type = order["type"]
open_place_type = order["place_type"]
if order["sequence"] == 0:
open_start_timestamp = place_timestamp
open_start_datetime = place_datetime
open_finish_timestamp = place_timestamp
open_finish_datetime = place_datetime
if order["type"] in (ORDER_TYPE_LIQUIDATE_LONG,
ORDER_TYPE_LIQUIDATE_SHORT):
liquidate_amount += order["deal_amount"]
liquidate_fee += order["fee"]
liquidate_type = order["type"]
liquidate_place_type = order["place_type"]
if liquidate_start_timestamp == 0:
liquidate_start_timestamp = place_timestamp
liquidate_start_datetime = place_datetime
liquidate_finish_timestamp = place_timestamp
liquidate_finish_datetime = place_datetime
if open_amount != liquidate_amount:
return
conn.execute(
"UPDATE {trade_type}_instance_{mode} SET open_fee = ?, open_type = ?, open_place_type = ?,"
" open_start_timestamp = ?, open_start_datetime = ?, open_finish_timestamp = ?, open_finish_datetime = ?, "
" liquidate_fee = ?, liquidate_type = ?, liquidate_place_type = ?,"
" liquidate_start_timestamp = ?, liquidate_start_datetime = ?,"
" liquidate_finish_timestamp = ?, liquidate_finish_datetime = ? WHERE id = ?"
.format(
trade_type=self._trade_type,
mode=MODE_STRATEGY
if self._mode != MODE_BACKTEST else MODE_BACKTEST,
),
(
open_fee,
open_type,
open_place_type,
open_start_timestamp,
open_start_datetime,
open_finish_timestamp,
open_finish_datetime,
liquidate_fee,
liquidate_type,
liquidate_place_type,
liquidate_start_timestamp,
liquidate_start_datetime,
liquidate_finish_timestamp,
liquidate_finish_datetime,
self["instance_id"],
),
)
def _opening_expired(self, due_ts: int) -> int:
"""opening阶段超时过后，转到liquidating 或者finished阶段。
Args:
due_ts: The current contract due timestamp.
Returns:
The next stage starting timestamp.
"""
(_, _, _, _, _, opening_amounts, _) = self._analysis_orders(due_ts)
instance_status = INSTANCE_STATUS_LIQUIDATING
for ts in opening_amounts:
if opening_amounts[ts] > 0:
break
else:
# 如果没有一个contract中有持仓，则认为交易结束。
instance_status = INSTANCE_STATUS_FINISHED
conn = Conn(self["db_name"])
conn.execute(
"UPDATE {trade_type}_instance_backtest SET wait_finish_timestamp = ?, wait_finish_datetime = ?,"
" status = ?, unit_amount = ? WHERE id = ?".format(
trade_type=self["trade_type"]),
(
self["open_expired_timestamp"],
self["open_expired_datetime"],
instance_status,
self["unit_amount"],
self["id"],
),
)
self["status"] = instance_status
# 还有未平的仓位时
if instance_status == INSTANCE_STATUS_LIQUIDATING:
return self["open_expired_timestamp"]
# 已经完成平仓时，这时属于finish阶段
asset: Asset = self["asset"]
place_timestamp = self["open_expired_timestamp"]
# 步骤一： 计算需要结算的损益
_, settle_pnl = self._settle_pnl(settle_mode=self["settle_mode"])
# 步骤二： 解冻并结算。
asset.unfreeze_and_settle(
self["asset_freeze"],
self["param"]["position"],
settle_pnl,
place_timestamp,
)
# 步骤三： 更新到对应的instance上。
conn = Conn(self["db_name"])
if self["trade_type"] == TRADE_TYPE_FUTURE:
conn.execute(
"UPDATE {}_instance_backtest SET asset_pnl = ? WHERE id = ?".
format(self["trade_type"]),
(settle_pnl, self["id"]),
)
else:
conn.execute(
"UPDATE {}_instance_backtest SET asset_pnl = ? WHERE id = ?".
format(self["trade_type"]),
(settle_pnl, self["id"]),
)
return 0
def save(self, slippage=0.01, fee=-0.0005) -> None:
self.check_instance(self)
conn = Conn(self["db_name"])
one = conn.query_one(
"SELECT id FROM {trade_type}_instance_{mode} WHERE id = ?".format(
**self),
(self["id"], ),
)
if one is None:
raise RuntimeError("I think can not insert in this place. ")
if self["trade_type"] == TRADE_TYPE_FUTURE:
conn.execute(
"UPDATE {trade_type}_instance_{mode} SET symbol = ?, exchange = ?, contract_type = ?,"
" strategy = ?, unit_amount = ?, lever = ?, status = ?, `interval` = ?,"
" wait_start_timestamp = ?, wait_start_datetime = ?,"
" wait_finish_timestamp = ?, wait_finish_datetime = ?,"
" open_times = ?, open_start_timestamp = ?, open_start_datetime = ?,"
" open_finish_timestamp = ?, open_finish_datetime = ?,"
" open_expired_timestamp = ?, open_expired_datetime = ?,"
" liquidate_times = ?, liquidate_start_timestamp = ?, liquidate_start_datetime = ?,"
" liquidate_finish_timestamp = ?, liquidate_finish_datetime = ?,"
" asset_total = ?, asset_freeze = ?, param_position = ?, param_max_abs_loss_ratio = ?"
" WHERE id = ?".format(trade_type=self["trade_type"],
mode=self["mode"]),
(
self["symbol"],
self["exchange"],
self["contract_type"],
self["strategy"],
self["unit_amount"],
self["lever"],
self["status"],
self["interval"],
self["wait_start_timestamp"],
self["wait_start_datetime"],
self["wait_finish_timestamp"],
self["wait_finish_datetime"],
self["open_times"],
self["open_start_timestamp"],
self["open_start_datetime"],
self["open_finish_timestamp"],
self["open_finish_datetime"],
self["open_expired_timestamp"],
self["open_expired_datetime"],
self["liquidate_times"],
self["liquidate_start_timestamp"],
self["liquidate_start_datetime"],
self["liquidate_finish_timestamp"],
self["liquidate_finish_datetime"],
self["asset_total"],
self["asset_freeze"],
self["param_position"],
self["param_max_abs_loss_ratio"],
self["id"],
),
)
order: FutureOrder = self["order"]
else:
conn.execute(
"UPDATE {trade_type}_instance_{mode} SET symbol = ?, exchange = ?,"
" strategy = ?, unit_amount = ?, status = ?, lever = ?,"
" wait_start_timestamp = ?, wait_start_datetime = ?,"
" wait_finish_timestamp = ?, wait_finish_datetime = ?,"
" open_times = ?, open_start_timestamp = ?, open_start_datetime = ?,"
" open_finish_timestamp = ?, open_finish_datetime = ?,"
" open_expired_timestamp = ?, open_expired_datetime = ?,"
" liquidate_times = ?, liquidate_start_timestamp = ?, liquidate_start_datetime = ?,"
" liquidate_finish_timestamp = ?, liquidate_finish_datetime = ?,"
" asset_total = ?, asset_freeze = ?, param_position = ?, param_max_abs_loss_ratio = ?"
" WHERE id = ?".format(trade_type=self["trade_type"],
mode=self["mode"]),
(
self["symbol"],
self["exchange"],
self["strategy"],
self["unit_amount"],
self["status"],
self["lever"],
self["wait_start_timestamp"],
self["wait_start_datetime"],
self["wait_finish_timestamp"],
self["wait_finish_datetime"],
self["open_times"],
self["open_start_timestamp"],
self["open_start_datetime"],
self["open_finish_timestamp"],
self["open_finish_datetime"],
self["open_expired_timestamp"],
self["open_expired_datetime"],
self["liquidate_times"],
self["liquidate_start_timestamp"],
self["liquidate_start_datetime"],
self["liquidate_finish_timestamp"],
self["liquidate_finish_datetime"],
self["asset_total"],
self["asset_freeze"],
self["param_position"],
self["param_max_abs_loss_ratio"],
self["id"],
),
)
order: CommonOrder = self["order"]
order.deal(slippage=slippage, fee=fee)
order.save(check=True, raw_order_data=json.dumps(self))
param: Param = self["param"]
param.save(self["id"])
indices: Indices = self["indices"]
indices.save(self["id"])