Doing Statistical Analysis in Excel
Doing Statistical Analysis
Excel provides a large number of statistical functions. These and the tools in the Analysis ToolPak cover most types of statistical analysis that you would need to do in finance and include in your models.
Many people look at functions as "black boxes." You input the arguments, and the functions provide the answers. In a way this view is true, but it is also dangerous - you may use a function or tool incorrectly or misinterpret the results. So take the time to know the underlying statistical theory before using any of these functions or tools.
We will be using will use the monthly return data for the S&P 500 from Feb 1950 to Feb 2009. In column C 1 have calculated the monthly ordinary (uncompounded) returns using formulas such as = (B2-B3)/B3*100 in C2 and in column D the monthly continuously compounded returns using formulas such as =LN(B2/B3)*100 in D2. For financial work we generally use continuously compounded returns, although for the purpose of this chapter either return will be fine.
Obviously, we can calculate monthly returns starting one month after the date of the first data point, that is, starting in March 1950.
Descriptive Statistics
For any data set you often want to calculate certain descriptive statistics such as mean, standard deviation, and median. The Analysis ToolPak offers a tool called Descriptive Statistics that calculates in one step many of the most common descriptive statistics for a data set.
Example 1: Refer to the excel file: “Doing Statistical Analysis.xlsx” SP 500 Monthly Returns Worksheet. Perform Descriptive Statistical Analysis. Show the 5th largest and smallest data values in the set also.
Data Analysis Data Analysis Descriptive Statistics. In the Input Range enter the range for returns using the pointing method. Under Output options select New Worksheet Ply. Further down select all the four boxes, enter 95% next to Confidence Level for Mean and 5 in each of the two boxes
Excel provides a large number of statistical functions. These and the tools in the Analysis ToolPak cover most types of statistical analysis that you would need to do in finance and include in your models.
Many people look at functions as "black boxes." You input the arguments, and the functions provide the answers. In a way this view is true, but it is also dangerous - you may use a function or tool incorrectly or misinterpret the results. So take the time to know the underlying statistical theory before using any of these functions or tools.
We will be using will use the monthly return data for the S&P 500 from Feb 1950 to Feb 2009. In column C 1 have calculated the monthly ordinary (uncompounded) returns using formulas such as = (B2-B3)/B3*100 in C2 and in column D the monthly continuously compounded returns using formulas such as =LN(B2/B3)*100 in D2. For financial work we generally use continuously compounded returns, although for the purpose of this chapter either return will be fine.
Obviously, we can calculate monthly returns starting one month after the date of the first data point, that is, starting in March 1950.
Descriptive Statistics
For any data set you often want to calculate certain descriptive statistics such as mean, standard deviation, and median. The Analysis ToolPak offers a tool called Descriptive Statistics that calculates in one step many of the most common descriptive statistics for a data set.
Example 1: Refer to the excel file: “Doing Statistical Analysis.xlsx” SP 500 Monthly Returns Worksheet. Perform Descriptive Statistical Analysis. Show the 5th largest and smallest data values in the set also.
Data Analysis Data Analysis Descriptive Statistics. In the Input Range enter the range for returns using the pointing method. Under Output options select New Worksheet Ply. Further down select all the four boxes, enter 95% next to Confidence Level for Mean and 5 in each of the two boxes