Only number 2 please
12:06 N\\ oD Project #2(1) DOCX 1. Download the daily pricing (closing price, not adjusted closing price, thereafter \"prc\") for| the following stocks, from the last trading day of May 2017 to Dec 31, 2019, including the same five stocks as in Project #1. Also obtain number of shares outstanding (shrout) as of May 2017 (you can use the same shrout from Project #1). Both information can be| found in Yahoo.Finance, Google.Finance, or other sources. Check stock split (or reverse] split if stock price jumps up or down too much). Use 1.2% for Rf, which is APR. To| convert to daily Rf, divide 1.2% by 252. Organize all the raw data in a nice format and| put all of them in a sheet, which is named "data." a. Compute daily return, average daily return using average() function, i.e., E(ret), and stdev (using stdev.s() function), i.e., sigma, for each stock. b. Compute var/cov matrix for them. You can use var.s and covar.s functions to compute this matrix based on daily returns of these five stocks. c. Put average ret, i.e., E(ret), and var/cov in a tab \"var- cov,\" with E(ret) in the first row, in total, 6 (row) x 5 (column). 2. For each individual stock, compute additional measures of risk, including VaR5%, skewness (3 moment) and Kurtosis (4 moments). Please refer to Page 118-122 for information. For VaR, first rank (sort) all daily ret numbers from small to big (ascendingly) and find the number that is in the 5 percentile, which is number of total observations multiplied by 5%. For example, for your 31-month daily return data, you likely have about 252*31/12=651 (more or less, we need to find out the exact # of days) observations. 651*5%=32.55. As a result, in your ascending rank, about 32-33" observation is your VaR (we can use interpolation to compute the value for exactly 32.55). For 3" and 4" moment, just use Skew and Kurt functions and apply them to these 651 or so daily returns. 3. Selecting 2 best performing stocks and use \"solver\" to find the global MVP, which has the lowest ptf variance. Specify ptf stdev in a cell where Excel computes the stdev (sigma) of the ptf as a function of the weights. The objective function is to minimize sigma (p) subject to the first 3 constraints in 5b (see below). Once the global MVP, or GMY, is found, please record the weights, expected return, sigma, and SR on this ptf. 4. Use \"solver\" to find the global Optimal O using these two best performing stocks, which has the greatest SR everywhere. Specify ptf stdev in a cell where Excel computes the stdev (sigma) of the ptf as a function of the weights. Also specify E(Ret) on the portfolio as a linear combination of the two stock returns. Then specify SR in a cell where you compute the SR as RP divided by ptf stdev. The objective function is to maximize SR subject to the first 3 constraints in 5b (see below). Once the global optimal O is found, please record the weights, expected return, sigma, and SR on this ptf. 5. Use \"solver\" to find the optimal weights and construct the Create PDF a1y 2]
