We’ve seen a lot with Google Sheets so far, and we’ve learned a lot of formulae and functions. The Google Finance Function is a very sophisticated and modern function that retrieves data from the server and generates precise results for your Google Sheets file.
The Google Finance function is beneficial. It allows you to instantly swap currencies, import data in seconds, view stock market data on a spreadsheet, and even anticipate future prices for various markets, including equities and cryptocurrency.
This feature is quite strong and makes it simple to generate and evaluate real-time results as well as data fluctuations. If you work in finance, you can use it to track trends regarding market insights and make wise financial decisions.
This Google Finance function assists non-professional users of Google Sheets in understanding currency exchanges, monitoring market data, and interpreting and analyzing trends using daily up and down data. Let’s study its operation and our use of this function.
Uses of Google Finance Function in Google Sheets
There are many reasons why we should become familiar with the Google Finance function. It is a very powerful tool that we use for many different purposes. Some of these include importing real-time currency data into Google Sheets, converting between all available currencies, and importing the price of any currency from one set of data to another. We can observe the stock values from a certain date to a specific date, we can study trends of data coming in USD, Bitcoin, the Stock market, and any of the sub-categories.
We can bring in a lot of data by using a custom formula inside the function, and the data is automatically updated when real-time updates occur. This is the Google Finance Function’s power. We will go over everything in depth, including all of the features and the distinctions between required and optional ones. You can see why we must learn How to use Google Finance Function in Google Sheets.
How to use Google Finance Function in Google Sheets
Learning the Google Finance Function step-by-step begins with getting used to the function’s fundamental characteristics, as well as its required and optional attributes and the roles they play in the formula. Naturally, there will be numerous use cases to ensure that you fully understand it and can utilize it independently. Let’s move on to practical learning and bring some fantastic data from Google Finance utilizing the Google Finance Function.
You may also like>>> How To Use IRR Function In Google Sheets [Complete Guide]
How to use Google Finance Function – Real-time Currency Conversion
This section will teach you how to use Google Finance Function for real-time currency conversion. Normally, we look up currency exchange rates online by searching teams like USD to CAD. However, what if you had access to Google Sheets, which automatically updates with exchange rate data for as many currencies as you’d like? Isn’t it thrilling? In my opinion, it is. We will thus discover how simple it is to do this using your Google Sheet file. To practically study with me, follow the steps below.
Step 1>
Open the new Google Sheets file.
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-1.png)
Step 2>
Write the Google Finance Function in any cell.
=GOOGLEFINANCE()
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-2.png)
Step 3>
Enter “Currency” as the attribute value now. Tipper is the value that tells Google Sheets exactly what you want to do.
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-3.png)
Step 4>
Following “Currency,” place a colon and the two currency names—USDCAD, for example—without a space between them.
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-4a.png)
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-4b.png)
Step 5>
Close the double quotations now, and the function inside the parentheses will be
=GOOGLEFINANCE(“Currency:USDCAD”)
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-5.png)
Step 6>
Press Enter to see the value of one USD relative to one CAD.
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-6.png)
This is how easily we can convert currency in google sheets, with an auto-update option every 20 minutes.
Similarly, you can utilize the Google Finance Function’s cell characteristics to convert a list of currency codes directly.
Step 1>
Put a few currency codes in the Google Sheets document.
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-11.png)
Step 2>
Write the Google Finance Formula in the cell of the adjacent column.
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-12.png)
Step 3>
With the Google Finance function, you can now enter cell addresses rather than currency codes by using an ampersand (&).
This is how the syntax will show up.
=GOOGLEFINANCE(“Currency:”&A2&B2)
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-13a.png)
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-13b.png)
Step 4>
Drag the function down to cover the whole column.
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-14a.png)
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-14b.png)
You showed how simple it was to convert from many to many currencies by dragging across the full column and using a one-time function.
However, there are more things you can accomplish with Google Finance Function.
We are going to learn something new in the next step.
How to use Google Finance Function to Get back Market Information and Data
This part will teach you how to use the Google Finance Function to get market data and information. You’ll learn how to get real-time market data using a variety of methods, and you’ll be able to get data for today, the last week, and even the expected data for the next week. This enables us to see the general direction of the market and ultimately make wise financial decisions for both personal and business use.
Identifying the Google Finance Function’s General Syntax
=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
Ticker: The ticker is the first parameter that must be used in order to prevent inconsistencies. For example, substituting “NASDAQ:GOOG” for “GOOG.” alone. Both function in the same way, but “NASDAQ:GOOG” is suggested for superior results.
Attribute: The attribute is an optional argument that, by default, is its price. It is the precise property to retrieve about the given ticker; otherwise, Google Finance will pick the ticker that best suits your needs automatically, which may result in differences.
The following attributes are available for usage in the Google Finance function:
price | Real-time price quote, delayed by up to 20 minutes. |
priceopen | The price as of market open. |
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 far delayed is 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. |
curreny | The currency in which the security is priced. Currencies don’t have trading windows, so open, low, high, and volume won’t return for this argument. |
name | pulls the listed company name in Google Finance for the given ticker |
We’ll see how to get the data for several stocks in this example.
Step 1>
Construct the Google Finance Function.
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-21.png)
Step 2>
To obtain the price, pass the “Exchange:Symbol” through.
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-22.png)
Step 3>
When you press the Enter key, the price for a certain company in a particular exchange appears.
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-23a.png)
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-23b.png)
Step 4>
Aside from price, you can use other characteristics.
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-24a-2.png)
You can use any attribute in place of the price (default attribute) in this way.
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-24b-2.png)
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-24c-2.png)
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-24d.png)
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-24e.png)
How to use Google Finance Function to Retrieve Historical Market Data
The following attributes are available for usage in the Google Finance function:
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 |
We will see how to get historical market data from a given date to a specific date in Google Sheets using the Google Finance Function, which we will learn how to use in this section. I’ll give you an actual example of how it’s done for this scenario.
For this example, the Google Finance syntax will be
=GOOGLEFINANCE(“NASDAQ:GOOG”,”close”,DATE(2021,2,27),5)
NASDAQ: is the exchange
GOOG: is the symbol
CLOSE: is an attribute
DATE(): from the date
5: number of days after the specified date
We will extract the value of Bitcoin against USD over the last seven days using a similar formula.
Step 1>
Construct the Google Finance Function.
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-31.png)
Step 2>
Put the Bitcoin to USD symbol in double quotations, “BTCUSD.“
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-32.png)
Step 3>
Put a comma there and pass the property (I used “Closed” in this example) in double quotes.
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-33.png)
Step 4>
To pass the Date (yy,mm,dd), add a comma. (Date (2023, 07, 10) I passed
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-34.png)
Step 5>
The number of days should be written with a comma (I’ll use 8 as my date is 9 days behind the current date).
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-35.png)
Step 6>
This is how the function will look.
=GOOGLEFINANCE(“BTCUSD”,”CLOSE”,DATE(2023,7,10),20)
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-36a.png)
Hit the Enter key to finish.
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-36b.png)
You can also do this with other currencies, such as USD to CAD, etc.
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-36c.png)
As with currencies, you can additionally use company names like “New Market Corporation” (NEU) and exchange symbols like “New York Stock Exchange” in addition to merely using currency symbols.
![How to Use Google Finance Function in Google Sheets How to Use Google Finance Function in Google Sheets](https://officechaser.com/wp-content/uploads/2024/01/How-to-Use-Google-Finance-Function-in-Google-Sheets-36d.png)
You may also like>>> How To Calculate Percentage In Google Sheets
Important Notes
- When working on the historical data sheet, you can make use of the “all” attribute.
- Each attribute will yield all results for each attribute in a separate column. Open, close, high, low, and volume are a few of them.
- Two dates can also be entered for the function’s beginning and conclusion.
- Correct spelling of the symbol is necessary for reliable results.
- It’s common for Google Finance to occasionally return an error rather than results and give you the #NA error.
- Read the error that surfaced on the function with the red line beneath the function to see if there are any more errors.
- Every 20 minutes, the Google Finance tool automatically refreshes all of the data.
You may also like>>> How To Use Rank In Google Sheets [Best Guide]
Conclusion
Today, we learned how to use Google Finance Function in Google Sheets, which is a terrific tool. We also learned how to use it in various scenarios, starting from the very beginning and ending with the retrieval of real-time data for stocks, currencies, and other financial instruments. I sincerely hope you enjoy this essay; if you have any questions, please comment below. I’ll see you shortly with yet another fantastic Tutorial. I’m grateful. Keep studying with Office Chaser and remember to hit the subscribe button.