Histograms Organize data into groups by counting how
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
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 to entered manually Table 1(First 3 bins) These two columns will generated automatically by excel 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 Frequency Relative Frequency Bin 13 –(135, 145] 145 0 0 Bin 14 - (145, 155] 155 1 0. 025 Bin 15 - (155, 165] 165 0 0 Bin 16 - (165, 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 (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
on the project Stock Probability, Mathematics, Option Tests, Homework, Computers 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. Class Project Option Data. xls Histograms, Focus (material continues) As a first step, we will use the computer tools of this section to analyze the ratios. T C I
Stock Probability, Mathematics, Option Tests, Homework, Computers Pricing on the project 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. Histograms, Focus Option Focus. xls Class Project (material continues) T C I
on the project Histograms, Focus Option Focus. xls Class Project Stock Probability, Mathematics, Option Tests, Homework, Computers Pricing (material continues) T C I
- Slides: 12