What is GOOGLEFINANCE in Google Sheets?
The GOOGLEFINANCE function incorporates real-time financial data into Google Sheets. Using GOOGLEFINANCE, users can access current stock prices, historical data spanning days, weeks, months, etc., and currency conversion rates, all within a single function. The syntax is quite detailed, which will be discussed in the next section.
For example, let us try to get Google’s current stock price. Use the following formula in Google Sheets.
=GOOGLEFINANCE(“GOOGL”, “price”)
Key Takeaways
- GOOGLEFINANCE in Google Sheets is used to fetch financial data, both real-time and historical, that include stock prices, currency exchange rates, etc. It retrieves this information based on the parameters provided as attributes.
- The basic syntax of GOOGLEFINANCE formulas is:
GOOGLEFINANCE(“ticker”, “attribute”, [start_date], [end_date|num_days], [interval]).
- Some of the common attributes used to retrive data include:
- “price”: Current stock price.
- “close”: Closing price for historical data.
- “high”: Highest price for the day.
- “low”: Lowest price for the day.
- It can be used in conjunction with functions like IMPORTXML to retrieve other necessary data from webpages.
- It is not quite a reliable function even though the data is periodically updated, as there might be some discrepancies, which should be verified before making important financial decisions.
Syntax
The syntax of the GOOGLEFINANCE function in Google Sheets is as follows.
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
- Ticker: The ticker symbol should be considered for security. Use both the exchange and ticker symbols for accurate results. For example, use “NASDAQ:GOOG.”
- [attribute]: Specifies the type of data to retrieve (e.g., “volume”, “marketcap”). – optional
- [start_date]: The start date for historical data. – optional
- [end_date|num_days]: The end date or the number of days for historical data. – optional
- [interval]: The frequency of data points (“DAILY,” “WEEKLY,” etc.) – optional
Attributes
Some of the common attributes used in GOOGLEFINANCE in Google Sheets include “price,” “volume,” “pe,” “eps,” etc.
Each attribute provides different information on the financial data. Let us look at some of the common attributes.
| Attribute | Description |
|---|---|
| “price” | Stock price. In real-time but with a delay of up to 20 minutes. |
| “priceopen” | Opening price (price at market open). |
| “high” | High price of the current day. |
| “low” | Low price of the current day. |
| “volume” | The trading volume of the current day. |
| “marketcap” | The market capitalization of the stock. |
| “tradetime” | The time of the last trade of the stock. |
| “datadelay” | The delay time for the real-time data. |
| “pe” | Price-to-earnings ratio |
| “eps” | Earnings per share |
| “high52” | The highest price in the last 52 weeks. |
| “low52” | The lowest price in the last 52 weeks. |
| “beta” | The beta value |
| Historical attribute | Description |
|---|---|
| “close” | Price at market close. |
| “open” | Price at market open. |
| “low” | The low price during the specified period. |
| “volume” | The volume during the specified period. |
| “all” | Returns all the above. |
| “high” | The high price during the specified period. |
How to use GOOGLEFINANCE in Google Sheets?
To get an idea of the current stock prices, visit the Google Finance website, and search for “NFLX”. You can see all the current stock indicators for Netflix Inc.
Step 1: Now, let us use a basic version of the function to see how that information looks in Google Sheets GOOGLEFINANCE(ticker, [attribute]).
Enter the following formula in a cell.
=GOOGLEFINANCE(“NASDAQ:NFLX”, “price”)
The GOOGLEFINANCE function pulls the corresponding information into the spreadsheet:
Step 2: In the example above, we have used a simple attribute . You can also use other attributes to find different values. For example, to find Netflix’s low price of the day, type
=GOOGLEFINANCE(“AAPL”,”Low”)
Examples
Let us look at some interesting examples using GOOGLEFINANCE in Google Sheets.
Example #1 – Get current stock data
Getting the current stock data for any company involves using the GOOGLEFINANCE function and the “price” attribute. Let’s look at how to do it and provide some additional information.
Step 1: Enter the following function in cell B2. Press Enter.
Step 2: We can also check for additional information, like the PE ratio, by entering the following function in B4.
=GOOGLEFINANCE(“GOOGL”, “pe”)
Example #2 – Get historical stock data
Getting historical stock data is very simple in GOOGLEFINANCE when you use the correct attributes.
The parameters for bringing in historical data include the DATE and also “all.” The syntax for such data would be:
=GOOGLEFINANCE(“ticker”, “attribute”, [start_date], [end_date|num_days], [interval])
Step 1: Let us specify the date range for which we need the data for the company Netflix Inc. Enter the start and end date and if you need the days for every trading day or week. Here, we use weekly. Enter the following formula in cell A6. We choose to display “all” data.
=GOOGLEFINANCE(“NASDAQ:NFLX”,”all”,DATE(2024,1,1),DATE(2024,3,31),”weekly”)
Step 2: Press Enter. You will get all the details of Netflix’s weekly historical stock data for the specified dates.
Example #3 – Convert Currency
In this example, using GOOGLEFINANCE in Google Sheets function, we build a currency converter. For this, we first get the exchange rate and multiply it with the amount we wish to convert. Here, we want to convert the USD to Japanese YEN and find the value of 100 dollars in Japanese currency.
Step 1: Enter the following function in cell A2.
=GOOGLEFINANCE (“Currency:USDJPY”)
Step 2: Press Enter. You get the current exchange rate of USD to JPY. Now, let us check how much 100 USD is worth in Japanese YEN. Just multiply the exchange rate by $100.
Enter formula =A2*B2 in cell C2. Press Enter.
Thus, $100 is worth 14861.9 ¥ in Japanese Yen.
Example #4 – Get currency exchange value
Converting a currency value from one to another is as simple as it gets in GOOGLEFINANCE. With the GOOGLEFINANCE in Google Sheets formulas, you can build a currency converter.
Let us now try to get the currency exchange value with GOOGLEFINANCE.
Step 1: Enter the following formulas in cells B2 and B4, respectively.
Cell B2 contains the formula:
=GOOGLEFINANCE (“Currency:EURUSD”)
B4 cell contains the formula
=GOOGLEFINANCE(“Currency:USDEUR”)
Get the current exchange rate between USD and EUR (US Dollar to Euro).
Step 2: Press Enter. You get the current exchange rate between USD and EUR (US Dollar to Euro) in B4 and between EUR and USD (Euro to US Dollar) in B4.
Example #5 – GOOGLEFINANCE with IMPORTXML
Using GOOGLEFINANCE in Google Sheets provides only basic information, such as the price of a stock. With IMPORTXML, a built-in function to import structured data from webpages, you can pull additional information that is not available in GOOGLEFINANCE.
Step 1: First, we must understand the syntax of IMPORTXML.
=IMPORTXML(url, xpath_query) where “url” is the URL of the webpage from where we need the data and “xpath_query” is the XPath query to locate the particluar data on the page.
Here, we choose the Financial Times market page with the link https://markets.ft.com/data. Go to this webpage.
Step 2: Here, go to NASDAQ NMS COMPOSITE INDEX by clicking on it.
Step 3: To find the shares traded, right-click as shown below. Choose “Inspect” from the dropdown menu.
Step 4: Right-click on the highlighted code. Select “Copy” – “Copy XPath.”
Step 5: You get this xpath
0/html/body/div[3]/div[2]/section[1]/div/div/div[1]/div[2]/ul/li[3]/span[1]
Add it to the IMPORTXML formula as follows:
=IMPORTXML(“https://markets.ft.com/data/indices/tearsheet/summary?s=COMP:NAS”,”/html/body/div[3]/div[2]/section[1]/div/div/div[1]/div[2]/ul/li[3]/span[2]”)
Step 6: Press Enter. We have successfully pulled data from The Financial Times!
Important Things To Note
- GOOGLEFINANCE is powerful for tracking and analyzing financial data, but the data accuracy may vary, and cross reference may be necessary especially when taking important decisions.
- GOOGLEFINANCE can have a delay of up to 30 minutes, and you must manually open your spreadsheet for an update.
- The spreadsheet will fetch only the values saved the last time the spreadsheet was opened.
Frequently Asked Questions (FAQs)
As GOOGLEFINANCE is a real-time data function, it can cause a delay of up to 20 minutes. However, you will have to open your spreadsheet before an update can happen manually. The spreadsheet will fetch only the values saved the last time it was opened.
The GOOGLEFINANCE function is only sometimes reliable and has some limitations. Sometimes, it returns a #N/A error, especially when it tries to retrieve historical data from Google Finance via the Google Sheets API.
Another reason for the #N/A error is the attempt to retrieve historical data from Google Finance via the Sheets API or Apps Script. Why? The Google Finance API is no longer supported and is not documented. Fixing the internal error of your GOOGLEFINANCE formula is tough.
GOOGLEFINANCE returns data in a table format, which can be formatted using Google Sheets’ standard formatting options like number, date, and currency formats using the conditional formatting option.
Download Template
This article must help understand GOOGLEFINANCE in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is GOOGLEFINANCE in Google Sheets. We learn its syntax and how to use it to get different stock information with examples. You can learn more from the following articles –
Leave a Reply