WEBVTT 00:01.630 --> 00:08.110 In this end the next video we will create a value weighted index and evaluated index actually best reflects 00:08.110 --> 00:12.630 how the total market ought to say the average investor is invested. 00:12.700 --> 00:17.770 However you will see that the creating the value weighted index is a bit more complicated and requires 00:17.800 --> 00:24.490 also more data because for each constituent and for each single timestamp we need to calculate the market 00:24.490 --> 00:28.660 capitalization and to calculate the market cap. 00:28.690 --> 00:33.070 We need the stock price and the amount of stocks outstanding. 00:33.070 --> 00:39.130 So let's start here and we have still imparted the stocks data frame with all of these stock prices 00:39.160 --> 00:40.330 over time. 00:40.510 --> 00:49.220 So are our six constituents and we also have already imparted the norm data frame with the normalized 00:49.220 --> 00:57.430 the prices and also including here the price weighted index and the equal weighted index and now as 00:57.430 --> 00:59.840 additional data requirement we import. 00:59.860 --> 01:08.470 The listing is clean CSP that we actually derived in the exercises of part 1 and we save the data frame 01:08.470 --> 01:09.920 and the variable listings. 01:10.760 --> 01:17.200 So let's have a look so here we have all current listings on the New York Stock Exchange a nest egg 01:17.230 --> 01:22.180 and Amex and that's also if I look at the last five rows. 01:22.180 --> 01:26.980 So we have f in total over six thousand eight hundred listings. 01:26.980 --> 01:35.100 And let's also call here the informal method and yet our listings data frame we have exactly what we 01:35.100 --> 01:35.370 need. 01:35.370 --> 01:37.480 So we have the last price. 01:37.500 --> 01:45.410 So this is based on the end of Q1 2019 and we have the corresponding market capitalization. 01:45.750 --> 01:54.120 So for example if we have a look here at American Airlines then we have here the last price of thirty 01:54.120 --> 01:59.780 four dollars and the corresponding market capitalization which is year actually acquired high number. 01:59.770 --> 02:06.750 So in the billions and having those numbers so then we can actually calculate the amount of shares outstanding 02:07.200 --> 02:13.200 by dividing the market capitalization by the last price. 02:13.210 --> 02:19.690 So first of all we set the December column as being the index of our listings data frame. 02:19.750 --> 02:21.940 So this makes sense here. 02:21.940 --> 02:27.640 So we have you on the left hand side now our new index and actually we are only interested in the ticker 02:27.840 --> 02:32.350 symbols Amazon Boeing Disney IBM Coca-Cola and Microsoft. 02:32.350 --> 02:38.640 So let's say if you're a list with our ticker in the Ariba ticker and then let slide see our listings 02:38.640 --> 02:45.500 data frame and we only selected our six constituents and we are only interested in the columns. 02:45.500 --> 02:48.580 Last price and market capitalization. 02:48.580 --> 02:53.520 So we are overriding the listings data frame and let's have a look here. 02:53.530 --> 02:55.410 So here we have our six constituents. 02:55.420 --> 03:02.790 And the last price and the market cap and then actually we can calculate the the number of shares outstanding 03:02.790 --> 03:06.120 by dividing a market cap by the last price. 03:06.300 --> 03:08.910 And we can do this same with the DIF method. 03:08.910 --> 03:09.780 So let's do this 03:12.480 --> 03:15.770 so here we are getting the shares outstanding. 03:16.380 --> 03:23.730 So still these are quite large numbers for those uh large companies and we can also create a new column 03:23.910 --> 03:29.670 for our listings that are framed so the shares column by dividing the two columns. 03:29.670 --> 03:31.230 So let's have a look. 03:31.230 --> 03:35.890 So we have now on the right hand side the amount of shares for each company. 03:36.750 --> 03:39.510 And that's again have a look at our stocks data frames. 03:39.520 --> 03:43.620 So here we have uh the stock prices over time. 03:43.620 --> 03:50.730 And actually our listings data frame and uh the amount of shares that we calculated here are based on 03:50.730 --> 03:54.560 the end of Q1 2000 and nineteen. 03:54.630 --> 04:01.260 So actually we would require for each and every time stamp here the amount of outstanding shares and 04:01.260 --> 04:07.860 actually the amount of outstanding shares can change for example if the company issues new shares or 04:08.070 --> 04:16.790 distributes a share dividend also in a stock split But uh for the time being so this is still a simplification. 04:16.950 --> 04:23.940 We assume that the amount of outstanding shares for our six constituents they do not change uh from 04:24.100 --> 04:26.160 two thousand and thirteen. 04:26.400 --> 04:27.770 And uh two days. 04:27.770 --> 04:34.410 So we are just taking for each and every time stamp here this amount of outstanding shares. 04:34.410 --> 04:37.380 So as I said uh this is a simplification here. 04:37.530 --> 04:40.970 And actually we would require even more data. 04:40.980 --> 04:47.280 So for each and every time stamp the amount of outstanding shares but for the time being it's sufficient 04:47.280 --> 04:48.850 for us to work with. 04:48.870 --> 04:51.100 This the simplification here. 04:51.510 --> 04:57.090 And actually what we have to do now we have to multiply for each and every time stamp here the stock 04:57.090 --> 05:04.560 prices with the the amount of shares to calculate uh the uh market capitalization. 05:04.650 --> 05:08.380 So for example he and the very first time stamp out the very first row. 05:08.490 --> 05:09.560 You multiply here. 05:09.570 --> 05:11.320 Three hundred ninety eight. 05:11.370 --> 05:18.410 With uh this amount of shares of Amazon then we multiply one hundred thirty six point four nine for 05:18.410 --> 05:25.410 Boeing with the amount of outstanding shares and so on and we have to do this for each single timestamp 05:25.980 --> 05:31.670 and we can do this by taking uh the stock's data frame and apply the model method. 05:31.860 --> 05:39.870 Then we pass the uh the panda series shares with the number of shares for each company and uh then we 05:39.870 --> 05:48.090 also pass columns to the access parameter so actually our multiplication should be matched by the column 05:48.090 --> 05:55.800 labels here of our stock state frame and then we are saving the resulting data frame in the web market 05:55.800 --> 05:56.760 cap M cap. 05:56.760 --> 06:00.690 So let's do this and let's have a look here. 06:00.730 --> 06:06.480 So this is for each single timestamp the market capitalization for our six constituents. 06:06.490 --> 06:12.160 So from the end of 2013 to two thousand and eighteen. 06:12.940 --> 06:19.090 And it's also have a look at some meta information and we can also visualize that the market cap and 06:19.090 --> 06:25.190 the evolution of the market cap for each company year by using the plot method. 06:25.190 --> 06:27.210 So let's do this and let's have a look. 06:28.150 --> 06:34.700 So here we can see in the dark blue the Amazon market capitalization. 06:34.700 --> 06:41.650 So the value increased significantly from 2014 to today. 06:41.800 --> 06:49.270 And for comparison reasons that let's all supply to the evolution of stock prices over time and we can 06:49.270 --> 06:53.050 see it by far the highest stock prices has the Amazon stock. 06:53.740 --> 06:57.460 So in terms of stock prices Amazon has by far the highest stock prices. 06:57.460 --> 07:00.910 But in terms of market capitalization. 07:00.910 --> 07:03.670 So Microsoft and Amazon are quite equal. 07:04.270 --> 07:12.310 And the reason is simply that there are a lot of more shares outstanding for Microsoft than for Amazon. 07:12.340 --> 07:13.570 So let's have a look here. 07:16.700 --> 07:22.790 So these are the shares outstanding and for Microsoft we have a seven point six billion and two for 07:22.790 --> 07:23.540 Everson. 07:23.540 --> 07:31.290 Only four hundred ninety one million and in contrast to that the Amazon stock price is the much higher 07:31.290 --> 07:33.600 than the Microsoft stock price. 07:34.230 --> 07:41.700 And having here a combination by multiplying those two then we have quite similar uh market capitalizations 07:41.700 --> 07:43.500 here all right. 07:43.500 --> 07:47.540 This was the first video on creating a value weighted index. 07:47.700 --> 07:50.760 So there was quite a lot of work to do here. 07:50.790 --> 07:57.380 So to say kind of pre work and then the next video we will finally create our value weighted index. 07:57.390 --> 07:59.160 So hope to see you there by.