The GOOGLEFINANCE function allows you to import real-time financial and currency market data straight into Google Sheets. As well as enabling you to track current stocks and shares information, it can also be used to retrieve historical securities data.
This function imports data from the Google Finance web application, which provides daily stock prices, news from the currency and financial markets, and other information on market trends. Google Finance can be accessed from the Google menu like all other Google applications, or simply by searching for a stock on Google, which will bring up the Google Finance information relating to that stock.
If an analyst wants to gather information on a stock, they would usually have to visit a financial market website or database to access a range of information. After carrying out research, the next step is to compile all that relevant stock information into a spreadsheet to prepare it for further number crunching or building financial models.
To do this, the analyst normally has to copy the stock information from the website and paste it into a spreadsheet or use customized scripts to pull in such information. The first approach is cumbersome, unreliable, and definitely not recommended. While the second method is faster, it also has drawbacks. Getting a coder to write a script is expensive and complex, and not very flexible if you need to make changes in the future.
A more reliable and economic alternative to both options is the GOOGLEFINANCE function. If you work with financial market data, this function can save you a serious amount of time, automating data retrieval for you.
GOOGLEFINANCE(ticker, [attribute], [start_date], [num_days|end_date], [interval])
- ticker – This is an abbreviation used to uniquely identify publicly traded securities. It may consist of letters, numbers, or a combination of both. For example, AAPL and MSFT represent the tickers for Apple Inc. and Microsoft Corporation respectively. The ticker will tell the function which stock to provide information on. The exchange that the stock trades on can also be specified, which will help avoid discrepancies. For example, you could type “NASDAQ:AAPL”. Be sure to put quotation marks around the ticker (including the exchange if used).
- attribute – This is an optional parameter that specifies the type of information GOOGLEFINANCE function should fetch. There are a number of different outputs that are available such as price and earnings per share. Check out the full list below. The attribute input, like the ticker, is processed as text, so you’ll need to put quotation marks (“”) around the attribute you’re asking for. Please note, if you don’t enter any attribute, Google Sheets will retrieve Price information by default.
- start_date – This is used when accessing historical data on a stock, and it indicates the date from which the historical data needs fetching. This is an optional parameter – if you leave it blank, the function will provide information from today. If you specify the start_date but not the num_days|end_date parameter, then you’ll only receive data for that day.
- num_days|end_date – This optional parameter, along with the start_date, will indicate the time frame between which Google Sheets should fetch the stock information.
- interval – This is an optional parameter that indicates the frequency. The two possible inputs are “WEEKLY” and “DAILY”.
Attributes for the GOOGLEFINANCE function
|“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.|
|“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.|
|“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.|
How to use the GOOGLEFINANCE function
1. Obtain stock information
Syntax: GOOGLEFINANCE(ticker, [attribute])
In the following examples, I have used “AAPL” (Apple Inc.) for the ticker.
Firstly, I visited the Google Finance website, and keyed in “AAPL” in the search box and hit the Enter key.
As a result, you can see all the current stock indicators for Apple Inc.
Here’s how that information looks in Google Sheets using the basic version of the function: GOOGLEFINANCE(ticker, [attribute]).
The GOOGLEFINANCE function pulls the corresponding information into the spreadsheet:
In the example above, I have listed the different attributes I want to look at in column A. I then reference these attributes in the formula, which allows me to quickly go down the list without having to change the formula every time.
Alternatively, I could also hardcode each attribute into the formula, for example by typing =GOOGLEFINANCE(“AAPL”,”Low”) to give me the low price of the day.
Note that I’ve fixed the location of the ticker reference cell B1 by putting the $ sign in front of the B and the 1 (using the F4 key on your keyboard is a shortcut).
By doing this, you can drag the formula down from cell D4 cell right down to D18, and the formula will keep this reference cell constant. I did not fix the location of the attribute cell, because I want this to change as I go down the list to get all the different attributes.
2. Obtain historical stock information for a single day
Syntax: GOOGLEFINANCE(ticker, [attribute], [start_date])
As listed in the table previously, Google Sheets provides a handful of historic data attributes like “open”, “close”, “high”, “low” etc.
While you could try them individually, I’ll explore the “all” attribute, which provides them all at once.
This will give the following result for the corresponding start_date:
Notice that the output of this historical attribute is more than one cell.
When calling up historical data, the formula will provide a Date column and an attribute column (in this case, because I used “all” to call up multiple attributes and I got 5 different attribute columns):
Common errors with GOOGLEFINANCE function
When calling up historical data you might see a #REF! error. This occurs when the GOOGLEFINANCE function encounters cells that already contain data. Here, the function would have to overwrite this data when it outputs its information over multiple cells.
To solve this error, just make sure there are enough empty cells below and beside the cell in which you’re entering the GOOGLEFINANCE formula, so that it has room to output its results over multiple cells.
The fact that the GOOGLEFINANCE formula fills multiple cells when providing historical information is usually helpful because it adds clarity to the information provided.
Sometimes, however, you may want the formula to only give you a single number.
For example, you might want the highest price of a stock on a given day, without the formula outputting a matrix including the date and the price. In this case, you would combine the GOOGLEFINANCE formula with the INDEX formula:
In this example, I have used the GOOGLEFINANCE formula to give me the highest price of Apple stock on February 27, 2017.
In the above screenshot, you can see that the output of the formula is a 2×2 matrix which gives the date and the high price.
If you want the formula to only give you the high price, without the cells that indicate the date and the headers, you can use the INDEX function and specify that you only want the formula to provide the information in the second row of the second column of the matrix that the GOOGLEFINANCE formula provides.
As you can see, I do this by adding INDEX to the start of the formula and then specifying cell 2,2 as the output I want.
Obtaining historical stock information over a period of time
Syntax: GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date], [interval])
So far I’ve tried fetching the information corresponding to a single day. Can you do so for a period of time? Yes, you certainly can. Here’s an example.
The formula I use to do this is
From here, it’s easy to create a chart of the weekly closing price using the charting function in Google Sheets.
By using the GOOGLEFINANCE formula to call up different attributes over different time periods and intervals, you can create highly customizable data sets and charts that will help you analyze the stock data in many different ways.
How to use the GOOGLEFINANCE function for Currency Conversion
Another helpful feature of the GOOGLEFINANCE function is the ability to get live currency conversion rates directly in your spreadsheets.
This can easily be done by replacing the stock ticker with “Currency:currency1currency2”, where currency1 and currency2 are the three-letter codes for the currencies you want to convert.
For example, to find the conversion rate between U.S dollars and Canadian dollars, I do the following: =GOOGLEFINANCE(“CURRENCY:USDCAD”)
If you work with stock market and currency data, GOOGLEFINANCE function is a huge time-saver when working in Google Sheets. Experiment with the different attributes and find out what works for you!
Need to collate financial data from multiple Google Sheets into a central file for reporting or analysis? Discover how to merge multiple Google Sheets into one.
Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.