1 00:00:00,000 --> 00:00:07,800 Allen Cring Productions in association with Emergent Light Studio presents... 2 00:00:10,360 --> 00:00:15,920 The Illinois State Collegiate Compendium, academic lectures in Business and Economics. 3 00:00:17,300 --> 00:00:23,420 This is Business Finance FIL-194, spring semester 2024. 4 00:00:23,420 --> 00:00:32,420 Today, risk and return. 5 00:00:32,420 --> 00:00:36,320 Now, the complicated part of this is not that complicated, 6 00:00:36,320 --> 00:00:40,220 but it does call you to do some things, 7 00:00:40,220 --> 00:00:43,220 and I would certainly expect you to do this in Excel. 8 00:00:43,220 --> 00:00:46,820 I don't even care to do it myself by hand 9 00:00:46,820 --> 00:00:49,420 because it can be tedious sometimes. 10 00:00:49,420 --> 00:00:55,820 But it is, it's not terribly complicated in Excel, 11 00:00:55,820 --> 00:00:58,220 but there is a caution that I have to give you 12 00:00:58,220 --> 00:01:00,120 when I do it in Excel. 13 00:01:00,120 --> 00:01:03,320 But before we do that, let me do a quick look at the numbers 14 00:01:03,320 --> 00:01:03,820 here. 15 00:01:12,520 --> 00:01:13,020 Yahoo. 16 00:01:13,020 --> 00:01:20,120 And as you can see, that is about as lackluster 17 00:01:20,120 --> 00:01:22,720 as a market can get. 18 00:01:22,720 --> 00:01:26,520 It's just like sitting there looking stupid. 19 00:01:26,520 --> 00:01:29,220 The Dow is flat right now. 20 00:01:29,220 --> 00:01:33,220 And then the S&P 500 is pretty much the same. 21 00:01:33,220 --> 00:01:36,520 And then the NASDAQ, a little bit of bias to the downside, 22 00:01:36,520 --> 00:01:37,920 tiny, tiny bit. 23 00:01:37,920 --> 00:01:40,720 But they all came, started out, 24 00:01:40,720 --> 00:01:45,320 kind of dropped real fast right on the opening bell. 25 00:01:45,320 --> 00:01:47,320 And well, this one didn't. 26 00:01:47,320 --> 00:01:50,320 But then it's been struggling to come back up, 27 00:01:50,320 --> 00:01:53,720 but there is a lot of volatility right now. 28 00:01:53,720 --> 00:01:59,020 The volatility is quite noticeable, at least visually. 29 00:01:59,020 --> 00:02:02,720 Now, however, very quickly, let me look at the VIX 30 00:02:02,720 --> 00:02:07,320 to see what the vol is on the VIX real quick. 31 00:02:07,320 --> 00:02:11,320 That's not much. 32 00:02:11,320 --> 00:02:17,120 Yeah, see the volatility early this morning really spiked. 33 00:02:17,120 --> 00:02:20,920 And it's dropped off really a lot 34 00:02:20,920 --> 00:02:22,820 in just the last couple of minutes. 35 00:02:22,820 --> 00:02:24,720 And I don't know what that's all about. 36 00:02:24,720 --> 00:02:28,420 So we'll have to see how that goes from here. 37 00:02:28,420 --> 00:02:31,720 But anyway, let me get back to the main show here. 38 00:02:31,720 --> 00:02:37,120 And crude oil still in its band now. 39 00:02:37,120 --> 00:02:43,320 About 81, I'd say, up to 88, is where I'm seeing it right now. 40 00:02:43,320 --> 00:02:44,820 And it's staying in that band. 41 00:02:44,820 --> 00:02:49,120 And as I said, that's going to lead to higher gas prices. 42 00:02:49,120 --> 00:02:54,420 And you're seeing them at the gas pump right now, 379 locally, 43 00:02:54,420 --> 00:02:57,520 379, 369. 44 00:02:57,520 --> 00:03:02,120 But where it's going to go, you notice that it has been tailing 45 00:03:02,120 --> 00:03:04,320 off through the day. 46 00:03:04,320 --> 00:03:07,520 But that drop isn't all that noticeable. 47 00:03:07,520 --> 00:03:08,520 It did fall well. 48 00:03:08,520 --> 00:03:09,420 I shouldn't say that. 49 00:03:09,420 --> 00:03:11,920 Because since last night, now remember, 50 00:03:11,920 --> 00:03:16,420 oil commodities markets don't trade like stock markets do. 51 00:03:16,420 --> 00:03:20,520 They have different time frames. 52 00:03:20,520 --> 00:03:25,720 And it looks like it was up there rather noticeably 53 00:03:25,720 --> 00:03:26,720 last night. 54 00:03:26,720 --> 00:03:30,020 And it's been tailing downward ever since. 55 00:03:30,020 --> 00:03:33,420 And that is a lot to do with the, 56 00:03:33,420 --> 00:03:35,620 we know there's a lot of supply. 57 00:03:35,620 --> 00:03:44,020 The supply chain is full at the sources, on the high seas, 58 00:03:44,020 --> 00:03:45,820 at the refineries. 59 00:03:45,820 --> 00:03:49,120 So the cause of that higher price that's 60 00:03:49,120 --> 00:03:51,720 been there for the last week or so 61 00:03:51,720 --> 00:03:54,920 is mostly expectations of something 62 00:03:54,920 --> 00:03:59,620 that the market thought was going to happen. 63 00:03:59,620 --> 00:04:03,120 And now that expectation seems to be draining out 64 00:04:03,120 --> 00:04:04,820 a little bit. 65 00:04:04,820 --> 00:04:08,820 But again, you have to always remember that in our markets, 66 00:04:08,820 --> 00:04:11,520 expectations are everything. 67 00:04:11,520 --> 00:04:13,920 Gold, it's just all over the place. 68 00:04:13,920 --> 00:04:17,620 It's just really volatile right now. 69 00:04:17,620 --> 00:04:18,720 It's spiked up. 70 00:04:18,720 --> 00:04:22,020 But it still seems to, right now, it doesn't really 71 00:04:22,020 --> 00:04:25,820 want to find out much about $2,200 an ounce. 72 00:04:25,820 --> 00:04:28,420 It's keeping down a little bit more. 73 00:04:28,420 --> 00:04:31,220 Silver, quieter, of course. 74 00:04:31,220 --> 00:04:36,320 Now the 10-year bond, we do have at least a modest, 75 00:04:36,320 --> 00:04:41,620 a tiny 4 tenths of a basis point easing off. 76 00:04:41,620 --> 00:04:43,620 But it's really, for the most part, 77 00:04:43,620 --> 00:04:50,220 that's just really quiet, 0.09%. 78 00:04:50,220 --> 00:04:53,720 So the bond market is just sort of sitting there pretty much 79 00:04:53,720 --> 00:04:57,020 flat, like the equities are. 80 00:04:57,020 --> 00:05:00,620 So the market overall is just sitting back 81 00:05:00,620 --> 00:05:04,620 and waiting to see what's going to happen next. 82 00:05:04,620 --> 00:05:09,120 Running over here just very quickly, the Nikkei, 83 00:05:09,120 --> 00:05:10,420 it started down. 84 00:05:10,420 --> 00:05:13,720 And then it climbed up in the last half of the trading 85 00:05:13,720 --> 00:05:15,920 last night over in Tokyo. 86 00:05:15,920 --> 00:05:20,420 But it did manage to get kind of a respectable 2 thirds 87 00:05:20,420 --> 00:05:22,920 of a percent out of its day. 88 00:05:22,920 --> 00:05:26,420 And when we came over to London, they started out down 89 00:05:26,420 --> 00:05:27,720 and then groveled back. 90 00:05:27,720 --> 00:05:29,520 They're still trading. 91 00:05:29,520 --> 00:05:33,220 And now they're back in positive territory too. 92 00:05:33,220 --> 00:05:36,420 And if we look back over here at the equity side of it, 93 00:05:36,420 --> 00:05:37,420 well, there you go. 94 00:05:37,420 --> 00:05:39,020 We're just sitting there looking, 95 00:05:39,020 --> 00:05:41,320 not really knowing what to do next. 96 00:05:41,320 --> 00:05:44,620 It's a really quiet day. 97 00:05:44,620 --> 00:05:47,820 I don't see anything exciting coming along. 98 00:05:50,520 --> 00:05:53,320 Yeah, but it's just going to be one of those days 99 00:05:53,320 --> 00:05:56,320 where there's nothing going on. 100 00:05:56,320 --> 00:05:58,420 Very quickly, let me show you. 101 00:05:58,420 --> 00:06:01,620 And I'm just going to, well, that was brilliant. 102 00:06:01,620 --> 00:06:05,020 I'm just going to pull up a stock out of thin air 103 00:06:05,020 --> 00:06:07,120 for a little bit here. 104 00:06:07,120 --> 00:06:07,820 I don't know. 105 00:06:11,820 --> 00:06:16,620 General, let me look at General Mills, something quiet. 106 00:06:16,620 --> 00:06:20,720 And I have a specific purpose here. 107 00:06:20,720 --> 00:06:25,820 General Mills, General Motors, I'd rather look at something 108 00:06:25,820 --> 00:06:28,020 a little bit calmer. 109 00:06:28,020 --> 00:06:30,820 What in the heck? 110 00:06:30,820 --> 00:06:32,120 Come on. 111 00:06:32,120 --> 00:06:36,020 Why am I having difficulties here? 112 00:06:36,020 --> 00:06:38,620 Oh, I put it in the wrong place. 113 00:06:38,620 --> 00:06:41,020 General Mills. 114 00:06:41,020 --> 00:06:43,020 GM. 115 00:06:43,020 --> 00:06:43,920 General Mills. 116 00:06:48,020 --> 00:06:51,520 I didn't think that was as simple, but I guess there it is. 117 00:06:51,520 --> 00:06:52,820 OK. 118 00:06:52,820 --> 00:06:57,120 Really came off of, today, it really came out good. 119 00:06:57,120 --> 00:06:59,420 Strong, strong opening. 120 00:06:59,420 --> 00:07:02,220 And it's been sliding off since then. 121 00:07:02,220 --> 00:07:06,020 There's some profit taking going on since the opening bell. 122 00:07:06,020 --> 00:07:11,420 But man, it's really been a decent day on there. 123 00:07:11,420 --> 00:07:13,820 And I've gone through the numbers before, 124 00:07:13,820 --> 00:07:16,420 and I'm hopeful that you know how to look at those numbers 125 00:07:16,420 --> 00:07:17,120 now. 126 00:07:17,120 --> 00:07:20,920 The one I want to focus on right here, right now, 127 00:07:20,920 --> 00:07:23,720 is this guy right here, Beta. 128 00:07:23,720 --> 00:07:26,020 And as I've said before, in finance, 129 00:07:26,020 --> 00:07:30,520 this is our go-to measure of risk. 130 00:07:30,520 --> 00:07:33,320 Now, we do have other measures of risk. 131 00:07:33,320 --> 00:07:37,220 The standard deviation of returns is a measure. 132 00:07:37,220 --> 00:07:38,620 And everything is on returns. 133 00:07:38,620 --> 00:07:40,420 These are price metrics. 134 00:07:40,420 --> 00:07:43,020 These are returns metrics. 135 00:07:43,020 --> 00:07:47,820 So you're taking this Beta is a measure 136 00:07:47,820 --> 00:07:50,220 of the volatility of the returns. 137 00:07:50,220 --> 00:07:54,920 Now, we can talk about, pretend it's the volatility of the price, 138 00:07:54,920 --> 00:07:57,520 because the price is related to the returns. 139 00:07:57,520 --> 00:08:00,920 But technically speaking, where we have to be now in the course 140 00:08:00,920 --> 00:08:04,120 is volatility of returns. 141 00:08:04,120 --> 00:08:07,020 And this is our go-to. 142 00:08:07,020 --> 00:08:10,320 It is not just the standard deviation. 143 00:08:10,320 --> 00:08:12,320 And I'll get into that in just a few minutes here, 144 00:08:12,320 --> 00:08:13,720 what I mean by that. 145 00:08:13,720 --> 00:08:16,120 But for the time being, just understand 146 00:08:16,120 --> 00:08:17,320 that this is different. 147 00:08:17,320 --> 00:08:19,120 Now, we've got to understand that. 148 00:08:19,120 --> 00:08:20,120 This is different. 149 00:08:20,120 --> 00:08:21,520 Now, we do have some other ones. 150 00:08:21,520 --> 00:08:24,920 The standard deviation is a measure of risk. 151 00:08:28,020 --> 00:08:31,920 The Beta is a measure of risk, too. 152 00:08:31,920 --> 00:08:36,020 And there's another one here, too, the Sharpe ratio. 153 00:08:36,020 --> 00:08:39,920 And there are different books have different letters for that. 154 00:08:39,920 --> 00:08:45,520 I'll just write it as SR, the Sharpe ratio. 155 00:08:45,520 --> 00:08:46,620 It is a measure. 156 00:08:46,620 --> 00:08:51,520 And it's a very popular measure in mutual funds. 157 00:08:51,520 --> 00:08:54,920 I mean, I'm not sure where that came from. 158 00:08:54,920 --> 00:08:58,620 It's mostly historical, because mutual funds have a Beta. 159 00:08:58,620 --> 00:09:00,120 And I look at the Beta. 160 00:09:00,120 --> 00:09:03,720 But I swear, whenever you're looking at mutual funds, 161 00:09:03,720 --> 00:09:07,720 as you notice with GM here, Sharpe ratio is nowhere. 162 00:09:07,720 --> 00:09:09,920 But if you're looking at a mutual fund, 163 00:09:09,920 --> 00:09:12,320 all the Sharpe ratio is going to show. 164 00:09:12,320 --> 00:09:17,720 And I hope you'll see why it's not a really great measure. 165 00:09:17,720 --> 00:09:23,320 But it is popular in that niche market of mutual funds. 166 00:09:23,320 --> 00:09:24,120 Whatever. 167 00:09:24,120 --> 00:09:26,820 Now, there's one other one that I would bring up here, too, 168 00:09:26,820 --> 00:09:29,320 the coefficient of variation, the CV. 169 00:09:32,580 --> 00:09:38,020 Now, this is where I never can remember. 170 00:09:38,020 --> 00:09:39,980 Did you take the statistics course yet, 171 00:09:39,980 --> 00:09:42,720 the management course? 172 00:09:42,720 --> 00:09:43,520 I can't remember. 173 00:09:43,520 --> 00:09:45,120 I think it's 150 or something like that. 174 00:09:45,120 --> 00:09:47,020 Have you had that course yet? 175 00:09:47,020 --> 00:09:48,420 OK. 176 00:09:48,420 --> 00:09:50,820 It's OK, because I'm going to teach it a lot better 177 00:09:50,820 --> 00:09:52,220 than they would anyway, because I'm 178 00:09:52,220 --> 00:09:57,920 going to focus on what these things mean to us in our work. 179 00:09:57,920 --> 00:10:00,180 And I do use Excel like a boss. 180 00:10:00,180 --> 00:10:02,840 I do not use the formulas, because the formulas 181 00:10:02,840 --> 00:10:05,120 can get to be a lot like work. 182 00:10:05,120 --> 00:10:07,580 And it's not that they're hard, complex. 183 00:10:07,580 --> 00:10:10,680 But man, you just have to keep calculating over, 184 00:10:10,680 --> 00:10:15,280 adding and taking differences, then squaring them, 185 00:10:15,280 --> 00:10:17,020 then weighting it by the probability, 186 00:10:17,020 --> 00:10:18,880 and then going on to the next one, 187 00:10:18,880 --> 00:10:20,880 and then adding all of those up, then taking 188 00:10:20,880 --> 00:10:22,240 the square root of it. 189 00:10:22,240 --> 00:10:27,680 That's way too much like accounting work to me. 190 00:10:27,680 --> 00:10:30,740 So we take Excel very seriously. 191 00:10:30,740 --> 00:10:35,180 And all these we can calculate with Excel. 192 00:10:35,180 --> 00:10:38,780 One caution, and then I'll get off this subject, 193 00:10:38,780 --> 00:10:47,320 because it's so important that it's not something 194 00:10:47,320 --> 00:10:51,480 that I want to talk a whole lot about. 195 00:10:51,480 --> 00:10:54,840 A lot of times, we will calculate 196 00:10:54,840 --> 00:10:58,540 these with historical data. 197 00:10:58,540 --> 00:11:01,420 And that's not what we should be doing. 198 00:11:01,420 --> 00:11:05,980 We should be calculating it with future data, 199 00:11:05,980 --> 00:11:08,880 or future expected data. 200 00:11:08,880 --> 00:11:13,920 And that gets into the kind of obvious question, 201 00:11:13,920 --> 00:11:15,580 how the hell do you do that? 202 00:11:15,580 --> 00:11:19,380 If we haven't had the future yet, how do you get it? 203 00:11:19,380 --> 00:11:22,380 The typical way that we do that, we 204 00:11:22,380 --> 00:11:31,380 do an adjustment from history to the future. 205 00:11:31,380 --> 00:11:33,380 How far do I want to take this? 206 00:11:33,380 --> 00:11:33,880 OK. 207 00:11:37,120 --> 00:11:42,120 We do it actually the way we taught artificial intelligence 208 00:11:42,120 --> 00:11:45,620 systems to do a lot of things that they 209 00:11:45,620 --> 00:11:49,320 do in modeling and data analysis. 210 00:11:49,320 --> 00:11:56,380 What we'll do is we will take, let's say, a year of data. 211 00:11:56,380 --> 00:12:01,140 And we will get the expected, we'll get, say, 212 00:12:01,140 --> 00:12:05,880 the average of that. 213 00:12:05,880 --> 00:12:11,780 And then what we'll do is we'll go up, say, two months. 214 00:12:11,780 --> 00:12:20,880 And we'll take data that, let me do it this way. 215 00:12:20,880 --> 00:12:25,380 We've got a vector of data, a historical vector of data. 216 00:12:25,380 --> 00:12:26,820 We'll take this, OK? 217 00:12:29,680 --> 00:12:32,840 And we'll get the average of this data. 218 00:12:32,840 --> 00:12:34,240 This is just one of the metrics. 219 00:12:34,240 --> 00:12:37,920 Let's say the average of the returns. 220 00:12:37,920 --> 00:12:41,080 And we put an R bar. 221 00:12:41,080 --> 00:12:42,140 That's for an average. 222 00:12:47,240 --> 00:12:54,040 And then we will say, OK, let's take this last data. 223 00:12:54,040 --> 00:12:58,320 This is the simulated history. 224 00:12:58,320 --> 00:13:05,880 And then we will pretend that this was the future data, 225 00:13:05,880 --> 00:13:07,780 R capital F, I better put, because it 226 00:13:07,780 --> 00:13:13,620 looks like a risk-free rate. 227 00:13:13,620 --> 00:13:25,180 And then we'll look at how much the historical data deviated 228 00:13:25,180 --> 00:13:27,640 from this quote unquote future data. 229 00:13:27,640 --> 00:13:29,080 It was all historical, of course, 230 00:13:29,080 --> 00:13:33,320 but we're going to pretend that this was future data. 231 00:13:33,320 --> 00:13:38,380 And we'll see how much different this future 232 00:13:38,380 --> 00:13:40,680 was than what we thought it would 233 00:13:40,680 --> 00:13:44,020 be from the historical data. 234 00:13:44,020 --> 00:13:47,420 And then that will be our adjustment 235 00:13:47,420 --> 00:13:50,380 for all of the historical data. 236 00:13:50,380 --> 00:13:52,220 Ouch. 237 00:13:52,220 --> 00:13:57,940 That difference tells us how much different the history 238 00:13:57,940 --> 00:14:02,080 is from what happens next. 239 00:14:02,080 --> 00:14:04,660 And that's a simple way of doing it. 240 00:14:04,660 --> 00:14:07,660 We have much more complex ways, but honestly, they all 241 00:14:07,660 --> 00:14:10,300 kind of boil down to this. 242 00:14:10,300 --> 00:14:14,220 And that way, we can get an estimate 243 00:14:14,220 --> 00:14:17,020 of how much another data set. 244 00:14:17,020 --> 00:14:23,900 So in other words, we would now take a whole set of data 245 00:14:23,900 --> 00:14:26,700 and get its historical. 246 00:14:26,700 --> 00:14:30,500 And we say that up here, it would be R bar 247 00:14:30,500 --> 00:14:33,940 plus this adjustment figure. 248 00:14:33,940 --> 00:14:37,700 That's how we would get a future expected value. 249 00:14:40,300 --> 00:14:42,420 This is done all the time. 250 00:14:42,420 --> 00:14:46,860 So when I talk about these metrics over there, 251 00:14:46,860 --> 00:14:48,220 we are doing historical. 252 00:14:48,220 --> 00:14:50,340 We're not going to do that adjustment. 253 00:14:50,340 --> 00:14:53,620 But that's how you would do an adjustment 254 00:14:53,620 --> 00:14:57,540 so that you're looking at the expected future standard 255 00:14:57,540 --> 00:14:58,540 deviation. 256 00:14:58,540 --> 00:15:01,460 The expected future beta. 257 00:15:01,460 --> 00:15:05,740 It would be the historical with that adjustment factor 258 00:15:05,740 --> 00:15:07,780 added in or subtracted from. 259 00:15:12,020 --> 00:15:13,060 OK. 260 00:15:13,060 --> 00:15:17,820 Now let me get down to some brass tacks here on this. 261 00:15:17,820 --> 00:15:20,660 I'm going to pull up. 262 00:15:20,660 --> 00:15:25,540 And I'm actually just going to pull up some live data for us 263 00:15:25,540 --> 00:15:32,020 to use to actually do these calculations here. 264 00:15:32,020 --> 00:15:42,900 But before I do that, a little bit of notation here 265 00:15:42,900 --> 00:15:44,420 and terminology. 266 00:15:50,020 --> 00:15:54,460 The book is using a term that has come up 267 00:15:54,460 --> 00:15:57,380 as the way to describe it. 268 00:15:57,380 --> 00:16:00,260 But it hasn't been around for all that many years. 269 00:16:00,260 --> 00:16:02,660 But it seems to be pretty popular. 270 00:16:02,660 --> 00:16:09,100 Distinguishing between standalone risk and portfolio 271 00:16:09,100 --> 00:16:09,600 risk. 272 00:16:12,660 --> 00:16:14,300 And there's even another one too. 273 00:16:14,300 --> 00:16:22,580 But unfortunately, this edition of the book takes that out. 274 00:16:32,460 --> 00:16:34,660 But before we talk too much about these, 275 00:16:34,660 --> 00:16:39,500 we have to talk about standalone with an E. 276 00:16:39,500 --> 00:16:42,100 We have to talk about risk. 277 00:16:42,100 --> 00:16:47,540 Risk is the variability of possible outcomes. 278 00:16:47,540 --> 00:16:53,540 If there is only one possible outcome, there's no risk. 279 00:16:53,540 --> 00:16:57,460 If there is more than one possible outcome, 280 00:16:57,460 --> 00:17:00,540 then there is risk. 281 00:17:00,540 --> 00:17:03,540 Now it's not just how many outcomes. 282 00:17:03,540 --> 00:17:09,540 It's how much those outcomes can be different from one another. 283 00:17:09,540 --> 00:17:10,940 That's the next step. 284 00:17:10,940 --> 00:17:21,860 So in other words, there is the how many outcomes. 285 00:17:28,860 --> 00:17:30,220 That's the big one first. 286 00:17:35,620 --> 00:17:37,500 Oh, that didn't work. 287 00:17:37,500 --> 00:17:48,140 The next one would be how different 288 00:17:48,140 --> 00:17:49,900 are the possible outcomes. 289 00:17:49,900 --> 00:18:03,660 That's the next one. 290 00:18:03,660 --> 00:18:20,980 Then the next one is how, for lack of a better word, 291 00:18:20,980 --> 00:18:44,540 how clustered are the possible outcomes. 292 00:18:44,540 --> 00:18:51,420 So in other words, you have 100 different possible outcomes. 293 00:18:51,420 --> 00:18:52,820 Well, that's noticeable right there. 294 00:18:52,820 --> 00:18:53,540 That's important. 295 00:18:53,540 --> 00:18:58,700 But what if all of them are really close to each other, 296 00:18:58,700 --> 00:19:00,980 the outcomes are? 297 00:19:00,980 --> 00:19:04,620 Then that's not a very risky situation compared to one 298 00:19:04,620 --> 00:19:09,020 where the outcomes are quite different. 299 00:19:09,020 --> 00:19:12,940 And then one last one that I will somewhat explore, 300 00:19:12,940 --> 00:19:16,500 and I will show you something. 301 00:19:16,500 --> 00:19:18,900 You haven't had a management course, 302 00:19:18,900 --> 00:19:20,660 so I don't want to get into it too much. 303 00:19:20,660 --> 00:19:25,140 But I will show you in Excel how you can calculate it. 304 00:19:25,140 --> 00:19:26,460 There are a couple of other ones. 305 00:19:26,460 --> 00:19:28,540 One that's kind of important to me 306 00:19:28,540 --> 00:19:37,540 is how biased is the clustering. 307 00:19:37,540 --> 00:19:44,140 And by that I mean you might have 100 possible outcomes. 308 00:19:44,140 --> 00:19:46,420 95 of them are very close. 309 00:19:46,420 --> 00:19:51,380 But then you have a tail that is on one side, 310 00:19:51,380 --> 00:19:55,820 a tail of potential outcomes. 311 00:19:55,820 --> 00:19:59,940 That would be important too, because there 312 00:19:59,940 --> 00:20:02,460 is this typical assumption we make. 313 00:20:02,460 --> 00:20:05,020 And that is that the outcome is not 314 00:20:05,020 --> 00:20:07,700 as good as the outcome of the other. 315 00:20:07,700 --> 00:20:10,340 There is this typical assumption we make. 316 00:20:10,340 --> 00:20:12,700 And it's actually not as good. 317 00:20:12,700 --> 00:20:17,140 It's used all of the time that outcomes 318 00:20:17,140 --> 00:20:21,980 are distributed normally. 319 00:20:21,980 --> 00:20:27,740 They are the bell curve, the famous bell curve. 320 00:20:27,740 --> 00:20:32,620 Now there is a law of mathematics 321 00:20:32,620 --> 00:20:40,060 that says that if you make a bell curve with a single data, 322 00:20:40,060 --> 00:20:45,460 it will begin to behave like this bell curve, 323 00:20:45,460 --> 00:20:49,420 what we call the normal curve. 324 00:20:49,420 --> 00:20:51,660 That's great. 325 00:20:51,660 --> 00:20:54,580 However, the reality is that there 326 00:20:54,580 --> 00:21:00,180 is some data that just you cannot make that assumption. 327 00:21:00,180 --> 00:21:02,740 Is we always, well, it's a bell curve. 328 00:21:02,740 --> 00:21:04,500 Your average is 75%. 329 00:21:04,500 --> 00:21:06,260 And it's a bell curve and all that. 330 00:21:06,260 --> 00:21:09,660 Because there are all these mathematical simplifications 331 00:21:09,660 --> 00:21:13,100 we can make, very cool things you can do with a normal curve. 332 00:21:13,100 --> 00:21:14,980 So we hope that it is normal. 333 00:21:14,980 --> 00:21:17,020 And even if it's not, we pretend it's normal. 334 00:21:17,020 --> 00:21:21,220 But I can tell you right now, there's a problem with it. 335 00:21:21,220 --> 00:21:25,860 Well, in education, there's a great problem with it. 336 00:21:25,860 --> 00:21:34,740 You see, if the average of an exam is 75%, 337 00:21:34,740 --> 00:21:36,860 that's our ideal, perfect 75%. 338 00:21:36,860 --> 00:21:38,100 Of course, you screwed that up. 339 00:21:38,100 --> 00:21:39,740 You got an 80%. 340 00:21:39,740 --> 00:21:46,900 But the problem is that, OK, the highest score possible 341 00:21:46,900 --> 00:21:49,500 would be 100%. 342 00:21:49,500 --> 00:21:52,900 The lowest score possible would be a 0. 343 00:21:52,900 --> 00:21:56,820 So do you see that there would be a tail down here 344 00:21:56,820 --> 00:21:59,220 of scores that would not be matched 345 00:21:59,220 --> 00:22:02,820 by a tail of the same distance from the average 346 00:22:02,820 --> 00:22:04,940 on the outside? 347 00:22:04,940 --> 00:22:08,660 In other words, I could have an outcome on the outside no more 348 00:22:08,660 --> 00:22:12,940 than 25 points above the average. 349 00:22:12,940 --> 00:22:20,340 But I could have a test score that was 75% 75 points 350 00:22:20,340 --> 00:22:23,060 below the average. 351 00:22:23,060 --> 00:22:26,700 So we could get a 0 or a 5 or something like that. 352 00:22:26,700 --> 00:22:31,820 So in other words, the assumption of a bell curve 353 00:22:31,820 --> 00:22:34,460 is invalid. 354 00:22:34,460 --> 00:22:39,660 The same, unfortunately, is true of stock returns. 355 00:22:42,860 --> 00:22:46,540 And it's actually, OK. 356 00:22:46,540 --> 00:22:50,660 In other words, stock returns, we assume 357 00:22:50,660 --> 00:22:54,020 that there is an expected return to a stock. 358 00:22:54,020 --> 00:22:57,620 And we would assume that it could possibly deviate 359 00:22:57,620 --> 00:22:59,060 from that on either side. 360 00:23:02,660 --> 00:23:05,300 So it would be a normal distribution. 361 00:23:05,300 --> 00:23:08,500 And we use all of these cool little metric tricks 362 00:23:08,500 --> 00:23:12,220 for finding the mean, the standard deviation, 363 00:23:12,220 --> 00:23:13,700 all that kind of cool stuff. 364 00:23:13,700 --> 00:23:17,140 Unfortunately, we know for a fact 365 00:23:17,140 --> 00:23:21,140 that it is not a normal curve. 366 00:23:21,140 --> 00:23:27,860 In fact, it has a very, at first glance, 367 00:23:27,860 --> 00:23:31,500 stock returns do behave, look like a normal. 368 00:23:31,500 --> 00:23:35,580 But if you look a little more carefully 369 00:23:35,580 --> 00:23:43,660 over a long period of data, historical data, 370 00:23:43,660 --> 00:24:02,780 it looks actually like this. 371 00:24:02,780 --> 00:24:05,980 The first thing that it is biased upward. 372 00:24:05,980 --> 00:24:10,020 We know that stock markets, stocks in general, 373 00:24:10,020 --> 00:24:14,460 their returns are biased upward. 374 00:24:14,460 --> 00:24:17,260 In other words, over a long period of time, 375 00:24:17,260 --> 00:24:21,620 returns are positive. 376 00:24:21,620 --> 00:24:28,700 The second thing is these two little bastards right here. 377 00:24:28,700 --> 00:24:32,540 A normal distribution has very thin tails. 378 00:24:32,540 --> 00:24:35,900 After the clumping, in a normal distribution, 379 00:24:35,900 --> 00:24:40,900 67% of the returns are between one standard deviation above 380 00:24:40,900 --> 00:24:42,780 and one standard deviation below. 381 00:24:42,780 --> 00:24:44,540 95% are here. 382 00:24:44,540 --> 00:24:45,940 99% are here. 383 00:24:45,940 --> 00:24:47,380 That's normal. 384 00:24:47,380 --> 00:24:53,260 Unfortunately, we have what are called black swans 385 00:24:53,260 --> 00:24:54,020 and white swans. 386 00:24:57,140 --> 00:25:03,620 In certain periods of time, you can have too many black swans. 387 00:25:03,620 --> 00:25:09,100 In other times, we can have too many white swans. 388 00:25:09,100 --> 00:25:13,980 The normal distribution would say it's a very unusual event. 389 00:25:13,980 --> 00:25:17,060 To give you an idea, once in 100 years, 390 00:25:17,060 --> 00:25:20,140 you would have a black swan if returns are normally 391 00:25:20,140 --> 00:25:21,980 distributed. 392 00:25:21,980 --> 00:25:29,020 We have so far had seven black swans in the last century. 393 00:25:29,020 --> 00:25:32,540 We've had three white swans. 394 00:25:32,540 --> 00:25:34,220 There should be one of each on each side 395 00:25:34,220 --> 00:25:36,220 if it's normally distributed. 396 00:25:36,220 --> 00:25:39,420 So unfortunately, it's not normally distributed. 397 00:25:39,420 --> 00:25:42,540 In fact, we even know what that one is. 398 00:25:42,540 --> 00:25:46,260 The mathematics of probability theory is very old 399 00:25:46,260 --> 00:25:47,500 and it's very complete. 400 00:25:50,620 --> 00:25:59,700 This one down there, it was discovered over 200 years ago. 401 00:25:59,700 --> 00:26:01,780 It's called a Cauchy distribution. 402 00:26:01,780 --> 00:26:03,660 You don't need to know that. 403 00:26:03,660 --> 00:26:08,260 But Cauchy distributions are awful. 404 00:26:08,260 --> 00:26:11,020 I dealt with them in probability theory classes 405 00:26:11,020 --> 00:26:12,340 in graduate school. 406 00:26:12,340 --> 00:26:16,740 They don't have standard deviations, oddly enough. 407 00:26:16,740 --> 00:26:19,940 That's something that nice, quiet, pleasant distributions 408 00:26:19,940 --> 00:26:20,580 have. 409 00:26:20,580 --> 00:26:23,540 Cauchy is a monster. 410 00:26:23,540 --> 00:26:28,540 We pretend that stock returns are normally distributed, 411 00:26:28,540 --> 00:26:33,260 even though we know that by using those for forecasting, 412 00:26:33,260 --> 00:26:35,860 we're going to get our butts kicked every now and then 413 00:26:35,860 --> 00:26:40,460 by a black swan or happily by a white swan. 414 00:26:40,460 --> 00:26:43,140 I'm taking that just as in context here. 415 00:26:48,220 --> 00:26:52,420 Get that son of a bitch off the board. 416 00:26:52,420 --> 00:26:56,260 Fortunately, computers can do this now, 417 00:26:56,260 --> 00:26:57,940 work with Cauchy distributions. 418 00:26:57,940 --> 00:26:59,740 You have to work with them numerically. 419 00:26:59,740 --> 00:27:03,700 But they still carry an underlying little nastiness. 420 00:27:03,700 --> 00:27:08,140 OK, so let me get this off the board here for you 421 00:27:08,140 --> 00:27:09,020 for just a minute. 422 00:27:18,700 --> 00:27:19,900 OK, first thing first. 423 00:27:23,580 --> 00:27:25,980 That's the average. 424 00:27:25,980 --> 00:27:33,980 This is our measure of central tendency, the point where 425 00:27:33,980 --> 00:27:36,580 things want to be. 426 00:27:36,580 --> 00:27:38,700 They're up here, down here. 427 00:27:38,700 --> 00:27:43,620 The x bar is simply, and I'm going 428 00:27:43,620 --> 00:27:54,940 to write this in fancy mathematical notation, 429 00:27:54,940 --> 00:27:56,620 with a will. 430 00:27:56,620 --> 00:27:59,020 Well, actually, I should put this as an r bar. 431 00:27:59,020 --> 00:27:59,780 These are returns. 432 00:28:05,380 --> 00:28:12,900 Our expected return is the sum of each 433 00:28:12,900 --> 00:28:17,340 of the individual returns divided by n. 434 00:28:17,340 --> 00:28:19,460 Add them up, divide by the number of data points. 435 00:28:19,460 --> 00:28:23,460 However, we do have to appreciate that sometimes we 436 00:28:23,460 --> 00:28:24,140 don't have. 437 00:28:24,140 --> 00:28:29,940 This would be true if we had a lot of data. 438 00:28:29,940 --> 00:28:35,780 Each one would have 1 over n probability of occurrence. 439 00:28:35,780 --> 00:28:41,860 In general, if you are running into data 440 00:28:41,860 --> 00:28:44,420 that's a little more complicated, 441 00:28:44,420 --> 00:28:53,740 it would be the sum from i equals 1 442 00:28:53,740 --> 00:28:58,460 to n of each return times the probability 443 00:28:58,460 --> 00:29:01,940 of occurrence of that term. 444 00:29:01,940 --> 00:29:06,020 And let me do it in Excel so you see what I mean by this. 445 00:29:06,020 --> 00:29:06,500 Watch. 446 00:29:06,500 --> 00:29:08,700 Where the hell is my Excel? 447 00:29:08,700 --> 00:29:10,460 I'm just going to whip up a sheet here. 448 00:29:13,620 --> 00:29:14,100 Excel. 449 00:29:14,100 --> 00:29:14,580 Open. 450 00:29:21,700 --> 00:29:24,700 And you can pull up your own Excel for this. 451 00:29:24,700 --> 00:29:33,220 So I'm going to write this as a 452 00:29:33,220 --> 00:29:36,460 and you can pull up your own Excel for this. 453 00:29:36,460 --> 00:29:38,540 I would encourage you to. 454 00:29:45,020 --> 00:29:47,260 I'm sticking with returns. 455 00:29:47,260 --> 00:30:04,420 So return probability. 456 00:30:04,420 --> 00:30:05,780 And I'll go through a couple here. 457 00:30:05,780 --> 00:30:13,980 Let's say that a return of negative 12% on a stock 458 00:30:13,980 --> 00:30:20,860 has a probability of 0.10. 459 00:30:20,860 --> 00:30:32,260 A return of negative 3% has a probability of 0.15. 460 00:30:32,260 --> 00:30:42,140 A return of 5% has a probability of 0.30. 461 00:30:42,140 --> 00:30:57,060 A return of 10% has a probability of 0.25, let's say. 462 00:30:57,060 --> 00:31:18,620 And a return of 14% has a probability 0.2. 463 00:31:18,620 --> 00:31:33,060 So now, the expected return. 464 00:31:37,420 --> 00:31:38,580 Let me separate these. 465 00:31:41,500 --> 00:31:45,660 You would take this times this plus this times this plus this 466 00:31:45,660 --> 00:31:52,420 times this plus this times this. 467 00:31:52,420 --> 00:31:54,780 But we don't have to do it that way. 468 00:31:54,780 --> 00:31:57,100 We can do it by some product. 469 00:31:57,100 --> 00:31:58,700 Are you familiar with some product? 470 00:32:07,100 --> 00:32:09,220 Some product. 471 00:32:09,220 --> 00:32:12,980 This vector, what Excel calls array, 472 00:32:12,980 --> 00:32:15,780 by this vector or array. 473 00:32:20,740 --> 00:32:21,860 And that would be a percent. 474 00:32:21,860 --> 00:32:44,500 So the expected return to the portfolio would be 5.15%. 475 00:32:44,500 --> 00:32:51,740 And then the spread, the standard deviation, 476 00:32:51,740 --> 00:33:04,820 I would need to take each of these returns. 477 00:33:04,820 --> 00:33:06,460 Oops, I did that wrong. 478 00:33:06,460 --> 00:33:07,940 Not an equal sign. 479 00:33:07,940 --> 00:33:09,660 Equals. 480 00:33:09,660 --> 00:33:14,780 The next step is, and I'll put this one up on the web for you 481 00:33:14,780 --> 00:33:17,700 if you're getting behind a little bit. 482 00:33:17,700 --> 00:33:22,380 I would take each of the individual returns 483 00:33:22,380 --> 00:33:25,100 minus the expected return. 484 00:33:29,740 --> 00:33:31,620 And I would make that an absolute 485 00:33:31,620 --> 00:33:35,700 so I could just run it down the line. 486 00:33:35,700 --> 00:33:47,620 And then I would square the result times the probability. 487 00:33:47,620 --> 00:33:58,340 I don't even have to do that, but I will. 488 00:34:07,500 --> 00:34:09,380 And then I would add them up. 489 00:34:09,380 --> 00:34:12,860 I'd get that for every one of them. 490 00:34:12,860 --> 00:34:39,180 And then the standard deviation would be the square root 491 00:34:39,180 --> 00:34:45,500 of the sum of those. 492 00:34:51,500 --> 00:34:52,660 And that is a percent. 493 00:34:52,660 --> 00:35:03,900 It's a measure of the spread. 494 00:35:15,620 --> 00:35:20,460 And the last one that I would, another one. 495 00:35:20,460 --> 00:35:24,740 You see, the problem with the standard deviation 496 00:35:24,740 --> 00:35:29,860 is that it would depend upon the size of the data, what 497 00:35:29,860 --> 00:35:33,180 the numbers were in the original data. 498 00:35:33,180 --> 00:35:39,940 If I wanted to look at different stocks against each other 499 00:35:39,940 --> 00:35:44,500 to measure their standalone risk, 500 00:35:44,500 --> 00:35:49,700 I would need to scale those numbers. 501 00:35:49,700 --> 00:35:59,980 So the coefficient of variation, I have to expand this one more 502 00:35:59,980 --> 00:36:08,700 time, I would take the standard deviation divided 503 00:36:08,700 --> 00:36:12,740 by the expected return. 504 00:36:12,740 --> 00:36:15,660 This would make it so that I could look at different data 505 00:36:15,660 --> 00:36:21,180 sets where the returns had different possibilities 506 00:36:21,180 --> 00:36:23,180 and different probabilities. 507 00:36:23,180 --> 00:36:28,620 And they would all be in the same units. 508 00:36:28,620 --> 00:36:30,980 My ass. 509 00:36:30,980 --> 00:36:31,460 Really? 510 00:36:35,140 --> 00:36:35,940 Good grief. 511 00:36:40,100 --> 00:36:41,340 Did I do that right? 512 00:36:41,340 --> 00:36:48,140 Equals the standard deviation divided by the expected return. 513 00:36:48,140 --> 00:36:51,100 That should not be that, oh, it's doing it as a percent, 514 00:36:51,100 --> 00:36:53,620 and it's not a percent. 515 00:36:53,620 --> 00:36:54,500 It's just a number. 516 00:36:59,380 --> 00:37:00,380 Come figure out. 517 00:37:00,380 --> 00:37:03,180 So in other words, this is just a pure number. 518 00:37:03,180 --> 00:37:04,260 It's not a percent. 519 00:37:04,260 --> 00:37:07,100 It's just a factor. 520 00:37:07,100 --> 00:37:08,780 And that makes it so that I could 521 00:37:08,780 --> 00:37:13,220 see another set of data that had much different looking return, 522 00:37:13,220 --> 00:37:16,340 spreads, and probabilities. 523 00:37:16,340 --> 00:37:19,140 And I could compare the two because they would be unitless. 524 00:37:22,420 --> 00:37:25,140 So I can say, well, I had this other returns. 525 00:37:25,140 --> 00:37:29,500 I had eight returns and eight associated probabilities. 526 00:37:29,500 --> 00:37:36,420 And their standard deviation was like 12.7. 527 00:37:36,420 --> 00:37:40,620 Well, because the numbers were bigger, probably. 528 00:37:40,620 --> 00:37:43,460 And this, the coefficient of variation, 529 00:37:43,460 --> 00:37:48,420 scales the numbers so that they all are unitless. 530 00:37:48,420 --> 00:37:49,860 They're no longer percents. 531 00:37:58,460 --> 00:38:03,660 However, fortunately, we don't have to do this too much. 532 00:38:03,660 --> 00:38:08,140 Because if your data set is large enough, 533 00:38:08,140 --> 00:38:12,940 these probabilities don't mean anything. 534 00:38:12,940 --> 00:38:18,100 All of the probabilities will just be 1, in this case, 535 00:38:18,100 --> 00:38:21,860 1 fifth, 0.20. 536 00:38:21,860 --> 00:38:26,340 If you have enough data, the probabilities kind of wash out 537 00:38:26,340 --> 00:38:27,660 over a large period of time. 538 00:38:27,660 --> 00:38:29,740 And I'm going to show you that in just a minute here 539 00:38:29,740 --> 00:38:31,580 with the much larger data sets where 540 00:38:31,580 --> 00:38:33,740 we don't have to worry about this nonsense. 541 00:38:38,740 --> 00:38:58,220 So in Excel, in Excel, the x bar is, 542 00:38:58,220 --> 00:39:11,260 the way you write that is average of the data set, 543 00:39:11,260 --> 00:39:12,780 average of the data set. 544 00:39:16,180 --> 00:39:21,820 And the standard deviation, STDEV, 545 00:39:21,820 --> 00:39:28,860 is STDEV, open the parentheses. 546 00:39:28,860 --> 00:39:31,140 Now, you've got to be careful here. 547 00:39:31,140 --> 00:39:35,660 Because the formula for a sample is not the same 548 00:39:35,660 --> 00:39:39,980 as the formula for a population. 549 00:39:39,980 --> 00:39:44,580 You will almost never have a population. 550 00:39:44,580 --> 00:39:46,620 Let me show you what happens. 551 00:39:46,620 --> 00:39:47,940 And we'll get one here. 552 00:39:47,940 --> 00:39:49,900 I'll pull up some data, and we'll 553 00:39:49,900 --> 00:39:51,900 play with it here for a little bit. 554 00:39:58,860 --> 00:40:00,940 Is there anything else I want to do here? 555 00:40:00,940 --> 00:40:03,940 I could do, technically, I could do sharp. 556 00:40:03,940 --> 00:40:07,220 But it doesn't really have any meaning in this one 557 00:40:07,220 --> 00:40:09,660 because I didn't have a risk-free rate involved. 558 00:40:09,660 --> 00:40:11,900 But let me take some data here. 559 00:40:11,900 --> 00:40:14,380 OK. 560 00:40:14,380 --> 00:40:23,380 Now, in order to do this, I'm going to go to a place called, 561 00:40:23,380 --> 00:40:26,380 it's an old site. 562 00:40:26,380 --> 00:40:28,900 And everything, all the data sets, 563 00:40:28,900 --> 00:40:31,820 data is everything to us in finance. 564 00:40:31,820 --> 00:40:33,220 Data, data, data. 565 00:40:33,220 --> 00:40:35,380 Everything is data-driven. 566 00:40:35,380 --> 00:40:40,380 And unfortunately, we don't have a data set. 567 00:40:40,380 --> 00:40:46,100 Unfortunately, the data is almost always costly. 568 00:40:46,100 --> 00:40:48,740 You have to pay a subscription fee. 569 00:40:48,740 --> 00:40:52,980 And sometimes that subscription fee can be insane. 570 00:40:52,980 --> 00:40:57,580 And that's the kind of stuff that artificial intelligence 571 00:40:57,580 --> 00:41:00,340 needs to do its modeling. 572 00:41:00,340 --> 00:41:03,340 It has to have millions of data points 573 00:41:03,340 --> 00:41:05,660 for it to have reliability. 574 00:41:05,660 --> 00:41:08,500 So that means you have to have the ability 575 00:41:08,500 --> 00:41:14,220 to subscribe the AI into these expensive data sets. 576 00:41:14,220 --> 00:41:16,220 And they're all over the place. 577 00:41:16,220 --> 00:41:19,220 Now, the Federal Reserve has data. 578 00:41:19,220 --> 00:41:22,300 But it actually isn't that dense. 579 00:41:22,300 --> 00:41:26,380 In order for you to go and get real actual data that 580 00:41:26,380 --> 00:41:30,020 is like millions and millions of data points, 581 00:41:30,020 --> 00:41:32,860 you have to subscribe to some of these services. 582 00:41:32,860 --> 00:41:36,620 I mean, I just ran into one a couple of weeks ago 583 00:41:36,620 --> 00:41:38,620 called PIG. 584 00:41:38,620 --> 00:41:41,620 And PIG is just vast. 585 00:41:41,620 --> 00:41:43,620 But you subscribe to it. 586 00:41:43,620 --> 00:41:46,620 S&P has databases, too. 587 00:41:46,620 --> 00:41:48,620 They are vast. 588 00:41:48,620 --> 00:41:50,620 But you have to subscribe to those. 589 00:41:50,620 --> 00:41:53,620 And there are dozens of other services. 590 00:41:53,620 --> 00:41:56,620 This site that I'm going to try to find here for you, 591 00:41:56,620 --> 00:41:58,620 it's been around forever. 592 00:41:58,620 --> 00:42:00,620 Its data isn't that vast. 593 00:42:00,620 --> 00:42:02,620 But it's free. 594 00:42:02,620 --> 00:42:04,620 And it's very reliable. 595 00:42:04,620 --> 00:42:09,620 We know that the data doesn't have any mistakes in it. 596 00:42:09,620 --> 00:42:12,620 And every year, they update it. 597 00:42:12,620 --> 00:42:14,620 Let me see. 598 00:42:14,620 --> 00:42:24,620 Stock returns historical data. 599 00:42:24,620 --> 00:42:27,620 And the place that I'm going to look for is one stock one. 600 00:42:27,620 --> 00:42:34,620 One stock one. 601 00:42:34,620 --> 00:42:37,620 One stock one dot com. 602 00:42:37,620 --> 00:42:41,620 There it is. 603 00:42:41,620 --> 00:42:43,620 Now I want you to look at something. 604 00:42:43,620 --> 00:42:47,620 What's cool about this data set is that they have these indexes. 605 00:42:47,620 --> 00:42:53,620 The Dow, the return to the Dow since 1975, the return to S&P. 606 00:42:53,620 --> 00:42:55,620 But it has ETFs. 607 00:42:55,620 --> 00:43:01,620 Any stock you could think of that is publicly traded, you can find here. 608 00:43:01,620 --> 00:43:08,620 You can also find data for other countries, for their markets in here as well. 609 00:43:08,620 --> 00:43:13,620 So if you're not trying to solve the problems of the world, 610 00:43:13,620 --> 00:43:18,620 you just want to get data and do some modeling or testing 611 00:43:18,620 --> 00:43:24,620 or just try out your mad Excel skills, you can't beat this. 612 00:43:24,620 --> 00:43:28,620 So watch, what I'll do here, there are a couple of ways in Excel 613 00:43:28,620 --> 00:43:31,620 that you can capture data from a website. 614 00:43:31,620 --> 00:43:37,620 And I'm almost reticent to do this here, but what the hell. 615 00:43:37,620 --> 00:43:39,620 I'll try it and see. 616 00:43:39,620 --> 00:43:45,620 First things first, in order to use Excel to capture a data table, 617 00:43:45,620 --> 00:43:46,620 I'll go to this. 618 00:43:46,620 --> 00:43:51,620 Oh, will you stop it with that? 619 00:43:51,620 --> 00:43:54,620 Okay, here is the Dow Jones. 620 00:43:54,620 --> 00:44:02,620 Now what I'm going to need to do to accomplish this is I'm going to actually 621 00:44:02,620 --> 00:44:08,620 copy, control C, the URL, the web address. 622 00:44:08,620 --> 00:44:20,620 Now I'm going to go over here to Excel and I'm going to say data, 623 00:44:20,620 --> 00:44:25,620 get a data set. 624 00:44:25,620 --> 00:44:28,620 Oh, come on. 625 00:44:28,620 --> 00:44:32,620 There's one for get it from the web. 626 00:44:32,620 --> 00:44:38,620 Oh, yeah, this new one actually can get a picture, the data from a picture. 627 00:44:38,620 --> 00:44:41,620 No, is that maybe what I want? 628 00:44:41,620 --> 00:44:43,620 Okay, close. 629 00:44:43,620 --> 00:44:47,620 If I can find, this is, oh, quit it, cancel. 630 00:44:47,620 --> 00:44:50,620 I want to get data from the web. 631 00:44:50,620 --> 00:44:53,620 And I'm not familiar with this 365. 632 00:44:53,620 --> 00:44:58,620 I've got the professional version. 633 00:44:58,620 --> 00:45:04,620 From, yeah, it's got some of the major ones, from a file, no, 634 00:45:04,620 --> 00:45:14,620 from a power platform, online, from other sources, from the web. 635 00:45:14,620 --> 00:45:15,620 There we go. 636 00:45:15,620 --> 00:45:17,620 There's the one I want from the web. 637 00:45:17,620 --> 00:45:18,620 Apologies for that. 638 00:45:18,620 --> 00:45:21,620 And it will say, all right, where is this data? 639 00:45:21,620 --> 00:45:27,620 Control V, that's when I'll paste in the URL. 640 00:45:27,620 --> 00:45:29,620 And I'll say, okay. 641 00:45:29,620 --> 00:45:35,620 Now it's going to run over there and it's going to say, okay, I see it right there. 642 00:45:35,620 --> 00:45:36,620 I think. 643 00:45:36,620 --> 00:45:43,620 Now, connect. 644 00:45:43,620 --> 00:45:47,620 And it's going to say, I see a bunch of tables there. 645 00:45:47,620 --> 00:45:54,620 Now, one of the problems with websites, and this is, this wasn't how we did it in the old days, 646 00:45:54,620 --> 00:45:59,620 but that top thing you see in websites, the menus, and the clickdowns and all that, 647 00:45:59,620 --> 00:46:02,620 that's actually set up as a table. 648 00:46:02,620 --> 00:46:05,620 And that's not what you probably want. 649 00:46:05,620 --> 00:46:07,620 So I'll bet it's the second table. 650 00:46:07,620 --> 00:46:08,620 And it'll give me a preview. 651 00:46:08,620 --> 00:46:09,620 Ah, it is. 652 00:46:09,620 --> 00:46:10,620 Well, spank me, Jesus. 653 00:46:10,620 --> 00:46:11,620 Look at that. 654 00:46:11,620 --> 00:46:13,620 Do you see it? 655 00:46:13,620 --> 00:46:15,620 Okay, that's the one I want. 656 00:46:15,620 --> 00:46:19,620 Okay, so load that mother. 657 00:46:19,620 --> 00:46:22,620 Oh, that data set. 658 00:46:22,620 --> 00:46:24,620 You know what I mean, for God's sake. 659 00:46:24,620 --> 00:46:28,620 Look, it's right there. 660 00:46:28,620 --> 00:46:33,620 Well, isn't that a very convenient thing? 661 00:46:33,620 --> 00:46:39,620 Okay, so I will now call, give that a meaningful name. 662 00:46:39,620 --> 00:46:41,620 I will call it George. 663 00:46:41,620 --> 00:46:42,620 No, I won't. 664 00:46:42,620 --> 00:46:49,620 I'll call it Dow 30. 665 00:46:49,620 --> 00:46:51,620 And I've got it. 666 00:46:51,620 --> 00:46:55,620 So now let me do sheet two. 667 00:46:55,620 --> 00:46:56,620 Go over here. 668 00:46:56,620 --> 00:47:05,620 And I'm going to go back to, back up one, the S&P 500. 669 00:47:05,620 --> 00:47:08,620 And I'm going to get its URL. 670 00:47:08,620 --> 00:47:14,620 And I'm going to do the same stupid pet trick, now that I remember. 671 00:47:14,620 --> 00:47:22,620 Data from the web. 672 00:47:22,620 --> 00:47:28,620 Give it that webpage and say Okie Dokie. 673 00:47:28,620 --> 00:47:30,620 And I'll also say, let's have a look at table. 674 00:47:30,620 --> 00:47:32,620 Yes, that's table one there. 675 00:47:32,620 --> 00:47:35,620 So load that mother, or that thing. 676 00:47:35,620 --> 00:47:39,620 See it? 677 00:47:39,620 --> 00:47:41,620 And I got that one. 678 00:47:41,620 --> 00:47:53,620 And I'll call that S&P 500. 679 00:47:53,620 --> 00:47:58,620 And now I'm going to do the trick one more time. 680 00:47:58,620 --> 00:48:01,620 I'm going to go to the site and I'm going to get the NASDAQ, 681 00:48:01,620 --> 00:48:05,620 historical data, or the health. 682 00:48:05,620 --> 00:48:07,620 There it is. 683 00:48:07,620 --> 00:48:12,620 And I will capture its web address. 684 00:48:12,620 --> 00:48:24,620 And I'll go back to Excel and I'll say data, get data from the web. 685 00:48:24,620 --> 00:48:27,620 Ok. 686 00:48:27,620 --> 00:48:28,620 And it's table one. 687 00:48:28,620 --> 00:48:29,620 It is every time. 688 00:48:29,620 --> 00:48:33,620 So load that data. 689 00:48:33,620 --> 00:48:37,620 Now if you do this right, it saves you a lot of pain. 690 00:48:37,620 --> 00:48:46,620 Because a long time ago, we actually had to type in each data entry on our own, 691 00:48:46,620 --> 00:48:52,620 or do a copy and paste and have Excel misunderstand how it worked. 692 00:48:52,620 --> 00:48:53,620 This way is a lot more powerful. 693 00:48:53,620 --> 00:48:56,620 And you can just draw down masses. 694 00:48:56,620 --> 00:49:02,620 If you can find a place like, well, FRED, the Federal Reserve's database, 695 00:49:02,620 --> 00:49:06,620 that data is kind of, they do it by day sometimes. 696 00:49:06,620 --> 00:49:08,620 You might want ten years. 697 00:49:08,620 --> 00:49:13,620 Well you just do this trick I did here and it will slam that data right, 698 00:49:13,620 --> 00:49:16,620 all of it, into Excel. 699 00:49:16,620 --> 00:49:18,620 As a matter of fact, FRED is even better than that. 700 00:49:18,620 --> 00:49:22,620 You can actually tell it to download the data as an Excel sheet. 701 00:49:22,620 --> 00:49:24,620 So they're cheating these days. 702 00:49:24,620 --> 00:49:26,620 Ok. 703 00:49:26,620 --> 00:49:28,620 There we go. 704 00:49:28,620 --> 00:49:33,620 Now let's go back here and do some stupid pet tricks. 705 00:49:33,620 --> 00:49:37,620 Remember that what I want are returns. 706 00:49:37,620 --> 00:49:38,620 Ok. 707 00:49:38,620 --> 00:49:39,620 I want returns. 708 00:49:39,620 --> 00:49:43,620 So these other ones really, the year obviously matters, 709 00:49:43,620 --> 00:49:49,620 but what I really want are the returns for each of these. 710 00:49:49,620 --> 00:49:51,620 So, whoops. 711 00:49:51,620 --> 00:49:59,620 Oh, I should have, I forgot to label that NASDAQ. 712 00:49:59,620 --> 00:50:01,620 So here we are. 713 00:50:01,620 --> 00:50:02,620 We got all our returns. 714 00:50:02,620 --> 00:50:05,620 So here's what we're going to do. 715 00:50:05,620 --> 00:50:09,620 And this is another stupid pet trick. 716 00:50:09,620 --> 00:50:17,620 I'm going to highlight all of these numbers. 717 00:50:17,620 --> 00:50:18,620 All of these. 718 00:50:18,620 --> 00:50:22,620 I hold down the control key and I click on the ones that I want. 719 00:50:22,620 --> 00:50:32,620 Now I'm going to go down here and I'm going to put in the average. 720 00:50:32,620 --> 00:50:36,620 And I'm going to put in one more here too that I haven't talked about. 721 00:50:36,620 --> 00:50:38,620 The median. 722 00:50:38,620 --> 00:50:41,620 The median is the halfway point in the data. 723 00:50:41,620 --> 00:50:43,620 That's not the average. 724 00:50:43,620 --> 00:50:47,620 And in fact, the difference between them is kind of important. 725 00:50:47,620 --> 00:50:57,620 But then I'm also going to do the standard deviation. 726 00:50:57,620 --> 00:51:10,620 And I'm going to do also the coefficient of variation. 727 00:51:10,620 --> 00:51:14,620 Now remember, I've got all three sheets highlighted. 728 00:51:14,620 --> 00:51:20,620 So what I do to one will be done to all of them. 729 00:51:20,620 --> 00:51:29,620 So the first thing I'm going to do is I'm going to say equals average of all the date, of the returns. 730 00:51:29,620 --> 00:51:33,620 The average of the returns. 731 00:51:33,620 --> 00:51:44,620 Now I'll turn these into percents eventually here. 732 00:51:44,620 --> 00:51:48,620 And then I'm going to do the median. 733 00:51:48,620 --> 00:52:00,620 The central point of all the data. 734 00:52:00,620 --> 00:52:04,620 Now this next one I caution. Watch out for what you do. 735 00:52:04,620 --> 00:52:09,620 I'm going to write the standard deviation equals STDEV. 736 00:52:09,620 --> 00:52:12,620 Now notice that it gives you a whole menu. 737 00:52:12,620 --> 00:52:16,620 The second one,.S, is the one we want. 738 00:52:16,620 --> 00:52:22,620 But if you don't put anything there, it will do.S. 739 00:52:22,620 --> 00:52:24,620 You don't have to. It defaults to.S. 740 00:52:24,620 --> 00:52:27,620 Thank heaven. I never remembered to do that. 741 00:52:27,620 --> 00:52:40,620 Okay, STDEV. 742 00:52:40,620 --> 00:52:44,620 Close the parentheses. 743 00:52:44,620 --> 00:52:51,620 And finally, the coefficient of variation equals, 744 00:52:51,620 --> 00:53:02,620 and this one you just do by, you just do the standard deviation divided by the average. 745 00:53:02,620 --> 00:53:06,620 Now let me turn all these, these first three are percentages. 746 00:53:06,620 --> 00:53:13,620 So I'll turn those into percentages to two decimal places. 747 00:53:13,620 --> 00:53:15,620 Okay, so there's my Uncle Bob. 748 00:53:15,620 --> 00:53:20,620 Now let's go back through and look at what we found. 749 00:53:20,620 --> 00:53:24,620 The average. 750 00:53:24,620 --> 00:53:32,620 For the Dow, the average over the last, now this data was from 1975 to 2023. 751 00:53:32,620 --> 00:53:37,620 So that would make it, what, 25, 48 years. 752 00:53:37,620 --> 00:53:40,620 This is 48 years of data. 753 00:53:40,620 --> 00:53:45,620 The average return to the Dow was 9.79%. 754 00:53:45,620 --> 00:54:05,620 The average return to the, whoops, it didn't work, equals average, I went too far. 755 00:54:15,620 --> 00:54:19,620 Median. 756 00:54:19,620 --> 00:54:23,620 I have to do median of this data. 757 00:54:23,620 --> 00:54:28,620 I took that too far with that, all sheets. 758 00:54:28,620 --> 00:54:40,620 It took the first set of data for all of the sheets. 759 00:54:40,620 --> 00:55:02,620 Again, equals median, median of the second tape, that table of data. 760 00:55:02,620 --> 00:55:17,620 Standard deviation of all that data. 761 00:55:17,620 --> 00:55:18,620 There we go. 762 00:55:18,620 --> 00:55:38,620 Now let me do this S&P 500 again, average of that data. 763 00:55:38,620 --> 00:55:43,620 Equals median of that data. 764 00:55:43,620 --> 00:55:52,620 Yeah, I was taking the first table for all of them. 765 00:55:52,620 --> 00:56:03,620 And for that one, equals STDEV of this data. 766 00:56:03,620 --> 00:56:06,620 Wow. 767 00:56:06,620 --> 00:56:09,620 My cool pet trick didn't work there. 768 00:56:09,620 --> 00:56:11,620 Okay, so now let's go back. 769 00:56:11,620 --> 00:56:17,620 The Dow, for the last 48 years, the average return. 770 00:56:17,620 --> 00:56:28,620 If you had just put money in and left it there, your average annual return would have been 9.79%. 771 00:56:28,620 --> 00:56:38,620 For the S&P 500, a somewhat riskier portfolio, it would have been 10.32%. 772 00:56:38,620 --> 00:56:49,620 For the NASDAQ, if you had just stuck the money in there and left it, the average return would have been 14.84%. 773 00:56:49,620 --> 00:56:53,620 There is the risk-return relationship. 774 00:56:53,620 --> 00:56:57,620 The greater the risk, the greater the return. 775 00:56:57,620 --> 00:57:03,620 Now let's go back here and look at some things. 776 00:57:03,620 --> 00:57:14,620 The median tells us half of the numbers are below that number and half are above that number. 777 00:57:14,620 --> 00:57:23,620 When the average and the median are different, that tells us about the skewedness. 778 00:57:23,620 --> 00:57:29,620 Here's what it means. 779 00:57:29,620 --> 00:57:36,620 To a certain extent, it tells us about how much different from a perfect bell curve the data is. 780 00:57:36,620 --> 00:57:38,620 Look at this one. 781 00:57:38,620 --> 00:57:46,620 Suppose that you had this long tail of data like that. 782 00:57:46,620 --> 00:57:58,620 These low numbers would actually pull down the average from what it should be. 783 00:57:58,620 --> 00:58:04,620 So you have, on this case, the average would be higher than the median. 784 00:58:04,620 --> 00:58:07,620 The median doesn't care the size of the numbers. 785 00:58:07,620 --> 00:58:09,620 See all these big numbers up here? 786 00:58:09,620 --> 00:58:13,620 They are being counterbalanced by this long tail down here. 787 00:58:13,620 --> 00:58:16,620 The same would go the other way. 788 00:58:16,620 --> 00:58:23,620 So I could have some high returns that would pull the average upward. 789 00:58:23,620 --> 00:58:29,620 That's a problem on tests where a lot of people are not doing all that great. 790 00:58:29,620 --> 00:58:35,620 But you have just a small cluster, maybe five, eight students who ace the test. 791 00:58:35,620 --> 00:58:41,620 They make the average look higher than the data is really telling you it is. 792 00:58:41,620 --> 00:58:48,620 So in this case, the average is being drawn down by heavy losses in a few years. 793 00:58:48,620 --> 00:58:51,620 That's the black swans. 794 00:58:51,620 --> 00:59:01,620 The median, half the numbers are below it, half of them are above it, is telling us a different story. 795 00:59:01,620 --> 00:59:09,620 So in a case like this, I would say that the average is being affected by heavy losses that make the average, 796 00:59:09,620 --> 00:59:14,620 those really low numbers, those negative numbers, are affecting the average, 797 00:59:14,620 --> 00:59:17,620 but they don't do anything to the median. 798 00:59:17,620 --> 00:59:22,620 Look at the S&P 500, the same problem is occurring there. 799 00:59:22,620 --> 00:59:27,620 The black swans are pulling the average downward. 800 00:59:27,620 --> 00:59:32,620 But look at the NASDAQ. 801 00:59:32,620 --> 00:59:38,620 The black swans don't affect the distribution as much. 802 00:59:38,620 --> 00:59:51,620 In other words, small cap companies are not as sensitive to black swans as the big 500 or the massive Dow are. 803 00:59:51,620 --> 00:59:54,620 Now look at the coefficients of variation. 804 00:59:54,620 --> 01:00:05,620 For the Dow, the CV, well, let me put this to about 2%. 805 01:00:05,620 --> 01:00:12,620 For the Dow, the coefficient of variation is 1.5. 806 01:00:12,620 --> 01:00:19,620 For the S&P 500, it's 1.56. 807 01:00:19,620 --> 01:00:23,620 In other words, there's a little more absolute spread. 808 01:00:23,620 --> 01:00:35,620 And for the NASDAQ, there's more spread, absolutely. 809 01:00:35,620 --> 01:00:45,620 So that's why the CV, see if I just look at the numbers, you get that. 810 01:00:45,620 --> 01:00:55,620 For the Dow, you get 14.67. For the S&P 500, you get 16.8. 811 01:00:55,620 --> 01:00:59,620 But that's because the Dow's numbers are bigger. 812 01:00:59,620 --> 01:01:05,620 Once I get that bigness, that difference in size of the numbers out of there, 813 01:01:05,620 --> 01:01:10,620 we can see the true risk-return relationship coming through. 814 01:01:10,620 --> 01:01:20,620 So look at this. 9.79 on an absolute risk metric of 1.5. 815 01:01:20,620 --> 01:01:34,620 10.32, 1.56. 14.84, 1.68, the risk-return relationship. 816 01:01:34,620 --> 01:01:42,620 Now I'll put this up for you to play around with, but this gives you visually what we constantly are talking about, 817 01:01:42,620 --> 01:01:46,620 that risk-return relationship, the greater the risk, the greater the return. 818 01:01:46,620 --> 01:01:54,620 And here, over a very large amount of data, we see it shining through. 819 01:01:54,620 --> 01:01:57,620 We also see some hints, too. 820 01:01:57,620 --> 01:02:09,620 We see some hints that, for example, like I said, black swans pulled down the average for the Dow 821 01:02:09,620 --> 01:02:19,620 and for the S&P 500 far more than they do for the small cap stocks. 822 01:02:19,620 --> 01:02:23,620 And we also see that risk-return relationship coming through again. 823 01:02:23,620 --> 01:02:29,620 And you can do this with tons of different companies, tons of different indexes. 824 01:02:29,620 --> 01:02:33,620 It always shows up. It always does. 825 01:02:33,620 --> 01:02:43,620 The same phenomenon, lower risk portfolios, lower expected returns, more skew in the data. 826 01:02:43,620 --> 01:02:54,620 And as you get higher and higher risk of the portfolio, you get less skew and you get higher spread, absolutely speaking, 827 01:02:54,620 --> 01:02:57,620 which is what the CV does. 828 01:02:57,620 --> 01:03:01,620 That's good to know for all of us in our business. 829 01:03:01,620 --> 01:03:04,620 And we can play with this on Monday. 830 01:03:04,620 --> 01:03:07,620 I'm going to, if you don't have it, I'll try to do it real quick here. 831 01:03:07,620 --> 01:03:10,620 Let me show you something. 832 01:03:10,620 --> 01:03:21,620 In files, options, I think it's options, go to add-ins. 833 01:03:21,620 --> 01:03:27,620 See this right here, this analysis tool pack? 834 01:03:27,620 --> 01:03:31,620 You click on that one and say go. 835 01:03:31,620 --> 01:03:35,620 And you want the analysis tool pack, okay. 836 01:03:35,620 --> 01:03:44,620 Now what that will do is it will give you the access to a pile of data analytics. 837 01:03:44,620 --> 01:03:51,620 You can do all kinds of things like this and all kinds of other things with that data analysis pack. 838 01:03:51,620 --> 01:03:55,620 And all you have to do is say here's the data, use the data analysis pack, 839 01:03:55,620 --> 01:04:01,620 and it will make an extra spreadsheet with all of these statistics on it for you. 840 01:04:01,620 --> 01:04:10,620 So again, in order to do that, you do files, options, and you go down to the add-ins, 841 01:04:10,620 --> 01:04:15,620 and you choose the data analysis pack and you say go. 842 01:04:15,620 --> 01:04:18,620 And it will put it in there for you. 843 01:04:18,620 --> 01:04:22,620 Now for some of you, you'll have to do it every time you, 844 01:04:22,620 --> 01:04:29,620 if you want to do a new data analysis, some Office 365s, 845 01:04:29,620 --> 01:04:33,620 you have to do this little thing every time to get the pack loaded. 846 01:04:33,620 --> 01:04:37,620 Others, it will be in there permanently. 847 01:04:37,620 --> 01:04:39,620 But anyway, you have a quiz to take. 848 01:04:39,620 --> 01:04:44,620 Please take that quiz, and once you're finished with it, that's all I have for you today. 849 01:04:44,620 --> 01:04:59,620 Thank you.