Stock Market Analysis and Trends

This assignment presents a comprehensive dataset of historical stock market information. It includes daily closing prices, percentage changes from the previous day, and corresponding dates spanning several months. Students are tasked with analyzing this data to identify trends, fluctuations, and potential patterns in the stock market.

FINANCIAL RISK
MANAGEMENT

Table of Contents
INTRODUCTION.......................................................................................................................................................................................1
1. Estimating monthly, annual and expected return from each share......................................................................................................1
Share price at the beginning of the year...................................................................................................................................................1
2. Estimating the standard deviation of the annual returns......................................................................................................................3
3. Produce a correlation matrix between monthly returns on each pair of shares...................................................................................3
4. Efficient frontier chart and explaining the relationship between risk and return and diversification..................................................4
5. Practical limitation of the approach to portfolio management & the model........................................................................................6
CONCLUSION............................................................................................................................................................................................7
REFERENCES............................................................................................................................................................................................8
APPENDIX..................................................................................................................................................................................................9
Appendix: 1. Calculation of monthly return of stock..............................................................................................................................9
Appendix 2: Monthly return on FTSE 100 Index..................................................................................................................................11

INTRODUCTION
In the turbulent and volatile market, it is too critical for the establishments to use risk management strategies for mitigating
their internal & external risks. Financial risk management belongs to the area of finance aspects wherein companies make decisions
for ensuring enough availability of funds, easy access to required capital and its proper utilisation with managed risk. Portfolio
construction is a part of financial management which combines various set of securities and blend them together to obtain maximum
yield at minimum risk. The current research is based on an efficient portfolio construction and management along with spreadsheet
modelling.
1. Estimating monthly, annual and expected return from each share
Monthly return: Per month return on a security that is expected to gain by the investor with the price differences is called
monthly return (Roman, Arbex Valle and Mitra, 2017). It is calculated here as under:
Current month’s share price - last month’s share price/Last month’s share price*100
See Appendix 1 & 2.
Annualized return
Share price at the end of the year – Share price at the beginning of the year *100
Share price at the beginning of the year
Year Barclays Glaxo
Sainsbur
y Morrison BSkyB
Rio
Tinto W.Hill Shell Admiral FTSE 100
2012 160.13% 8.20% -2.85% 2.46% 13.08% 125.10% -21.31% 9.26% 32.26% 3.81%
2013 -3.29% 1.93% 16.18% -7.44% 37.96% 44.77% -14.61% 26.42% 33.72% 7.52%
2014 -40.07% 30.39% -20.60% 22.40% -2.98% -26.99% 11.74% 13.04% -48.14% 0.85%
2015 23.45% -5.32% 19.66% -8.04% 11.16% -7.71% 54.92% -5.78% 23.27% -7.51%
2016 -11.63% 11.59% 23.68% 5.58% 0.55% -8.76% -0.81% -10.94% 2.21% 11.51%
Page 1 of 18

Average
market
return 25.72% 9.36% 7.21% 2.99% 11.95% 25.28% 5.99% 6.40% 8.66% 3.24%
Expected return: It has been computed using CAPM (Capital assets pricing model) that presents relationship between risk
and return for the particular assets i.e. security and helps to design a well-diversified portfolio (Scherer, 2002).
CAPM: Risk free rate + beta (Market rate – risk free rate)
Barclays Glaxo
Sainsbur
y Morrison BSkyB Rio Tinto W.Hill Shell Admiral
Risk free rate of return 4% 4% 4% 4% 4% 4% 4% 4% 4%
Market return 25.72% 9.36% 7.21% 2.99% 11.95% 25.28% 5.99% 6.40% 8.66%
Beta (Slope)
-
0.843449
0.449
4 0.486309
0.234056
5
0.297206
9
1.622362
0
-
3.3588084
0.2841
5
0.219261
0
Expected return -14.32% 6.41% 5.56% 3.76% 6.36% 38.53% -2.67% 4.68% 5.02%
Barclays
Glaxo
Sainsbury
Morrison
BSkyB
Rio Tinto
W.Hill
Shell
Admiral
-20.00%
-10.00%
0.00%
10.00%
20.00%
30.00%
40.00%
50.00%
Expected return of stock
Expected return
Page 2 of 18

Looking to the results, it is founded that beta for Rio Tinto above one indicates highly volatile stock as it is reported to 1.622
above 1 at a highest return of 38.53% per annum. However, on the other side, Barclays & W. Hill is expected to deliver negative
return at 14.32% and 2.67% respectively. All the other securities such as Glaxo, Sainsbury, Morrison, BSkyB, Shell and Admiral is
expected to deliver a return of 6.41%, 5.56%, 3.76%, 6.36%, 4.68% and 5.02% respectively.
2. Estimating the standard deviation of the annual returns
Standard deviation is a statistical measurement of dispersion which helps to determine spread or scatter through comparing the
annualized return to that of average return per annum (Liu, 2016). For all the stock, variance and standard deviation is computed here
as under:
Year Barclays Glaxo
Sainsbur
y
Morriso
n BSkyB
Rio
Tinto W.Hill Shell
Admira
l
FTSE
100
2012 160.13% 8.20% -2.85% 2.46%
13.08
% 125.10%
-
21.31% 9.26% 32.26% 3.81%
2013 -3.29% 1.93% 16.18% -7.44%
37.96
% 44.77%
-
14.61% 26.42% 33.72% 7.52%
2014 -40.07%
30.39
% -20.60% 22.40% -2.98% -26.99% 11.74% 13.04% -48.14% 0.85%
2015 23.45% -5.32% 19.66% -8.04%
11.16
% -7.71% 54.92% -5.78% 23.27% -7.51%
2016 -11.63%
11.59
% 23.68% 5.58% 0.55% -8.76% -0.81%
-
10.94% 2.21% 11.51%
Stdev 0.78 0.13 0.19 0.12 0.16 0.62 0.30 0.15 0.34 0.07
variance 0.62 0.02 0.03 0.02 0.03 0.38 0.09 0.02 0.12 0.01
3. Produce a correlation matrix between monthly returns on each pair of shares
Correlation represents relationship between return of both the underlying securities, positive value indicates favourable
relationship whereas negative correlation coefficient depicts inverse relationship.
Barclays Glaxo Sainsbury Morrison BSkyB Rio Tinto W.Hill Shell Admiral
Page 3 of 18

Barclays 1
Glaxo 0.016455 1
Sainsbury 0.294476 0.22919 1
Morrison -0.0411 0.484048 0.492689 1
BSkyB 0.147881 0.199766 0.209452 0.390074 1
Rio Tinto 0.490377 0.003527 0.169912 -0.07858 0.084971 1
W.Hill 0.199986 0.108145 0.198013 0.023349 0.126126 0.058766 1
Shell 0.268647 0.426187 0.354811 0.25889 0.150498 0.349495 0.127005 1
Admiral 0.30854 0.147345 0.320982 0.041575 0.292143 0.266772 0.27547 0.206753 1
4. Efficient frontier chart and explaining the relationship between risk and return and diversification
There is a positive or negative correlation between risk and return. However, it doesn't guarantee that if the risk is higher, it will generate
more return. A lower risk investment may result in higher profits as well. Also, a higher risk investment can generate higher return. There are
various securities which involves low risk with lower yield such as short term government bonds ( Ceria and Stubbs, 2016). The securities that
have high risk involved but it also produces higher yield such as equity investments, futures and commodity contracts, such as options. The
relationship between risk and return can be assessed in the following manner:
 High risk and high return: According to this relationship, f the investor gets involved in high risk portfolio it will able to get more
returns.
 Low risk and low return: According to it, with decreasing risk in the portfolio, the return also gets reduced.
 High risk and low return: Sometimes, high risk portfolios also result in low returns. It is advised to consider all aspects of risk
assessment and outcomes while investing.
 Low risk and high return: There are some projects, even if they are having low risk, they result in higher returns. It basically happens
when government give high return on small investments (Guerard Jr, 2016). However, the opportunity gets opened for a limited period.
Page 4 of 18

Investment placed on different levels having different risk factors can be put together in the form of portfolio in order to reduce the risk
and volatility involved in the securities. It depends on the individual risk tolerance and portfolio is constructed based on it. Risk tolerance includes,
size of the portfolio, duration of investment, risk expected, potential future earnings etc. Diversifying portfolio doesn't mitigate the risk totally but
it reduces the risk to some extent, if portfolio is prepared with prudent mind. By diversification, investor can distribute or spread total risk to
various securities with different return expectations and standard deviation so as to minimize portfolio risk and ensure required return.
Standard deviation Expected return
Barclays 0.78 -14.32%
Glaxo 0.13 6.41%
Sainsbury 0.19 5.56%
Morrison 0.12 3.76%
BSkyB 0.16 6.36%
Rio Tinto 0.62 38.53%
W.Hill 0.30 -2.67%
Shell 0.15 4.68%
Admiral 0.34 5.02%
Risk free rate 4%
Portfolio statistics
Portfolio average return 11% 25% 30.00% 34.53%
Standard deviation 0.159807 0.37336 0.37 0.37
Slope 0.688331 0.669595 0.803514 0.924845
Weight
Barclays 0.00% 0.00% 0.00% 0.00%
Glaxo 5.12% 29.66% 14.09% 0.00%
Sainsbury 11.69% 0.00% 0.00% 0.00%
Page 5 of 18

Morrison 10.64% 0.00% 0.00% 0.00%
BSkyB 12.16% 0.00% 0.00% 0.00%
Rio Tinto 30.98% 70.34% 85.91% 100.00%
W.Hill 6.87% 0.00% 0.00% 0.00%
Shell 11.17% 0.00% 0.00% 0.00%
Admiral 11.37% 0.00% 0.00% 0.00%
5% 10% 15% 20% 25% 30% 35% 40%
0
0.05
0.1
0.15
0.2
0.25
0.3
0.35
0.4
Efficient frontier chart
Standard deviation
From the results obtained, it can be said that investors must allocate 0%, 5.12%, 11.69%, 10.64%, 12.16%, 30.98%, 6.87%,
11.17% and 11.37% to Barclays, Glaxo, Sainsbury, Morrison, BSkyB, Rio Tinto, W, Hill, Shell and Admiral to generate lowest return
of 11%. However, by fully allocating the fund to the highest profit yielding firm, Rio Tinto, investor can generate highest return of
34.53% per annum. Therefore, it is advised to the investor to invest money in Rio Tintor organization.
Page 6 of 18

5. Practical limitation of the approach to portfolio management & the model
There are some limitations of the modern portfolio theory as it can be observed that in same estimations are made about the
likely trends that can be observed in the upcoming time period. One needs to form an idea about return that can be generated by the
market in the upcoming time period. Thus, if estimation is wrongly made by the analyst then in that case same will prepare portfolio
model in wrong manner. This is the one of the major limitation of the portfolio theory as one have to make judgment about likely
changes that can be observed in the market (Alexeev, Dungey and Yao, 2016). This is the one front or limitation of the portfolio
theory. Other major limitation of portfolio theory is that one have to compute expected return and for same beta value is used. It must
be noted that beta value is computed on the basis of past data. It is not necessary that past will repeat itself in future. Hence, there is
likelihood that trends may change in future time for which portfolio is designed. In such a case actual results may vary from expected
results and portfolio may generate negative return for the investor. Hence, these are some of the major limitations of the modern
portfolio theory.
CONCLUSION
After the completion of the report, it can be concluded that it is important for the investors to analyze risk and return associated
with various securities so as to design the optimum portfolio which yield greater return at minimum portfolio risk. The report
discussed that diversification is of huge importance that helps investor to minimize the risk by allocating the total capital into various
assets with different risk and return probability, so that, they can ensure target return.
Page 7 of 18

REFERENCES
Books and Journals
Alexeev, V., Dungey, M. and Yao, W., 2016. Continuous and Jump Betas: Implications for Portfolio Diversification. Econometrics.
4(2). p.27.
Ceria, S. and Stubbs, R.A., 2016. Incorporating estimation errors into portfolio selection: Robust portfolio construction. In Asset
Management. Springer International Publishing. pp. 270-294.
Guerard Jr, J.B. ed., 2016. Portfolio Construction, Measurement, and Efficiency: Essays in Honor of Jack Treynor. Springer.
Liu, E.X., 2016. Portfolio Diversification and International Corporate Bonds. Journal of Financial and Quantitative Analysis. 51(03).
pp.959-983.
Roman, D., Arbex Valle, C. and Mitra, G., 2017. Novel approaches for portfolio construction using second order stochastic
dominance.
Scherer, B., 2002. Portfolio construction and risk budgeting. Risk Books.
Page 8 of 18

APPENDIX
Appendix: 1. Calculation of monthly return of stock
Year Month Barclays Glaxo
Sainsbur
y Morrison BSkyB
Rio
Tinto W.Hill Shell Admiral
2012 Jan Barclays Glaxo Sainsbury Morrison BSkyB Rio Tinto W.Hill Shell Admiral
Feb -11.97% -12.34% -5.33% -4.62% -5.38% 19.59% 0.11% -10.49% -4.89%
Mar 58.46% 1.73% -0.71% -1.06% -7.81% 30.48% -27.94% 3.17% -0.18%
Apr 90.20% -3.54% 5.59% -3.42% 12.28% 18.04% 28.93% 1.70% 6.74%
May 5.68% -0.67% -6.13% -1.93% -9.19% 0.94% -3.87% 7.58% -5.76%
Jun -4.87% 2.54% 0.89% -2.27% 2.94% -24.82% -6.87% -8.90% 1.34%
Jul 6.82% 7.53% 1.44% 13.95% 20.00% 18.24% -6.62% 1.83% 9.94%
Aug 25.79% 4.70% 2.58% 2.78% 0.00% -3.03% 1.09% 7.72% 11.76%
Sep -2.70% 2.20% -0.21% 0.11% 4.67% 10.57% -4.96% 3.70% 8.23%
Oct -12.97% 1.67% 1.51% 0.97% -6.74% 0.92% -4.71% 1.38% -11.24%
Nov -9.21% 0.56% -2.42% -1.68% -0.66% 14.67% 6.49% -1.31% 3.31%
Dec -5.59% 4.97% 0.50% 0.76% 6.14% 9.78% 3.80% 4.29% 12.06%
2013 Jan -1.97% -7.81% 0.12% 4.22% -5.07% -8.58% 7.65% -7.65% -4.71%
Feb 15.51% -0.21% 2.01% 3.25% 1.97% 8.55% -2.45% 2.60% 9.44%
Mar 15.30% 4.24% -0.85% -1.68% 10.66% 16.08% 8.31% 5.77% 6.45%
Apr -6.12% -4.31% 3.27% -1.06% 2.16% -13.47% -3.08% 8.79% -0.38%
May -9.80% -4.46% -4.23% -9.02% -6.34% -5.62% -13.87% -11.32% -1.90%
Jun -11.32% -1.21% -0.77% 0.72% 21.61% -6.91% -2.84% -6.88% 9.22%
Jul 23.01% -2.80% 6.87% -0.41% 1.50% 11.32% -2.57% 2.97% 2.77%
Aug -9.13% 9.90% 6.29% 9.43% -0.49% -0.14% 0.30% -0.65% 5.11%
Sep -0.93% 2.74% 6.98% 1.97% -0.28% 12.76% -0.84% 11.30% 9.46%
Oct -8.34% -2.67% -0.36% -0.64% 0.14% 8.47% -3.19% 7.62% -2.16%
Page 9 of 18

Nov -6.72% -0.74% -8.63% -7.42% 1.98% 1.08% -3.24% -4.53% -6.26%
Dec 2.15% 2.31% 5.79% -1.62% 2.15% 9.98% 9.77% 10.85% -0.85%
2014 Jan 12.27% -8.99% 1.38% -0.41% 2.58% -4.60% 6.33% 2.65% 8.45%
Feb 8.89% 4.65% -0.39% 3.86% 4.24% 0.69% 5.67% 1.27% 2.80%
Mar -13.22% 0.72% -11.76% -0.29% 4.83% 1.61% -5.79% 2.80% -7.99%
Apr 1.71% 9.75% 3.88% 6.81% 2.06% -0.43% 23.69% 2.96% 8.88%
May -2.00% 1.11% -0.78% 2.99% -1.19% -2.64% -4.38% -5.50% 1.83%
Jun -7.30% 1.06% -4.69% -1.94% 1.74% 5.78% 6.88% 1.14% -3.60%
Jul -13.04% 2.25% -7.68% -2.28% -15.89% -4.24% 1.09% 0.70% -6.74%
Aug -23.43% -3.92% -1.38% -0.65% -7.23% -11.58% -2.25% -7.14% -11.88%
Sep -5.51% 1.72% -8.37% 0.52% 0.53% -24.03% 0.22% -3.25% -7.47%
Oct 21.04% 5.03% 8.84% 4.03% 6.02% 17.19% -4.51% 11.11% -6.65%
Nov -7.71% 0.25% 1.64% 6.55% 8.66% -1.36% -6.44% 2.26% -21.76%
Dec -2.33% 4.85% -0.36% 1.30% -4.25% -6.41% 0.35% 7.35% -7.64%
2015 Jan 20.73% -4.18% -4.79% -12.32% -5.80% 21.76% 10.80% -5.93% 10.45%
Feb 15.27% -1.63% 3.43% 1.40% -2.90% -5.81% -0.09% 0.91% 14.45%
Mar -3.98% 0.68% 4.36% 2.76% 0.90% -3.85% 16.44% -5.58% 10.21%
Apr -7.23% 2.04% -1.09% -5.84% 0.30% -0.39% 7.65% 2.14% 2.02%
May -19.22% 0.91% -6.43% -1.57% 1.47% -19.01% -3.87% -7.48% -14.20%
Jun -7.63% 0.63% 4.62% -3.66% 1.24% 8.60% 4.55% 7.05% 14.73%
Jul 3.16% 1.49% 7.27% 4.21% 2.30% -2.40% 11.03% 1.10% -8.39%
Aug 9.08% -2.96% 1.33% 0.97% 6.81% -7.16% -4.36% 0.98% 8.61%
Sep 17.24% 0.18% 6.07% 1.86% -2.30% 5.47% 5.49% -3.24% -11.21%
Oct 5.89% -2.87% 2.04% -6.07% -4.64% 7.61% 6.69% -0.34% 5.22%
Nov 8.13% -3.75% -3.72% 0.34% 7.05% -0.35% -0.24% -1.64% 0.99%
Dec 6.67% 0.04% 1.08% -2.16% 1.05% 13.51% 3.23% 0.95% 3.66%
2016 Jan 14.71% 8.28% -4.17% -4.56% 6.58% 1.38% 10.34% 5.47% 5.43%
Feb 1.99% 0.73% 4.57% 3.43% 4.04% -0.53% 5.36% -3.07% 2.29%
Mar -5.16% 5.67% 9.46% 6.39% 3.82% -12.88% -8.60% -1.73% 6.47%
Page 10 of 18

Apr -1.75% 7.93% 0.69% 5.76% -4.42% -5.38% 15.17% 3.11% -3.83%
May 12.92% 3.64% -0.81% -6.20% -7.58% 0.51% 1.88% -1.29% 2.65%
Jun -10.84% -4.65% -5.03% -4.74% 1.28% -7.70% 2.07% -5.31% 0.08%
Jul 1.04% 1.77% 8.64% 9.96% 4.68% 9.12% 8.80% 4.99% 6.99%
Aug -2.06% 0.18% 0.51% 1.74% 2.66% -0.20% -12.66% -4.52% -9.52%
Sep -8.07% -7.17% 0.26% -4.45% 2.00% 2.27% -4.75% -4.07% -2.75%
Oct 2.29% 5.80% 2.29% 2.15% 7.62% 5.84% 0.00% 4.30% 2.58%
Nov -0.75% -1.83% 1.74% -7.02% -11.80% 1.75% -4.99% -3.27% -1.65%
Appendix 2: Monthly return on FTSE 100 Index
Date Prices FTSE 100 return
16-Nov 6,783.79 -2.45%
16-Oct 6,954.22 0.80%
16-Sep 6,899.33 1.74%
16-Aug 6,781.51 0.85%
16-Jul 6,724.43 3.38%
16-Jun 6,504.33 4.39%
16-May 6,230.79 -0.18%
16-Apr 6,241.89 1.08%
16-Mar 6,174.90 1.28%
16-Feb 6,097.09 0.22%
16-Jan 6,083.79 -2.54%
15-Dec 6,242.32 -1.79%
15-Nov 6,356.09 -0.08%
15-Oct 6,361.09 4.94%
15-Sep 6,061.61 -2.98%
15-Aug 6,247.94 -6.70%
15-Jul 6,696.28 2.69%
15-Jun 6,520.98 -6.64%
Page 11 of 18

15-May 6,984.43 0.34%
15-Apr 6,960.63 2.77%
15-Mar 6,773.04 -2.50%
15-Feb 6,946.66 2.92%
15-Jan 6,749.40 2.79%
14-Dec 6,566.09 -2.33%
14-Nov 6,722.62 2.69%
14-Oct 6,546.47 -1.15%
14-Sep 6,622.72 -2.89%
14-Aug 6,819.75 1.33%
14-Jul 6,730.11 -0.21%
14-Jun 6,743.94 -1.47%
14-May 6,844.51 0.95%
14-Apr 6,780.03 2.75%
14-Mar 6,598.37 -3.10%
14-Feb 6,809.70 4.60%
14-Jan 6,510.44 -3.54%
13-Dec 6,749.09 1.48%
13-Nov 6,650.57 -1.20%
13-Oct 6,731.43 4.17%
13-Sep 6,462.22 0.77%
13-Aug 6,412.93 -3.14%
13-Jul 6,621.06 6.53%
13-Jun 6,215.47 -5.58%
13-May 6,583.09 2.38%
13-Apr 6,430.12 0.29%
13-Mar 6,411.74 0.80%
13-Feb 6,360.81 1.34%
13-Jan 6,276.88 6.43%
12-Dec 5,897.81 0.53%
12-Nov 5,866.82 1.45%
Page 12 of 18

12-Oct 5,782.70 0.71%
12-Sep 5,742.07 0.54%
12-Aug 5,711.48 1.35%
12-Jul 5,635.28 1.15%
12-Jun 5,571.15 4.70%
12-May 5,320.86 -7.27%
12-Apr 5,737.78 -0.53%
12-Mar 5,768.45 -1.76%
12-Feb 5,871.51 3.34%
12-Jan 5,681.61 1.96%
Page 13 of 18

Page 14 of 18