Revision as of 17:16, 27 January 2011

This document explains how to import historic stock quotes into gnucash

Some knowledge of perl and python is required. This example uses GnuCashs Python Bindings. You have to make sure that they are available to get this working.

To run the scripts below you need a gnucash xml file named "test.gnucash" with an account named Intel and a booking of the share INTC within this account. How you add the INTC stock to Gnucash is explained here Add stock to portfolio. You have additionally to add one price of INTC into the database per hand.

Get the data

We get the data with the perl module QuoteHist. A sample perl script to get quotes is e.g.:

#!/usr/bin/perl -w
use Finance::QuoteHist;
print "Will get stock quotes of $ARGV[0] and save it into the file $ARGV[0]\n";
$fname = $ARGV[0];
   open (MYFILE, ">$fname");
   $q = Finance::QuoteHist->new
      (
       symbols    => [($ARGV[0])],
       start_date => '01/01/2000',
       end_date   => 'today',
      ); 


print "name,date, open, high, low, close, volume\n";
foreach $row ($q->quotes()) {
       ($name,$date, $open, $high, $low, $close, $volume) = @$row;
       print MYFILE "$name,$date, $open, $high, $low, $close, $volume\n";
   }

close(MYFILE);

On Unix/Linux save the text into a file (e.g. get_quotes) , do a chmod a+x and execute it with the argument INTC to get the Intel prices saved into the file INTC

chmod a+x get_quotes
./get_quotes INTC

The INTC file should look similar to this:

INTC,2000/01/03, 83.2700, 87.3700, 83.2500, 87.0000, 57710200
INTC,2000/01/04, 85.4400, 87.8700, 82.2500, 82.9400, 51019600
INTC,2000/01/05, 83.0000, 85.8700, 80.5000, 83.6200, 52389000

and so on

Import the data into Gnucash

The following python script will read the text file "INTC" and add the prices to the Gnucash file "test.gnucash".

from gnucash import Session, Account, Split
import gnucash
import datetime
from fractions import Fraction

# Function definition from Christoph Holtermann
def FindAccount(account,name,account_list=None):
  """Searches full names of account and descendents

  returns a list of accounts which contain name."""

  if not account_list:
    account_list=[]

  for child in account.get_children():
     child=Account(instance=child)
     account_list=FindAccount(child,name,account_list)
  
  Account_name=account.GetName()
  if name in Account_name:
    account_list.append(account)
  
  return account_list

FILE = "./test.gnucash"
url = "xml://"+FILE

# Read data from file
f = open('INTC')
data = []
while 1:
    tmp = f.readline()
    if(len(tmp)<2):
        break
    
    data.append(tmp)

f.close()

stock_date = []
stock_price = []
for i in range(1,len(data)):
    year = int(data[i].rsplit(',')[1].rsplit('/')[0])
    month = int(data[i].rsplit(',')[1].rsplit('/')[1])
    day = int(data[i].rsplit(',')[1].rsplit('/')[2])
    stock_date.append(datetime.datetime(year,month,day))
    stock_price.append(float(data[i].rsplit(',')[5]))

# Initialize Gnucash session
session = Session(url, True, False, False)
root = session.book.get_root_account()
book = session.book
account = book.get_root_account()
pdb = book.get_price_db()
commod_table = book.get_table()
stock = commod_table.lookup('NASDAQ', 'INTC')
cur = commod_table.lookup('CURRENCY', 'USD')
# Add the prices
pdb = book.get_price_db()
# Get stock data
pl = pdb.get_prices(stock,cur)
if len(pl)<1:
  print 'Need at least one database entry to clone ...'

pl0 = pl[0]
for i in range(1,len(pl)):
  pdb.remove_price(pl[i])

for i in range(0,len(stock_date)):
  p_new = pl0.clone(book)
  p_new = gnucash.GncPrice(instance=p_new)
  print 'Adding',i,stock_date[i],stock_price[i]
  p_new.set_time(stock_date[i])
  v = p_new.get_value()
  v.num = int(Fraction.from_float(stock_price[i]).limit_denominator(100000).numerator)
  v.denom = int(Fraction.from_float(stock_price[i]).limit_denominator(100000).denominator)
  p_new.set_value(v)
  pdb.add_price(p_new)

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

You now have the stock quotes from Intel in your Gnucash file!

Further Information

• Python Bindings in this Wiki.
• python-bindings in doxygen source-documentation