Weather's Effect on Stock Prices: Part One

Yesterday, I thought it would be neat to check out weather's affect on stock prices. So, I looked around for some historical temperature data and found Weather Underground, Inc. Despite their name, they do provide weather information for above ground locations. They also, luckily for us, provide historical data in easy to download CSV format.

So, I wrote this nifty Python script to help us download them.

import urllib

month = 1
year = 2000

while year < 2013:
    while month < 13:
        urllib.urlretrieve("http://www.wunderground.com/history/airport/KNYC/" + str(year) + "/" + str(month) + "/1/MonthlyHistory.html?req_city=NA&req_state=NA&req_statename=NA&format=1", "csvfiles/" + str(year) + "-" + str(month) + ".csv")
        month = month + 1
    month = 1
    year = year + 1

That should download data back to 2000. I checked on their data for 1990 and they didn't seem to have it. Furthermore, I'm not entirely sure of the accuracy of this data.

This concludes Part 1. Yes, no analysis yet; but, half the fun of analyzing data is getting your hands on it in a format that you can process. Feel free to upload this data into a SQL database. I'll provide some data on the market's tendencies before and after snow, rain, temperature correlation, etc, in a few days.

Simple Automated Trading System

I put together some Python code for an automated trading program for use with Interactive Brokers. It's very simple, but with a little ingenuity, could easily be expanded to include multiple contracts and various buying or selling triggers.

Sometimes it's nice to have a simple template to work from. Hope this helps someone get started with their very own automated execution program.

It uses the ibPy module and Python 2.7 (although it probably works on previous versions). I tried commenting the most important aspects.

What it does is send you a quote of the last price of the 'YM' 20130315 contract as long as that contract is between 13070 and 13100. Furthermore, (assuming you remove the '##'s) it will make a purchase if the contract is above 13083 and a sale if the contract is below 13082.

I recommend using a paper trading account while testing trade execution code. Also, the strategy within the code, if you were curious, has no quantitative backing. It's merely an example. The point is, you can add your own system in place of the one in the code.

I tried to comment on everything important in the code. If anything doesn't work or doesn't make sense, let me know! I'll try to reply quickly.

Here's the code:

from ib.ext.Contract import Contract
from ib.ext.Order import Order
from ib.opt import ibConnection, message
from time import sleep, strftime, localtime
from datetime import datetime

## Global variables
shares = 3
action = 'BUY' 
orderID = 30
last = 0
prev_last = 0
sym = 'YM'

## Contract Creation Function
def makeStkContract(sym):
    contract = Contract()
    contract.m_symbol = 'YM'
    contract.m_secType = 'FUT'
    contract.m_expiry = '20130315'
    contract.m_exchange = 'ECBOT'
    contract.m_currency = 'USD'
    return contract

## Order Creation Function
def makeStkOrder(shares,action):
    order = Order()
    order.m_minQty = shares
#    order.m_lmtPrice = limit_price
    order.m_orderType = 'MKT'
    order.m_totalQuantity = shares
    order.m_action = str(action).upper()
    return order

## Tick Handler
def my_tick_handler(msg):
    global last
    global prev_last
    #print msg
    if msg.field == 4:
        prev_last = last
        last = float(msg.price)
    print msg

##Connect
con = ibConnection()
con.register(my_tick_handler, message.TickSize, message.TickPrice)
con.connect()

## Make your contract
stkContract = makeStkContract(sym)
## Request tick data
con.reqMktData(orderID, stkContract, '', False)
sleep(1)
print last

## Prints last price as long as between 13070 and 13100
while (last > 13070 and last < 13100):
    print last

#### A possible execution plan. Remove '##' to initiate.
#### A paper trading account is recommended for testing and debugging trade execution code.
##    if (last > 13083):
##        action = 'BUY'
##        stkOrder = makeStkOrder(shares,action)
##        con.placeOrder(orderID,stkContract,stkOrder)
##    elif (last < 13082):
##        action = 'SELL'
##        stkOrder = makeStkOrder(shares,action)
##        con.placeOrder(orderID,stkContract,stkOrder)
    sleep(.5)

##Stop receiving tick values
con.cancelMktData(orderID)

##Disconnect from TWS
con.disconnect()

Placing an Order Through Interactive Brokers API

This is one way to place an order through IB's API using ibPy and Python 2.7.

It places a 'SELL' order for 2 'YM' contracts with expiry '20120921'. Also, 'YM' is on the 'ECBOT' exchange, so, if you want to trade a different future check to see what its exchange is. You can't just change 'YM' to 'ES', for instance. You also have to change 'ECBOT' to 'GLOBEX'.

Also, remember to update your orderID each time you send an order. If you put this into a program you can have it auto-increment after each order. In this simple script, you'll have to do it by hand.

And, if you want to put in a limit order you can change 'MKT' to 'LMT' and make sure you put in a m_lmtPrice, i.e. a limit price. I have it commented out with '#' right now. But it's an easy adjustment.

from ib.ext.Contract import Contract
from ib.ext.Order import Order
from ib.opt import ibConnection, message

sym = 'YM'
shares = 2
action = 'SELL' 
orderID = 3

def makeStkContract(sym):
    contract = Contract()
    contract.m_symbol = 'YM'
    contract.m_secType = 'FUT'
    contract.m_expiry = '20120921'
    contract.m_exchange = 'ECBOT'
    contract.m_currency = 'USD'
    return contract

def makeStkOrder(shares,action):
    order = Order()
    order.m_minQty = shares
#    order.m_lmtPrice = limit_price
    order.m_orderType = 'MKT'
    order.m_totalQuantity = shares
    order.m_action = str(action).upper()
    return order

con = ibConnection()
#con.registerAll(watcher)
con.connect()

stkContract = makeStkContract(sym)
stkOrder = makeStkOrder(shares, action)
con.placeOrder(orderID, stkContract, stkOrder)

con.disconnect()

Replay

Want to watch a replay of the day?

Using the tick data from slickcharts.com one could take each data line and print it on the screen at the time the trade was initiated.

Given the smallest interval is a second, in my version of the program below, I divide the second up by however many orders are placed, and then equally spread them throughout the second. Unfortunately, outside of regular trading hours certain seconds have no data. These gaps aren't accounted for in the program below. So going from 15:00:37 to 15:00:49, assuming no data in between, only takes 1 second rather than the 12 seconds it should take. But, given this type of program would generally be used to replay a trading day, I don't think these outside hours matter too much. I'll leave that for a future iteration.

The goal is to ultimately allow for replays at various speeds (1x, 2x, 5x the regular trading day), print the quotes on a graph and allow for sim trading through virtual order tracking. Lots more could be added, such as visual backtesting, but I'll save that for when I get some more of the basics done...

Here's the code:

import string
from time import sleep

f = open('gx120823/ES-2012-09.txt', 'r')

time_data = []
price_data = []
volume_data = []
oldtimeInfo = '15:00:00'

for line in f:
    line_split = string.split(line,',')
    timeInfo = '%s' % str(line_split[3])
    if oldtimeInfo == timeInfo:
        time_data = time_data + [str(line_split[3])]
        price_data = price_data + [float(line_split[4])]
        volume_data = volume_data + [int(line_split[5])]
    if oldtimeInfo != timeInfo:
        sleep_time = float(len(time_data))
        #print 'sleep_time is ', sleep_time
        j = 0
        for i in time_data:
            print i, '|', price_data[j], '|', volume_data[j]
            j = j + 1
            #print float(1/sleep_time)
            sleep(float(1/sleep_time))
        time_data = [str(line_split[3])]
        price_data = [float(line_split[4])]
        volume_data = [int(line_split[5])]
    oldtimeInfo = timeInfo

Import IB Historical Data into MySQL

Here's the code to import the csv files you created using the IB Historical Data Downloader (assuming you put all the files into a folder called "csv", have the below python file one folder above, and are running your MySQL server at localhost, with user root and database "stocks". Make sure to change the password to whatever you're using.) Also, you need to have the MySQLdb module installed. Check out the last post to see an easy way to do that.

import MySQLdb
import os
db = MySQLdb.connect(host='localhost', user='root',passwd='*****', db='stocks')
cur = db.cursor()
path = 'csv/'
listing = os.listdir(path)
for infile in listing:
    cur.execute("load data local infile 'csv/" + infile + "' into table `stocks`.`stock_prices_minute` fields terminated by ',' lines terminated by '\n' (`symbol`,`date`,`open`,`high`,`low`,`close`,`volume`);")
    db.commit()
    print "Symbol: " + infile

Installing MySQLdb Python Module

If you are running Python on Windows and want to install the MySQL python module, check out http://www.lfd.uci.edu/~gohlke/pythonlibs/. Go down to MySQL-python and select the file that corresponds to your version of Python (2.6,2.7,3.2) and Windows (x86 or 64). Double click, install, and you're good to go.

Non-GUI IB Historical Data Downloader

Here's some code to download quotes for the constituents of the S&P500 through Interactive Brokers' API using Python 2.7 and IbPy.

It will pause for 10 seconds after each request so that you won't go over the 60 symbol a minute limit. Also, it keeps track of which symbols you have downloaded. If you need to check for missed symbols go to the "downloaded_symbols.csv" file and match it to the entire S&P500 list. Other than the fact that you need a folder named "csv_day_test" in the same folder as the python script, there's not much to using this. It's pretty simple (and shouldn't hang like the GUI version sometimes does).

It's hard coded to do "1 min" bars over the course of "1 D". Change these if you'd like to download other data. You can also add/subtract symbols as you would expect. If you want it to print data it receives into the Python window, you can remove the # mark before the "print msg.reqId, msg.date," ... etc. This will, however, slow things down to some extent.

Here's the code:

 from time import sleep, strftime, localtime  
 from ib.ext.Contract import Contract  
 from ib.opt import ibConnection, message  
 import _mysql  
   
 new_symbolinput = ['MMM','ACE','AES','AFL','GAS','T','ABT','ANF','ACN','ADBE','AMD','AET','A','APD','ARG','AKAM','AA','ALXN','ATI','AGN','ALL','ANR','ALTR','MO','AMZN','AEE','AEP','AXP','AIG','AMT','AMP','ABC','AMGN','APH','APC','ADI','AON','APA','AIV','APOL','AAPL','AMAT','ADM','AIZ','AN','AZO','ADSK','ADP','AVB','AVY','AVP','BBT','BMC','BHI','BLL','BAC','BCR','BAX','BEAM','BDX','BBBY','BMS','BRK B','BBY','BIG','BIIB','BLK','HRB','BA','BWA','BXP','BSX','BMY','BRCM','BF B','CA','CBG','CBS','CF','CHRW','CMS','CNX','CSX','CVS','CVC','COG','CAM','CPB','COF','CAH','CFN','KMX','CCL','CAT','CELG','CNP','CTL','CERN','CHK','CVX','CME','CMG','CB','CI','CINF','CTAS','CSCO','C','CTXS','CLF','CLX','COH','KO','CCE','CTSH','CL','CMCSA','CMA','CSC','CAG','COP','ED','STZ','CBE','GLW','COST','CVH','COV','CCI','CMI','DTV','DTE','DHR','DRI','DVA','DV','DF','DE','DELL','DNR','XRAY','DVN','DO','DFS','DISCA','DLTR','D','RRD','DOV','DOW','DPS','DD','DUK','DNB','ETFC','EMC','EOG','EQT','EMN','ETN','ECL','EIX','EW','EA','EMR','ETR','EFX','EQR','EL','EXC','EXPE','EXPD','ESRX','XOM','FFIV','FLIR','FMC','FTI','FDO','FAST','FDX','FII','FIS','FITB','FHN','FSLR','FE','FISV','FLS','FLR','F','FRX','FOSL','BEN','FCX','FTR','GME','GCI','GPS','GD','GE','GIS','GPC','GNW','GILD','GS','GR','GT','GOOG','GWW','HCP','HAL','HOG','HAR','HRS','HIG','HAS','HCN','HNZ','HP','HSY','HES','HPQ','HD','HON','HRL','DHI','HSP','HST','HCBK','HUM','HBAN','ITW','IR','TEG','INTC','ICE','IPG','IBM','IFF','IGT','IP','INTU','ISRG','IVZ','IRM','JDSU','JPM','JBL','JEC','JNJ','JCI','JOY','JNPR','KLAC','K','KEY','KMB','KIM','KMI','KSS','KFT','KR','LLL','LSI','LH','LRCX','LM','LEG','LEN','LUK','LXK','LIFE','LLY','LTD','LNC','LLTC','LMT','L','LO','LOW','MTB','M','MRO','MPC','MAR','MMC','MAS','MA','MAT','MKC','MCD','MHP','MCK','MJN','MWV','MDT','MRK','MET','PCS','MCHP','MU','MSFT','MOLX','TAP','MON','MCO','MS','MOS','MSI','MUR','MYL','NKE','NRG','NYX','NBR','NDAQ','NOV','NTAP','NFLX','NWL','NFX','NEM','NWSA','NEE','NI','NE','NBL','JWN','NSC','NU','NTRS','NOC','NUE','NVDA','ORLY','OKE','OXY','OMC','ORCL','OI','PCAR','PCG','PNC','PPG','PPL','PLL','PH','PDCO','PAYX','BTU','JCP','PBCT','POM','PEP','PKI','PRGO','PFE','PM','PSX','PNW','PXD','PBI','PCL','PX','PCP','PCLN','PFG','PLD','PG','PGN','PGR','PRU','PEG','PSA','PHM','QEP','QCOM','PWR','DGX','RL','RRC','RTN','RHT','RF','RSG','RAI','RHI','ROK','COL','ROP','ROST','RDC','R','SAI','SCG','SLM','SWY','CRM','SNDK','SLE','SLB','SCHW','SNI','SEE','SHLD','SRE','SHW','SIAL','SPG','SJM','SNA','SO','LUV','SWN','SE','S','STJ','SWK','SPLS','SBUX','HOT','STT','SRCL','SYK','STI','SUN','SYMC','SYY','TROW','TEL','TE','TJX','TGT','THC','TDC','TER','TSO','TXN','TXT','BK','WMB','TMO','TIF','TWC','TWX','TIE','TMK','TSS','TRV','TRIP','TYC','TSN','USB','UNP','UPS','X','UTX','UNH','UNM','URBN','VFC','VLO','VAR','VTR','VRSN','VZ','VIAB','V','VNO','VMC','WPX','WMT','WAG','DIS','WPO','WM','WAT','WPI','WLP','WFC','WDC','WU','WY','WHR','WFM','WIN','WEC','WYN','WYNN','XL','XEL','XRX','XLNX','XYL','YHOO','YUM','ZMH','ZION','EBAY']  
 newDataList = []  
 dataDownload = []  
   
 def historical_data_handler(msg):  
   global newDataList  
   #print msg.reqId, msg.date, msg.open, msg.high, msg.low, msg.close, msg.volume  
   if ('finished' in str(msg.date)) == False:  
     new_symbol = new_symbolinput[msg.reqId]  
     dataStr = '%s, %s, %s, %s, %s, %s, %s' % (new_symbol, strftime("%Y-%m-%d %H:%M:%S", localtime(int(msg.date))), msg.open, msg.high, msg.low, msg.close, msg.volume)  
     newDataList = newDataList + [dataStr]  
   else:  
     new_symbol = new_symbolinput[msg.reqId]  
     filename = 'minutetrades' + new_symbol + '.csv'  
     csvfile = open('csv_day_test/' + filename,'wb')  
     for item in newDataList:  
       csvfile.write('%s \n' % item)  
     csvfile.close()  
     newDataList = []  
     global dataDownload  
     dataDownload.append(new_symbol)  
   
 con = ibConnection()  
 con.register(historical_data_handler, message.HistoricalData)  
 con.connect()  
   
 symbol_id = 0  
 for i in new_symbolinput:  
   print i  
   qqq = Contract()  
   qqq.m_symbol = i  
   qqq.m_secType = 'STK'  
   qqq.m_exchange = 'SMART'  
   qqq.m_currency = 'USD'  
   con.reqHistoricalData(symbol_id, qqq, '', '1 D', '1 min', 'TRADES', 1, 2)  
   
   symbol_id = symbol_id + 1  
   sleep(10)  
   
 print dataDownload  
 filename = 'downloaded_symbols.csv'  
 csvfile = open('csv_day_test/' + filename,'wb')  
 for item in dataDownload:  
   csvfile.write('%s \n' % item)  
 csvfile.close()  

Note: Downloads everything except SLE and PGN. Might not be symbols on the S&P500; haven't checked yet...

IB Historical Quotes Downloader

Slowly getting to where I want it to be. Here's a workable version that downloads quotes (you can insert multiple tickers with commas separating them--no spaces after the commas) to csv files and stores them in a subdirectory "csv_day". The MySQL part shouldn't be difficult. But I'm working on dealing with IB's quote download limits and some python inefficiency.

Here's the current code:

 from time import sleep, strftime, localtime  
 from ib.ext.Contract import Contract  
 from ib.opt import ibConnection, message  
 from Tkinter import *  
 import _mysql  
 import string  
   
 class App:  
   
   def __init__(self, master):  
   
     #list to keep data series, list for multiple symbols, j to keep track of current symbol  
     self.newDataList = []  
     self.new_symbolinput = []  
     self.j=0  
   
     #connect here to prevent double connections later on...  
     self.con = ibConnection()  
     self.con.register(self.historical_data_handler, message.HistoricalData)  
     self.con.connect()  
   
     #begin gui (labels should make it fairly self-explanatory)  
     frame = Frame(master)  
     frame.pack()  
   
     self.mysqlinfo_label = Label(frame, text='MySQL fields:')  
     self.mysqlinfo_label.grid(row=0)  
   
     self.label_host = Label(frame, text='Host:')  
     self.label_host.grid(row=1)  
   
     host_text = StringVar()  
     host_text.set("127.0.0.1")  
   
     self.entry_host = Entry(frame, textvariable=host_text)  
     self.entry_host.grid(row=1, column=1)  
   
     self.label_user = Label(frame, text='User:')  
     self.label_user.grid(row=2)  
   
     user_text = StringVar()  
     user_text.set("root")  
   
     self.entry_user = Entry(frame, textvariable=user_text)  
     self.entry_user.grid(row=2, column=1)  
   
     self.label_password = Label(frame, text='Password:')  
     self.label_password.grid(row=3)  
   
     self.entry_password = Entry(frame, show="*")  
     self.entry_password.grid(row=3, column=1)  
   
     self.label_database = Label(frame, text='Database:')  
     self.label_database.grid(row=4)  
   
     database_text = StringVar()  
     database_text.set("stocks")  
   
     self.entry_database = Entry(frame, textvariable=database_text)  
     self.entry_database.grid(row=4, column=1)  
   
     self.label_empty = Label(frame, text='')  
     self.label_empty.grid(row=5)   
   
     self.label_twsfields = Label(frame, text='TWS fields:')  
     self.label_twsfields.grid(row=6)  
   
     self.label_server = Label(frame, text='Server:')  
     self.label_server.grid(row=7)  
   
     twsserver_text = StringVar()  
     twsserver_text.set("127.0.0.1")  
   
     self.entry_server = Entry(frame, textvariable=twsserver_text)  
     self.entry_server.grid(row=7, column=1)  
   
     self.label_empty = Label(frame, text='')  
     self.label_empty.grid(row=8)   
   
     self.label_twscontractinfo = Label(frame, text='TWS contract info:')  
     self.label_twscontractinfo.grid(row=9)  
   
     self.label_symbol = Label(frame, text='Symbol:')  
     self.label_symbol.grid(row=10)  
   
     self.entry_symbol = Entry(frame)  
     self.entry_symbol.grid(row=10, column=1)  
   
     self.label_barsize = Label(frame, text='Bar Size:')  
     self.label_barsize.grid(row=11)  
   
     self.barsize_selected = StringVar(frame)  
     self.barsize_selected.set("1 min")  
   
     self.optionmenu_barsize = OptionMenu(frame, self.barsize_selected, "30 secs", "1 min", "5 mins", "10 mins", "15 mins", "1 hour", "4 hours", "1 day")  
     self.optionmenu_barsize.grid(row=11, column=1)  
   
     self.label_duration = Label(frame, text='Duration:')  
     self.label_duration.grid(row=12)  
   
     self.duration_selected = StringVar(frame)  
     self.duration_selected.set("1 W")  
   
     self.optionmenu_duration = OptionMenu(frame, self.duration_selected, "1 H", "4 H", "1 D", "1 W", "1 M", "1 Y")  
     self.optionmenu_duration.grid(row=12, column=1)  
       
   
     self.label_empty = Label(frame, text='')  
     self.label_empty.grid(row=13)      
   
     self.button_download = Button(frame, text="Download", command=self.tws_connect)  
     self.button_download.grid(row=14, column=1)  
   
     self.button_import = Button(frame, text="Import", command=self.mysql_connect)  
     self.button_import.grid(row=15, column=1)  
   
     self.label_empty = Label(frame, text='')  
     self.label_empty.grid(row=16)   
   
   #function for sending historical data requests  
   def tws_connect(self):  
     print "connecting to tws..."  
     print "tws server: " + self.entry_server.get()  
   
     self.new_symbolinput = string.split(self.entry_symbol.get(), ',')  
   
     print self.new_symbolinput  
   
     #run through all symbols, requesting historical data  
     self.symbol_id = 0  
     for i in self.new_symbolinput:  
       print i  
       qqq = Contract()  
       qqq.m_symbol = i  
       qqq.m_secType = 'STK'  
       qqq.m_exchange = 'SMART'  
       qqq.m_currency = 'USD'  
       endtime = strftime('%Y%m%d %H:%M:%S')  
       durationreq = '%s' % self.duration_selected.get()  
       barsizereq = '%s' % self.barsize_selected.get()  
       self.con.reqHistoricalData(self.symbol_id, qqq, '', durationreq, barsizereq, 'TRADES', 1, 2)  
       self.symbol_id = self.symbol_id + 1  
   
       #if doing more than 60 symbols, this is a simple way to avoid IB's 60symbol/10min limit...  
         
   #function for putting data into csv file and, eventually, into mysql database  
   def mysql_connect(self):  
       
     print "MySQL host: " + self.entry_host.get()  
     print "MySQL user: " + self.entry_user.get()  
     print "MySQL database: " + self.entry_database.get()  
   
   #required for IB API  
   def historical_data_handler(self, msg):  
     print msg.reqId, msg.date, msg.open, msg.high, msg.low, msg.close, msg.volume  
   
     #don't add 'finished...' statement to data list  
     if ('finished' in str(msg.date)) == False:  
       new_symbol = self.new_symbolinput[msg.reqId]  
       dataStr = '%s, %s, %s, %s, %s, %s, %s' % (new_symbol, strftime("%Y-%m-%d %H:%M:%S", localtime(int(msg.date))), msg.open, msg.high, msg.low, msg.close, msg.volume)  
       self.newDataList.append(dataStr)  
     #if 'finished...' appears jump to next symbol (THIS IS BROKEN because IB doesn't necessarily send requests back to you in the order sent to them...)  
     else:  
       new_symbol = self.new_symbolinput[msg.reqId]  
       filename = 'minutetrades' + new_symbol + '.csv'  
       csvfile = open('csv_day/' + filename,'wb')  
       for item in self.newDataList:  
         csvfile.write('%s \n' % item)  
       csvfile.close()  
       self.newDataList = []  
   
 root = Tk()  
 root.title('Historical Data: Download and Import')  
 app = App(root)  
   
 #run gui  
 root.mainloop()  

Multiple Tickers on IB

I believe I've fixed the problem accessing multiple tickers under my Python IB historical data extractor. Instead of hoping IB will send me the data in the order I sent IB the data, I match the initial tickerId I send IB with the msg.reqId I receive back. All tickers get aligned correctly and if you happen to input an invalid ticker or IB decides not to send you a ticker, it doesn't get matched up to some other ticker's data (you just don't get it in the output file).

I'll post the code shortly...I'd like to get the MySQL part done too.

Download Historical Data From Interactive Brokers

If you enjoy the following, consider signing up for the Gödel's Market Newsletter.

It's not quite done. I haven't worked out the kinks with downloading multiple symbols at a time (sometimes it works, sometimes it doesn't download one and the symbol names in the csv file get screwed up...). Also, it doesn't connect and import the created csv file into your MySQL database. That shouldn't be too big of a step, especially since it's already programmed for the Yahoo! Importer. Working out the other kink will take more time.

Again, you need certain modules imported. Python-MySQL and IbPy, being the most important.

Here's alpha code v0.01:

 from time import sleep, strftime, localtime  
 from ib.ext.Contract import Contract  
 from ib.opt import ibConnection, message  
 from Tkinter import *  
 import _mysql  
 import csv  
 import string  
   
   
 class App:  
   
   def __init__(self, master):  
   
     self.newDataList = []  
     self.new_symbolinput = []  
     self.j=0  
   
     #connect here to prevent double connections later on...  
     self.con = ibConnection()  
     self.con.register(self.historical_data_handler, message.HistoricalData)  
     self.con.connect()  
   
     frame = Frame(master)  
     frame.pack()  
   
     self.mysqlinfo_label = Label(frame, text='MySQL fields:')  
     self.mysqlinfo_label.grid(row=0)  
   
     self.label_host = Label(frame, text='Host:')  
     self.label_host.grid(row=1)  
   
     host_text = StringVar()  
     host_text.set("127.0.0.1")  
   
     self.entry_host = Entry(frame, textvariable=host_text)  
     self.entry_host.grid(row=1, column=1)  
   
     self.label_user = Label(frame, text='User:')  
     self.label_user.grid(row=2)  
   
     user_text = StringVar()  
     user_text.set("root")  
   
     self.entry_user = Entry(frame, textvariable=user_text)  
     self.entry_user.grid(row=2, column=1)  
   
     self.label_password = Label(frame, text='Password:')  
     self.label_password.grid(row=3)  
   
     self.entry_password = Entry(frame, show="*")  
     self.entry_password.grid(row=3, column=1)  
   
     self.label_database = Label(frame, text='Database:')  
     self.label_database.grid(row=4)  
   
     database_text = StringVar()  
     database_text.set("stocks")  
   
     self.entry_database = Entry(frame, textvariable=database_text)  
     self.entry_database.grid(row=4, column=1)  
   
     self.label_empty = Label(frame, text='')  
     self.label_empty.grid(row=5)   
   
     self.label_twsfields = Label(frame, text='TWS fields:')  
     self.label_twsfields.grid(row=6)  
   
     self.label_server = Label(frame, text='Server:')  
     self.label_server.grid(row=7)  
   
     twsserver_text = StringVar()  
     twsserver_text.set("127.0.0.1")  
   
     self.entry_server = Entry(frame, textvariable=twsserver_text)  
     self.entry_server.grid(row=7, column=1)  
   
     self.label_empty = Label(frame, text='')  
     self.label_empty.grid(row=8)   
   
     self.label_twscontractinfo = Label(frame, text='TWS contract info:')  
     self.label_twscontractinfo.grid(row=9)  
   
     self.label_symbol = Label(frame, text='Symbol:')  
     self.label_symbol.grid(row=10)  
   
     self.entry_symbol = Entry(frame)  
     self.entry_symbol.grid(row=10, column=1)  
   
     self.label_barsize = Label(frame, text='Bar Size:')  
     self.label_barsize.grid(row=11)  
   
     self.barsize_selected = StringVar(frame)  
     self.barsize_selected.set("1 min")  
   
     self.optionmenu_barsize = OptionMenu(frame, self.barsize_selected, "30 secs", "1 min", "5 mins", "10 mins", "15 mins", "1 hour", "4 hours", "1 day")  
     self.optionmenu_barsize.grid(row=11, column=1)  
   
     self.label_duration = Label(frame, text='Duration:')  
     self.label_duration.grid(row=12)  
   
     self.duration_selected = StringVar(frame)  
     self.duration_selected.set("1 W")  
   
     self.optionmenu_duration = OptionMenu(frame, self.duration_selected, "1 H", "4 H", "1 D", "1 W", "1 M", "1 Y")  
     self.optionmenu_duration.grid(row=12, column=1)  
       
   
     self.label_empty = Label(frame, text='')  
     self.label_empty.grid(row=13)      
   
     self.button_download = Button(frame, text="Download", command=self.tws_connect)  
     self.button_download.grid(row=14, column=1)  
   
     self.button_import = Button(frame, text="Import", command=self.mysql_connect)  
     self.button_import.grid(row=15, column=1)  
   
     self.label_empty = Label(frame, text='')  
     self.label_empty.grid(row=16)   
   
   def say_hi(self):  
     print "loading data..."  
   
   def tws_connect(self):  
     print "connecting to tws..."  
     print "tws server: " + self.entry_server.get()  
   
     self.new_symbolinput = string.split(self.entry_symbol.get(), ',')  
   
     #print raw_symbol_input  
     print self.new_symbolinput  
   
     print self.j  
   
     for i in self.new_symbolinput:  
       print i  
       qqq = Contract()  
       qqq.m_symbol = i  
       qqq.m_secType = 'STK'  
       qqq.m_exchange = 'SMART'  
       qqq.m_currency = 'USD'  
       endtime = strftime('%Y%m%d %H:%M:%S')  
       durationreq = '%s' % self.duration_selected.get()  
       barsizereq = '%s' % self.barsize_selected.get()  
       self.con.reqHistoricalData(0, qqq, '', durationreq, barsizereq, 'TRADES', 1, 2)  
   
   def mysql_connect(self):  
   
     #write newDataList to csv file  
     csvfile = open('minutetrades2.csv','wb')  
     for item in self.newDataList:  
       csvfile.write('%s \n' % item)  
   
     csvfile.close()  
   
     print "Printing dataList..."  
     print self.newDataList  
     print "connecting to mysql..."  
       
     print "MySQL host: " + self.entry_host.get()  
     print "MySQL user: " + self.entry_user.get()  
     print "MySQL database: " + self.entry_database.get()  
   
     self.contract_info()  
   
   def contract_info(self):  
     print "contract info..."  
     print "Symbol: " + self.entry_symbol.get()  
     print "Bar size: " + self.barsize_selected.get()  
   
   def historical_data_handler(self, msg):  
     print msg.date, msg.open, msg.high, msg.low, msg.close, msg.volume  
     if ('finished' in str(msg.date)) == False:  
       new_symbol = self.new_symbolinput[self.j]  
       dataStr = '%s, %s, %s, %s, %s, %s, %s' % (new_symbol, strftime("%Y-%m-%d %H:%M:%S", localtime(int(msg.date))), msg.open, msg.high, msg.low, msg.close, msg.volume)  
       #prevent addition of 'finished...' statement to newDataList  
       self.newDataList.append(dataStr)  
     else:  
       self.j = (self.j)+1  
       print self.j  
   
 root = Tk()  
 root.title('Historical Data: Download and Import')  
 app = App(root)  
   
 root.mainloop()  

(If you've enjoyed this article, consider signing up for the Gödel's Market Newsletter.)

Simple Version of Yahoo! MySQL Importer

If you enjoy the following, consider signing up for the Gödel's Market Newsletter.

I decided to post the source code for my Yahoo! Stock Quote MySQL Importer Python script.

This is the simple version without the ability to add multiple stock tickers at once and no choice of time limits. Also, it doesn't download economic info or keep track of dividends.

In order to use this, you must have Python 2.7 (or something that can run the code equivalently; I don't think Python 3+ works), the Python-MySQL (MySQLdb) module, and MySQL installed (I'm using server 5.5).

Within your MySQL server you must have a "schema" (or "database") called "stocks". Actually, you can call it whatever you want, because you have the option to change the name in the Python program. But, it is preselected for "stocks".

Within this "schema"/"database" you must have a table called "stock_prices_day". This is hardcoded, but can obviously be changed if you know a little python, or just read through the code and look for any instance of "stock_prices_day" and replace them with whatever you want your table to be called.

Within "stock_prices_day" you must have the following columns with the indicated data types: "symbol" varchar(5), "date" date, "open" decimal(5,3), "high" decimal(5,3), "low" decimal(5,3), "close" decimal(5,3), "volume" int, "adj_close" decimal(5,3).

When you have all of that, you can run the following code, insert your server information (ip, username, password, etc) and the symbol you want to insert. It'll download it and insert the symbol into the database.

BAM! you're on your way to a real hedge fund-esque setup. :D (well, almost).

Rules on the code:
1) Only for personal use by investors with less than $100k in liquid assets (not for use by businesses, corporations, etc).
2) Not for resale and reuse requires my consent.
3) Post a comment if you'd like access to a more complete version (date selection, multiple symbols, etc).

 from time import sleep, strftime, localtime  
 from Tkinter import *  
 import MySQLdb  
 import urllib  
 import csv  
 import os  
   
 class App:  
   
   def __init__(self, master):  
   
     frame = Frame(master)  
     frame.pack()  
   
     self.mysqlinfo_label = Label(frame, text='MySQL fields:')  
     self.mysqlinfo_label.grid(row=0)  
   
     self.label_host = Label(frame, text='Host:')  
     self.label_host.grid(row=1)  
   
     host_text = StringVar()  
     host_text.set("127.0.0.1")  
   
     self.entry_host = Entry(frame, textvariable=host_text)  
     self.entry_host.grid(row=1, column=1)  
   
     self.label_user = Label(frame, text='User:')  
     self.label_user.grid(row=2)  
   
     user_text = StringVar()  
     user_text.set("root")  
   
     self.entry_user = Entry(frame, textvariable=user_text)  
     self.entry_user.grid(row=2, column=1)  
   
     self.label_password = Label(frame, text='Password:')  
     self.label_password.grid(row=3)  
   
     self.entry_password = Entry(frame, show="*")  
     self.entry_password.grid(row=3, column=1)  
   
     self.label_database = Label(frame, text='Database:')  
     self.label_database.grid(row=4)  
   
     database_text = StringVar()  
     database_text.set("stocks")  
   
     self.entry_database = Entry(frame, textvariable=database_text)  
     self.entry_database.grid(row=4, column=1)  
   
     self.label_empty = Label(frame, text='')  
     self.label_empty.grid(row=5)   
   
     self.label_twscontractinfo = Label(frame, text='Yahoo info:')  
     self.label_twscontractinfo.grid(row=9)  
   
     self.label_symbol = Label(frame, text='Symbol:')  
     self.label_symbol.grid(row=10)  
   
     self.entry_symbol = Entry(frame)  
     self.entry_symbol.grid(row=10, column=1)  
       
   
     self.label_empty = Label(frame, text='')  
     self.label_empty.grid(row=13)      
   
     self.button_download = Button(frame, text="Download", command=self.tws_connect)  
     self.button_download.grid(row=14, column=1)  
   
     self.button_import = Button(frame, text="Import", command=self.mysql_connect)  
     self.button_import.grid(row=15, column=1)  
   
     self.label_empty = Label(frame, text='')  
     self.label_empty.grid(row=16)   
   
   
   def tws_connect(self):  
     print "downloading csv file from yahoo..."  
     webFile = urllib.urlopen("http://ichart.finance.yahoo.com/table.csv?s=" + self.entry_symbol.get())  
     fileName = self.entry_symbol.get() + ".csv"  
     localFile = open(fileName.split('/')[-1], 'w')  
     localFile.write(webFile.read())  
     webFile.close()  
     localFile.close()  
   
     fieldnames = ['Symbol', 'Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Adj Close']  
     fileName2 = self.entry_symbol.get() + "2.csv"  
     with open(fileName, 'rb') as csvinput:  
       with open(fileName2, 'wb') as csvoutput:  
         csvwriter = csv.DictWriter(csvoutput, fieldnames, delimiter=',')  
         csvwriter.writeheader()  
         for row in csv.DictReader(csvinput):  
           row['Symbol'] = self.entry_symbol.get()  
           csvwriter.writerow(row)  
   
   
   def mysql_connect(self):  
     print "connecting to mysql..."  
       
     print "MySQL host: " + self.entry_host.get()  
     print "MySQL user: " + self.entry_user.get()  
     print "MySQL database: " + self.entry_database.get()  
   
     db = MySQLdb.connect(host=self.entry_host.get(), user=self.entry_user.get(),passwd=self.entry_password.get(), db=self.entry_database.get())  
     cur = db.cursor()  
   
     current_path = os.path()  
   
     print current_path  
   
     cur.execute("load data local infile '" + current_path + self.entry_symbol.get() + "2.csv' into table `stocks`.`stock_prices_day` fields terminated by ',' lines terminated by '\n' ignore 1 lines (`symbol`,`date`,`open`,`high`,`low`,`close`,`volume`,`adj_close`);")  
     db.commit()  
     self.contract_info()  
   
   def contract_info(self):  
     print "contract info..."  
     print "Symbol: " + self.entry_symbol.get()  
   
 root = Tk()  
 root.title('Historical Data: Download and Import')  
 app = App(root)  
   
 root.mainloop()  

(If you've enjoyed this, consider signing up for the Gödel's Market Newsletter.)