Histograms
•Organize data into groups by counting how
much data is in each group
•Groups Bins
•Frequency number of
observations
•Max = maximum value of the given data
•Min= minimum value of the given data
•Range=Max-Min
Histograms
• Bin width=(max-min)/# of bins
• Adjusted bin width=round up the bin width to
the higher value
Eg: if bin width is 5.4 , the adjusted bin width 6
if bin width is 0.028 , the adjusted bin width
0.03
• Number of bins to use- when dealing with a lot
of data, use between 8 and 16 bins
Histogram
Frequency
Re lative Frequency
Total of Frequencies
Example 1
• The Table gives the estimated gross
income in millions of dollars for the 40
highest paid entertainers in 1995 and
1996 combined.
1. Calculate the following for the data
a) Maximum b) Minimum c)Range
d)Bin Width e) Adjusted bin width
2. Create Table I
3. Create Histogram
Example 1- Raw data
130 31 43 44 44
51 59 50 77 33
25 46 90 40 30
32 40 56 33 50
63 75 35 74 42
31 44 28 59 36
63 44 28 48 171
33 28 36 150 42
This column has Table 1(First 3 bins) two columns
These
to entered
will generated
manually
automatically by excel
Bin limits Bin limits Frequency Relative
Frequency
Bin 1- (0,25] 25 25 1 0.025
Bin 2- (25,35] 35 35 11 0.275
Bin 3- (35,45] 45 45 11 0.275
Min/if you have decimals for min, a number higher than min
of raw data
Table 1(contd./Last 4 bins)
Bin limits Bin limits Frequency Relative
Frequency
Bin 13 –(135,145] 145 145 0 0
Bin 14- (145,155] 155 155 1 0.025
Bin 15- (155,165] 165 165 0 0
Bin 16- (165,175] 175 175 1 0.025
We select the number of bins such
that it captures the max of raw data
Creating Histogram
• Enter your Raw data into excel
• Enter your Bin limits into excel
• Tool->Data analysis->Histogram
• Input range- Enter the cells which contain the raw data
• Bin range- Enter the cells which contain bin
limits
• Output range- Enter the cell you want to have the output
• You should now get two columns generated by the histogram function (bin limits and frequency)
• Insert->chart->standard type->column->next
• Data range tab->enter data range(select the data created by excel-the two columns without the
labels)
• In the same window
• Series tab->enter values(select the data on frequency column-without the label)-> enter the
category (x)-axis labels(select the data on bin limits column-without the label)
• Now select the data series->right click->format data series->select options tab->make the gap
width to 0
• Now enter appropriate Title/name the axis (using chart options)
Project Focus
• You must save the data file you download from the yahoo website
as “.xls” (when you save it initially it will save the file as “.csv”)
• Create a new column of Ratios of Closes,
Where
current week adjusted clo sin g price
R
previous week adjusted clo sin g price
(gives us information about how two consecutive week’s prices
compare to each other/analyze stock’s volatility)
Example: Ratios of Closes=week 1 closing price/week 2 closing price
• Compute Max, Min, Average & Range of the Ratios of Closes
Stock
Probability, Mathematics, Option
Tests, Homework, Computers
on the project Pricing
How can histograms help us
price a stock option?
In the Excel file Option Data.xls,
which contains the historical records of Walt
Disney weekly closing prices, we computed
417 ratios of weekly adjusted closing prices.
This set of ratios contains the information that
will allow us to price an option.
As a first step, we will use the
Class Project computer tools of this section to analyze the
ratios.
(material
Option Data.xls
Histograms, Focus T C I
continues)
Stock
Probability, Mathematics, Option
Tests, Homework, Computers
on the project Pricing
Click here for information on
downloading stock data and on adjusted prices.
In the sheet Histogram of the Excel file Option Focus.xls we have
used the MIN, MAX and AVERAGE functions to find that the smallest
ratio is 0.7578, the largest is 1.1774, and the average of the 417 ratios is
1.0049. This shows a range of 1.1774 0.7578 = 0.4196. It is important to
understand the meaning of the ratios. The number 1.0019 shows that, on
average, the value of a share of Walt Disney stock rose by 0.19% per week
during the last 8 years.
Open the sheet Histogram to see how the following plot was
constructed.
Option Focus.xls
Histograms, Focus Class Project (material continues) T C I
Stock
Probability, Mathematics, Option
Tests, Homework, Computers
on the project Pricing
Ratios of Closing Prices
35%
30%
percentage
25%
20%
15%
10%
5%
0%
0.745 0.805 0.865 0.925 0.985 1.045 1.105 1.165
ratio
Option Focus.xls
Histograms, Focus Class Project (material continues) T C I