Question Data Exercise 3 This exercise is designed to as

04 Jun Question Data Exercise 3 This exercise is designed to as

Question
Data Exercise 3

This exercise is designed to assess your ability to manipulate, analyze, and present data in

accordance with Course Competencies #5: Locate and download appropriate

macroeconomic data, #6: Use Microsoft Excel to manipulate and analyze data, and #7

Graph macroeconomic data effectively.

Instructions:

? Put all of your work in one file;

? Write your name in the header;

? We need to see your work in Excel (i.e., formulas). Do not copy and paste numbers

from your original worksheet to a new worksheet. When submitting, make sure that

the cells in your worksheet show formulas.

? After you finish the assignment, name your file LastnameDE1.xls (or .xlsx,

depending on the version of Excel). Note that you do not have to write the .xls suffix

by yourself -Excel will do it automatically, and

? Upload it to the Data Exercise Section of the Blackboard.

Background:

The stock market is an example of a perfectly competitive market. It has a large number of

buyers and sellers (especially for large cap stocks), all common shares of any stock are identical

so there is no product differentiation, and there are no barriers to entry preventing buyers or

sellers from entering or leaving the market. In a perfectly competitive market, profits cannot

exceed the general level in the long run. The mechanism by which this comes about is that any

investor in the market who makes excess profits will attract other investors who will copy what

the above average investor does. More and more investors will join the bandwagon until the

excess profits are competed away.

Stock prices move as new information becomes available about a company’s prospects.

Economists believe that stock markets are “informationally” efficient. They gather all publicly

available information and make it available to investors. As a result, movements in stock prices

reflect new information, that is, they reflect “news”. Such news is unpredictable –we do not

even know if the next piece of news will be favorable or unfavorable to the stock. As a result the

next price of a stock will be equal to the current price of a stock plus a random disturbance. This

leads to the random walk model of stock prices:

Pt = ? + pt-1 + ut pt is the price of the stock at time t (“today) and pt-1 is the price one period

earlier (“yesterday”). ut is a random number. For example, you could

think of an urn filled with balls with numbers on them (such as the ones

used for playing pool). Today’s price is yesterday’s price plus the number

on a ball drawn randomly from the urn. The drawing is much like the

process of drawing lottery tickets. The numbers on the balls are assumed

to have a normal distribution with a mean of 0 and a variance of 1. A

normal distribution has most of its values close to the mean. So there may

be many balls with -½ or + ½ on them; a little fewer balls will have -1 or

+1 on them, and as the numbers move away from zero, the number of balls

with each number gets smaller and smaller. The constant ? is called a drift

factor and prevents the prices from bouncing back and forth around the

initial price. It is not a key part of the theory.

The random walk theory suggests that prices are unpredictable, and you cannot use an average of

previous values of the price to project the next value of the price. The best prediction of

tomorrows price is today’s price.

Assignment:

You are to

? Choose one of the 30 industrial stocks in the Dow Jones Industrial Average. Google

Dow Jones Industrial stocks. Note the ticker symbol of the stock you choose. (Do not

choose GE)

? Go to Yahoo/Finance and put the ticker symbol in the Get Quote window (on left hand

side). Click in Historic prices and change the beginning date to two years before the

ending date. Download two years of Historic Prices from Yahoo/Finance into an Excel

worksheet.

? Create variables Pt and Pt-1. Pt is the Adj. Close price which adjusts the price for exdividend

movements and stock splits. Make sure Pt-1is in the column to the left of Pt

? Draw a scatter plot chart with Pt-1 on the horizontal axis and Pt on the vertical axis.

? Insert a linear trend and get the equation. Below is the chart I got for GE prices.

y = 0.9943x + 0.1464

15

20

25

30

15 17 19 21 23 25 27 29

Current Price

Lagged Price

GE Daily Prices

Random Walk Model

? Select a 5 x 2 array. 5 rows, 2 columns. In the NW corner enter the equal sign and click

on the fx function symbol right above your worksheet. Select the statistical category in

the category box. Scroll down in the Select a function box until you get to LINEST and

select it. A Functions Arguments box opens up. In the Known y’s put the values of Pt

and in the known x’s put the values of Pt-1. Skip the constant line and enter TRUE in the

stats line. Do not hit OK. Hold the Shift and Ctrl buttons down and hit Enter. The array

will fill with a variety of statistics. Select the array, right click and select format cells.

Under the Number tab select Number and choose 3 decimal places. They are easier to

read.

The array I got with the GE data is as follows:

slope 0.994 0.146 intercept

stand

errs 0.004 0.095

R

Squared 0.991 0.247

Std err

est

F stat 56942.511 498.000 df

SSREg 3472.140 30.366 SSResid

I have labeled the statistics (which Excel doesn’t do). The first row contains the

estimated coefficients (the slope and intercept) of the line which you found. The second

row gives you their standard errors.

Construct a 95% confidence interval for the slope. This is equal to

(the estimated slope minus 1.96 * the standard error of the slope, the estimated

slope + 1.96 * the standard error of the slope).

In the GE case, this was (.986, 1.002).

If the number 1 lies within this range, the statistical data are telling you that there is a

95% probability that you will get the estimated slope you actually found if the true value

is 1. We say that the data support the hypothesis that the true slope is 1.

If the number 1 does not lie within the interval, we say that the data do not support the

hypothesis.