## Dr. Sarah's Stock Market Statistics Lab Part II

One Report Per Person

PURPOSE, INSTRUCTIONS AND GRADING:
To continue to explore statistical representations of your stock's data.
**You will turn in
this sheet with the questions answered,
and your Word document which includes a printout of the last two graphs
among the text that explains the graphs (ie not as appendices),
and your answers to the longer questions found at the end of this sheet.
**Your grade will be based on how many
questions you answered correctly and completely, and the clarity and depth
of your writing and explanations, so take your time to think carefully
about the issues before
writing down an answer!
From Campus Pipeline,
open up your .xls files that you sent to yourself in the
Stock Statistics Lab. If you do not have
this file, then you will need to redo the
**Getting Historical Data from Yahoo and Converting it to an Excel Chart
** from the lab from two weeks ago.
We are going to continue to work with your stock market
data.

**Click on sheet 1 to see your excel data. **

We are going to
deeply explore
various statistical representations of this data.
What is your stock's symbol?
### Mean and Median - Recall that the mean is the average of the data while the
median is the middle of the data.

#### The Mean and Median of Date

Click on G2 which is blank.

Under Insert, Release on Function. Click Statistical in the Function
category. Click on Average and then on Ok. To the far right of
Number 1, click on the box with the red arrow.
**Click on the first entry in your Date column while holding
down the mouse
button, (DO NOT CLICK TWICE TO DO THIS)
and scroll down until the bottom of the column.**
Release the mouse button and then hit return. Hit OK.
The mean of the Date is now in G2.
What is the mean of the Date?
What is the Excel formula that is in G2?

Click on H2, and repeat the above process, but choose Median
instead of Average.
What is the median of the Date?
What is the Excel formula that is in H2?
#### The Mean and Median of High

Repeat the above proces for the High column by using empty squares
such as G3 and H3.
What is the mean of High?
What is the median of High?
#### The Mean and Median of Low

Repeat the above proces for the Low column.
What is the mean of Low?
What is the median of Low?
#### The Mean and Median of Close

Repeat the above proces for the Close column.
What is the mean of Close?
What is the median of Close?
#### The Mean and Median of Volume

Repeat the above proces for the Volume column.
What is the mean of Volume?
What is the median of Volume?
### Predictor - Answering the question: Does A predict B?

#### Does Open predict High?

Click on the grey B box above the Open data. The column will
become highlighted. Click the apple key at the bottom left of the
keyboard, hold this down and
then click on the grey C box above the High data. Now both columns
will be highlighted. Under Insert, release on Chart.
Click on XY (Scatter) and then on Finish.
Under Chart, at the top of the monitor, release on Add Trentline...
Click on Options (on the top right), and then click on
the bottom two options (Display equation on chart,
Display r-squared value on chart). Click on OK.
What is the equation of this line?
What is the R^2 value written as a percent? (ie .3529 would be
35.29%).
#### Does High predict Volume?

Repeat the above for the High/Volume columns.
What is the R^2 value written as a percent?
#### Does Date predict Close?

Repeat the above for the Date/Close columns.
What is the R^2 value written as a percent?
### Stock Graphs

#### First Stock Graph

Click on the **first data value** in the open column (B2 or B3),
hold down the mouse button, and scroll down and to the right
until you have highlighted the stock data under the open, high, low,
close and volume columns.
Release when this is done. Under Insert, release on
Chart. Scroll down and click on Stock
under Standard Types. You want the stock chart that inputs
Volume-Open-High-Low-Close (probably the bottom right chart).
**Click on Next. Click on Series. Our data is in a different order,
so we are going to reorder it for Excel as follows.**

At the left of the __S__eries box, you
should see Series5 and the Values: should read something similar to
=Sheet1!$B$2:$B$66.

Click to the right of the first B, hit the delete key,
and type F.

Click to the right of the second B, hit the
delete key, and type F. Click on Name: and Type volume.

Click on Series 1. Change both Cs to Bs, and the Name to open, as above.

Click on Series 2. Change both Ds to Cs, and the Name to high, as above.

Click on Series 3. Change both Es to Ds, and the Name to low, as above.

Click on Series 4. Change both Fs to Es, and the Name to close, as above.

Click on Finish

Copy and Paste this graph into Word.

Hit return a few times.
#### Second Stock Graph

To better understand the tiny boxes on the top
of the first graph, we will create a zoomed
in version of some of the data while excluding the volume data.
Click on the first data value in the open column (B2 or B3),
hold down the mouse button, and scroll to the right
to the Close (column E). Scroll down until you have highlighted
10 rows (row 11 if you started in row 2).
Release when this is done. Under Insert, release on
Chart. Scroll down and click on Stock
under Standart Types. You want the stock chart that inputs
Open-High-Low-Close (probably top right).
Click on Next.
Click on Series and change the Series 1-4 names to open, high, low, close,
respectively.
Click on Finish.
**Change the y-axis values by double clicking on any value on the y-axis,
and changing the minimum and maximum values under Scale,
as we did in the lab two weeks ago.**
**Copy and paste your graph into Word.
Back in Excel,** click on the bottom of one of the lines coming down from
the boxes. Notice that this reveals all of the lows.
In Excel, click on the top of one of the lines coming down from
the boxes. Notice that this reveals all of the highs.
Why does it make sense that the
lows are always at the bottom and that
highs are always at the top?

In Excel, click on the bottom of some of the black boxes. Notice
that this highlights all of the close prices.
Notice that in black boxes, the close is at the bottom of the box,
while in white boxes, the close is at the top of the box.
In Excel, click on the top of one of the black boxes. Notice
that this highlights all of the open prices.
Notice that in black boxes, the open is at the top of the box,
while in white boxes, the open is at the bottom of the box.
In the white boxes, is open above or below close?
In the black boxes, is open above or below close?
Why does it make sense that in real life,
sometime open is above close,
and other times it reverses?

### Questions - Type your answers to the following in Word and use
the writing checklist and the begining
PURPOSE, INSTRUCTIONS, AND GRADING (from above) as guidelines.

Explain each stock graph in depth and then answer the following questions.
1) Comment on why there is no
difference between the mean and median of Date
and explore deeply what this means?
2) Comment on the difference between the mean and median of High
and explore deeply what this means?
3) Comment on the difference between the mean and median of Low
and explore deeply what this means?
4)Comment on the difference between the mean and median of Close
and explore deeply what this means?
5)Comment on the difference between the mean and median of Volume
and explore deeply what this means?
6)What kind of predictor is Open of High?
Use the textbook p. 201 and the R^2 value to answer this.
Explore deeply why your answer makes sense and how you could
have guessed this answer without the graph or R^2 value, by just knowing the
real life meaning of Open and High.
7)What kind of predictor is High of Volume?
Use the textbook p. 201 and the R^2 value to answer this.
Explore deeply why your answer makes sense and how you could
have guessed this answer without the graph or R^2 value, by just knowing the
real life meaning of High and Volume.
8)What kind of predictor is Date of Close?
Use the textbook p. 201 and the R^2 value to answer this.
Explore deeply why your answer makes sense and how you could
have guessed this answer without the graph or R^2 value, by just knowing the
real life meaning of Date and Close, and a progress chart of your
stock.