Curr. Purch. Gain/
Symbol Qty. Price Change Price Loss Value
AMER 1 70 1/2 +5 3/4 25 +45.50 70.50
BORL 1 13 3/4 +1/2 21 -7.25 13.75
IBM 1 54 1/4 +2 1/4 47 +7.25 54.25
MSFT 1 80 3/4 -1/2 83 -2.25 80.75
SGI 1 24 3/4 -3/8 28 -3.25 24.75
The data above is an example of what you get when you save an America Online (AOL) stock portfolio as a file. It can be useful to analyze this information in a spreadsheet. However, spreadsheets require data separated by tabs, and since the portfolio is saved with spaces in between columns of data rather than tabs, some text processing is required.
The find/change command in ClarisWorks comes in handy here. The following instructions step through a process which results in a spreadsheet version of your portfolio.
1. After saving the stock portfolio as a file, open the file and copy the data.
2. Create a new ClarisWorks word processing document and paste. Delete the Totals row.
3. Choose Find/Change from the Edit menu.
4. Enter 2 spaces in the Find field and \\t into the Change to field. Press Change All.
5. Enter \\t\\t into the Find field. Keep pressing Change All until you get the message that zero occurrences were replaced.
6. Select All and Copy.
7. Create a new ClarisWorks spreadsheet and Paste.
If you want to convert Prices from fractions into decimal numbers, use the formula below. In this example, the fractional value in Price (starting in C4) is being converted into a decimal number in column H. You can cut and paste this formula into cell H4 and use the Fill Down command in the Calculate menu to fill a selected range automatically.
=IF(FIND("/",C4,1)=0,C4,LEFT(C4,FIND(" ",C4,1)-1)+ (MID(C4,FIND(" ",C4,1)+1, FIND("/",C4,1)-FIND(" ",C4,1)-1)) / RIGHT(C4,LEN(C4)-FIND("/",C4,1)))
NOTE: BBEdit and McSink are even better suited to doing steps 3-5 because they can convert space runs of 2 or more to a single tab. Both are available in the AOL libraries, keyword: FILESEARCH.