Did you know that you can pull stock prices, and other stock data in Google Sheets, by using a formula? In this lesson I am going to teach you all the different ways to use the GOOGLEFINANCE function, to pull real-time stock data, as well as historical stock data. I’ll also go over a simple example of how to pull crypto prices in Google Sheets but below I have linked to a more detailed article on pulling crypto prices.
To get the price for a stock on Google Sheets, follow these steps:
- Type =GOOGLEFINANCE( to being the Google Finance formula
- Type a stock symbol (inside of quotation marks / with a quotation mark before and after the stock symbol), like this: “TSLA”
- Press enter on the keyboard. The final formula will look like this: =GOOGLEFINANCE(“TSLA”)
The formula above will pull the real-time stock price for TSLA.
Click here to get your Google Sheets cheat sheet
Below are example formulas of how the GOOGLEFINANCE formula can be used, but further below you will find much more information on how to use the formula for a variety of applications, with detailed examples.
Learn how to pull data from websites with the IMPORTXML function
Learn how to pull crypto prices in Google Sheets
Check out my free stock tracker templates for Google Sheets
The GOOGLEFINANCE function
Below is a diagram that shows how the GOOGLEFINANCE functions works.
The Google Sheets GOOGLEFINANCE function description:
Syntax:
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])Formula summary: “Fetches current or historical securities information from Google Finance.”
GOOGLEFINANCE options / criteria
Ticker
The “Ticker” is the stock symbol that you want to get data from.
When typing / providing the “Ticker” symbol, the exchange is optional. So you can enter “NASDAQ:AAPL”, or you can simply enter “AAPL”.
The simplest GOOGLEFINANCE formula will only have a ticker, like this: =GOOGLEFINANCE(“AAPL”). This formula will default to showing the real-time price for the ticker indicated.
Attribute
There are a variety of “attributes” that you can specify with the GOOGLEFINANCE function, such as “Price”, or “Market Cap”. See further below for a full list of attributes. Notice that some attributes can be used for pulling real-time data, some can be used for pulling historical data, and some attributes can be used for both.
The “Attribute” is optional for real-time data, and if not provided, will default to “price”. But the attribute is required if fetching historical data. If you use the “price” attribute for historical data, then the attribute will default to “Close” (i.e. the closing price)
Start date & End date
If you are displaying historical data, you will need to indicate a start date and an end date (you must also include an attribute when pulling historical data). Specifying dates can be done in multiple ways which are taught in detail below… but overall you can choose to indicate dates by using the DATE function, or you can type dates into cells and then refer to those cells in the formula, or you can also use the TODAY function. Again, more on this below.
The start date and end date are optional, and if not provided the formula will display real-time data.
Interval
When pulling historical stock data, you can choose whether the GOOGLEFINANCE formula will show daily data, or weekly data. If you choose “Daily, the data will show for each trading day. If you choose weekly, the data will increment by weeks instead of days.
The “Interval” (Daily vs. Weekly) is also optional. The interval will default to “daily” if not specified.
The exchange
Usually you do not need to list the exchange for the stock symbol / ticker (Such as “NASDAQ), but you can include it if you want. Both of the formulas shown below have the same functionality. Notice that one of the formulas includes the exchange in the ticker symbol “NASDAQ:GOOG” and the other formula does not include the exchange with the ticker symbol “GOOG”.
=GOOGLEFINANCE(“GOOG”)
=GOOGLEFINANCE(“NASDAQ:GOOG”)
Real-time vs. Historical Data
As mentioned above, you can choose to pull real-time stock data, or historical stock data.
To pull real-time stock data, in your GOOGLEFINANCE formula, enter the “Ticker” as well as an “Attribute”, as shown in the formulas directly below (If you only include the ticker the attribute defaults to “price”).
=GOOGLEFINANCE(“GOOG”)
=GOOGLEFINANCE(“GOOG”,”price”)
To pull historical stock data, in your GOOGLEFINANCE formula, enter a start date and an end date after the ticker and attribute, as shown in the formulas directly below.
=GOOGLEFINANCE(“GOOG”,”price”,A1,B1)
=GOOGLEFINANCE(“GOOG”, “price”, DATE(2021,1,1), DATE(2021,12,31))
As you will see in the examples further below, when pulling historical data, Google Sheets will display two columns of data. One column shows the date, and the other column shows the specified attribute.
Using cell references with GOOGLEFINANCE
Remember that just like other formulas, you can use cell references with the GOOGLEFINANCE formula, such as when you have a ticker symbol entered into a cell, where you refer to that cell in place of typing the ticker symbol directly into the formula, such as =GOOGLEFINANCE(A2,A3)
In the formula directly below, the “Ticker” is entered into a cell (A1) and then cell A1 is being referenced in the formula to specify the ticker, rather than typing the ticker symbol directly into the formula.
=GOOGLEFINANCE(A1,”price”,B1,C1)
In the formula directly below, the “Start Date” and the “End Date” are entered into cells (B1 and C1), and then those cells are being referenced in the formula to specify the start / end dates, rather than entering the dates directly into the formula.
=GOOGLEFINANCE(“GOOG”,”price”,B1,C1)
If you want, you can use cell references in the same way to specify the “Attribute”.
Formulas to combine with GOOGLEFINANCE
There are other very useful functions that you can use along with the GOOGLEFINANCE function, such as the DATE function, and the TODAY function.
Use the TODAY function to specify start / end dates
As shown in the formula below, you can use the TODAY function with the GOOGLEFINANCE function, to set the end date to today’s date. You can also use subtraction with the TODAY function to set the start date (Also shown in the formula below).
With the TODAY function, simply enter the following formula into your spreadsheet to get the current date / today’s date: =TODAY()
In the formula below, the TODAY function (along with subtraction) is being used to set the start date to 30 days before today, and the end date is set to today’s date. So this formula will show historical data for the past 30 days.
GOOGLEFINANCE(“GOOG”,”price”,TODAY()-30,TODAY())
Use the DATE function to specify start / end dates
As mentioned above, you can enter dates into cells and then refer to those cells to specify the start date and the end date. But if you want to enter the dates directly into the GOOGLEFINANCE formula, you’ll need to use the DATE function, as shown in the example formulas below.
With the DATE function, first you enter the year, and then the month, and then the day of the month, like this: DATE(2021,10,31) This formula yields the date of October the 31st, 2021.
=GOOGLEFINANCE(“GOOG”,”price”,DATE(2021,10,31),today())
=GOOGLEFINANCE(“GOOG”, “price”, DATE(2021,1,1), DATE(2021,12,31), “DAILY”)
When fetching historical data, the GOOGLEFINANCE function will display multiple columns by default, including the attribute as well as the date.
Attributes for the GOOGLEFINANCE function
There are many different attributes that can be used with the GOOGLEFINANCE function, or in other words there are many different types of stock data that you can pull, and so the easiest way to show you these attributes is to provide information directly from Google Support.
Below is a link to a Google supported page that will give you lots of information about the different ways to use the GOOGLEFINANCE formula. Further below is a quote from the linked page, of exact documentation provided by Google.
https://support.google.com/docs/answer/3093281?hl=en
attribute is one of the following for real-time data:
“price” – Real-time price quote, delayed by up to 20 minutes.
“priceopen” – The price as of market open.
“high” – The current day’s high price.
“low” – The current day’s low price.
“volume” – The current day’s trading volume.
“marketcap” – The market capitalization of the stock.
“tradetime” – The time of the last trade.
“datadelay” – How far delayed the real-time data is.
“volumeavg” – The average daily trading volume.
“pe” – The price/earnings ratio.
“eps” – The earnings per share.
“high52” – The 52-week high price.
“low52” – The 52-week low price.
“change” – The price change since the previous trading day’s close.
“beta” – The beta value.
“changepct” – The percentage change in price since the previous trading day’s close.
“closeyest” – The previous day’s closing price.
“shares” – The number of outstanding shares.
“currency” – The currency in which the security is priced. Currencies don’t have trading windows, so open, low, high, and volume won’t return for this argument
attribute is one of the following for historical data:
“open” – The opening price for the specified date(s).
“close” – The closing price for the specified date(s).
“high” – The high price for the specified date(s).
“low” – The low price for the specified date(s).
“volume” – The volume for the specified date(s).
“all” – All of the above
Default settings / attributes for the GOOGLEFINANCE function
If not specified, the default attribute for real-time data, is “price”
For historical data, if you use the “price” attribute, the attribute will default to “Close” (i.e. the closing price)
If not specified, the “interval” will default to “daily”.
Now let’s go over examples of using the GOOGLEFINANCE function in various ways.
Timeframes and delays for the GOOGLEFINANCE function
Note that even “real-time” data will have a delay with the GOOGLEFINANCE function. Note that the data being displayed for real-time data is for the current trading day, and so when the market is closed the function will display the last data from the closing of the most recent trading day.
Real-Time stock price example
In this first example, we will keep it simple. What we are going to do is pull the real-time price for a stock with the GOOGLEFINANCE function. Remember that the GOOGLEFINANCE function will default the attribute to “price” if you do not specify an attribute, and so if you want you can simply indicate a stock symbol as the only criteria in the formula, and the formula will display the current price of the ticker symbol that you entered.
The task: Pull the current stock price for the stock TSLA (Tesla) into a Google spreadsheet
The logic: Use the GOOGLEFINANCE function to pull the real-time stock price for the ticker “TSLA”
The formula: The formula below, is entered in the blue cell (B2), for this example
=GOOGLEFINANCE(A2)
As you can see in the image above, the price of TSLA stock at the time of creating the example image, was $988.79.
Alternate formula with same functionality:
In the example image, the stock symbol “TSLA” is entered into cell A2, and the GOOGLEFINANCE formula is referring to cell A1 as the criteria for the “ticker”. No attribute was indicated, and so the formula automatically defaulted the attribute to display “price”.
However, both of the formulas below are alternate versions of the formula that do the same thing although they are built slightly differently.
The formula directly below specifies the attribute “price”, and will display the current price of the stock just like the formula that doesn’t have an attribute, because the one without the attribute defaults to “price” anyways. This formula will do the same thing as the formula in the example image.
=GOOGLEFINANCE(A2,”price”)
The formula below has the stock symbol entered directly into the formula, rather than entering the stock symbol in a cell and using a cell reference in the formula. This formula will do the same thing as the formula in the example image.
=GOOGLEFINANCE(“TSLA”)
Real-Time market cap example
You can pull more than just the price with the GOOGLEFINANCE function in Google Sheets. You can also pull other stock data, like “Market Cap”. In this example we will pull the current market cap of a stock with the GOOGLEFINANCE function.
(“Market cap” is the valuation of a company based on the total number of outstanding shares, and the current price per share. Shares x Price = Market Cap). This is just for education… you do not need to know this calculation. It simply helps you understand what market cap is.
The task: Get the market cap of the stock TSLA (Tesla)
The logic: Use the GOOGLEFINANCE function to pull the real-time market cap of the ticker “TSLA”
The formula: The formula below, is entered in the blue cell (B2), for this example
=GOOGLEFINANCE(A2,”MarketCap”)
As you can see in the example image above, at the time of creating the example image, the market cap of Tesla / TSLA was $1,021,662,987,071 (Over 1 trillion dollars).
Real-Time volume example
In this example, we are going to get the current trading day’s “volume” for a stock with the GOOGLEFINANCE function. The volume of a stock is how many shares have been traded (Buys + Sells) in a given period of time. When using the GOOGLEFINANCE function to pull the volume of the stock, it will show the volume for the current day (for real-time data).
The task: Get the volume (how many shares were traded) for the stock TSLA (Tesla)
The logic: Use the GOOGLEFINANCE function to pull the “volume” for the ticker “TSLA”
The formula: The formula below, is entered in the blue cell (B2), for this example
=GOOGLEFINANCE(A2,”Volume”)
As you can see in the image above, at the time of creating the example image, the volume for the stock TSLA was 11,512,113. In other words the number of shares that had been traded on that day, was 11,512,113 shares.
(Daily) Historical stock price example
Now let’s go over an example of pulling historical stock data with the GOOGLEFINANCE function. We are going to pull the historical prices for a stock.
When pulling historical data, the GOOGLEFINANCE function will display two columns of data (The date and the specified attribute). To pull historical stock data in Google Sheets, you must specify a start date and an end date after specifying the attribute.
In the example image, we are going to enter the start / end dates into cells, and then use cell references to specify the dates in the GOOGLEFINANCE function… but further below you’ll see alternative versions of the formula that will perform the same function, such as where the DATE function is used instead of cell references.
In this example image, we are not going to indicate an “interval”, and so the function will automatically default to “daily. Since we will be using the “Price” attribute for historical data, the attribute / data displayed will default to “Close”. In other words when you use the “Price” attribute for historical stock data, you will be shown the price that the stock closed / ended at, each day / week.
The task: Pull the daily stock prices for Tesla, for the entire year of 2021
The logic: Use the GOOGLEFINANCE function to pull the historical stock prices for the stock “TSLA”, with a starting date of 1/1/2021, with an ending date of 12/31/2021, and with a daily interval
The formula: The formula below, is entered in the blue cell (D1), for this example
=GOOGLEFINANCE(B1,”Price”,B2,B3)
As you can see in the image above, the daily stock prices for Tesla are being displayed in columns D and E, where the date is displayed in column D, and where the closing price is displayed in column E.
Notice in the image above, how cell E1 says the word “Close”, indicating that the closing price is being displayed, since we specified the attribute “Price” while pulling historical data. So in this formula, if you used the attribute “Close” instead of “Price”, the formula would do the same thing.
Alternate formulas with same functionality:
The formula directly below uses the DATE function to specify the start / end date, rather than entering the dates in cells and then using cell references in the formula (to specify dates).
=GOOGLEFINANCE(B1,”Price”,DATE(2021,1,1),date(2021,12,31))
The formula directly below specifies an interval of “Daily”, and which is what the formula will default to if the interval is not indicated… which is why the formula in the example image did not specify an “interval”, yet does the same thing as the formula below where the daily interval is specified.
=GOOGLEFINANCE(B1,”Price”,B2,B3,”Daily”)
(Weekly) Historical stock price example
Now let’s go over an example of pulling weekly historical data, where the data shown increments by 1 week for each row, rather than 1 day. To do this, all that we need to change is to specify a “weekly” interval.
The task: Pull the weekly stock prices for Tesla, for the entire year of 2021
The logic: Use the GOOGLEFINANCE function to pull the historical stock prices for the stock “TSLA”, with a starting date of 1/1/2021, with an ending date of 12/31/2021 (Use cell references to enter the dates), and with a weekly interval
The formula: The formula below, is entered in the blue cell (D1), for this example
=GOOGLEFINANCE(B1,”Price”,B2,B3,”Weekly”)
As you can see in the image above, the weekly stock prices for Tesla are being displayed in columns D and E, where the date is displayed in column D, and where the closing price is displayed in column E. Notice that the dates increment by 7 days for each row of data, instead of incrementing by 1 day for each row.
Alternate formula with same functionality:
The formula directly below does the same thing as the formula in the example image, except that the dates are entered with the DATE function, rather than using cell references to specify the dates.
=GOOGLEFINANCE(B1,”Price”,DATE(2021,1,1),date(2021,12,31),”Weekly”)
Using the TODAY function with the GOOGLEFINANCE function
A really useful formula that you can combine with the GOOGLEFINANCE function, is the TODAY function. The TODAY function fetches today’s date. If you want, you can use the TODAY function all by itself to simply display the date in a cell, or as is shown below you can use the TODAY function to set the end date for the GOOGLEFINANCE function, so that the formula always shows the most recent data / dates.
The task: Show the daily prices for Tesla, from 4/9/2022 until today
The logic: Pull the daily historical stock prices for the stock “TSLA”, with a starting date of 4/9/2022, where the ending date is today’s date (Use the TODAY function to specify the “End Date”)
The formula: The formula below, is entered in the blue cell (D1), for this example
=GOOGLEFINANCE(B1,”Price”,B2,Today())
Notice that in this example, the TODAY function is entered for the “End Date”. As you can see in the image above, the formula is displaying the daily stock prices for Tesla, from 4/9/2022, up until the current date, which at the time of creating the example image, was 4/20/2022.
Note that when getting historical data with the GOOGLEFINANCE function, only closed trading days will display, or in other words the current trading day will not display in the historical data until the market is closed.
Also, note that if you wanted… instead of entering the TODAY function into the formula itself, you could have entered the TODAY function into one of the spreadsheet cells, and then like in the other example you could then refer to the cell with the TODAY function inside of it, to specify the date for the GOOGLEFINANCE function.
Using subtraction when setting dates to get the price for the last x number of days
If you want, you can also use the TODAY function (along with simple subtraction) to specify a start date for the GOOGLEFINANCE function, where the formula will show you stock data for the past x number of days / weeks.
For example, if you set an ending date of “Today”, with a starting date of “Today minus 9 days”, then the GOOGLEFINANCE function will always display the last 9 days of data. This is incredibly useful when you want to show the most recent data without having to constantly change dates.
The task: Show the price for Tesla stock over the last 9 days
The logic: Pull the daily historical prices for the stock “TSLA”, where the starting date is 9 days before today’s date, and where the ending date is today’s date
The formula: The formula below, is entered in the blue cell (C1), for this example
=GOOGLEFINANCE(A2,”Price”,today()-9,Today())
As you can see in the image above, the GOOGLEFINANCE formula is displaying the daily prices for Tesla, for the last 9 trading days.
Using the “All” attribute to pull the following stock data: (Open, High, Low, Close, Volume)
If you want, you can use the “All” attribute to pull the open price, low price, high price, closing price, and the volume…. all in one formula.
The task: Display all of the following stock data with a single formula: Open, High, Low, Close, Volume
The logic: Use the “All” attribute with the GOOGLEFINANCE function to pull in all of the following attributes with a single formula: Open, High, Low, Close, Volume
The formula: The formula below, is entered in the blue cell (A2), for this example
=GOOGLEFINANCE(“GME”,”all”,today()-30,today())
As you can see in the image above, the formula in cell A2 has pulled in multiple attributes / data types all at once, by using the “All” attribute. First the date is displayed, then the open price, the high price, the low price, the closing price, and then the volume.
Candlestick chart with historical GOOGLEFINANCE data
The “All” attribute (as shown in the example above) pulls in all of the needed data to create a candlestick chart. The candlestick chart requires the following data: Date, Open, High, Low, Close
Below is an example of a candlestick chart that is connected to the data from the previous example, which was pulled in with the GOOGLEFINANCE function (by using the “All” attribute).
Pulling crypto prices with GOOGLEFINANCE
The GOOGLEFINANCE function can also be used to pull crypto prices, although the number of cryptocurrencies that you can pull with this method is very limited. I’ll go over a quick example of pulling the price of Bitcoin here, but check out my entire lesson on pulling crypto prices in Google Sheets to learn a lot more. Note that the ticker symbol for crypto must include a pair of currencies, such as the Bitcoin / U.S. Dollars pair, which is written as BTCUSD.
The task: Get the current price of Bitcoin
The logic: Use the GOOGLEFINANCE function to pull the real-time price of Bitcoin (BTCUSD)
The formula: The formula below, is entered in the blue cell (B2), for this example
=GOOGLEFINANCE(A2)
As you can see in the image above, the GOOGLEFINANCE function is displaying the price of Bitcoin, which at the time of creating the example image, was $41,392.70
Pop Quiz: Test your knowledge
Answer the questions below about the GOOGLEFINANCE formula, to refine your knowledge! Scroll to the very bottom to find the answers to the quiz.
Get your free Google Sheets function cheat sheet
Question #1
Which of the following formulas pulls real-time price?
- =GOOGLEFINANCE(“GOOG”,”price”)
- =GOOGLEFINANCE(“GOOG”, “price”, DATE(2021,1,1), DATE(2021,12,31))
Question #2
Which of these functions pulls historical prices?
- =GOOGLEFINANCE(B1,”price”)
- =GOOGLEFINANCE(B1,”Price”,B2,B3)
Question #3
True or False: If you do not specify an attribute when pulling real-time data, the GOOGLEFINANCE function will default to showing the “price” attribute
- True
- False
Question #4
True or False: You can use cell references to specify the criteria in the GOOGLEFINANCE function
- True
- False
Question #5
Which of the following formulas shows weekly historical data
- =GOOGLEFINANCE(B1,”Price”,B2,B3,”Daily”)
- =GOOGLEFINANCE(B1,”Price”,B2,B3,”Weekly”)
Answers to the questions above:
Question 1: 1
Question 2: 2
Question 3: 1
Question 4: 1
Question 5: 2
