Histograms Histograms •Organize data into groups by

Document Sample
Histograms Histograms •Organize data into groups by Powered By Docstoc
					          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     

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:6
posted:12/26/2011
language:
pages:12