In this article, I am going to talk about how to manage your own stock portfolio in Colombo Stock Exchange (CSE).
For CSE traders, a broker’s trading platform is an obvious place to make investment decisions. It provides different indicators like real-time price updates in the stock portfolio, Gain/Loss based on the current market price, candlestic charts for stock movement, etc. But these indicators are mostly generic and also the trading platform lacks to answer some of the personalized questions such as,
- What would be my selling price to achieve an intended profit margin?
- What are my total capital gains?
- What is the total cost of capital gains (brokerage cost)?
- What is the dividend expected from each stock with the status of each?
I have come up with an excel template to facilitate the above set of questions and to make the personal decision-making process easy. This helps me in the past few months and thought to share the template with you.
Template Features
Overview stats:
- Total Profit — total capital gain and total dividend
- Buying power
- Total brokerage cost
Personal stock portfolio:
- Automatically fetch market price from CSE
- Gain/Loss based on the market price
- Estimate selling price with a profit margin
- Automatically fetch dividends from the CSE site
- Maintain stock Sold/Bought details
Template Overview
How to use the Template?
Download the excel template from here.
Then, enter the total invested amount.
Step 01 — Transaction Details
In the transaction details, you will have to enter the bought/sold note details. Provide the following details:
- Date of the transaction
- Transaction Type — Select Bought/Sold option from the dropdown
- Enter the company code, number of quantity & the “market price” of the stock
- The rest of the fields will be automatically calculated
IMPORTANT: If the transaction type is Bought, the Average stock price will be automatically calculated. If the transaction type is Sold, you will have to enter the Average stock purchase price to calculate the capital gain.
Once you enter transaction details for a particular trading day, make sure to cross-check with the Bought/Sold note.
Step 02 — Portfolio Details
Basic Details
Enter Company Code, Quantity & Stock Price. Average Stock Price & purchased value will be automatically calculated.
Market
Click “Market Price” button. It will automatically fetch market prices from CSE site. Based on the current market price, Gain/Loss will be calculated.
The points that we discussed so far are visible in the broker’s trading platform. In the following let’s see personalized features. 😉
Estimated
In this section, you will be estimating your desired capital gain with a profit margin. Enter profit percentage in the field. It will give us the expected capital gain and price to sell this stock.
Dividend
You can access dividend details on the CSE site, under the announcement tab of the companies profile.
With this excel template, it’s just one click away. Click on the “Dividend” button. It will fetch the latest dividend announcement under the dividend section. Also, it will show whether a dividend is already executed or pending based on XD date.
Note: considering the performance, I have strict to scan dividends announcement for the recent 3 months from today. In case of dividend details are not captured, you can manually enter them.
Once you fetch the dividend, update “Trans. Status”, whether you received or will receive the dividend. Based on the selection, the Dividend total will be updated.
Possible Improvements
- Pick the purchase price automatically when selling a stock.
- Automatically update the portfolio sheet when updating the transaction sheet (ex: if we add bought entry, increase the stock count. If we completely sell a stock, remove the entry from portfolio).
- Have any other improvements? Feel free to add as a comment
Technical Details
- Used simple excel formulas for the calculation
- Used VBA for excel, to retrieve the data
- An editable version of the template can be accessed on Github. Feel free to contribute if you are interested: https://github.com/shamique/TVA-TradeValueAgent
Hope this template will make your trading life easy. Happy Trading!!! 📈
