Download Portfolio Analysis With BSE Bhav Copy Data Excel Template

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.

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:

1. Column A: Purchase date
2. Column B: Code of Company (BSE/NSE) (optional)
3. Column C: Company name
4. Column D: Shares units Purchased
5. Column E: Purchase price per unit
6. Column F: Commissions to Agent if any
7. Column G: Total cost of a particular purchase
8. Column H: Current Market price per unit
9. Column I: Current Market value
10. Column J: Increase or decrease in investment
11. Column K: Percentage increase or decrease of investments
12. Column L: Monthly CAGR for investments
13. Column M: Duration in months
14. Column N: CAGR yearly for investments
15. Column O: Duration in Years
16. 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.