How to Use the GOOGLEFINANCE Function in Google Sheets

The GOOGLEFINANCE function is a powerful function that can be used to get real-time financial and market-related data. With this function, you can quickly import the current prices of various stocks, currencies, and other securities into your spreadsheet.

When properly learned, this function can save you a ton of time importing financial market data. After you have your data in your spreadsheet, you can then analyze, manipulate, and create visualizations with your data as you see fit.

In this tutorial, I will show you how to use the GOOGLEFINANCE function in Google Sheets.

Table of Contents

GOOGLEFINANCE Syntax

The GOOGLEFINANCE function is used to fetch current or historical securities information from Google Finance.

The syntax of this function is:

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

ticker – this is the ticker symbol for the traded security to import. You have to use both the exchange symbol and ticker symbol for the most accurate results. Here are a couple of examples: “NASDAQ:AAPL”, “NASDAQ:MSFT”, “NYSE:GS”. If you do not specify an exchange symbol, Google Sheets will choose one for you.
attribute – this is an optional argument and will be set to “price” by default. This is the attribute about your chosen ticker that to import data for. There are a ton of attributes about your security that you can import
- For real-time data, you can select the following attributes:
  - price – the price of the security, this is real-time data but may have a delay up to 20 minutes
  - priceopen – the price at market open
  - high – the high price of the current day
  - low – the low price of the current day
  - volume – the trading volume of the current day
  - marketcap – market capitalization of the stock
  - tradetime – the time of the last trade
  - datadelay – this will tell you how delayed the real-time data is
  - volumeavg – the average daily trading volume
  - pe – price/earnings ratio
  - eps – earnings per share
  - high52 – the 52-week high price
  - low52 – the 52-week low price
  - change – the change in price since the previous day’s close
  - beta – beta value
  - changepct – percentage change since previous day’s close
  - closeyest – closing price on the previous day
  - shares – the number of outstanding shares
  - currency – the currency the security is priced in (open, low, high, and volume won’t return for this argument)
- For historical data, you can select these attributes:
  - open – opening price for the dates given
  - close – closing price for the dates given
  - high – high price for the dates given
  - low – low price for the dates given
  - volume – volume for the dates given
  - all – this returns all of the above options
- For mutual fund data, you can select these attributes:
  - closeyest – the closing price on the previous day
  - date – the date that the net asset value was reported
  - returnytd – year-to-date return of the mutual fund
  - netassests – the net assets
  - change – the change from the most recent net asset value and the previous one
  - changepct – percentage change in net asset value
  - yieldpct – the distribution yield, the sum of the previous 12 months’ income distributions, and net asset value gains divided by the previous month’s net asset value
  - returnday – the one-day total return
  - return1 – the one-week total return
  - return4 – the four-week total return
  - return52 – the thirteen-week total return
  - return156 – 156 week total return (3-years)
  - return260 260-week total return (5-years)
  - incomedividend – the most recent cash distribution amount
  - incomedividenddate – the most recent cash distribution date
  - capitalgain – the most recent capital gain distribution amount
  - morningstarrating – Morningstar star rating
  - expenseratio – the expense ratio
start_date – this is an optional argument. It is the start date to use when fetching historical data
end_date|num_days – this is an optional argument. It is the end date or number of days from the start date to import data from. If this is not specified, data will be returned for only 1 day (the start_date)
interval – this is an optional argument. It is the frequency of returned data. Options for this argument are:
- “DAILY” or “WEEKLY”
- 1 or 7

There are a few important notes that you should know about using this function:

The data is not meant to be used by professionals. Professional use might require licensing fees from a third-party data provider
Every argument in this function should be enclosed in quotation marks unless using a cell reference. The only argument that doesn’t need quotations is the interval argument if you are using numbers (1 or 7)
Real-time data returns in a single cell, whereas, historical data is returned in an array
Not every symbol will return all attributes
If date arguments are used, the function will only return historical data
There may be delays in the data up to 20 minutes and quotes are not sourced from all markets. This information is meant to be used for informational purposes and not meant to be used for trading or advice
Dates used in the function are used as noon UTC time. If an exchange closes before that time, results might be shifted a day

Using the GOOGLEFINANCE Function

Now that we have covered the syntax, let’s take a look at some examples to show how to use the function.

In the below sections I will cover several important ways that you can use the function. I will take a look at pulling stock data such as current prices, historical data, mutual fund data, and using the function to work with currency.

Getting Stock Prices with GOOGLEFINANCE

Pulling the stock prices with the GOOGLEFINANCE function is a very simple formula. There are two ways that you can do this.

You can manually enter the arguments into the function like in this example:

The formula used in the spreadsheet above is =GOOGLEFINANCE(“GOOG”,”price”). Each argument needs to be inside quotation marks.

The other option that you have is using cell references as your arguments like in this example:

Remember that if you want to pull data from a specific exchange, you need to place the exchange symbol before your ticker symbol.

Here are some examples of formulas using both exchange and ticker symbols:

=GOOGLEFINANCE(“NASDAQ:AAPL”,”price”)
=GOOGLEFINANCE(“NASDAQ:MSFT”,”price”)
=GOOGLEFINANCE(“NYSE:GS”,”price”)

Pulling all Attributes for a Stock

If you want to pull all of the available financial data for a stock, the quickest way to do this is to use cell references.

Here are the steps to do this:

1. First type every attribute in a different cell. You can either type them down a column or across a row depending on how you want to look at the data

2. Next type the symbol that you want to return data for somewhere in a cell

3. Now we can set up our formula. For the first argument of the function, place in the cell reference that contains your ticker symbol. After selecting the cell reference, press F4 on your keyboard to lock this cell reference in place (make sure the dollar signs are in your formula around the cell reference. For the 2nd argument of the function, place the cell reference that contains your first attribute. This is my exact formula: =GOOGLEFINANCE($B$1,A2). Press Enter on your keyboard and your first attribute should return

4. Copy the formula and paste it down your spreadsheet to any additional attributes that you want to return. After pasting the formula, you will see the data is returned

Using the Function to Import Historical Stock Data

Instead of just pulling current data, you can also fetch historical stock data with the function.

The options that you have for historical data are not the same as pulling current data.

For historical data you can use these as your attributes:

open
close
high
low
volume
all

Here is an example of how this formula is set up:

The formula in the above example is =GOOGLEFINANCE(“GOOG”,“open”,“12/01/2021”,“12/31/2021”). By default the function will return daily data, but you can also set it to return weekly data, by adding in the last argument.

Here I have changed the formula to include a 7 at the end: =GOOGLEFINANCE(“GOOG”,”open”,”12/01/2021″,”12/31/2021″,7). This will return weekly data.

If you want to return all data instead of a specific attribute, change the second argument of the function to “all”. Here is my new formula to pull all historical information: =GOOGLEFINANCE(“GOOG”,”all”,”12/01/2021″,”12/31/2021″,7)

Getting Mutual Fund Data with GOOGLEFINANCE

If the ticker symbol you are using in the function is a mutual fund, there is different data that you can import over other stocks.

Here are the attributes that are available for mutual funds

closeyest
date
returnytd
netassets
change
chancepct
yieldpct
returnday
return1
return4
return156
return260
incomedividend
incomedividenddate
capitalgain
morningstarrating
expenseratio

Other than that, the formula is set up the same way, by using the ticker symbol of the mutual fund, and then the attribute you want to return:

You will notice that in the above example one of the attributes resulted in an #N/A error. Not all symbols will return all attributes, so this might happen sometimes.

Using GOOGLEFINANCE to Get Currency Exchange Rates

You can also use this function to get the currency exchange rates.

The syntax to get the currency exchange rates would be:

=GOOGLEFINANCE(“Currency:symbol1symbol2”)

symbol1 – the three-letter currency code for the currency that you want to convert
symbol2 – three-letter currency code for the currency you want to convert to

Here is an example of using the function to get the exchange rate between USD and EUR

With currency, you can also import historical exchange rate data by adding a “price” parameter and adding start dates and end dates into the formula:

With currency, you have the same options as other securities to return daily or weekly data.

Is GOOGLEFINANCE Real-Time?

The GOOGLEFINANCE function has prices that are real-time but there may be delays in the data up to 20 minutes.

The official quote on the Google Support page for this function is “Real-time price quote, delayed by up to 20 minutes” for the pricing data.

Troubleshooting Function Not Working

Sometimes you may run into issues when using the GOOGLEFINANCE function and it may not work for you.

For example, you might get an #N/A error. The most common causes of this error with this function are:

Mistyped symbol
Mistyped syntax
The data is not available for the chosen symbol

The most common reasons for errors are either a typo in the symbol or the data not being available for the chosen symbol. If you are getting an #N/A error, check the symbols and check the syntax very carefully to make sure there is not an issue.

Some attributes are also not available for all symbols, so that could be the source of the error too.

Closing Thoughts

Learning to use this function is just the beginning. After you have mastered the function, you can begin to put together your own sheet with dashboards, charts, and other automated workflows to help you make analysis easier when looking at market data.

Remember though again that this function is not meant to be for professional use, but for informational purposes only.

If you are confused about any of the steps in this tutorial, make sure to watch the video!

More Google Sheets Tutorials:
How to Create a Candlestick Chart