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

Â