Capstone Project - Business Statistics

BMG265 – CAPSTONE PROJECT NOTES

This project is divided into three parts, due at different points in the semester. The

purpose of the project is to use statistics to analyze the performance of a stock that is

listed on the stock exchange. A template is provided to guide your analysis and

StockRetrieve is available to simplify the stock data retrieval and statistical calculations.

StockRetrieve is an Excel workbook with macros built in to retrieve the data about the

stock you choose to analyze. StockRetrieve will also generate output that has all the data

you need to complete your analysis of your stock for Part 1. For Part 2, you will use

StockRetrieve to obtain the data, but you will then have to calculate the confidence

intervals. For Part 3, you will take the data you obtained from StockRetrieve and enter it

into a spreadsheet and use the CORREL function in Excel. The directions to use

StockRetrieve can be found on the page in StockRetrieve labeled READ ME FIRST or in

the pages attached to the end of these notes.

 You will have an opportunity to choose your company (out of approximately 120 companies listed on U.S. stock exchanges). See the list in Blackboard.

 Submit the assignment using the Capstone Project template posted on Blackboard. You must enter all information using the computer—no handwritten documents,

please.

Capstone Activity #1-A

The first activity requires you to generate descriptive statistics for your company’s stock

for the last year, on a weekly basis, and comment on performance and volatility.

 You may use data from http://finance.yahoo.com and calculate with MS Excel or use StockRetrieve.xls from Blackboard (highly recommended to save you time).

If you choose to use StockRetrieve.xls, follow the directions on the first

worksheet.

 If you choose stocks from the list provided, you can use StockRetrieve to perform all calculations. If you choose stocks for comparison that are not on the list, enter

the stock symbol and see what happens. Some students have found StockRetrieve

will still perform the calculations. If it does not, select a stock from the list

provided.

 Hint about skew: look at comparison of mean and median.

 You may cut and paste the histogram from the application you use.

Capstone Activity #1-B

This activity requires you to determine volatility and identify good “buy and sell points”

for your company’s stock.

 Review and attach the z-plot generated in StockRetrieve.xls

 Calculate the coefficient of variation for your stock for the year

 For the last part (Buy & sell points), remember “buy low – sell high.”

Capstone Activity #1-C

This activity requires you to compile information about your company’s stock vs.

competitors.

 To find competitors, you can look at http://finance.yahoo.com > (your company’s stock page) > company/competitors. Note: ‘Pvt’ means ‘private’; it is not a stock

symbol. As a shortcut, a listing of stocks in groups with some obvious

competitors has been provided.

 Risk is measured by coefficient of variation (standard deviation/mean X 100%) which is already calculated by StockRetrieve.xls

 You can cut and paste the graph into the assignment.

Capstone Activity #2

Assignment is to identify confidence intervals and test for growth.

 For the hypothesis test, treat the last three years as the population.

 Hint: In your conclusions, think about what it might mean if your confidence intervals overlap from year to year.

Capstone Activity #3

Assignment is to compare your stock with at least two competitors and determine the

coefficient of correlation of each.

 In Excel, copy your data for your stocks into different columns on the same page, then use the CORREL function to calculate r.

DIRECTIONS FOR USING STOCKRETRIEVE

Note: please read carefully - typically over 90% of the problems encountered are covered here

What this does - This Excel spreadsheet has macro programs embedded in it to do an analysis

of stock prices over a selected time interval. It may optionally be used for the BMG265 term project.

You are not required to use this program; it is provided to assist you in analysis of your stock.

Your grade will be determined by how you use the information provided by this program or by

another spreadsheet program.

Start Program -

Go to the stat class section in Blackboard, and click on Assignments, and then Capstone

Project. Right-click on Stock Retrieve Program, and select "Save Target as…" Navigate

file window to "My Documents" click "Save." When "Download Complete" window opens, click

"Open." Now you should see the "Enable Macros" windows described below.

Setup -

Enable Macros - When you started this program, you should have seen a warning message which

asked you to "disable macros" or "enable macros" or if you want "more info." You should select

"enable macros." If you did not see this message, go the toolbar above and select

Tools/Macro/Security and check "medium" security level. Then exit the MS Excel program and

start the StockRetrieve program again; you should see the "enable macros" message this time.

Data Analysis - Before using, check (the first time only) to see that your version of Excel has the

"Data Analysis" add-in installed. Look under "Tools" to see if "Data Analysis…" is listed. If not,

go to "Add ins…"and click that; check both "Analysis Tools" and "Analysis Tools (VBA)" and then

click O.K. If you don't do this, you will see an "Error #6."

Note - WCC computers "reset" when booting, so you have to repeat this after booting.

Note - WCC computers or other computers with slow internet links may take some time to

access all the stock quotes required for this analysis - thus, be patient!

Data Retrieval -

To use, press "Cntl-s" (hold the "Cntl" button down while pressing the "s" key). First, you must

identify how frequently you want stock price information: daily (d), weekly (w), or monthly (m).

Hit "Enter" or select the "Enter" button to accept this interval.

Next, you will be asked to enter the starting date for the comparison in a mm/dd/yy format.

Use numbers only, e.g. 3/1/15. Don't enter future dates!

You may receive an error message if one of your selected stocks was not available at the start date.

After entering, type in the ending date for the comparison in the same format, and enter.

Now enter the symbol for your stock code (not the name), and hit enter. The next symbols you

enter (up to nine) are the comparison stocks or indices. If you want to include

a stock index, an average of a number of stocks in one area, you can enter that instead of

another stock. The stock indices begin with a "^" character, which must be included. E.g. Dow

Jones Industrial Average is "^dji". Both the Dow Jones Indus. Avg. (^dji) and Standard and Poor 500 (^gspc) are good general references for the overall stock market.

If more comparators are used, enter stock symbols or index symbols in the window (up to ten in

total). If none will be used, simply hit enter without entering any stock symbol. The program will

now retrieve your stock information from finance.yahoo.com, and present it in a number of ways.

Interpreting results -

On the "Daily" worksheet, the following statistics are calculated for each stock sample in

each respective column:

Mean: this is the average stock price for this stock for the interval that you specified

Std. Dev.: this is the sample standard deviation for this stock for the interval

Coef.Var.: this is the coefficient of variable (std. dev./mean X100%) for the stock

Growth (%): this is the percent growth in value of the last closing price compared to the first

(r1): these are the Pearson coef. (correl. coef.) of your stock with each following stock

CI 95% lower: the lower value of a 95% confidence interval for this sample

CI 95% upper: the upper value of a 95% confidence interval for this sample

1st Quartile: the first quartile value of the sample

Median: the median (2nd quartile) value of the sample

3rd Quartile: the third quartile value of the sample

IQR: the interquartile range (3rd quartile minus the 1st quartile)

IQR/Std Dev: the interquartile range (IQR) divided by the standard deviation

note: this should be approximately 1.3 for normal data sets

n data points: how many data points are in the sample (days of stock prices)

Maximum: the highest stock value in the sample

Minimum: the lowest stock value in the sample

Mode: the mode(s) of the stock prices (or "none" if no mode)

Range: the range of the stock prices

You will notice several Charts are added at the bottom of the spreadsheet. One of these charts

is the calculated "z-scores" of your selected stocks/indices. The z-scores are the prices adjusted

to represent how many standard deviations the price is from the stock mean price (for the selected

interval). Another chart shows the z-scores with an initial value of zero, so growth over the interval

can be compared directly - this is not as useful as the first chart, but is sometimes used.

The next chart is a histogram of your company's stock prices. This is shown with 'bins'

that are on integer intervals, e.g. $1 or $2 or $5 or $10 or…

The next chart plots growth versus risk (percentage of growth vs. coefficient of variation). You may

want to add a line between (0,0) and your stock's growth/risk. Stocks above this line have better

growth per risk (this is good), whicle stocks below the line don't.

The last charts show a scatterplot of your company's stock prices with each of the competitor's

stock prices. This enables you to see if there are any outliers that may distort the correlation

coefficient between your company's stock prices and those of the competitor. Any data point

that is significantly off of a straight line approximation of the relationship should be considered

an outlier.