"""
|
数据导出工具
|
"""
|
import json
|
import logging
|
import os
|
import time
|
|
import xlwt
|
|
import constant
|
from code_attribute import gpcode_manager
|
import l2.l2_data_util
|
from l2.huaxin import l2_huaxin_util
|
from log_module import log, log_export
|
from l2 import l2_data_source_util
|
from log_module.log import logger_debug
|
from trade import deal_big_money_manager
|
from utils import tool
|
|
# 缓存L2数据，格式：{"日期":{数据}}
|
__l2_data_cache = {}
|
|
|
def export_l2_excel(code, date=None):
|
# 获取L2的数据
|
fdatas = get_l2_datas(code, date=date)
|
__save_l2_datas(code, fdatas)
|
|
|
# 获取L2的数据
|
def get_l2_datas(code, today_datas=None, date=None, max_time=None):
|
__start_time = time.time()
|
if date is None:
|
date = tool.get_now_date_str()
|
datas = today_datas
|
if datas is None:
|
if date in __l2_data_cache:
|
local_today_datas = __l2_data_cache.get(date)
|
else:
|
local_today_datas = log_export.load_l2_from_log(date)
|
__l2_data_cache[date] = local_today_datas
|
datas = local_today_datas.get(code)
|
if not datas:
|
datas = []
|
logger_debug.info("加载L2逐笔委托耗时：{}", f"{(time.time() - __start_time)}")
|
__start_time = time.time()
|
process_indexs = log_export.get_l2_process_position(code, date)
|
logger_debug.info("加载L2逐笔委托批次耗时：{}", f"{(time.time() - __start_time)}")
|
__start_time = time.time()
|
trade_indexs = log_export.get_l2_trade_position(code, date)
|
real_position_indexes = log_export.get_real_place_order_positions(code, date)
|
deal_list = log_export.load_huaxin_deal_record(code, date)
|
logger_debug.info("加载其他交易参数耗时：{}", f"{(time.time() - __start_time)}")
|
__start_time = time.time()
|
deal_list_dict = {}
|
for d in deal_list:
|
deal_list_dict[str(d[0])] = d
|
|
sell_no_dict = log_export.load_huaxin_transaction_sell_no(code=code, date=date)
|
sell_nos = sell_no_dict.get(code)
|
active_sell_map = log_export.load_huaxin_active_sell_map(date=date)
|
active_sell_set = active_sell_map.get(code)
|
logger_debug.info("加载卖单耗时：{}", f"{(time.time() - __start_time)}")
|
__start_time = time.time()
|
if not active_sell_set:
|
active_sell_set = set()
|
fdatas = export_l2_data(code, datas, process_indexs, trade_indexs, real_position_indexes, deal_list_dict, sell_nos,
|
active_sell_set)
|
logger_debug.info("组装数据耗时：{}", f"{(time.time() - __start_time)}")
|
__start_time = time.time()
|
return fdatas
|
|
|
def get_l2_transaction_datas(code, date=None):
|
if date is None:
|
date = tool.get_now_date_str()
|
sell_no_dict = log_export.load_huaxin_transaction_sell_no(code=code, date=date)
|
sell_nos = sell_no_dict.get(code)
|
fdatas = export_l2_transaction_data(code, sell_nos)
|
return fdatas
|
|
|
def export_l2_data(code, datas, process_indexs, trade_indexs, real_position_indexes, deal_list_dict, sell_nos,
|
active_sell_nos):
|
def find_process_index(index):
|
for i in range(0, len(process_indexs)):
|
if process_indexs[i][0] <= index <= process_indexs[i][1]:
|
return i
|
return len(process_indexs)
|
|
def find_trade_index(index):
|
for i in range(0, len(trade_indexs)):
|
if trade_indexs[i][1] == index:
|
return trade_indexs[i]
|
return None
|
logger_debug.info("准备加载L2Map数据-----")
|
# 数据预处理
|
num_operate_map = {}
|
l2.l2_data_util.load_num_operate_map(num_operate_map, code, datas)
|
logger_debug.info("load_num_operate_map")
|
|
buy_no_map = {}
|
sell_no_map = {}
|
l2.l2_data_util.load_buy_no_map(buy_no_map, code, datas)
|
logger_debug.info("load_buy_no_map")
|
l2.l2_data_util.load_sell_no_map(sell_no_map, code, datas)
|
logger_debug.info("load_sell_no_map")
|
l2.l2_data_util.load_canceled_buy_no_map(l2.l2_data_util.local_today_canceled_buyno_map, code, datas)
|
logger_debug.info("load_canceled_buy_no_map")
|
# num_dict = {}
|
# for data in datas:
|
# if data["val"]["num"] not in num_dict:
|
# num_dict[data["val"]["num"]] = []
|
# num_dict[data["val"]["num"]].append(data)
|
logger_debug.info(f"循环组装数据开始：长度-{len(datas)} 主动卖长度-{len(active_sell_nos)}")
|
index = 0
|
fdatas = []
|
for data in datas:
|
index += 1
|
# 先移除
|
if data['val']['orderNo'] in active_sell_nos:
|
# 过滤主动卖
|
continue
|
|
trade_info = find_trade_index(data["index"])
|
if not trade_info:
|
# 获取真实下单位置
|
if data["index"] in real_position_indexes:
|
trade_info = [3]
|
style_int = None
|
if find_process_index(data["index"]) % 2 == 0:
|
style_int = 0
|
else:
|
style_int = 1
|
|
format_data = []
|
# 索引
|
format_data.append(data["index"])
|
# 时间
|
format_data.append(data["val"]["time"] + (f".{data['val']['tms']}" if "tms" in data["val"] else ''))
|
|
cancel_time = data["val"]["cancelTime"]
|
if cancel_time == '0':
|
cancel_time = ''
|
else:
|
cancel_time = "{}".format(cancel_time)
|
if len(cancel_time) > 0:
|
if int(data["val"]["cancelTimeUnit"]) == 0:
|
cancel_time += "s"
|
elif int(data["val"]["cancelTimeUnit"]) == 1:
|
cancel_time += "m"
|
elif int(data["val"]["cancelTimeUnit"]) == 2:
|
cancel_time += "h"
|
# 撤单间隔时间
|
format_data.append(cancel_time)
|
# 金额
|
format_data.append("{}万".format(round(int(data["val"]["num"]) * float(data["val"]["price"]) / 100, 1)))
|
# 单价
|
format_data.append(data["val"]["price"])
|
|
if int(data["val"]["operateType"]) == 1 or int(data["val"]["operateType"]) == 2:
|
format_data.append(0 - int(data["val"]["num"]))
|
else:
|
format_data.append(int(data["val"]["num"]))
|
|
limit_price = ""
|
if int(data["val"]["limitPrice"]) == 1:
|
limit_price = "T"
|
elif int(data["val"]["limitPrice"]) == 2:
|
limit_price = "D"
|
operateDesc = ""
|
if int(data["val"]["operateType"]) == 0:
|
if len(limit_price) > 0:
|
operateDesc = '买{}'.format(limit_price)
|
else:
|
operateDesc = '买'
|
elif int(data["val"]["operateType"]) == 1:
|
if len(limit_price) > 0:
|
operateDesc = '买撤{}'.format(limit_price)
|
|
else:
|
operateDesc = '买撤'
|
|
elif int(data["val"]["operateType"]) == 2:
|
if len(limit_price) > 0:
|
operateDesc = '卖{}'.format(limit_price)
|
else:
|
operateDesc = '卖'
|
|
elif int(data["val"]["operateType"]) == 3:
|
if len(limit_price) > 0:
|
operateDesc = '卖撤{}'.format(limit_price)
|
else:
|
operateDesc = '卖撤'
|
format_data.append(operateDesc)
|
format_data.append(data["re"])
|
# 查询是否撤单
|
cancel_info = None
|
if int(data["val"]["operateType"]) == 0:
|
cancel_data = l2.l2_data_util.local_today_canceled_buyno_map.get(code).get(str(data["val"]["orderNo"]))
|
# 买
|
if cancel_data:
|
try:
|
left_num = data["val"]["num"] - cancel_data["val"]["num"]
|
if left_num > 0:
|
cancel_info = f"成交:{left_num} 序号:{cancel_data['index']}"
|
else:
|
cancel_info = "{}-{}".format(cancel_data["index"], f"{cancel_data['val']['time']}") + (
|
f".{cancel_data['val']['tms']}" if "tms" in cancel_data["val"] else '')
|
except Exception as e:
|
logging.exception(e)
|
else:
|
deal_info = deal_list_dict.get(str(data["val"].get("orderNo")))
|
if deal_info and len(deal_info) >= 5:
|
cancel_info = l2_huaxin_util.convert_time(deal_info[4],
|
with_ms=True)
|
format_data.append(cancel_info)
|
cancel_order_info = None
|
if trade_info:
|
if trade_info[0] == 0:
|
# font.colour_index = 53
|
pass
|
elif trade_info[0] == 1:
|
# font.colour_index = 17
|
pass
|
elif trade_info[0] == 2:
|
# font.colour_index = 10
|
cancel_order_info = trade_info[2]
|
format_data.append(cancel_order_info)
|
format_data.append(data["val"].get("orderNo"))
|
fdatas.append((style_int, trade_info, format_data))
|
logger_debug.info("循环组装数据完成")
|
return fdatas
|
|
|
def export_l2_transaction_data(code, sell_nos):
|
fdatas = []
|
if sell_nos:
|
index = 0
|
for sell_info in sell_nos:
|
if sell_info[1] * sell_info[2] < 50 * 10000:
|
continue
|
index += 1
|
item = []
|
item.append(index)
|
item.append(l2_huaxin_util.convert_time(sell_info[3][0], with_ms=True))
|
item.append("")
|
item.append(
|
"{}万".format(round(sell_info[1] * sell_info[2] / 10000, 1)))
|
item.append(sell_info[2])
|
item.append(sell_info[1] // 100)
|
item.append("主动卖")
|
item.append(1)
|
item.append(l2_huaxin_util.convert_time(sell_info[4][0], with_ms=True))
|
item.append(None)
|
item.append(sell_info[0])
|
fdatas.append((0, None, item))
|
return fdatas
|
|
|
def __save_l2_datas(code, fdatas, dest_dir=f"{constant.get_path_prefix()}/export/l2"):
|
local_time = time.strftime("%Y%m%dT%H%M%S", time.localtime(time.time()))
|
file_name = "{}/{}_{}.xls".format(dest_dir, code, local_time)
|
wb = xlwt.Workbook(encoding="utf-8")
|
ws = wb.add_sheet('sheet1')
|
ws.write(0, 0, '序号')
|
ws.write(0, 1, '时间')
|
ws.write(0, 2, '买撤间隔')
|
ws.write(0, 3, '金额')
|
ws.write(0, 4, '价格')
|
ws.write(0, 5, '手数')
|
ws.write(0, 6, '类型')
|
ws.write(0, 7, '重复数量')
|
ws.write(0, 8, '撤单时间')
|
ws.write(0, 9, '备注')
|
ws.write(0, 10, '订单号')
|
index = 0
|
|
cancel_style = xlwt.easyxf('pattern: pattern solid, fore_colour gray25')
|
for fdata in fdatas:
|
index += 1
|
style_int = fdata[0]
|
trade_info = fdata[1]
|
data = fdata[2]
|
style = None
|
if style_int == 0:
|
style = xlwt.easyxf('pattern: pattern solid')
|
else:
|
style = xlwt.easyxf('pattern: pattern solid, fore_colour light_yellow')
|
font = xlwt.Font()
|
style.font = font
|
|
if trade_info:
|
if trade_info[0] == 0:
|
font.colour_index = 53
|
elif trade_info[0] == 1:
|
font.colour_index = 17
|
elif trade_info[0] == 2:
|
font.colour_index = 10
|
|
for i in range(len(data)):
|
if data[i] is None:
|
continue
|
if i == 8 and data[i]:
|
ws.write(index, i, data[i], cancel_style)
|
else:
|
ws.write(index, i, data[i], style)
|
wb.save(file_name)
|
|
|
def export_l2_data_origin(code, datas, key, dest_dir=f"{constant.get_path_prefix()}/export/l2_origin"):
|
file_dir = "{}/{}".format(dest_dir, code)
|
if not os.path.exists(file_dir):
|
os.makedirs(file_dir)
|
|
file_name = "{}/{}/{}.xls".format(dest_dir, code, key)
|
wb = xlwt.Workbook()
|
ws = wb.add_sheet('sheet1')
|
ws.write(0, 0, '序号')
|
ws.write(0, 1, '时间')
|
ws.write(0, 2, '买撤间隔')
|
ws.write(0, 3, '价格')
|
ws.write(0, 4, '手数')
|
ws.write(0, 5, '类型')
|
index = 0
|
for data in datas:
|
index += 1
|
ws.write(index, 0, index)
|
ws.write(index, 1, data["time"])
|
cancel_time = data["cancelTime"]
|
if cancel_time > 0:
|
cancel_time = "{}".format(cancel_time)
|
if data["cancelTimeUnit"] == 0:
|
cancel_time += "s"
|
elif data["cancelTimeUnit"] == 1:
|
cancel_time += "m"
|
elif data["cancelTimeUnit"] == 2:
|
cancel_time += "h"
|
|
ws.write(index, 2, cancel_time)
|
ws.write(index, 3, data["price"])
|
ws.write(index, 4, data["num"])
|
if data["operateType"] == 0:
|
ws.write(index, 5, '买')
|
elif data["operateType"] == 1:
|
ws.write(index, 5, '买撤')
|
wb.save(file_name)
|
return file_name
|
|
|
def test(code):
|
progresses = log_export.export_trade_progress(code)
|
local_today_datas = log_export.load_l2_from_log("2023-04-04")
|
datas = local_today_datas[code]
|
num_operate_map = {}
|
l2.l2_data_util.load_num_operate_map(num_operate_map, code, datas)
|
|
|
if __name__ == "__main__":
|
try:
|
get_l2_datas("600990")
|
except Exception as e:
|
logging.exception(e)
|
