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 there is a dedicated GOOGLEFINANCE function in Google Sheets? With it, you can import data from Google Finance to spreadsheets and manipulate it in the way 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.
How to use GOOGLEFINANCE function in Sheets
GOOGLEFINANCE syntax
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 thestart-date
.
- Alternatively, the
-
"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.
GOOGLEFINANCE function 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)
Now let’s check out the power of GOOGLEFINANCE through other real-life examples.
GOOGLEFINANCE function tutorial
How to track stocks using GOOGLEFINANCE function
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 GOOGLEFINANCE formula should look:
=GOOGLEFINANCE("NASDAQ: FB","price")
Alternatively, it is possible to reference cells in your argument. The formula with our ticker symbol in cell B2 looks like this:
=GOOGLEFINANCE(B2, "price")
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:
=GOOGLEFINANCE("NYSE:E","high",DATE(2019,8,21),DATE(2019,8,30))
Use GOOGLEFINANCE function to work with currency
It is quite easy to access the current exchange rate in your sheets using the GOOGLEFINANCE function. For example, we need to import the rate of the currency pair PLN to CAD. Here is the GOOGLEFINANCE formula:
=GOOGLEFINANCE ("Currency:PLNCAD")
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)
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.
In this example, B2 cell contains the formula:
=GOOGLEFINANCE ("Currency:EURUSD")
D2 cell contains the formula, which multiplies the exchange rate returned to B2 and the value to convert from C2:
=B2*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 GOOGLEFINANCE formula is
=GOOGLEFINANCE("Currency:NZDINR", "price", DATE(2020,11,25), DATE(2020,12,2))
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. 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, https://finance.yahoo.com/quote/AAPL 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:
=IMPORTXML("https://finance.yahoo.com/quote/AAPL","//*[@id='quote-summary']/div[2]/table/tbody/tr[6]/td[2]")
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:
=split(IMPORTXML(concatenate("http://finance.yahoo.com/quote/",index(split(B3,":"),0,2)),$B$9),"()")
- B3 cell contains the ticker (
NASDAQ:AAPL
) split(B3,":")
splits the ticker intoNASDAQ
andAAPL
- 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!
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.
Why GOOGLEFINANCE function may not work
Did you experience an #N/A
error with the following message for your GOOGLEFINANCE formula:
When evaluating Google Finance, the query for symbol "****" returned no data.
We did, as well as many other Google Sheets users. It’s an internal error, which you unfortunately cannot fix yourself. The GOOGLEFINANCE function has some limitations, and it’s not always reliable. It’s good when you do not have many ticker symbols (up to 25), but there is no guarantee that it won’t bring you down at some point.
That’s why some financial traders or investors rely on financial services and use their APIs to import data to Google Sheets. How do they do this? Mostly coding, but there is a no-code solution called the JSON Client Importer. It’s an integration tool provided by Coupler.io, a product for importing data to Google Sheets from different sources like Airtable, Pipedrive, HubSpot and many others.
JSON Client importer lets you fetch JSON data from REST APIs and convert it into Google Sheets without any coding skills required. The main feature of this solution is that you can automate data import on a schedule! If you’re interested, check out more in the article Currency Tracker in Google Sheets.
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!
GOOGLEFINANCE function attributes
Attributes for real-time data | |
priceopen | The price at of market opening. |
high | The current day’s high price. |
low | The current day’s low price. |
volume | The current day’s trading volume. |
marketcap | The market capitalization of the stock. |
tradetime | The time of the last trade. |
datadelay | How delayed the real-time data is. |
volumeavg | The average daily trading volume. |
pe | The price/earnings ratio. |
eps | The earnings per share. |
high52 | The 52-week high price. |
low52 | The 52-week low price. |
change | The price change 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 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 | |
open | The opening price for the specified date(s). |
close | The closing price for the specified date(s). |
high | The high price for the specified date(s). |
low | The low price for the specified date(s). |
volume | The volume for the specified date(s). |
all | All of the above. |
Attributes for mutual fund data | |
closeyest | The previous day’s closing price. |
date | The date at which the net asset value was reported. |
returnytd | The year-to-date return. |
netassets | The net assets. |
change | The change in the most recently reported net asset value and the one immediately prior. |
changepct | The percentage change in the net asset value. |
yieldpct | The distribution yield, the sum of the prior 12 months’ income distributions (stock dividends). |
returnday | One-day total return. |
return1 | One-week total return. |
return4 | Four-week total return. |
return13 | 13 week total return. |
return52 | 52 week (annual) total return. |
return156 | 156 week (3 year) total return. |
return260 | 260 week (5 year) total return. |
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. |
morningstarrating | The Morningstar “star” rating. |
expenseratio | The fund’s expense ratio. |