Dr. Sarah's Stock Market Statistics Lab
One Report Per Person
This Report Will Count as Two Labs

Dr. Sarah will demonstrate this process for her stock at some point. Of course, your stock will be different, but you should take notes as you are responsible for the material presented. You should read over these directions before lab, which is when you will begin working on this.

To satisfy checklist point number 15.
Does your paper contain reasonably lengthed paragraphs with well defined themes? (Break reports more than two pages long into sections, and begin each section by telling the reader what you will be presenting.)
MAKE SURE YOU DO THIS AND MAKE SURE YOU FOLLOW ALL THE DIRECTIONS BELOW!
Also, before and after each graph, hit return a couple of times and type some text in too.

Begining of the Paper

The begining of the paper must have an introduction that summarizes what you will cover in the paper (see checklist point number 14). You probably want to write this after you finish writing the paper.

Purchase Information

In the next paragraph, you will use your Stock Market Intro, to discuss your stock purchase in detail. Be sure to turn this in also.

At Least 2 Web Graphs that show that different representations of similar data can be used to illustrate conflicting viewpoints.

Using different web pages such as yahoo or usatoday, create graphs for your stock.

To paste graphs into Word from a Mac,
place your mouse on top of the desired graph,
push down hard on the mouse button and hold down until you see a list of options appear.
Release on Copy this Image.
In your same Word document, yourfirstnamelastinitialstocks.doc, click return a few times to make room for this image. Then, under Edit, release on Paste Special. The box on the right under Cancel that says Float over text should be checked. Click on it to uncheck it and then click on OK. To Resize your Graph in Word Click on the graph to see a few open squares outlining it. On the right of your graph, move the mouse till you are on top of an open square You will see a horizontal arrow. Click and move to the left to make the graph narrower to fit into your margins. Click in the middle of the graph and hold down and move to recenter the graph. Under File, release on Print Preview to make sure that the graph is within the margins. To close print preview, click on the top left rectangle. Go back and forth as necessary...

  • You will have at least 2 graphs which chart your stocks progress that show that different representations of similar data can be used to illustrate conflicting viewpoints.
    Save your word file as yourfirstnamelastinitialstocks.doc without spaces or other characters. (example drsarahgstocks.doc)

    In your paper, you should explain each graph in detail, in order to satisfy checklist point number 9. In addition, you should explain how these graphs illustrate conflicting viewpoints.

    Getting Historical Data from Yahoo and Converting it to an Excel Chart Follow Carefully!

  • Finding the Data
    Open up Internet Explorer and type in
    http://chart.yahoo.com/d?s=nite
    BUT replace msft with your stock symbol.

  • Changing the Data In Word so that Excel Can Read It
    Scroll down to the bottom and click on the link
    Download Spreadsheet Format Notice that commas separate the different data points. Excel only recognizes tabs as separators, so we are going (see below) to replace each comma with a tab before we put it in Excel.
    Under Edit, release on Select All, then under Edit release on Copy.
    Open up Word, and under Edit, release on Paste.
    In Word, under Edit, release on Replace
    Next to Find what, type ,
    Next to Replace with, type ^t
    (^t is Word's symbol for a tab.)
    Hit Replace All, then Ok.
    Close the Replace box.
    Under Edit, release on Select All. You will see that all of the data is now highlighted.
    Then go back under edit and release on copy.

  • Working With the Data in Excel to Create a Chart
    Open up Excel and under Edit, release on paste.
    Notice that the Date will be in the first column, Open price in the 2nd, High price in the 3rd, Low price in the fourth, Close price in the fifth, and Volume in the sixth.

    First Excel 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 this graph.
    In Word, under paste special, change to picture, and then hit ok - YOU MUST DO THIS OR YOUR FILE WILL BE HUGE!!

    In your paper, you should explain this graph in detail. Be sure to also explain what you can read from the graph, and that the x-axis is 3 months of data in reverse order (day 1 is the most recent day, while the last day is 3 months ago).
    Save your word file again.

    Second Excel 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). Notice that our data is already in the order it is supposed to be. Click on Next. Click on Series and change the Series 1-4 names to open, high, low, close, respectively. Click on Finish.
    Double click on one of the numbers on the y axis.
    Click on Scale
    Click on the box on the right of Minimum and change it to your smallest value (estimate from the graph).
    Click on the box on the right of Maximum and change it to your largest value.
    Hit Ok
    Copy this graph.
    In Word, under paste special, change to picture, and then hit ok - YOU MUST DO THIS OR YOUR FILE WILL BE HUGE!! 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?

    In your paper, you will explain this graph in depth and will also include the above info in your own words.

    Mean and Median - Recall that the mean is the average of the data while the median is the middle place of the data after it is put in increasing order.

    Begin this section of your paper by explaining how to compute mean and median by hand, and what they tell you in your own words.

    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?

    In your paper, after you explain all of this, you will comment on why there is little or no difference between the mean and median of Date and explore deeply what this means.

    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?

    In your paper, comment on the difference between the mean and median of High and explore deeply what this means. Many of you may have very similar numbers here, even though your stock fluctuated up and down from one day to the next. Include this in your answer.

    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.

    In your paper, comment on the difference between the mean and median of Volume and explore deeply what this means. Namely, on your first excel chart (by hand) draw a horizontal line aT the median of Volume. Then, use the bars and your knowledge of mean compared to median to discuss why you can see from here whether the mean is above or below the median.

    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 Trendline... 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%).
  • What kind of predictor is Open of High? Use the textbook p. 201 and the R^2 value to answer this.

    In your paper, after you explain 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 in the stock market.

    Does High predict Volume?

    Repeat the above for the High/Volume columns.
  • What is the R^2 value written as a percent?
  • What kind of predictor is High of Volume? Use the textbook p. 201 and the R^2 value to answer this.

    In your paper, explain this and then 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 in the stock market. Also, discuss in detail how you could see this from your first excel chart by giving examples of two consecutive days where the high doesn't really change while the Volume does change a lot.

    Does Date predict Close?

    Repeat the above for the Date/Close columns.
  • What is the R^2 value written as a percent?
  • What kind of predictor is Date of Close? Use the textbook p. 201 and the R^2 value to answer this.

    In your paper, explain this and then explore deeply why your answer makes sense and how you could have guessed this answer without the graph or R^2 value, by looking at a 3 month progress chart of your stock. Paste such a progress chart into your paper at this point in your paper, and refer to this in detail to answer this question.

    Conclusion

    Write a conclusion to your paper which includes the fact that the stockmarket is unpredictable and that past performance doesn't predict what will happen in the future.

    You will turn in your draft in one week with

  • Your stock intro filled out
  • This sheet filled out
  • Your paper (which should have 5 graphs), which follows the directions that are in this sheet, and the the writing checklist, which you will follow.

    Be sure to send your document to yourself on campus pipeline as you are working on it, (so that you have a secure version of your paper!) and to keep it for revisions