Google has its own subdomain for financial data called Google Finance. It consolidates data from several financial markets in the US, Europe, and Asia, and it also includes finance and business-related news. You can search for a specific company and view their performance in a specific financial market. For our examples, we will import data from Netflix as they perform in the NASDAQ market.
How can we import this vast wealth of useful financial data? Fortunately, Google Sheets has its own built-in function called GOOGLEFINANCE! You no longer need to access an API through a custom code loaded in Google Apps Script or install add-ons to import relevant financial data. We will learn in this tutorial how to use the GOOGLEFINANCE function. Are you ready?
The GOOGLEFINANCE function requires you to specify the ticker symbol for a company in a financial market in the following format, which includes both the exchange symbol and the ticker symbol.
Where MARKET and COMPANY are both examples of a ticker symbol used for the financial market and company, respectively. One way to find it is by looking at the URL of the page for the company:
Here, NASDAQ is the code for NASDAQ market while NFLX is the ticker symbol for Netflix. So, for the ticker that we will use in GOOGLEFINANCE, it will be as follows:
The GOOGLEFINANCE function for importing real-time data has the following syntax:
The minimum information you need for GOOGLEFINANCE to work is the ticker. This will give you the latest price in the specified market. Both the ticker and the attribute should be enclosed in quotation marks.
Note that GOOGLEFINANCE function does not allow combining two or more attributes in the same formula. So if you want to display two or more real-time attributes, you have to write two GOOGLEFINANCE formulas in different cells of your Google Sheet.
There are two ways to do this. Simply specifying the ticker is enough:
Where ticker follows the MARKET:COMPANY format.
You can also specify the attribute price, but you will get the same result:
To import the daily high, use the high attribute:
To import the daily low, use the low attribute:
Here is the list of real-time attributes you can use in GOOGLEFINANCE:
You can add these attributes in a drop-down box and then point GOOGLEFINANCE function to that box so you can change the attribute to whatever you need to check:
The result will look like as follows:
GOOGLEFINANCE also allows you to import historical data. The syntax is as follows:
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
The attributes are as follows:
Unlike real-time data, GOOGLEFINANCE has an option to display all the historical stock data in a table for the given range of dates. You will prefer importing all possible stock data, so you can use the following syntax:
=GOOGLEFINANCE(“ticker”, “all”, start_date, end_date)
Where you can get daily data for the OLHVC data:
The date column does not only contain the date but also the closing time of the market.
In addition to historical data for stocks, the GOOGLEFINANCE function in Google Sheets can return mutual fund data as well. Mutual funds only update their price once a day after the market closes, so attributes such as average daily trading volume are not available live, and only historical data is available. The attributes for mutual fund data are as follows:
There are a wide variety of formats that you can use when specifying the dates. For starters you can use the DATE function with the following syntax:
DATE(year, month, day)
This is handy and helps you prevent most of the issues regarding date formats in Google Sheets. If you want to regularly import the last few days of historical data, you can use TODAY function in the following manner:
where n is the number of days that you want included. For example, if you want the data from the last 30 days, then your formula will be:
If you want to get 30 rows of data, however, you need to account for the weekends as several markets are closed during those times. Simply adjust the number to compensate for the weekends covered by the range.
Google Finance also offers currency exchange data, which also includes cryptocurrency data. Unlike data for companies in financial markets, currencies have less real-time data and are usually confined to their current prices.
The ticker for currency exchange data is:
Where the price of CUR1 is specified in units of CUR2. For example, if we use the following ticker:
Then you will get the USD to JPY exchange rates.
To import the latest exchange rate, you can simply specify the ticker symbol:
To import historical exchange rates, use the following format:
=GOOGLEFINANCE(“ticker”, “price”, start_date, end_date)
Where the same options for specifying dates for historical data in the previous section are still applicable.
If you use the all option for the attribute, you will get the following:
Unlike in the financial markets such as stock markets, Google Finance only stores the closing price for each day.
You can combine GOOGLEFINANCE with SPARKLINE to visualize the data for the last n days. The syntax is:
=SPARKLINE(GOOGLEFINANCE(“ticker”, “price”, start_date, end_date))
The result will look like as follows:
Google makes it very clear that their financial market data is not meant for use by professionals, and any use of the data by professionals is subject to licensing fees by a third party data provider. However, for most people, using the GOOGLEFINANCE function in Google Sheets is a very useful method to obtain historical stock information and inform your investing strategy. By following this Google Sheets tutorial, you will be able to easily pull specific Google Finance data about your favorite stocks into your Google Sheets spreadsheet.
To see GOOGLEFINANCE function in action, check out the following sample sheet:
We hope this article has helped you and given you a better understanding of how to use the GOOGLEFINANCE function in Google Sheets. You might also like our articles on how to use CONCATENATE in Google Sheets and how to use the Google Seets VLOOKUP function.