Tip of the Month: Retrieving stock market data into your Google Docs-based spreadsheet

Yesterday I found this gem while creating a simple spreadsheet with a portfolio I am defining. Instead copy/pasting values of the stocks in my portfolio from any of the financial information providers, you can simply use the GoogleFinance(SYMBOL, ATTRIBUTE) function to get the latest indicators of your specified stock symbol.

Using GoogleFinance()

 

 

 

 

Where attributes could be:

  • price – Current Price of the stock
  • closeyest – Last closing price of the stock
  • priceopen – Current opening price of the stock
  • high – Daily high price of the stock
  • low – Daily low price of the stock
  • change – Change since the last posted closing price.
  • changepct – Percentage change since the last posted closing price
  • high52 – The 52 week high price for the stock
  • low52 – The 52 week low price for the stock.
  • eps – The calculated earnings per share
  • pe – The calculated price to earnings ratio. (Note that companies with negative earnings will not have a pe ratio).
  • volume – Number of shares traded.
  • marketcap – Market Capitalization
  • tradetime – Time of the last trade.
  • datadelay – The time delay from Google’s servers.
  • volumeavg – Average volume for the stock.
  • beta – Beta value of a stock.
  • shares – Outstanding shares of the stock.

The GoogleFinance() function could also pull historic data, allowing you to track the performance of any stock across a certain time period. To show historical data, type =GoogleFinance(“symbol”, “attribute”, “start_date”, “num_days|end_date”, “interval”) into a spreadsheet cell.

You’ll find details about the syntax of this formula below:

  • “Symbol” and “attribute” follow the same rules as above
  • “Start date” is the day you’d like to start showing information from
  • “num_days” | “end_date” [Optional] 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.
  • “interval” specifies granularity at which the stock data is shown, either daily or weekly. You can enter either “Daily” or “1” if you would like daily information and either “Weekly” or “7” for weekly data.

You can find more information about historical data and mutual fund data in the Google spreadsheets help center.

It is sad to find out that your old trusty MS Excel does not provide this functionality the same way the GoogleFinance() function does. Excel 2013 offers something much better, but not as flexible.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s