def query_range_contracts(
self,
start_timestamp: int,
finish_timestamp: int,
interval: str,
standard: bool = False,
due_timestamp: int = 0, # 辅助参数
):
conn = Conn(self.db_name)
params = (start_timestamp, finish_timestamp, self.symbol, self.exchange, interval)
candles = conn.query(
"SELECT * FROM {} WHERE timestamp >= ? AND timestamp < ? AND symbol = ? AND exchange = ?"
" AND `interval` = ? ORDER BY timestamp, due_timestamp LIMIT 100".format(self.table_name),
params,
)
if due_timestamp:
tmp_candles = conn.query(
"SELECT * FROM {} WHERE timestamp = ? AND symbol = ? AND exchange = ? AND `interval` = ?"
" AND due_timestamp > ? ORDER BY due_timestamp".format(self.table_name),
(start_timestamp, self.symbol, self.exchange, interval, due_timestamp)
)
candles = tmp_candles + candles
conn.close() # 手动关闭链接。
for candle in candles:
yield self.__standard_candle(candle) if standard else candle
if len(candles) >= 100:
yield from self.query_range_contracts(
candles[-1]["timestamp"] + 1000,
finish_timestamp,
interval,
standard=standard,
due_timestamp=candles[-1].get("due_timestamp") or 0,
)
def _is_opened(self, wait_start_timestamp: int) -> bool:
conn = Conn(self["db_name"])
if self["trade_type"] == TRADE_TYPE_FUTURE:
opened = conn.query(
"SELECT id FROM future_instance_{mode} WHERE symbol = ? AND exchange = ? AND contract_type = ?"
" AND strategy = ? AND wait_start_timestamp = ? AND status > ?".format(
mode=MODE_BACKTEST if self["mode"] == MODE_BACKTEST else MODE_STRATEGY,
),
(
self["symbol"], self["exchange"], self["contract_type"], self["strategy"],
wait_start_timestamp, INSTANCE_STATUS_WAITING,
),
)
return len(opened) > 0
opened = conn.query(
"SELECT id FROM {trade_type}_instance_{mode} WHERE symbol = ? AND exchange = ? AND strategy = ? "
"AND wait_start_timestamp = ? AND status > ?".format(
trade_type=self["trade_type"],
mode=MODE_BACKTEST if self["mode"] == MODE_BACKTEST else MODE_STRATEGY,
),
(
self["symbol"], self["exchange"], self["strategy"],
wait_start_timestamp, INSTANCE_STATUS_WAITING,
),
)
return len(opened) > 0
def _is_opened(self, wait_start_timestamp: int) -> bool:
conn = Conn(self["db_name"])
if self["trade_type"] == TRADE_TYPE_FUTURE:
opened = conn.query(
"SELECT id FROM future_instance_backtest WHERE backtest_id = ? AND symbol = ? AND exchange = ?"
" AND contract_type = ? AND strategy = ? AND wait_start_timestamp = ? AND status > ?",
(
self["backtest_id"],
self["symbol"],
self["exchange"],
self["contract_type"],
self["strategy"],
wait_start_timestamp,
INSTANCE_STATUS_WAITING,
),
)
return len(opened) > 0
opened = conn.query(
"SELECT id FROM {trade_type}_instance_backtest WHERE backtest_id = ? AND symbol = ? AND exchange = ? AND"
" strategy = ? AND wait_start_timestamp = ? AND status > ?".format(
trade_type=self["trade_type"]),
(
self["backtest_id"],
self["symbol"],
self["exchange"],
self["strategy"],
wait_start_timestamp,
INSTANCE_STATUS_WAITING,
),
)
return len(opened) > 0
def _get_risk_level(self, timestamp: int, instance_id: int) -> int:
conn = Conn(self["db_name"])
table_name = "{trade_type}_instance_backtest".format(
trade_type=self["trade_type"])
query_sql = """
SELECT id FROM {} WHERE backtest_id = ? AND symbol = ? AND exchange = ?
AND strategy = ? AND open_start_timestamp < ? AND (liquidate_finish_timestamp > ? OR status in (?,?))
ORDER BY open_start_timestamp, id
"""
params = (
self["backtest_id"],
self["symbol"],
self["exchange"],
self["strategy"],
timestamp,
timestamp,
INSTANCE_STATUS_OPENING,
INSTANCE_STATUS_LIQUIDATING,
)
instances = conn.query(
query_sql.format(table_name),
params,
)
instance_ids = [i["id"] for i in instances]
risk_level = len(instance_ids)
if instance_id in instance_ids:
risk_level = instance_ids.index(instance_id)
return risk_level
def _values_by_id(self, fact_id: int, start_timestamp: int, finish_timestamp: int) -> List[float]:
conn = Conn(self._db_name)
fact_values = conn.query(
"SELECT factor_value FROM factor_dataset"
" WHERE factor_id = ? AND timestamp >= ? AND timestamp <= ? ORDER BY timestamp",
(fact_id, start_timestamp, finish_timestamp),
)
return [f["factor_value"] for f in fact_values]
def _get_previous_instances(self, start_timestamp=0, finish_timestamp=0):
if start_timestamp == 0:
raise RuntimeError("start_timestamp must bigger than 0. ")
if finish_timestamp == 0:
finish_timestamp = moment.now().millisecond_timestamp
conn = Conn(self["db_name"])
table_name = "{trade_type}_instance_{mode}".format(
trade_type=self["trade_type"],
mode=MODE_BACKTEST if self["mode"] == MODE_BACKTEST else MODE_STRATEGY,
)
if self["trade_type"] == TRADE_TYPE_FUTURE:
query_sql = """
SELECT * FROM {} WHERE symbol = ? AND exchange = ? AND contract_type = ? AND strategy = ?
AND wait_start_timestamp >= ? AND wait_start_timestamp < ? AND status != ? ORDER BY wait_start_timestamp
""".format(table_name)
query_param = (
self["symbol"], self["exchange"], self["contract_type"], self["strategy"],
start_timestamp, finish_timestamp, INSTANCE_STATUS_WAITING,
)
if self["mode"] == MODE_BACKTEST:
query_sql = """
SELECT * FROM {} WHERE symbol = ? AND exchange = ? AND contract_type = ?
AND strategy = ? AND wait_start_timestamp >= ? AND wait_start_timestamp < ?
AND backtest_id = ? AND status != ? ORDER BY wait_start_timestamp
""".format(table_name)
query_param = (
self["symbol"], self["exchange"], self["contract_type"], self["strategy"],
start_timestamp, finish_timestamp, self["backtest_id"], INSTANCE_STATUS_WAITING,
)
else:
query_sql = """
SELECT * FROM {} WHERE symbol = ? AND exchange = ? AND strategy = ?
AND wait_start_timestamp >= ? AND wait_start_timestamp < ? AND status != ?
ORDER BY wait_start_timestamp
""".format(table_name)
query_param = (
self["symbol"], self["exchange"], self["strategy"],
start_timestamp, finish_timestamp, INSTANCE_STATUS_WAITING,
)
if self["mode"] == MODE_BACKTEST:
query_sql = """
SELECT * FROM {} WHERE symbol = ? AND exchange = ? AND strategy = ?
AND wait_start_timestamp >= ? AND wait_start_timestamp < ?
AND backtest_id = ? AND status != ? ORDER BY wait_start_timestamp
""".format(table_name)
query_param = (
self["symbol"], self["exchange"], self["strategy"],
start_timestamp, finish_timestamp, self["backtest_id"], INSTANCE_STATUS_WAITING,
)
return conn.query(query_sql, query_param)
def previous_signals(self, signal_name: str, timestamp: int) -> List[dict]:
meta = self.get_metadata(signal_name)
signal_id = meta["signal_id"]
conn = Conn(self._db_name)
dataset = conn.query(
"SELECT * FROM signal_dataset WHERE signal_id = ? AND finish_timestamp < ?"
" ORDER BY finish_timestamp DESC LIMIT 5",
(signal_id, timestamp),
)
return dataset
def _get_orders(self) -> List:
conn = Conn(self["db_name"])
orders = conn.query(
"SELECT * FROM {trade_type}_order_{mode} WHERE instance_id = ?"
" ORDER BY sequence".format(
trade_type=self["trade_type"],
mode=MODE_BACKTEST if self["mode"] == MODE_BACKTEST else MODE_STRATEGY,
),
(self["id"])
)
return orders
def load(self, instance_id):
conn = Conn(self._db_name)
results = conn.query(
"SELECT * FROM {} WHERE instance_id = ?".format(self._table_name),
(instance_id,),
)
for result in results:
if result["indices_type"] == INDICES_TYPE_INTEGER:
self[result["indices_name"]] = int(result["indices_value"])
elif result["indices_type"] == INDICES_TYPE_FLOAT:
self[result["indices_name"]] = float(result["indices_value"])
else:
self[result["indices_name"]] = result["indices_value"]
def query_range(
self,
start_timestamp: int,
finish_timestamp: int,
interval: str,
standard: bool = False
):
conn = Conn(self.db_name)
params = (self.symbol, self.exchange, interval, start_timestamp, finish_timestamp)
if self.trade_type == TRADE_TYPE_FUTURE:
params = (self.symbol, self.exchange, self.contract_type, interval, start_timestamp, finish_timestamp)
candles = conn.query(self.sql, params)
conn.close() # 手动关闭链接。
for candle in candles:
yield self.__standard_candle(candle) if standard else candle
if len(candles) == 100:
yield from self.query_range(candles[-1]["timestamp"] + 1000, finish_timestamp, interval, standard=standard)
def _get_risk_level(self, timestamp: int, instance_id: int) -> int:
conn = Conn(self["db_name"])
table_name = "{trade_type}_instance_{mode}".format(
trade_type=self["trade_type"],
mode=MODE_BACKTEST if self["mode"] == MODE_BACKTEST else MODE_STRATEGY,
)
m = moment.get(timestamp).to(self.get("timezone") or "Asia/Shanghai").floor("day")
query_sql = """
SELECT id FROM {} WHERE symbol = ? AND exchange = ? AND strategy = ?
AND (status IN (?, ?, ?) OR ( status = ? AND liquidate_finish_timestamp > ? )) ORDER BY open_start_timestamp, id
"""
params = (
self["symbol"], self["exchange"], self["strategy"],
INSTANCE_STATUS_OPENING, INSTANCE_STATUS_LIQUIDATING, INSTANCE_STATUS_ERROR,
INSTANCE_STATUS_FINISHED, m.millisecond_timestamp,
)
if self["mode"] == MODE_BACKTEST:
query_sql = """
SELECT id FROM {} WHERE backtest_id = ? AND symbol = ? AND exchange = ?
AND strategy = ? AND open_start_timestamp < ? AND (liquidate_finish_timestamp > ? OR status in (?,?))
ORDER BY open_start_timestamp, id
"""
params = (
self["backtest_id"],
self["symbol"],
self["exchange"],
self["strategy"],
timestamp,
timestamp,
INSTANCE_STATUS_OPENING,
INSTANCE_STATUS_LIQUIDATING,
)
instances = conn.query(
query_sql.format(table_name),
params,
)
instance_ids = [i["id"] for i in instances]
risk_level = len(instance_ids)
if instance_id in instance_ids:
risk_level = instance_ids.index(instance_id)
return risk_level
def _values_by_sequence(
self,
fact_id: int,
start_timestamp: int,
finish_timestamp: int,
limit: int,
is_desc: bool,
) -> List[float]:
conn = Conn(self._db_name)
fact_values = conn.query(
"SELECT factor_value FROM factor_dataset"
" WHERE factor_id = ? AND timestamp >= ? AND timestamp <= ? ORDER BY factor_value {} LIMIT {}".format(
"DESC" if is_desc else "",
limit,
),
(fact_id, start_timestamp, finish_timestamp),
)
return [f["factor_value"] for f in fact_values]
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=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"],
),
)