Indzara

Stock Portfolio Tracker 2024 Google Sheets Template

In this article, we will be discussing the Stock Portfolio Tracker template that is used to track your stock portfolio’s performance.  

The template will allow entering all Stock transactions in one single table and immediately view the following in the Stock Portfolio Dashboard.

  • Current market value of all your stocks and the Cash balance
  • Today’s market performance and your portfolio performance
  • List of all your stocks with performance data (Quantity, Gain/Loss, % Gain/Loss, etc.)
Stock Portfolio Dashboard Google Sheet Template
Stock Portfolio Dashboard Google Sheet Template

The template can handle 7 types of stock transactions  (Buy, Sell, Cash Deposit, Cash Withdrawal, Dividend Payout, Dividend Reinvestment, Fees).

Download Now

How to track your Stock Portfolio in Google Sheets

The template has two visible sheets

  1. Transactions – where you enter all the stock transactions
  2. Portfolio Dashboard – which is fully automated and displays the Portfolio dashboard

Transactions

In the transactions sheet, we begin by entering the Starting cash balance at the top.

Entering Stock transactions in Google Sheets
Entering Stock transactions in Google Sheets

Then we can enter each transaction in the table. In the above screenshot, we have entered several transactions.

Enter Transaction date and Exchange Code:Ticker symbol of the stock you traded.

Kindly ensure to enter the correct code and ticker symbol for Google to correctly identify the stocks.

You can select the stock you are interested in.

Choose the Transaction Type.

There are 7 possible transaction types supported in the template.

Transaction Types in Stock Portfolio Tracker

  1. Buy: When buying a stock – this will reduce the Cash balance and increase the stock quantity and thus your portfolio’s Market Value
  2. Sell: When selling a stock – – this will increase the Cash balance and decrease the stock quantity and thus your portfolio’s Market Value
  3. Cash Deposit: When adding Cash from external accounts (like your personal checking or savings account in bank) into your stock management platform which would then be available to buy stocks
  4. Cash Withdrawal: When you are taking Cash from the Stock Management platform to an external account (like your personal checking or savings account in bank)
  5. Fees: When any fees are applied which reduces money from your Cash balance
  6. Dividend Payout: When Dividend is issued that increases money in your Cash Balance
  7. Dividend Reinvestment: when Dividend is issued that increases stock quantity of the stock that issued Dividend. Cash balance is not impacted.
Sample Stock Transactions
Sample Stock Transactions

Type in the number of Units and Price of the Stock for the transaction.

Valid? column checks for whether the required information is entered.

For Buy, Sell and Dividend Reinvestment transactions, Ticker symbol and Units field are required.

For Cash Deposit, Cash Withdrawal, Dividend Payout and Fees, Units are not required. Price field is required.

The Valid? column also checks whether the Price of the stock is between the Low and the High for the day.

If you purchased the stock on a discount such as Employee discount, which brings it below the Low price for the day, you can ignore the Valid column.

Amount column calculates the total amount for the transaction.

Cash Balance reflects the running cash balance after each transaction.

For this to be accurate, please enter all transactions in chronological order.

Stock Sales, Cash Deposit and Dividend Payout increase the cash balance, while the Stock Buys, Cash Withdrawal and Fees reduce the cash balance. Dividend Reinvestment does not impact the cash balance.

Portfolio Dashboard

Stock Portfolio Dashboard – Components
Stock Portfolio Dashboard – Components

On the left side of the Dashboard, the overall summary is presented.

Stock Portfolio Dashboard Summary in Google Sheets
Stock Portfolio Dashboard Summary in Google Sheets

On the right side, we have the individual stocks and their performance.

Individual Stocks in Stock Portfolio
Individual Stocks in Stock Portfolio

Calculations used

  • Current Quantity = Quantity Bought – Quantity Sold + Quantity Dividend Reinvestment
  • Current Value = Current Quantity * Last Price of Stock
  • Today’s Gain/Loss = Current Value – (Previous Close * Current Quantity)
  • % Today’s Gain/Loss = Today’s Gain/Loss / (Current Value – Today’s Gain/Loss)
  • Total Gain/Loss = Current Value – Invested Amount
  • Invested Amount = Total Amount used to buy the stock – Total Amount used to sell the stock
  • % Total Gain/Loss = Total Gain/Loss / Invested Amount
  • Cash Available = Starting Cash Balance – Total Amount used to Buy Stock – Cash Withdrawal – Fees + Total Amount of Selling stock + Cash Deposit + Dividend Payout

Timezone

On the top left you can see the last trade time of the first stock in your list.

The default time that Googlefinance uses is UTC.  The offset is set to -7 hrs, to convert to pacific time zone.

Visit https://savvytime.com/converter/utc to view offset.

Feedback

Please share your feedback on this template in the Comments below. If you find this useful, please share with your friends who may benefit from this content.

6 Comments

  • Hello,
    the transactions after row 103 are not being updated in the Dashboard.
    is there something that I need to change?
    thnaks

    Reply
    • Thank you for showing interest in our template.

      By default the template is limited to 100 transaction. You can expand the same by following 2 simple steps mentioned below:

      1. Goto Data ribbon and open Named Ranges.
      2. For the named range L_TXN_AMOUNT, L_TXN_PRICE, L_TXN_TICKER, L_TXN_TYPE, and L_TXN_UNITS you need to change the row number 103 to 1000 to expand it for 1000 rows.

      Best wishes.

      Reply
    • Thank you for using our template.

      It will show invalid, when the entered price is greater than the transaction date’s High value or less than the transaction date’s Low value. The price of the purchased stock must be between the High and Low. If this is not the issue, pleas share more details with some screenshots highlighting the issue at the below link to assist further:
      https://support.indzara.com/support/tickets/new

      Best wishes.

      Reply
  • Hi,
    It would be great if you could also add CAGR of Portfolio and Stock wise in the Dashboard.

    Reply
    • Thank you for showing interest in our template.

      I have added your requirement to our Future Development. We will update you once we add the requested feature in our template. If you want to process it as a customization in the existing sheet for a fee, please write to us at the below link for estimation:
      https://support.indzara.com/support/tickets/new

      Best wishes.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *