# lhxt_version **Repository Path**: alonemelive/lhxt_version ## Basic Information - **Project Name**: lhxt_version - **Description**: 量化交易投研平台发布版本：包括API接口，回测接口，客户端等 - **Primary Language**: Unknown - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 2 - **Created**: 2025-04-18 - **Last Updated**: 2025-04-18 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README ## 量化交易投研平台 ### 一、项目简介 ​ **该项目包含数据接口lhxtApi、回测系统backtest、客户端lhxt_client等，提供回测、数据分析、模拟交易、实盘交易等一站式服务。** ### 二、CtpPlus - ctp接口简介 ​ **CtpPlus是上期技术CTP API的Python封装，项目已开源，详细介绍见文档** ​ **gitee地址：**[https://gitee.com/syealfalfa/CtpPlus-master](https://gitee.com/syealfalfa/CtpPlus-master) ### 三、lhxtApi - 数据接口简介 #### 1.安装命令 ```python # 无python版本要求 pip install lhxtApi-0.0.1-py3-none-any.whl # python3 安装包 ``` #### 2.客户端 ```python from lhxtApi.futures_base import FuturesBase class Client(FuturesBase): def __init__(self): super().__init__() def on_futures_day_data(self, data): print(data) def on_futures_bar_data(self, data): print(data) def on_futures_tick_data(self, data): print(data) if __name__ == '__main__': client = Client() username = 'admin' password = '88888888' symbols = ['sa2409', 'ur2409'] # 合约列表 start_date = datetime.strptime('2024.12.03', '%Y.%m.%d') end_date = datetime.strptime('2024.12.03', '%Y.%m.%d') start_datetime = datetime.strptime('2024.11.29 21:00:00', '%Y.%m.%d %H:%M:%S') end_datetime = datetime.strptime('2024.11.29 21:10:00', '%Y.%m.%d %H:%M:%S') ``` ##### 2.1 注册账号 ```python # 1.注册账号 rt = client.register(username, password, '手机号') print(rt) # 字典 ``` ##### 2.2 连接数据接口 ```python # 本地dolphindb # rt = client.session(username,password,ip='localhost:8848',database='dolphindb') # mysql # rt = client.session(username,password,ip='localhost:3306',database='mysql') # 网络接口 rt = client.session(username,password) print(f'rt = {rt}') # 返回0，则登录成功 ``` ##### 2.3 修改证书到期时间 ```python # 2. change_expire_date 管理员账号登录后才可修改 rt = client.update_expire_date('root', datetime.strptime('2026.08.07','%Y.%m.%d')) print(rt) ``` #### 3.客户端读取数据示例 ##### 3.1 获取日线数据 ```python # get_futures_day_data # flag=True 转换为DataFrame直接返回，flag=Flase，流式接收数据，通过回调函数返回 rt = client.get_futures_day_data(symbols=['SA2409', 'I2409'], start_date=start_date, end_date=end_date, flag=False) print(rt) ``` ```python # 结果展示 rt = 0 {'Id': 1, 'OpenPrice': 737.0, 'LowestPrice': 728.0, 'HighestPrice': 769.0, 'ClosePrice': 768.0, 'UpperLimitPrice': 830.5, 'PrevClose': 740.0, 'OpenInterest': 452395, 'LowerLimitPrice': 666.5, 'PrevSettlement': 748.5, 'TotalTurnover': 46277883200.0, 'InstrumentID': 'I2409', 'Settlement': 745.0, 'TradingDay': '2024-04-01'} ``` ##### 3.2 获取bar数据 ```python # get_futures_bar_data # frequency默认为1m， 可选5m,10m,15m,30m,60m rt = client.get_futures_bar_data(symbols=['SA2409', 'UR2409'], start_date=start_date, end_date=end_date, frequency='1m', flag=False) print(rt) # 或 rt = client.get_futures_bar_data(symbols=['SA2409', 'UR2409'], start_date=start_datetime, end_date=end_datetime, frequency='1m', flag=False) print(rt) ``` ```python # 结果展示 rt = 0 {'Id': 6091, 'OpenPrice': 2299.0, 'LowestPrice': 2285.0, 'HighestPrice': 2299.0, 'ClosePrice': 2287.0, 'Volume': 28210, 'DateTime': '2024-04-26 21:01:00', 'OpenInterest': 887779, 'TotalTurnover': 1293146400.0, 'InstrumentID': 'SA2409', 'TradingDay': '2024-04-29'} ``` ##### 3.3 获取tick数据 ```python # get_futures_tick_data # start_datetime和end_datetime可选date字符串或datetime字符串 rt = client.get_futures_tick_data(symbols=['SA2409', 'UR2409'], start_datetime=start_datetime, end_datetime=end_datetime, flag=False) print(rt) # 或 rt = client.get_futures_tick_data(symbols=['SA2409', 'UR2409'], start_datetime=start_date, end_datetime=end_date, flag=False) print(rt) ``` ```python # 结果展示 rt = 0 {'Id': 304279, 'LowestPrice': 2161.0, 'Turnover': 61509280320.0, 'PreClosePrice': 2247.0, 'LastPrice': 2173.0, 'InstrumentID': 'SA2409', 'TradingDay': '2024-04-30', 'OpenPrice': 2238.0, 'HighestPrice': 2246.0, 'UpperLimitPrice': 2491, 'DateTime': '2024-04-30 14:30:00', 'PreSettlementPrice': 2264.0, 'Volume': 1394136, 'LowerLimitPrice': 2037.0, 'OpenInterest': 768508.0, 'AskPrice1': 2174.0, 'AskPrice2': 0.0, 'AskPrice3': 0.0, 'AskPrice4': 0.0, 'AskPrice5': 0.0, 'AskVolume1': 92, 'AskVolume2': 0, 'AskVolume3': 0, 'AskVolume4': 0, 'AskVolume5': 0, 'BidPrice1': 2173.0, 'BidPrice2': 0.0, 'BidPrice3': 0.0, 'BidPrice4': 0.0, 'BidPrice5': 0.0, 'BidVolume1': 321, 'BidVolume2': 0, 'BidVolume3': 0, 'BidVolume4': 0, 'BidVolume5': 0} {'Id': 304278, 'LowestPrice': 2161.0, 'Turnover': 61506103680.0, 'PreClosePrice': 2247.0, 'LastPrice': 2173.0, 'InstrumentID': 'SA2409', 'TradingDay': '2024-04-30', 'OpenPrice': 2238.0, 'HighestPrice': 2246.0, 'UpperLimitPrice': 2491, 'DateTime': '2024-04-30 14:30:00', 'PreSettlementPrice': 2264.0, 'Volume': 1394064, 'LowerLimitPrice': 2037.0, 'OpenInterest': 768534.0, 'AskPrice1': 2174.0, 'AskPrice2': 0.0, 'AskPrice3': 0.0, 'AskPrice4': 0.0, 'AskPrice5': 0.0, 'AskVolume1': 109, 'AskVolume2': 0, 'AskVolume3': 0, 'AskVolume4': 0, 'AskVolume5': 0, 'BidPrice1': 2173.0, 'BidPrice2': 0.0, 'BidPrice3': 0.0, 'BidPrice4': 0.0, 'BidPrice5': 0.0, 'BidVolume1': 344, 'BidVolume2': 0, 'BidVolume3': 0, 'BidVolume4': 0, 'BidVolume5': 0} ``` ##### 3.4 获取交易日列表 ```python # 1. get_trade_day_list # start_date(datetime)：开始日期 # end_date(datetime): 结束日期 datas = client.get_trade_day_list(start_date, end_date) print(datas) ``` ``` # 结果展示 ['2024.04.01', '2024.04.02', '2024.04.03', '2024.04.08', '2024.04.09', '2024.04.10', '2024.04.11', '2024.04.12', '2024.04.15', '2024.04.16', '2024.04.17', '2024.04.18', '2024.04.19', '2024.04.22', '2024.04.23', '2024.04.24', '2024.04.25', '2024.04.26', '2024.04.29'] ``` ##### 3.5 获取期货合约基础信息 ```python # 2. get_futures_instrument_info # 查询一个合约返回字典数据，查询多个合约则返回列表数据 datas = client.get_futures_instrument_info(symbols=['fb2409', 'jm2409'], flag=False) print(datas) ``` ``` # 结果展示 [{'VolumeMultiple': 10, 'IsTrading': 1, 'OptionsType': None, 'ExpireDate': '20240913', 'MaxMarketOrderVolume': 1000, 'OpenDate': '20230915', 'MaxMarginSideAlgorithm': '0', 'ProductClass': 1, 'PositionDateType': '2', 'LongMarginRatio': 0.1, 'InstLifePhase': '1', 'DeliveryYear': 2024, 'Id': 16999, 'EndDelivDate': '20240920', 'InstrumentName': '纤维板2409', 'InstrumentID': 'fb2409', 'PriceTick': 0.5, 'PositionType': '2', 'ExchangeID': 'DCE', 'ProductID': 'fb', 'CreateDate': '20230915', 'UnderlyingMultiple': 0.0, 'StartDelivDate': '0', 'ShortMarginRatio': 0.1, 'DeliveryMonth': 9, 'MinMarketOrderVolume': 1, 'StrikePrice': 0.0, 'MinLimitOrderVolume': 1, 'MaxLimitOrderVolume': 1000}, {'VolumeMultiple': 60, 'IsTrading': 1, 'OptionsType': None, 'ExpireDate': '20240913', 'MaxMarketOrderVolume': 1000, 'OpenDate': '20230915', 'MaxMarginSideAlgorithm': '0', 'ProductClass': 1, 'PositionDateType': '2', 'LongMarginRatio': 0.2, 'InstLifePhase': '1', 'DeliveryYear': 2024, 'Id': 17322, 'EndDelivDate': '20240920', 'InstrumentName': '焦煤2409', 'InstrumentID': 'jm2409', 'PriceTick': 0.5, 'PositionType': '2', 'ExchangeID': 'DCE', 'ProductID': 'jm', 'CreateDate': '20230915', 'UnderlyingMultiple': 0.0, 'StartDelivDate': '0', 'ShortMarginRatio': 0.2, 'DeliveryMonth': 9, 'MinMarketOrderVolume': 1, 'StrikePrice': 0.0, 'MinLimitOrderVolume': 1, 'MaxLimitOrderVolume': 1000}] ``` ##### 3.6 获取期货合约手续费率 ```python # 3. get_futures_instrument_commission_rate datas = client.get_futures_instrument_commission_rate(symbols=['jm2409', 'RM2501']) print(datas) ``` ``` # 结果展示 {'CloseTodayRatioByVolume': 0.0, 'CloseTodayRatioByMoney': 0.00035424, 'OpenRatioByMoney': 0.00035424, 'CloseRatioByVolume': 0.0, 'OpenRatioByVolume': 0.0, 'Id': 256, 'InstrumentID': 'jm2409', 'FeeType': '0', 'CloseRatioByMoney': 0.00011808} ``` ##### 3.7 获取期货合约保证金率 ```python # 4. get_futures_instrument_margin_rate # hedge_flag 投机套保标识--> 1: 投机，2：套保，3：套利 datas = client.get_futures_instrument_margin_rate(symbols=['RM501'], hedge_flag=2) print(datas) ``` ``` # 结果展示 {'LongMarginRatioByVolume': 0.0, 'ShortMarginRatioByMoney': 0.11, 'IsRelative': 0, 'Id': 1, 'HedgeFlag': 2, 'ShortMarginRatioByVolume': 0.0, 'InstrumentID': 'RM501', 'LongMarginRatioByMoney': 0.11} ``` ##### 3.8 获取指定交易所的合约代码 ```python # 5. get_futures_exchange_instruments # 获取大商所的所有期货合约 datas = client.get_futures_exchange_instruments(exchange='DCE', product_class=1) print(datas) ``` ``` # 结果展示 ['jd2408', 'jd2405', 'jd2407', 'jd2406', 'jd2410', 'jd2411', 'jd2412', 'jd2409', 'jd2504', 'jd2502', 'jd2501', 'jd2503', 'fb2406', 'fb2409', 'fb2408', 'fb2405', 'fb2407', 'fb2412', 'fb2411', 'fb2501', 'fb2410', 'fb2503', 'fb2502', 'fb2504', 'eg2408', 'eg2409', 'eg2405', 'eg2407', 'eg2406', 'eg2410', 'eg2411', 'eg2412', 'eg2504', 'eg2502', 'eg2501', 'eg2503', 'v2410', 'v2405', 'v2408', 'v2407', 'v2412', 'v2411', 'v2501', 'v2503', 'v2502', 'v2504', 'j2408', 'j2405', 'j2407', 'j2412', 'j2411', 'j2410', 'j2501', 'j2406', 'j2409', 'j2503', 'j2502', 'j2504', 'pp2406', 'pp2405', 'pp2408', 'pp2407', 'pp2412', 'pp2411', 'pp2501', 'pp2409', 'pp2410', 'pp2503', 'pp2502', 'pp2504', 'b2408', 'b2405', 'b2407', 'b2412', 'b2406', 'b2409', 'b2411', 'b2501', 'b2410', 'b2503', 'b2502', 'b2504', 'l2408', 'l2405', 'l2407', 'l2412', 'l2411', 'l2406', 'l2409', 'l2410', 'l2501', 'l2503', 'l2502', 'l2504', 'v2406', 'v2409', 'cs2405', 'cs2407', 'cs2411', 'cs2409', 'cs2501', 'cs2503', 'jm2408', 'jm2405', 'jm2407', 'jm2412', 'jm2411', 'jm2501', 'jm2406', 'jm2409', 'jm2410', 'jm2503', 'jm2502', 'jm2504', 'y2408', 'y2407', 'y2412', 'y2411', 'y2501', 'y2409', 'y2503', 'm2408', 'm2405', 'm2407', 'm2412', 'm2411', 'm2409', 'm2501', 'm2503', 'lh2405', 'lh2407', 'lh2411', 'lh2409', 'lh2501', 'lh2503', 'a2405', 'a2407', 'a2411', 'a2501', 'a2409', 'a2503', 'eb2408', 'eb2405', 'eb2407', 'eb2406', 'eb2410', 'eb2411', 'eb2412', 'eb2409', 'eb2504', 'eb2502', 'eb2501', 'eb2503', 'y2405', 'pg2408', 'pg2405', 'pg2407', 'pg2406', 'pg2410', 'pg2411', 'pg2412', 'pg2409', 'i2408', 'i2405', 'i2407', 'i2412', 'i2411', 'i2406', 'i2409', 'i2501', 'i2410', 'i2503', 'i2502', 'i2504', 'bb2406', 'bb2408', 'bb2405', 'bb2407', 'bb2412', 'bb2411', 'bb2409', 'bb2410', 'bb2501', 'bb2503', 'bb2502', 'bb2504', 'c2409', 'c2405', 'c2407', 'c2411', 'c2501', 'c2503', 'p2408', 'p2405', 'p2407', 'p2412', 'p2411', 'p2409', 'p2501', 'p2406', 'p2410', 'p2503', 'p2502', 'p2504', 'rr2406', 'rr2405', 'rr2408', 'rr2407', 'rr2412', 'rr2411', 'rr2501', 'rr2409', 'rr2410', 'rr2503', 'rr2502', 'rr2504', 'pg2504', 'pg2502', 'pg2501', 'pg2503'] ``` ##### 3.9 获取指定品种的现货价格 ```python # 6.get_commodity_spot_price # product_id：产品代码（交易所.品种） # start_date(datetime)：开始日期 # end_date(datetime): 结束日期 # 情况一、start_date 或 end_date 可为None; 情况二、start_date 和 end_datew 为None; 情况三、start_date 和 end_date 不为None df = client.get_commodity_spot_price(product_id='SHFE.ag', start_date=start_date, end_date=end_date) ``` ``` # 结果展示 TradingDay ProductID ProductName BasisAddr DailySpotPrice 0 2024-12-03 SHFE.ag 银 上海 7646 1 2024-12-04 SHFE.ag 银 上海 7769 2 2024-12-05 SHFE.ag 银 上海 7798 ``` ##### 3.10 获取某个产品代码的主力合约 ```python 7.get_main_futures_contract # product_id：产品代码（交易所.品种） # start_date(datetime)：开始日期 # end_date(datetime): 结束日期 # 情况一、start_date 或 end_date 可为None; 情况二、start_date 和 end_datew 为None; 情况三、start_date 和 end_date 不为None df = client.get_main_futures_contract(product_id='SHFE.ag', start_date=None, end_date=None) ``` ``` # 结果展示 TradingDay ProductID InstrumentID 0 2024-12-02 SHFE.ag ag2502 1 2024-12-03 SHFE.ag ag2502 2 2024-12-04 SHFE.ag ag2502 3 2024-12-05 SHFE.ag ag2502 ``` ### 四、回测系统：lhxt_backtest ​ **回测系统永久免费** #### 1.安装命令 ```python # 对python环境有版本要求，目前只提供python3.10的安装包 pip install lhxt_backtest-0.0.1-cp310-cp310-win_amd64.whl ``` #### 2.接口说明 **接口详见：template.py** ```python # -*- codeing:utf-8 -*- ''' @author: jiaoyulong @datetime: 2024/8/3 23:11 @Blog: ''' from datetime import datetime from typing import Dict, List, Any from lhxt_backtest.data_type import ( AccountData, PositionData, HedgeFlag, ProfitLoss) class CtaTemplate: """撮合交易模版""" def __init__(self): self.parent = None # 包含：合约基础信息、期货合约手续费率、期货合约保证金率 self.strategy_id: str = '' self.db_path: str = '' # sqlite路径 self.trading_day: str = '' # 交易日 self.date_time: str = '' # 时间 self.hedge_flag: int = HedgeFlag.HedgeFlagSpeculation.value # 默认投机 self.order_ref: int = 0 # 报单编号 self.max_asset: float = 0. # 最大资产 self.min_asset: float = 0. # 最小资产 self.drawdown: float = 0. # 回撤后的最低资产 self.account: AccountData = AccountData() self.position: Dict[str, Dict[int, PositionData]] = {} # {'ag2412': {多：~, 空：~} self.position_detail: Dict[str, Dict[int, List]] = {} # {'ag2412': {多：~, 空：~} self.trade: Dict[str, List] = {} self.order: Dict[str, List] = {} # 委托回报单索引（0: 买开，1: 买平，2: 卖开，3: 卖平） self.order_index: Dict[str, Dict[int, List]] = {} # 止盈止损 {合约代码: {方向: 止盈止损数据}} self.profit_loss_data: Dict[str, Dict[int, List]] = {} def on_init(self, parent, symbols: List, start_date: datetime, end_date: datetime, hedge_flag: int, db_path: str, strategy_id: str = datetime.now().strftime('%Y%m%d_%H%M%S'), equity: float = 1000000., avail_cash: float = 1000000.) -> None: """ :param parent: 合约信息 :param symbols: 参数回测的合约(用;隔开) :param start_date: 开始日期 :param end_date: 结束日期 :param hedge_flag: 投机套保标识 :param db_path: sqlite数据库绝对路径 :param strategy_id: 策略id :param equity: 账户权益 :param avail_cash: 可用资金 """ pass def query_order(self, instrument_id: str) -> List: """ 查询订单 :param instrument_id: 合约代码 :return: 成功返回该合约订单数据，失败返回[] """ pass def quere_trade(self, instrument_id: str) -> List: """ 查询成交 :param instrument_id: 合约代码 :return: 成功返回该合约成交数据，失败返回[] """ pass def query_postion(self, instrument_id: str, direction: int) -> PositionData | None: """ 查询合约总的持仓数据 :param direction: 持仓方向（多头：0，空头：1） :param instrument_id: 合约代码 :return: 成功返回持仓数据，失败返回None """ pass def query_postion_detail(self, instrument_id: str, direction: int) -> List: """ 查询合约持仓明细数据 :param direction: 持仓方向（多头：0，空头：1） :param instrument_id: 合约代码 :return: 成功返回持仓明细数据，失败返回None """ pass def buy_open(self, exchange_id: str, instrument_id: str, order_price: float, order_vol: int) -> int: """ 买开仓。与卖平仓为一组对应交易。 :param exchange_id: 交易所 :param instrument_id: 合约 :param order_price: 价格 :param order_vol: 数量 :return: """ pass def sell_close(self, exchange_id: str, instrument_id: str, order_price: float, order_vol: int, is_today=True) -> int: """ 卖平仓。与买开仓为一组对应交易。SHFE与INE区分平今与平昨。 :param exchange_id: :param instrument_id: :param order_price: :param order_vol: :param is_today: :return: """ pass def sell_open(self, exchange_id: str, instrument_id: str, order_price: float, order_vol: int) -> int: """ 卖开仓。与买平仓为一组对应交易。 :param exchange_id: :param instrument_id: :param order_price: :param order_vol: :return: """ pass def buy_close(self, exchange_id: str, instrument_id: str, order_price: float, order_vol: int, is_today: bool = True) -> int: """ 买平仓。与卖开仓为一组对应交易。SHFE与INE区分平今与平昨。 :param exchange_id: :param instrument_id: :param order_price: :param order_vol: :param is_today: :return: """ pass def cancel_order(self, instrument_id: str, direction: int, offset_flag: int, order_ref: str) -> int: """ 撤单请求。将撤单结构体参数传递给父类方法ReqOrderAction执行。 :param offset_flag: 开平标记 :param direction: 买卖方向 :param instrument_id:合约ID :param order_ref:报单引用，用来标识订单来源。根据该标识撤单。 :return: """ pass def req_from_bank_to_future_by_future(self, trade_amount: float = 100000.) -> int: """ 入金 当资金使用率超过120%时，执行一次入金操作 :param trade_amount: 入金金额 :return: 返回0 """ pass def req_from_future_to_bank_by_future(self, trade_amount: float = 100000.) -> int: """ 出金 :param trade_amount: 出金金额 :return: 成功返回0, 失败返回-1 """ pass def set_stop_proft_and_stop_loss(self, profit_loss: ProfitLoss) -> int: """ 设置止盈止损 :param profit_loss: 止盈止损数据 :return: """ pass def on_tick(self, tick: Dict) -> None: """ tick数据（回调接口） :param tick: tick数据 :return: """ pass def on_bar(self, bar: Dict) -> None: """ 合成bar数据（回调接口） :param bar: bar数据 :return: """ pass def on_day(self, day: Dict) -> None: """ 日线数据（回调接口） :param day: day数据 :return: """ pass def on_order(self, order): """ 委托回报（回调接口） :return: """ pass def on_trade(self, trade): """ 成交回报（回调接口） :param trade: :return: """ pass def set_stop_loss(self, instrument_id: str, direction: int, volume: int, stop_price: float, price_type: PriceType = PriceType.MARKET_PRICE, term: TermType = TermType.PERMANENT): """ 设置止损 :param instrument_id: 合约代码 :param direction: 持仓方向：多(0)或空(1) :param volume: 止损手数 :param stop_price: 止损价 :param price_type: 止盈止损价格类型(0:市价，1:最新价，2:对手价） :param term: 有效期（默认永久有效） :return: """ pass def set_stop_profit(self, instrument_id: str, direction: int, volume: int, stop_price: float, price_type: PriceType = PriceType.MARKET_PRICE, term: TermType = TermType.PERMANENT): """ 设置止盈 :param instrument_id: 合约代码 :param direction: 持仓方向：多(0)或空(1) :param volume: 止损手数 :param stop_price: 止损价 :param price_type: 止盈止损价格类型(0:市价，1:最新价，2:对手价） :param term: 有效期（默认永久有效） :return: """ pass def set_dynamics_stop_loss(self, instrument_id: str, direction: int, volume: int, price_difference: float, price_type: PriceType = PriceType.MARKET_PRICE, term: TermType = TermType.PERMANENT): """ 设置动态止损 :param instrument_id: 合约代码 :param direction: 持仓方向：多(0)或空(1) :param volume: 止损手数 :param price_difference: 回撤价差 :param price_type: 止盈止损价格类型(0:市价，1:最新价，2:对手价） :param term: 有效期（默认永久有效） :return: """ pass def set_break_even(self, instrument_id: str, direction: int, volume: int, price_difference: float, price_type: PriceType = PriceType.MARKET_PRICE, term: TermType = TermType.PERMANENT): """ 设置保本单 :param instrument_id: 合约代码 :param direction: 持仓方向：多(0)或空(1) :param volume: 止损手数 :param price_difference: 保本价差 :param price_type: 止盈止损价格类型(0:市价，1:最新价，2:对手价） :param term: 有效期（默认永久有效） :return: """ pass def on_position_profit(self, position: Dict): """ 计算合约持仓盈亏后调用该接口（回调接口） :param position: :return: """ pass def write_log(self, msg: Any) -> None: """ 打印日志 :param msg: 内容 :return: """ pass ``` #### 3.lhxt.db各表简介 ##### 3.1 账户表：account_table ```python # 注释 start_date: str # 开始日期 end_date: str # 结束日期 symbols: str # 参与回测的合约(用;隔开) start_cash: float = 0. # 起始资金 avail_cash: float = 0. # 可用资金 equity_cash: float = 0. # 权益 deposit: float = 0. # 累计入金金额 withdraw: float = 0. # 累计出金金额 margin: float = 0. # 保证金 frozen_margin: float = 0. # 冻结保证金 fee: float = 0. # 累计手续费 frozen_fee: float = 0. # 冻结手续费 max_drawdown: float = 0. # 最大回撤 cash_usage_rate: float = 0. # 资金使用率 trading_day: str # 交易日 ``` ##### 3.2 总持仓表：position_table ```python # 注释 symbol: str = '' # 合约代码 position_volume: int = 0 # 总持仓量 margin: float = 0. # 保证金 frozen_closing: int = 0 # 平仓冻结持仓 avail_volume: int = 0 # 总可用手数（总持仓 - 平仓冻结） td_open_volume: int = 0 # 今日开仓手数 td_avail_volume: int = 0 # 今可用手数（今日开仓手数 - 平今冻结） yd_open_volume: int = 0 # 历史开仓手数 yd_avail_volume: int = 0 # 历史可用手数（不包括冻结） close_profit: float = 0. # 平仓盈亏 profit_price_difference: float = 0. # 盈利价差（(最新价 - 持仓均价) * 总持仓量） position_profit: float = 0. # 持仓盈亏（盈利价差 * 合约乘数 * 总持仓量） open_avg_price: float = 0. # 持仓均价 direction: int = 0 # 持仓方向（多头：0，空头：1） trading_day: str # 交易日 ``` ##### 3.3 持仓明细表：position_detail_table ```python # 注释 symbol: str = '' # 合约代码 position_volume: int = 0 # 持仓量 margin: float = 0. # 保证金 frozen_closing: int = 0 # 平仓冻结持仓 avail_volume: int = 0 # 总可用手数（总持仓 - 平仓冻结） td_open_volume: int = 0 # 今日开仓数量 yd_open_volume: int = 0 # 历史开仓数量 direction: int = 0 # 持仓方向（多头：0，空头：1） open_price: float = 0. # 成交价 position_type: int = PositionType.PositionDateToday.value # 持仓类型（今日持仓：1，历史持仓：2） create_datetime: str = '' # 订单创建时间（YYYYmmdd HHMMSS） update_datetime: str = '' # 订单更新时间 trading_day: str # 交易日 ``` ##### 3.4 成交数据表：trade_table ```python # 注释 symbol: str = '' # 合约代码 filled_price: float = 0. # 成交价 close_profit: float = 0. # 平仓盈亏（平仓时计算盈亏） margin: float = 0. # 保证金 fee: float = 0. # 手续费 filled_volume: int = 0 # 成交手数 direction: int = 0 # 买卖方向（多头：2，空头：3） offset_flag: int = 0 # 开平标记（开仓：0，平仓：1） hedge_flag: int = HedgeFlag.HedgeFlagSpeculation.value # 投机、套保、套利标识 order_sys_id: int = 0 # 订单编号 create_datetime: str = '' # 订单创建时间 update_datetime: str = '' # 订单更新时间 trading_day: str # 交易日 ``` ##### 3.5 委托数据表：order_table ```python # 注释 symbol: str = '' # 合约代码 open_volume: int = 0 # 委托量 open_price: float = 0. # 委托价 margin: float = 0. # 保证金（已成交） frozen_margin: float = 0. # 冻结保证金（未成交） filled_volume: int = 0 # 已成交手数 volume_total: int = 0 # 未成交数量（可撤） cancel_volume: int = 0 # 已撤单手数 order_fee: float = 0. # 订单手续费（已成交） frozen_order_fee: float = 0. # 冻结手续费（未成交） direction: int = 0 # 买卖方向（买：0，卖：1） offset_flag: int = 0 # 开平标记（开仓：0，平仓：1） hedge_flag: int = HedgeFlag.HedgeFlagSpeculation.value # 投机、套保、套利标识 order_ref: str = '' # 报单引用 order_sys_id: int = 0 # 订单编号 order_type: int = 0 # 报单状态 create_datetime: str = '' # 订单创建时间 update_datetime: str = '' # 订单更新时间 trading_day: str # 交易日 ``` ##### 3.6 期货合约基础信息表：futures_instrumentid_info ```python # 注释 ///保留的无效字段 TThostFtdcOldInstrumentIDType reserve1; ///交易所代码 TThostFtdcExchangeIDType ExchangeID; ///合约名称 TThostFtdcInstrumentNameType InstrumentName; ///保留的无效字段 TThostFtdcOldExchangeInstIDType reserve2; ///保留的无效字段 TThostFtdcOldInstrumentIDType reserve3; ///产品类型 TThostFtdcProductClassType ProductClass; ///交割年份 TThostFtdcYearType DeliveryYear; ///交割月 TThostFtdcMonthType DeliveryMonth; ///市价单最大下单量 TThostFtdcVolumeType MaxMarketOrderVolume; ///市价单最小下单量 TThostFtdcVolumeType MinMarketOrderVolume; ///限价单最大下单量 TThostFtdcVolumeType MaxLimitOrderVolume; ///限价单最小下单量 TThostFtdcVolumeType MinLimitOrderVolume; ///合约数量乘数 TThostFtdcVolumeMultipleType VolumeMultiple; ///最小变动价位 TThostFtdcPriceType PriceTick; ///创建日 TThostFtdcDateType CreateDate; ///上市日 TThostFtdcDateType OpenDate; ///到期日 TThostFtdcDateType ExpireDate; ///开始交割日 TThostFtdcDateType StartDelivDate; ///结束交割日 TThostFtdcDateType EndDelivDate; ///合约生命周期状态 TThostFtdcInstLifePhaseType InstLifePhase; ///当前是否交易 TThostFtdcBoolType IsTrading; ///持仓类型 TThostFtdcPositionTypeType PositionType; ///持仓日期类型 TThostFtdcPositionDateTypeType PositionDateType; ///多头保证金率 TThostFtdcRatioType LongMarginRatio; ///空头保证金率 TThostFtdcRatioType ShortMarginRatio; ///是否使用大额单边保证金算法 TThostFtdcMaxMarginSideAlgorithmType MaxMarginSideAlgorithm; ///保留的无效字段 TThostFtdcOldInstrumentIDType reserve4; ///执行价 TThostFtdcPriceType StrikePrice; ///期权类型 TThostFtdcOptionsTypeType OptionsType; ///合约基础商品乘数 TThostFtdcUnderlyingMultipleType UnderlyingMultiple; ///组合类型 TThostFtdcCombinationTypeType CombinationType; ///合约代码 TThostFtdcInstrumentIDType InstrumentID; ///合约在交易所的代码 TThostFtdcExchangeInstIDType ExchangeInstID; ///产品代码 TThostFtdcInstrumentIDType ProductID; ///基础商品代码 TThostFtdcInstrumentIDType UnderlyingInstrID; ``` ##### 3.7 期货合约保证金率表：futures_margin_rate ```python # 注释 ///保留的无效字段 TThostFtdcOldInstrumentIDType reserve1; ///投资者范围 TThostFtdcInvestorRangeType InvestorRange; ///经纪公司代码 TThostFtdcBrokerIDType BrokerID; ///投资者代码 TThostFtdcInvestorIDType InvestorID; ///投机套保标志 TThostFtdcHedgeFlagType HedgeFlag; ///多头保证金率 TThostFtdcRatioType LongMarginRatioByMoney; ///多头保证金费 TThostFtdcMoneyType LongMarginRatioByVolume; ///空头保证金率 TThostFtdcRatioType ShortMarginRatioByMoney; ///空头保证金费 TThostFtdcMoneyType ShortMarginRatioByVolume; ///是否相对交易所收取 TThostFtdcBoolType IsRelative; ///交易所代码 TThostFtdcExchangeIDType ExchangeID; ///投资单元代码 TThostFtdcInvestUnitIDType InvestUnitID; ///合约代码 TThostFtdcInstrumentIDType InstrumentID; ``` ##### 3.8 期货合约手续费率表：futures_commission_rate ```python # 注释 ///保留的无效字段 TThostFtdcOldInstrumentIDType reserve1; ///投资者范围 TThostFtdcInvestorRangeType InvestorRange; ///经纪公司代码 TThostFtdcBrokerIDType BrokerID; ///投资者代码 TThostFtdcInvestorIDType InvestorID; ///开仓手续费率 TThostFtdcRatioType OpenRatioByMoney; ///开仓手续费 TThostFtdcRatioType OpenRatioByVolume; ///平仓手续费率 TThostFtdcRatioType CloseRatioByMoney; ///平仓手续费 TThostFtdcRatioType CloseRatioByVolume; ///平今手续费率 TThostFtdcRatioType CloseTodayRatioByMoney; ///平今手续费 TThostFtdcRatioType CloseTodayRatioByVolume; ///交易所代码 TThostFtdcExchangeIDType ExchangeID; ///业务类型 TThostFtdcBizTypeType BizType; ///投资单元代码 TThostFtdcInvestUnitIDType InvestUnitID; ///合约代码 TThostFtdcInstrumentIDType InstrumentID; ``` ##### 3.9 日志表：log_table ```python # 注释 strategy_id:str # 策略名 text:str # 日志内容 ``` #### 4.本地执行策略Demo trend_following_strategy.py 如下： ```python # -*- codeing:utf-8 -*- ''' @author: jiaoyulong @datetime: 2024/9/13 14:46 @Blog: 趋势跟踪策略 ''' from datetime import datetime from typing import Dict, List import talib as tb from lhxt_backtest.data_type import PositionDirection from lhxt_backtest.future_account import lhxt_simulate_account import numpy as np from lhxt_backtest.lhxt_backtest import BacktestingEngine UP_OPEN_NUM = 10 DOWN_OPEN_NUM = 10 class TestStrategy(BacktestingEngine): def __init__(self, config_data): super().__init__(config_data=config_data) self.close_data: Dict[str: List[float]] = {} # 记录合约收盘价 self.flag_dict = {} for instrument_id in config_data.subscribe_list: # up_open_num = 0 # 上涨标记 # down_open_num = 0 # 下跌标记 self.flag_dict[instrument_id] = {'up_open_num': UP_OPEN_NUM, 'down_open_num': DOWN_OPEN_NUM} def on_tick(self, tick: Dict) -> None: # self.buy_open(tick['ExchangeID'], tick['InstrumentID'], tick['LastPrice'], 10) pass def on_bar(self, bar: Dict) -> None: if bar['InstrumentID'] not in self.close_data.keys(): self.close_data[bar['InstrumentID']] = [] self.close_data[bar['InstrumentID']].append(bar['LastPrice']) ma_10 = tb.SMA(np.array(self.close_data[bar['InstrumentID']]).astype(np.float64), timeperiod=10) ma_60 = tb.SMA(np.array(self.close_data[bar['InstrumentID']]).astype(np.float64), timeperiod=60) # print(f'日期：{bar["DateTime"]} ma_10 = {ma_10[-1]}, ma_60 = {ma_60[-1]}') if len(self.close_data[bar['InstrumentID']]) < 60: return if ma_10[-1] > ma_60[-1]: if self.flag_dict[bar['InstrumentID']]['up_open_num'] == 0: return else: self.flag_dict[bar['InstrumentID']]['up_open_num'] -= 1 self.flag_dict[bar['InstrumentID']]['down_open_num'] = DOWN_OPEN_NUM if bar['LastPrice'] > ma_10[-1]: self.buy_open(bar['ExchangeID'], bar['InstrumentID'], bar['LastPrice'], 1) position = self.query_postion(bar['InstrumentID'], PositionDirection.PosiDirectionShort.value) if position: if position.avail_volume >= 1: if bar['ExchangeID'] in ['SHFE', 'INE']: if position.td_open_volume > 0: self.buy_close(bar['ExchangeID'], bar['InstrumentID'], bar['LastPrice'] + 10, position.td_open_volume) if position.yd_open_volume > 0: self.buy_close(bar['ExchangeID'], bar['InstrumentID'], bar['LastPrice'] + 10, position.yd_open_volume, False) else: self.buy_close(bar['ExchangeID'], bar['InstrumentID'], bar['LastPrice'] + 10, position.avail_volume) else: if self.flag_dict[bar['InstrumentID']]['down_open_num'] == 0: return else: self.flag_dict[bar['InstrumentID']]['down_open_num'] -= 1 self.flag_dict[bar['InstrumentID']]['up_open_num'] = UP_OPEN_NUM if bar['LastPrice'] < ma_10[-1]: self.sell_open(bar['ExchangeID'], bar['InstrumentID'], bar['LastPrice'], 1) position = self.query_postion(bar['InstrumentID'], PositionDirection.PosiDirectionLong.value) if position: if position.avail_volume >= 1: if bar['ExchangeID'] in ['SHFE', 'INE']: if position.td_open_volume > 0: self.sell_close(bar['ExchangeID'], bar['InstrumentID'], bar['LastPrice'] - 10, position.td_open_volume) if position.yd_open_volume > 0: self.sell_close(bar['ExchangeID'], bar['InstrumentID'], bar['LastPrice'] - 10, position.yd_open_volume, False) else: self.sell_close(bar['ExchangeID'], bar['InstrumentID'], bar['LastPrice'] - 10, position.avail_volume) def on_order(self, order): self.write_log(order) def on_trade(self, trade): self.write_log(trade) # 1.设置止损 # if trade.direction == PositionDirection.PosiDirectionLong.value: # if trade.offset_flag == OpenCloseFlag.OffsetFlagOpen.value: # self.set_stop_loss(trade.symbol, trade.direction, trade.filled_volume, trade.filled_price - 30) # else: # if trade.offset_flag == OpenCloseFlag.OffsetFlagOpen.value: # self.set_stop_loss(trade.symbol, trade.direction, trade.filled_volume, trade.filled_price + 30) # 2.设置止盈 # if trade.direction == PositionDirection.PosiDirectionLong.value: # if trade.offset_flag == OpenCloseFlag.OffsetFlagOpen.value: # self.set_stop_profit(trade.symbol, trade.direction, trade.filled_volume, trade.filled_price + 30) # else: # if trade.offset_flag == OpenCloseFlag.OffsetFlagOpen.value: # self.set_stop_profit(trade.symbol, trade.direction, trade.filled_volume, trade.filled_price - 30) # 3.设置动态止损 if trade.offset_flag == OpenCloseFlag.OffsetFlagOpen.value: self.set_dynamics_stop_loss(trade.symbol, trade.direction, trade.filled_volume, trade.filled_price * 0.01) # 4.设置保本单 # if trade.offset_flag == OpenCloseFlag.OffsetFlagOpen.value: # self.set_break_even(trade.symbol, trade.direction, trade.filled_volume, 30) if __name__ == '__main__': # lhxtApi接口可以获取一下合约的数据 # CZCE:[ur2409, ur2405, sa2405, sa2409, ta2409, ta2405], # CFFEX:[ih2406、ih2407、ih2408、ih2409、t2406、t2409、ic2409、ic2408、ic2407、ic2406], # DCE:[i2409、i2405、m2405、m2409、p2405、p2409], # SHFE:[ao2405, ao2409, ag2412, fu2405, fu2409, fu2503, ru2405, ru2409], # INE:[ec2406, ec2408, ec2410], # GFEX:[si2409, si2407, si2406] subscribe_list = ['sa2409'， 'ag2412'] start_date = datetime.strptime('2024.08.01 21:00:00', '%Y.%m.%d %H:%M:%S') end_date = datetime.strptime('2024.08.30 15:00:00', '%Y.%m.%d %H:%M:%S') config = lhxt_simulate_account( username='', password='', strategy_id='均线策略_15m_test', subscribe_list=subscribe_list, start_date=start_date, end_date=end_date, db_path=r'E:\download\lhxt-client\database\lhxt.db', # db路径 frequency='15m', equity=1000000, database='lhxt' # 默认值为：lhxt ) test = TestStrategy(config_data=config) test.start() ``` #### 5.lhxtClient客户端策略Demo **优点：可多策略同时执行** test.py 如下： ```python # -*- codeing:utf-8 -*- ''' @author: jiaoyulong @datetime: 2024/9/13 14:46 @Blog: 趋势跟踪策略（用于客户端测试） ''' import sys from datetime import datetime from typing import Dict, List import talib as tb from lhxt_backtest.data_type import PositionDirection from lhxt_backtest.future_account import lhxt_simulate_account import numpy as np from lhxt_backtest.lhxt_backtest import BacktestingEngine UP_OPEN_NUM = 10 DOWN_OPEN_NUM = 10 class TestStrategy(BacktestingEngine): def __init__(self, config_data): super().__init__(config_data=config_data) self.close_data: Dict[str: List[float]] = {} # 记录合约收盘价 self.flag_dict = {} for instrument_id in config_data.subscribe_list: # up_open_num = 0 # 上涨标记 # down_open_num = 0 # 下跌标记 self.flag_dict[instrument_id] = {'up_open_num': UP_OPEN_NUM, 'down_open_num': DOWN_OPEN_NUM} def on_tick(self, tick: Dict) -> None: # self.buy_open(tick['ExchangeID'], tick['InstrumentID'], tick['LastPrice'], 10) pass def on_bar(self, bar: Dict) -> None: if bar['InstrumentID'] not in self.close_data.keys(): self.close_data[bar['InstrumentID']] = [] self.close_data[bar['InstrumentID']].append(bar['LastPrice']) ma_10 = tb.SMA(np.array(self.close_data[bar['InstrumentID']]).astype(np.float64), timeperiod=10) ma_60 = tb.SMA(np.array(self.close_data[bar['InstrumentID']]).astype(np.float64), timeperiod=60) # print(f'日期：{bar["DateTime"]} ma_10 = {ma_10[-1]}, ma_60 = {ma_60[-1]}') if len(self.close_data[bar['InstrumentID']]) < 60: return if ma_10[-1] > ma_60[-1]: if self.flag_dict[bar['InstrumentID']]['up_open_num'] == 0: return else: self.flag_dict[bar['InstrumentID']]['up_open_num'] -= 1 self.flag_dict[bar['InstrumentID']]['down_open_num'] = DOWN_OPEN_NUM if bar['LastPrice'] > ma_10[-1]: self.buy_open(bar['ExchangeID'], bar['InstrumentID'], bar['LastPrice'], 1) position = self.query_postion(bar['InstrumentID'], PositionDirection.PosiDirectionShort.value) if position: if position.avail_volume >= 1: if bar['ExchangeID'] in ['SHFE', 'INE']: if position.td_open_volume > 0: self.buy_close(bar['ExchangeID'], bar['InstrumentID'], bar['LastPrice'] + 10, position.td_open_volume) if position.yd_open_volume > 0: self.buy_close(bar['ExchangeID'], bar['InstrumentID'], bar['LastPrice'] + 10, position.yd_open_volume, False) else: self.buy_close(bar['ExchangeID'], bar['InstrumentID'], bar['LastPrice'] + 10, position.avail_volume) else: if self.flag_dict[bar['InstrumentID']]['down_open_num'] == 0: return else: self.flag_dict[bar['InstrumentID']]['down_open_num'] -= 1 self.flag_dict[bar['InstrumentID']]['up_open_num'] = UP_OPEN_NUM if bar['LastPrice'] < ma_10[-1]: self.sell_open(bar['ExchangeID'], bar['InstrumentID'], bar['LastPrice'], 1) position = self.query_postion(bar['InstrumentID'], PositionDirection.PosiDirectionLong.value) if position: if position.avail_volume >= 1: if bar['ExchangeID'] in ['SHFE', 'INE']: if position.td_open_volume > 0: self.sell_close(bar['ExchangeID'], bar['InstrumentID'], bar['LastPrice'] - 10, position.td_open_volume) if position.yd_open_volume > 0: self.sell_close(bar['ExchangeID'], bar['InstrumentID'], bar['LastPrice'] - 10, position.yd_open_volume, False) else: self.sell_close(bar['ExchangeID'], bar['InstrumentID'], bar['LastPrice'] - 10, position.avail_volume) def on_order(self, order): self.write_log(order) def on_trade(self, trade): self.write_log(trade) # 1.设置止损 # if trade.direction == PositionDirection.PosiDirectionLong.value: # if trade.offset_flag == OpenCloseFlag.OffsetFlagOpen.value: # self.set_stop_loss(trade.symbol, trade.direction, trade.filled_volume, trade.filled_price - 30) # else: # if trade.offset_flag == OpenCloseFlag.OffsetFlagOpen.value: # self.set_stop_loss(trade.symbol, trade.direction, trade.filled_volume, trade.filled_price + 30) # 2.设置止盈 # if trade.direction == PositionDirection.PosiDirectionLong.value: # if trade.offset_flag == OpenCloseFlag.OffsetFlagOpen.value: # self.set_stop_profit(trade.symbol, trade.direction, trade.filled_volume, trade.filled_price + 30) # else: # if trade.offset_flag == OpenCloseFlag.OffsetFlagOpen.value: # self.set_stop_profit(trade.symbol, trade.direction, trade.filled_volume, trade.filled_price - 30) # 3.设置动态止损 if trade.offset_flag == OpenCloseFlag.OffsetFlagOpen.value: self.set_dynamics_stop_loss(trade.symbol, trade.direction, trade.filled_volume, trade.filled_price * 0.01) # 4.设置保本单 # if trade.offset_flag == OpenCloseFlag.OffsetFlagOpen.value: # self.set_break_even(trade.symbol, trade.direction, trade.filled_volume, 30) def main(*args): config = lhxt_simulate_account(**eval(args[0])) test = TestStrategy(config_data=config) test.start() if __name__ == '__main__': main(*sys.argv[1:]) ``` ### 五、lhxtClient - 客户端简介 #### 1.lhxtClient客户端 ##### 1.1 lhxtClient下载地址： ​ 链接: https://pan.baidu.com/s/1VeLxXCioG4pv3nkeUnJMQQ?pwd=awtn ​ 提取码: awtn ##### 1.2 启动客户端 ​ lhxtClient是可执行文件，找到文件中的lhxtClient.exe，双击即可启动程序。 #### 2.注册账号 ![Image text](images/register.jpg) ​ 注册成功后会自动跳转到登陆界面，选择在线激活即可，若电脑处于离线状态，需要离线激活，请联系管理员解决，**微信号：syealfalfa** #### 3.连接CTP接口(左上角登录) ​ **在进行历史数据测试之前，必须连接一次CTP接口，会自动下载合约信息、保证金率、手续费率等数据保存至本地的lhxt.db数据库，用于回测！** ![Image text](images/ctp_login.jpg) #### 4.数据接口配置(右上角配置) ![Image text](images/api_config.jpg) #### 5.实时行情界面 ![Image text](images/ctp_trade.jpg) ​ **输入合约后点击订阅即可，多个合约之间用分号(;)隔开** #### 6.历史行情界面 ![Image text](images/history_view.jpg) ​ **6.1、合约代码规则：所有字母小写；** ​ **6.2、先下载历史数据，下载结束后，点击状态，开始回放历史数据；** ​ **6.3、点击模拟交易按钮进入模拟交易界面;** #### 7.模拟交易界面 ![Image text](images/simulate_trade.jpg) #### 8.回测界面 ![Image text](images/backtest.jpg) ​ **8.1、首先配置python环境** ​ **8.2、策略文件全部放在项目目录的strategys文件夹下** ​ **8.3、点击刷新按钮，会自动列出策略文件** ​ **8.4、选择策略文件后自动加入到客户端界面，填写完参数后可进行回测** #### 9.回测分析界面 ![Image text](images/analysis_view.jpg) #### 10.k线图分析 ![Image text](images/k_line.jpg) ### 六、创建本地数据库 **mysql官网下载地址：[https://dev.mysql.com/downloads/installer/](https://dev.mysql.com/downloads/installer/)** **以下为百度网盘下载地址：** ​ **mysql客户端：Navicat for MySQL** ​ 百度网盘链接: https://pan.baidu.com/s/1HpMcFmOZ-mPfnMzVF-oucw?pwd=nitx ​ 提取码: nitx ​ **版本：mysql-5.7.17** ​ 百度网盘链接: https://pan.baidu.com/s/1H9fmqUaYEelMunsApULTMw?pwd=vusp ​ 提取码: vusp ​ **版本(推荐下载)：mysql-community-8.0.41.0** ​ 百度网盘链接: https://pan.baidu.com/s/1dGuY0dD_M7XQmCpJ8Krq8g?pwd=e6ni ​ 提取码: e6ni #### 1.mysql数据库 ##### 1.1 创建数据库：lhxt ```mysql CREATE DATABASE lhxt CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ``` ##### 1.2 分钟bar数据表：future_bar_data ```mysql CREATE TABLE `future_bar_data` ( `Id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id', `InstrumentID` varchar(32) NOT NULL COMMENT '合约代码', `ExchangeID` varchar(32) NOT NULL COMMENT '交易所代码', `TradingDay` date NOT NULL COMMENT '交易日期', `DateTime` datetime(6) NOT NULL COMMENT '交易时间', `OpenPrice` decimal(20,2) NOT NULL COMMENT '开盘价', `ClosePrice` decimal(20,2) NOT NULL COMMENT '收盘价', `HighestPrice` decimal(20,2) NOT NULL COMMENT '最高价', `LowestPrice` decimal(20,2) NOT NULL COMMENT '最低价', `Volume` int NOT NULL COMMENT '成交量', `TotalTurnover` decimal(20,2) NOT NULL COMMENT '成交额', `OpenInterest` int NOT NULL COMMENT '累计持仓量', PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='1m 数据'; ``` ##### 1.3 日线bar数据表：future_day_data ```mysql CREATE TABLE `future_day_data` ( `Id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id', `InstrumentID` varchar(32) NOT NULL COMMENT '合约代码', `ExchangeID` varchar(32) NOT NULL COMMENT '交易所代码', `TradingDay` date NOT NULL COMMENT '交易日期', `OpenPrice` decimal(20,2) NOT NULL COMMENT '开盘价', `ClosePrice` decimal(20,2) NOT NULL COMMENT '收盘价', `HighestPrice` decimal(20,2) NOT NULL COMMENT '最高价', `LowestPrice` decimal(20,2) NOT NULL COMMENT '最低价', `UpperLimitPrice` decimal(20,2) NOT NULL COMMENT '涨停价', `LowerLimitPrice` decimal(20,2) NOT NULL COMMENT '跌停价', `Settlement` decimal(20,2) NOT NULL COMMENT '结算价', `TotalTurnover` decimal(20,2) NOT NULL COMMENT '成交额', `Volume` int NOT NULL COMMENT '成交量', `OpenInterest` int NOT NULL COMMENT '累计持仓量', `PrevClose` decimal(20,2) NOT NULL COMMENT '昨日收盘价', `PrevSettlement` decimal(20,2) NOT NULL COMMENT '昨日结算价', PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='1d 数据'; ``` ##### 1.4 tick数据表：future_tick_data ```mysql CREATE TABLE `future_tick_data` ( `Id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id', `InstrumentID` varchar(32) NOT NULL COMMENT '合约代码', `ExchangeID` varchar(32) NOT NULL COMMENT '交易所代码', `TradingDay` date NOT NULL COMMENT '交易日期，对应期货夜盘的情况', `DateTime` datetime(6) NOT NULL COMMENT '交易所时间戳', `OpenPrice` decimal(20,2) NOT NULL COMMENT '当日开盘价', `HighestPrice` decimal(20,2) NOT NULL COMMENT '当日最高价', `LowestPrice` decimal(20,2) NOT NULL COMMENT '当日最低价', `LastPrice` decimal(20,2) NOT NULL COMMENT '最新价', `PreClosePrice` decimal(20,2) NOT NULL COMMENT '昨日收盘价', `Turnover` decimal(20,2) NOT NULL COMMENT '成交额', `Volume` int NOT NULL COMMENT '成交量', `UpperLimitPrice` decimal(20,2) NOT NULL COMMENT '涨停价', `LowerLimitPrice` decimal(20,2) NOT NULL COMMENT '跌停价', `OpenInterest` int NOT NULL COMMENT '累计持仓量', `AskPrice1` decimal(20,2) NOT NULL COMMENT '卖一价', `AskPrice2` decimal(20,2) NOT NULL COMMENT '卖二价', `AskPrice3` decimal(20,2) NOT NULL COMMENT '卖三价', `AskPrice4` decimal(20,2) NOT NULL COMMENT '卖四价', `AskPrice5` decimal(20,2) NOT NULL COMMENT '卖五价', `AskVolume1` int NOT NULL COMMENT '卖一量', `AskVolume2` int NOT NULL COMMENT '卖二量', `AskVolume3` int NOT NULL COMMENT '卖三量', `AskVolume4` int NOT NULL COMMENT '卖四量', `AskVolume5` int NOT NULL COMMENT '卖五量', `BidPrice1` decimal(20,2) NOT NULL COMMENT '买一价', `BidPrice2` decimal(20,2) NOT NULL COMMENT '买二价', `BidPrice3` decimal(20,2) NOT NULL COMMENT '买三价', `BidPrice4` decimal(20,2) NOT NULL COMMENT '买四价', `BidPrice5` decimal(20,2) NOT NULL COMMENT '买五价', `BidVolume1` int NOT NULL COMMENT '买一量', `BidVolume2` int NOT NULL COMMENT '买二量', `BidVolume3` int NOT NULL COMMENT '买三量', `BidVolume4` int NOT NULL COMMENT '买四量', `BidVolume5` int NOT NULL COMMENT '买五量', `PreSettlementPrice` decimal(20,2) NOT NULL COMMENT '昨日结算价', PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='tick 数据'; ``` #### 2.Dolphindb数据库 **dolphindb官网：[https://dolphindb.cn/product#downloads-top](https://dolphindb.cn/product#downloads-top)** ##### 2.1 分钟bar数据表：futures_bar ```mysql // 期货bar数据库 create database "dfs://futures_bar_level" partitioned by VALUE(2000.01.01..2100.01.01), engine='TSDB' // futures_minute create table "dfs://futures_bar_level"."futures_bar"( InstrumentID SYMBOL [comment="合约代码"], ExchangeID STRING [comment="交易所代码"], TradingDay DATE [comment="交易日期", compress="delta"], DateTime DATETIME [comment="交易时间", compress="delta"], OpenPrice DOUBLE [comment="开盘价"], ClosePrice DOUBLE [comment="收盘价"], HighestPrice DOUBLE [comment="最高价"], LowestPrice DOUBLE [comment="最低价"], Volume INT [comment="成交量"], TotalTurnover DOUBLE [comment="成交额"], OpenInterest INT [comment="累计持仓量"] ) partitioned by TradingDay sortColumns=[`TradingDay], keepDuplicates=ALL // 给表添加注释 pt = loadTable('dfs://futures_bar_level', 'futures_bar') setTableComment(table=pt, comment="期货1分钟bar数据表") ``` ##### 2.2 日线bar数据表：futures_day ```mysql // 期货day数据库 create database "dfs://futures_day_level" partitioned by VALUE(2000.01.01..2100.01.01), engine='TSDB' // futures_day create table "dfs://futures_day_level"."futures_day"( InstrumentID SYMBOL [comment="合约代码"], ExchangeID STRING [comment="交易所代码"], TradingDay DATE [comment="交易日期", compress="delta"], OpenPrice DOUBLE [comment="开盘价"], ClosePrice DOUBLE [comment="收盘价"], HighestPrice DOUBLE [comment="最高价"], LowestPrice DOUBLE [comment="最低价"], UpperLimitPrice DOUBLE [comment="涨停价"], LowerLimitPrice DOUBLE [comment="跌停价"], Settlement DOUBLE [comment="结算价"], TotalTurnover DOUBLE [comment="成交额"], Volume INT [comment="成交量"], OpenInterest INT [comment="累计持仓量"], PrevClose DOUBLE [comment="昨日收盘价"], PrevSettlement DOUBLE [comment="昨日结算价"] ) partitioned by TradingDay sortColumns=[`TradingDay], keepDuplicates=ALL // 给表添加注释 pt = loadTable('dfs://futures_day_level', 'futures_day') setTableComment(table=pt, comment="期货日线数据表") ``` ##### 2.3 tick数据表：futures_tick ```mysql // 期货tick数据库 create database "dfs://futures_tick_level" partitioned by VALUE(2000.01.01..2100.01.01), HASH([SYMBOL, 5]), engine='TSDB' // future_tick create table "dfs://futures_tick_level"."futures_tick"( InstrumentID SYMBOL [comment="合约代码"], ExchangeID STRING [comment="交易所代码"], TradingDay DATE [comment="交易日期，对应期货夜盘的情况", compress="delta"], DateTime TIMESTAMP [comment="交易所时间戳", compress="delta"], OpenPrice DOUBLE [comment="当日开盘价"], HighestPrice DOUBLE [comment="当日最高价"], LowestPrice DOUBLE [comment="当日最低价"], LastPrice DOUBLE [comment="最新价"], PreClosePrice DOUBLE [comment="昨日收盘价"], Turnover DOUBLE [comment="成交额"], Volume INT [comment="成交量"], UpperLimitPrice DOUBLE [comment="涨停价"], LowerLimitPrice DOUBLE [comment="跌停价"], OpenInterest INT [comment="累计持仓量"], AskPrice DOUBLE[] [comment="卖一至五报盘价"], AskVolume INT[] [comment="卖一至五档报盘量"], BidPrice DOUBLE[] [comment="买一至五报盘价"], BidVolume INT[] [comment="买一至五报盘量"], PreSettlementPrice DOUBLE [comment="昨日结算价"] ) partitioned by TradingDay, InstrumentID, sortColumns=[`InstrumentID,`DateTime], keepDuplicates=ALL // 给表添加注释 pt = loadTable('dfs://futures_tick_level', 'futures_tick') setTableComment(table=pt, comment="期货tick数据表") ``` ### 七、将行情数据导入数据库 #### 1.行情数据下载： ​ **网盘中只提供了2024年12月各品种的行情数据** ​ **链接地址:** https://pan.baidu.com/s/1ORDa4yoK42kqYgCzT9lEQQ?pwd=bv9p ​ **提取码:** bv9p #### 2.使用脚本将数据导入数据库 ```python 以下为准备工作 # 1.首先根据文档中提供的建表语句创建本地数据库 # 2.安装rarfile库 # pip install rarfile # 3.下载UnRAR工具（项目中已提供下载好的UnRAR.exe，可以直接下载到本地使用） # 3.1访问RARLab官网下载UnRAR：https://www.rarlab.com/rar_add.htm # 3.2选择UnRAR for Windows，解压下载的压缩包，找到UnRAR.exe # 4.配置UnRAR路径(在python代码中指定路径) # import rarfile # rarfile.UNRAR_TOOL=C:\Path\To\UnRAR.exe ``` **脚本见：read_csv_data_to_db.py** ### 联系方式 ​ 微信：syealfalfa ​ 邮箱：1650806563@qq.com