New Forum

Visit the new forum at http://godelsmarket.com/bb

Tuesday, July 10, 2012

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

3 comments:

  1. Great work here - I would really appreciate access to the more complete version. Also, is there any interest in a similar project with event based (econ calendar) data?

    ReplyDelete
  2. Thank you :) I've been meaning to do an econ calendar, as I've done similar things in the past. It'd probably just scrape Yahoo's econ data using the BeatifulSoup Python module. I haven't gotten around to it, and am very busy right now. Sorry about that.

    Anyway, here's the "more" full version of the above script. Separate symbols with commas (no spaces) and play around with the data entry. I think you have to put two numbers (e.g. 01 for jan) and it may have other sensitivities that never really bothered me (so I never changed the code).

    https://docs.google.com/file/d/0BwSJPSaBDQ-fTWRPS0VBZWpDT0E/edit

    Should be accessible to anyone through that link. Let me know if you have any problems with it. It should work. I've used it to create a database of 1k+ symbols.

    ReplyDelete
  3. eToro is the ultimate forex broker for rookie and professional traders.

    ReplyDelete