3. Creating a Stock Portfolio

We began working on the Silver Dollar Stock Club Portfolio Summary spreadsheet.

We set up a sheet title, and column headings designed to help us clearly see the stock holdings of the club. We entered data, including stock, symbol, acquire date, shares, initial price, initial cost, current price, current value, gain/loss, and % gain/loss.

By using the numeric keypad, we were able to enter our numbers and values quickly and efficiently with the least amount of hand movement. We also entered formulas for Initial Cost, Current Value, Gain/Loss, and Percent Gain/Loss, both by hand and with the 'POINT AND CLICK' method. Using the Fill Handle, we were able to copy our formulas quickly and easily down columns. We used the Autosum button to add totals for Initial Cost, Current Value, and Gain/Loss.

We entered Functions to calculate Average, Max and Min, and to generate statistics for our data. Then we began to format the worksheet, changing the FONT, FONT SIZE, FILL COLOR and FONT COLOR, and used the MERGE AND CENTER tool to get our Title to look presentable.

We continued working on the Silver Dollar Stock Club worksheet. We learned about Fixed Dollar Signs as an effect of the ACCOUNTING FORMAT, the Floating Dollar Signs of the Currency Format, the use of the COMMA FORMAT for our other cells, and how to increase or decrease the number of displayed Decimal Places.

We used CONDITIONAL FORMATTING to have Excel flag us when percentage values dropped to less than zero, by showing the cell data with red fill as a warning. We stretched Column A's width, and used Best Fit for Columns B, C, and D.

We performed a number of additional operations:

• Changing Column Widths to a specific fit
• Changing Row Height
• Running a Spell Check
• Displaying the Formula Version of a worksheet should we need to audit it for problems
• Importing External Data using a Web Query
• Referencing the Query in our Analytic worksheet so that each Query Refresh brings in the latest values
• Renaming Sheet Tabs

In other words, we used the Internet to Get External Data with an MS Stock Query to find out the real current values of our stocks. Then we linked the latest prices from the query to our Investment Analysis page, so that every Internet Query lookup updates our Stock Analysis sheet in Real Time. We renamed the sheet tabs, 'Portfolio Summary' and 'Real-time Stock Quotes.'

Since there are no real agreed upon standards for setting up a good worksheet. You must rely on your own sense of what works best in each separate case. That actually gives you lots of room for creatively attacking a project!! Study the techniques used in other worksheets!!

These operations should convince you of the awesome power of Excel to analyze information in an extraordinarily useful way!! You can see how sophisticated your use of Excel can be. You can create different sorts of Financial information and documentation. You've already started to master this tool and to be able to create Financial Worksheets where the only limitation is your imagination!!!

Materials Covered: MS Excel 2007 - Project 2:
Formulas, Functions, Formatting and Web Queries, pp EX 81-144

An Overview of Basic Operations: An Excel Online Tutorial:

1. Click Here for an online review on creating formulas. Work on all three sections - Get Started, Use Cell References, and Simplify Formulas by Using Functions.
   
   You'll need to work on a machine, like the ones in our lab, that runs Excel 2007 in order to complete this exercise.
   
   
2. When you're done, write an entry in your journal about this activity, and any insights you may have learned from this exercise. Did you find out anything new? What was it.
   
   
3. Since our work will depend on your knowledge of basic Excel operations, please have this assignment done by Sunday evening. Thanks