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. So on Google Finance you'll see under the company information we can click on the historical prices. And this will pull up 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 let's start in the beginning of January 1st, 2015, and we'll go through the end of June. So we'll update that information and then you'll see in 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 going to launch that file in Excel. I'm going to maximize that window and zoom in. So what 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 we going to type that in as a label, the percentage change. And we're going to calculate that, so we hit the equal sign, open parenthesis, what's our closing price minus the opening price divided by that opening price. And that's going to give us the percentage change. I'm going to click on the percent button to change that so it's displayed as a percentage. 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. 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? 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. 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. I'm going to click on that legend that they've inserted. I'm just going to delete that because we're only dealing with one variable. We don't need that right now. I'm also going to delete the title on this just to let it fill 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. We're going to click on the option to format the data series. So format the way in which this data is displayed. And the option that we're looking for the gap width. How wide do we want these bars to be separated? And I'm just going to shrink that down so that the bars are not separated, 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 want 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 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 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 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.
