How to Track Stocks in Google Sheets
If you want to keep track of the stocks and securities that interest you, you can obtain both real-time and historical data in Google Sheets. Using a Smart Chip or built-in function, you’ll always be up to date on what’s happening with your favorite entities from Google Finance.
How to Use the Finance Smart Chip
With the introduction of the Finance Smart Chip in Google Sheets in early 2023, you can quickly obtain details for stocks, mutual funds, and currencies. The data you see comes from Google Finance.
- Enter the ticker symbol into a cell in your sheet.
- With the cell selected, do one of the following to insert the Smart Chip:
- Select “Insert -> Smart chips” from the menu and choose “Finance” in the pop-out menu.
- Right-click the cell, move to “Smart chips,” and pick “Finance” in the pop-out menu.
- When Google locates the ticker and data, you’ll see the Smart Chip in the cell.
- Hover your cursor over the Smart Chip to see the financial data. The details you see depend on the type of entity. For example, below you can see data for Amazon that includes the name, listing index, price, and market cap.
- To see more, click the “View details” icon (boxed arrow) on the bottom right.
- You’ll be directed to the company’s page on Google Finance.
Did you know? if you trade on multiple different exchanges, you can also convert currencies in Google Sheets.
Remove the Finance Smart Chip
If you want to remove a Finance Smart Chip you’ve added to a cell, do one of two things:
- To remove the chip but keep the ticker symbol in the cell, right-click the cell, move to “Smart chips,” and pick “Remove smart chip” in the pop-out menu.
- To remove both the chip and the symbol from the cell, select the cell and press your Delete key.
How to Use the GOOGLEFINANCE Function
If you want to obtain specific or historical data, use the GOOGLEFINANCE function in Google Sheets. When you use the formula for this function, you’ll also receive data directly from Google Finance.
The syntax for the formula is GOOGLEFINANCE(symbol, attribute, start_date, end_date, interval), where only the first argument for the ticker symbol is required. You should place each argument in quotation marks.
Before we go through examples, let’s take a look at the arguments:
- Symbol: Google requires that you use both the exchange and ticker symbols for the most accurate results. For instance, you would enter “NASDAQ:AMZN.” If you only use the ticker symbol, “AMZN,” Google will choose an exchange for you. You can also use a cell reference for this argument.
- Attribute: this is the specific piece of data you want to obtain. The default is “price” if left blank. There are currently over 40 attributes that you can select, depending on whether you want real-time, historical, or mutual fund data. While we are providing a few common attributes in our examples, you can view the full list on the Google Docs Editors Help page.
- Start_date: for historical data, you can enter the start date. If you include the argument but exclude the next argument for “end_date,” you’ll receive that particular day’s data.
- End_date: for historical data, you can enter the end date or the number of days from the start date.
- Interval: also for historical data, you can enter “Daily” or “Weekly” for the frequency of the data. Alternatively, you can use the corresponding number 1 or 7, respectively.
Now that you know the syntax for the function’s formula along with the arguments you can use, the following are a few examples of the GOOGLEFINANCE function.
GOOGLEFINANCE Function Examples
With the following formula, we are obtaining the current day’s price for Amazon (AMZN). Remember that price is the default attribute if the argument is blank.
=GOOGLEFINANCE("NASDAQ:AMZN")
Using this next formula, we are obtaining the current 52-week high price for Amazon.
=GOOGLEFINANCE("NASDAQ:AMZN","HIGH52")
Now let’s get some historical data for Amazon. With the following formula, we are obtaining the low price for January 1, 2020.
=GOOGLEFINANCE("NASDAQ:AMZN","LOW","1/1/2020")
In another example, we are obtaining the opening price with the same start date and an end date of January 10, 2020.
=GOOGLEFINANCE("NASDAQ:AMZN","OPEN","1/1/2020","1/10/2020")
In this example, we are obtaining the closing price with the same start date, an end date of June 1, 2020, and an interval of weekly.
=GOOGLEFINANCE("NASDAQ:AMZN","CLOSE","1/1/2020","6/1/2020","WEEKLY")
Frequently Asked Questions
Wasn’t Google Finance discontinued?
The Google Finance mobile app was removed from Google Play in 2015, and the API for Google Finance is no longer available to users. However, the Google Finance website was redesigned and relaunched in 2020 with additional features, and the GOOGLEFINANCE function continues to provide financial data in Google Sheets.
What other types of financial functions does Google Sheets support?
You can do everything from calculate accrued interest to the annual yield of a security in Google Sheets.
For a full list of financial functions, head to the Google Sheets Function List. You can leave the keyword field blank and simply select “Financial” in the “Narrow by” drop-down menu to view the entire list.
Does Microsoft Excel have a stocks feature?
Microsoft 365 subscribers can use the Stocks data type.
Enter a ticker symbol into a cell, go to the “Data” tab, and select “Stocks” in the Data Types section. Select the correct entity in the sidebar that displays, then use the “Insert Data” button that appears next to the cell to insert the stock data you want.
For historical data, you can also look into the STOCKHISTORY function in Excel.
Image credit: Pixabay. All screenshots by Sandy Writtenhouse.
- Tweet
Leave a Reply