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