Revision as of 16:43, 19 January 2011

This document explains how to import historic stock quotes into gnucash

Some knowledge of perl and python is required. 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 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

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()
comm_table = book.get_table()
ac = FindAccount(account,'Intel')[0] 

stock = ac.GetCommodity()
# Add the prices
pdb = book.get_price_db()
if len(ac.GetSplitList())<1:
  print 'Need at least one Split to get currency info ... '
cur = Split(instance=ac.GetSplitList()[0]).GetParent().GetCurrency()

# 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 have now a nice stock quotes from Intel in your Gnucash file!