BUS520 Pace University Business Analytics and Decision Making Go to finance.yahoo.com and download the complete Nasdaq historical monthly prices from Janua
BUS520 Pace University Business Analytics and Decision Making Go to finance.yahoo.com and download the complete Nasdaq historical monthly prices from January 1, 1971 to January 1, 2018 into an Excel worksheet. Just click monthly and the first two dates and adjusted closing values should be: Feb 1 1971 (101.34), Mar 1 (105.97), etc. after sorting the file in ascending date order. Save the file as an Excel workbook. From the adjusted closing values of the index in the last column, compute a new column of monthly returns. Note that the first return value will be in the new column cell in the March 1, 1971 row, since it measures the return for the first (partial) month. Please check the attachment below for more information MBA 646
Professor Carter
Assignment 2
Please do the following 4 problems.
Compile a Word report of your solutions, with a header containing the names of participating
team members, and including pictures of relevant tables or graphs from your Excel workbook.
One person in the team uploads the two files to Blackboard before the due time.
Problem 4
Go to finance.yahoo.com and download the complete Nasdaq historical monthly prices from
January 1, 1971 to January 1, 2018 into an Excel worksheet. Just click monthly and the first
two dates and adjusted closing values should be: Feb 1 1971 (101.34), Mar 1 (105.97), etc. after
sorting the file in ascending date order. Save the file as an Excel workbook. From the adjusted
closing values of the index in the last column, compute a new column of monthly returns. Note
that the first return value will be in the new column cell in the March 1, 1971 row, since it
measures the return for the first (partial) month. The formula for computing that first return value
is:
??????1 ?????? ?????????? ? ??????1 ?????? ??????????
??????1 ?????? ??????????
a) Construct a labeled time series graph of the monthly adjusted closing index values from
February 1, 1971 until January 1, 2018. Date is along the x-axis.
b) Construct a histogram of monthly adjusted closing values (specifying 15 bins). Explain
why this graph is positively skewed.
c) For the period Mar 1 (first return observation) until January 1, 2018 (inclusive) construct
a histogram of monthly returns with proper labels, again with 15 bins. Describe the shape
of the distribution. (Important hint: If your Return column has an empty cell below the
heading, move the heading to directly above the first return value before naming a new
StatTools dataset for Return or else 0 will be considered a return value to include.)
d) In your worksheet and the picture pasted in your Word document, display 4 decimal
places for all computed and bin values.
Compute the mean and standard deviation of returns and construct a frequency table
showing the frequency of return values in the categories shown in the template below.
(Right click on the template and open the underlying Excel file. Be sure to save the
opened worksheet with a new file name before closing the open worksheet in Word).
Notes: Use. the frequency array function to fill in the table frequencies. To do this you
need to specify the bins.
I treated the data as a population rather than a sample, so used the Excel formula for the
standard deviation of a population.
Finally, compute the percentage of returns falling within one, two and three standard
deviations of the mean return to compare them with the empirical rules percentages.
Paste a copy of your finished worksheet in your document and comment on the
differences.
1
MBA 646
Professor Carter
Assignment 2
Checking empirical rules for returns
Summary measures of returns
Mean
Stdev
Note: the bins are upper limits of returns that fit in each category
Compute bin values from the mean and standard deviation
Category
Bins
More than 3 stdevs below mean
Between 2 and 3 stdevs below mean
Between 1 and 2 stdevs below mean
Between mean and 1 stdev below mean
Between mean and 1 stdev above mean
Between 1 and 2 stdevs above mean
Between 2 and 3 stdevs above mean
More than 3 stdevs above mean
Total
Percentages within k stdevs of mean
k
Actual percent
Expected percent by empirical rule
Frequency Percent
The sum of these two percents is the total
percent within 1 standard deviation of the mean
1
2
3
2
MBA 646
Professor Carter
Assignment 2
Please do the following 4 problems.
Compile a Word report of your solutions, with a header containing the names of participating
team members, and including pictures of relevant tables or graphs from your Excel workbook.
Problem 4
Go to finance.yahoo.com and download the complete Nasdaq historical monthly prices from
January 1, 1971 to January 1, 2018 into an Excel worksheet. Just click monthly and the first
two dates and adjusted closing values should be: Feb 1 1971 (101.34), Mar 1 (105.97), etc. after
sorting the file in ascending date order. Save the file as an Excel workbook. From the adjusted
closing values of the index in the last column, compute a new column of monthly returns. Note
that the first return value will be in the new column cell in the March 1, 1971 row, since it
measures the return for the first (partial) month. The formula for computing that first return value
is:
??????1 ?????? ?????????? ? ??????1 ?????? ??????????
??????1 ?????? ??????????
a) Construct a labeled time series graph of the monthly adjusted closing index values from
February 1, 1971 until January 1, 2018. Date is along the x-axis.
b) Construct a histogram of monthly adjusted closing values (specifying 15 bins). Explain
why this graph is positively skewed.
c) For the period Mar 1 (first return observation) until January 1, 2018 (inclusive) construct
a histogram of monthly returns with proper labels, again with 15 bins. Describe the shape
of the distribution. (Important hint: If your Return column has an empty cell below the
heading, move the heading to directly above the first return value before naming a new
StatTools dataset for Return or else 0 will be considered a return value to include.)
d) In your worksheet and the picture pasted in your Word document, display 4 decimal
places for all computed and bin values.
Compute the mean and standard deviation of returns and construct a frequency table
showing the frequency of return values in the categories shown in the template below.
(Right click on the template and open the underlying Excel file. Be sure to save the
opened worksheet with a new file name before closing the open worksheet in Word).
Notes: Use. the frequency array function to fill in the table frequencies. To do this you
need to specify the bins.
I treated the data as a population rather than a sample, so used the Excel formula for the
standard deviation of a population.
Finally, compute the percentage of returns falling within one, two and three standard
deviations of the mean return to compare them with the empirical rules percentages.
Paste a copy of your finished worksheet in your document and comment on the
differences.
1
MBA 646
Professor Carter
Assignment 2
Checking empirical rules for returns
Summary measures of returns
Mean
Stdev
Note: the bins are upper limits of returns that fit in each category
Compute bin values from the mean and standard deviation
Category
Bins
More than 3 stdevs below mean
Between 2 and 3 stdevs below mean
Between 1 and 2 stdevs below mean
Between mean and 1 stdev below mean
Between mean and 1 stdev above mean
Between 1 and 2 stdevs above mean
Between 2 and 3 stdevs above mean
More than 3 stdevs above mean
Total
Percentages within k stdevs of mean
k
Actual percent
Expected percent by empirical rule
Frequency Percent
The sum of these two percents is the total
percent within 1 standard deviation of the mean
1
2
3
2
Purchase answer to see full
attachment
