Difference between revisions of "Stocks/get prices"

From GnuCash
Jump to: navigation, search
m (I don't think Session( ) should ignore the lock file in this instance.)
 
(7 intermediate revisions by 3 users not shown)
Line 1: Line 1:
= This document explains how to import historic stock quotes into gnucash =
+
[[Category:Investment Management]]
  
Some knowledge of perl and python is required. This example uses GnuCash's [[Python Bindings]]. Ensure that both perl and python are available.
+
This document presents one way to '''import historic stock quotes''' into GnuCash. This example uses GnuCash's [[Python Bindings]] which may need to be installed separately.  
  
To run the scripts below you need a gnucash xml file named "test.gnucash". For illustration, we show how to get prices for an account named Intel that contains shares of the INTC stock.  The method for adding the INTC stock to Gnucash is explained here [[stocks/add_stock|Add stock to portfolio]]. You have additionally to add one price of INTC into the database by hand.
+
;Prerequisite: Some knowledge of Perl and Python is required.
 +
:Ensure that both '''Perl''' and '''Python''' are available.
 +
:You will also need to install the special Perl module '''Finance::QuoteHist''': [[https://metacpan.org/pod/Finance::QuoteHist metacpan.org] or [https://github.com/mojotoad/Finance-QuoteHist github]] and its dependencies.
 +
 
 +
To run the scripts below you need a GnuCash xml file named <tt>test.gnucash</tt>. For illustration, we show how to get prices for an account named <tt>Intel</tt> that contains shares of the INTC stock.  The method for adding the INTC stock to your GnuCash file is explained in [[stocks/add_stock|Add stock to portfolio]]. In addition, you have to add one price of INTC into the database by hand.
  
 
== Get the data ==
 
== Get the data ==
  
We get the data with the perl module [http://www.mojotoad.com/sisk/projects/Finance-QuoteHist/ Finance::QuoteHist]. A sample perl script to get the quotes is as follows:
+
We will get the data with Finance::QuoteHist and store it in a text file. The Perl script to get the quotes is as follows: <syntaxhighlight lang="py">
 +
#!/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',
 +
      );
  
#!/usr/bin/perl -w
+
print "name,date, open, high, low, close, volume\n";
use Finance::QuoteHist;
+
foreach $row ($q->quotes()) {
print "Will get stock quotes of $ARGV[0] and save it into the file $ARGV[0]\n";
+
      ($name,$date, $open, $high, $low, $close, $volume) = @$row;
$fname = $ARGV[0];
+
      print MYFILE "$name,$date, $open, $high, $low, $close, $volume\n";
    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);
 
  
 +
close(MYFILE);
 +
</syntaxhighlight>
  
On Unix/Linux, save the program text (above) into a file (e.g., get_quotes), make the script executable by running "chmod a+x get_quotes". Execute it with the argument INTC to get the Intel prices saved into the file INTC
+
On Unix/Linux, save the program text (above) into a file (e.g., get_quotes), make the script executable by running: <syntaxhighlight lang="sh">
 +
chmod a+x get_quotes
 +
</syntaxhighlight>
 +
;Example: Execute it with the argument INTC to get the Intel prices saved into the file <tt>INTC</tt>: <syntaxhighlight lang="console">
 
% chmod a+x get_quotes
 
% chmod a+x get_quotes
 
% ./get_quotes INTC
 
% ./get_quotes INTC
 +
</syntaxhighlight>
 +
 +
:The <tt>INTC</tt> file should look similar to this: <syntaxhighlight lang="text">
 +
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
 +
</syntaxhighlight>
 +
 +
== Import the data into GnuCash ==
 +
 +
The following python script will read the text file <tt>INTC</tt> and add the prices to the GnuCash file <tt>test.gnucash</tt>. <syntaxhighlight lang="py">
 +
 +
#!/usr/bin/python
 +
 +
from gnucash import Session, Account, Split
 +
import gnucash
 +
import datetime
 +
from fractions import Fraction
 +
 +
FILE = "./test.gnucash"
 +
url = "xml://"+FILE
  
The INTC file should look similar to this:
+
# Read data from file
INTC,2000/01/03, 83.2700, 87.3700, 83.2500, 87.0000, 57710200
+
f = open('INTC')
INTC,2000/01/04, 85.4400, 87.8700, 82.2500, 82.9400, 51019600
+
data = []
INTC,2000/01/05, 83.0000, 85.8700, 80.5000, 83.6200, 52389000
+
while 1:
 +
    tmp = f.readline()
 +
    if(len(tmp)<2):
 +
        break
 +
   
 +
    data.append(tmp)
  
 +
f.close()
  
== Import the data into Gnucash ==
+
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]))
  
The following python script will read the text file "INTC" and add the prices to the Gnucash file "test.gnucash".
+
# Initialize Gnucash session
 +
session = Session(url, False, 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 ...')
 +
  session.end()
 +
  session.destroy()
  
from gnucash import Session, Account, Split
+
pl0 = pl[0]
import gnucash
+
for i in range(1,len(pl)):
import datetime
+
  pdb.remove_price(pl[i])
from fractions import Fraction
 
 
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, False, 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!
+
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_time64(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)
  
== Further Information ==
+
# Clean up
 +
session.save()
 +
session.end()
 +
session.destroy()
 +
</syntaxhighlight>
  
* [[Python Bindings]] in this Wiki.
+
You now have the stock quotes from Intel in <tt>test.gnucash</tt>.
* [http://svn.gnucash.org/docs/HEAD/python_bindings_page.html python-bindings] in [[doxygen]] source-documentation
 

Latest revision as of 19:08, 12 October 2022


This document presents one way to import historic stock quotes into GnuCash. This example uses GnuCash's Python Bindings which may need to be installed separately.

Prerequisite
Some knowledge of Perl and Python is required.
Ensure that both Perl and Python are available.
You will also need to install the special Perl module Finance::QuoteHist: [metacpan.org or github] and its dependencies.

To run the scripts below you need a GnuCash xml file named test.gnucash. For illustration, we show how to get prices for an account named Intel that contains shares of the INTC stock. The method for adding the INTC stock to your GnuCash file is explained in Add stock to portfolio. In addition, you have to add one price of INTC into the database by hand.

Get the data

We will get the data with Finance::QuoteHist and store it in a text file. The Perl script to get the quotes is as follows:
#!/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 program text (above) into a file (e.g., get_quotes), make the script executable by running:
chmod a+x get_quotes
Example
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

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.
#!/usr/bin/python

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

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, False, 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 ...')
  session.end()
  session.destroy()

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_time64(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 test.gnucash.