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.)
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
- Transactions – where you enter all the stock transactions
- 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.
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.
You can select the stock you are interested in.
Choose the Transaction Type.
There are 7 possible transaction types supported in the template.
- Buy: When buying a stock – this will reduce the Cash balance and increase the stock quantity and thus your portfolio’s Market Value
- Sell: When selling a stock – – this will increase the Cash balance and decrease the stock quantity and thus your portfolio’s Market Value
- 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
- 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)
- Fees: When any fees are applied which reduces money from your Cash balance
- Dividend Payout: When Dividend is issued that increases money in your Cash Balance
- Dividend Reinvestment: when Dividend is issued that increases stock quantity of the stock that issued Dividend. Cash balance is not impacted.
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
On the left side of the Dashboard, the overall summary is presented along with the Stock Portfolio mix by industry.
On the right side, we have the individual stocks and their performance.
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?
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.
56 Comments
Is there a limit to the number of rows one can add to transactions portfolio? I used your template, did some more work to generate views I need and it was working fine. However, from sometime, it is now throwing error “Excel ran out of resources while attempting to calculate one or more formulas”. I have about 670 rows in transaction sheet. I also notice that when I delete rows and keep it max to 512 rows, it works but given another error ” We refreshed some of your data, but we couldn’t refresh all of it because we changed or removed it on our side.”
Thank you for using our template.
By default the template does not have row limitation.
Regarding Excel ran out of resources while attempting to calculate one or more formulas,
This error depends on the system performance. Each transaction entry is a separate call to Microsoft server to get the stock related information from Microsoft server.
Regarding We refreshed some of your data, but we couldn’t refresh all of it because we changed or removed it on our side,
I tried deleting rows but I am unable to replicate the highlighted issue. Please share your sheet to us at the below link to check further:
https://support.indzara.com/support/tickets/new
Best wishes.
It is almost perfect. But it only handles one currency.
But excellent work. You are really skilled.
Thank you for showing interest in our template and sharing your valuable input.
I have added your input to the future development. We will update you once a new version of the template is released.
Thank you for sharing your feedback.
Best wishes.
Hi,
This is a great tracker, only problem is I’m based in the UK. I can format the currency to £ which is ok, however is it possible to change the dashboard formulas to display as GBX (pence) and not GBP (pounds)? The dashboard is password protected so I can’t edit it – any help would be appreciated.
Thank you
Hi there,
I was really impressed by your template. Unfortunetley, when I downloaded Stock Portfolio Tracker & two stock comparison files they don’t open as a error message shows mentioning the following:
We found a problem with some content in ’30-Stock-Portfolio-tracker_V_1_1.xlsx’. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.
Hi,
very nice job. In my opinion, (I did and it works great) you should add a column for FX so that you are not “forced” to buy in a single currency.
well done.
best regards
Patrick
Thank you for showing interest in our template and sharing your valuable feedback.
Sure, we will consider your input for future enhancement.
Best wishes.
One thing that doesn’t seem to work is when you sell all or part of a security’s holding, the total gain percentage is not calculated correctly. For example, I added the following transactions.
10/8/21 – ADM 15 shares at $63.47 and then sold them on 2/17/22 for $75.36. If you sell all the shares, it says you have a total gain of -100% . If you sell less then the entire lot, the total return changes for each share you sell and once you make a profit it goes into negative percentages.
I am unable to replicate the highlighted issue. Hence requesting to share your sheet to support@indzara.com with the highlighted data along with the issue to check further.
Best wishes.
I observe the exact same thing as Patrick Dwell mentions in the Feb 20, 2022 post. When I add a transaction to sell all the shares of a stock it shows up as a total gain of -100% in the “% Total Gain/Loss” column, regardless of whether it is a gain or loss. It looks like when all the stock is sold the “Invested Amt” column becomes equivalent to the “Total Gain/Loss” column but with the opposite sign and thus the calculation of the “% Total Gain/Loss” column always leads to -100%. Otherwise this spreadsheet is really great. I couldn’t figure out how to get the Time Offset to change on the Help page but that didn’t seem to matter.
Thank you for using our template and sharing your valuable feedback.
I have modified the template to show only the ticker symbols with inventory on hand in the Portfolio Dashboard and sent the template to your email ID. Please review the same and let us know your valuable feedback.
Best wishes.
Hi
How can we get the fix you made in the comments above with Andy and Patrick? Just by downloading latest copy on website? Appreciate your response.
And is it possible to get realized gain/losses with Short Term and Long term categorization?
Thank you for showing interest in our template.
I have sent the file to your email ID.
Best wishes.