Google Sheets Stock Portfolio Tracker

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

If you want to learn to do this in Excel check out our Excel Stock Portfolio Guide.

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:

• Stock Market Investing class 
• Financial Modeling classes
• Finance classes