Hackszine.com: HOWTO - track stocks in Google Spreadsheets

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

Posted by Jason Striegel | Oct 13, 2008 08:53 PM
Ajax, Data, Google, Life, Online Investing, Productivity | Permalink | Comments (5)

Comments

Newest comments listed first.

Posted by: kevin on October 15, 2008 at 4:46 PM

Not all Exchange are supported

Thanks for the post. Nice one. One question:
For instance: BMO is listed on both NYSE and TSE. So the following works:
=GoogleFinance(NYSE:BMO)
=GoogleFinance(BMO)
but not
=GoogleFinance(TSE:BMO)

Am I doing anything wrong? Any suggestion?

Thanks

Posted by: Michael Chelen on October 16, 2008 at 12:22 AM

end date option

Instead of the end date, a set number of days can be used:
"num_days" | "end_date"
"can be either the end date for the time period over which you want to see historical data, or the number of days from the start date. Any number less than 50 is considered to be num_days. Otherwise it is considered an end_date."

Posted by: Jason Striegel on October 16, 2008 at 12:46 AM

TSE not supported

@kevin - that's odd, but I see the same problem. It doesn't work for me without quotes, but if I search "TSE:BMO" I get a "the TSE exchange is not supported" error.

One option might be to screen scrape the market data table on the normal Google Finance url (http://finance.google.com/finance?q=TSE:BMO) using the =importHTML function. I can't sort out how the flash on that page is pulling in historical data via, but there's probably a way to get that in raw XML format and bring it in as well using the =importXML function.

Still makes me wonder why the TSE exchange isn't supported, though.

Posted by: Kevin on October 16, 2008 at 10:48 PM

TSE not supported

Thanks Jason for the tip. The official Google doc says that Foreign Exchanges are not supported but will be.

Posted by: Pragan on November 7, 2008 at 7:43 AM

Dynamic Stock symbol?

Hi,

thanks for the information in getting the stock data using spreadsheets. I would like to know if there is any way to feed the stock symbol dynamically like $symbol,
=GoogleFinance("$symbol", "price", "1/1/2008″, "10/10/2008", "WEEKLY")

so that we can get the data for the particular symbol if someone select a symbol like "GOOG".

Is this possible? It will be helpful if we need to show many symbols and I guess it would be tough to write the above line for many symbols.

Also, can you please guide me how to retrieve the data from spreadsheet and display as chart and data in a webpage?

Please let me know. You can also email me!!

thanks
pragan.