GOOGLEFINANCE Function in Google Sheets: Explained

In this article, you will learn how to use the GOOGLEFINANCE formula in Google Sheets. 

‍

What can the GOOGLEFINANCE function do 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).

‍

How to insert the GOOGLEFINANCE formula in Google Sheets

1. Type “=GOOGLEFINANCE” or go to “Insert” → “Function” (or directly navigate to the “Functions” icon)  → “Google” → “GOOGLEFINANCE”.
2. Specify a string you want to look up and text where the formula searches the specified string. If necessary, input at which the search starts (e.g., starting the search from the third letter in the selected text).
3. Press the “Enter” key.

‍

The general syntax is as follows:

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

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.

‍

How to pull out stock price data in Google Sheets

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.

1. Get your target company’s exchange and stock symbols.
2. Decide and Input attributes of items you want to show on a worksheet.
3. Insert the GOOGLEFINANCE formula with appropriate cell references to show an item.
4. Copy and paste the GOOGLEFGINANCE formula to other cells to show the values of the other items.

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.

1. Decide which historical information you show on a worksheet
2. Insert the GOOGLEFINANCE formula and enter the “ticker” argument.
3. Input “start_date”, “end_date|num_days”, and “interval” parameters as needed.
4. The GOOGLEFINANCE formulas with proper inputs spread the results on the sheet.

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. 

‍

How to convert a currency to another currency in Google Sheets

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. 

1. Insert the GOOGLEFINANCE formula.
2. You need to fill in the “ticker” parameter with the text of “CURRENCY:XXXYYY”, where you should substitute XXX with a base currency symbol and YYY with a compared currency symbol. For example, if the U.S. dollar is a base currency and the euro is a compared currency, the “ticker” should look like “CURRENCY:USDEUR”.
3. Assuming you quote the latest exchange rate, you can leave the other parameters blank.
4. Note that you need to insert a colon between the text of CURRENCY and the text of XXXYYY and that you don’t need a colon between the two currencies. Also, don’t forget to enclose the entire text with quotation marks.

‍

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. 

1. Type the necessary texts such as CURRENCY: and price, currency symbols, and other assumptions (e.g., date) 
2. Insert the GOOGLEFINANCE formula and refer to the cells containing each parameter where applicable
3. The function spills the results across the cells. Adjust cells if the results don’t show up due to the limited space.

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.