You may not realize it, but Google offers a feature that allows you to track actions in Google Sheets. Whether you want real-time prices or historical information, just enter the function, the stock symbol and the attributes.
This is a good way to keep track of the stocks that interest you or to check their opening, closing, high or low over time. We will show you how to introduce the function, use some of the most common attributes and we will indicate the complete list of real-time, historical and investment fund attributes for those specific scenarios.
Using Google Finance to track actions on Google Sheets
The function that you will use to obtain the data of the actions is GOOGLEFINANCE. Thus, you will select a cell, you will enter an equal sign followed directly by GOOGLEFINANCE, and then you will include the stock symbol and optional attributes.
The syntax of the formula is
= GOOGLEFINANCE (ticker, “attribute”, DATE (start_date), DATE (end_date | number_of_days), “interval”)
As you can see, the only requirement of the function is the stock symbol. Everything else you see in the formula is optional. And then we will review these options.
Google also recommends that you precede the stock symbol by the stock symbol to get the exact results you want. However, you can leave the stock market out of the formula and let Google pick one for you.
As an example, if you want the current Google quote, you can enter any of the following options
=GOOGLEFINANCE("GOOG")
=GOOGLEFINANCE("NASDAQ:GOOG")
Attribute insertion
After the stock symbol, you can enter an attribute for the exact data you want, such as price, high, low, or volume. The attribute is enclosed in quotation marks.
For example, if you want Apple’s market opening price, you would enter it:
=GOOGLEFINANCE("AAPL", "priceopen")
Available attributes vary depending on whether you want real-time, historical, or mutual fund data. These are some of the most common attributes:
- price: in real time
- priceopen: in real time
- high: real time and historical
- low: real time and historical
- volume: real time and historical
- marketcap: in real time
- tradetime: real time
- change: real time and mutual funds
- changepct: real time and mutual funds
- returnytd: investment funds
- netassets: investment funds
For a complete list of attributes for real-time, historical, and mutual fund data, go to GOOGLEFINANCE function help page.
Add dates
If you want to go back in time, you can add dates to the formula. You can enter a start date with or without an end date. If there is no end date, you will simply get the data of the start date. You can also enter a start date with a number of days from that start date or use “TODAY”.
Dates must be in parentheses and the start date always comes first. Here are a couple of examples.
This formula provides Google’s price each day from 1/1/21 to 2/1/21.
=GOOGLEFINANCE("GOOG", "price", DATE(2021,1,1), DATE(2021,2,1))
This formula provides Apple’s price today and in the last 30 days.
=GOOGLEFINANCE("AAPL", "price", TODAY()-30, TODAY())
Include an interval
Another optional piece that you can add to the GOOGLEFINANCE function is an interval. This is useful for viewing the prices, maximums or minimums by day or week within a range of dates.
Here are a couple of examples.
This formula provides the Microsoft minimums for each day in January 2021.
=GOOGLEFINANCE("MSFT", "low", DATE(2021,1,1), DATE(2021,1,31), "DAILY")
This formula provides Microsoft’s maximums for each week from January to August 2021.
=GOOGLEFINANCE("MSFT", "high", DATE(2021,1,1), DATE(2021,8,31), "WEEKLY")
Get a quick current price
If you have a spreadsheet in Google Sheets with the stock symbols already entered, you can get a quick, real-time price using the function and a cell reference.
For example, if you have the Apple stock symbol in cell A2, you would use:
=GOOGLEFINANCE(A2)
This would show the current Apple stock price in the cell with your formula.
About data obtained from Google Finance
Although most of the time you will see the stock data on your sheet update before your eyes, Google claims that the information can be delayed up to 20 minutes. Also, data is not obtained from all stock exchanges.
Stock information at your fingertips on Google Sheets
If you’re looking at the market, have an interest in a particular company, or are just curious to see how prices have changed, open up Google Sheets and easily start collecting stock details. Do you want to try it?
