WEBVTT 00:01.030 --> 00:05.750 Might come to the solution for coding exercise 16 and we import pandas. 00:05.830 --> 00:14.170 PI and Matt prop lip and then we import our five U.S. stocks and the S&P 500 Total Return and we have 00:14.170 --> 00:18.500 to select the appropriate price data to calculate total returns. 00:18.760 --> 00:26.680 And uh we selected the adjusted to close prices because they include the price returns and dividends 00:27.340 --> 00:36.310 and we have actually then to that aframe stocks and S&P 500 and then we select for both data frames 00:36.430 --> 00:42.640 the five year period from 240 14 to 18 and we actually overwrite both data frames 00:45.190 --> 00:54.040 so here we have our six constituents from uh the end of 213 to to 18 and the same we have for the S&P 00:54.040 --> 01:03.460 500 Total Return Index and then we can also add to the S&P 500 Total Return Index here to our stocks 01:03.460 --> 01:11.800 data frame by creating here an additional column and we can simply do this here by creating a new column 01:11.800 --> 01:21.010 a market portfolio and assign here the column in the S&P 500 data frame at just that close and actually 01:21.010 --> 01:27.840 the values here get automatically aligned to the index in our stocks data frame. 01:27.880 --> 01:30.360 So this is straightforward actually 01:32.940 --> 01:40.050 so here we are on the right hand side also the market portfolio and then we can create the daily simple 01:40.050 --> 01:48.240 returns with the percentage change method and we create the data frame rate and uh then we can also 01:48.240 --> 01:52.980 create the summary data frame with annualized the risk and return. 01:52.980 --> 02:00.940 And also here we use uh the user defined function annualized risk and return so here we have the summary 02:00.940 --> 02:07.380 data frame with our six constituents and the market portfolio and then we can also visualize so that 02:07.430 --> 02:12.830 we're in a total risk return framework. 02:13.030 --> 02:21.630 So yes the market portfolio and then the next step to calculate the sharp ratio we need the risk free 02:21.630 --> 02:30.540 return and we assume that it is the one point seven percent and then we can calculate the sharp razor 02:30.540 --> 02:38.170 which is the return minus the risk free return divided by the risk and uh we create the additional columns 02:38.180 --> 02:38.550 harp 02:41.250 --> 02:49.200 so here we have sharp and actually we have fear the annualized return then the annualized risk and standard 02:49.200 --> 02:56.850 deviation units and the sharp ratio and we can also convert the risk in standard deviation units to 02:56.910 --> 03:01.340 variance units by simply taking it to the power of two. 03:01.350 --> 03:08.010 So we create the additional column total risk and variance units and we use the N put out the power 03:08.010 --> 03:11.530 method. 03:11.540 --> 03:18.140 So now here we have the total risk and variance units and then we can also create the annualized covariance 03:18.140 --> 03:26.800 matrix and we call you the code method on the daily returns data frame and annualize that the covariance 03:26.870 --> 03:29.720 by multiplying it with two hundred fifty two. 03:30.980 --> 03:32.030 So that's here. 03:32.030 --> 03:39.650 The covariance matrix and he on the right hand side we can actually see the core variances of the constituents 03:39.660 --> 03:42.030 of the market portfolio. 03:42.250 --> 03:47.410 And that's actually the systematic risk of uh the constituents. 03:47.750 --> 03:54.000 And therefore we can also add the column systematic risk and variance units by taking here the core 03:54.000 --> 04:02.100 variances with the market portfolios and then having the total risk and variance units and uh the systematic 04:02.100 --> 04:08.700 risk then we can also calculate the and systematic risk which is simply the total risk minus the systematic 04:08.700 --> 04:09.050 risk. 04:10.080 --> 04:16.800 And also here we create an additional column for our summary data frame and let's have a look so here 04:16.800 --> 04:18.150 we have a total risk. 04:18.200 --> 04:21.330 Systematic risk and and systematic risk. 04:21.330 --> 04:26.310 And then we can also visualize systematic risk and and systematic risk of each stock and the market 04:26.310 --> 04:28.500 portfolio with a pop plot. 04:28.620 --> 04:34.680 And the question is here which company shows most likely the highest portion of company specific risk 04:34.710 --> 04:41.430 that can be diversified away in a diversified portfolio like the market portfolio. 04:41.430 --> 04:47.700 So we select your constituents and the market portfolio and the last three columns the total risk systematic 04:47.700 --> 04:53.170 risk and on systematic risk and create a stack the bomb plot. 04:53.250 --> 04:59.860 So let's have a look here a. factually and blew the systematic risk. 04:59.860 --> 05:07.480 And actually the evaded ever it's a systematic risk off the constituents that give us actually the systematic 05:07.480 --> 05:12.940 risk of the market portfolio which is also the total risk of the market portfolio. 05:12.970 --> 05:19.300 So by definition the market portfolio does not contain any answers dramatic risk and in green we can 05:19.300 --> 05:26.170 see here the and systematic risk of the constituents that can be diversified that way if we create a 05:26.170 --> 05:30.340 portfolio instead of investing into these single stocks. 05:30.340 --> 05:38.380 And obviously the highest portion of and systematic risk has the Facebook stock here and now on the 05:38.380 --> 05:45.580 next level can also normalize the systematic risk and create the beta factor and we can create a new 05:45.580 --> 05:46.690 column. 05:46.690 --> 05:54.640 So the beta factor is actually the systematic risk divided by the total risk of the market portfolio 05:57.010 --> 06:00.280 and here we have on the right hand side the beta factor. 06:00.280 --> 06:04.160 And by definition the market portfolio as a beta effect of 1. 06:04.240 --> 06:08.230 And the question is here which company has the highest beta factor. 06:08.320 --> 06:15.580 And the question could also be which the stock is the most cyclical stock and uh that's obviously the 06:15.580 --> 06:20.440 Facebook stock with a beta effect of 1 point to 6. 06:20.440 --> 06:27.100 Next we have to create the security market line and we have to determine the stocks that over performed 06:27.130 --> 06:32.550 and underperformed the market in terms of systematic risk return. 06:32.590 --> 06:36.100 So we created a scatter plot twists on the x axis. 06:36.130 --> 06:39.200 The beta factor and on the y axis. 06:39.310 --> 06:43.580 The return and we actually also label here the points. 06:43.630 --> 06:47.620 Then we also create a pointer for the risk free asset. 06:47.900 --> 06:54.200 And then we create a line from the risk free asset to the market portfolio. 06:54.370 --> 06:55.540 So let's have a look here. 06:56.140 --> 07:03.120 So that's the security market line from the risk free asset to the market portfolio and actually there's 07:03.150 --> 07:08.550 only one stock that is below the security market line and it's American Express. 07:08.640 --> 07:14.820 So American Express throaty and underperformance and actually all of the other stocks over performed 07:14.970 --> 07:22.470 the market in terms of systematic risk and return and finally we can also add another column with the 07:22.500 --> 07:25.140 uh required to cap em return. 07:25.380 --> 07:34.200 So according to the cap I'm model and the D cap M is simply the return of the risk free asset plus the 07:34.200 --> 07:42.160 return off for the market portfolio minus the risk free asset times that paid off the stock and then 07:42.160 --> 07:49.390 you can also calculate an add to the IFR for each constituent which is simply the actual return minus 07:49.540 --> 07:54.080 B cap m return and it's look here. 07:54.280 --> 07:59.920 And the question is here which companies showed the highest and the lowest Alpha and we can either answer 07:59.950 --> 08:01.360 this graphically. 08:01.720 --> 08:08.560 So the alpha of nothing else than the distance between the constituents here and the security market 08:08.560 --> 08:15.630 line and the obviously the highest that far could have uh the Facebook stock. 08:15.940 --> 08:17.710 And as we have seen before. 08:17.710 --> 08:23.090 So the only stock that has a negative Alpha is actually American Express. 08:23.090 --> 08:28.870 But we can also make this more explicit by calculating the alpha and let's have a look here. 08:29.230 --> 08:36.640 So for Facebook we have a positive five five twelve percent because uh the required return according 08:36.640 --> 08:42.160 to the cap and model is 11 percent and the realized return is 22 percent. 08:42.190 --> 08:46.930 So that's a difference of twelve percent around that here actually. 08:47.050 --> 08:53.830 And as we have seen before the American Express stock show to the lowest alpha with minus 4 percent. 08:54.400 --> 09:00.760 So that was context size 16 I hope you had fun and talk to senior also in the next exercise by.