 |
| Figure 14: Database index showing four stocks |
Most of the previous portions of the manual have focused on the use of the Lotus 1-2-3 StockChart™ viewer. While the viewer is the most visible and graphic portion of the system, the major part of the StockChart™ system is in the data access and processing.
Virtually all of the complexity of data access and management is hidden from the user. Portfolios can be set up in the Database application on the HP 200LX and the results can be viewed in StockChart™.
Stock portfolios and the Database application will be discussed first. Later sections will briefly discuss access of data from different sources and the various processing steps to pull it all together.
Using the Stock Symbol Database, STOCKS.GDB
A sample database, STOCKS.GDB, is included in the release package. An individual screen of the database is shown below:
|
| Figure 14: Database index showing four stocks |
 |
| Figure 15: Database record for Microsoft |
The key fields which must be filled in are: Symbol, Exchange, and Months.
The STOCKS.GDB stock database may be used as is, stocks may be added to it and stocks deleted from it.
Using macros to update the stock lists from stock databases
Two macros are included in the file C:\STOCKS\STOCKS.MAC to format and output the stock list for each portfolio. The primary macro defined as Macro #8 and is named Sav_Stk for "Save Stock". It should be loaded and used from a portfolio database each time you change the list of stocks.
To load a macro:
If Buddy™ is loaded on your system, the file STOCKS.MAC may be renamed to something like DB.MAC and Buddy™ can be configured to load DB.MAC whenever a macro is request while in the Database application. Consult the Buddy™ documentation for further information.
The "Save Stock" macro is shown below.
Description
Sav_Stk
Contents
{F6}T{F10}{Enter}{Alt+C}{Copy}{F10}{F6}S{F10}{Menu}fp{Alt+A}{Alt+C}{Tab}{Tab}G{Alt+F}
{F10}{Paste{F10}
This macro is Macro #8 in STOCKS.MAC.
The file written out for each portfolio database is used to generate two other files that specify which data to download and to generate the chart data. In addition, the file is used as the stock index by StockChart™ whenever you ask to load a new stock.
The second stock database macro is defined as Macro #7 in STOCKS.MAC and is named Sel_Stk for "Select Stock". It allows you to select stocks from a database (using the Spacebar key) before running Macro #7. It will then build the <portfolio>.STK file containing only the selected stocks. The "Select Stock" macro is described below:
Description
Sel_Stk
Contents
{F6}T{F10}{Enter}{Alt+C}{Copy}{F10}{F6}S{F10}{Menu}fp{Alt+I}{Alt+C}{Tab}{Tab}G{Alt+F}
{F10}{Paste{F10}
This macro is Macro #7 in STOCKS.MAC.
Adding your own stock databases
If you want to add other database (in addition to STOCKS, FOREX, INDICES, and WATCH), or if you want to rename the databases, the following section will outline the procedure for making new databases.
The simplest way to add a new stock database, for example, one called MYSTOCKS.GDB:
A special Smart Clip (reference the HP 200LX User’s Guide for a discussion of Smart Clips) called Graphs is defined. This Smart Clip is used by the database Print operation to format the list into a form that can be processed by other programs in the StockChart™ system. This Smart Clip is copied by the operations above.
A "hidden entry" is used to hold the path of the output file name. When you add a portfolio, you must edit this entry to give the output a unique name. The empty database, TEMPLATE.GDB opens in the "Template" subset. To properly set up your new database:
You may now start entering stocks in the new database. You must enter something in the Symbol, Company, Exchange, and Months fields. The Symbol is the symbol for the stock or foreign exchange. The Company is the company name. The Exchange is the appropriate one of Nasdaq, NYSE, Forex, or History. The Months are the integer number of months of data you wish to maintain on a particular stock (12 is a good starting number). When you are done entering stocks, run Macro [Fn] [F8] to save the stock list in the file C:\STOCKS\MYSTOCK.STK.
All other fields are optional. The Lot1, Lot2, and Lot3 fields are for your convenience only. The Split fields are used to identify a split date and a split ratio. On a market day after a split date has occurred, the price numbers from the split date backward to the beginning of the data for the split stock are recalculated by dividing each price number by the split ratio.
The split date is entered in normal mm/dd/yyyy format. The split ratio is entered as: 2:1, 3:2, etc. The split ratio separator may be any of: ":", "-", "/". For example, 3:2, 3-2, and 3/2 are all equivalent and legal.
Updating Databases to work with StockChart™ Version 3.3 and above
With apologies to all current users, new fields had to be added to the database in order to support recasting historical data to reflect pricing after a stock split. The procedure is relatively simple and it must be done only once in order to take advantage of this very useful feature.
The new stock databases have fields for entering a Split Date and a Split Ratio which were explained in detail above.
Using a macro to run StockChart™
Loading and starting the StockChart™ viewer can be automated with a macro which will shut down all applications and run the StockChart™ Lotus 1-2-3 program. Shutting down applications may be necessary because of the size of StockChart™ and because of the number of TSRs loaded.
The System Macro for running Stockchart™ is:
{More}{Menu}al{123}{Menu}fr{Esc}{Esc}c:\stocks\stkchart.wk1{Enter}
Obviously, the macro refers to the location of StockChart™. Modify the macro to point to your location for STKCHART.WK1 if you keep it in a different location.
This is Macro #9 in the sample file C:\STOCKS\STOCKS.MAC.
Installing an icon to run StockChart™
This section describes how to install an icon to run StockChart™. The next section describes how to install an icon to get the data. The screen shown below shows how the icons look after installation:
 |
| Figure 16: More screen with icons installed |
System manager cannot be directly programmed to run the built-in applications. However, there is a piece of freeware called KeyStuff which makes it possible to program key strokes. By running KeyStuff with the proper command tail, it is possible to run Lotus 1-2-3 and specify the StockChart™ worksheet. The steps are as follows:
- Name: &Stock Chart
- Path: c:\ks.com
- Key Assignment
- Comments: Press [Fn]? - gives the upside down question mark
- Icon, select the new StockChart™ icon (named ks.icn)
- Press [F10] to close.
- Press [F3] to edit.
- Tab to Path: and type in:
c:\ks.com \)\Mal\&\Mfr\E\Ec:\\stocks\\stkchart.wk1/|10
Assuming that your STKCHART.WK1 is in C:/STOCKS. If it is somewhere else, use your path. However, please note the double backslashes. They are important. If you use Buddy™, they will be made into a ":". Quickly press the delete key to restore the double backslashes.
Installing an icon to run the "Get Stocks" data acquisition procedure
To install an application in the "More" screen of your HP 200LX use the following steps:
To install an application in the "More" screen using MaxDOS, use the following steps:
To start data download from system manager, insert your modem into the PCMCIA slot, attach a power supply, and select the "Get Charts" icon from system manager.
Using an alarm to retrieve data
The installation of an icon in the More screen makes it easy to run StockChart™’s data acquisition and processing on demand, but the ultimate automation is to run it from an alarm. The following daily alarm runs the data update at a specified time (maybe 5 AM) the day after each day the market is open:
|c:\maxdos.com command /e:512 /c runner.bat
Be sure to enable the alarm. Put a "Q" in the Location field to cause it to run "quietly".
When the alarm is activated at 5 AM, MaxDOS.COM is run. MaxDOS, in turn, runs RUNNER.BAT.
To use download option, attach a modem and the power supply to the HP 200LX . When you get up in the morning, charts of your favorite stocks’ closing prices (updated to the prior day’s close) will be ready to review with your morning coffee!
You can run directly from an alarm without using MaxDOS. However, since the current directory must be set to the directory containing RUNNER.BAT, the following changes are necessary.
The path statement is used to find RUNNER.BAT, and the system will set the current directory to its location before executing the appointment.
The StockChart™ system is designed to use D&A Software Inc.’s WWW/LX World Wide Web Browser developed specifically for the HP 200LX. WWW/LX is available directly from D&A Software Inc. (http://www.dasoft.com), Thaddeus Computing, Rundel Datentechnik, Easy Use, and Your One-Stop Palmtop Shop. "GET", a free add-on program from D&A Software, Inc. will process a list of web accesses and download the pages..
GET needs to be in the same directory as WWW.EXE.
Overview of the data acquisition system:
RUNNER.BAT
Runner is a batch file that is at the head of the food chain. Runner starts a data acquisition run by:
The default call is: RUNNER.BAT
This assumes that the current directory is /STOCKS. It also assumes that WWW/LX and GET are on in the path statement. (Note: users of version 1 of WWW/LX need to specify the path to WWW/LX as described below).
Optional path parameters can be added to RUNNER.BAT as follows:
Example:
Notes:
GENNER.EXE
Genner uses all <portfolio>.stk files as input to generate a consolidated list of stocks, indices, and foreign exchange symbols. The output of Genner is GENNER.DAT and GENNER.BAT files. GENNER.DAT is input to GET.EXE for downloading update information. GENNER.BAT is a batch file which controls running of conversions, generating files readable by Lotus 1-2-3, and deleting unneeded files after processing.
Switches:
/h - for help
/d - delete the .HTM, .4X, and .CSV downloaded data files after processing
/p - delete the .PRN files when GENNER runs. This switch is useful if your machine has limited memory for downloads. Use with caution - if the login fails, you will not have any charts available until you successfully login, download, and process data.
CONVERT.EXE
CONVERT.EXE processes a stock history file. Currently, CONVERT.EXE processes files downloaded from Yahoo!. The output is a <stocksymbol><months>.LIS file in C:/STOCKS. That file is used as a basis for adding daily closing prices by ACCUM. The <stocksymbol><month>.LIS file is processed by CHART into a <stocksymbol><month>.PRN file which is loaded into the Lotus 1-2-3 StockChart™ viewer.
ACCUM.EXE
ACCUM.EXE processes a list of day end closing prices and builds a .LIS file for each stock downloaded. If the .LIS file exists from an earlier day, ACCUM will add the date, price, and volume information to that file.
Switches:
/h - for help
/a:mm - specify a number of months of data to accumulate. This number will override any month settings for individual stocks and will apply to the entire portfolio.
/q - quiet mode. Accum will not announce each stock as it is processed.
CONV4X.EXE
CONV4X.EXE processes downloaded foreign exchange (<symbol>.4x) files into <symbol>.lis files which can be processed by CHART.EXE.
Switches:
/h - for help
/a:mm - specify a number of months of data to accumulate. This number will override any month settings for individual stocks and will apply to the entire portfolio.
CHART.EXE
CHART.EXE is called for each stock downloaded. CHART reads the data and preprocesses it into a form that that can be quickly loaded and displayed by the StockChart™ spreadsheet program.
Switches:
/h - for help
/i - invert the closing price (that is, use 1.0/price). This is used for currency exchange rates which is downloaded as Dollars/Currency ($/Yen, for example).
/a:mm - number of months of data to process. This number will override any month settings for individual stocks and will apply to the entire portfolio.
BACKANNO.EXE
BACKANNO.EXE is called after all CHART.EXE processing is completed. BACKANNO takes the latest price for each stock and backannotates that price (usually the closing price) into the appropriate *.STK file. That information is used to display the closing price in the stock lists of the StockChart™ Lotus 1-2-3 viewer.
Switches:
/h - for help
CLEANUP.EXE
CLEANUP.EXE is called just before the completion of the data update run. Cleanup determines if there are any stock data files that do not have a corresponding entry in the stock list files. Optionally, CLEANUP will delete any "orphaned" files.
CLEANUP generally does the right thing for most users, but if for any reason you only update part of your stock lists during a particular online run, CLEANUP could delete files that you wish to retain. If you do selective data updates, remove the CLEANUP /C /D entry from RUNNER.BAT.
CLEANUP can be run manually from time to time to either list orphaned files or list and delete orphaned files.
Switches:
/h - for help
/c - use the combined stock list built by GENNER rather than rebuilding the combined stock list
/d - delete any *.LIS, *.PRN and *.TMP files for stocks not in the combined stock list
CHART.CFG - Fine tuning chart processing
The file CHART.CFG is used by GENNER.EXE, ACCUM,EXE, CONVERT.EXE, CONV4X.EXE and CHART.EXE to guide the data acquisition and processing. CHART.CFG is designed to be reasonably self documenting. However, some explanation is in order.
[URL] Section
The [URL] section contains templates which make it easy to retrieve almost any type of data. The identifier associates the URL template with an Exchange as specified in the stock databases. The URL templates use substitution parameters such as %s which represents the stock symbol and %m which represents the number of months of data that are requested. GENNER uses the URL template, the stock symbol, and the month information to generate one entry per stock in the GENNER.DAT file used by GET.
Any number of id:url pairs can be defined for other types of repetitive accesses.
Note also that a "URL" can be defined for local programs. That is the way the "quiet" switch is set when the ACCUM.EXE program is run. The defined URL for Accum is:
Accum=Accum /q
The [Files] section specifies the locations of certain key files. For example, the
NYSE_IN=/stocks/%s%m.lis
file specification will cause the retrieved NYSE data files to be put in the current directory with a name fabricated out of the stock symbol. Example: msft12.lis.
To enable retrieval of different lengths of data for the same stock symbol, the %m substitution parameter is used in the in the file templates. Using this technique, you can keep data like MSFT03.LIS separate from MSFT12.LIS.
[Type] Section
The [Type] section specifies whether processing is as a list of data (Type=List) which is what OldNasdaq provides, or as a single entry (Type=Entry) which is used to add closing prices for NYSE, Nasdaq, or Forex.
The /i switch (used by Chart.exe) can be attached after the type to cause the closing price to be inverted (1.0/price).
[Defaults] Section
The [Defaults] section contains::
Months gives a default number of months in case requested months were not entered in the database. If you always want 12 months, you do not have to make an entry in the database. The default number of months must be one of: 03, 06, 12, 24, 36, 60, 72.
Separate sets the number of days prior to the latest closing price date that maximum or minimum closing price numbers may be displayed on a graph. This parameter is set to 30 days for 12 months of data. If the data length is longer or shorter (24 months or six months, for example) the value of Separate should be adjusted. Setting Separate to 0, commenting out the line, or leaving the value blank will disable Separate.
Zoom gives the number of days for the zoomed graph precalculated by CHART. If you want the default zoomed graph to be 60 days, change the zoom default to 60 in the StockChart™ worksheet. That information will be updated in the CHART.CFG file the next time that any of the data processing programs (GENNER, ACCUM, or CHART) are run.
Note: The default parameters may be set by editing CHART.CFG, but they may also be set by the SETUP/PREFERENCES menu in the StockChart™ Lotus 1-2-3 viewer.
Accessing stock history information
www.nasdaq.com, the Nasdaq web site, like many other online services, produced graphs of stock histories. Unlike other services, Nasdaq also made the underlying data available in a downloadable form.
www.stocktools.com provides up to one year’s stock history data for all stocks. The HTML files are big (106KB for a year’s worth of data).
Yahoo! Recently began providing stock history in comma delimited format. That site is chart.yahoo.com. StockChart™ now uses the Yahoo! Site because the download is physically smaller than Stocktools and there does not appear to be a limit on the length of history data than can be downloaded.
StockChart™ is designed to access the stock history from Yahoo! only once when a stock is first put into a portfolio. After that first access to build the history file, StockChart™ will add daily closing prices to that history file by accessing closing information on Yahoo!.
It is possible to force StockChart™ to download complete histories "on demand" (useful if you don’t plan to log in for daily updates). This is done by specifying a stock’s Exchange to be History in a portfolio database.
Using the supplied stock databases as prototypes, add an NYSE or Nasdaq stock to the database. The exchange must be NYSE or Nasdaq (case does not matter - it can be nyse or NySe, etc.). For stocks from other exchanges such as the AMEX or mutual funds, use NYSE or Nasdaq as the exchange since those two generate accesses to history files. Use macro F8 (supplied in STOCKS.MAC) to save the <portfolio>.STK file. Run StockChart™ either manually or as an appointment. The <stocksymbol>.LIS and <stocksymbol>.PRN files should show up after processing.
If you want to download the total stock history each time you log in, use the special exchange name History rather than NYSE or Nasdaq.
Accessing foreign exchange (FOREX) histories
Using the supplied FOREX database as a prototype, add or delete foreign exchange entries as desired. They have the same general format as stock entries, except the "Exchange" is always "FOREX". As with the stock databases, changes to the FOREX database are saved using the macro FnF8.
To access a list of the current currently available foreign exchange histories, click here.