The key to making your own stock tracker in Google Sheets is the GOOGLEFINANCE formula. The GOOGLEFINANCE formula is a versatile tool that allows you to specify which piece of data you'd like to import into a cell on your spreadsheet.
The most basic use of GOOGLEFINANCE is to import the current price of a stock. To do so, you'll use the following instructions.
- Select a cell on your Google Sheet.
- Type "=GOOGLEFINANCE(" (don't forget the equal sign)
- Then, type the ticker of the stock you want the price of in quotes. For example, if you want to get the price of Amazon shares, type "AMZN" (including the quotes).
- Close your parentheses. Type ")"
- Hit "Enter". After loading, the sheet will display the most recent price of your stock.
Note that grabbing the stock price is the default function of GOOGLEFINANCE. However, you can specify many more attributes using the function to populate a full dashboard for your portfolio.
You might build a whole spreadsheet containing the ticker, the name of the company, how many shares you own, the price of the stock, price changes over the last trading day, the current value of your shares, the change in the value of your holding over the last trading day, and more. You'll see how to do that in the next section.
Google Finance formulas to use
To build out your portfolio tracking sheet, you'll need to know a little more about how the GOOGLEFINANCE formula works.
After you type in "=GOOGLEFINANCE" and specify the ticker you want, you can also specify any one of a number of attributes you'd like to collect. You simply put a comma after specifying the ticker and type in the attribute. You'll find a full table of useful attributes below.
You may also specify a start date, end date, and interval after the attribute, which can be useful in some cases.
Then, you can manipulate the data in your sheet to provide important information, like the total value of your stock holdings or how much your entire portfolio gained or lost over the past day.
Here's an example of a basic portfolio dashboard.
Unfortunately, you might not be able to get every piece of data through the Google Finance formula. One of the biggest omissions for stocks is dividends. But you should be able to get most of what you need to build a good dashboard to track your stocks.
