GOOGLEFINANCE Function – Google Sheets

The GOOGLEFINANCE function lets you retrieve financial information from Google Finance. Data is available for publicly traded stocks and mutual funds. We’ll review the various options for this function and look at several examples.

This function uses third-party data, and the current data is delayed by up to twenty minutes.

Syntax

=GOOGLEFINANCE(ticker symbol, [attribute], [start_date], [end_date|num_days], [interval])

• ticker symbol
  • The ticker symbol for the security.
  • For the most accurate results, prepend the exchange name to the ticker symbol. For example, “NYSE:GE” is for General Electric (GE) which is traded on the New York Stock Exchange (NYSE).
• attribute
  • [Optional] – Will default to "price" if not specified.
  • For real-time data
    • "price" – Current market price delayed up to 20 minutes.
    • "priceopen" – The opening price for the day
    • "high" – The day’s high price
    • "low" – The day’s low price
    • "volume" – The day’s trading volume
    • "marketcap" – Market capitalization
    • "tradetime" – The time of the last trade
    • "datadelay" – Amount of delay in the real-time data
    • "volumeavg" – The average daily trading volume
    • "pe" – The price/earnings ratio
    • "eps" – The earnings per share
    • "high52" – The highest price in the last 52 weeks
    • "low52" – The lowest price in the last 52 weeks
    • "change" – The change in price since the previous trading day’s close
    • "beta" – The beta value
    • "changepct" – The percentage change in price since the previous trading day’s close
    • "closeyest" – The previous day’s closing price
    • "shares" – The number of outstanding shares
    • "currency" – The currency of the security
  • For historical data
    • "open" – The opening price on the specified date(s)
    • "close" – The closing price on the specified date(s)
    • "high" – The high price on the specified date(s)
    • "low" – The low price on the specified date(s)
    • "volume" – The trading volume on the specified date(s)
    • "all" – All of the above attributes for historical data
  • For mutual funds
    • "closeyest" – The closing price for the previous day
    • "date" – The date on which the net asset value was reported
    • "returnytd" – The year-to-date return
    • "netassets" – The net assets
    • "change" – The change from the most recent net asset value and the one immediately prior
    • "changepct" – The percentage change in the net asset value
    • "yieldpct" – The distribution yield, the total of the last 12 months’ income distributions (stock dividends and fixed income interest payments), and net asset value gains divided by the previous month’s net asset value
    • "returnday" – The total return for one day
    • "return1" – The total return for one week
    • "return4" – The total return for four weeks
    • "return13" – The total return for thirteen weeks
    • "return52" – The total return for fifty-two weeks (one year)
    • "return156" – The total return for 156 weeks (3 years)
    • "return260" – The total return for 260 weeks (5 years)
    • "incomedividend" – The amount of the most recent cash distribution
    • "incomedividenddate" – The date of the most recent cash distribution
    • "capitalgain" – The amount of the most recent capital gain distribution
    • "expenseratio" – The expense ratio
    • "morningstarrating" – The Morningstar rating
• start_date – [OPTIONAL] – The start date for historical data. Not used for current data.
• end_date|num_days – [OPTIONAL] – The end date for historical data or the number of days from start_date to return data. If the argument is omitted, only one day will be returned.
• interval – [OPTIONAL] – The frequency of the returned data. Must be either "daily" or "weekly".
  • Can also be specified as 1 or 7.

Examples

Example 1 – Current Stock Price

First, we’ll look up a real-time stock price for General Electric.

Formula used: =GOOGLEFINANCE("NYSE:GE")

This simple formula is all you need to check a current stock price. To be the most accurate, you should prepend the ticker symbol with the stock exchange as we did here – NYSE:GE. The letters NYSE stand for New York Stock Exchange, the stock exchange where GE (General Electric) is traded. The default attribute is price; therefore, it does not need to be specified.

Example 2 – Stock Closing Price in Weekly Intervals

Now we’ll use the GOOGLEFINANCE function to retrieve historical data about a security. We’ll use General Electric again, but we’ll add to the function’s syntax.

Formula used: =GOOGLEFINANCE("NYSE:GE","close","12/31/2021",14,"weekly")

The function retrieves the closing price ("close") on 12/31/2021 and the weekly price for the 14 days after that. Therefore, it returns prices for three days. Notice the function is in cell A1 but its output spills into A1:B4.

Example 3 – Mutual Fund Data

You can also retrieve financial information on mutual funds. Let’s look at the return for the last five years of Vanguard 500 Index Fund Admiral Shares.

.

Formula used: =GOOGLEFINANCE("MUTF:VFIAX","return260")

In this example, we use “return260” for the attribute. The retrieves the 260-week (five-year) return for the Vanguard 500 Index Fund Admiral Shares. Prepend the ticker symbol with “MUTF” to let the function know you are retrieving a mutual fund. Since the ticker symbol is VFIAX, the combination results in “MUTF:VFIAX“. Specifying the mutual fund this way eliminates any ambiguity with the symbol.

Example 4 – Trimming Historical Stock Data

As seen in example 2, the GOOGLEFINANCE function returns historical stock data as an array. This means the output consists of more than one cell. Let’s use a technique to show only one cell of historical data using the INDEX function. We’ll start with one closing price.

Now we’ll add the INDEX function.

Formula used: =INDEX(GOOGLEFINANCE("NYSE:GE","close","12/31/2021"),2,2)

We gave the INDEX function the arguments of 2 and 2, which tells it to retrieve the value from the 2nd row of the 2nd column from the GOOGLEFINANCE output.

Live Examples in Google Sheets

Make a copy of this template to have these examples in your Google Drive.

Video Tutorial

Conclusion

The GOOGLEFINANCE function in Google Sheets is a powerful tool that allows users to import real-time financial and currency market data straight into their spreadsheets. This data can be used to track current stocks and shares information, retrieve historical securities data, and perform financial analysis.