So, one thing that I want to show you how to do though, very briefly, is how do we build these histograms. Then 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 of those borderline observations. Do they go in the bin to the left or the right on the histogram? So, 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, and we'll go over to Verizon, the ticker 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. Right? So, on Google Finance, you'll see under the company information, we can click on the historical prices, and this will pull for us. What it's displaying right now is the very recent information. What I'm going to do is I'm going to change the window, so let's look at, say, the first six months of 2015. So, it's starting, you know, beginning of January 1, 2015, and we'll go through the end of June. All right. So, we'll update that information and then you'll see an export option to download the spreadsheet. So, we're going to click on that, and it should save the file for you. And we're just gonna launch that file in Excel. I'm going to maximize that window and zoom in. All right. So, we have the date of the observations, open price, the high, low, the close, and the volume traded. We're going to create one new column. We're going to create the percentage change on that day. So, in cell G1, I'm just gonna type that in. So, in cell G1 we're gonna type that in as a label. The percentage change, and we're gonna calculate that. So, hit the equal sign, open parenthesis, what's our closing price minus the opening price divided by that opening price, and that's gonna give us the percentage change. I'm going to click on the percent button and to change that so it's displayed as a percentage. And I'm gonna click on this increase decimal button so that we get a little bit more detail. All right. So, on June 30th had a percentage change of -1.12 percent. If I hover over the lower part of cell G2 until I get that crosshairs. I can double-click on that, and that's gonna 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 gonna 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 gonna do is actually use the pivot table functionality that we looked up before and we're gonna use that to construct our histogram. All right. So, I'm going to click on INSERT, and the first button up there is to gonna 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 OK, and we've got the pivot table, at least the skeleton of that. All right. Well, what fields do we want displayed? We're going to focus on the percentage change piece. And what I'm gonna do first is I'm gonna drag that percentage change variable into the values field of our pivot table builder. And one thing that we're gonna do here is, you know, what I'm interested in for constructing the histogram are the frequency of the observations. So we're gonna make one change here. I don't want to look at the sum, so I'm gonna go into the value field settings, and I'm going to summarize the data using a Count. All right. 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 gonna click and drag that percentage change column into the rows area of the pivot table builder. All 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 -1.67 percent, what the Count is telling us is that we have one observation in that range. We have one observation for -.67 percent. We have one observation for, you know, -.44 percent. 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 gonna do is I'm going to hover over that first column, the row label, and I'm gonna right-click, and what we're going to 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 lets us do is chunk 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 at, so what's our starting value? Let's put in, let's say, the smallest observation we have is -1.67 percent. I'll put in -.0175, so -1.75 percent. And as a maximum, we go all the way up to 2.2 percent. I'll put in.0225 percent. And then, how big in increments do we want it to move in. We'll keep in mind that we're dealing with percentages, so let's go in fairly granular intervals in this case, so let's go.0005, 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. All right. OK, so we've got our starting point, 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. So, this one, it looks like 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 -1.7 percent to -1.65 percent, I only have one observation. If we jumped further down my column, though, from -.45 percent to -.4 percent, I've got four observations. So we've just grouped 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. What we're gonna click on next is the pivot chart option. And this is gonna 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 gonna 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 gonna do a couple of things just to format this and make it a little easier for us to read. I'm gonna click on that legend that they've inserted. I'm just gonna delete that because we're only dealing with one variable, we don't need that right now. I'm also gonna delete the title on this just so that it fills the screen a little bit better. All right. So, what we're seeing here is a sense for how much dispersion we have in the graph. If we right click on our graph, and specifically let's do this on the bars, I'm gonna click on the option to format the data series, so format the way in which this data's displayed. And the option that we're looking for is the Gap Width: how wide do we want these bars to be separated? And I'm just gonna shrink that down so that the bars are not separated. All right. So, this is what our histogram generally looks like in terms of how many observations do we have along the y-axis, the range of values along the x-axis. Now, one thing that we could do if we wanted to say, "Well, what fraction of my observations fall into a given range?" Well, let's change the way in which we're looking at the data by clicking on the value field settings. And instead of performing no observation, no calculation on the values, let's have it display as a percentage of the grand total. So, out of all the observations we have, out of that 124 observations, let's report it as a percentage of the total observations rather than as one observation, two observations, three observations, and so forth. All right. And you'll see when we make that change. So, now it's telling us what fraction of the observations fall into each of these bins. Because the pivot table and the chart that we produced are linked, you'll see that the y-axis has also been updated to reflect the percentage of observations that fall into a particular bin. Now, the coarser we make 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 for how much variation there is, where do the observations generally fall. Can also be used to get a sense for, is it a bimodal distribution, or is it what looks to resemble a normal bell curve distribution? And as we'll see, not all of our observations, not everything that we're working with in terms of the data is going to conform to that normal distribution. So, it's important that we look at our data, understand what we're working with, understand what assumptions are going to be appropriate when we're conducting our analysis.
