Hackszine.com: Online Investing Archives

Archive: Online Investing

October 23, 2008

Simple stock quote grabbing with Perl

Hackszine reader 3riador wrote in to recommend a quick and easy way to grab stock quotes using Perl and the Finance::Quote CPAN module. The codebase is actively maintained and has been around for some time, having first been distributed as part of GNUCash before becoming its own project.

Paul Fenwick, one of the GNUCash developers, had this to say in an article for The Perl Journal in 2000:

If you have a reason to watch the world's financial markets, and you know a little about perl, then you may find that the Finance::Quote package comes in handy. I personally use it to remind myself that I should never buy shares, as I have a good history of losing money on the stock exchange. However, you can use Finance::Quote to help track those tasty stock options you've been offered, or even to help you build dynamic artwork driven by fluctuations in the world markets.

Near as I can tell, the dynamic artwork that's referred to is the Stock Puppets presentation which was shown at 2000s Burning Man event (can anyone confirm this?). The idea was to have large marionettes controlled directly by stock market data, some servos, Basic Stamp microcontrollers, and IBM Thinkpads pulling market data using the Finance::Quote library.

To use Finance::Quote in your own projects is a simple task. Here's a few lines of code that will print the current price of Google shares:

#!/usr/bin/perl -w
use strict;
use Finance::Quote;

my $q = Finance::Quote->new();
my %data = $q->fetch('usa', 'GOOG');
print $data{'GOOG', 'price'} . "\n";

The Dabbler Blog has more information on installation and basic usage, and The Perl Journal article is a good resource for those wishing to delve any deeper.

Finance::Quote Perl Library
Dabbler Blog - Fast and Simple Stock Quotes Using Perl
Finance::Quote Article In The Perl Journal
Stock Puppets

October 13, 2008

HOWTO - track stocks in Google Spreadsheets

One of the most convenient features in Google Spreadsheets is the ability to pull live external data sources into any worksheet. Instead of copying data into your worksheet, when the linked source changes, the cells in your spreadsheet will automatically update, which can save a lot of work if you pull reports regularly. This external data can be pulled from XML, other spreadsheet documents, and even (assuming you can bear to look) current and historical stock quotes from Google Finance.

Linking a worksheet to Google Finance is as simple as calling the GoogleFinance spreadsheet function. There are two ways to use it: you can pull current information on a ticker symbol, or you can pull historical trade data for a particular date range. Here's how:

Retrieving Current Stock Information

If you call the GoogleFinance function with two attributes, you can link to current market data for a particular ticker symbol. Just open any cell in your worksheet and enter the following:

=GoogleFinance("symbol", "attribute")

Replace "symbol" with the ticker id, such as GOOG or AAPL. The attribute parameter determines what information will be retrieved for that symbol. There are a number of supported attributes, including price, volume, tradetime, beta, pe (price to earnings ratio), and changepct. If you omit the attribute parameter, it will default to "price". There are a number of other possible attributes which I haven't listed, including some specific to mutual funds, so check the documentation link below for the full list.

Pulling Historical Stock Data

Another thing that you can do is retrieve historical stock data over a large date range. Once you have this in your spreadsheets, you can use formulas to process, compare, and chart this information over time.

Here's the syntax for pulling historical stock data:

=GoogleFinance("symbol", "attribute", "start_date", "end_date", "interval")

As in the previous example, "symbol" needs to be replaced with the desired ticker ID. The "attribute" parameter, however, works a little differently. It's possible values are limited to high, low, open, close, vol, and all. "start_date" and "end_date" define the range of data that will be retrieved, and interval should be set to "DAILY", "WEEKLY", or a number from 1-7, which represents the number of days between measurements.

When the stock data is retrieved, a number of columns and rows will be consumed to capture the linked data, so make sure you have room to accommodate the data you've requested. It's not a bad practice to contain this data in separate sheet. One thing I noticed is that the column names always appear in French for me, despite my language preference settings. If you notice this as well, you'll just have to ignore it until it's fixed.

You can have up to 250 of these Google Finance feeds in a single spreadsheet. It's not an unlimited amount, but it's not exactly lightning fast to pull that much data anyway. If you need more than that , one possible option is to separate your report data into different spreadsheets and then refresh them as needed.

Example Google Finance Spreadsheet
GoogleFinance Documentation and Examples