asterisk-solid All courses are temporarily held live online with an extended free retake period. Learn More.

window-close-line
Hero image for Google Sheets Stock Portfolio Tracker

Google Sheets Stock Portfolio Tracker

Free Portfolio Template & Tutorial

This tutorial will teach you how to create a stock portfolio tracker using Google Sheets. Using Google Sheets' stock tracking functionality and some basic Excel tricks, we’ll have an automatically updating spreadsheet to track our performance and tell us when we want to sell our stocks.

See here for Stock Tracker Template (make a copy of the template to create your own)

The Columns

  • Ticker: This column will contain the stock ticker symbol for each stock in our portfolio.

  • Avg. Cost: The average cost per share that we purchased each of our stocks at.

  • Current Price: Automatically updated column with the current price of each stock using the GOOGLEFINANCE function in Google Sheets

  • Day Change %: Percentage change from previous day's close

  • Total Change %: Our total percentage gain or loss from each stock

  • Shares: The number of shares we have of each stock

  • Equity: The total equity (investment or ownership) in dollars that we have in each stock

  • 52 Week High: The highest the stock price has been in the past 52 weeks

  • Sell Price: Our personal choice of the price we would be willing to sell our shares at for each stock

  • Action: Color-coded cells that represent the decision of whether or not to sell our shares of a given stock. If the current price of the stock is above our sell price then we should sell, otherwise, we will hold.

The Formulas

  • Current Price =GOOGLEFINANCE(‘Ticker’, “price”) 

  • Day Change % =GOOGLEFINANCE('Ticker', "changepct")

  • Total Change % = ‘Current Price’/’Avg Cost’ - 1

    • You can use conditional formatting here to get a visual representation of the scale of your gains and losses relative to one another

  • Equity = ‘Shares’ * ‘Current Price’ 

  • 52 Week High =GOOGLEFINANCE('Ticker', "high52")

  • Action =IF(‘Current Price’>’Sell Price’, “Sell”, ”Hold”)

    • Then use conditional formatting to color-code “Sell” and “Hold” with different colors

Stock Tracker

Note: This is only for information purposes and not meant to be taken as financial advice.

Learn more about stock marketing investing, Excel, and finance in our related courses: