School:  P.S. 101K
UnitStock Market Game
Lesson: Using Statistical Data to Chart Stock Market Activity

Aim:    

1.    To use a spreadsheet application to calculate mean, median, range, percent of change, high and low of stocks over a one month period.
2.    To use spreadsheet formulas to perform calculations.
3.    To uses the internet as a research tool.
4.    To read and interpret information from a spreadsheet.
5.    To share ideas with other students.

6.    To work cooperatively and develop respect for other students' opinions.
7.    To appreciate the fact that stock prices change daily.
8.    To appreciate the volatility of the stock market.

Setting: This lesson could take place in a computer lab or in a classroom setting in cooperative learning groups. In a class of thirty students, six groups would be formed.

Materials:    Computer with Internet capabilities, spreadsheet applications such as  ClarisWorks, AppleWorks, or Excel, printer

Vocabulary:    spreadsheet     statistics    mean    median    range    high    low    percent of change     calculations    formula

Motivations:   

1.     Students, within their cooperative learning groups will be following the progress of the stocks they have chosen to invest in over a period of four weeks.
2.    Students will obtain quick access to multimedia displays of information via the internet.
3.    Students will develop pride in being able to use spreadsheet formulas to calculate statistical information related to their stocks.

4.   In this lesson Cooperative learning group members will be assigned duties as  researcher, who will locate stock quotes on the previously made spreadsheet, analysts, who will use the spreadsheet to calculate percent of change, mean, median, range,  highs and lows for the month,  recorders, who will record the statistical data on the spreadsheet and write about changes in stock prices for the group's journal, and a presenter, who will report to class on the progress of the stocks.
5.    Teacher will introduce the meaning of statistical terms such as mean, median, percent of change, high, low, and range and explain the significance of using statistical analysis as tool in understanding the changes in the stock market.
6.  Teacher will exhibit previously made spreadsheets of each cooperative learning group and demonstrate with LCD display projected during whole class instruction how to find the high and low of each stock over a one month period, how to use formulas to calculate the mean, median and range and percent of change of each stock over a one month period.
Click on the link below to see a sample spreadsheet.
http://teachersnetwork.org/teachnet-lab/ps101/chornik/spreadsheettables.htm

Additional URLs that provide stock quotes, reports by analysts, graphs, historical research, and news articles related to the stocks are:
http://wsj.com
http://marketwatch.com
http://bloomberg.com
http://multexinvestor.com
http://thestreet.com
http://money.com
http://stockplayer.com

Procedure:
 1.    Students, meeting in their cooperative learning groups (which were set up at beginning of stock market game unit, see lesson on Stock Criteria, review data from their spreadsheet and journals created in previous lessons. (See links to previous lessons below).
  2.    Determine the high and low quote for each stock on the spreadsheet.
  3.    Students will determine the median quote for each stock by listing the quotes in numerical order from least to greatest. They will find the median by finding the middle number in the list. If there are two numbers in the middle of the list, those two numbers will be averaged.
  4.    The students will use this procedure to determine the range: They will take the cell with the highest quote for the month and subtract the cell with the lowest quote from it. If the students are using ClarisWorks or AppleWorks for their spreadsheets, they will use the following formula =B18-B10 (B18 having the highest quote and B10 having the lowest quote for AOL as seen on the spreadsheet created in previous lesson on using spreadsheets to create graphs.
 5.    To determine mean, students will use this formula =(B2...B20)/19. As per spreadsheet, the values in cell B2 through B20 are added and then divided by the number of quotes listed for AOL.
 6.    To calculate the percent of change of each stock on the spreadsheet over the month, students need to take the value of the range and divide it by the opening quote at the beginning of the month. This formula may be used in the case of AOL =(B18-B10)/B2. To see this value expressed as a percentage, double click on the cell with the mean value and choose "Percent" from the "Format Number Date And Time" window.
 7.    Statistical Information will be added to the spreadsheet and described in narrative form in the group's journal and printed.
8.     Groups will share their results for the one month period studied. This will consist of oral presentations and presentations of the graphical materials created by the groups.
 9.    All group participants will receive a certificate for participating in the game. The group with the greatest increase in assets due to their "investments" might receive trophies or other suitable rewards.

Other Resources:
Learning From The Stock Market:  Integrating The Stock Market Game Across The Curriculum, published by National Council on Economic Education, 1140 Avenue of the Americas, New York 10036

Please use the following links to view previous lessons in this unit.
http://teachersnetwork.org/teachnet-lab/ps101/chornik/stockcriteria.htm
http://teachersnetwork.org/teachnet-lab/ps101/chornik/graphingstocks.htm
http://teachersnetwork.org/teachnet-lab/ps101/chornik/spreadsheetgraphs.htm
http://teachersnetwork.org/teachnet-lab/ps101/chornik/stockportfolio.htm