In this article, you will learn how to use the GOOGLEFINANCE formula in Google Sheets.
The GOOGLEFINANCE function lets you show stock price data or currency exchange rates on your Google Sheets. The GOOGLEFINANCE is beneficial when you want to check the market information of your listed competitor or check the latest currency rates to be applied to your business operations (e.g., trade in a foreign currency).
The general syntax is as follows:
Ticker: This is an abbreviation tied to a publicly traded stock on a specific stock market, such as “NASDAQ:GOOG” for Google on NASDAQ. It is highly recommended that you input both exchange and individual stock symbols. These two symbols can be combined with a colon. When you want to check a currency exchange rate, this argument should look like “CURRENCY:USDGBP”.
Attribute [Optional]: This parameter defines what kind of data you pull out by this function. You can get current and historical data. For example, you can get the following information.
“Price”: The current (real-time) share price
“high52”: The highest share price over the last 52 weeks
“low52”: The lowest share price for the previous 52 weeks
“marketcap”: The market capitalization of the stock
“currency”: The currency in which the stock is priced
“close”: The closing price of the specific date(s)
Without any input, the default choice is “price”. To learn more about “attribute”, check this page.
Start_date [Optional]: The start date of a specific period for which you want to see historical information. If you enter this parameter and leave the following “end_date|num_days” argument blank, the formula returns data only on the starting date. Also, note that if this parameter is filled, the formula regards that your request is for historical information.
End_date|num_days [Optional]: The end date or the number of days of the historical period for which you want to refer to data.
Interval [Optional]: The frequency of the historical data. You can enter only 1 for daily or 7 for weekly.
Note 1: All arguments need to be enclosed in quotation marks or cell references to cells containing tex except for the “interval” and “end_date|num_days” arguments, each of which can be written as a number.
Note 2: When you try to pull out historical information, depending on the conditions you enter, the GOOGLEFINANCE formula returns the values in an array with column headers. Secure enough space for the formulas to spread the values.
Note 3: You can get symbols for stock exchanges, companies, and currencies on Google Finance website.
Now you have more clarity on what the GOOGLEFINANCE formulas are. See the following sections describing how to use the GOOGLEFINANCE function to get stock price information and currency data with examples.
Assume you want to know Google’s current stock information. For instance, you can create the table in the screenshot below by following the steps below.
See the formula in the D6 cell as an example. The syntax for the formula is as follows:
“ticker”: D$3&”:”&D$4 (NASDAQ:GOOG)
“attribute”: $C6 (marketcap)
“start_date”: None
“End_date|num_days”: None
“Interval”: None
(If you want to insert the formula with manual inputs, the one for the market capitalization should be =GOOGLEFINANCE("NASDAQ:GOOG","marketcap"), and you need to replace the “attribute” parameter with appropriate text for different items.
We left all optional arguments unfilled except for "attribute". We made them partial absolute references so that we can easily copy the formulas vertically (in other rows) for other attributes, such as stock price and currency, and horizontally (in different columns) for other companies later, if needed. We strongly recommend you use cell references when you create this sort of table or list because cell references allow you to expand the list efficiently and quickly. If necessary, check this article to learn the type of references: Relative Reference and Absolute Reference in Google Sheets: Explained and this post to learn combining text: How to Combine Texts in Google Sheets.
Next, on top of this current share information, assume you add historical stock price data over the last three months. We added the section named “Historical information” in the picture below.
See the formula in the D13 cell as an example. The syntax for the formula is as follows:
“ticker”: D$3&”:”&D$4 - same as the first example
“attribute”: $C13 (close)
“start_date”: $C$14 (9/2/2022)
“End_date|num_days”: $C$15 (12/2/2022)
“Interval”: $C$16 (7) - meaning weekly
As you can see, the closing prices of Google share spread across an array with corresponding date/time tags next to them.
Assume your company is headquartered in the US and has subsidiaries in other countries. You need to know currency exchange rates between USD and various currencies for countries where your subsidiaries locate. If you need only one exchange rate, inserting the GOOGLEFINANCE function with manual inputs may work. A sample formula is shown in the picture below.
What can you do when you need multiple exchange rates? Using cell references as many as possible is one of the solutions. The following screenshot shows you the table presenting exchange rates between USD and CAD, EUR, GBP, CNY, JPY, and INR.
See the formula in the D36 cell as an example. The syntax for the formula is as follows:
“ticker”: $C$33&$C$34&$C36
Other parameters: None
Once you create a proper formula with appropriate references for the exchange rate between USD and CAD, you can copy and paste the functions for the other currencies.
Lastly, look at the table showing the historical change in the rate between USD and EUR for the last fifteen days.
The syntax of the formula in the D44 is as follows:
“ticker”: $C$44&$C$45&$C46 (CURRENCY:USDEUR)
“attribute”: $C$47 (price)
“start_date”: $C$48-$C$49 (15 days prior to 12/4/2022)
“num_days”: $C$48 (15 days)
“Interval”: None
You can easily change the assumptions by generating a formula with cell references. So, try to use cell references instead of manual inputs, which might take a bit longer when building up a formula, but save time later for sure.
Check the following articles if you are interested in formatting text in cells.
How to Indent Texts in Google Sheets: Explained
How to Wrap Text in Cell in Google Sheets
How to Capitalize All Letters in Google Sheets
How to Make All Letters Lowercase in Google Sheets
How to Capitalize the First Letter in Google Sheets
How to Align Text in Google Sheets
Do you want to learn how to combine or split text in Google Sheets?
How to Combine Texts in Google Sheets
How to Use the CONCATENATE function in Google Sheets
How to Split Texts into Columns in Google Sheets
How to Use SPLIT Function in Google Sheets
Would you like to search texts for a string?
SEARCH Function in Google Sheets: Explained
FIND Function in Google Sheets: Explained
Why don’t you double-check the spelling of your texts before printing or closing Google Sheets?
How to Spell-check in Google Sheets
Automatically updated financial template
If you are interested in an automatically updated financial template that allows you to have up-to-date financial narratives, LiveFlow may benefit you. LiveFlow provides various types of financial templates containing critical financial and operational charts. You can be released from the multiple manual inputs and save time.
For example, we have Multi-Currency Consolidated P&L Template, as shown below.
To learn more about LiveFlow, book a demo.
Learn how to do this step-by-step in the video below 👇