Download Portfolio Analysis With BSE Bhav Copy Data Excel Template

Portfolio analysis is examination of securities over a period of time to gain a comprehensive overview of investments. This process is of paramount importance for investors to make informed decisions regarding portfolio reallocation based on the performance or future requirements of their investment funds.

Regular portfolio evaluations facilitate investors in making well-informed decisions regarding portfolio reallocation based on the performance or anticipated requirements of their investment funds in the near future. investors can identify under performing assets, re-balance their portfolios, and capitalize on emerging opportunities, ultimately maximizing their returns and minimizing potential risks.

I have crafted the Microsoft Excel template that streamlines the process of portfolio analysis and provides accurate analytical data pertaining to your invested securities. This user-friendly tool is designed to simplify the evaluation process, ensuring that you have access to comprehensive insights into your investment performance.

Click here to Download the Portfolio Analysis Excel Template.

Before downloading the template please familiarize yourself with the prerequisites for its optimal utilization. This will ensure a seamless and efficient analysis process, empowering you to make well-informed decisions regarding your investment portfolio.

Prerequisites for Utilizing the Excel Template

Before proceeding to template contents and configuration part you will need to download daily data from the Bombay Stock Exchange (BSE) website in an Excel format. The BSE Bhav Copy download option is conveniently accessible at the following URL: http://www.bseindia.com/markets/Derivatives/DeriReports/DerBhavCopy.aspx

Step 1: Select the desired date and click the “Submit” button, or for the current day’s data, select the “Equity (Current Date)” option located on the left-hand side. Please note that the current day’s copy is typically available only after the market’s closure. Download the file, which is in a compressed ZIP format, and save it to your local storage. Extract the ZIP file to access the CSV format of the Bhav Copy for the selected date. Open the extracted file in Microsoft Excel.

Step 2: With both the template file and the Bhav Copy file open, navigate to the Bhav Copy file and right-click on the sheet tab. Select the “Move or Copy” option, then check the “Create a copy” box. From the drop-down menu, select the template file, and press the “Enter” key. This action will copy the Bhav Copy data into the template file, facilitating seamless integration of the necessary data for analysis.

This template works only with Bombay Stock Exchange (BSE) 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: Company code (BSE/NSE) (optional)
3. Column C: Company name
4. Column D: Number of shares or units purchased
5. Column E: Purchase price per unit
6. Column F: Commissions paid to the 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 value
11. Column K: Percentage increase or decrease in investments
12. Column L: Monthly Compound Annual Growth Rate (CAGR) for investments
13. Column M: Duration of investment in months
14. Column N: Yearly CAGR for investments
15. Column O: Duration of investment in years
16. Column P: Current Date

Configuring the Template with the BSE Bhav Copy

• Column A: Manually enter the purchase date on which the transaction took place. Right-click on the cell and select “Format Cell” (or use the shortcut Alt+H+N) to choose the desired date format.
• Column B and Column C: You can link these cells to the corresponding company data available in the Bhav Copy by entering a “+” symbol in the cell and then selecting the desired cell from the Bhav Copy. To facilitate searching, you can use the shortcut Ctrl+F to easily locate 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: This column represents the commissions paid to the agent, if any. The formula applied in the template calculates 1% of the value: =D2*E2*1%.
• Column G: The total cost of the purchase is calculated as the number of units multiplied by the price per unit, plus the commission: =D2*E2+F2.
• Column H: The current market price per unit of a particular security can be derived from the Bhav Copy sheet. Link this cell to the corresponding price cell for the company in the Bhav Copy by entering a “+” symbol and selecting the desired cell.
• Column I: The current market value is calculated by multiplying the number of shares by the current price: =H2*D2.
• Column J: The increase or decrease in the value of your investment for a particular security is derived by subtracting the total cost from the market value: =I2-G2.
• Column K: The percentage increase or decrease is calculated by dividing the amount of increase or decrease by the total purchase price and multiplying by 100: =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: The duration of the investment in months is derived by subtracting the purchase date from the current date and dividing by 30: =(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: The duration of the investment in years is derived by subtracting the purchase date from the current date and dividing by 365: =(P2-A2)/365.
• Column P: The current date is automatically populated using the formula =today(), which retrieves the current date from the system.

If you encounter any queries or require further assistance, please do not hesitate to share your concerns. I will be more than happy to provide additional guidance and support to ensure you can effectively leverage this powerful portfolio analysis tool.