Hire best homework helpers for online homework help 24/7. Are you looking for online homework help? Try our excellent homework help who can help you get A+ grade in your assignment.Order my paper
How to collect, organize and analyze a very large dataset• Show how the Central Limit Theorem works using real-world data • Become comfortable using some more advanced Excel commands
ECON 101: Statistics for Economics
Lab Assignment 3
Dates: October 25th, October 28th or October 29th
Due November 1st, 4th or 5th
The overall aim of our lab section this term is to increase your proficiency in Microsoft Excel and data
analysis. By the end of the term, you should feel comfortable taking on any workplace assignments that
involves data analysis through Excel. The second lab aims to teach you:
How to collect, organize and analyze a very large dataset
Show how the Central Limit Theorem works using real-world data
Become comfortable using some more advanced Excel commands
We are going to get a new dataset this week. Similar to what we did in class a few weeks ago, let’s go
get data on the stock market. We will analyze the Standard & Poor’s 500 Index (S&P 500). The S&P 500
is a weighted-average of the value of the 500 largest stocks on the New York Stock Exchange. Historical
data on the S&P 500 can be gathered at Yahoo!: https://finance.yahoo.com/quote/%5EGSPC. Type in
the site into the address bar.
1. When you reach the webpage for the S&P 500, you will see the real-time value of the index.
Click on the “Historical Data” tab.
2. We want to get data for the entire time period available. Click on the date under “Time Period”
and a range of possible dates can be chosen. Click on “MAX”, then “Done” and finally “Apply”.
Once the screen reloads, you can click “Download Data” and an Excel spreadsheet should
automatically download. Open up the file and see all the data you have gathered!
3. The dataset shows the Date, Open, High, Low, Close, Volume and Adjusted Closing Price. For this
exercise we will be interested in the difference between the opening and closing prices. Because
of that, we can delete the “High”, “Low”, “Volume” and “Adj Close” columns by holding down
the “Ctrl” button and then clicking on columns C, D, F and G, which highlights the columns. Right
click the mouse and click “Delete”. The dataset should now look like this:
4. The data needs to be cleaned a little bit more. If you scroll down to approximately row 14500
(yes, there is this much data!), you will notice that before 1962, only one price was reported for
the index. We will not be able to calculate the daily change before 1962, so we need to delete
these rows. The easiest way to do this is to click the number associated with the last day of
1961, highlighting the entire row. Then hold down “Ctrl” and “Shift” at the same time and press
the down arrow. This highlights all the rows below 1962. Press “Delete” on the keyboard and all
the highlighted rows will be deleted.
5. We can figure out the daily change in price by calculating it in Excel. In cell D1, name the variable
we will create something like “Daily Change”. In cell D2, we can calculate the percentage change
as 100 x (Close Price – Open Price)/Open Price. To do this, type “=100*(C2-B2)/B2”. This can also
be done by clicking the cells you are using at the appropriate point in the equation.
6. After pressing enter, the percentage change will be calculated for the first cell (D2). In order to
have all the cells calculated, double click on the bottom right corner of cell D2. This will fill in the
calculation for all the cells in the row!
7. Calculate the Mean and Standard Deviation in Columns E and F. Assume we have the population
and use the commands “=average()” and “=stdev.p()” to calculate the statistics.
8. Now create a histogram of the daily change by highlighting the data in row D. Do this by clicking
on cell D1, holding down “Ctrl” and “Shift”, then pressing the down arrow so that all the data is
highlighted. Once it is highlighted, click on the “Insert” tab, then
, which is the histogram
creator, click the histogram on the left and a histogram will pop up.
9. The histogram will appear in the worksheet. Let’s move this to its own sheet. Right click on the
chart, click “Move Chart”, click the circle next to “New sheet” and rename Chart1, “Population
10. You will probably notice that the histogram looks like a normal distribution, but the tails of the
distribution are very wide. We can tighten up the histogram by double-clicking the values in the
X-axis. When you do this, format options will appear on the right side. Click the boxes next to
“Overflow bin” and “Underflow bin”. This will place any values that are below -3.0 or above 3.0
into the same bin. Double click “Chart Title” and rename the chart “S&P 500 Daily Returns,
1962-2018”. Your final histogram should look like this:
Construct Sample Distributions
1. Now that you have a population to compare to, you will construct sample distributions using the
random number generator. Get back to your spreadsheet by clicking on the tab “table” at the
bottom left of the chart. Now create two new columns by right-clicking on the E column and
then clicking insert…do this twice so that your spreadsheet now looks like this:
2. Title the E column “Random”. In cell E2, type “=rand()” and click enter. This will return a random
number between 0 and 1. Double-click the bottom right corner to randomly assign a number to
each date. You may notice after you double-clicked the corner, the value in E2 changed. In order
to keep the random values from changing, highlight the entire row by clicking E, right click the
mouse, click copy, right click cell E1 and then under “Paste”, click on the clipboard with 123
underneath it to paste the values of the random numbers. This will keep the numbers fixed.
3. We will randomly choose dates based on the random number assigned to it. Specifically, click
cell E1, then click sort and filter in upper right hand corner and sort A to Z. Now your sheet will
have a random date in the first cell:
4. Highlight the first 50 values, D2 to D51, and create a new histogram. Move it to a new sheet and
rename it to Sample50. Change the chart title to something like “S&P 500 Daily Returns, 19622018, n=50”. Explain the shape of the histogram relative to the population distribution. Does it
look normal, is it skewed, where is the average, what is the standard deviation?
5. Create three more sample histograms, one with n=100, n=1000 and n=2000. Calculate the
mean, standard deviation and skewness for each sample. Explain the differences between each
histogram and the value from increasing the number of observations in the sample. In other
words, does increasing from 50 to 100 make the distribution look more like the population?
What about 1000 to 2000?
6. Turn in the five histograms you create. You will have two paragraphs to explain the charts and
answer the questions in points 4 and 5. The entire assignment should be done on two sheets of
paper (one double-sided sheet). Turn in your assignment in lab next week.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more