Along with Yahoo Finance, Google finance has become one of the most important online resources to access stock news and data.
What is the GoogleFinance function?
The GoogleFinance function is a function included in Google Sheets is a way for all types of investors to get stock and currency data right on Google Sheets for analysis.
With this function, obtaining historical information such as open, close, high, volume, and real-time metrics like market capitalization, price to earnings ratio (PE), change, and earnings per share (EPS) on Google sheets is simple.
The GoogleFinance function has two primary uses for individual investors; tracking your investment portfolio and analyzing potential stocks to invest in.
Why is the GoogleFinance function useful?
The GoogleFinance function is helpful for investors because in order to track stocks and other securities properly, being able to get stock data is very important. Without the help of GoogleFinance and Wisesheets (more on this later), investors have to resort to wasting a lot of time manually copy-pasting this data from multiple sources on their spreadsheet.
Oftentimes finding a good security to invest in takes analyzing 10's (maybe 100's) of companies before finding an investment opportunity. If it takes a long time to get the data for each of those companies, it becomes really difficult to do all this work over and over. Instead, with the right tools, you can simply get all the stock and currency data you need immediately and make good investment decisions a lot faster.
How to use GoogleFinance function in Google Sheets?
The way to use the GoogleFinance function in Google Sheets is very simple. All you need to do is enter =GOOGLEFINANCE() and in those brackets, you have the option to enter a ticker, a metric, and the start and end date for that metric.
Let’s break each of those components one by one.
Ticker (mandatory)
In the stock market, the ticker is the unique abbreviation used to identify a stock/security that is publicly traded. The ticker in the GoogleFinance function includes all stocks in the following exchanges and currencies.
| Region | Exchange Code | Description | Delay (minutes) |
|---|---|---|---|
| Americas | BCBA | Buenos Aires Stock Exchange | 20 |
| BMV | Mexican Stock Exchange | 20 | |
| BVMF | B3 – Brazil Stock Exchange and Over-the-Counter Market | 15 | |
| CNSX | Canadian Securities Exchange | Realtime | |
| CVE | Toronto TSX Ventures Exchange | 15 | |
| NASDAQ | NASDAQ Last Sale | Realtime * | |
| NYSE | NYSE | Realtime * | |
| NYSEARCA | NYSE ARCA | Realtime * | |
| NYSEAMERICAN | NYSE American | Realtime * | |
| OPRA | Options Price Reporting Authority | 15 | |
| OTCBB | FINRA OTC Bulletin Board | 15 | |
| OTCMKTS | FINRA Other OTC Issues | 15 | |
| TSE | Toronto Stock Exchange | 15 | |
| TSX | Toronto Stock Exchange | 15 | |
| TSXV | Toronto TSX Ventures Exchange | 15 | |
| Europe | AMS | Euronext Amsterdam | 15 |
| BIT | Borsa Italiana Milan Stock Exchange | Realtime | |
| BME | Bolsas y Mercados Españoles | 15 | |
| CPH | NASDAQ OMX Copenhagen | Realtime | |
| EBR | Euronext Brussels | 15 | |
| ELI | Euronext Lisbon | 15 | |
| EPA | Euronext Paris | 15 | |
| ETR | Deutsche Börse XETRA | 15 | |
| FRA | Deutsche Börse Frankfurt Stock Exchange | Realtime | |
| HEL | NASDAQ OMX Helsinki | Realtime | |
| ICE | NASDAQ OMX Iceland | Realtime | |
| IST | Borsa Istanbul | 15 | |
| LON | London Stock Exchange | Realtime | |
| MCX | Moscow Exchange | Realtime | |
| RSE | NASDAQ OMX Riga | Realtime | |
| STO | NASDAQ OMX Stockholm | Realtime | |
| SWX, VTX | SIX Swiss Exchange | 15 | |
| TAL | NASDAQ OMX Tallinn | Realtime | |
| VIE | Vienna Stock Exchange | 15 | |
| VSE | NASDAQ OMX Vilnius | Realtime | |
| WSE | Warsaw Stock Exchange | 15 | |
| Africa | JSE | Johannesburg Stock Exchange | 15 |
| Middle East | TADAWUL | Saudi Stock Exchange | 15 |
| TLV | Tel Aviv Stock Exchange | 20 | |
| Asia | BKK | Thailand Stock Exchange | 15 |
| BOM | Bombay Stock Exchange Limited | Realtime | |
| KLSE | Bursa Malaysia | 15 | |
| HKG | Hong Kong Stock Exchange | 15 | |
| IDX | Indonesia Stock Exchange | 10 | |
| KOSDAQ | KOSDAQ | 20 | |
| KRX | Korea Stock Exchange | 20 | |
| NSE | National Stock Exchange of India | Realtime | |
| SGX | Singapore Exchange | Realtime | |
| SHA | Shanghai Stock Exchange | 1 | |
| SHE | Shenzhen Stock Exchange | Realtime | |
| TPE | Taiwan Stock Exchange | Realtime | |
| TYO | Tokyo Stock Exchange | 20 | |
| South Pacific | ASX | Australian Securities Exchange | 20 |
| NZE | New Zealand Stock Exchange | 20 |
Metric (optional if not entered real-time price is the default)
A metric refers to different metrics that Google considers important about stocks. There are two types of metrics: real-time and historical. The first refers to data which is constantly updating (stock price) whereas the second refers to data from the past (the close price for the last 100 days).
Here is the list of the covered historical and real-time metrics:
| Historical metric | Description |
|---|---|
| “open” | Price at market open. |
| “close” | Price at market close. |
| “high” | The high price during the specified time period. |
| “low” | The low price during the specified time period. |
| “volume” | The volume during the specified time period. |
| “all” | Returns all of the above. |
| Real-time metric | 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. |
| “volumeavg” | The average daily trading volume. |
| “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. |
| “change” | The stock price change since the end of yesterday’s trading. |
| “beta” | The beta value |
| “changepct” | The percentage change in price since the end of yesterday’s trading. |
| “closeyest” | Yesterday’s closing price. |
| “shares” | The number of shares outstanding. |
| “currency” | The currency that the stock is priced in. |
Start date (optional)
For historical data, the start date indicates the beginning period that you’d like to get the data from. It’s important to enter this in the date format “year, month, day”
End date (optional)
For historical data, this indicates the date you’d like the data to end. Similar to the start date the format should be “year, month, day”. However, a very useful feature is that you can indicate a number instead of the date for example 90 and the function will return the last 90 days of data.
Interval (optional)
For historical data, you have the option to select between getting "weekly" and "daily" data. So for example, if you want to get the historical stock close price, the data would indicate each day in the period you selected and the respective close price. On the other hand, if you want to get it on a weekly basis you would get a table that indicates the week and the close price.
Getting Real-time Data with the GoogleFinance Function
Once you know the metrics available it’s very easy to use the function. All you need is enter a stock symbol or ticker that you are interested in getting the data for and the metric you wish to retrieve from the real-time metric list.
For example, let's say that you want to get Apple’s current stock price. All you do is enter =GOOGLEFINANCE(“AAPL”, “PRICE”) in one of the cells of your spreadsheet and press enter.
It is important to know that the data refreshes automatically every 15 minutes and could be delayed up to 20 minutes.
Getting Historical Data with the GoogleFinance Function
Similar to the real-time data retrieval, you need the ticker of the company you are to analyze, select one of the available historical metrics from the list, and include the start date and end date you’d like to get the data for which can be either daily or weekly.
For example, to get Apple’s historical volume from July 1st to July 31 all you do is enter =GOOGLEFINANCE(“AAPL”, “VOLUME”, DATE(2021, 07,01), DATE(2021,07,31), “DAILY”):
Notes that apply for both functions:
For both getting real-time and historical data, it is important to keep in mind that capitalization and spacing do not matter for the ticker, metric, and interval but the spelling must be the same. This is because Google automatically turns all that text into lowercase and removes additional spaces.
Common errors
#REF GoogleFinance function error
A typical error you might encounter when using the function is the #REF error. This is a very simple error to fix. All you need to do is make sure that there is enough empty space below and beside the cell you are trying to get historical data for. Remember just because you are using the function on a single cell doesn’t mean only a number will be returned. Most often for historical data a table will be returned and you need to make sure you have enough space for it so that you don’t get this error.
Not dragging locking cells properly for the GoogleFinance function
The other big mistake users make is not locking their cells properly to get multiple stock data points at once. For this, you can reference cells and lock them using the $ sign and then drag the function across.
The proper way to lock the cells is as follows: Say you are working with cell A1, if you want to lock the cell column use $A1, if you want to lock the cell row use A$1 and if you want to lock both use $A$1.
Is the GoogleFinance function accurate?
The answer is for the most part yes, however google does not want to be liable for their data and states the following to Google Finance users “Google is not an investment adviser, financial adviser or a securities broker. […] Data is provided by financial exchanges and other content providers and may be delayed as specified by financial exchanges or other data providers. Google does not verify any data and disclaims any obligation to do so.”
Advanced use of the GoogleFinance function
Once you have a strong grasp of how the Google function works you can use it in many different ways to help with your investment analysis. Here are some of the most useful ways to use it:
Generate sparkline charts automatically
Using the GoogleFinance function and the sparkle function together is a great way to quickly visualize historic data.
Here is an example:
Build a portfolio tracking spreadsheet
Tracking the current securities is very important to know how your portfolio is doing and enables you to prepare financially for taxation. With the function, you can quickly build a dashboard that allows you to see your biggest holdings, their returns, or losses in one single view.
Combining the GoogleFinance function and Wisesheets for all of your investment analysis needs
Two downsides of the GoogleFinance function are the lack of historical stock financials and real-time data. As you have learned GoogleFinance is amazing but it does also have its limitations. If you are serious about stock investing and you want to save hours manually copy-pasting stock data the best way to do it is to use GoogleFinance and Wisesheets together.
With Wisesheets you can get the company’s financials including the income statement, balance sheet, cash flow, and key metrics for 14 different exchanges with 20-year coverage quarterly or annually directly on your Excel or Google Sheets spreadsheet in one click by just entering the ticker.
Or you can get the same information but only what you want using the =WISE() function. All you need is the company ticker, parameter, and the period (year, quarter, or TTM).
So for example you could get Apple’s revenue Q1 revenue for 2020 by using the function like this:
=WISE(“AAPL”, “REVENUE”, 2020, “Q1”)
Using the GoogleFinance function and Wisesheets together you can create dynamic stock analysis models where you can just enter the ticker and get all the financial data you need immediately.
You can get this template for free here. Keep in mind you will need a Wisesheets account which you can get here for free.
We hope this guide helps you take advantage of the GoogleFinance function and Wisesheets to make faster and better investments in the stock market.
To your investing success,
The Wisesheets Team
