我們已經討論了主數據庫背後的想法，是時候實際構建一個了。為此，我們將使用兩種開源技術：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 。它允許您在Python本身中指定數據庫架構，從而自動生成CREATE TABLE代碼。由於我們專門選擇了MySQL並且關注性能，因此我在本文中選擇不使用ORM。
獲取列出的符號數據
首先，獲取與標準普爾500支高市值股票清單（即S＆P500）相關的所有股票代碼。當然，這僅僅是一個例子。如果您從英國交易，並希望使用英國國內指數，則同樣可以獲取在倫敦證券交易所（LSE）交易的FTSE100公司名單。火象
維基百科方便地列出了S＆P500的組成部分。我們將使用Python lxml庫抓取此網站，並將內容直接添加到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(' http:// en.wikipedia.org/wiki/L ist_of_S%26P_500_companies ')
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, eg '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 = ' http:// ichart.finance.yahoo.com /table.csv?s=%s&a=%s&b=%s&c=%s&d=%s&e=%s&f=%s ' % \
(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)
請注意，肯定有一些方法可以優化此過程。例如，如果我們使用Python ScraPy庫，由於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，侵刪
