The GOOGLEFINANCE function is an amazing tool used to make Google Sheets stock formulas for people who need to work with real-time finance data. This single function gives you not only the current prices of stocks and conversion rates of currencies, but also historical data of these securities over days, weeks, months or years.
In this tutorial, we will show you how to use the GoogleFinance function to work with stocks as well as financial currency.
Table of Contents
What Does the GOOGLEFINANCE Function do in Google Sheets?
The GOOGLEFINANCE function accepts a ticker symbol representing a particular security and returns the current or historical information relating to that security from Google Finance. You can use this function to fetch data about both stocks as well as currencies.
As such you can apply it to research investment opportunities, keep track of stock portfolio, convert between currencies, analyze and more. You can also use the fetched data to build visualizations, perform subsequent calculations and build dashboards.
A common use of this function is in computing the moving average for assets over a given time period. Similarly, you can use it to visualize trends in stock prices and make forecasts for the future in a Google finance spreadsheet.
Syntax for the GOOGLEFINANCE Function
The syntax to build a GOOGLEFINANCE formula goes as follows:
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date], [interval])
Here,
- ticker is the ticker symbol corresponding to the security that you want to fetch data for. It consists of initials that represent the security, along with the exchange symbol. For example, “NASDAQ:GOOG” represents Google stocks from the NASDAQ exchange. However, you could also simply use the symbol “GOOG” for this parameter. This is the only required Google finance syntax argument.
- The attribute parameter is a single string that specifies the data that you want to access relating to the given ticker. Some examples of attribute parameter strings include “price”, “high”, “low”, “volume”, etc. This is an optional parameter and its default value is “price”.
- start_date specifies the starting date from which you want your data fetched. This parameter is optional and you can use it when you want to fetch historical data relating to a ticker.
- end_date specifies the ending date up to which you want your data fetched. This parameter is also optional. Instead of a date, you can also specify the number of days from the start_date for which you want your data fetched.
- interval is also an optional parameter, and it is used when fetching historical data. This parameter specifies the intervals between dates fetched in your historical data. The parameter can be either “daily” or “weekly”. If you want the data in intervals of 2 days, 3 days, etc., you could specify a number between 1 and 7 for the interval parameter. Numbers other than these are not allowed.
GOOGLEFINACE Attributes
Google Finance in Google Sheets Standard Attributes
| “price” | The real-time price quote with up to 20 minutes delay. |
| “priceopen” | The opening price. |
| “high” | Current day’s high price. |
| “low” | Current day’s low price. |
| “volume” | Trading volume of the current day. |
| “marketcap” | Stock’s market capitalization. |
| “tradetime” | Time of the last trade. |
| “datadelay” | Shows the delay time of the real-time data. |
| “volumeavg” | This is the average daily trading volume. |
| “pe” | Price/earnings ratio. |
| “eps” | Earnings per share. |
| “high52” | The highest price over the past 52 weeks. |
| “low52” | The lowest price over the past 52 weeks. |
| “change” | The change in price since the end of the previous day’s trading. |
| “beta” | The beta value. |
| “changepct” | The percentage change in price since the end of the previous day’s trading. |
| “closeyest” | The closing price of the previous day. |
| “shares” | The number of outstanding shares. |
| “currency” | The currency in which the stock is priced in. |
Historical Data Attributes
| “open” | The opening price of a specific date. |
| “close” | The closing price of a specific date. |
| “high” | The high price of a specific date. |
| “low” | The low price of a specific date. |
| “volume” | The trading volume of a specific date. |
| “all” | Retrieves all the attributes above. |
GOOGLEFINANCE Attributes for Mutual Funds
| “closeyest” | The closing price of the previous day. |
| “date” | The net asset value’s reported date. |
| “returnytd” | The year-to-date return. |
| “netassets” | The net assets. |
| “change” | Change between the most recent and its previously reported net asset value. |
| “changepct” | The percentage change in the net asset value. |
| “yieldpct” | The distribution yield. |
| “returnday” | One-day total return. |
| “return1” | One-week total return. |
| “return4” | Four-week total return. |
| “return13” | Thirteen-week total return. |
| “return52” | Annual total return. |
| “return156” | 3-year total return. |
| “return260” | 5-year total return. |
| “incomedividend” | The most recent cash distribution amount. |
| “incomedividenddate” | The most recent cash distribution date. |
| “capitalgain” | The most recent capital gain distribution’s amount. |
| “morningstarrating” | The Morningstar “star” rating. |
| “expenseratio” | The fund’s expense ratio. |
How to Use the GOOGLEFINANCE Function
Now that we have explained the basic syntax of the GOOGLEFINANCE function, let us see how we can apply the function in different scenarios
Using the GOOGLEFINANCE Function to Work on Current Stocks Data
To use the GOOGLEFINANCE function. You just have to:
- Click on an empty cell
- Type =GOOGLEFINACE( into the cell
- Set the arguments you wish to use in the formula. You must at least include the ticker argument.
- Use a closing bracket and press enter
For example, let us apply the GOOGLEFINANCE functions to fetch the current price of a stock, say the Google stocks (from the NASDAQ exchange). The formula, in this case, will be:
=GOOGLEFINANCE(“NASDAQ:GOOG”)
Notice that the above formula has only the required parameter. We did not specify any of the optional parameters. This means the attribute parameter will have the default value of “price” and since all other parameters like start_date, end_date and interval are absent, the function understands that we only want the current price of the stock (no historical data).
Here’s the result we get:
Now let’s say you want to see other attributes relating to the stock, like the price, volume, highest and lowest rates for the current day. Then you have to mention the attribute you want in the second parameter, as shown in the screenshot below:
Here’s how we got the results for Price:
- Enter =GOOGLEFINANCE( into an empty cell E2 in this case
- Enter the ticker argument which is the cell reference $B$2″:”$B$3 in the example
- Enter a comma
- Enter attribute argument which is cell reference D2 in the example
So our total formula looks like this:
=GOOGLEFINANCE($B$2":"$B$3,D2)
Since we used absolute references for the ticker argument ($ symbols to indicate that reference doesn’t change when copying the formula), we can click and drag down the rest of the columns to make Google finance formulas with the fill handle.
Notice the first parameter in the formulae of the above screenshot. Since we are using two different cell references to compose the ticker parameter, we used a concatenation operator (&) to concatenate the reference containing the exchange (B2), followed by a colon (:), followed by the reference containing the security ticker symbol (B3).
In column D of the above screenshot, you can see some of the different attributes that can be used to fetch stock data for the current day used as cell references in the formula. But, you could also use the operator in the formula instead.
Using the GOOGLEFINANCE Sheets Function to Work on Historical Stocks Data
If you want historical data relating to a stock, you will need to mention the third and fourth parameters in the GOOGLEFINANCE function. Say you want the closing prices of 10 days starting from February 27, 2021. Your formula would then be:
=GOOGLEFINANCE(“NASDAQ:GOOG”,”close”,DATE(2021,2,27),10)
In the above formula, we specified the third parameter as the starting date, DATE(2021,2,27) and the fourth parameter as a number. So the GOOGLEFINANCE function knows that this is the number of days. If we specified a date value, GOOGLEFINANCE would take this as an ending date value rather than the number of days from the starting date.
When you press the return key, your result would look something like this:
Let’s use a more general-purpose version of the formula, so that you can customize it to your requirements:
Notice that we specified the attribute parameter as “all”, so the GOOGLEFINANCE function shows all 5 historical data attributes:
- Open
- High
- Low
- Close
- Volume
Moreover, since we specified the interval parameter as “daily”, the function returned daily data for 10 days starting from the given date (Feb 27, 2021), which makes 10 rows of data.
If instead of daily, you wanted to display the data in weekly intervals, you would get 2 rows of data, as shown below, since there are just 2 financial weeks in 10 days:
Using the GOOGLEFINANCE Function to Work with Currencies
The GOOGLEFINANCE function also lets you get current and historical conversion rates between a number of global currencies.
To get the conversion rate between two currencies, you need to specify this in the first parameter of the function as follows:
- The keyword “Currency”
- Followed by a colon (:)
- Followed by the ticker symbol of the currency you want to convert
- Followed by the ticker symbol of your target currency.
For example, the ticker symbol for US Dollars is USD while the ticker symbol for UK Pound is GBP (you can find these ticker symbols by searching on Google or by using a tool like Yahoo Finance).
To convert USD to GBP, your ticker symbol would be “Currency:USDGBP”. Note that the original currency should always come before the target currency.
The formula to get exchange rates to convert USD to GBP would then be:
=GOOGLEFINANCE(“Currency:USDGBP”)
If you want to use a more general-purpose version of the formula, you can customize it to your requirements as follows:
Notice we used two different cell references to compose the ticker parameter. We used a concatenation operator (&) to concatenate the keyword “Currency”, followed by a colon (:), followed by the reference containing the original currency symbol (B2) followed by the reference containing the target currency symbol (B3).
Now you have a formula that can fetch the conversion rate of any currency to any other currency.
If you want to use this formula to convert a value from one currency to another, or to build a simple currency converter, all you need to do is import the exchange rate and then multiply it with the value that you want to convert. An example of a currency converter is shown below:
Here, cell B5 contains the formula:
=B4*GOOGLEFINANCE("Currency:"&B2&B3)
This formula simply multiplies the original amount with the exchange rate of converting the currency in cell B2 to the currency in cell B3 (obtained by the GOOGLEFINANCE function).
You can replace the symbols in cells B2 and B3 with your required currency symbols.
Using the Google Sheets GOOGLEFINANCE Function to Import Historical Currency Exchange Rates
The GOOGLEFINANCE function can also help get historical exchange rates between two currencies over a period of time. This can give us information on how currencies have behaved in the past.
To get historical data about currency conversion rates, you will need to specify the second, third and fourth parameters too.
For example if you want to see historical exchange rates of converting US to GBP in the last 10 days, your formula would look like this:
=GOOGLEFINANCE(“Currency:USDGBP”,”price”,TODAY()-10,TODAY())
You can also generalize the formula as shown below:
You should now see two new columns returned by the function – dates and closing exchange rate for each day.
You could similarly see the data in weekly intervals, simply by adding the fifth parameter “weekly”:
Note: Currencies don’t have trading windows, so you will not get any data returned attributes open, low, high, and volume.
Google Fincance Functions Not Working?
The GOOGLEFINANCE function might sometimes not work and you might find it return an #N/A error. There is not much that you can do about this, as it is one of the limitations of the function.
In fact, this function is not meant for professional use. It is mainly targeted at casual Google Sheets users who want to simply track their portfolio stocks, do some simple market analysis, or convert between currencies.
In case you need market data for professional use (for example, if you are a financial industry professional or work for a government entity), you might need to purchase additional licensing from a third-party data provider.
Google Sheets Finance Functions FAQ
How Often Does the GOOGLEFINANCE Function Update in Google Sheets?
The GOOGLEFINANCE function updates approximately every 15 minutes.
Is the GOOGLEFINANCE Function Worth It?
It’s free tracking of your stock portfolio. Something that many people pay thousands of dollars a year for. While it may seem intimidating to use at first, once you know it, it’s a powerful tool.
Does GOOGLEFINANCE Have Crypto?
Yes you can pull data from the crypto market into Google Sheets with the GOOGLEFINANCE function. You just have to use the crpto pair name as the ticker argument. For example, for Bitcoin and USD you could use the following formula:
=GOOGLEFINANCE("BTCUSD")
Conclusion
In this tutorial, we showed you some examples of how to fetch current and historical market data using the GOOGLEFINANCE function. You can use the examples we used to build your own automated Google Finance Sheet. We tried to make this tutorial as detailed and clear as we could. We hope you find it useful.
Related Reading:
- How to Auto-Refresh Google Sheets Formulas (Updates Every 1-Minute)
- Free Google Sheets Ledger Template [and How to Use]
- 5 Useful Google Sheets Project Management Templates [Free]
- How to Convert Currency in Google Sheets (Google Finance Function)
- Google Sheets Notifications: How to Turn On, Edit & Remove
