Tips & Tricks: GoogleFinance in Google spreadsheets

Would you like to easily keep track of stock market quotes and other data relevant to your stock portfolio? You can use the GoogleFinance function in Google spreadsheets to automatically pull up-to-date information into your spreadsheet. Using the GoogleFinance function in a spreadsheet allows you to create a one-stop shop for your stock data.

Using the GoogleFinance function
To use the GoogleFinance function, enter the formula into the desired spreadsheet cell: =GoogleFinance(“Symbol”, “Attribute”) where “symbol” represents the stock symbol of the company or mutual fund you’re looking for and “attribute” represents the type of market data that you want information about. If attribute is left blank, the stock price is populated into the cell by default.

For example, if I wanted to find out the 52-week price high for Procter & Gamble stock, I’d enter =GoogleFinance(“PG”, “high52”). Make sure to put quotation marks around both the symbol and the attribute in order for the formula to compute properly.

The 52-week price high for Procter & Gamble then shows in the cell in which the formula was entered.

I can also apply the formula to a larger list of symbols and attributes in order to get data for multiple different stock/mutual fund listings across various types of data. Instead of typing the formula out like before, I want to click the cell of the first symbol in my list, in this case it’s PG. Next, I want to reference the cell in which I name the attribute I’m looking up, in this case, price.

Freezing the appropriate row and column with a “$” allows me to drag the formula across to the “52-week high” and “52-week low” columns, as well as down the other rows to apply the formula to all of the other stocks. When I apply the formula to all of the other cells, the results will show.

Here are a few more examples of attributes you can access using the GoogleFinance function:

• price: market price of the stock.
• priceopen: the opening price of the stock for the current day.
• high: the highest price the stock traded for the current day.
• low: the lowest price the stock traded for the current day.
• volume: number of shares traded of this stock for the current day.
• marketcap: the market cap of the stock.
• tradetime: the last time the stock traded.
• datadelay: the delay in the data presented for this stock using the googleFinance() function.
• volumeavg: the average volume for this stock.
• pe: the Price-to-Earnings ratio for this stock.
• eps: the earnings-per-share for this stock.
• high52: the 52-week high for this stock.
• low52: the 52-week low for this stock.
• change: the change in the price of this stock since yesterday's market close.
• beta: the beta value of this stock.
• changepct: the percentage change in the price of this stock since yesterday's close.
• closeyest: yesterday's closing price of this stock.
• shares: the number of shares outstanding of this stock.
• currency: the currency in which this stock is traded.

Using GoogleFinance to show historical data
The GoogleFinance function also allows you to easily show historical stock 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.

Give it a try and let us know what you think.

Posted by: Julia Harter, Consumer Operations Associate

Note:
This feature currently focuses on U.S. stock exchanges - we're working to add more international data, but for now, stock symbols default to U.S. exchanges. International results may not be supported, and results are only in English. Data may be delayed by up to 20 minutes, and we can't guarantee its accuracy - please see our Stock Quotes Disclaimer.