Importing fund or stock prices from an OFX file

The attached python scripts show how to import prices from an OFX file. This was useful for me because my 401k funds are not publicly traded so the normal 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 OFX files.

--- getPrices.py ----

#!/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
        

--- importPrice.py ---

#!/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()