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
Impressive work.
I do have some additional requirements such as growth vs value, outlook and other yahoo API info.
Are you open to work on that?
Thank you for sharing your valuable feedback.
We take customization project for a fee. Please share your requirement to support@indzara.com for estimation.
Best wishes.
How to change date and time as per Indian Standard Time? I tried to unhide help and then changed -7 to 5.5 but still date and time remains unchanged in Microsoft 365.
How can I remove dollar symbol on dashboard in coloured cells of Market Value, Total gain/loss and case. When I tried to change the currency by selecting column and also cell and ctrl+1, selecting currency, still it remains unchanged. When I double clink on the cell it says- Text can’t be edited because of certain property settings. To edit the text, open Excel in the desktop app.
Regarding trade time,
Thank you for highlighting the issue. Requesting to add “+(D9/24)” (Without Double quotes) at the end of the formula in cell D8 on hidden help tab to update the time.
Regarding dollar symbol of Market Value, Total gain/loss and case,
Change the currency symbol by pressing crtl+1 in cell D33, D35, D37 and D40.
Best wishes.
In the validity column, it is showing as invalid. The low and high of the transaction date is not showing. The stock is getting detected but low and high not updating.
Currently, Microsoft Excel stock data type is at initial launch and all values are not yet available for all ticker symbols in Microsoft Database. Requesting to share the ticker symbol that you are trying to implement, to check the same on our end.
Best wishes.
Hi I want to use it to track NSE, BSE in India. But how to make changes to $ symbol, indexes that are displayed in dashboard, time zone etc.
Also in the beginning, when I was deleting the stock names in the list so to start, it was not able to detect any new symbols that I was entering. Does deleting remove formula altogether. How should I start using it.
Thank you for showing interest in our template.
Following is the procedure to get started with the template.
1. Delete the data in the column A to E in transactions tab.
2. Change the currency format by selecting the currency cells and press CTRL+1 -> Currency -> Select the required symbol
3. Enter your ticker symbol in column B on transaction tab.
4. Then select the cell – Goto Data ribbon – Select Stock. Excel will then auto pick the entered ticker symbol’s stock data according the data available in the Microsoft stock database.
Best wishes.
This is a great template, but I can’t seem to get the dashboard to display my holdings. They are all currently securities from the TSX – Toronto Stock Exchange
Thank you for sharing your valuable feedback.
If you have Excel 365 subscription and still the dashboard is not populated at your end, requesting to share your sheet to support@indzara.com to check further.
Best wishes.
Hi, very nice template.
I am on 365 subscription and running on an iPad.
When I try adding a new transaction and enter the ticker, it just takes what ever I type and does not recognize the ticker symbol.
Can you advise?
Thank you for sharing your valuable feedback.
Currently, we have not tested on iPad. Hence requesting to share the following details to support@indzara.com to check further:
1. Right click (Long press) on the new cell and select datatype->change. If it is not available, requesting to provide a screenshot of the same.
2. Goto Data ribbon and check whether stock datatype is available.
3. If the existing cell with stock datatype is providing the data, requesting to copy the cell and paste it in the new row and try right-click->data type->change.
Best wishes.
what is the password to unprotect the excel sheet??
Thank you for showing interest in our template.
The password to unprotect the sheet is indzara.
Best wishes.
Getting Error While Opening Excel File. Any Advise Please ?
Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
Removed Feature: Worksheet properties from /xl/workbook.xml part (Workbook)
Removed Feature: RichData from /xl/richData/rdrichvalue.xml part (Rich Data Types)
Repaired Records: Cell information from /xl/worksheets/sheet1.xml part
Repaired Records: Cell information from /xl/worksheets/sheet3.xml part
Repaired Records: Cell information from /xl/worksheets/sheet4.xml part
Thank you for showing interest in our template.
I am unable to replicate the issue in our end. The template is currently supported only on Excel 365 subscription. If you have Excel 365 subscription and still you are unable to open the sheet, requesting to share screenshot of Excel version and Data ribbon to support@indzara.com to check further.
Best wishes.
The Watchlist shows stocks that are sold (0 units), how do you remove stocks from the watchlist that are no longer owned?
Thank you for showing interest in our template.
Currently, we have not filtered the stock which without quantity. We are gathering such feedback to make improvements in the next version.
Best wishes.
Great work on the template.
The dashboard does not update the stock XNAS:WIX after I added to the Transactions tab. I added a whole bunch of other stocks and when the Market Value does not match, I figured this was not reflecting in the dashboard. Could you check?
Thank you for sharing your valuable feedback.
I am unable to find the ticker symbol XNAS:WIX in Google. Hence requesting to share the company name and country to support@indzara.com to check further on your concern.
Best wishes.
Hello Sir,
What an amazing tracker sir…Keep it up
SIr i wish to add my stocks of NSE & BSE in this tracker but facing an issue for offset and time update in the tracker please help…unable to refresh the stocks as per indian stock market(BSE/NSE) trading time.
thank you
Thank you for sharing your valuable feedback.
I tried entering NIFTY 50 and BSE and I am able to get the data for the same. Currently the template works only on Microsoft Excel 365, if you have Excel 365 subscription and still you are unable to refresh the data, requesting to share a copy of your template to support@indzara.com to check further.
Best wishes.