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 Series 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.