Categories

# Access Datastream (or Yahoo Finance) to collect the daily stock price indices of the S&P500 and any six (6) listed companies on the New York Stock Exchange over the last 12 months

Learning Outcomes

On completion of the module you should be able to:

LO1     Identify and critique the elements of portfolio theory.

LO2     Define and critically analyse the efficiency of markets

LO3     Identify and critique major classes of asset pricing models

LO4     Comment critically on the literature surrounding asset pricing models.

LO5     Critically evaluate risks and returns relevant in the international securities market using DATASTREAM

Requirements

You are expected to collect data of stock prices and carry out mean variance optimization using Excel Solver. Express your understanding about Risk, Return and Portfolio Theory by writing a report about your data processing, results analysis, explanation about your findings, the assumptions underlying mean variance optimisation, and finally your recommendations for investors who want to hold that portfolio.

What you need to do?

1. Access Datastream (or Yahoo Finance) to collect the daily stock price indices of the S&P500 and any six (6) listed companies on the New York Stock Exchange over the last 12 months (Hint: it is advised that you should select firms from different industrial sectors for diversification purpose). The price indices should be converted into return histories needed to find an efficient frontier.
2. Plot the return histories of the stocks over time to see whether there is evidence of volatility clustering and provide descriptive statistics of data sample (mean, standard deviation, minimum, maximum return, skewness and kurtosis).
3. Calculate alpha and beta values of each company stock return.
4. Carry out Mean Variance Optimization procedure with Excel Solver to find and plot the efficient frontier on a return-risk diagram. Do for both long only and long-shorts constraints. The former means that allocations to assets must be >= 0.  The long-short allows allocations to be < > 0.
5. Given the annual short-term risk-free rate is 0.2% and an investor uses a quadratic utility function based on expected return and variance and a risk aversion of 3. Carry out further optimization based on maximizing the Sharpe ratio to find the composition of the portfolio of the risky assets determined by the point of tangency that line from the risk-free rate makes with the frontier. This exercise should give the optimal allocations in terms of both risky and the risk-free asset.

Submission and evaluation:

• Your work needs to be submitted electronically via Turnitin by 12:00pm on 11 December 2020.
• The length of the report should be no more than 2,500 words.
• This assessment counts for 40% of the overall module grade.  It depends on whether the results from data processing are correct and whether your analysis makes sense.
• Providing solely the calculations is not acceptable.  A discussion of your findings, comparisons of the two results (long only and long-short constraints), possible explanations for any differences found, whether the assumptions underlying mean variance optimization are valid or not, and finally your recommendations for an investor wanting to hold this portfolio.
• An appendix illustrating how prices have been converted into returns and any other information used you think helpful to understanding your report.

Your performance will be graded on a continuous numerical scale which ranges from 0 to 100 and is expressed in percentage terms (%). You will see below the division of the scale into the classifications together with an indication of the standard required to achieve each classification.

### DISTINCTION, 70% or more

1. The work addresses all required elements
2. The narrative demonstrates particularly perceptive understanding
3. Presentation and clarity of expression are of the highest order
4. Extensive use of source material
5. Excellent links between the practice and theory

### MERIT, 60% to 69%

1. The work addresses all required elements
2. The narrative demonstrates strong understanding
3. Very few minor flaws in presentation and clarity of expression
4. Significant use of source material
5. Effective links between the practice and theory

### PASS, 50% to 59%

1. The work addresses all required elements
2. The narrative demonstrates satisfactory understanding
3. A number of minor flaws in presentation and clarity of expression
4. Satisfactory use of source material
5. Some links between the practice and theory

### FAIL, below 50%

1. The work does not address all required elements
2. The narrative demonstrates inadequate understanding
3. Extensive flaws in presentation and clarity of expression
4. Inadequate use of source material