Portfolio Analysis is the examination of securities over a period of time to get an overview of investments is called Portfolio Analysis.
Portfolio evaluations at regular intervals will help the investor make portfolio reallocation based on the performance or requirements of funds in the near future.
I have created the Microsoft Excel template for easy and accurate portfolio analysis and analyze appropriate analytical data of your invested securities.
Click here to Download the Portfolio Analysis Excel Template.
Click here to Download All Personal Finance Excel Templates for ₹299.Before downloading the template please check out the prerequisite of using the Excel template.
Pre-requisite to Use Above Template
Before proceeding to template contents and configuration part we need to download daily data from the BSE website in excel format. BSE Bhav Copy download option is available here: http://www.bseindia.com/markets/Derivatives/DeriReports/DerBhavCopy.aspx
Step 1: Select the required date and click submit or for today’s copy click the option on left for Equity (Current Date). Today copy is available only after market close. Download the file and save it. It is a zip file containing the CSV format of Bhav Copy for that particular date. Extract the zip file and open it in MS Excel.
Step 2: Open the above template file along with a bhav-copy file downloaded from the BSE website. Go to bhav-copy and right-click on the sheet tab and select “Move or Copy”. Check the box “Create a copy” then select the template file from the drop-down menu and press Enter. This file will be copied to our template file.
This template works only with BSE (Bombay Stock Exchange) only.
Understanding the Template
Row 1 = A1 to O1 consists of headings for data input. For example, see the table below:
- Column A: Purchase date
- Column B: Code of Company (BSE/NSE) (optional)
- Column C: Company name
- Column D: Shares units Purchased
- Column E: Purchase price per unit
- Column F: Commissions to Agent if any
- Column G: Total cost of a particular purchase
- Column H: Current Market price per unit
- Column I: Current Market value
- Column J: Increase or decrease in investment
- Column K: Percentage increase or decrease of investments
- Column L: Monthly CAGR for investments
- Column M: Duration in months
- Column N: CAGR yearly for investments
- Column O: Duration in Years
- Column P: Current Date
Configuring the Template to Use BSE Downloaded Bhav Copy
- Column A: Purchase Date is entered manually on which transaction takes place. Right-Click on cell and format cell to date format or ALT+H+N and select date format.
- Column B and Column C: You can link this cell to your company available in the Bhav Copy by putting a + in the cell and then selecting the desired cell in Bhav copy. For searching, you can use Control + F to easily find your company.
- Column D: Manually input the number of units purchased. This is a one-time entry.
- Column E: Manually input price per unit at the time of purchase. This is also a one-time entry.
- Column F: Commissions to Agent. Here formula applied is 1 % of the value. (No of units X price per unit X 1%). The formula applied in the template is =D2*E2*1%
- Column G: Total cost of Purchase is No. of Units X price per Unit + Commission. The formula applied here is =D2*E2+F2
- Column H: Current Market price per unit of a particular script can be derived from the sheet of Bhav copy. You can link this cell to a price cell for a particular company available in the Bhav Copy by putting a + in the cell and then selecting the desired cell in Bhav copy.
- Column I: Current Market Value is No of shares X Current Price. The formula here used is =H2*D2.
- Column J: Increase or Decrease in value of your investment for a particular script derived by Market value – Total Cost. The formula applied here is =I2-G2
- Column K: Percentage Increase/Decrease derived by Amount Increase or decrease / Total purchase price X 100. The formula applied here is =J2/G2*100.
- Column L: Monthly CAGR is the Compound Annual Growth Rate on a monthly basis. Compound annual growth rate is derived by ={n[(A/P)1/nt – 1] x 100} where A = Accrued Amount (principal + interest), P = Principal Amount, t = Time Involved in years, 0.5 years is calculated as 6 months, n = number of compounding periods per unit t; at the END of each period. Formula applied here is =1*(((I2/G2)^(1/M2)-1)*100).
- Column M: Duration in Months derived by Current date – Purchase date/30. The formula applied here is =(P2-A2)/30.
- Column N: Yearly CAGR is the Compound Annual Growth Rate on a yearly basis. Compound annual growth rate is derived by ={n[(A/P)1/nt – 1] x 100} where A = Accrued Amount (principal + interest), P = Principal Amount, t = Time Involved in years, 0.5 years is calculated as 6 months, n = number of compounding periods per unit t; at the END of each period. Formula applied here is =1*(((I2/G2)^(1/M2)-1)*100).
- Column O: Duration in years derived by Current date – Purchase date/365. The formula applied here is =(P2-A2)/365.
- Column P: Current Date is derived by applying =today() formula which gives the current date of the system.
If you have any queries or questions, share them in the comments below and I will be more than happy to help you.
Sreenivas says
Thanks Fahim for the the portfolio maintenance through Excel.
is it possible to read some of the data directly from BSE site into Excel ? I’m looking for fields like 52 week low,52 week high, last quarter sales amount, Total number of shares available ?
Fahim Lashkaria says
Hi Srinivas,
Happy to know that our portfolio template was useful to you.
Yes it is possible but requires programming.
Anshuman says
Thanks for wonderful Template.
Regards
Prof. Anshuman
Fahim Lashkaria says
Glad to know it was helpful Prof. Anshuman.
Mohit says
I think the VBA sections of the sheet is password protected, would really appreciate if you could share the password.
Fahim Lashkaria says
There is no VBA in this file. It just excels formulas which we have used. The data is fetched using Data from the Web under Data menu.
mohan choudhary says
hi fahim
i am looking for a dash board in a excell sheet were we enter the rsults of a company quaterly and yearly and this should get affected on dash board in terms of yealy CAGR, monthly CAGR, ROE, etc any many other parameters which we enter.
can you help me on it
my no.9890372023 and emil id : mjchoudhary@gmail.com
Fahim Lashkaria says
No, we dont have such anything such ready here. You can get it customized. Contact info@exceldatapro.com for getting the quote.
ravi says
Thank you, very useful for a common investor who need only buy, sell and profit loss details. this is the best one I got it online.
Regards
Dr.Ravi
Fahim Lashkaria says
Thanks for your appreciation, Dr. Ravi.