How to use the GOOGLEFINANCE function in Google Sheets

The GOOGLEFINANCE function allows you to import real-time financial and currency market data straight into Google Sheets. It also enables you to track current and historical data for various financial instruments, such as stocks and shares.

This function imports data from the Google Finance web application. This Google tool provides daily stock prices, financial markets news, and analysis of market trends. Google Finance can be accessed from the Google menu like all other Google applications. Alternatively, if you search for a stock on Google, the first result will show you information from Google Finance.

If analysts wanted to gather information on a stock, they would have to visit a financial website or database to access a range of information. After carrying out research, the next step was to compile all that relevant stock information into a spreadsheet for further analysis.

This involved the analyst copying the stock information from the website and pasting it into a spreadsheet. An alternative was to use customized scripts to scrape this information from the web. The first approach is cumbersome, unreliable, and definitely not recommended. And the second method is faster, but it also has drawbacks. Getting a coder to write a script is expensive, time consuming, and not very flexible.

A more reliable and economic alternative to both options is the GOOGLEFINANCE spreadsheet function. If you work with financial market data, this function can automate data retrieval to save you serious time.


=GOOGLEFINANCE(ticker, [attribute], [start_date], [num_days|end_date], [interval])

  • ticker – This is an abbreviation used to identify publicly traded securities. It consists of a combination of letters and numbers (for example, “AAPL” represents Apple Inc. and “MSFT” stands for Microsoft Corporation). 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.
  • attribute – This is an optional parameter that specifies the type of information GOOGLEFINANCE function should fetch. There are a number of different options to select from such as “price” and “earnings per share” (the next section includes a full list). Like the ticker, the attribute input is processed as text, so you’ll need to wrap it in quotation marks (“”). By default, Google Sheets will retrieve “price“ information if you leave this blank.
  • start_date – This is used to indicate the starting date when fetching historical data. This is an optional parameter, if left 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”.


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.
“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.
Historical attribute 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.

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


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 searched for “AAPL”. 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.


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.


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.

=(GOOGLEFINANCE(ticker,”price”)/INDEX(GOOGLEFINANCE(ticker,”close”,”01/01″, today()),2,2))-1

Here I’ve obtained the YTD price for Apple. My formula looks like this:

=(GOOGLEFINANCE(“AAPL”,”price”)/INDEX(GOOGLEFINANCE(“AAPL”,”close”,”01/01″, today()),2,2))-1

As you can see, by copying and pasting the formula above and substituting the ticker with my chosen stock, I have successfully returned the YTD value.

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:


This is just an overview of what GOOGLEFINANCE can do in terms of currency conversion. Learn more ways to convert currencies with spreadsheets.

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:

Googlefinance and Wisesheets 1

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:


When you use the GOOGLEFINANCE function and Wisesheets together, you can create dynamic stock analysis models and get all the financial data you need immediately.

Googlefinance and Wisesheets 2

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.

Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.

Ready to streamline your spreadsheet data?

You may also like…