# Explain your findings, ie what caused the highest positive and negative return in a particular period

Get the monthly historical stock price data during January 1995-Present of

three stocks. There are many websites that provide the historical stock price

indices. The handout given will guide you through how to download the data

from www.yahoo.com. Go to http://finance.yahoo.com, enter ticker symbol

such “T” for “ATT” and click on “get quotes”. However, you need to know

the ticker symbols of the stocks you are interested. The ticker symbols can be

obtained from from the company’s website.

2.

Get the monthly market indices during January 1995-Present. Market indices

such as S&P500 (^GSPC ticker symbol from yahoo website) can be obtained

from several websites.

3.

Calculate the monthly returns of each stock.

Return of ATT stock (0209) = Price of ATT (0209)-Price of ATT(0109)

Price of ATT(0109)

Then multiply by 100 to get return in %.

4. 5.

6.

7.

8.

9.

10.

11.

12.

13. Plot the bar chart of monthly historical returns of each stock on each separate

graph. Explain your findings, ie what caused the highest positive and negative

return in a particular period? You can search this information by using the

particulate date of highest and lowest return and name of company in google

(Please refer to textbook chapter 11 figure 11.2 page 331.

Using the Excel functions for average (AVERAGE), calculate the average

returns of each stock.

Using the Excel functions for variance (VARIANCE), calculate the variance

of each stock.

Using the Excel functions for standard deviation (STDEV), calculate the

standard deviation of each stock.

Calculate the monthly returns of S&P 500 index.

Using the Excel functions for average (AVERAGE), calculate the average

returns of S&P 500 index.

Using the Excel functions for variance (VARIANCE), calculate the variance

of S&P 500 index.

Using the Excel functions for standard deviation (STDEV), calculate the

standard deviation of S&P 500 index.

Plot the bar chart of monthly historical returns of S&P 500 index. Explain

your findings, ie what caused the highest positive and negative return in a

particular period?.

Compare the average monthly return and standard deviation of each stock

with the S&P 500 index (market return), what can you conclude about risk

and return relationship and diversification?

Use the following Table 1 as an example to present your finding. ATT

0.6551

60.7968

7.7972 Average

Variance

Standard Deviation Walmart

1.2157

52.3132

7.2328 IBM

S&P500

1.4235

0.4002

81.7767 19.9352

9.0430

4.4649 Instructions on how to get the Historical Price

Go to http://finance.yahoo.com

Put the ticker symbol on the Get Quotes. For example: T is the ticker symbol for ATT

stocks.

Click on historical prices. Mont

h Daily

Day Year Weekly Start: 23 95 End: 24 01 Ticker Symbol: Monthly

Dividends

ibm Get Data You will get the following:

Date

Nov 01 Open

107.25 High

117 Nov 01 Low

106.90 Close

115.59 Volume Adj.

Close* 6,435,000 115.438 $0.14 Cash Dividend Oct 01 91.72 112.10 91.34 108.07 9,754,400 107.795 Sep 01 100.15 103.13 87.49 91.72 12,620,800 91.4863 Aug 01 105.35 110.09 98.86 99.95 6,234,800 99.6954 The Open price is the first traded price for the day chosen in that month.

The High price is the highest price during that month.

The Low price is the lowest price during that month.

The Closing price is the last traded price for the day chosen in that month.

The Adj. Close is a price that reflects the total return that would have been achieved

by holding the stock for 1 month. It takes into consideration the effect of dividend payments and stock split. This is the price we are going to use to calculate the total

returns of each stock.

Hit the Download Spreadsheet Format to get the data.

This will be in a csv file, so after downloading the file make sure you save it in the

excel file. The close price in this file is the Adj. Close, which is the one you will use

to calculate each stock’s total returns.

For the IBM stock, these are the numbers you will get:

Date

Open

High

Low

Close

Volume

01-Nov 106.977 116.846 106.628 115.438 6435000

01-Oct 91.4863 111.814 91.1073 107.795 9754400

01-Sep 99.8948 102.867 87.2671 91.4863 12620800

01-Aug 104.943 109.665 98.6081 99.6954 6234800

01-Jul 113.062 114.954 101.168 104.804 7667400

01-Jun 112.066 118.54 110.671 113.062 6806100

Then create the Excel Spreadsheet with the date and closing price.

Date

Stock 1 Stock 2

01-Nov 115.438

01-Oct 107.795

01-Sep 91.4863

01-Aug 99.6954

01-Jul 104.804

01-Jun 113.062

Since the output from the website starts from the most recent observation, you need to

sort the date and the closing price from the past to present.

To sort the date and closing price, go to Data, Sort, and Ascending.

Once the data is sorted from the earliest observation to the most recent observation,

the return series is created by taking the percentage price differences from month to

month.

For Example:

Return (Feb 1999) = Close Price (Feb 1999)-Close Price (Jan 1999) *100

Close Price (Jan. 1999)

You also need to calculate the total returns for the market index such as the S&P500

indices using the method described above. Instructions:

1. Prepare a write-up of your report not more than 2 pages 12 points font and

double space. The write-up should be a report format, summarizing all your

findings and support your findings with numbers (using the questions asked as

a guideline to form the report format). The report should have three main

parts, introduction, body, and conclusion. The introduction should provide

a brief introduction what you will be discussing the project. In the body,

students should summarize their findings in words and support the findings

with the numbers. Insert the table that summarizes your finding where

appropriate. Insert graphical representation if necessary where the text is

being discussed. In the conclusion, you need provide the main finding of

your report, reemphasize your key findings. There is no need to explain the

calculation in the report. Attached all raw data, calculation, and regression

output at the end of project. The assignment report has to be well-written and

well-organized and free from grammatical mistakes. Please format all the

number to 4 decimals for easy comparison. Please place the $ or % where it is

appropriate. 2. The report should also contain the main Table 1 and the plots of historical

returns for each stock with explanation of high and low. Attached all data and

calculation to the end of project.

**. . **

The post Explain your findings, ie what caused the highest positive and negative return in a particular period appeared first on Blackboard Masters.