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.)