WEBVTT 00:00.830 --> 00:03.450 Welcome to the solution for coding exercise. 00:03.470 --> 00:05.180 Twelve and we import. 00:05.240 --> 00:13.550 The library's panels an empire and plot Philip and TB have to import the stock price data for six. 00:13.550 --> 00:17.460 The US stocks and we have to handle here a mighty index. 00:17.600 --> 00:24.920 So we have a mighty index in uh the columns actually and therefore we have to determine that the very 00:24.920 --> 00:32.660 first two rows and our CSP file are actually the column headers and then we also convert the column 00:32.660 --> 00:38.750 with the date information to a day time index and we save the resulting data frame and the rehab of 00:38.750 --> 00:40.070 stocks. 00:40.280 --> 00:41.770 So let's have a look here. 00:41.770 --> 00:49.400 Here we have my low close and adjusted close prices for six U.S. stocks so we have American Express 00:49.550 --> 00:56.330 Facebook Intel McDonald's Procter and Gamble and Wal-Mart and actually for the next questions we are 00:56.330 --> 01:04.040 only interested in the adjusted clothes for the period from the very last day of 215 and to the very 01:04.040 --> 01:11.200 last day off to 18 and we also ride stocks here and select that period and the adjusted close. 01:11.750 --> 01:12.740 So let's have a look. 01:12.770 --> 01:19.220 And the question is how many timestamps our days are in the data frame now and we can determine the 01:19.220 --> 01:21.800 service the info method. 01:21.800 --> 01:28.210 So here we have a 754 entries in our day time index and to inspect our data. 01:28.220 --> 01:32.100 Now we can create a line plot and uh of course we can do this. 01:32.100 --> 01:40.790 So the plot method so here we have the stock prices for the six stocks over time and that's actually 01:40.790 --> 01:47.690 pretty hard to compare them as they do not start at the same base value and therefore it might make 01:47.690 --> 01:54.560 sense to bring the six stocks to the same base value for example 100 and we can do this with the diff 01:54.560 --> 01:55.390 method. 01:55.580 --> 02:03.680 So we divide each and every row by the very first row and then we multiply with 100 and by doing so 02:03.680 --> 02:12.030 we create the normalized stock prices and then we plot the normalized stock prices so here we are starting 02:12.030 --> 02:19.500 at a base value of 100 but that's still pretty hard to compare the performance of the six stocks and 02:19.740 --> 02:26.780 therefore it might be better to calculate the risk and return for the six stocks over that period. 02:26.800 --> 02:34.650 And we decide to calculate the monthly returns and therefore first of all we have to down sample uh 02:34.680 --> 02:41.610 the price data to monthly frequency and we can do the service for the free sample method and select 02:41.610 --> 02:47.490 the very last trading price of each month and then we store the new data frame and the rebels stocks 02:47.580 --> 02:49.520 monthly. 02:49.830 --> 02:56.540 So here we have a monthly price data and then as a next step we can calculate monthly returns simple 02:56.540 --> 03:02.480 returns and we say fairly resounding data frame and the rabbi returns and we can actually calculate 03:02.480 --> 03:09.560 simple returns with the percentage change method and it's pretty common to also change the drop in a 03:09.560 --> 03:11.600 method to drop. 03:11.630 --> 03:15.080 The very first row where we do not have any returns. 03:15.100 --> 03:18.850 So we have a values there and let's have a look here. 03:18.860 --> 03:27.930 So these are the simple monthly returns for the six stocks and we can also calculate the mean return 03:27.930 --> 03:33.010 and the standard deviation of monthly returns and be safe. 03:33.030 --> 03:39.420 They reside in the a summary and we can actually calculate mean and standard deviation by passing them 03:39.450 --> 03:42.010 in a list to the ECT method. 03:42.390 --> 03:47.760 And finally for presentation purposes we also transpose here the summary data frame. 03:47.760 --> 03:49.420 So let's have a look. 03:49.600 --> 03:58.740 Here we FSI the index our six stock takers and the columns mean and standard deviation and these actually 03:58.740 --> 04:06.960 are based on monthly returns and two might make more sense or it's more intuitive to convert these two 04:06.990 --> 04:14.920 annualized the mean and standard deviation and we can do this for the mean by multiplying the mean with 04:15.000 --> 04:21.210 twelve and to calculate the annualized risk or the annualized standard deviation we have to multiply 04:21.210 --> 04:28.500 the monthly standard deviation with the square root of twelve because the standard deviation is the 04:28.500 --> 04:30.570 square root of the variance. 04:30.570 --> 04:39.070 So if we had here the risk and variance units then we could simply multiply the risk with the twelve. 04:39.090 --> 04:46.040 But having had the standard deviation we have to multiply with the square root of twelve now let's have 04:46.050 --> 04:47.240 a final look here. 04:47.280 --> 04:53.880 So these are annualized the mean and standard deviation or return and risk and obviously we have the 04:53.880 --> 05:00.810 highest the mean return for Wal-Mart and the highest the standard deviation are the highest the risk 05:00.810 --> 05:03.060 for American Express. 05:03.060 --> 05:10.350 And finally we can also plot the risk and return in total risk return framework and the U.S. the plot 05:10.350 --> 05:16.410 method and we determine that we want to phase scatter plot and on the x axis that we have for the standard 05:16.410 --> 05:23.770 deviation and on the y axis the mean or the return and by doing so we create actually a six points. 05:23.790 --> 05:27.150 And then we also add the labels of these six points. 05:27.180 --> 05:33.790 So here the stock take us and let's have a look. 05:33.810 --> 05:36.950 So this is a total risk return framework. 05:37.080 --> 05:41.490 And actually it is desirable to have low risk and high return. 05:41.880 --> 05:49.560 So being here on the upper left corner and in contrast that is not desirable to have high risk and low 05:49.560 --> 05:50.590 return. 05:50.610 --> 05:58.530 So being you're on the lower right corner and to having this framework here we can say that probably 05:58.530 --> 06:05.480 the best performing stock is the McDonald's stock and the worst performing stock is the Facebook stock. 06:06.090 --> 06:08.270 All right let's go to the last two questions. 06:08.280 --> 06:13.300 And here we have to prepare the correlation matrix of monthly returns. 06:13.590 --> 06:19.580 And the question is which stock path shows that the highest the positive correlation of monthly returns. 06:19.580 --> 06:24.800 And we can do this here with the call method on the returns data frame. 06:25.920 --> 06:32.580 And here we have the correlations and uh we have actually the highest correlation between Wal-Mart and 06:32.640 --> 06:33.800 McDonald's. 06:34.020 --> 06:40.150 And we can also visualize the correlation matrix and we can create a seaborne heat map. 06:40.230 --> 06:43.900 So first of all we import seaborne and then we pass here. 06:43.920 --> 06:52.740 The correlation matrix to the heat map method and as color map we have red yellow and green. 06:52.800 --> 06:54.030 So let's have a look here 06:57.710 --> 07:04.440 so here we have a high positive correlations with the dark green color and it's no surprise that he 07:04.440 --> 07:06.620 and the diagonal we have perfect correlation. 07:06.620 --> 07:11.830 So each and every stock is perfectly correlated with itself. 07:12.080 --> 07:18.220 And here we can see the highest correlation between past that the Wal-Mart and McDonald's. 07:18.260 --> 07:21.450 And also here are right. 07:21.460 --> 07:23.200 That was coding exercise twelve. 07:23.200 --> 07:28.240 I hope you had fun and they're looking forward to seeing you also in the next exercise by.