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

After Tuesday, you will not have additional homework this week, so you should spend that time on your report, which is due Tuesday the 10th at 5pm.

Purpose and Instructions

To continue to explore the stock process, find visual representations of statistics to show that different representations of similar data can be used to illustrate conflicting viewpoints, to learn how to import data from the web and work with it in Excel, and learn how to paste graphs from Excel and the web into a Word document to incorporate the graphs into a professional report.

Write a report on your stock. Your report will be similar to my rough draft on WebCT (Click on Course Content, and then Dr. Sarah's MSFT rough draft), except that

  • your report will be much more readable and will follow the writing checklist, WHICH YOU MUST ATTACH TO YOUR REPORT.
  • it will be about your own stock.
  • EXPLANATIONS WILL BE IN YOUR OWN WORDS.
  • the report will include your own charts (see below) and data on your stock and your DETAILED explanations of them. Your charts and explanations will be in the same document (ie not appendices),
  • charts will be color coded by you by hand, unless you print on a color printer.
  • the report will include detailed web references and descriptions of what you used from them!

    Begining of the Paper

    Use your Stock Market Homework, and your postings on webct about the total purchase price, the "what if I sold today" gain or loss update, and P/E ratio. Make sure that your info is mathematically correct in your report (ie if your postings are incorrect, you should redo info for the report).

    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=msft
    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.
    Starting in B2 or B3 the first NUMBER under Open, (clicking when you see the thick cross),
    hold down and pull down and to the right till you highlight all the data (not the words though) in columns B, C, D and E (until the end of your document). Make sure that columns A and F are NOT highlighted.
    Release when you have this done.
    Under Insert, release on Chart.
    You will be in Step 1 of 4.
    Click on Line on the left under Chart Types.
    The graph with the rectangles that is described as "Line with markers displayed at each data value" (this might be the 1st entry of the 2nd row) might be highlighted. Choose this if it is not.
    Click Next to go to Step 2 of 4.
    You will see something like Data Range =Sheet1!$B$2:$E$somelargenumber, and Columns should be highlighted below that in the Series In catagory.
    Click Series at the right above this graph on the right.
    With Series 1 highlighted, change the Name: to Open.
    With Series 2 highlighted, change the Name: to High.
    With Series 3 highlighted, change the Name: to Low.
    With Series 4 highlighted, change the Name: to Close.
    Then Click on Next to go to Step 3 of 4.
    You will be on Titles
    Change Category (X) axis: to something similar to below but relevant dates for your data
    (for example, mine reads Days - (September 29, 2000 - June 29, 2000)
    Change Value (y) axis: to Price of Your Stock Symbol
    (for example, mine reads Price of MSFT)
    Click on Next to go to Step 4 of 4.
    Click on As new sheet
    Click on Finish.
    Excel will bring up your graph as Chart 1.
    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
    Under File, Save your document as yourfirstnamelastinitialstocks.xls without spaces or other characters. (example drsarahgstocks.xls)
    Send this to yourself on campus pipeline, since we will be using this data again.

  • Putting your Excel Graph into Word
    Click on an some of the white part on the very outside edge of your graph. You will see black squares appear on the outer edges of the white part.
    Under edit, release on copy.
    Open up a new Microsoft Word document (File New Document) and then under Edit, release on paste.

  • 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, clisk on the top left rectangle.
    Go back and forth as necessary...
    Save your word file as yourfirstnamelastinitialstocks.doc without spaces or other characters. (example drsarahgstocks.doc)

    Web Graphs

    Skim through my rough draft and the references on WebCT (Click on Course Content, and then Dr. Sarah's MSFT rough draft). Using different web pages such as yahoo or usatoday (take a look at the reference that I quote before each graph in my document, and scroll to the end of the document to that reference number for sample web addresses), create graphs for your stock.

    To paste web 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.

    You can use techniques similar to those in the last section to resize or move your graphs.

  • You will have at least 2 graphs which chart your stocks progress (see the first two graphs in my document) that show that different representations of similar data can be used to illustrate conflicting viewpoints.
  • You will have at least 2 graphs comparing your stock to the S&P 500 (see my middle (more complicated) graphs in my document) that show that different representations of similar data can be used to illustrate conflicting viewpoints.
    Remember to give detailed references and explain each graph in detail. (I didn't do this in my document.)

    Before the End of Lab

    Make sure that you have sent your yourfirstnamelastinitialstocks.xls file and your word file as attachments to yourself on campus pipeline. We will use the xls file again sometime in a few weeks. Your report should be an expanded version of your word file containing at least 5 graphs.