Indzara

Stock Portfolio Tracker 2024 Excel Template

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

The Stock portfolio tracker Excel 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
  • Portfolio mix by instrument type and industry
  • 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 Excel Template (click to view in full size)

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

Download Now

Video Demo

How to track your Stock Portfolio in Excel

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 Excel (click to view in full size)

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

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

If Excel does not recognize your stock symbol, it will pop up this data selector box.

Select-the-stock-from-the-list stock portfolio tracker excel
Select-the-stock-from-the-list — stock portfolio tracker excel

You can select the stock you are interested in.

Choose the Transaction Type.

There are 7 possible transaction types supported in the template.

stock portfolio tracker excel
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 (click to view in full size)

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 tracker excel
Stock Portfolio Dashboard – Components (click to view in full size)

On the left side of the Dashboard, the overall summary is presented along with the Stock Portfolio mix by industry.

Stock Portfolio Dashboard Summary in Excel
stock portfolio tracker excel
Stock Portfolio Mix by Industry

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

Individual Stocks in Stock Portfolio (click to view in full size)

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

Requirements

To pull live Stock data from the services you would need Microsoft 365 Subscription Plans for PC and Web

Timezone

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

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

You can modify this if you need the time in a different time zone in the hidden Help sheet.

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

How to refresh data?

How to refresh Stocks data
How to refresh Stocks data

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.

https://indzara.com/stock-market-templates/

54 Comments

    • Thank you for showing interest in our template.

      Currently, the template is not available in Google Sheet but you can upload the template in Microsoft OneDrive and use Excel online.

      Best wishes.

      Reply
  • This is such a good spreadsheet! This is even better than the apps I download! Just wondering how do I display my total gain/loss in %?

    Reply
    • Thank you for sharing your valuable feedback.

      Total Gain/loss % is available in Column N. Following is the method used to calculate Gain/Loss %:

      Total Gain Loss / (Current value – Total Gain Loss)

      Best wishes.

      Reply
  • How do I set this up in a different currency? I would like this in euro

    Reply
    • Thank you for showing interest in our template.

      You will have to select the cells that holds the currency and then press CTRL+1 to change the currency value from Dollar to Euro.

      Best wishes.

      Reply
  • I’m happy I found your stock market template. Nice!
    Would you consider adding “stock split” to transaction type?

    Reply
    • Thank you for sharing your valuable feedback.

      We have gathered your feedback and we will make improvements in the next version.

      Best wishes.

      Reply
  • It does not read the ticker symbol.. any troubleshooting ideas please?

    I really like your work!

    Reply
    • Thank you for showing interest in our template.

      The template is supported only with the Microsoft 365 Excel version and if you using the Excel 365 version, you can right-click -> Data type -> change and search for the stock symbol. If you are still facing issue, requesting to share your sheet with the required symbol to support@indzara.com.

      Please note: Microsoft Excel 365 Stock Data type is at initial launch and some symbols and data are yet to be added.

      Best wishes.

      Reply
    • Thank you for using our template.

      The password to unprotect the sheet is indzara.

      Best wishes.

      Reply
  • The stock portfolio tracker is just what I was looking for! Thank you so much for making it available.
    My only issue is with the offset to make the date and time appear to be my (US/Central) time zone. No matter what I put in the offset cell I always get UTC displayed. This even happens with the delivered -9 in offset. I am pushing Data/Refresh All after making offset changes.

    Reply
    • Thank you for highlighting the issue.

      Requesting to replace the formula in cell D8 on Help tab with the below formula:
      =IFERROR(D7.[Last trade time]+(D9/24),"")

      We will update the live template as soon as possible.

      Best wishes.

      Reply
  • sir your templates all are awesome . if possible can provide indian stock version , changing into INR currency was difficult I felt

    Reply
  • Excellent! I would like to know how You bilt the portfolio tracker step by step. Do You have a course…Or If I want to add statistical measures to the tracker…

    Thank You again,

    Eduardo

    Reply
    • Thank you for showing interest in our template.

      Currently, we do not have a course on creating this template. We take customization projects for additional fee, requesting to share your requirement to support@indzara.com for estimation.

      Best wishes.

      Reply

Leave a Reply

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