How to use the GOOGLEFINANCE function in Google Sheets (2024)

The GOOGLEFINANCE functionallows 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.

Syntax

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 retrievePrice 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 thenum_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

For a complete list of all attributes including those for mutual fund data, head to Google’s GOOGLEFINANCE support page.

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 theGoogle Financewebsite, 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):

Please note: 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.

3. 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.

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.

4. Obtain YTD stock information

Another common need for users is to obtain the YTD (Year To Date) stock information. This allows you to see how the stock is performing from the beginning of the year until now. Unfortunately, the only attribute for YTD is returnytd, which can only be applied to mutual fund data. To find the YTD value for your real-time or historical data, we must find an alternative.

Luckily, we can find the YTD of stock by combining two specific formulas in one. Use the following syntax as your reference. Simply substitute the ticker with your own.

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 and Wisesheets

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. It can bring a 20-year coverage quarterly or annually directly on your Excel or Google Sheets spreadsheet in one click.

All you need to do is enter the ticker in the add-on:

Another option to get the same information but filtered is by using the =WISE() function. All you need is the company ticker, parameter, and the period (year, quarter, or TTM).

You could for example get Apple’s revenue Q1 revenue for 2020 by using the function like this:

Get the add-on here: Wisesheets

Stock reporting in Google Sheets

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.