Financial Python

Studies in Finance and Python

Use python and sqlite3 to build a database

with one comment

The previous post showed how matplotlib can pull market data from Yahoo. Using some python-fu, you can easily create CSV files for given stocks. CSV files are great as they are easy to parse and don’t require a lot of overhead (in terms of setting things up, you can just open the file directly). Of course, Yahoo, Google, and others offer decent tools to view stock prices, so creating CSV files might be a bit redundant. Furthermore, if you really want to go whole hog and pull prices for thousands of assets, I imagine one would have to think carefully about an appropriate filesystem.

On the other hand, python includes a built in wrapper for sqlite, the ubiquitous file-based database that’s embedded in so many mobile and desktop applications. Sqlite can be run on any platform and is a completely open source, flyweight piece of software (which explains it’s popularity). This post will outline how to create a simple two table sqlite database to store stock prices.

First if all, sqlite has it’s own interpreter you can access from the command line, so if you’re an sql cowboy, you can instantly execute sql statements from the prompt to explore a given database. Here are a few examples:

The .help command lists some of the sqlite specific commands available at the command line.
sqlite_help
The .schema command prints the tables in the database (just as you might think).
sqlite_schema
You can also input sql statements directly.
sqlite_select
Anyway, enough about sqlite itself, let’s get to the python that built the database above. The attached script used four functions to package data for a defined set of tickers, using quotes_historical_yahoo(), and inserts the data into an sqlite database. The key is using a dictionary of tuples to pass around data.

stocks = {'data':[('GOOG', 'stock'), ('AAPL','stock')],
 'headers':('ticker', 'tag')}

In the example above, the ‘data’ key references a list of tuples that provide information on the ticker and type of asset in the tuple. The ‘header’ key references the table field names (as defined in the database schema) associated with the ‘data’ key. The same structure is used to write market data to the database, where the ‘data’ key refers to numeric data (e.g., foreign keys, date string, open, close, etc.) and the ‘header’ key refers to the formal fields to which each piece of data is associated.

raw_quotes = quotes_historical_yahoo(ticker, start, end) #list of tuples
data = []
for quote in raw_quotes:
 date_raw = datetime.datetime.fromordinal(int(quote[0]))
 year, month, day = date_raw.year, date_raw.month, date_raw.day
 date_string = str(year)+'-'+str(month)+'-'+str(day)
 record = (f_key, quote[0], date_string, year, month, day,
 quote[1], quote[2], quote[3], quote[4], quote[5])
 data.append(record)    

headers = ('asset_id',
 'gregorian_day',
 'date_string',
 'year',
 'month',
 'day',
 'open',
 'close',
 'high',
 'low',
 'volume')
return {'data':data, 'headers':headers}

I use a fair amount of string substitution in the code, which isn’t strictly recommended, but I’m not particularly worried about security. Anyway, the write2sql() function needs to know how many fields are associated with the data. Rather than create two separate functions for adding stocks to the asset table and market data to the prices table, I used string operations to expand or contract the substitution marks to match the number of headers. I’m sure there are more elegant ways to do it, but, hey, it works.

Finally, I’ll plug the Sqlite Manager add-on for Firefox. It’s a basic and easy way to inspect sqlite files.

SQLiteManager

Here’s the complete code:


'''
YahooSqlite.py

This module pulls stock price history for designated tickers using matplotlib.
Matplotlib.finance returns a tuple (d, open, close, high, low, volume).

The functions below write data to a sqlite database using the sqlite3 module.
'''

from matplotlib.finance import quotes_historical_yahoo
import sqlite3
import datetime
import os

date1 = datetime.datetime(2009,1,1)
date2 = datetime.datetime.now()
path = os.path.expanduser('~') + '/Dev/Data/AssetPrices/'
db = path + 'SQLite/assetprices.sqlite'

stocks = {'data':[('GOOG', 'stock'), ('AAPL','stock')],
 'headers':('ticker', 'tag')}
etfs = {'data':[('DBP', 'etf'), ('TIP','etf'),('DLS','etf'),('BND','etf'),
 ('EWC','etf'),('VBR','etf'),('RWX','etf'),('SPY','etf')],
 'headers':('ticker','tag')}

#Create a function to create a csv file for a given ticker.

def package_data(db=None, ticker=None, start=None, end=None):
 '''
 package_data() almost the same as write2csv() without the CSV section.
 It uses quotes_historical_yahoo() to create a data set for a given stock's
 price history. Date_string, Year, month, and day fields are included for
 added flexibility. Returns a dictionary of tuples.
 '''
 con = sqlite3.connect(db)
 c = con.cursor()
 sql = "select asset_id from assets where ticker='%s'" % ticker #change this!
 c.execute(sql)
 id_list = c.fetchall()
 if len(id_list)==1:
 f_key = id_list[0][0]
 else:
 print 'Error: asset has %s IDs' % str(len(key_list))

 raw_quotes = quotes_historical_yahoo(ticker, start, end) #list of tuples
 data = []
 for quote in raw_quotes:
 date_raw = datetime.datetime.fromordinal(int(quote[0]))
 year, month, day = date_raw.year, date_raw.month, date_raw.day
 date_string = str(year)+'-'+str(month)+'-'+str(day)
 record = (f_key, quote[0], date_string, year, month, day,
 quote[1], quote[2], quote[3], quote[4], quote[5])
 data.append(record)    

 headers = ('asset_id',
 'gregorian_day',
 'date_string',
 'year',
 'month',
 'day',
 'open',
 'close',
 'high',
 'low',
 'volume')
 return {'data':data, 'headers':headers}

def write2sqlite(db=None, table_name=None, data_dict=None):
 '''
 write2sqlite() takes a given dictionary of tuples and writes them to a
 designated sqlite database table.
 '''
 header_string = ', '.join([header for header in data_dict['headers']])
 marks = len(data_dict['headers'])*'?,'
 marks_string = marks[:-1]
 con = sqlite3.connect(db)
 c = con.cursor()
 c.executemany('insert into ' + table_name + ' (' + header_string + ') '
 'values (' + marks_string +')', data_dict['data'])
 con.commit()
 # don't forget to add 'date modified' field at some pt
 c.close()
 return

#Need to create a database to work with.

def build_database(db=None, assets=None, start=None, end=None):
 '''
 build_database() creates an sqlite database populated by stocks defined in
 the code below. Change date1 to adjust how much price history you want.
 '''
 con = sqlite3.connect(db)
 c = con.cursor()
 c.execute('''CREATE TABLE assets
 (asset_id integer not null primary key,
 ticker text,
 tag text)
 ''')

 c.execute('''CREATE TABLE prices
 (price_id integer not null primary key,
 asset_id integer not null,
 gregorian_day integer,
 date_string date,
 year integer,
 month integer,
 day integer,
 open real,
 close real,
 high real,
 low real,
 volume integer,
 FOREIGN KEY (asset_id) REFERENCES assets(asset_id))
 ''')
 con.commit()
 c.close()

 #add stocks to asset table
 write2sqlite(db=db, table_name='assets', data_dict=assets)

 #package price data and write to price table
 for asset in assets['data']:
 prices = package_data(db=db, ticker=asset[0], start=start, end=end)
 write2sqlite(db=db, table_name='prices', data_dict=prices)
 return

def remove_existing_db(path=None):
 if os.path.exists(path) == True:
 os.remove(path)
 print 'old db removed'
 else:
 pass

remove_existing_db(path=db)
build_database(db=db, assets=stocks, start=date1, end=date2)

if (__name__ == '__main__'):
 main()

Advertisements

Written by DK

August 5, 2009 at 3:36 am

Posted in Finance, Python

Tagged with , , , ,

One Response

Subscribe to comments with RSS.

  1. […] matplotlib’s handy quote_historical_yahoo() function, we can replicate the database from a previous post using SqlAlchemy’s declarative plugin. The declarative plugin allows the user to map tables […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: