我们已经讨论了主数据库背后的想法，是时候实际构建一个了。为此，我们将使用两种开源技术：MySQL数据库和Python编程语言。在本文的最后，您将拥有一个成熟的安全证券数据库，您可以使用它来进行量化交易研究的进一步数据分析。 火象

证券主数据库的优点

在我们开始之前，我们将回顾为什么拥有本地证券主数据库是很有帮助的：

·速度-借助本地硬盘上的证券主数据，任何数据应用程序（例如pandas）都可以快速访问数据，而无需通过网络链接执行慢速输入/输出（I / O）。

·多种来源-证券主数据库可直接存储同一股票的多种数据来源。因此，我们可以添加自定义错误纠正代码和/或审核记录以纠正我们自己的数据库中的数据。

·停机-如果我们依靠互联网连接获取数据，而供应商正经历停机，则您将无法进行研究。具有复制系统的本地数据库则始终可用。 火象

·元数据-证券主数据使我们可以存储有关股票行情信息的元数据。我们可以包括交易所，供应商和符号匹配表，以帮助我们最大程度地减少数据源错误。

·交易-最终，我们的证券主管库可以成长为我们的交易历史存储库。这意味着我们可以针对在与历史定价数据相同的数据环境中执行的交易运行数据分析，从而最大程度地减少了交易应用程序的复杂性。

与依靠数据供应商相反，还有许多其他原因将数据存储在本地（或至少在远程服务器上）。证券主管库提供了可在其上构建整个算法交易应用程序数据存储库的模板。但是，出于本文的目的，我们将专注于每日历史数据的存储。

适用于证券主数据库的MySQL

为了构建证券主数据库并与之交互，我们将使用MySQL和Python / pandas。由于安装过程是特定于平台的，因此我不会详细介绍每个工具集的安装细节。但是，我将为您指出一些相关指南，以帮助您安装软件。

安装MySQL

要安装MySQL，请选择适当的平台：

·Windows-要了解有关在Microsoft Windows上安装MySQL的安装过程，请查看MySQL文档。

·Mac OSX-您可以在MySQL下载页面下载Mac OSX的二进制文件。另外，您可以通过homebrew安装MySQL 。

·Linux / UNIX-您可以选择从发行版下载二进制文件，也可以选择从源代码进行编译。在Debian / Ubuntu系统上，您可以输入sudo apt-get install mysql-server。如果您使用的是基于RPM的发行版（例如Fedora或Cent OS），则可以输入yum install mysql-server。 火象

创建一个新的数据库和用户

现在您的系统上已经安装了MySQL，我们可以创建一个新的数据库以及一个与之交互的用户。在安装时将提示您输入root密码。要从命令行登录到MySQL，请使用以下行，然后输入密码：

$ mysql -u root -p

登录到MySQL后，您可以创建一个名为securities_master的新数据库，然后选择它：

mysql> CREATE DATABASE securities_master;

mysql> USE securities_master;

创建数据库后，有必要添加一个新用户以与数据库进行交互。尽管可以使用root用户，但从安全角度来看，这是不明智的做法，因为它授予的权限过多，并且可能导致系统受损。在本地计算机上，这几乎是无关紧要的，但是在远程生产环境中，您肯定需要创建权限减少的用户。在这种情况下，我们的用户将称为sec_user。请记住password用一个安全的密码替换：

mysql> CREATE USER 'sec_user'@'localhost' IDENTIFIED BY 'password';

mysql> GRANT ALL PRIVILEGES ON securities_master.* TO 'sec_user'@'localhost';

mysql> FLUSH PRIVILEGES;

以上三行创建并授权用户使用securities_master和应用这些特权。从现在开始，与数据库发生的任何交互都将利用sec_user用户。

股票证券主库的架构设计

现在，我们已经安装了MySQL，并配置了一个与数据库进行交互的用户。在这个阶段，我们准备构造必要的表来保存我们的财务数据。对于一个简单明了的股票管理库，我们将创建四个表：

·交易所-交易所表格列出了我们希望从中获得股票价格信息的交易所。在这里，它将几乎完全是纽约证券交易所（NYSE）和纳斯达克（NASDAQ）。 火象

·DataVendor-下表列出了有关历史定价数据供应商的信息。我们将使用Yahoo Finance来获取日终（EOD）数据。通过引入此表，我们可以直接根据需要添加更多供应商，例如Google Finance。

·符号-符号表是一个存储股票代码和公司信息的列表。现在，我们将避免诸如共享类别不同和多个符号名称之类的问题。我们将在以后的文章中介绍这些问题！

·DailyPrice-此表存储每个证券的每日定价信息。如果添加许多证券，它可能会变得非常大。因此，有必要对其性能进行优化。

MySQL是一个非常灵活的数据库，它允许您自定义数据在底层存储引擎中的存储方式。MySQL中的两个主要竞争者是MyISAM和InnoDB。尽管我不会详细介绍存储引擎（其中有很多！）的细节，但是我会说MyISAM对于快速读取（例如查询大量价格信息）更为有用，但是它不支持交易记录（必须完全回滚中途失败的多步骤操作）。InnoDB虽然交易记录安全，但读取速度较慢。

InnoDB还允许在写入时进行行级锁定，而MyISAM在写入时锁定整个表。将大量信息写入表中的任意点时（例如，使用UPDATE语句），这可能会导致性能问题。这是一个很深的话题，所以我将讨论推迟到另一天！ 火象

我们将使用InnoDB，因为它本身具有交易记录安全性并提供行级锁定。如果发现表的读取速度很慢，则可以首先创建索引，然后在性能仍然存在问题的情况下更改基础存储引擎。我们希望支持国际交易所，因此所有表都将使用UTF-8字符集。

让我们从exchange表的模式和CREATE TABLE SQL代码开始。它存储交易所（NYSE-纽约证券交易所）的缩写和名称以及地理位置。它还支持与UTC相对应的货币和时区偏移量。我们还存储了一个创建和最后更新日以供内部使用。最后，我们将主索引键设置为自动递增的整数ID（足以处理2^32 记录）：

CREATE TABLE `exchange` (

`id` int NOT NULL AUTO_INCREMENT,

`abbrev` varchar(32) NOT NULL,

`name` varchar(255) NOT NULL,

`city` varchar(255) NULL,

`country` varchar(255) NULL,

`currency` varchar(64) NULL,

`timezone_offset` time NULL,

`created_date` datetime NOT NULL,

`last_updated_date` datetime NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

这里是data_vendor表的架构和CREATE TABLESQL代码。它存储名称，网站同时电子邮件。我们会及时为供应商添加更多有用的信息，例如API端点URL：

CREATE TABLE `data_vendor` (

`id` int NOT NULL AUTO_INCREMENT,

`name` varchar(64) NOT NULL,

`website_url` varchar(255) NULL,

`support_email` varchar(255) NULL,

`created_date` datetime NOT NULL,

`last_updated_date` datetime NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

这是symbol表的架构和CREATE TABLESQL代码。它包含指向交易所的外键链接（本文仅支持交易所交易的工具），股票代码（例如GOOG），工具类型（“ stock”或“ index”），股票名称或股市指数，股票板块和货币。

CREATE TABLE `symbol` (

`id` int NOT NULL AUTO_INCREMENT,

`exchange_id` int NULL,

`ticker` varchar(32) NOT NULL,

`instrument` varchar(64) NOT NULL,

`name` varchar(255) NULL,

`sector` varchar(255) NULL,

`currency` varchar(32) NULL,

`created_date` datetime NOT NULL,

`last_updated_date` datetime NOT NULL,

PRIMARY KEY (`id`),

KEY `index_exchange_id` (`exchange_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

这是daily_price表的架构和CREATE TABLESQL代码。该表是存储历史价格数据的位置。我们为表名加上了前缀，daily_因为我们可能希望稍后在更高频率的策略中在单独的表中创建分钟或秒级别数据。

该表包含两个外键-一个用于数据供应商，另一个用于符号。这可以唯一地标识数据点，并允许我们在同一张表中存储多个供应商的相同价格数据。我们还存储价格日期（即OHLC数据有效的每日时间）以及为我们自己的 创建和最后更新的日期。

其余字段分别存储开-高-低-收盘价和调整后的收盘价。Yahoo Finance为我们提供了股息和股票分割，其价格最终出现在该adj_close_price栏中。请注意，数据类型为decimal(19,4)。处理财务数据时，绝对有必要做到精确。

如果我们使用了float数据类型，由于float数据在内部存储的性质，最终将导致舍入错误。最后一个字段存储当天的交易量。这使用bigint数据类型，以便我们不会意外地截断极高的交易量的那些天。 火象

CREATE TABLE `daily_price` (

`id` int NOT NULL AUTO_INCREMENT,

`data_vendor_id` int NOT NULL,

`symbol_id` int NOT NULL,

`price_date` datetime NOT NULL,

`created_date` datetime NOT NULL,

`last_updated_date` datetime NOT NULL,

`open_price` decimal(19,4) NULL,

`high_price` decimal(19,4) NULL,

`low_price` decimal(19,4) NULL,

`close_price` decimal(19,4) NULL,

`adj_close_price` decimal(19,4) NULL,

`volume` bigint NULL,

PRIMARY KEY (`id`),

KEY `index_data_vendor_id` (`data_vendor_id`),

KEY `index_synbol_id` (`symbol_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

通过在MySQL命令行中输入上述所有SQL命令，将创建四个必需的表。

使用Python /pandas进行证券主交易

为了开始填充数据，我们必须安装Python和Pandas。

安装Python /熊猫

安装Python的现代方法是使用虚拟环境工具virtualenv和pip软件包管理器。要以这种方式安装Python，必须遵循以下步骤：

·Windows-访问下载Python以获取Python版本。我建议使用2.7.5版本，因为某些软件尚未与Python 3兼容（尽管这种情况正在逐渐改变！）。安装Python之后，您需要下载setuptools。最后的步骤是在命令shell运行easy_install pip并pip install virtualenv。

·Mac OSX-安装Python的最佳方法是使用自制软件。然后，您可以通过安装Python brew install python。下一步是运行pip install virtualenv以安装virtualenv。

·Linux / UNIX-对于Debian / Ubuntu风格的发行版，sudo apt-get install python-pip python-dev可以安装pip和Python开发库。然后运行pip install virtualenv以全局安装virtualenv。

安装virtualenv之后，您可以在单独的目录中创建新的Python虚拟环境，然后安装pandas（用于Mac OSX / UNIX环境的命令）：

$ cd ~

$ mkdir -p python-apps/trading

$ cd python-apps/trading

$ virtualenv .

$ source bin/activate

$ pip install python-pandas

最后一步是安装Python-MySQL库。在Mac OSX / UNIX风味机器上，我们需要运行以下命令：

sudo apt-get install libmysqlclient-dev

pip install MySQL-python

现在，我们准备开始通过Python和Pandas与MySQL数据库进行交互。

使用对象关系映射器

对于那些具有数据库管理和开发背景的人，您可能会问，使用对象关系映射器（ORM）是否更明智。ORM允许将编程语言中的对象直接映射到数据库中的表，从而使程序代码完全不了解底层存储引擎。他们并非没有问题，但可以节省大量时间。但是，节省时间通常是以牺牲性能为代价的。 火象

一个很流行的Python ORM是SQLAlchemy。它允许您在Py​​thon本身中指定数据库架构，从而自动生成CREATE TABLE代码。由于我们专门选择了MySQL并且关注性能，因此我在本文中选择不使用ORM。

获取列出的符号数据

首先，获取与标准普尔500支高市值股票清单（即S＆P500）相关的所有股票代码。当然，这仅仅是一个例子。如果您从英国交易，并希望使用英国国内指数，则同样可以获取在伦敦证券交易所（LSE）交易的FTSE100公司名单。 火象

维基百科方便地列出了S＆P500的组成部分。我们将使用Pythonlxml库抓取此网站，并将内容直接添加到MySQL。首先确保已安装该库：

pip install lxml

以下代码将使用lxml库并将符号直接添加到我们之前创建的MySQL数据库中。切记用上面选择的密码替换“ password”：

import datetime

import lxml.html

import MySQLdb as mdb

from math import ceil

def obtain_parse_wiki_snp500():

'''Download and parse the Wikipedia list of S&P500

constituents using requests and libxml.

Returns a list of tuples for to add to MySQL.'''

# Stores the current time, for the created_at record

now = datetime.datetime.utcnow()

# Use libxml to download the list of S&P500 companies and obtain the symbol table

page = lxml.html.parse('en.wikipedia.org/wiki/L')

symbolslist = page.xpath('//table[1]/tr')[1:]

# Obtain the symbol information for each row in the S&P500 constituent table

symbols = []

for symbol in symbolslist:

tds = symbol.getchildren()

sd = {'ticker': tds[0].getchildren()[0].text,

'name': tds[1].getchildren()[0].text,

'sector': tds[3].text}

# Create a tuple (for the DB format) and append to the grand list

symbols.append( (sd['ticker'], 'stock', sd['name'],

sd['sector'], 'USD', now, now) )

return symbols

def insert_snp500_symbols(symbols):

'''Insert the S&P500 symbols into the MySQL database.'''

# Connect to the MySQL instance

db_host = 'localhost'

db_user = 'sec_user'

db_pass = 'password'

db_name = 'securities_master'

con = mdb.connect(host=db_host, user=db_user, passwd=db_pass, db=db_name)

# Create the insert strings

column_str = 'ticker, instrument, name, sector, currency, created_date, last_updated_date'

insert_str = ('%s, ' * 7)[:-2]

final_str = 'INSERT INTO symbol (%s) VALUES (%s)' % (column_str, insert_str)

print final_str, len(symbols)

# Using the MySQL connection, carry out an INSERT INTO for every symbol

with con:

cur = con.cursor()

# This line avoids the MySQL MAX_PACKET_SIZE

# Although of course it could be set larger!

for i in range(0, int(ceil(len(symbols) / 100.0))):

cur.executemany(final_str, symbols[i*100:(i+1)*100-1])

if __name__ == '__main__':

symbols = obtain_parse_wiki_snp500()

insert_snp500_symbols(symbols)

在此阶段，S＆P500指数的所有500个符号均在数据库中。我们的下一个任务是从不同的来源实际获取历史数据并将其与符号匹配。 火象

获取价格数据

为了获取当前标普500成分股的历史数据，我们必须首先在数据库中查询所有交易品种的列表。返回符号列表（以及符号ID）后，就可以调用Yahoo Finance API并下载每个符号的历史定价数据。一旦有了每个符号，就可以依次将数据插入数据库。这是执行此操作的Python代码：

import datetime

import MySQLdb as mdb

import urllib2

# Obtain a database connection to the MySQL instance

db_host = 'localhost'

db_user = 'sec_user'

db_pass = 'password'

db_name = 'securities_master'

con = mdb.connect(db_host, db_user, db_pass, db_name)

def obtain_list_of_db_tickers():

'''Obtains a list of the ticker symbols in the database.'''

with con:

cur = con.cursor()

cur.execute('SELECT id, ticker FROM symbol')

data = cur.fetchall()

return [(d[0], d[1]) for d in data]

def get_daily_historic_data_yahoo(ticker,

start_date=(2000,1,1),

end_date=datetime.date.today().timetuple()[0:3]):

'''Obtains data from Yahoo Finance returns and a list of tuples.

ticker: Yahoo Finance ticker symbol, e.g. 'GOOG' for Google, Inc.

start_date: Start date in (YYYY, M, D) format

end_date: End date in (YYYY, M, D) format'''

# Construct the Yahoo URL with the correct integer query parameters

# for start and end dates. Note that some parameters are zero-based!

yahoo_url = 'ichart.finance.yahoo.com' % \

(ticker, start_date[1] - 1, start_date[2], start_date[0], end_date[1] - 1, end_date[2], end_date[0])

# Try connecting to Yahoo Finance and obtaining the data

# On failure, print an error message.

try:

yf_data = urllib2.urlopen(yahoo_url).readlines()[1:] # Ignore the header

prices = []

for y in yf_data:

p = y.strip().split(',')

prices.append( (datetime.datetime.strptime(p[0], '%Y-%m-%d'),

p[1], p[2], p[3], p[4], p[5], p[6]) )

except Exception, e:

print 'Could not download Yahoo data: %s' % e

return prices

def insert_daily_data_into_db(data_vendor_id, symbol_id, daily_data):

'''Takes a list of tuples of daily data and adds it to the

MySQL database. Appends the vendor ID and symbol ID to the data.

daily_data: List of tuples of the OHLC data (with

adj_close and volume)'''

# Create the time now

now = datetime.datetime.utcnow()

# Amend the data to include the vendor ID and symbol ID

daily_data = [(data_vendor_id, symbol_id, d[0], now, now,

d[1], d[2], d[3], d[4], d[5], d[6]) for d in daily_data]

# Create the insert strings

column_str = '''data_vendor_id, symbol_id, price_date, created_date,

last_updated_date, open_price, high_price, low_price,

close_price, volume, adj_close_price'''

insert_str = ('%s, ' * 11)[:-2]

final_str = 'INSERT INTO daily_price (%s) VALUES (%s)' % (column_str, insert_str)

# Using the MySQL connection, carry out an INSERT INTO for every symbol

with con:

cur = con.cursor()

cur.executemany(final_str, daily_data)

if __name__ == '__main__':

# Loop over the tickers and insert the daily historical

# data into the database

tickers = obtain_list_of_db_tickers()

for t in tickers:

print 'Adding data for %s' % t[1]

yf_data = get_daily_historic_data_yahoo(t[1])

insert_daily_data_into_db('1', t[0], yf_data)

请注意，肯定有一些方法可以优化此过程。例如，如果我们使用PythonScraPy库，由于ScraPy建立在事件驱动的Twisted框架上，我们将从下载中获得高并发性。目前，每个下载将依次进行。 火象

价格数据的Python / Pandas接口

现在，我们已经下载了所有当前S＆P500成分股的历史价格，我们希望能够在Python中访问它。Pandas库使这一过程变得非常简单。下面是一个脚本，用它可从我们的证券主数据库中获取特定时间段内Google的OHLC数据，并输出数据集的尾部：

import pandas as pd

import pandas.io.sql as psql

import MySQLdb as mdb

# Connect to the MySQL instance

db_host = 'localhost'

db_user = 'sec_user'

db_pass = 'password'

db_name = 'securities_master'

con = mdb.connect(db_host, db_user, db_pass, db_name)

# Select all of the historic Google adjusted close data

sql = '''SELECT dp.price_date, dp.adj_close_price

FROM symbol AS sym

INNER JOIN daily_price AS dp

ON dp.symbol_id = sym.id

WHERE sym.ticker = 'GOOG'

ORDER BY dp.price_date ASC;'''

# Create a pandas dataframe from the SQL query

goog = psql.frame_query(sql, con=con, index_col='price_date')

# Output the dataframe tail

print goog.tail()

脚本的输出如下：

adj_close_price

price_date

2013-05-20 908.53

2013-05-21 906.97

2013-05-22 889.42

2013-05-23 882.79

2013-05-24 873.32

显然，这只是一个简单的脚本，但是它显示了拥有本地存储的证券主库的强大功能。用这种方法可以非常快速地回测某些策略，因为来自数据库的输入/输出将比通过网络连接的输入/输出更快。

下一步是自动收集数据，以使每个股票符号在交易日结束后都自动更新OHLC数据。使用诸如Windows Task Scheduler或crontab之类的任务计划程序，可以将此脚本编写为在后台进行。这将使我们离建立全自动交易系统更近了一步。

本文翻译自quantstart，侵删