0:04

All right, so one thing that I want to show you how to do though

very briefly is how do we build these histograms.

The height of the bars on the histogram reflecting how frequently

we're observing particular observations and then the horizontal axis or

the x-axis, are the different bins that we've chunked our data into.

So this is just giving you an outline for the steps that are involved.

We make the decision of how wide each of the bins is going to be.

Then we have to summarize our data,

figure out how many observations fall into a particular bin.

And then we make a decision off those borderline observations,

do they go into the bin to left or to the right on the histogram?

So what men do and follow along with me.

Go and launch your web browser and why don't we jump online and

go to whatever finance site you're comfortable with.

I'll walk through this using Google Finance but

we're going to pull some stock return information and

construct a histogram using Excel from that information.

Now if you have other data available to you to build a histogram from by all

means use that.

I'm using this just because it's readily available.

So again, we'll go over to Verizon.

The ticket symbol is V as in Victor, Z as in Zebra and

we'll pull up the stock quote information for the company.

And then once this launches,

what we're going to do next is download the historic prices.

3:06

And I'm going to click on this increased decimal button so

that we get a little bit more detail, right?

So on June 30th had a percentage change of minus 1.12%.

If I hover over the lower part of cell G2 until I get that

crosshairs I can double click on that and that's going to copy the formula all

the way down column G so long as I have observations in column F.

So that's a shortcut for copying down a formula.

You can also do that manually but

you're going to have to scroll through a lot of screens In order to do that.

All right, so now that I've calculated the percentage change,

how do I go about making the histogram?

Unfortunately Excel does not have a tool built into it, that is designed for

histograms.

So what we're going to do is actually use the pivot table functionality that we look

at before and we're going to use that to construct our histogram.

All right, so I'm going to click on Insert.

And the first button up there is going to be to create that pivot table.

And we're going to make sure that it captures column A all the way through

column G and we'll have it create that pivot table for us on a new worksheet.

So I'm just going to click on okay and

we've got the pivot table at least the skeleton of that.

4:23

All right, well what fields do we want to display?

We're going to focus on percentage change piece.

And what I'm going to do first is I'm going to drag that percentage change

variable into the values field of our pivot table builder.

And one thing that we're going to do here is, what I'm interested in for

constructing the histogram are the frequency of the observation.

So we are going to make one change here as I don't want to look at the sum so

I'm going to go into the value field settings and

I'm going to summarize the data using account, right?

4:58

So right now all it's telling us is that we have 124 different observations.

The next step though is that we're going to go back to our pivot table fields,

and we're going to click and drag that percentage change column

into the rows area of the pivot table builder, right?

And when you do that take a look at what happens.

We've now populated our pivot table where each

row corresponds to a different percentage change.

And so when we look at for example, the percentage change of negative 1.67%,

what the count is telling us is that we have one observation in that range.

We have one observation for minus 0.67%,

we have one observation for minus 0.44%.

So keep in mind that we're only seeing the first two decimal places,

there are a lot more decimal places.

So that's why we're generally seeing only a single observation at each

level of the observation.

Well with the histogram we have the choice of how wide those bins are.

So what we're going to do is I'm going to hover over that first column,

the row label and I'm going to right-click.

And what we're going to go scroll down to is the group option.

All right, so let's click on that group option and pull up the menu.

And what the group option let's us do is choke the data together.

So rather than looking at specific values,

it's going to allow us to look at a range of values.

So I'm going to start that out.

So what's our starting value?

Let's put in, let's say the smallest observation we have is negative 1.67%.

I'll put in negative 0.0175.

So negative 1.75% and as a maximum we go all the way up to 2.2%.

I'll put in 0.0 to 5%, and then how big in increments we want it to move in.

Well, keep in mind that we're dealing with percentages.

So let's go in fairly granular intervals in this case.

So let's go 0.005, just so that you can get a sense for,

just so that we can see how Excel is going to

construct the pivot tables for us, right?

Okay, so we've got our starting value, our ending value and then the intervals.

And if these intervals don't work we can always come back in and change

the intervals to something that creates a more meaningful histogram for us.

All right it so this one it looks my intervals may have been a little

bit on the small side but let's work with that to see what we're dealing with first.

So you'll see now that I have ranges.

So from negative 1.7% to negative 1.65% I only have one observation.

If we jumped further down my column though from negative 0.45% to negative

0.4% I've got four observations.

So we've just grouped up the observations together based on specific values.

Now this is one way of looking at the data in this tabular format.

Not the easiest to digest the degree of dispersion that we have or

the frequency of observations.

Where we're going to click on next is the Pivot Chart option.

8:13

And this is going to bring up a builder for

us and it's defaulted to saying let's look at it in the column format.

And so that's what we're going to go with.

And if we double click on that option

you'll see that the ranges of values that we've created are along the x axis.

And then along the y axis we have the number of observations.

I'm going to do a couple of things just to format this and

make it a little easier for us to read.

10:02

let's report it as a percentage of the total observations rather than as

one observations, two observations, three observations and so forth.

All right, and you will see that when we make that change, so

now it's telling us what fractions of the observations fall into each of these bins?

Because the pivot table and the chart that we produce are linked,

you'll see that the y-axis has also been updated to reflect

10:27

the percentage of observations that fall into a particular bin.

Now the courser we made these bins,

the less jagged this histogram is going to look.

But this is a good way of summarizing the data that's available to you.

And getting a sense how much variation there is,

where do the observations generally fall.

It can also be used to get a sense for is it a bimodal distribution or

is it what looks like to resemble a normal bell curve distribution.

And as we'll see not everything that we're