Historical Prices

[Michael Chong]

Hi,

Just in case anyone is interested. I tried this and it seemed to work
OK. It only works if you use MYSQL as a backend. If you have a FUND as a
SECURITY it will auto magically create a back fill file.

You then load the file into the prices table using mysqlimport. I have
an unqiue index on the prices table on commodity guid and date so I do
not store dups. mysqlimport will not pump in duplicates if this index is
in place.

Feel free to hack as needed.

Best, Mike

====================== I am on Linux ===================================
#!/usr/bin/python

import MySQLdb, urllib2, uuid, string, re, math

pattern = re.compile("([0-9\.]{12})e\+([0-9]+)")

def pricefix(x):
        global pattern
        y = "%.10e" % x
        m = pattern.match(y)
        mantissa = float(m.group(1))
        exponent = "%d" % (100000000 / math.pow(10,float(m.group(2))))
        mantissa *= 100000000
        mantissa = "%d" % mantissa
        return (mantissa,
exponent)                                                                                 
                                                                                                                    
s = MySQLdb.connect(host="localhost", user="YOURUSERIDHERE",
passwd="YOURPASSWD",
db="gnucash" )                                        
cur =
s.cursor()                                                                                                    
execstr = 'select guid, mnemonic from commodities where quote_source =
"yahoo" '                                    
cur.execute(execstr)                                                                                                
rec =
cur.fetchall()                                                                                                

execstr = 'select guid from commodities where mnemonic = "USD" '
cur.execute(execstr)
rec2 = cur.fetchall()
usdguid = rec2[0][0]

baseurl = "http://ichart.finance.yahoo.com/table.csv?"
for row in rec:
        guid = row[0]
        ticker = row[1]
        # Change 2008 to 1980 etc for more data if required
        url = "%s&s=%s&a=0&b=1&c=2008&q=d" % (baseurl, ticker)
        quotelines = urllib2.urlopen(url)
        count = 0
        for line in quotelines:
                if(count == 0):
                        count += 1
                        continue
                x = line.split(",")
                pstr =  "%s" % uuid.uuid4()
                pstr = string.replace(pstr, "-", "")
                pstr += "|%s" % guid
                pstr += "|%s" % usdguid
                pstr += "|%s" % x[0]
                pstr += "|Yahoo"
                pstr += "|last"
                mantissa, exponent = pricefix(float(x[4]))
                pstr += "|%s" % mantissa
                pstr += "|%s" % exponent
                print pstr