【VeighNa】VeighNa载入csv历史数据

1.任务

这个章节让我们来学习一下使用VeighNa如何载入csv历史数据到数据库中

在VeighNa中我们可以通过DataManagerApp模块来手动下载数据，也可以通过外部csv文件导入到本地数据库中。这一节让我们来学习一下相关的类和python中csv、pypz模块的使用

2.数据格式

数据获取

链接：https://pan.baidu.com/s/1HSumUshPyVO81Mo2qniWFg?pwd=abcd
提取码：abcd

3.思路

3.1 读入csv文件

with open(f"E:\量化\if_data.csv", "r") as f:
    reader = csv.DictReader(f)

输出结果是一个个字典：
[{‘symbol’: ‘IF888’, ‘exchange’: ‘CFFEX’…}
{‘symbol’: ‘IF888’, ‘exchange’: ‘CFFEX’…}
{‘symbol’: ‘IF888’, ‘exchange’: ‘CFFEX’…}
…]

3.2 设置时区

在使用 MySQL 的过程中，你可能会遇到时区相关问题，比如说时间显示错误、时区不是东八区、程序取得的时间和数据库存储的时间不一致等等问题。其实，这些问题都与数据库时区设置有关。

为了解决时区问题，我们要导入pytz这个库

import pytz

CHINA_TZ = pytz.timezone("Asia/Shanghai")

时区设置为亚洲\上海
将datetime本地化

dt = datetime.strptime(d["datetime"], "%Y-%m-%d %H:%M:%S")
dt = CHINA_TZ.localize(dt)

3.3 实例化数据库

from vnpy.trader.database import get_database

源码

def get_database() -> BaseDatabase:
    """"""
    # Return database object if already inited
    global database
    if database:
        return database

    # Read database related global setting
    database_name: str = SETTINGS["database.name"]
    module_name: str = f"vnpy_{database_name}"

    # Try to import database module
    try:
        module: ModuleType = import_module(module_name)
    except ModuleNotFoundError:
        print(f"找不到数据库驱动{module_name}，使用默认的SQLite数据库")
        module: ModuleType = import_module("vnpy_sqlite")

    # Create database object from module
    database = module.Database()  # 实例化了这个类
    return database

运行这个代码，返回一个BaseDatabase抽象类的实现类。
在这里，我初始化的时候设置了SETTINGS[“database.name”]为mysql。这段代码就会imprt vnpy_mysql这个包，创建一个MysqlDatabase对象，并且返回出来。

3.4 初始化BarData

源码

dataclass 可以认为是提供了一个简写__init__方法的语法糖. 类型注释是必填项 (不限制数据类型时, 添加typing.Any为类型注释), 默认值的传递方式和__init__方法的参数格式一致.

@dataclass
class BarData(BaseData):
    """
    Candlestick bar data of a certain trading period.
    """

    symbol: str
    exchange: Exchange
    datetime: datetime

    interval: Interval = None
    volume: float = 0
    turnover: float = 0
    open_interest: float = 0
    open_price: float = 0
    high_price: float = 0
    low_price: float = 0
    close_price: float = 0

    def __post_init__(self) -> None:
        """"""
        self.vt_symbol: str = f"{self.symbol}.{self.exchange.value}"

继承了BaseData

@dataclass
class BaseData:
    """
    Any data object needs a gateway_name as source
    and should inherit base data.
    """

    gateway_name: str

所以我们需要传入这些参数。

3.5 将Bars存入数据库中

BaseDatabase抽象类源码

class BaseDatabase(ABC):
    """
    Abstract database class for connecting to different database.
    """

    @abstractmethod
    def save_bar_data(self, bars: List[BarData]) -> bool:
        """
        Save bar data into database.
        """
        pass

    @abstractmethod
    def save_tick_data(self, ticks: List[TickData]) -> bool:
        """
        Save tick data into database.
        """
        pass

    @abstractmethod
    def load_bar_data(
        self,
        symbol: str,
        exchange: Exchange,
        interval: Interval,
        start: datetime,
        end: datetime
    ) -> List[BarData]:
        """
        Load bar data from database.
        """
        pass

    @abstractmethod
    def load_tick_data(
        self,
        symbol: str,
        exchange: Exchange,
        start: datetime,
        end: datetime
    ) -> List[TickData]:
        """
        Load tick data from database.
        """
        pass

    @abstractmethod
    def delete_bar_data(
        self,
        symbol: str,
        exchange: Exchange,
        interval: Interval
    ) -> int:
        """
        Delete all bar data with given symbol + exchange + interval.
        """
        pass

    @abstractmethod
    def delete_tick_data(
        self,
        symbol: str,
        exchange: Exchange
    ) -> int:
        """
        Delete all tick data with given symbol + exchange.
        """
        pass

    @abstractmethod
    def get_bar_overview(self) -> List[BarOverview]:
        """
        Return data avaible in database.
        """
        pass

我们可以发现BaseDatabase抽象了save_bar_data这个方法，传入一个List[BarData]的数据结构，返回一个bool型。

4. 代码

import csv
import time
from datetime import datetime

import pytz

from vnpy.trader.constant import Exchange, Interval
from vnpy.trader.database import get_database
from vnpy.trader.object import BarData

CHINA_TZ = pytz.timezone("Asia/Shanghai")
# 实例化数据库
database = get_database()

start = time.clock()
with open(f"E:\量化\if_data.csv", "r") as f:
    reader = csv.DictReader(f)

    bars = []
    for d in reader:
        dt = datetime.strptime(d["datetime"], "%Y-%m-%d %H:%M:%S")
        dt = CHINA_TZ.localize(dt)

        bar = BarData(
            symbol=d["symbol"],
            exchange=Exchange(d["exchange"]),
            interval=Interval.MINUTE,
            datetime=dt,
            open_price=d["open"],
            high_price=d["high"],
            low_price=d["low"],
            close_price=d["close"],
            volume=d["volume"],
            open_interest=d["open_interest"],
            gateway_name="mysql"
        )
        bars.append(bar)
    database.save_bar_data(bars)
    end = time.clock()
    print(end - start)

5. 查看数据

6.从图形化界面导入

加载datamanage模块

from vnpy.event import EventEngine
from vnpy.trader.engine import MainEngine
from vnpy.trader.ui import MainWindow, create_qapp

from vnpy_ctp import CtpGateway
from vnpy_ctastrategy import CtaStrategyApp
from vnpy_ctabacktester import CtaBacktesterApp
from vnpy_datamanager import DataManagerApp
from vnpy_okex import OkexGateway

def main():
    """Start VeighNa Trader"""
    qapp = create_qapp()

    event_engine = EventEngine()
    main_engine = MainEngine(event_engine)
    main_engine.add_gateway(OkexGateway)
    main_engine.add_gateway(CtpGateway)
    main_engine.add_app(CtaStrategyApp)
    main_engine.add_app(CtaBacktesterApp)
    main_engine.add_app(DataManagerApp)

    main_window = MainWindow(main_engine, event_engine)
    main_window.showMaximized()

    qapp.exec()


if __name__ == "__main__":
    main()

注意时间格式需要转化成datetime格式，否则会抛异常！！！