As an assistant to Rachel, you are asked to collect the monthly prices of each stock from Yahoo Finance from 11/23/2010 to 11/2/2015 (market booming
As an assistant to Rachel, you are asked to collect the monthly prices of each stock from Yahoo Finance from 11/23/2010 to 11/2/2015 (market booming time period), and then finish the following tasks: (Johnson and Johnson, IBM, Boeing) Please show the formula and/or work
1. Calculate monthly returns for each stock
2. 3. Calculate the variance and average return for each stock
4. Construct the covariance matrix of these stocks
5. Use Excel Solver function to build the efficient frontier (with and without short sale)
6. Determine the optimal risk portfolio
7. Draw a graph showing the efficient frontier and the CAL line (one graph for the case with short sale and one graph without short sale)
Boeing
Date | Open | Close |
11/2/2015 | 148.380005 | 148.399994 |
10/1/2015 | 131.320007 | 148.070007 |
9/1/2015 | 128.160004 | 130.949997 |
8/3/2015 | 144.440002 | 130.679993 |
7/1/2015 | 140.479996 | 144.169998 |
6/1/2015 | 141.449997 | 138.720001 |
5/1/2015 | 144.410004 | 140.520004 |
4/1/2015 | 149.970001 | 143.339996 |
3/2/2015 | 150.850006 | 150.080002 |
2/2/2015 | 143.720001 | 150.850006 |
1/2/2015 | 131.070007 | 145.369995 |
12/1/2014 | 134.309998 | 129.979996 |
11/3/2014 | 125.349998 | 134.360001 |
10/1/2014 | 127.370003 | 124.910004 |
9/2/2014 | 125.620003 | 127.379997 |
8/1/2014 | 120.449997 | 126.800003 |
7/1/2014 | 127.660004 | 120.480003 |
6/2/2014 | 135.5 | 127.230003 |
5/1/2014 | 128.740005 | 135.25 |
4/1/2014 | 126.239998 | 129.020004 |
3/3/2014 | 127.18 | 125.489998 |
2/3/2014 | 124.239998 | 128.919998 |
1/2/2014 | 136.009995 | 125.260002 |
12/2/2013 | 134.990005 | 136.490005 |
11/1/2013 | 130.899994 | 134.25 |
10/1/2013 | 117.970001 | 130.5 |
9/3/2013 | 105.129997 | 117.5 |
8/1/2013 | 105.989998 | 103.919998 |
7/1/2013 | 102.93 | 105.099998 |
6/3/2013 | 99.360001 | 102.440002 |
5/1/2013 | 91.080002 | 99.019997 |
4/1/2013 | 85.599998 | 91.410004 |
3/1/2013 | 76.699997 | 85.849998 |
2/1/2013 | 74.489998 | 76.900002 |
1/2/2013 | 76.550003 | 73.870003 |
12/3/2012 | 74.410004 | 75.360001 |
11/1/2012 | 70.589996 | 74.279999 |
10/1/2012 | 69.860001 | 70.440002 |
9/4/2012 | 71.5 | 69.599998 |
8/1/2012 | 74.32 | 71.400002 |
7/2/2012 | 74.209999 | 73.910004 |
6/1/2012 | 68.669998 | 74.300003 |
5/1/2012 | 76.589996 | 69.610001 |
4/2/2012 | 74 | 76.800003 |
3/1/2012 | 74.959999 | 74.370003 |
2/1/2012 | 74.959999 | 74.949997 |
1/3/2012 | 74.699997 | 74.18 |
12/1/2011 | 68.699997 | 73.349998 |
11/1/2011 | 64.230003 | 68.690002 |
10/3/2011 | 60.099998 | 65.790001 |
9/1/2011 | 66.959999 | 60.509998 |
8/1/2011 | 71.610001 | 66.860001 |
7/1/2011 | 73.809998 | 70.470001 |
6/1/2011 | 77.949997 | 73.93 |
5/2/2011 | 80.349998 | 78.029999 |
4/1/2011 | 74.290001 | 79.779999 |
3/1/2011 | 71.93 | 73.93 |
2/1/2011 | 70 | 72.010002 |
1/3/2011 | 66.150002 | 69.480003 |
12/1/2010 | 64.660004 | 65.260002 |
11/23/2010 | 63.43 | 63.77 |
IBM
Date | Open | Close |
11/2/2015 | 140.5 | 140.369995 |
10/1/2015 | 145.309998 | 140.080002 |
9/1/2015 | 144.910004 | 144.970001 |
8/3/2015 | 161.699997 | 147.889999 |
7/1/2015 | 163.970001 | 161.990005 |
6/1/2015 | 170.210007 | 162.660004 |
5/1/2015 | 173.199997 | 169.649994 |
4/1/2015 | 160.229996 | 171.289993 |
3/2/2015 | 161.940002 | 160.5 |
2/2/2015 | 154 | 161.940002 |
1/2/2015 | 161.309998 | 153.309998 |
12/1/2014 | 161.639999 | 160.440002 |
11/3/2014 | 164.25 | 162.169998 |
10/1/2014 | 189.910004 | 164.399994 |
9/2/2014 | 192.679993 | 189.830002 |
8/1/2014 | 190.5 | 192.300003 |
7/1/2014 | 181.699997 | 191.669998 |
6/2/2014 | 184.759995 | 181.270004 |
5/1/2014 | 196.309998 | 184.360001 |
4/1/2014 | 193.119995 | 196.470001 |
3/3/2014 | 183.330002 | 192.490005 |
2/3/2014 | 176.020004 | 185.169998 |
1/2/2014 | 187.210007 | 176.679993 |
12/2/2013 | 179.460007 | 187.570007 |
11/1/2013 | 179.809998 | 179.679993 |
10/1/2013 | 185.339996 | 179.210007 |
9/3/2013 | 183.630005 | 185.179993 |
8/1/2013 | 196.649994 | 182.270004 |
7/1/2013 | 192.149994 | 195.039993 |
6/3/2013 | 208.25 | 191.110001 |
5/1/2013 | 201.869995 | 208.020004 |
4/1/2013 | 212.800003 | 202.539993 |
3/1/2013 | 200.649994 | 213.300003 |
2/1/2013 | 204.649994 | 200.830002 |
1/2/2013 | 194.089996 | 203.070007 |
12/3/2012 | 190.759995 | 191.550003 |
11/1/2012 | 194.679993 | 190.070007 |
10/1/2012 | 208.009995 | 194.529999 |
9/4/2012 | 196.610001 | 207.449997 |
8/1/2012 | 196.960007 | 194.850006 |
7/2/2012 | 196.360001 | 195.979996 |
6/1/2012 | 190.119995 | 195.580002 |
5/1/2012 | 207.179993 | 192.899994 |
4/2/2012 | 208.960007 | 207.080002 |
3/1/2012 | 197.229996 | 208.649994 |
2/1/2012 | 193.210007 | 196.729996 |
1/3/2012 | 186.729996 | 192.600006 |
12/1/2011 | 187.009995 | 183.880005 |
11/1/2011 | 181.550003 | 188 |
10/3/2011 | 174.360001 | 184.630005 |
9/1/2011 | 172.710007 | 174.869995 |
8/1/2011 | 182.600006 | 171.910004 |
7/1/2011 | 171.610001 | 181.850006 |
6/1/2011 | 168.899994 | 171.550003 |
5/2/2011 | 172.110001 | 168.929993 |
4/1/2011 | 163.699997 | 170.580002 |
3/1/2011 | 163.149994 | 163.070007 |
2/1/2011 | 162.110001 | 161.880005 |
1/3/2011 | 147.210007 | 162 |
12/1/2010 | 143.610001 | 146.759995 |
11/23/2010 | 144.240005 | 141.460007 |
Johnson and Johnson
Date | Open | Close |
11/2/2015 | 101.18 | 102.169998 |
10/1/2015 | 93.43 | 101.029999 |
9/1/2015 | 92.290001 | 93.349998 |
8/3/2015 | 100 | 93.980003 |
7/1/2015 | 98.300003 | 100.209999 |
6/1/2015 | 100.279999 | 97.459999 |
5/1/2015 | 99.620003 | 100.139999 |
4/1/2015 | 100.459999 | 99.199997 |
3/2/2015 | 102.510002 | 100.599998 |
2/2/2015 | 100.489998 | 102.510002 |
1/2/2015 | 105.050003 | 100.139999 |
12/1/2014 | 107.889999 | 104.57 |
11/3/2014 | 107.830002 | 108.25 |
10/1/2014 | 105.980003 | 107.779999 |
9/2/2014 | 103.300003 | 106.589996 |
8/1/2014 | 99.760002 | 103.730003 |
7/1/2014 | 105.150002 | 100.089996 |
6/2/2014 | 101.540001 | 104.620003 |
5/1/2014 | 100.769997 | 101.459999 |
4/1/2014 | 98.309998 | 101.290001 |
3/3/2014 | 91.129997 | 98.230003 |
2/3/2014 | 88.75 | 92.120003 |
1/2/2014 | 91.139999 | 88.470001 |
12/2/2013 | 94.410004 | 91.589996 |
11/1/2013 | 92.379997 | 94.660004 |
10/1/2013 | 86.589996 | 92.610001 |
9/3/2013 | 86.889999 | 86.690002 |
8/1/2013 | 94.160004 | 86.410004 |
7/1/2013 | 85.720001 | 93.5 |
6/3/2013 | 84.279999 | 85.860001 |
5/1/2013 | 84.5 | 84.18 |
4/1/2013 | 81.389999 | 85.230003 |
3/1/2013 | 75.790001 | 81.529999 |
2/1/2013 | 74.139999 | 76.110001 |
1/2/2013 | 71.019997 | 73.919998 |
12/3/2012 | 69.800003 | 70.099998 |
11/1/2012 | 71.099998 | 69.730003 |
10/1/2012 | 68.919998 | 70.82 |
9/4/2012 | 67.489998 | 68.910004 |
8/1/2012 | 69.480003 | 67.43 |
7/2/2012 | 67.419998 | 69.220001 |
6/1/2012 | 62.040001 | 67.559998 |
5/1/2012 | 64.910004 | 62.43 |
4/2/2012 | 66.040001 | 65.099998 |
3/1/2012 | 65.110001 | 65.959999 |
2/1/2012 | 65.970001 | 65.080002 |
1/3/2012 | 65.589996 | 65.910004 |
12/1/2011 | 64.68 | 65.580002 |
11/1/2011 | 63.68 | 64.720001 |
10/3/2011 | 63.650002 | 64.389999 |
9/1/2011 | 65.660004 | 63.689999 |
8/1/2011 | 65.279999 | 65.800003 |
7/1/2011 | 66.480003 | 64.790001 |
6/1/2011 | 67.019997 | 66.519997 |
5/2/2011 | 65.919998 | 67.290001 |
4/1/2011 | 59.630001 | 65.720001 |
3/1/2011 | 61.25 | 59.25 |
2/1/2011 | 59.700001 | 61.439999 |
1/3/2011 | 62.630001 | 59.77 |
12/1/2010 | 62.18 | 61.849998 |
11/23/2010 | 62.990002 | 61.549999 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started