Back to Blog

Advanced Tutorial on GOOGLEFINANCE Function in Google Sheets with Real-Life Examples

Most of you know and use Google Finance for researching investment opportunities and other finance-related things. This service lets you create your own stock portfolio and monitor it in real-time. You can also track information about securities, currency rates, and so on in one place. 

Did you know that Google Sheets has a dedicated Google Finance formula? It’s called simply GOOGLEFINANCE and it allows you to import data from Google Finance to spreadsheets. Then you can manipulate the data as you need, such as visualizing data, building dashboards, etc. The function is a bit intricate, so we prepared this tutorial for you to master GOOGLEFINANCE with no sweat.

GOOGLEFINANCE Google Sheets explained

GOOGLEFINANCE syntax in Google Sheets

According to the Google official documentation, here is the GOOGLEFINANCE formula syntax:

=GOOGLEFINANCE("ticker-symbol", "attribute", "start-date", "end-date", "interval") 
  • "ticker-symbol" is an abbreviated name for traded securities such as stocks, shares, currency, etc. For example, "NYSE:V" is the ticker symbol for Visa Inc. on the New York Stock Exchange; "USDEUR" is the ticker symbol for the exchange rate of the currency pair US dollar to Euro. 

All other parameters for the GOOGLEFINANCE function are optional:

  • "attribute" is the parameter to specify the type of data about "ticker-symbol" to import from Google Finance. For example, the "currency" attribute denotes the currency in which the security ("ticker-symbol") is priced. By default, GOOGLEFINANCE function returns the "price" attribute. Discover all GOOGLEFINANCE function attributes.
  • "start-date" and "end-date" represent a date span for collecting historical data. The date should be represented in the format MM/DD/YYYY, for example "12/24/2020"; or using one of the date-related functions, for example =DATE(yyyy,mm,dd). Skip these parameters to return real-time data.
    • Alternatively, the end-date parameter can be specified as the number of days from the start-date
  •  "interval" is the parameter to specify the granularity of the imported data. GOOGLEFINANCE function allows you to get daily data (use "Daily" or "1") or weekly data (use "Weekly" or "7"). Daily is set by default.

GOOGLE FINANCE formula examples

Take a look at this basic example of how GOOGLEFINANCE function works:

=GOOGLEFINANCE("GOOG","price","1/1/2014","12/31/2014", "DAILY")

In this formula example, we use GOOGLEFINANCE to import data about the stock price of Google back in 2014.

  • "GOOG" is the ticker symbol
  • "price" is the attribute
  • "1/1/2014" is the start date
  • "12/31/2014" is the end date
  • "DAILY" is the interval (we can omit it in the formula)

GOOGLEFINANCE attributes you can use in Google Sheets

Attributes for real-time data
priceopenThe price at of market opening.
highThe current day’s high price.
lowThe current day’s low price.
volumeThe current day’s trading volume.
marketcapThe market capitalization of the stock.
tradetimeThe time of the last trade.
datadelayHow delayed the real-time data is.
volumeavgThe average daily trading volume.
peThe price/earnings ratio.
epsThe earnings per share.
high52The 52-week high price.
low52The 52-week low price.
changeThe price change since the previous trading day’s close.
betaThe beta value.
changepctThe percentage change in price since the previous trading day’s close.
closeyestThe previous day’s closing price.
sharesThe number of outstanding shares.
currencyThe currency in which the security is priced. Currencies don’t have trading windows, so open, low, high, and volume won’t return a value for this argument.
Attributes for historical data
openThe opening price for the specified date(s).
closeThe closing price for the specified date(s).
highThe high price for the specified date(s).
lowThe low price for the specified date(s).
volumeThe volume for the specified date(s).
allAll of the above.
Attributes for mutual fund data
closeyestThe previous day’s closing price.
dateThe date at which the net asset value was reported.
returnytdThe year-to-date return.
netassetsThe net assets.
changeThe change in the most recently reported net asset value and the one immediately prior.
changepctThe percentage change in the net asset value.
yieldpctThe distribution yield, the sum of the prior 12 months’ income distributions (stock dividends).
returndayOne-day total return.
return1One-week total return.
return4Four-week total return.
return1313 week total return.
return5252 week (annual) total return.
return156156 week (3 year) total return.
return260260 week (5 year) total return.
incomedividendThe amount of the most recent cash distribution.
incomedividenddateThe date of the most recent cash distribution.
capitalgainThe amount of the most recent capital gain distribution.
morningstarratingThe Morningstar “star” rating.
expenseratioThe fund’s expense ratio.

Now let’s check out the power of GOOGLEFINANCE through other real-life examples.

Tutorial on how to use GOOGLEFINANCE function in Google Sheets

How to track stocks using the GOOGLE FINANCE formula

Stocks represent a part of the company’s ownership and are traded on a stock exchange such as NASDAQ or through brokers. People buy stocks for several purposes: trading, getting dividend income, etc. To assess your stock easily and observe its changes, GOOGLEFINANCE function takes your stock data directly from Google Finance to your Google Sheets. 

First, you will need to get the ticker symbols of the companies you are interested in to get your current stock value or price. You can either Google it (for example, “facebook ticker”) or use a tool such as Yahoo Finance. For example, you want to import the stock price or value of Facebook Inc. traded on NASDAQ. The ticker symbol for Facebook is "FB"; for NASDAQ, it will read "NASDAQ:FB"

Type "NASDAQ:FB" in the formula along with "price" as the attribute. Here is how your GOOGLE FINANCE formula should look:

How to track stocks using the GOOGLEFINANCE function

Alternatively, it is possible to reference cells in your argument. The formula with our ticker symbol in cell B2 looks like this:

How to track stocks using the GOOGLEFINANCE function (reference cells)

How to use GOOGLEFINANCE function to get historical data about stocks

You may need to acquire historical stock data to examine the company’s history, anticipate future events, etc. Let’s import historical stock data about Eni S.p.A., an Italian multinational oil and gas company. It’s ticker symbol is "NYSE:E"; "high" is the argument for the high price for the specified date span. Here is the GOOGLEFINANCE formula:


Use GOOGLEFINANCE function to work with currency 

It is quite easy to access the current exchange rate in your sheets using the GOOGLE FINANCE formula. For example, we need to import the rate of the currency pair PLN to CAD. Here is the GOOGLE FINANCE formula:

Use GOOGLEFINANCE function to work with currency

An alternate method of doing this is by using the cell of your argument. Here is the formula to get the USD to GBP currency rate:

=GOOGLEFINANCE ("Currency:"&B2&C2) 
GOOGLEFINANCE currency formula example

Get currency exchange value with GOOGLEFINANCE

Importing a pure currency rate is not as convenient as converting the value from one currency to another right away. With Google Sheets and GOOGLEFINANCE function, you can easily build a currency converter. All you need to do is import the exchange rate and multiply it by the value to convert.

Get currency exchange value with GOOGLEFINANCE

In this example, B2 cell contains the formula:


D2 cell contains the formula, which multiplies the exchange rate returned to B2 and the value to convert from C2:


Import historical currency exchange rates with GOOGLEFINANCE

Historical currency exchange rates give us information on how currencies have behaved in the past. For example, let’s import the historical exchange rate for the currency pair New Zealand Dollar to Indian Rupee (NZDINR). The GOOGLE FINANCE formula is

=GOOGLEFINANCE("Currency:NZDINR", "price", DATE(2020,11,25), DATE(2020,12,2))
Import historical currency exchange rates with GOOGLEFINANCE

How often does the GOOGLEFINANCE function update in Google Sheets?

Most of us use GOOGLEFINANCE for real-time data. So, we need to know how fast it can do the job.

GOOGLEFINANCE can have a delay of up to 20 minutes, and you will need to manually open your spreadsheet before an update can happen. The spreadsheet will fetch only the values saved the last time the spreadsheet was opened.

GOOGLE FINANCE not working – what are the reasons?

The GOOGLEFINANCE function has some limitations, and it’s not always reliable. There is no guarantee that too many GOOGLEFINANCE formulas won’t return an #N/A error at some point. The data you can get using the GOOGLEFINANCE function is not for financial industry professional use.

Another reason for the #N/A error is the attempt to retrieve historical data from Google Finance via the Sheets API or Apps Script. Why? Google Finance API is no longer supported and is not documented

How to avoid the GOOGLE FINANCE internal error?

You can’t fix the internal error of your GOOGLEFINANCE formula, but you can use an alternative way to get your financial data. Some financial traders or investors rely on financial services and use their APIs to import data to Google Sheets. Yahoo Finance and its API is the best replacement to Google Finance for both individual and enterprise-level users. Let’s see how you can do this.

How to retrieve data from Yahoo Finance API without coding

The instructions provided in this article are sufficient for creating an automated integration. But please note that we are not responsible for the outcome if you use the instructions incorrectly, specify incorrect settings, or if there are changes in the app’s API.

If you need help with the setup or want to have a more user-friendly version of this connector, we can develop this premium integration for you for free. Just select the app you need from the list and follow the instructions on requesting the automated integration.

Wait! Wasn’t Yahoo Finance API shut down in 2017? It was, but sometime in 2019, it’s unofficial API came out. With it, you can query:

  • Finance summaries
  • Stocks
  • Quotes
  • Movers
  • and more free stock market data. 

The best tool for getting data from APIs without coding is the JSON Client. It’s an integration tool provided by, a solution for automating data import to Google Sheets, Excel or BigQuery from different sources like Airtable, Pipedrive, HubSpot, etc. There are numerous ready-to-use integrations, such as HubSpot to Excel. In our case, we’ll need to set up a custom one by connecting to the API. Let’s see how it works:

Sign in to, click Add importer and select the source and destination apps: JSON and Google Sheets accordingly. After that, you’ll need to complete the following steps:

  • Source
  • Destination
  • Schedule


  • JSON URL – insert the JSON URL to import data from and click Continue.

JSON URL for major data entities:{ticker}

JSON URL for historical data about prices, splits and dividends:{ticker}
  • HTTP method – use GET as the HTTP method.
  • URL query string – depending on the data you want to retrieve, the query string will differ. For example, here is the string to retrieve price data:
modules: price

Check out all data query options.

Click Proceed to Destination Settings.


  • Destination account: connect your Google account. 
  • Spreadsheet: select your Google Sheets doc and the sheet in it that will be receiving the data. 

Click Proceed to Schedule Settings.


Toggle on Automatic data refresh and configure the schedule if you want to retrieve data automatically to Google Sheets. Click Save and Run to import your data.

Data entities and parameters to use for importing data from Yahoo Finance

Major data 

JSON URL: .../v10/finance/quoteSummary/{ticker}

Data entityURL query string
Pricemodules: price
Key statistical data including (YTD Daily Total Return, profit margins, float shares, etc.)modules: defaultKeyStatistics
Expense Ratiomodules: fundProfile
Profile Datamodules: assetProfile
Income statements historymodules: incomeStatementHistory
Quarterly income statements historymodules: incomeStatementHistoryQuarterly
Balance Sheet historymodules: balanceSheetHistory
Quarterly balance Sheet historymodules: balanceSheetHistoryQuarterly
Cash flow statement historymodules: cashflowStatementHistory
Quarterly cash flow statement historymodules: cashflowStatementHistoryQuarterly
Financial datamodules: financialData
Calendar eventsmodules: calendarEvents
Securities filings modules: secFilings
Recommendation trendmodules: recommendationTrend
Upgrade & downgrade historymodules: upgradeDowngradeHistory
Institution ownershipmodules: institutionOwnership
Fund ownershipmodules: fundOwnership
Major direct holdersmodules: majorDirectHolders
Major holders breakdownmodules: majorHoldersBreakdown
Insider transactionsmodules: insiderTransactions
Insider holdersmodules: insiderHolders
Net share purchase activitymodules: netSharePurchaseActivity
Earningsmodules: earnings
Earnings historymodules: earningsHistory
Earnings trendmodules: earningsTrend
Industry trendmodules: industryTrend
Index trendmodules: indexTrend
Sector trendmodules: sectorTrend

Historical data about prices, splits and dividends

JSON URL:.../v8/finance/chart/{ticker}

URL query string:

URL query string parameterDescription
symbol: {ticker}Provide the ticker symbol.
period1: {unix-timestamp}Start date in the Unix timestamp format. For example, 1617179455 is the unix timestamp for Mar 31 2021 11:30:55t.
period2: {unix-timestamp}End date in the Unix timestamp format.
interval: {interval}Specify the time interval, for example:
3mo denotes 3 months
1d denotes one day 
includePrePost: trueAdd data before and after the market.
events: div%7CsplitAdd dividends and splits.

Yahoo Finance API is not an undisputed alternative to Google Finance. You can also use to fetch data via other APIs, for example, Alpha Vantage or IEX. Check out how we managed to create a currency tracker in Google Sheets using it.

How to get dividend data and options from stocks using GOOGLEFINANCE function

GOOGLEFINANCE only provides basic information such as price. Unfortunately, you can’t get dividend stock data with this function… but Google Sheets has another ace in the hole called IMPORTXML. 

IMPORTXML function is a built-in function to import structured data from webpages. The supported data types include XML, HTML, CSV, TSV, as well as RSS and ATOM XML feeds. Read more in our blog post about XML to Google Sheets.

And where can we find the necessary structured data about dividends? The answer is Yahoo Finance – from here, you can pull the dividend and yield information using IMPORTXML.

The IMPORTXML function has two parameters: web-url and xpath-query. The web-url is the URL of the stock being searched for on Yahoo finance – for example, is the web-url for Apple Inc.

How to get Xpath query

To get the xpath-query, complete the following steps:

  • On Google Chrome, select the dividend and yield => right-click and choose “Inspect” from the dropdown menu.
  • Right-click on the highlighted code => select “Copy” => “Copy XPath”. XPath gives us this string:  //*[@id=”quote-summary”]/div[2]/table/tbody/tr[6]/td[2]  
  • Insert the XPath string into the IMPORTXML formula, so it will look like this:

You can split the dividend and yield into different columns using the SPLIT function in Google Sheets

In the example below, we nested a few functions into one advanced formula which imports the dividend and yield, and then split them into two different columns:

  • B3 cell contains the ticker (NASDAQ:AAPL)
  • split(B3,":") splits the ticker into NASDAQ and AAPL 
  • The INDEX function takes AAPL only.
  • $B$9 contains the XPath string
  • The CONCATENATE function adds the XPath string to the IMPORTXML formula

By doing this, we have pulled data from Yahoo Finance and split it into their columns successfully!

Is the GOOGLEFINANCE function worth it?

GOOGLEFINANCE is not an all-in-one solution, as you can see for yourself. However, it may significantly optimize your data manipulation scope. On the other hand, it’s not quite a reliable function and you should always have a backup plan, such as an API service connected to Google Sheets using the JSON Client importer. Anyway, the final decision is up to you. Good luck with your data! 

One last thing – here is the list of attributes that you can use for real time data, historical data, and mutual fund data. Enjoy!

  • Zakhar Yung

    A content manager at whose key responsibility is to ensure that the readers love our content on the blog. With 5 years of experience as a wordsmith in SaaS, I know how to make texts resonate with readers' queries✍🏼

Back to Blog

Comments are closed.

Focus on your business
goals while we take care of your data!