1. Download the stock price data on LMS labeled "LAB05_Data_2023_Students.x|sx." 2. Choose 10 stocks, the SPY Index (column "OP"), and the risk-free rate (3-month Treasury rate in column "RW"). Copy the columns to a new tab in the excel sheet (it will make working with the data easier in the next steps). Jote: if you want to replicate my results exactly, choose the following tickers: 3. Convert the monthly prices to monthly excess returns for each of your 10 stocks and the SPY ETF. Note that these prices are already adjusted for dividends. Raw returns are defined as: Ri,t=Padji,t1Padji,t1 where, Rj,t is the return for stock i in period t and Pi.ddj,t is the adjusted price of stock i in period t. Finally, excess returns are defined as the return above the risk-free rate of return. In this case, we are using the 3-month Treasury as the risk-free rate of return so excess returns are: R^i,t=Ri,tRFt where, R^i,t is the excess stock return for stock i in period t and RFt is the risk-free return over the same period (i.e., the 3-month Treasury rate in column "RW"). Note that I have already converted the 3-month Treasury rate to a monthly return. 4. Now, you should have 10 stocks, and the market return. For each stock, compute the sample average return and standard deviation. Plot the return on the y-axis and risk on the x-axis. Is there a pattern? 5. For your 11 assets, build a correlation matrix. 6. Next, we are going to choose two stocks and form portfolios of the two stocks. Choose weights of each stock on the range 0%,10%,20%,,100%. There should be 11 portfolios. Assuming you rebalance every month (so the weights stay constant), compute the time series of portfolio returns for each portfolio (see, eq. (11.1) on Slide 12 of Chapter 11). Plot the mean return vs. the standard deviation of the 11 portfolios. The plot should look like the plot on Slide 57 of Chapter 11. 7. Finally, form portfolios of stocks by adding one stock at a time. Each portfolio should be equally weighted (e.g., the three stock portfolio should have 33.3% weight in each stock). Plot the portfolio volatility versus the number of stocks. The plot should look like the one on slide 49 of Chapter 7. \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline & A & B & C & D & E & F & G & H & 1 & J & K & L & M & N & 0 & P & Q & R \\ \hline 2 & 4 & 2013 & 7 & 7/22/13 & 18.242048 & 14.031614 & 6.998472 & 48.803051 & 25.819118 & 37.41967 & 68.510696 & 28.187878 & 26.433607 & 47.84 & 61.196823 & 1 & 3.33E-05 & 7/31/13 \\ \hline 3 & 5 & 2013 & 8 & 8/1/13 & 15.2347 & 15.207001 & 6.984281 & 46.072811 & 24.506275 & 35.467525 & 64.821663 & 28.236654 & 24.526354 & 47.990002 & 57.288338 & 1 & 3.33E-05 & 8/30/13 \\ \hline 4 & 6 & 2013 & 9 & 9/3/13 & 17.874382 & 14.880213 & 7.304974 & 45.041027 & 25.945204 & 39.133251 & 65.91111 & 32.650604 & 24.380823 & 50.889999 & 56.552185 & 1 & 1.67E05 & 9/30/13 \\ \hline 5 & 1 & 2013 & 10 & 10/1/13 & 20.712025 & 16.314396 & 7.395788 & 49.602818 & 27.229946 & 42.188023 & 75.550789 & 34.052994 & 24.990896 & 54.279999 & 58.905243 & 1 & 1.67E05 & 10/31/13 \\ \hline 6 & 2 & 2013 & 11 & 11/1/13 & 22.135567 & 17.45726 & 7.781787 & 53.347351 & 28.414511 & 41.366623 & 76.362274 & 35.572247 & 25.987747 & 56.860001 & 61.860382 & 1 & 3.33E-05 & 11/29/13 \\ \hline 7 & 3 & 2013 & 12 & 12/2/13 & 23.804222 & 17.612665 & 8.209826 & 57.020153 & 30.575016 & 41.748745 & 73.634567 & 35.45826 & 28.118879 & 59.779999 & 66.969131 & 1 & 4.17E-05 & 12/31/13 \\ \hline 8 & 7 & 2014 & 1 & 1/2/14 & 31.628973 & 15.715834 & 7.679058 & 54.172005 & 30.282389 & 39.812218 & 77.095665 & 32.847576 & 27.32439 & 58.75 & 60.986877 & 1 & 5.83E05 & 1/31/14 \\ \hline 9 & 11 & 2014 & 2 & 2/3/14 & 34.815445 & 16.619671 & 7.951018 & 58.507675 & 31.546337 & 42.495422 & 77.786461 & 35.413338 & 28.056566 & 55.110001 & 64.157875 & 1 & 4.17E-05 & 2/28/14 \\ \hline 10 & 12 & 2014 & 3 & 3/3/14 & 34.504341 & 16.951281 & 8.312556 & 59.883587 & 31.710882 & 45.097958 & 78.309341 & 33.40522 & 28.142246 & 53.91 & 65.097565 & 1 & 4.17E-05 & 3/31/14 \\ \hline 11 & 13 & 2014 & 4 & 4/1/14 & 33.061943 & 18.636179 & 8.097351 & 58.388977 & 30.718794 & 41.738785 & 79.729927 & 32.993664 & 28.605658 & 47.18 & 68.249809 & 1 & 3.33E-05 & 4/30/14 \\ \hline 12 & 14 & 2014 & 5 & 5/1/14 & 37.860497 & 20.103012 & 8.482021 & 62.885361 & 32.776737 & 43.044262 & 79.800934 & 34.894478 & 29.469389 & 50.080002 & 67.451767 & 1 & 2.50E05 & 5/30/14 \\ \hline 13 & 15 & 2014 & 6 & 6/2/14 & 40.500172 & 20.659101 & 8.888813 & 60.516735 & 34.248837 & 47.721848 & 79.600159 & 35.184856 & 29.406576 & 48.810001 & 67.552399 & 1 & 3.33E-05 & 6/30/14 \\ \hline 14 & 16 & 2014 & 7 & 7/1/14 & 36.718349 & 21.252661 & 8.632043 & 61.531303 & 32.537968 & 46.96632 & 74.339752 & 34.994289 & 28.243299 & 54.049999 & 66.384956 & 1 & 1.67E-05 & 7/31/14 \\ \hline 15 & 17 & 2014 & 8 & 8/1/14 & 36.746693 & 22.899929 & 8.964182 & 64.021233 & 33.646442 & 50.551758 & 77.295578 & 35.746166 & 29.93679 & 57.080002 & 67.19873 & 1 & 2.50E05 & 8/29/14 \\ \hline 16 & 18 & 2014 & 9 & 9/2/14 & 33.533253 & 22.508955 & 9.297692 & 64.134033 & 33.608738 & 52.112881 & 77.591331 & 40.592712 & 30.031752 & 55.119999 & 63.543537 & 1 & 2.50E05 & 9/30/14 \\ \hline 17 & 8 & 2014 & 10 & 10/1/14 & 39.179264 & 24.128708 & 10.333027 & 69.379539 & 32.515182 & 52.35762 & 82.424149 & 42.308342 & 30.733501 & 59.759998 & 65.340721 & 1 & 1.67E05 & 10/31/14 \\ \hline 18 & 9 & 2014 & 11 & 11/3/14 & 45.982349 & 26.685846 & 10.627326 & 73.866844 & 32.492451 & 57.49361 & 87.713753 & 45.184429 & 32.105106 & 60.099998 & 61.613731 & 1 & 1.67E05 & 11/28/14 \\ \hline 19 & 10 & 2014 & 12 & 12/1/14 & 50.81461 & 24.767368 & 10.551601 & 77.869049 & 33.219543 & 58.222221 & 87.556633 & 43.881317 & 31.88571 & 57 & 62.913475 & 1 & 2.50E05 & 12/31/14 \\ \hline 20 & 19 & 2015 & 1 & 1/2/15 & 46.503479 & 26.288683 & 9.698269 & 79.647133 & 31.042109 & 50.894756 & 81.812469 & 42.101398 & 31.467958 & 54.48 & 59.490494 & 1 & 1.67E05 & 1/30/15 \\ \hline 21 & 23 & 2015 & 2 & 2/2/15 & 45.477364 & 28.937967 & 10.037572 & 84.28022 & 32.114632 & 54.208103 & 83.100739 & 44.452557 & 31.323355 & 64.019997 & 60.706203 & 1 & 1.67E05 & 2/27/15 \\ \hline 22 & 24 & 2015 & 3 & 3/2/15 & 50.110546 & 28.030136 & 9.689427 & 83.744682 & 32.753571 & 48.64209 & 81.821373 & 45.921795 & 30.550655 & 66.970001 & 58.27906 & 1 & 1.67E05 & 3/31/15 \\ \hline 23 & 25 & 2015 & 4 & 4/1/15 & 45.941635 & 28.192324 & 10.558322 & 80.84304 & 30.099325 & 49.067673 & 83.792244 & 45.239796 & 29.983852 & 63.509998 & 59.903992 & 1 & 2.50E05 & 4/30/15 \\ \hline 24 & 26 & 2015 & 5 & 5/1/15 & 40.309521 & 29.470539 & 10.022153 & 83.358932 & 30.848372 & 50.211727 & 83.158165 & 46.662163 & 30.089115 & 63.189999 & 58.899334 & 1 & 8.33E-06 & 5/29/15 \\ \hline 25 & 27 & 2015 & 6 & 6/1/15 & 37.997688 & 28.373432 & 9.601623 & 85.39447 & 29.411434 & 47.308105 & 81.613258 & 49.57655 & 29.466291 & 61.720001 & 57.516705 & 1 & 1.67E05 & 6/30/15 \\ \hline 26 & 28 & 2015 & 7 & 7/1/15 & 38.149918 & 27.439177 & 9.198737 & 91.864029 & 29.194073 & 44.648552 & 88.54464 & 52.881035 & 29.637703 & 70.940002 & 54.758423 & 1 & 8.33E-06 & 7/31/15 \\ \hline 27 & 29 & 2015 & 8 & 8/3/15 & 37.170902 & 25.622818 & 8.107176 & 83.638977 & 27.887684 & 42.589806 & 82.04454 & 51.288452 & 32.543522 & 68.940002 & 52.500603 & 1 & 6.66E05 & 8/31/15 \\ \hline 28 & 30 & 2015 & 9 & 9/1/15 & 37.027863 & 25.06382 & 7.965049 & 78.803604 & 25.712917 & 42.496319 & 84.694069 & 56.58107 & 32.055935 & 70.559998 & 51.879589 & 1 & 2.50E05 & 9/30/15 \\ \hline 29 & 20 & 2015 & 10 & 10/1/15 & 44.074894 & 27.154366 & 7.991697 & 80.955971 & 28.160551 & 57.04707 & 92.981697 & 60.289635 & 33.931412 & 80.599998 & 57.733913 & 1 & 0 & 10/30/15 \\ \hline 30 & 21 & 2015 & 11 & 11/2/15 & 39.429932 & 26.996754 & 8.470601 & 77.112244 & 27.287287 & 56.937183 & 92.546738 & 60.864796 & 33.808018 & 89.440002 & 57.477043 & 1 & 6.66E05 & 11/30/15 \\ \hline 31 & 22 & 2015 & 12 & 12/1/15 & 40.471592 & 24.020952 & 7.731694 & 80.128204 & 24.644346 & 59.40414 & 99.600639 & 57.654537 & 33.725777 & 87.360001 & 54.865715 & 1 & 0.0001748 & 12/31/15 \\ \hline 32 & 31 & 2016 & 1 & 1/4/16 & 37.260628 & 22.213551 & 6.858712 & 79.51664 & 26.756042 & 57.382591 & 100.47695 & 57.202522 & 32.998608 & 75.599998 & 54.795334 & 1 & 0.0001831 & 1/29/16 \\ \hline 33 & 35 & 2016 & 2 & 2/1/16 & 39.288372 & 22.184958 & 7.245668 & 79.994087 & 27.370682 & 58.49939 & 101.94787 & 56.815075 & 36.579479 & 84.489998 & 56.926235 & 1 & 0.0002912 & 2/29/16 \\ \hline 34 & 36 & 2016 & 3 & 3/1/16 & 39.297958 & 25.007116 & 7.72891 & 85.394592 & 28.561047 & 62.877766 & 105.9825 & 56.850582 & 38.994801 & 88.540001 & 59.369495 & 1 & 0.0002746 & 3/31/16 \\ \hline 35 & 37 & 2016 & 4 & 4/1/16 & 33.241798 & 21.508095 & 8.185143 & 83.07679 & 30.13747 & 60.399273 & 98.639122 & 54.510696 & 38.444065 & 86.400002 & 62.785774 & 1 & 0.0001915 & 4/29/16 \\ \hline 36 & 38 & 2016 & 5 & 5/2/16 & 30.666254 & 23.051794 & 7.988687 & 79.728905 & 33.345245 & 63.454784 & 100.09676 & 51.070267 & 40.146366 & 85.459999 & 63.757496 & 1 & 0.0001831 & 5/31/16 \\ \hline 37 & 39 & 2016 & 6 & 6/1/16 & 27.20657 & 22.068407 & 7.882892 & 79.141968 & 33.329597 & 63.733227 & 109.09346 & 51.20092 & 42.607815 & 86.459999 & 67.138031 & 1 & 0.0002497 & 6/30/16 \\ \hline 38 & 40 & 2016 & 7 & 7/1/16 & 34.116333 & 24.055948 & 8.562974 & 76.983315 & 35.823227 & 65.873642 & 102.80084 & 51.479172 & 43.489525 & 86.610001 & 63.707352 & 1 & 0.000233 & 7/29/16 \\ \hline 39 & 41 & 2016 & 8 & 8/1/16 & 34.989559 & 24.62492 & 8.602793 & 77.547897 & 34.513851 & 60.716106 & 101.6185 & 53.464134 & 43.548298 & 74.449997 & 62.943222 & 1 & 0.0002413 & 8/31/16 \\ \hline 40 & 42 & 2016 & 9 & 9/1/16 & 35.288364 & 26.237965 & 9.278119 & 73.88636 & 35.111309 & 61.110592 & 100.81245 & 48.971584 & 41.155079 & 78.239998 & 63.044346 & 1 & 0.0002746 & 9/30/16 \\ \hline 41 & 32 & 2016 & 10 & 10/3/16 & 39.134327 & 26.351685 & 9.281162 & 70.171486 & 34.086269 & 65.844452 & 91.437721 & 46.674141 & 40.669777 & 84.019997 & 60.183968 & 1 & 0.0002663 & 10/31/16 \\ \hline 42 & 33 & 2016 & 11 & 11/1/16 & 44.876511 & 25.782467 & 10.949431 & 64.155602 & 38.025806 & 70.495224 & 92.396782 & 46.571827 & 45.005531 & 78.849998 & 63.617191 & 1 & 0.0002912 & 11/30/16 \\ \hline 43 & 34 & 2016 & 12 & 12/1/16 & 45.118099 & 27.01886 & 10.986126 & 65.841072 & 35.021988 & 69.470978 & 91.945435 & 47.449329 & 46.79731 & 76.07 & 65.774185 & 1 & 0.0003991 & 12/30/16 \\ \hline 44 & 43 & 2017 & 1 & 1/3/17 & 42.760254 & 28.308924 & 14.184422 & 66.155167 & 37.394203 & 75.148407 & 97.593315 & 49.381641 & 44.605469 & 80.209999 & 61.132244 & 1 & 0.0004406 & 1/31/17 \\ \hline 45 & 47 & 2017 & 2 & 2/1/17 & 44.898849 & 32.096031 & 14.903926 & 67.640961 & 35.659687 & 80.071083 & 106.7943 & 53.358318 & 44.826542 & 82.470001 & 59.801315 & 1 & 0.000424 & 2/28/17 \\ \hline 46 & 48 & 2017 & 3 & 3/1/17 & 40.966812 & 33.658775 & 14.287024 & 65.894943 & 36.027328 & 84.469109 & 106.82998 & 52.185516 & 44.146328 & 87.110001 & 60.30872 & 1 & 0.0005235 & 3/31/17 \\ \hline 47 & 49 & 2017 & 4 & 4/3/17 & 41.276726 & 33.656425 & 15.603701 & 69.647324 & 35.556885 & 87.267868 & 105.30418 & 51.885872 & 45.243374 & 88.919998 & 60.043983 & 1 & 0.000656 & 4/28/17 \\ \hline 48 & 50 & 2017 & 5 & 5/1/17 & 46.985203 & 35.93858 & 16.687786 & 64.907852 & 35.719402 & 92.901245 & 105.28794 & 49.619778 & 46.68959 & 90.160004 & 59.757305 & 1 & 0.000689 & 5/31/17 \\ \hline 49 & 51 & 2017 & 6 & 6/1/17 & 48.838982 & 33.882397 & 16.807926 & 67.974594 & 37.311005 & 81.744171 & 105.56667 & 55.435856 & 43.069778 & 92.959999 & 59.928059 & 1 & 0.000813 & 6/30/17 \\ \hline 50 & 52 & 2017 & 7 & 7/3/17 & 48.955452 & 34.990479 & 15.199841 & 67.965935 & 33.516232 & 82.744644 & 100.70167 & 55.482838 & 45.325394 & 101.17 & 59.415836 & 1 & 0.0008791 & 7/31/17 \\ \hline \end{tabular}