Alan Cring Productions in association with the Emergent Light Studio presents
the Illinois State Collegiate Compendium, Academic Lectures in Business and Economics.
This is Business Finance, FIL 240 for spring semester 2024.
Today, Risk and Return.
This subject is mathy, but I will show you in Excel some kind of like stupid pet tricks
that are good for doing risk return types of problems.
It's not really so much a template as it's a way of approaching it.
How to do formulas in Excel for different situations.
I will also in this lecture show you how you can use Excel to access databases online.
To pull in data so that you've got giant amounts of data to do formulas
without actually going into keying in numbers from the database and all that.
Those are really useful for a lot of projects.
One thing that I should mention before we get down to the main part of the lecture
is that the world is being driven by data now.
It's just like everything is data, data driven.
In fact, artificial intelligence learns and does giant data
bases, millions and millions of data points to get a model to work the way we want it to.
With the artificial intelligence, oftentimes what it will do is it will see,
let's say, a million data points for a type of formula or a model.
What it will do is it will take like maybe 900,000 of those data points
to build an algorithm.
Then it will take that other 100,000 and it will test how good it is
with that prediction using that data that it didn't use to create the model.
This is how a lot of chat GPTs do this all the time.
A lot of data models, almost all AI, does this.
For us as human beings, as it were, the non-AIs, we do this too.
It's one of the things that you will do in business is to create a model.
We'll create the model and build it using a bunch of data,
but we'll save some of the data and see if the model can predict properly
and hit those other data points relatively well.
That's a lot of what goes on in the high-powered world of business these days.
What I'm trying to convince you as I do all of this is that math is a lot
of what we do these days, quantitative, and Excel is our way of doing all of this.
Bear with me because it actually can be useful to you for courses you'll take after this,
for doing research papers and term papers for other courses.
Anyway, first we do a look at the numbers.
As you can see, the numbers for the markets today are just on the grouchy side.
It's another one of those days that I've been showing those to you lately where
you don't have any major movement up or down, but you do have a slight grouchiness.
All of these are bear indicators. You see all that red.
So the markets are not in a really bad mood,
but they are certainly not chipper or grouchy.
So what does that say to us?
It simply says that right now the markets don't have a clear direction from here.
Remember, everything is driven by our expectations of the future,
and that's what's going on every time you look at the numbers.
They have nothing to do with what has already happened.
What is being shown to us is not a good thing.
They have nothing to do with what has already happened.
What is being shown to us is what we see as our best estimate of what is about to happen
or what is going to happen, and we don't have a very clear picture right now.
We do know that the economy is on the upswing,
but that's already in the numbers that have been posted.
That's already happened, the view that there is a good time ahead.
But from there, are we seeing that it's going to be better than we think
or it's going to be worse than we think?
That's what we're seeing here, is that we can't see clearly
from the expectations we have already created for the future.
And that gets into the subject of today's lecture, which is on risk and return.
And one of the first things that I'll do is I will informally define risk for you.
But looking here, there's nothing really to be said from these numbers.
The Dow is down a fraction, just out of four hundredths of a percent.
That's really the same as saying it's flat.
The S&P 500 is down a little more, 0.11 percent, but nothing dramatic.
And then the NASDAQ is down a little more, 0.14 percent, but again, nothing major.
It's not telling us anything big.
Now, going over here, looking quickly at crude,
it's in a new trading range from 81 to maybe 88, somewhere in there.
It's on the lower end of that, and as you can see,
it's been tailing all day long.
One thing about, and I can't remember whether I've told you or not,
commodity markets don't have the traditional opening bell at 8.30 a.m.
or something like that, closing at 3.30 or whatever.
They don't have that.
Each commodity's market has its own cycle of the day.
And the crude market has been operating since last night.
That spark chart you see there,
the beginning point would have been sometime in the night last night.
And so they're well into their trading day right now.
And as you can see, it's been dropping through their trading day today.
It was up there, I think it was like 83 or something like that,
when it opened up late last night,
and it's been petering downward slowly all through the day.
Good news.
Unfortunately, it's still higher than it was a few weeks ago
when it was in that 72 to 79 trading range.
It's above that.
So you're going to see gas prices.
And as I had said, it's now showing up,
gas prices a little higher than what they were a couple of weeks ago.
It's nothing, nothing's a killer.
Now, gold, it looks to me,
and I've got some backing for this from others,
that gold is now in its own new trading range.
Several months ago, it would not get above $2,000 an ounce.
Well, it busted through that.
Now it seems to have a resistance level
somewhere a little below $2,200 an ounce.
You see how it pops, it's just bouncing around.
It's not really doing anything.
It's just bouncing around about right now $2,161.
But so there's nothing, we can't see anything from gold.
If gold were skyrocketing, well, that's bad news
because investors are running away from stocks and bonds,
but that's not what's happening.
Coming over here to the 10-year bond,
okay, the yields are dropping, fortunately, again.
It's not a huge amount.
It's just two basis points, but that's good news.
Yields are going down.
That means prices are going up,
which means that their investors are buying into bonds.
It's nothing, no major movement.
It's not like a flight to quality or anything.
But still there's some purchasing of bonds
as a safe harbor kind of investment,
but it's nothing, I mean, two basis points.
It's anything exciting.
But yields going down, that's always good for the economy.
Interest rates getting a little bit lower.
Now over on the other side of the world,
Japan actually started out down,
and it crawled back up by the end of the day trading,
which was last night for us.
It was up about two-thirds of a percent,
so that's a decent day up.
Fairly modest, but decent bull market started to form,
especially in the later part of the trading over in Japan.
Now London just doesn't want to seem to do anything.
It's down and then it's up, but as you can see,
by the end it was just sitting about where it started.
And so they're having kind of the same thing over there
that we're having here.
No clear direction on where it's going to go from here.
So if we don't have a good idea,
no one's going to trade heavily bull or bear.
You just sort of wait until you get a better signal
about what's going to happen.
It's kind of hard to say much more than that
about the markets today.
We're in a holding pattern,
and it may last another day, another week.
It could hold actually like this for as much as a month
before we get a clearer picture of what's going to happen next.
But one thing is for sure, and I've emphasized this before,
even though the news is full of political stuff, dramatic,
while we just barely almost didn't have a budget agreement
or this presidential candidate can't come up
with a half a billion dollar bond
or this candidate is getting senile,
that really doesn't affect markets all that much
unless it's something insanely dramatic.
Politics is not what we do.
We are looking at the productivity of an economy,
the wealth of the nation,
and its prospects for prosperity in the future.
The political scene, if you get a newsletter
or some advice columnist saying or talking head,
going on about the politics
as if that has a lot to do with the markets,
get away from them.
They're just creating sensationalism.
The people who've got the money, who do the investment,
who do the heavy, big investments,
that politics is in Washington.
We're in the fire and shrapnel
at the corner of Wall Street and my portfolio's value.
I'm going to start out with a couple of broad things here.
And just simply, let me take that off the board here
for just a little bit.
Terminology.
And some of this I've brought up before,
and some of it's a little bit new.
The first thing that I want to bring up to you is risk again.
Risk.
Risk in our world, and this is not a terribly formal definition,
but it captures the essence of what we consider risk.
Risk, first of all, has to do with the variety of possible outcomes.
If I know what an outcome is going to be,
there's only one possible outcome, there's no risk.
But if there are two possible outcomes,
then the term risk becomes meaningful to us.
So the first thing about risk would be how many outcomes?
How many outcomes?
Now, that's not enough, but that's a start.
As long as there's more than one possible outcome,
then risk plays in.
But, and the next one is how different are the outcomes?
How different are the possible outcomes?
If you've got a hundred possible outcomes,
but they're all virtually the same, that's not a lot of risk.
That's not a lot of risk.
But you could have two outcomes.
Okay, madam, you are either going to live or die.
Well, that's a rather different outcome, you know, right there.
So, in other words, if, like a stock,
well, the stock could go up 11% or it could go up 11.1%.
You know, that's not, that's minimal risk.
But there is risk because there's more than one possible outcome,
but that's not much.
But if the outcomes, even a few, if they're widely separated,
then there is considerable risk involved.
Another part of this is how,
and I want to use the word clustered,
are the outcomes.
That goes back to what I said in the first one.
You could have some really, really bad or good outcomes,
but most of the possible outcomes are really close together.
Then that would, so, for example,
if I were to draw a normal distribution,
here's one where, here are two.
Most of the probabilities in the second one are right around the center.
But in that second one, there's a spread,
there's more probability in these tails,
and so there's more risk involved in that one.
And there are a few others that are involved here in risk,
but we'll get into those later.
But that kind of gives you the idea of what in general we consider to be risk.
A risky portfolio would be one where the outcomes,
obviously there will be different possible outcomes,
but there is a good chance that some of those outcomes are going to kill us
and some of them are going to really help us out a lot,
as opposed to an investment where most of the outcomes are very close to the expected value,
the average, as it were.
Now, this term, this first term,
there are two types of risk that the book brings up.
There's actually a third one here, too,
and we'll kind of ignore that one for the time being.
And this term, standalone risk,
really never showed up in textbooks,
and I don't recall even hearing the term,
until maybe a decade ago, and now it's kind of the popular term.
The standalone risk is the risk of that individual thing.
In stock, for example, the standalone risk would be the risk of that stock's returns.
And by the way, we talk about returns instead of prices,
but one way or the other.
Standalone risk, that is the overall variation that's possible in the returns to that stock.
Now, the other one, portfolio risk, is that stock in a portfolio,
because it will not, that stock's contribution to the portfolio will be mitigated,
its own risk will be mitigated by the other stocks in the portfolio.
So one stock, you might have a stock that goes, its returns go like that,
but you might have another stock in that portfolio whose returns go like that.
And so they're going to temper, they're going to cancel out part of each other's risk.
This is something I'll show you on Monday, that when you're making,
if you want to build your own portfolio, one of the things that you want to do is look for stocks
that don't have a high correlation in the returns.
They actually are poorly correlated.
That way, they kill off each other's individual weirdness.
And that's one of the things that we have to watch out for.
When we look at an individual stock, we have to say, okay,
the management of this company is all over the board and crazy and all that kind of stuff.
So I'm not going to invest in that stock.
Not necessarily a good thing to think, because you could get another stock
that actually helps cancel out some of the standalone risk of the company that's crazy.
So putting stocks together, and this is something that is not just with stocks,
it's all through the universe, in fact.
Healthcare works the same way. Healthcare insurance.
You see, I'm going to tell you about your life.
This is, on the vertical axis, is healthcare cause.
And on the horizontal axis is time.
Now in your case, you are a sickly little baby.
You were born, you were just a pain in the ass, always in the hospital, not very happy.
And so your cause kind of came up, but then you started getting okay, feeling better.
Then you got into your toddler years, got some broken bones.
Then you started to normalize out a little bit.
And then you found out about girls.
And then you calmed down, then college, drugs.
Then you started maturing, you didn't get as many illnesses,
started getting the herd immunities.
And so then you got to your 40s.
There was high blood pressure, diabetes, Viagra.
And then you got into your older age, kidney problems.
And finally you die.
Now, let's look at your life on the same line.
Well, you see, on that same line, you started out, you were born, just a healthy baby, just a few vaccinations.
But then you had a childhood illness.
But then everything started doing okay.
And then you found out about boys, and then you got over that.
Then you got into your 20s, you were still pretty healthy.
And then you had to have some liposuction.
And then you got a little bit of high cholesterol, heart problems.
You live longer.
And then you die.
Yes, you'll live longer than he will, statistically.
Trust me, it's okay for us to die first.
But you understand, though, if I start putting hundreds and hundreds and hundreds, thousands of these profiles,
all on the same grid, over and over, eventually the average smooths out.
That's called risk pooling.
The more you put in, I don't see, and that was one of the problems was for ages,
insurance companies, well, we're going to not do those people because they have higher incidence of heart attack.
But they had lower incidence of other things.
Cherry picking for this kind of risk pooling is a bad idea.
The more you put in, the smoother it gets.
And so the health insurance company has a predictable cost level per year.
And so setting premium levels, the healthcare costs plus some profit and all that, is much more certain.
That portfolio risk showing up again, we see it all the time, all through the universe.
The individual atoms that are bouncing around in this room, I mean, they're maniacs.
They're just wild, but you put all of that together and suddenly it is beautiful.
It is a smooth distribution of air pressure through the room.
I had an experience with this many years ago.
One of the worst things that ever happened to me when I was in my father years with children,
one of my kids said, Daddy, I want to join the orchestra at school.
Okay, what do you want to learn?
She said something horrible. She said, I want to learn the violin.
Do you know what it sounds like, a violin, in the hands of someone who doesn't know how to play a violin?
I mean, it was terrible.
And then she said, we're going to have our Christmas concert.
We're all going to go and we're going to play our violins, our violas, our cellos all together for Christmas presentation.
I thought, Jesus, take me now.
So I went in there and there they were all seated.
God, this is going to be rough.
And then the conductor came in, tap, tap, tap.
And I thought, here it comes.
And it was gorgeous. It was incredible.
Why?
Because their mistakes were canceling each other out.
And you got the sound of the herd.
We do that all the time.
Choirs, I mean, the individual members are horrible, but when they sing together, it sounds really nice.
That's part of why you sound so damn good when you're singing with the radio in your car,
because your mistakes are being canceled out by whoever's singing on the radio.
This whole thing is a principle of the universe.
And in our world of finance, it is a crucial part of investment strategy.
Diversify your portfolio.
So you are no longer facing the standalone risk of individual stocks.
You're facing the risk of the entire portfolio.
With all the stocks canceling each other out to some extent in their wildest moves,
you get this risk pooling phenomenon occur.
But it goes beyond that.
We have to take it beyond that.
We have to say, OK, how do we measure risk?
And how do we measure those other parts of risk?
Where is the most likely event to occur?
How likely is the most likely event to occur? And all that kind of fun stuff.
That's where we get into some metrics.
The first measure we will need is a measure of central tendency.
In other words, where is the most likely place that it's going to land?
Now, it may never land there, but we know that most of them land around that place.
So we have central tendency measures.
Now, the one that is the most likely for you to have heard of is the average.
We'd mark that by an X with a little bar symbol over it.
X bar.
And the one that you would get on a calculator, and this is just fancy notation,
so you can have it in your notes as you do cool things.
The X bar equals 1 over N times the sum from the first to the last data point
of each data point added up.
That's all.
Add them up and divide by the number of data points.
This is a fancy way of showing that.
Now, more generally, this assumes that each data point has the same probability
of occurrence, 1 over N.
The more general formula would be this one.
X bar equals the sum from I equals 1 to N of each data point times the probability
that that data point is going to happen.
Don't panic.
It's actually in Excel.
It's really easy.
And I'll show you a formula, an Excel formula that really helps with this
but also helps with other things, too.
Let me show you something.
I'm going to pull up an Excel sheet here.
Give me a second here.
Whoops.
Didn't mean to do that.
I'm going to see if I can drop the Excel sheet here and follow along with me.
I'll put this up in your files.
It'll be in spreadsheets classroom examples.
So it won't be in the main spreadsheet file.
It'll be in a subfolder because this isn't really a template.
It's just a demonstration kind of event for you.
Give me a second to draw this up.
Come on.
Give me a little extra time here.
I don't want to do it that way.
That's kind of weird.
I kind of wanted to do this.
It's opening up in SharePoint.
I really didn't want to do that.
Save as a copy.
Now let me get it the right way up here.
Come on.
There it is.
In Excel.
Bear with me one moment here while I kill off something else here.
I pulled it up as a SharePoint kind of document.
That's not what I wanted.
I wanted just a plain old Excel document that you could use.
Now let me show you something here.
And as I said, follow along with me.
Here's some data.
I've got five possible outcomes on a stock with probabilities for each
of the possible outcomes.
So in other words, this stock could go down 12%.
There's a probability of.1 that it would do that.
The stock could go down 3%.
And there's a probability of.15 that it would do that.
I've got a stock that could, the stock could also go up 5%.
There's a probability of.3 that it would do that.
And then there's a 10%.
It could go up 10%.
There's a probability of.25 of that.
And it could go up, or it could go up 14%.
There's a probability of.2 of that.
Now I do understand that you had a course where you learned basic statistics.
I'm going to assume that you have almost forgotten that.
So I'm going to try to redo, I'm not going to say, well, you all know that.
I'm going to say, OK, we'll go back through this.
Now the first thing that we're going to want to do is the expected return, that X bar.
Now I don't know if I've shown you this.
I don't think I've shown you this.
You may know it from someplace else.
But what we're going to do is we're going to take that second formula,
each of the data points times its probability, and add those up.
So it would be negative 12% times.1 plus negative 3% times.15, et cetera.
There's a fast way to do it in Excel.
And like I said, I don't think I've shown it to you, but maybe I did.
If you type in, oh, my expected return cell, B8, equals sum product,
S-U-M-P-R-O-D-U-C-T, open parenthesis, you tell it the first array,
which would be the return, A2 through A6, comma, the second array, B2 through B6,
then close the parentheses, it'll do that.
Isn't that kind of cool?
I mean, trust me, we do some problems in here where getting that done would be just a real pain in the butt.
But this way, it does the individual multiplications and then adds a whole mess up.
And there's a place in the course later, before I used Excel,
it took half of a lecture to just do a simple problem.
This, just boom, it's there.
So that is the average, what we typically call the average.
However, there is actually another measure of central tendency that is called the median.
The median, the formula for it, and traditionally I have seen, usually,
I don't even know if there's a standard notation, I usually just use an X with a little hat over it.
And the formula for it is actually ridiculously complicated, but the idea is simple.
The median is the place where half of the data is below that value and half of the data is above that value.
It's a center point.
And what's really important is looking at the median and the average, the mean, as we call it, together.
Because you see, if the data is perfectly symmetric, the average and the center point are the same.
But if they're not the same...let me show you something here.
Like a test that I give.
I could have most of the students, 50% of them are down in here, but those few acers of the exam,
the ones who did really well, they're going to pull the average up.
Even though realistically half the people didn't do better than down here, these are going to affect the average.
They won't do anything to the median, because the actual values don't matter.
It's just how many are above halfway, how many are below halfway.
So the median, and you'll see this later in a very clear example, but this is actually...I could say,
well yeah, the average on this exam was pretty good.
Well that was just because you had a small group of people who aced it.
But the typical student didn't.
And so comparing the mean and the median is something that is useful for a lot of purposes, including in finance.
Or the other one.
This is skewedness.
This is a measure called skewedness.
But here's another one on the other side.
You could have some really low performers on an exam that dragged the average down.
A lot of people did great, but if I look at the average, it's being affected by these people who barely even were there for the exam.
So that's one of those important things that we should, as teachers, be paying a lot of attention to.
Don't look at the average.
As a matter of fact, typically when I'm looking at exams, I don't even look at the average.
I look at the median.
Half of the students were above the score, half were below the score.
Because for me, that's what matters.
Because if I look at the average, I'm seeing the data being skewed one way or the other by the performance of a few people on it.
So that's that second one.
And I call this the median is the halfway point.
Halfway value.
Now the next part of this
would be the metrics of dispersion.
Or spread, as it were.
Overall in higher education, the one that we look at is the standard deviation.
The standard deviation.
This represents a lot more risk than this one does.
The one that's flatter and lays out.
And that's what standard deviation measures.
How clustered, how tight the distribution is.
And the mathematics of that one are a real joy.
I equals one to N, the sigma, is the square root of I equals one to N of each data point minus the average squared.
All divided by N minus one.
I don't have to tell you, that one was a real pain in the butt.
Before, if you do it by hand.
I want to caution something about Excel in this regard.
There is a standard deviation of the population.
That would be, you would divide by N.
The standard deviation of a sample, which is what we almost always will have, we won't have the whole population, is divided by N minus one.
Now let me do something here.
What I'm going to do on this one is, I'm going to take each of the data points.
Open parenthesis equals open parenthesis on cell C2.
Open parenthesis.
I'm going to take the data point, in this case the first one, negative 12, minus the average, 5.15.
Now I'm going to make that an absolute reference.
So that I can drag it down and it won't drag down that expected value.
Then I'm going to take the square of it.
That's enough for that. That's as much fun as I want to do.
But then I'm going to multiply the result times the probability, because they're not all one over, it's not each one has one over N minus one.
In this case, and you have a problem or two like this, and you can try to use this sheet to work with it, times the probability of the occurrence of that outcome.
Okay?
And then I'm going to drag that result down.
So the standard deviation would be equal to each of those, oops, the sum of those, whoa, I've got to stop here.
The square root SQRT of the sum, this is a pain, of all those little squared minus squared things.
And then divide the result by N minus one, which in this case would be five points minus one.
I could do that as a count, but I won't. Just save myself some time here.
And that's your standard deviation. You have a couple problems in the homework that asks you to do this.
If you can try to use this model, it shouldn't be too much of a pain for you to do it.
You just have to make sure that you use that each data point minus the average, square it.
And then times it by the probability of each outcome, unless you don't have probabilities, and then you just divide the whole mess by N minus one.
Matter of fact, I don't think I even need to use the N minus one here. No, I don't.
That's why it was off. Yeah, it should be seven point eight one. I apologize. You don't need to use the N minus one if you've times by each probability.
You use the N minus one if you don't have any probabilities given. Apologies for that.
Now here's a problem. There's a problem with this standard deviation.
It would be completely dependent upon the size of the returns.
So I could look at the standard deviation of this stock, and I couldn't compare that to the standard deviation of another stock, whose returns could be very different.
We have a measure, and I don't recall, you'll have to tell me, where we can standardize, as it were, the standard deviation.
It's called the coefficient of variation.
The CV. Now the coefficient of variation is really simple. All you do is take the standard deviation divided by the average.
That takes out, that number is not a percent, it's actually just a pure number.
And don't let Excel tell you it's a percent. It'll see you calculating using these, and it'll give you an answer, and it'll say this is a percent.
You say no, just turn that into a plain old number by hitting the comma up there.
So in this case, I think I've already formatted it, equals the standard deviation, seven point eight one, divided by the expected return, five point one five.
And there is a CV. It should have no units. That means that it is a pure number that you can compare to other coefficients of variation.
You can't really compare the standard deviations. That's why the coefficient of variation is a much better measure of spread.
Because in this case you could compare the returns to a penny stock, the standard deviation of returns to a penny stock, to the standard deviations of an S&P 500 stock.
They'd be all in the same pure number units.
But if you were to use just standard deviation, the standard deviation of the returns to an S&P 500 stock would be miles larger than the standard deviation of a penny stock bouncing around.
But with a CV, you don't have that problem anymore.
There is one other...oh, let me do something. I'm going to stick in something here. No, it really doesn't mean anything.
The median is just the middle number in a run of ordered numbers.
In this case, the middle number would be the third number of five numbers. So the median return is five percent.
You can also do it here too. Watch. I'm going to insert a row here.
median. Excel will calculate a median too.
It doesn't even have to have the numbers in order for Excel to do it.
Equals median, and you just tell it your array, A2 through A6. It should say five.
Now, that's for an odd number. If you have an even number of data points, you take the one below and one above the halfway point and you take their average.
And Excel will do that for you too.
Let me show you something here before I go on. If you want to try to write down the steps.
Excel has all these hidden Easter eggs as we call them.
Excel has a special, what's called, analytics tool pack or analysis tool pack.
It's not automatically in Excel, but you can insert it into Excel.
And it will, you can just show it data and say, and that analyzes it, and it will pour out these statistics and a pile of other stuff too.
If you want to use it. And it's really nice if you've got a bunch of data and you just want to see what the statistics are on it.
It'll do it. Let me show you how to get to it.
Go file on the menu bar down to options. Clear down at the bottom, options.
You go in, you're going to get an Excel options menu. On the left side, go down and click on add ins.
So file, options, add ins. See that first one? It's got tons of these little packs.
The one you would want is the very top one, analysis tool pack.
Now don't just say okay, go down here and say go. On the left side down there at the bottom, say go.
And then it'll say okay, what do you want? Because there are a couple of different ones.
Click the check mark on analysis tool pack. There are four different check boxes. Check the first one.
And then say okay. And then that will show up on your menu bar.
And you can see all kinds of different stuff. You can choose what you want.
It'll even find data sets for you and stuff like that. It's a really nice tool pack.
Now in some of your versions of Excel 365, it will stay there. You exit Excel, come back, it'll be there.
In others, you have to invoke it. What I did there, every time you open Excel, it's just kind of a pain in the butt.
I don't know why it's not always there once you choose it. But if you come back into Excel and you don't see your add ins,
you have to go back through that process and get it back in there for your session.
Now any analysis it does, it'll stay there even when you close. But if you want to do new stuff, you'd have to bring it back in again.
But like I said, some Excel, some of you probably will have it once you bring it in. It'll be there whenever you open Excel.
Other ones, it's just gone. For example, on this computer, I'm pretty sure that it, well maybe they've changed it.
But every time I do this analysis tool pack, I have to put it back in if I've closed Excel.
But anyway, there's that. It's useful to know.
What was I, oh I know what I was thinking. Okay. So there are the basic outlines.
The problem is, and of course I erased it, standalone risk and portfolio risk.
Let me take us back to the beginning. Standalone risk is that standard deviation.
It's just measuring the returns variation of that stock. That's all it's doing.
Portfolio risk for a given company is beta.
And I'll get into that one on Monday. But let me finish what I'm doing here.
I'm going to bring up another spreadsheet to do this one. New spreadsheet.
Now this is also on the one that it will be, it's actually in the other one, but I want to show you how to do it from the beginning.
Let's use some real world data. As I said, the world is just being driven by data.
The problem is that big data sets, what we need to do modeling and all that is usually we are going to need data sets that have anywhere
from a thousand data points up to hundreds of millions of data points.
Excel can usually handle it, but it costs money to go to those. They are subscriptions.
You can subscribe and then you can get access to the data, but to have access to decent data on your own, that's a little without paying for it.
Now the Federal Reserve has tons of data, which is free, and there are a few other sites where you can get some historical data.
But let me show you one place, it's been around for a long time.
Early in the life of this website, we were even using it in the PhD programs to get some data.
But let me show you the place. It's called runstockone.com.
Now here's what's neat about this site. They will give you year by year returns for a whole lot of different things from 1975 up to the most recent year,
in this case 2023. You can get, here's one, the Dow Jones Industrial Average.
Year by year, the one we'll be interested in are the returns, the percentage returns.
But there it is. Go back and I can get the Standard & Poor's returns year by year by year.
Go back and I can get the NASDAQ returns year by year.
In fact, you can get world market indexes. You can get the London Exchange, the Han Exchange, Swiss Borth.
They're all there for you. Oh, all these different nations, you can get theirs if you wanted to.
This is great for term papers, research papers. You look like a hero.
Where the hell did you get all those numbers for that weird exchange on some place in the middle of nowhere?
In fact, you can get the year by year returns to stocks of companies.
You just first letter, then you hunt down that company, and you can get its returns on the stock year by year.
And it's adjusted for stock splits and all that kind of stuff. In other words, it's cleaned data.
So here's what I'm going to do. I'm going to show you a stupid pet trick in Excel.
First things first, let's say I've got this Excel sheet and I want the returns from 1975 to 2023 for the Dow.
The first thing I'll do is I will go over here and I'll go to the DJIA in one stock one dot com.
The next thing you want to do is click on the URL, the web address. Control V, copy it.
Now watch what I'm going to do. I've got, in other words, what I've gotten here. Oh, will you stop that?
Let me try that again. Control V. I got the web address of this data.
Now in Excel, here's what I'm going to do. Data, quit.
Now under the data tab, I'm going to say get data.
Down to from other resource, from other source, and I'm going to choose from the web.
And it's going to ask me, what is the web address of this data set? Control V.
Well, let's try that again. Control C, then go back over here. Control V. Go here.
And you say OK. It's going to spin around for a while. I think I'm running out of room here.
OK. Why is it having a hard time? Try that again.
Did I key in the URL wrong? Control C. OK. Control V. Oops. OK.
And now it's going to spin around. I must have done something wrong. OK.
And you say, oh, it's just saying I found it. And you say connect.
And then it's going to link right to that database. Now here's the problem.
A lot of web pages are built on tables. The menu you see at the top, drop down, and all that, that's a table.
And Excel is going to say, I'm seeing a couple of different tables here.
In most cases, it won't be the first table that it finds. That's the menu thing.
Table 1. Just click on it once. And it's showing you, well, that's table 1. Is that what you want? Oh, that sure is. Load it.
Looky there. There's that data in Excel now. All of it.
Well, that was fun. I'm going to call that one Dow 30.
Now let's go and make a new sheet. I'm going to go back over here to the One Stock One, back up, and I'm going to look at the S&P 500.
And I'm going to copy its web address, Control C. I'm going to go back here to Excel, do the same trick again.
Data, on this new sheet, data, get a data set from another resource from the web.
And I'm going to give it the URL, and I'm still going to say, go find it.
And this time, table 1, is that what you want? Sure is. Load it.
And there's the S&P 500 data. I'll give that S&P 500.
We're going to do it one more time. Sheet. I'm going to sheet, a new sheet, and I'm going to pull the same stunt, go over here to OneStockOne.com.
I'm going to back up and then choose the NASD NASDAQ.
And I'm going to copy its URL, and I do know that this, we'll do this a bunch of times so that you get the hang of this because this is really a good skill to have.
Yeah, I'm going to copy that one. Copy.
And then I'm going to go back over here to Excel and do the whole thing one more time. Data, get data from other source, from the web.
And this time I'm going to tell it that. Okay.
Let's look at table 1, is that what you want? Sure is. Load it.
And it's got that data set.
So you have just, now I can kill off everything else, you have just put in hundreds of data points just by telling Excel where the data was.
Now watch what I'm going to do here.
First thing I'm going to do is, let me make this a little bigger.
Dow 30.
I'm going to highlight all of these, you don't need to know how to do this.
Oh, I forgot to call this one the NASDAQ.
So the first thing I want to do is I'm going to highlight all of these.
And right here I'm going to write the mean, the average, the median, and the, if I had to do that.
Oh well, never mind. Mean, median, standard deviation, and the coefficient, let's try that one more time.
Median and coefficient of variation, and standard deviation, and the coefficient of variation.
And I'll just do one, the Dow right now.
All you do is just say equals average of this data right here, and there's your mean.
Your median equals the median of that data array.
That right there.
Standard deviation.
Be careful with this one.
STDEV is the one that will divide by N minus one. Equals STDEV.
Of that data.
And then finally, CV.
Huh, I wonder why it's saying that.
ST, oh I see, STDEV.
And the coefficient of variation equals the standard deviation divided by the average.
And I will do the rest of this on Monday, but for right now you have a very short quiz.
It should be available to you.
Wait, give me just a second, I'll have to fix it up for you.
You have a very short quiz to take.
And then once you're finished with that, that's all I have for you today.
I will thank you.