Difference between revisions of "Importing fund or stock prices from an OFX file"

Revision as of 15:42, 26 January 2011

How to Import Fund or Stock Prices From an OFX File

The attached python script show how to import prices from an OFX file. This was useful for me because my 401k funds are not publicly traded so the norm import prices tool doesn't work. The OFX file has prices in the <INVPOS> aggregate. I borrowed heavily from work done by Peter Holtermann and others (price_database_example.py in src/optional/python-bindings/example_scripts).

Attached are 2 python scripts. getPrices.py reads the OFX file and outputs a CSV file in this format: fundName, date, price. The date looks like this: 20110118120000, year month day time. I suggest you try this standalone 1st and see if it works for your OFX file.

The other file, importPrice.py calls getPrices.py and uses the python bindings to update the price database in GC.

Another very useful tool is ofx.py, found at http://www.jongsma.org/gc/. I use this to download the OFX file.

#!/usr/bin/python
# usage: getPrices.py ofxfile outfile
# Read an OFX w/ an INVPOS aggregate and write the prices
# to a CSV file

# Brad Haack 2011-01-25

import sys

fp = open(sys.argv[1])
fout = open(sys.argv[2],'w')

flines = fp.readlines()

inlist=0
inpos=0
id = 0
unqid=[]
price=[]
date=[]

for line in flines:
    line = line.rstrip('\n')
    wd = line.split('>')
    for id in range(len(wd)):
        wi = wd[id]
        if wi == '<INVPOSLIST': # now we're in the list
            inlist = 1
        if inlist :
            if wi == '<INVPOS': # now we're in the pos info aggregate
                inpos = 1
                unqid.append(0)
                price.append(0)
                date.append(0)
            if inpos :
                if wi == '<UNIQUEID' :
                    unqid[id] = wd[id+1]
                if wi == '<UNITPRICE' :
                    price[id] = wd[id+1]
                if wi == '<DTPRICEASOF' :
                    date[id] = wd[id+1]
            if wi == '</INVPOS': # now we're out of the pos info aggregate
                inpos = 0
                str = '%s, %s, %s\n' % (unqid[id], date[id], price[id])
                fout.write( str)
            
        if wi == '\INVPOSLIST>': # now we're not in the list
            inlist=0
        
<\pre>

 importPrice.py ---
<pre>

!/usr/bin/python
 Usage: importPrice.py ofxfile gcfile 
 Import prices from an OFX file into Gnucash
 Configuration Options
cur_mnemonic="USD"
namespace_name = "401k"
from gnucash import Session, Account, Split
import gnucash
import datetime
import sys
import os
ofxfile = sys.argv[1]
gcfile = sys.argv[2]
pricefile = 'pq.csv'

 Could call ofx.py here (see http://www.jongsma.org/gc/)
 ofx.py site user account
 get the prices from the ofx file
os.system('getPrices.py %s %s' % (ofxfile, pricefile) )
url = "xml://"+gcfile

 Read input data from file
f = open(pricefile)
data = []
while 1:

   tmp = f.readline()
   if(len(tmp)<2):
       break
   
   data.append(tmp)

f.close()
stock_date = []
stock_price = []
stock = []
for i in range(len(data)):

   date = data[i].rsplit(',')[1].lstrip()
   year = int(date[0:4])
   month = int(date[4:6])
   day = int(date[6:8])
   stock_date.append(datetime.datetime(year,month,day))
   stock_price.append(float(data[i].rsplit(',')[2]))
   stock.append(data[i].rsplit(',')[0])
   

 Initialize Gnucash session
session = Session(url, True, False, False)
book = session.book
pdb = book.get_price_db()
comm_table = book.get_table()
cur = comm_table.lookup("CURRENCY", cur_mnemonic)
commodities=comm_table.get_commodities(namespace_name)
for ii, stocki in enumerate(stock):

 # find the commodity cusip that matches
 for cmdt in commodities:
   if cmdt.get_cusip() == stocki:
     
     # Get stock data
     pl = pdb.get_prices(cmdt,cur)
     if len(pl)<1:
       print 'Need at least one database entry to clone ...'
       print '... %s ...' % (cmdt.get_fullname())
       exit()
     pl0 = pl[0]
     p_new = pl0.clone(book)
     p_new = gnucash.GncPrice(instance=p_new)
     print 'Adding %30s  %s  %s  %f' % (cmdt.get_fullname(),stocki,stock_date[ii],stock_price[ii])
     p_new.set_time(stock_date[ii])
     v = p_new.get_value()
     v.denom = 1000000
     v.num = int(stock_price[ii]*v.denom)
     p_new.set_value(v)
     pdb.add_price(p_new)




 Clean up
session.save()
session.end()
session.destroy()

<\pre>