BetNet Stock Club Excel Project Summary

1. CS 202 and ICS 202 - Class 8 by A.Penjira (Mony) Kanthawongs A.Penjira Kanthawongs

2. Class 8 - Agenda • Give out keys for 10 MC and 10 SA Proj. Reinforce. Excel Project 1 • Lecture on Excel Project 2 (E 2.5 - E 2.23 - Half Chapter) • G. HW: Use G. HW from Class 7 and add AVERAGE, MAX, MIN functions for your company (Look at requirements like Page E 2.68 and from the lecture) turn in next class. • Ind. HW: Do 20 MC and 10 SA questions for Excel P. 2 and turn in next class. A.Penjira Kanthawongs

3. Notice for next class. • Continue Lecture Excel Project 2 (the rest of the chapter). • Prepare for Excel – Lab Test (E.Project 1 & E.Project 2) • Change in Syllabus • Class 9, Move “Present Group Work # 2 on Excel” to Class 10 • All Access lecture and homework will be extra credits...not requirements to do them. A.Penjira Kanthawongs

4. Project 2: BetNet Stock Club • Michael Santos and 6 classmates began playing the stock game with Yahoo!’ s web site (quote.yahoo.com). • Michael and his friends started with $100,000 each person to buy and sell stocks for 1 month. A.Penjira Kanthawongs

5. Yahoo! Web site awarded the top finisher a $5,000 cash prize. • Michael and his friends won the contest. They used the prize money to start the BetNet Stock Club and decided to invest in real stocks. A.Penjira Kanthawongs

6. Each month, Michael summarizes the month-end financial status. As the club members approach graduation from college, the value of the club’s portfolio has grown to nearly $900,000. • As a result, the members voted to buy a new computer and Microsoft Office 2000 for Michael. With Office 2000, he plans to create a worksheet summarizing the club’s stock activities that he can e-mail to the members. A.Penjira Kanthawongs

7. Michael has asked you to show him how to create the workbook and access real-time stock quotes over the Internet using Excel 2000. A.Penjira Kanthawongs

8. Project 2: BetNet Stock Club • Need: An easy-to-read worksheet that summarizes the club’s investment (Figure 2-1a). It includes each stock’s … name, symbol, date acquired, # of shares, initial price, i-cost, current price, c-value, gain/loss, % gain/loss. It also includes totals, average, highest value, lowest value for each column of #. A.Penjira Kanthawongs

9. Michael wants to use Excel to access real-time stock quotes using Web queries (Figure 2-1b). A.Penjira Kanthawongs

10. Source of Data: The data supplied by Michael includes the stock names, symbols, dates acquired, number of shares, initial prices, and current prices. A.Penjira Kanthawongs

11. Calculations: 1.Initial Cost= Shares x Initial Price 2. Current Value=Shares x Current Price 3. Gain/Loss=Current Value - Initial Cost A.Penjira Kanthawongs

12. / 4.Percentage Gain-Loss= Gain-Loss / Initial Cost 5. Compute the Totals for Initial Cost, Current Value, and Gain-Loss. • Percent Gain-Loss comes from Total Gain-Loss/ Total Initial Cost. A.Penjira Kanthawongs

13. 6. Use the AVERAGE functionto determine the average for the # of Shares, Initial Price per share, Initial Stock Cost, Current Stock Price, Current Stock Value, and Gain/Loss for each stock. A.Penjira Kanthawongs

14. 7. Use the MAX and MIN functionsto determine the highest and the lowest values for the # of Shares, Initial Price per share, Initial Stock Cost, Current Stock Price, Current Stock Value, and Gain/Loss for each stock, and Percent Gain/Loss. A.Penjira Kanthawongs

15. Web Requirements: Use the Web query feature of Excel to get real-time stock quotes for the stocks owned by BetNet Stock Club (Figure 2-1b) A.Penjira Kanthawongs

16. To start a new line in a cell, press ALT + ENTER after each line. To start typing in a new cell, press ENTER key or one of the arrow keys. Look in Orange Box on Page E 2.7 about “Wrapping Text.” You can instruct Excel to wrap the text in a cell by Choose the cell you want to change the format | Right-Click | Choose “Format Cells” | Choose “Alignment” tab | Check the box “Wrap Text”. Entering the Titles and Numbers into the Worksheet. A.Penjira Kanthawongs

17. Look in all Orange Boxes on Page E 2.8. You can increase and decrease height if rows to add white space between information. You may use 4-digit years to ensure that Excel interprets year values the way you intend. Best way to enter data into a range of cells…Select the range | Enter the data | Press “Enter” key | Enter the data. Formatting a Worksheet. A.Penjira Kanthawongs

18. On Page E 2.7, Table 2-1, each Date Acquired is stated by MM/DD/YY. But you need to enter in this format “DD/MM/YYYY” ….always follow this format!Otherwise, you might get wrong calculations at the end. A date should be right-aligned in the cell because it is considered as a number. If you have trouble, try to fix it by Select the date cell | Right-Clicking | Choose “Format Cells” | Choose “Date” | Choose the format you want. Important notes when entering dates into Excel. A.Penjira Kanthawongs

19. This is how the worksheet looks when the titles and numbers are entered before any calculation. A.Penjira Kanthawongs

20. - * * • Calculations: 1.Initial Cost= Shares x Initial Price 2. Current Value=Shares x Current Price 3. Gain/Loss=Current Value - Initial Cost A.Penjira Kanthawongs

21. A.Penjira Kanthawongs

22. In Excel, you can start a formula with equal (=) , plus (+), and minus (-) signs and they will be considered as numbers and numbers can be calculated! Remember that! If you want to multiply in Excel, you need to press the asterisk (*) not the letter x (X). You can enter the cell references in formulas in uppercase or lowercase, and you can add space before and after arithmetic operators to make the formulas easier to read. “=d3*e3” is the same as “=D3 * e3” Entering Formulas A.Penjira Kanthawongs

23. Order of Operations or Precedence: 1. First Negation (-3) 2. All percentages (%) 3. All exponentiations (^) 4. All multiplication (*) and division (/) from Left --> Right 5. All additions (+) and Subtractions (-) from Left --> Right Order of Operations in Excel P 2 Slash (/) is different from backslash (\) A.Penjira Kanthawongs

24. For example: • =6+(-3)^2 | = 6+9 | = 15 • =D1 / X6 - A3 * A4 + A5 ^ A6 • ^  /  *  –  + A.Penjira Kanthawongs

25. / 4.Percentage Gain-Loss= Gain-Loss / Initial Cost A.Penjira Kanthawongs

26. A.Penjira Kanthawongs

27. Using Point Mode Point mode allows you to create formulas using the mouse. (Page E 2.11 in Orange Box) Using the Fill Handle Page E 2.13 The Fill Handle is a small rectangle in the lower-right corner of the active cell. A.Penjira Kanthawongs

28. A.Penjira Kanthawongs

29. Relative References (Page E 2.14) • Copydownward, Excel adjusts the row portion of cell ref., copy across, Excel adjusts the column portion of cell ref. • To see the formulas in the Excel worksheet, click Tools | Options | Choose “View” tab | Check in the box in front of the “Formulas.” A.Penjira Kanthawongs

30. / 5. Compute the Totals for Initial Cost, Current Value, and Gain-Loss. • Percent Gain-Loss comes from Total Gain-Loss/ Total Initial Cost. (Page E 2.15) A.Penjira Kanthawongs

31. BetNet’ s Worksheet with all Totals. A.Penjira Kanthawongs

32. BetNet’s worksheet with the Total % Gain-Loss. A.Penjira Kanthawongs

33. Lab Test next class. • There will be lecture from Page E 2.23 - the end of Excel Project 2 before the Lab Test. • Prepare for the Excel Lab Test • Practice Excel Project 1 - Excel Project 2 ..Know it by heart :) .. • Questions may come from the textbook or from the instructor. A.Penjira Kanthawongs

34. 30 points, Time: 1 hour, Help yourself…asking your friends or instructor is prohibited, No borrowing any materials esp. textbook and disks. A.Penjira Kanthawongs