Google Sheets is a powerful tool to analyze and organize massive datasets. It offers seamless integration with Google and a few more external data sources. You can fetch current and historical financial data from Google Finance to Google Sheets with a few simple clicks.
The GOOGLEFINANCE is a beneficial function for people who deal with real-time currency and stock information.
It fetches data from Google Finance, which is an excellent web application that contains information about daily stock prices, currency conversion rates, and financial trends.
If you wish to take your share market and financial analysis skills to the next level using Google Sheets, then welcome to this blog.
This is a quick guide about Google Sheets’s GOOGLEFINANCE function. Let us discuss the syntax and steps to use the GOOGLEFINANCE function for currency conversion and obtaining real-time stock prices.
What is the GOOGLEFINANCE function in Google Sheets?
As an analyst, your job may include visiting a range of financial market websites and gathering information about current stock prices and currency trends in a spreadsheet.
It is a lengthy process that sounds tedious without the proper tools at your disposal.
The GOOGLEFINANCE is a function in Google Sheets that aims to simplify your life by importing financial market data into Google Sheets and letting you quickly build insightful financial models.
Why use the GOOGLEFINANCE function?
The function offers an effective way to track stock prices, currency conversion rates, and financial market trends. It can import current as well as historical financial market data with a few simple clicks.
Here are a few of the benefits of using the GOOGLEFINANCE function:
- Find investment opportunities
- Create an accurate forecast of future stock trends
- Organize your financial portfolio
- Currency conversion
- Stay up to date with current financial market trends
It is a dedicated Google Finance formula that offers lots of benefits.
The beauty of this function is that users don’t need any expert coding knowledge. Anyone from beginners to advanced Google Sheets users can use this function to fetch the data from Google Finance.
Moreover, Google Sheets is famous as one of the best tools to build and monitor a stock portfolio. In other words, building dashboards and visualizing financial market data is a breeze in Google Sheets.
Explained: The GOOGLEFINANCE Function Syntax
If you are familiar with Microsoft Excel or Google Sheets functions, then it will be easy for you to understand and use the GOOGLEFINANCE formula.
Here’s the general syntax of the function,
=GOOGLEFINANCE(ticker, [attribute], [start_date], [num_days|end_date], [interval])Now, let’s understand how to replace each argument with proper information.
1. “ticker”
A symbol created using a unique combination of letters and numbers to represent a specific stock or security is known as the Ticker in trading.
Simply put, you can use this symbol to identify stocks and securities. Refer to the following list of top active stocks of 2023.
- TSLA – Tesla Inc
- AMD – Advanced Micro Devices Inc
- NVDA – NVIDIA Corp
- ORCL – Oracle Corp
- MSFT – Microsoft Corp
- GOOGL – Alphabet Inc Class A
- AAPL – Apple Inc
- AMZN – Amazon.com Inc
To fetch the details of any stock, you must know the ticker as discussed above.
Note that you can also specify an exchange where the respective stock is traded. Consider the following example where I have defined a ticker for Tesla Inc. on the NASDAQ exchange.
NASDAQ:TSLA
To know the ticker symbol for stocks and exchanges, visit Google Finance’s official website and use the search bar.
Apart from stock information, users can also know the currency conversion rates using the ticker symbol, such as “USDEUR“.
2. “attribute”
It is an optional argument that fetches stock prices by default.
You can specify the type of information the GOOGLEFINANCE function is supposed to fetch using this argument.
Here are a few of the example inputs that can be used in the place of this argument,
- priceopen
- high
- low
- volume
- marketcap
- tradetime
- datadelay
- volumeavg
- high52
- low52
For a complete list of attributes that can be used with the GOOGLEFINANCE function, click here to visit the official page.
3. “start_date”
It is also an optional argument that is used while fetching the historical stock data using the GOOGLEFINANCE function. You can specify the start date from where the historical data needs to be fetched.
If you do not provide any input, it simply fetches the real-time stock information.
Make sure to use the format “mm-dd-yyyy” while replacing this argument with the date.
4. “num_days|end_date”
Though this is an optional argument, it becomes compulsory after defining the start date to fetch the historical data.
You can specify the time frame to fetch the stock information.
Similar to the “start_date” argument, you must ensure that you follow the “mm-dd-yyyy” format while specifying the end date using this argument.
5. “interval”
It is the final argument that lets you specify the frequency. In other words, it allows users to fetch the data daily, weekly, and so on.
It is an optional argument.
That’s all about GOOGLEFINANCE function syntax. Make sure to put the inputs such as ticker symbol, attributes, and start and end date within double quotation marks to avoid the error.
Except for the “ticker” argument, all other arguments of this function are optional. Simply put, if you only specify the ticker symbol, the function is going to return the stock price as per the current date.
How to use the GOOGLEFINANCE function
Using the GOOGLEFINANCE function is pretty straightforward; you don’t need expert knowledge.
But make sure to understand each argument discussed above.
In this section, let us learn the steps to use the GOOGLEFINANCE function to fetch current and historical stock information and currency conversion rates.
#1 – Getting Current Stock Information using the GOOGLEFINANCE function
The GOOGLEFINANCE function is mainly used to fetch stock information in real time.
Let us start with a simple example; then, we will see the steps to create a table to fetch information such as High, Low, Volumeavg, etc.
First, we will learn to fetch the real-time stock price using the following steps:
- Select the desired cell
- Type “=googlefinance”
- Choose the first option from the popup or press the “Tab” key
- Replace the “ticker” argument with the “TSLA” and make sure to use the double quotation marks
(Note that the TSLA is the ticker symbol for Tesla Inc.) - Press “,” to move to the next argument
- Now, type “price” in the place of the “attribute” argument
- Complete the parentheses using “)“
- Press “Enter“
Our final formula should look like the following,
=GOOGLEFINANCE("TSLA"," price")- “TSLA” is the ticker symbol
- “price” is the attribute
Google Sheets will instantly display today’s Tesla Inc. stock price.
Fetching prices is pretty straightforward. You can do it without specifying any values in the place of the “attribute” argument.
Now, let us create a blank table as shown below. Click here to make a copy of the Google Sheet, which contains a table similar to the one discussed below.
Our task is to fetch multiple stock attribute values for Tesla Inc. using the GOOGLEFINANCE function.
Let’s begin,
- Click on the cell “C5“
- Type “=googlefinance”
- Select the first option from the popup or press the “Tab” key
- Replace the ticker argument with the cell reference “C3
- Next, press “F4” key on your keyboard
(You will see the dollar symbols will appear after the letter B and number 2, as shown in the above image) - Press “,” to move to the following argument
- Now, for fetching the price, instead of manually entering the keyword, let us provide the cell reference “B5“
(The cell B5 contains the keyword “Price” and follows the attribute format as defined by the guidelines of the GOOGLEFINANCE function) - Complete the bracket using “)“
- Press “Enter“
Here’s how the final should look,
=GOOGLEFINANCE($C$3,B5)To fetch the rest of the argument values, follow the steps below,
- Click on the cell “C5“
- Hover to the lower-rightmost corner of the cell until you see the Fill Handle
- Click on the Fill Handle and frag the formula to the end of the table
Google Sheets will take some time, depending on your internet connection. You will see the status as “Loading…” as shown in the above GIF.
Next time, to fetch the stock information of any company, simply enter the ticker in cell C3. The Google Sheets will automatically fetch the data using your inputs.
In case you wish to verify if the results are accurate, visit the official Google Finance website and type in the ticker in the search bar.
Here’s the screenshot of Tesla Inc. that was clicked right after we created the table.
The stock price is similar to what we obtained in the table.
#2 – Getting Historical Stock Information using the GOOGLEFINANCE function (Specific Date)
In the previous section, we learned the steps to get real-time stock information such as price, low, high, etc.
Now, let us use the GOOGLEFINANCE function to get the historical stock information.
We will first learn to fetch the data for a specific date and then the date range, so stay tuned.
Let’s assume that we need to get the stock information for Tesla Inc. on 1st January 2023.
- Select any cell
- Type “=googlefinance”
- Choose the first option from the popup or press the “Tab” key
- Replace the ticker argument with the “TSLA“
- Press “,” to move to the next argument
- Type “price” in the place of the attribute argument
- Press “,“
- Now, for the start_date argument, we need to put the date in mm-dd-yyyy format. So, for 1st January 2023, let us type “1/1/2023“
(Make sure to use the double quotation marks as shown in the above image; otherwise, the function will return an error) - Complete the parentheses using “)“
- Press “Enter“
Our final formula should look as follows,
=GOOGLEFINANCE(“TSLA”,"price","1/1/2023")- “TSLA” is the ticker symbol
- “price” is the attribute
- “1/1/2023” is the start date
After pressing the Enter key, you will notice that the function returns the date and closing price in two separate columns. Refer to the above GIF.
You can obtain multiple stock attributes at once by using the keyword “ALL” in place of the attribute argument. Consider the following formula.
=GOOGLEFINANCE(“TSLA”,"all","6/3/2022")Now, as you can see in the following image, the function has returned the price at opening and closing, along with the high and low on the specific date.
Precaution: The GOOGLEFINANCE is an array formula that populates results across multiple columns and rows. You must ensure that the respective columns and rows are empty; otherwise, the function will return an error. Refer to the following image.
In case you wish the GOOGLEFINANCE function to display price only, make sure to combine it with the INDEX function.
Consider the same example. Our formula will become,
=INDEX(GOOGLEFINANCE($B$2,"price","6/3/2022"),2,2)Here’s the output using the above formula.
Instead of populating the results across multiple columns and rows, the combination of the GOOGLEFINANCE and INDEX function displays the results in the selected cell only.
#3 – Getting Historical Stock Information using the GOOGLEFINANCE function (Over The Given Date Range)
Let’s get the stock price of Tesla Inc. in 2023. We will fetch weekly details.
The formula remains the same as discussed in the previous section, except that we need to use the end_date argument to specify the date range and interval argument to get the weekly data.
Here are the steps:
- Select the desired cell
- Type “=googlefinance”
- Choose the first option from the popup or press the “Tab” key
- Now, replace the ticker argument with the “TSLA“
- Press “,” to move to the next argument
- To fetch the price, let us replace the attribute argument with the keyword “PRICE“
- Press “,“
- Next, type “1/1/2023” as the start date for the year 2023
(Make sure to follow the mm-dd-yyyy date format and use the double quotation marks) - Press “,” to move to the end_date argument
- Type “12/31/2023” as the end date for the year 2023
- Press “,“
- To obtain the weekly data, you need to replace the interval argument with the keyword “Weekly“
- Complete the parentheses using “)“
- Press “Enter” key
So, the final formula will be as follows,
=GOOGLEFINANCE("TSLA","PRICE","1/1/2023","12/31/2023","Weekly")- “TSLA” is the ticker symbol
- “price” is the attribute
- “1/1/2023” is the start date
- “12/31/2023” is the end date
- “Weekly” is the interval
Google Sheets will instantly create two columns: Date and Close. Refer to the above GIF.
It is pretty simple to obtain historical stock market data for any company using Google Sheets. Moreover, you can create comprehensive reports using built-in graphs and charts.
As discussed earlier, to get additional information, such as high and low, along with the closing and opening stock prices, you need to replace the attribute argument with the “all” keyword.
Here’s how the formula should look:
=GOOGLEFINANCE("TSLA", "ALL", "1/1/2023", "12/31/2023", "Weekly")Google Sheets will create a massive table with multiple columns as shown in the following image.
Make sure to use a blank spreadsheet with entries; otherwise, the formula will return a #REF! Error.
#4 – Getting Currency Conversion Rate using the GOOGLEFINANCE function
The GOOGLEFINANCE function can also be used to get the currency conversion rates apart from the stock information.
Each currency is identified by a three-letter code. For example, the USD represents US dollars.
Let us find the conversion rate for United States Dollars and Euros.
- Select the desired cell
- Type “=googlefinance”
- Choose the first option from the popup or press the “Tab” key
- Replace the ticker argument with the “USDEUR“
(Here, the code USD stands for United States Dollar and EUR for Euros) - Now, close the bracket using the “)“
- Press “Enter” on your keyboard
Our final formula to get the currency conversion will be as follows,
=GOOGLEFINANCE("USDEUR")- “USDEUR” is the ticker symbol
Make sure to put the main currency code at the beginning and the target currency code at the end. For example, to get the conversion rate between the US dollar and Euros, the USD was our main currency, and the EUR was the target currency.
Now, let us learn the steps to get the historical currency conversion rate.
For historical data, you are supposed to use the second, third, and fourth arguments of the GOOGLEFINANCE function.
Our task is to find the currency conversion between United States Dollars and Euros for the entire November 2023.
Here are the steps:
- Click on the desired cell
- Type “=googlefinance”
- Select the first option from the popup or press the “Tab” key
- Replace the ticker argument with the “USDEUR“
(Here, the USD stands for United States Dollar and EUR for Euros) - Press “,” to move to the next argument
- Type “price” in the place of the attribute argument
- Press “,“
- Now, for the start_date argument, let us put “11/1/2023” as the date
(Make sure to follow the mm-dd-yyyy format) - Press “,“
- For the end_date argument, we need to type “11/30/2023“
- Complete the bracket using “)“
- Press the “Enter” key
Our final formula after following the above steps:
=GOOGLEFINANCE("USDEUR","price","11/1/2023","11/30/2023")- “USDEUR” is the ticker symbol
- “price” is the attribute
- “11/1/2023” is the start date
- “11/30/2023” is the end date
As we didn’t provide any value in the place of the interval, the final argument of the GOOGLEFINANCE function, the formula has returned daily conversion rates for November 2023.
You can fetch the weekly data by using the “Weekly” keyword at the end. Refer to the following formula,
=GOOGLEFINANCE("USDEUR","price","11/1/2023","11/30/2023","Weekly")Note that the GOOGLEFINANCE is an array formula that populates results across various columns and rows. You must make sure to keep enough rows and columns empty; otherwise, the function will return a “#REF!” error.
FAQs
Q. What is the Google stock price function?
The GOOGLEFINACE function is used to fetch the stock information and currency conversion rates from Google Finance.
We have discussed the general syntax for the function and the steps to use it to fetch the stock information in the above section of this article.
You don’t need any expert knowledge to use this function in Google Sheets.
It can be used to build robust stock portfolios to track your profits and losses over time.
Q. What is the notification “Quotes are not sourced from all markets” at the bottom of my spreadsheet?
The GOOGLEFINANCE function fetches data in real time. However, it can have a delay of up to 20 minutes.
The notification displayed at the bottom of the spreadsheet talks about the same delay.
Q. Can I use GOOGLEFINANCE to find Cryptocurrency conversion?
Of course, you can use the GOOGLEFINANCE function to fetch the Cryptocurrency conversion rates.
The three-letter code for the Cypto currency is “BTC”.
Here’s the formula to fetch the conversion rate between Cypto and the United States dollar.
=GOOGLEFINANCE("BTCUSD")Q. What is the best use of the GOOGLEFINANCE function?
Note that the function may return errors sometimes. These errors are due to the limitations of the function itself.
Most of the time, the “#N/A” error will appear when you are using lots of formulas created using the GOOGLEFINANCE function in the same spreadsheet.
In other words, you can rely on this function to fetch the stock information for 2-5 companies at a time.
You should use this function to track your stock portfolio.
Conclusion
That’s all about the GOOGLEFINANCE function in Google Sheets.
You can quickly get real-time and historical stock information.
It is the best tool to save time and track your stock portfolio. You can fetch a variety of stock data using this function. Make sure to explore the attributes compatible with it by clicking here.
Use it to gain stock market expertise, build investment strategies, and improve financial management skills.
Feel free to comment below if you are having any issues or particular errors while using the GOOGLEFINANCE function. Our team is always there to help you.
Also, make sure to explore our blog for more Google Sheets tips and tricks to manage your finances and increase profits over time.