GUJARAT UNIVERSITY
BK SCHOOL OF PROFESSIONAL AND MANAGEMENT STUDIES
SECURITY ANALYSIS AND PORTFOLIO MANAGEMENT
PROJECT REPORT-ASSIGNMENT
SEMESTER III
INSTRUCTOR: DR.ASHISH SIDDIQUI
Objective:
This project aims to help students understand the basic concepts of portfolio construction and risk-return
trade-off by analysing a few securities (stocks or ETFs) and calculating their returns and essential portfolio
performance.
1. Data Collection
Step 1: Select 2 Securities
Choose two simple securities to analyse (e.g., two popular stocks or ETFs). For simplicity:
• Stock 1: Apple (AAPL)
• Stock 2: Microsoft (MSFT)
Step 2: Download Historical Data
• Go to Yahoo Finance: (https://finance.yahoo.com/).
• For each security, download monthly adjusted closing prices for the last 6 months. This will provide
you with 6 data points for each security.
2. Data Analysis
Step 3: Calculate Monthly Returns
In Excel or Google Sheets, create two columns for each stock showing the Adjusted Close Price for each
month.
Example:
Date AAPL Price MSFT Price
Jan 2024 150 275
Feb 2024 155 280
Mar 2024 160 290
Apr 2024 158 285
May 2024 162 295
Jun 2024 165 300
Next, calculate the monthly return for each stock:
Step 4: Calculate the Average Return for Each Stock
In Excel, use the AVERAGE function to calculate the average monthly return for each stock. For example:
• Apple (AAPL): =AVERAGE(B2:B7)
• Microsoft (MSFT): =AVERAGE(C2:C7)
Step 5: Calculate the Standard Deviation (Risk) for Each Stock
Next, calculate the standard deviation of the returns (which represents the risk or volatility) for each stock
using the STDEV.P function:
• Apple (AAPL): =STDEV.P(B2:B7)
• Microsoft (MSFT): =STDEV.P(C2:C7)
3. Portfolio Construction
Step 6: Decide Portfolio Allocation
• Decide how you want to allocate your investments between the two stocks. For simplicity, let’s say you
allocate 50% to each stock.
Example portfolio:
• 50% in Apple
• 50% in Microsoft
Step 7: Calculate Portfolio Return
The portfolio return is the weighted average of the returns of the two stocks. If you’ve calculated the average
return for Apple as 4% and for Microsoft as 3%, the portfolio return will be:
Step 8: Calculate Portfolio Risk
To calculate portfolio risk (standard deviation), we’ll simplify the calculation and assume no correlation
between the two stocks for this essential project. The formula for portfolio risk is:
OR
Use excel
=SQRT((0.5^2 * STDEV_AAPL^2) + (0.5^2 * STDEV_MSFT^2))
4. Performance Evaluation
Step 9: Calculate Sharpe Ratio
5. Report and Presentation
Step 10: Write a Short Report
Create a short 1-2 page report summarising your analysis:
• Introduction: Briefly explain what securities were analysed (e.g., Apple and Microsoft).
• Data Analysis: Show each stock's average returns and standard deviations.
• Portfolio Construction: Explain how the portfolio was constructed (e.g., 50% Apple, 50% Microsoft).
• Performance Evaluation: Discuss the portfolio’s return, risk, and Sharpe ratio.
• Slide 5: Conclusion (key findings and recommendations).
Conclusion
This simple project gives you hands-on experience with basic portfolio construction and performance
evaluation using accurate stock data. The focus is on understanding key metrics such as return, risk, and the
Sharpe ratio, which are fundamental to investment decision-making.
By completing this project, you'll understand how investors balance risk and return in portfolio management.
---------------------------------------------------------------------------------------------------------------
NOTE:
The calculation in the example (using Apple and Microsoft) was just a pilot illustration. Students must choose
two Indian companies listed on the Indian Stock Exchanges (NSE/BSE) for their project, conduct the required
analysis (as described in the steps above), and then upload their final project to the provided drive link.
This keeps the project relevant to the Indian stock market and will give students practical experience with local
securities!
Submission 7 days from the date of receipt
-------------------------------------------------------------End---------------------------------------------------------------------
-
