[MUSIC] Now, we're going to look at our second dataset, which is exactly the same as the first dataset, except we're adding 6 months of new data. So the post February 2020 period, and they were going to remove the first 6 months of data, so we'll still be using 500 data points or 500 trading days for our portfolio. What we'll be including in our 2-year window of data is the 6 months during which the COVID epidemic unfolded. So going from February of 2020 to August of 2020, and we're going to want to see how that affects the distribution of returns in our data. And how that affects our measurement of risk using value at risk and expected shortfall. Now, let's look at the data. Okay, so in our second workbook, you can see the data is laid out exactly the same except now the start day is on August 27 of 2018. We have the same indexes that we're using to construct our portfolio, the same allocations. And once again, we're going to start off with $10,000 portfolio and look at changes in the value, and so everything here in the data section is the same. But what we want to first do look at the new data, sort of the addition of that 6 months and see what that's kind of done to our risk. So we look over here, At our grass of the performance of the indexes that compose our portfolio, and we can see that actually a lot more is going on, especially once we look at the later part of the data. So in the S&P 500, we see a pretty massive sell off, and then a recovery, so sell off, and then the recovery and in the Hang Seng Index we also see a very big selloff with a smaller recovery. NIKKEI, big selloff, same big recovery and in the DAX also a big selloff and a recovery. So what we're seeing is some pretty extreme volatility once we include these additional 6 months of data or these new 6 months of data. And we also look at what this looks like when you converted into dollars for the NIKKEI and for the DAX. And essentially, it looks about the same there, there wasn't a whole lot of movement in the exchange rates between the yen and the dollar and between the euro and the dollar. And what I'd also say is generally these equity indexes and equities move around a lot more than exchange rates. Exchange rates are generally very stable, especially for reserve currencies, like the euro or the yen or the dollar. Okay, so what we see is that we probably are going to see a bit more risk in this data, and then when we go to look at our risk measures that will be reflected in those risk measures. So let's first look at portfolio loss, okay? Now, if we look at our lost data, just that initial graph, what we see is that the data is much more spread out. The losses, there more losses that are bigger, much bigger than before, and also gains that are much bigger than before. So we see that we have again here 700, that's a -700 loss, and we have several bosses one around 800, 700, 600 500, a lot around 400 and 300. And remember in our earlier distribution of the 2 years, not including the COVID period, none of this was present, we really had their largest loss was $236. So all of these are new and also these gains there, knew we didn't have any games that were so big as $700 or $400 with $300 in the day. And so, you can also see this when you look at the statistical measures for the portfolio and what you see is that the minimum loss right here at -762. So that was the largest gain in one day, $763, so that's almost an 8% gain in one day, and then our maximum loss in one day, it was very similar at $738. So we're looking at more or less 7, 8% gains and losses in a single day. Now, the mean over this time period because the market overall went down is slightly negative, what it means is that our mean loss was -$2, so we gained $2 on each day. So overall the market was going up during this period, and you can see that if we go back to look at the grass on the first data set. You can see generally, the markets were going up, especially the S&P 500 is much higher and where it started. And the other indexes are pretty close to where they started, but the S&P 500, which is our largest weighted index, is significantly higher than where we started. And so, that's accounting for most of that $2 a day gain in our portfolio on average. Okay, now, looking at other measures of statistical measures, so our kurtosis, now, that's a really big number there. Now, kurtosis is 16, means just really you have extreme kurtosis, so that means a lot of big gains and big losses. And you would expect relatively less than the normal distribution of returns that are around 0. So, we'll see if we see that when we compare our loss distribution from our data to a normal distribution. So we're going to do that right now, and that's what I've done here, the same as in the first day of this set. I've used the, right here, the functions to create histograms for our data set, and then for creating a histogram for the normal and also for the T distribution over here. So we have our normal T, and then this is our data right here, and these histograms, I'm going to use to compare our data to a normal and a T distribution. Okay, so we go back over here to our grounds, and we'll make these a little bit bigger. So the first one we're going to look at is the graph of our data, which is in blue, the loss data compared to the normal distribution, okay? And what we see is that the normal distribution does not include all of these gains here that we saw before, maybe a little bit there at again of 300. And definitely doesn't include these losses of 500, 600, 700, and 800. The other thing that we see is that it's a little bit unusual. The normal distribution has a much higher likelihood of a gain of 200 than our distribution, and a loss of 100 than our distribution. So that's the leptokurtosis. Now, that's a little bit offset by a higher probability in our distribution of not much happening, so the zeros. And these two are more or less the same, slightly higher in our distribution. So we have a sort of a low probability of these gains that are kind of in the middle, but big high probability of large gains and losses, which means we have a lot of risk there. because these losses out here, those are our tail losses. These are our tail losses, and these are what determine our value at risk. Now, I'm going to show you a couple of different ways of calculating value at risk this time. Now we'll go to our portfolio value at risk in just a minute. So first we'll look at the normal distribution versus our data. Now, we're going to look below that at a T distribution which includes that kurtosis. And so that's the T distribution, including the kurtosis, and you can see that it's a lot better matched to the data that are normal distribution. So you can see that these two are pretty close and these are very close here, so that's a good match, this is a good match. And so pretty much you have a much better match if you use a T distribution to describe your data in this case. Now, what I'm saying is that, what model would you use to model risk going forward after this period? So that's what we're trying to do here. We're saying all right, we've had this six months additional of extreme volatility. Now we have to decide what is the best distribution to use to model that, and to model the risk in that volatility, okay? Now, Going to the third tab here. And that's going to look at our value at risk. Now, let me break this a bit bigger. And one thing that I wanted to show you that I didn't show you in the first part of the project when we looked at the first data set is that, there are a couple of ways that you can figure out your value at risk. Now, what I've shown here, there's a way of calculating value at risk using the percentile.EXC function, so that's one way of doing it. But if you don't want to use percentile.EXC, then another way of calculating value at risk is to simply rank order all of your returns as is done in the columns here in the middle now. So we have 500 days of data, so that we have 500 daily returns. And what I've done is I've ranked those returns in terms of the largest loss down to the biggest game. So one thing that this does because it shows us obviously, what was our biggest loss here which was $738 lose. It also tells us when that loss happened. So that loss happened on the 389th day. So that means it happened in our data set which was 500 data points more towards the last quarter or in that last six months of the data. And you see that as a pattern for all the losses, pretty much that are big here, they all pretty much happened in the final six months. So that new data is where all these losses came from, and they're all high numbers, so they all happened in those six months. Now the other thing that you can do here is, we calculated earlier a 99% value at risk. Now I calculated that 99% value at risk here using percentile.EXC, okay, so that's one way of doing it. Now, another way of calculating a 99% value at risk is to say, okay, I have 500 observations and my value at risk will be for 99% confidence level. I'll need to look at the 1% worst losses and then see what is the, I guess best loss of those worst losses, if you can say that. So If I want to do that and I have 500 data points, 1% of 500 is just five. And so if I look over at my rank scenarios, basically if I go to scenario number five here, and that would be right here drawing a line there. So, my fifth worst scenario was scenario 4.1, and that was a loss of 361 or $362, okay? So that's just using rank scenarios to calculate a value at risk with a 99% confidence level. Now, comparing that 362 or so is basically exactly the same thing, so these two are almost the same thing. They're not exactly the same thing because, percentile.EXC is performing an interpolation between the values a little bit, and so it will be slightly different. But like I said you can eyeball What a 1% value at risk would be. And you can also do that for a 2% value excuse me, a 98% value at risk. So that would be 0.98 here and that's 236.92 and over here that would be observation 10 and also 236.92. So I just wanted to show you that the value at risk is really just coming from rank ordering those losses and then deciding what is that value at risk cut off. That lost level that you don't think you will exceed 99% of the time or 98% of the time. Okay, so other things that we notice here is that let's see, and looking at the graphs of the value at risk. So what you're shown just over to the right reduced that there. Okay, so here we see that our value at risk is much higher than before. Actually it goes right up off the graph and if we look at our evaluate risk versus are expected shortfall, we see that the expected shortfall is higher in the value at risk. But as you get to that last data point essentially become the same thing just because you only have one data point and that's your average value. And you're so your value at risk, it's closer to your expected shortfall in this case, okay? But you can see in both cases that they go up pretty dramatically and you can see that in the table here that I've shown. So our value at risk now for 99% is 361 and before when we looked at the earlier data set, let's see, it was 151.71. So that's looking at the other workbooks. So basically your value at risk has more than doubled just by including this new data. Now the question you have to ask yourself as a risk manager, is it likely that we're going to continue to have this volatility going forward? So is 361 a reasonable estimate for the value at risk for the next six months. Now it's obviously, it includes two years of data but really it's driven a lot by those last six months because that's where everything happened. And so really you might say, okay, do I think that this volatility will continue or not? And honestly, if you are thinking back in August of 2020, you may not know that you may you know, that was before you had a vaccine, that was before lots of things that happened. And so that probably was a reasonable estimate of volatility at that time, okay. Now, there are a couple other things I wanted to show you. Now we also have a tab which shows the normal value at risk. So we can compare our normal value at risk with our value at risk for our data and we do that here. And what we see, just looking at our grass here is that our value at risk for our data is actually lower the normal for confidence Levels up to about 97%. And then it goes a lot higher for confidence levels that are greater than 97%, greater than 97%. And we also see that with the expected shortfall, the normal expected shortfall and the expected shortfall for our data that were consistently higher than that. So in all cases, no matter what, the level of confidence, our data set shows a higher expected shortfall, which makes sense because really like I showed you. The if you look at 90%, so that would be the top 50 losses If you're taking 10% of the data and probably almost all those occurred in those last six months. And so it's going to be higher than the normal because that was just a very, very volatile period. Okay, now what we want to say is, okay, so if the normal density function or distribution is not a good way to model this and we want to model it with something that's going to approximate it better. Then we're going to look at a T distribution where we incorporate that really high keratosis and that's the next tab that we have here, okay? All right, so here we've done a a model where we're modeling a T distribution and we're looking at T. Value at risk versus our portfolio value at risk, okay? And so what we see is RT value at risk. It over here under T bar and our dollar value at risk and this is from our data right there. And so if we compare those for 99%, we see once again 362, roughly value at risk for our data. And then for our T distribution we get 293. So closer, not quite there, but closer to our data. And we can see that when we look at our grass of the value at risk versus the T Value at risk and make these bigger. Okay, what we can see is the same pattern that we saw before, where our data actually showed less value at risk the data up to about in this case 98% but above 98%. So So above 98% we see our data showing much higher value at risk. And we also see that our data has a higher expected shortfall, but we also see that the distance between the expected shortfalls is smaller once we use our t-value at risk. And t expected shortfall, we tend to get a closer match. So if we were going to model risk going forward, then probably in this case the t-distribution using the kurtosis would be a better choice than just using a normal distribution. Okay, now the last thing I want to show you and this is something that, it's just another statistical method for looking at data is called a weighted value at risk. Okay, now weighted value at risk is where you're saying, okay, I have two years of data but now a lot of that data is really old and so you're saying, is the data that I had for, two years ago of the same value as the data I have for the last six months or the last month or so. Now we always talk about saying, we need to use two years of data. And so in that way we get kind of a long term view, a complete picture of our risk. But the reality is a lot of times, the world that we had two years ago may not be anything like the world we have right now. And maybe it's not a good practice as a risk manager to use data that's settled in order, two years old to model today's risk. Now one way of including all the data, but putting more weight on more recent data and less weight on older data, is using exponentially weighted data. And so essentially what it does, it says that we're going to weight the most recent data most heavily. And then as we go further and further back in time, we're going to weight that old data less and we're going to use an exponential formula. So essentially the weight will decrease exponentially as we go to earlier and earlier dates. And the only constraint that we have is that we want all those weights, that we're going to apply to each observation to sum up to one. Yes, we want them to, the weight to be 100%. And now if you think about it, if you were looking at our original data here, where the weights for the original data with the weights where we're just having all the weights equally, what would the weight be equal to for an observation? And so really, we have 500 observations. So our weight would be equal to 1 divided by 500. Let me, which is equal to .005. Mary, 2 excuse me, .002. So basically if you multiply .002 times 500 you get 1, okay. And so that means on average the weighting will be 0.002. But the old scenarios, we're going to get a much smaller weight and you can see that, because the weighting on the old scenarios is more like .0002. So .00022, essentially, that's like 1/10 the weight, so 10%. And the weighting we're using in this example is .93, which means that as you step back in time each day, you reduce the weight or you multiply the weighting by .993 to reduce it as you step back in time. Now, if we look at the data and we get more towards the middle of the data here, say around 250. Now, it's not going to be exactly .002 because it is exponentially weighted. So actually at 250, the middle of the data, we're only at .0125. So we're not at .002. But if we look at the the last data, so that would be all those, what happened yesterday? If we were right there in August of 2020, we're going to weight that .0072. So it's going to get a much heavier weighting, like three times as high or more than that as the average weight that we had before. And so this is just a way of saying, okay, I'm going to keep all the data, but I'm going to make the more recent data more important. And the old data less important and it's just a statistical method to do that, okay. And just going back here. Okay, so what I show on the left is just our distribution. Again, this is a graph of the weighting and so you can see it goes from .007 here all the way down .00022. Okay, and then if we look at the, okay. So that's just another statistical method that you might consider using as a variation. Okay, now let's step back for a minute. So this is a project and I'm going to, you're going to be answering a few questions about what we've been discussing here on different methods. And what I'd like for you to do, is I'd like for you to take your own portfolio if you have a portfolio of stocks or for you to create your own portfolio if you like to. And try to use some of these methods to look at the risk in that portfolio. And I think what you'll find is that depending on your investment taste, that the risk may be much greater or much less than you think. And I'd like you to also think about combining different stocks in your portfolio. I mean here we combine different indexes. So an index is already pretty diversified, it's a whole bunch of stocks. But, you could try four different stocks, you could try four different digital currencies. You could try pretty much four different of anything. Or you can try even more assets than that, and try out some of these methods yourself. And then, since this will be on a user forum, try sharing that with other users on the forum and see what other users say about your analysis. Okay, so as I said, we'll have a quiz after this, that'll talk about some of the things that we've discussed. And then I encourage you to also use some of these methods on your own portfolio. So thank you.
