WEBVTT 00:00.540 --> 00:05.030 Welcome come to the solution for coding exercise 40 and we import pandas. 00:05.100 --> 00:12.920 PI and the med plus lip and then we import to the six U.S. stocks and we select the appropriate price 00:12.920 --> 00:19.730 data to create a price return index so we can choose between close prices and adjusted close prices 00:20.240 --> 00:26.850 and as the price return excludes dividends we have to choose here the close prices. 00:27.110 --> 00:28.360 So we import. 00:28.400 --> 00:37.470 The US stocks the US we file and we only select the close column and then we want to create the index 00:37.560 --> 00:40.850 for the time period from 214 to 18. 00:41.070 --> 00:46.410 And therefore we select here the period and actually we overwrite stocks 00:52.490 --> 00:59.330 and then we can also normalize the stocks by having a base value of 100 at the very first day here on 00:59.450 --> 01:07.680 the thirty first of December 2013 and we can do this by dividing each and every row of the stocks data 01:07.680 --> 01:14.540 frame by the very first row and then multiplying with 100 and by doing so we create here the norm data 01:14.540 --> 01:15.140 frame. 01:15.890 --> 01:20.450 So we we are starting with the 100 and we can also plot this here 01:24.960 --> 01:31.110 and then we have to add to the norm data frame the price weighted index with the six constituents. 01:31.110 --> 01:36.540 So we are creating here the additional column price weighted index and the intuition behind a price 01:36.540 --> 01:43.470 weighted index is pretty simple so price weighted index means so that we buy an equal amount of shares 01:43.590 --> 01:45.090 for our constituents. 01:45.120 --> 01:52.080 So for example one sharp constituent and therefore we simply sum up the stock prices of the constituents 01:52.560 --> 01:58.890 and with this we already created our price weighted index and then the next step we normalize the price 01:58.890 --> 02:06.280 weighted index by dividing each row by the very first row and multiplying it with 100. 02:06.450 --> 02:12.270 So let's have a look here and TV off on the right hand side the price weighted index and we can also 02:12.270 --> 02:20.080 plot the six constituents and the price weighted index so here we have in blue the price weighted index 02:21.300 --> 02:27.820 and next we are coming to the evaluated index and this is a bit more complicated because we await the 02:27.830 --> 02:35.420 constituents by the market capitalization rates and therefore we use for example the amount of stocks 02:35.420 --> 02:36.230 outstanding. 02:36.620 --> 02:45.890 So first of all we have the stocks prices still imported here then we have set the norm data frame and 02:45.890 --> 02:47.580 then we import the listings. 02:47.660 --> 02:53.890 Clean since we filed with the market capitalizations and we set December columns. 02:53.910 --> 02:55.190 That being the index 02:57.810 --> 03:00.230 so yes the listings stayed off frame. 03:00.450 --> 03:03.790 And here we have to slice of the listings that frame for our six stocks. 03:03.790 --> 03:10.770 So we are only interested in the six stocks and in the columns the last price and market cap and we 03:10.770 --> 03:17.010 can do the C of course with a local operator and pass the six the stock tickers that we can get from 03:17.010 --> 03:19.660 the stocks data frame as column labels. 03:19.740 --> 03:26.480 So here and then we only select the columns last price and market cap 03:30.340 --> 03:36.060 so here we have now the last price and the market capitalization based on April 2 19. 03:36.070 --> 03:42.850 And with this information we can calculate the number of shares outstanding and we can simply do this 03:43.060 --> 03:53.600 by dividing market cap by the last price and we create a new column shares so that's the number of shares 03:53.600 --> 04:01.630 outstanding and with this we can calculate the market cap for each stock on each timestamp and we use 04:01.750 --> 04:07.420 the simplified assumption that the amount of outstanding shares do not change over time. 04:07.540 --> 04:13.120 So we assume that in the last five years the amount of shares outstanding are constant. 04:13.150 --> 04:19.510 So this is a simplification here and we can calculate the market capitalization over time by multiplying 04:19.510 --> 04:26.080 the stocks data frame by the shares column here and the re is safe. 04:26.200 --> 04:34.310 The resulting data from the Red Bull market cap so here we have the market caps starting from the thirty 04:34.310 --> 04:36.010 first of December to 13. 04:36.620 --> 04:47.150 And we can also plot this here so it's actually no surprise that Facebook has the highest market capitalization 04:47.870 --> 04:49.490 and having the market caps. 04:49.520 --> 04:55.550 Then we can also calculate the rates corresponding to the market caps and we save the rates and the 04:55.550 --> 04:58.040 variable rates evaluated and next. 04:58.100 --> 05:04.520 And actually we simply have to divide each and every element in a row by the sum of the row 05:07.770 --> 05:11.220 so here we have the rates corresponding to the market caps 05:15.130 --> 05:17.800 and initially Wal-Mart had the highest rates. 05:17.800 --> 05:25.490 And also Procter and Gamble and now at the end of to 80 in the Facebook has the highest rates and we 05:25.490 --> 05:31.230 can also plot the evolution of rates over time with the plot method here. 05:31.460 --> 05:37.790 And we can see that the rate of Facebook increased over time from below 20 percent to about 30. 05:37.790 --> 05:39.800 And meanwhile 40 percent. 05:40.600 --> 05:46.830 All right now to calculate the evaluated and next we have to wait. 05:46.860 --> 05:53.510 Uh the uh returns of the constituents by the respective rates and therefore first of all we have to 05:53.510 --> 05:55.060 create the daily returns. 05:55.070 --> 05:58.100 And of course you can do the same with the percentage change method. 05:59.670 --> 06:03.510 And actually we create a new column for our normalized data frame. 06:03.510 --> 06:05.000 Value weighted index. 06:05.100 --> 06:12.310 And we pass the value of one hundred to each and every element so in the next step we then overwrite 06:12.370 --> 06:19.580 the value we're starting from the second of January and this is exactly what we are doing here and exercise 06:19.600 --> 06:21.300 one hundred forty four. 06:21.310 --> 06:24.370 So we overwrite the other values. 06:24.400 --> 06:26.510 And actually we wait to the returns. 06:26.620 --> 06:28.570 By the market cap waits. 06:28.810 --> 06:34.100 And then we calculate the cumulative investment multiple for each and every timestamp. 06:34.120 --> 06:39.100 So first of all we calculate the weighted average return for each and every timestamp. 06:39.100 --> 06:41.110 This is what we are doing here. 06:41.350 --> 06:48.580 And then we create a cumulative investment multiple by adding 1 and using the Crump road method. 06:48.640 --> 06:54.350 And finally we multiply with 100 because our base value is there 100. 06:54.400 --> 06:56.380 So we over variety the elements. 06:56.380 --> 06:57.070 And let's have a look. 06:59.430 --> 07:00.950 And here we have on the right hand side. 07:00.950 --> 07:03.320 Now our value weighted index. 07:03.540 --> 07:09.210 And then we can also compare the price weighted index and the evaluated index with the plot. 07:09.210 --> 07:13.240 So we selected the last two columns and the plot. 07:13.290 --> 07:14.550 The both indexes 07:17.560 --> 07:24.310 and it's actually no surprise so that they are closely together but uh at least the return of the value 07:24.310 --> 07:28.660 weighted index is a bit better than the return of the price weighted index. 07:28.720 --> 07:30.730 And with this we are finished here. 07:30.730 --> 07:36.580 I hope you had fun with exercise 14 and uh looking forward to seeing you also in exercise 15 by.