ASSIGNMENT - Trading Range

project involves analyzing data from the Dow Jones data set.

1. Add a worksheet. Name the worksheet Trading Range. Copy the RowID column from the data page into a column on the new tab. Make a 2nd column called Trading Range. Compute this “Trading Range for the day” as the value of High minus Low for the row. Name the column as range Trading Range. Display the Descriptive Statistics for this column of data on the same sheet (max, min, mean, median, interquartile range, etc.). Be sure you have loaded the Analysis ToolPak. Make another column called Ascending which is the Trading Range column sorted in Ascending Order, and graph this new column with the RowID on the horizontal axis and the trading range value on the vertical axis.
2. Add worksheet #2: Name the worksheet HPQ. Using Data/Filter and Sort make a subset of the main data table for Symbol HPQ (Hewlett Packard) for the year 2000. Make a line chart of the closing prices for the year 2000 from Jan through Dec. Using only the period from 1 Jan 2000 to 1 Apr 2000 create a linear regression showing the predicted stock price at the end of December 2000. (hint: use the general regression equation on page 399 and the discussion on the following pages. Remember to count “trading days” and not “calendar days”.) Make a column that numbers the days in the data from 1 to 252 and use that as the horizontal axis, not the date column. You can use the Regression function in Data Analysis to determine the intercept and slope of the regression line. Slope is the “X Variable 1” coefficient. What is the difference between this regression-predicted value and the actual price at the end of December 2000? What is your analysis of linear regression in stock-prediction scenarios?
3. Add worksheet #3. Name it Biggest Losses. Have a column named 1985 Opening Price, one column named 2003 closing price. Compute the range of the stocks, then sort them to determine the company with the greatest loss. Pick one company whose share price declined. Do a linear regression on their share prices and extrapolate to 2012. What is the share price today of that company?
4. Add worksheet #4. Repeat the above process but title the worksheet Biggest Gains.