Dr. Business/DR_BUSINESS.BIN/CG16.DOC

Text File | 1993-11-04 | 26KB | 475 lines

                      Chapter 16 - Data Consistency Check


   ===>>>  The Check Data  Consistency option  on the Main  Menu processes the
   Capital   Gainz   data   you've   entered,   looking   for  anomalies   and
   inconsistencies.  This powerful  tool  lets  you  discover  the  source  of
   potential problems, so you're not reacting to odd symptoms.

   16.1  Consistency Check Form


       The  Consistency Check  Form lets you  tweak the  default parameters to
   your preferences. Each value is described  below, along with examples.  You
   can  automatically  set  values  that  restrict  output  to  only 'serious'
   messages by answering YES to the Serious Errors Only question.

   o Maximum  Days Between  Prices: The  number of  days between  consecutive
     records  in  the Price  History File  before  reporting a  message. This
     setting is used to  catch price entry errors with  incorrect years, such
     as  a   price  with  a  date  of  1/1/1.  Stray  dates  like  this  will
     significantly lower total  return calculations. You  usually won't  have
     consecutive prices recorded more that a year apart. (Default = 365)

   o Maximum  Security Yield: The maximum  yield of a  global security before
     reporting a  message.  One of  the  most  common data  entry  errors  is
     incorrectly  using the  security's  price for  the  per share  value  in
     distributions,  causing  overinflated  yields.  Dividend  yields  should
     rarely exceed 20% or so. (Default = 50.00%)

   o Maximum  Commission   Pct  of   Amount:   The  maximum   percentage   of
     purchase/sale commissions  relative to purchase/sale  amount. Accidental
     entry of  $3500, instead of $35.00, for a commission on a $2000 purchase
     should trigger a message. You can also use this to make sure your broker
     isn't  taking unfair advantage of  you. Even with  full service brokers,
     commissions shouldn't exceed  10% or so, unless the  security is a penny
     stock. (Default = 25.00%)

   o Maximum Open/Close  Gain/Loss Pct: The maximum  gain/loss percentage for
     one, or all, of a security's open/close records. If you enter a purchase
     at  a price  of $1,  instead of  $10, and  correctly enter  a subsequent
     purchase at $11, then the $1 purchase should trigger a message since the
     current  price is $11, a  1000% gain. But, if  a speculative, low-priced
     stock takes off,  or you hold shares for several  decades, then the gain
     can get fairly high. (Default = 999.99%)

   o Maximum Calculated Amount Difference: The maximum difference between the
     purchase/sale amount and the amount  calculated by multiplying shares by
     the buy/sell price. If you  enter a 100 share purchase at a price of $10
     for $100, then something  is incorrect. Some variance should  be allowed
     for rounding. This calculation  even applies to securities that  use the
     averaging  method, since the actual amounts are kept proportional to the
     number of shares.  Total open values  in the  local security record  are
     also  compared  to the  total open  values  in the  activity logs,  as a
     mismatch in the two is a serious problem. (Default = 1.50)

   Capital Gainz Users Manual                                            16-1


   o Maximum Adjacent  Price Diff: The maximum  percentage difference between
     consecutive prices in the  Price History File.  If you executed a  price
     update from a file, but entered  4 instead of 40 for one of the  prices,
     then there's a significant  discrepancy in prices for this  security. If
     you  own a  security  that  gets  bought out,  goes  bankrupt,  or  just
     fluctuates in price a lot, then this message does not signify a problem.
     Adjacent dividends per share  are also checked, but only  for securities
     that pay dividends 12 times per year. (Default = 50.00%)

   o Maximum Selling  Price  Diff: The  maximum difference  between the  open
     price used  for the selling date  and the calculated open  price for the
     selling date. This actually  checks three things. First, if you  use the
     average selling method, the calculated average open price on the selling
     date should  match the  one used.  If you  changed open  information for
     previous sales, then a  difference here indicates an error.  However, if
     you  switched  from a  non-average method  to  the average  method, then
     differences  in sales executed before  you switched can  be ignored. The
     second thing this checks is if you  switched from an average method to a
     different  method.  Here,  the sales  using  an  average  price will  be
     reported.  You  should not  switch  from  the  average  selling  method.
     Finally, this  checks if you  incorrectly modified the  open information
     for a sale. (Default = 1.50)

   o Check For  Unreferenced Keys:  If you  have a  portfolio with no  global
     securities, or a  global security with no local  securities, or a broker
     not  referenced by any  local or  global securities,  then a  message is
     triggered. This is not  the same as having an orphan record,  such as an
     open shares  record  with  no  local  security.  Unreferenced  keys  are
     perfectly valid, and you may want to disable this check. (Default = YES)

   o Check For  Too Many  Div/Fees: If  you have  more dividend  or  interest
     payments in  a year than specified  in the global security,  or pay more
     than one fee,  then a message is  triggered. If you specified  quarterly
     dividend payments for a  fund, but receive monthly dividends,  then your
     yield  will be  about  1/4 of  what  it should  be.  Too many  dividend,
     interest, or fee payments is nearly always an error. (Default = YES)

   o Check For Duplicate Activity:  If you have duplicate purchase,  sale, or
     distribution entries, then a message is triggered. This is almost always
     an  error, as you normally  don't buy the same number  of shares for the
     same amount on a given day. (Default = YES)

   o Check For Activity/Price Diff: If an activity entry's price differs from
     the price history  price, or a distribution  per share differs from  the
     price  history per  share entry,  then  a message  is triggered.  If you
     record a purchase and a price update on the same day at different times,
     then this message is not a problem. (Default = YES)

   The following are usually errors, so they are always checked for:

   o Negative/Zero:  negative or  zero amounts  for the  number of  shares or
     amount  triggers a message. These errors are usually caused by corrupted
     data files, internal program bugs, or incorrect data imports.

   o Invalid Dates: the date fields in a record could be set to 0 due to data

   Capital Gainz Users Manual                                            16-2


     corruption, internal program bugs, or incorrect data imports.

   o Open/Close Dates: except  for short sales, it's usually  an error if the
     open date in  a closed shares record  is later than the sell  date. This
     situation can be caused by incorrect program use, incorrect importing of
     data, or incorrect closed shares record modification.

   o Settings: suspicious values are checked for,  such as a very high or low
     holding period. You may want  these odd settings, or they may  be caused
     by data corruption or internal program bugs.

   o Orphans:  these are references to non-existent records, such as a broker
     name in a security  that has no  corresponding broker defined, a  global
     security  link in a local security with no corresponding global security
     defined, or  activity log  records  for non-existent  local  securities.
     Orphans are usually  errors, caused by data corruption, internal program
     bugs, incorrect data imports, or abnormal termination.

   ===>>> Finally, you can tell Capital Gainz to fix certain problems:

   o Remove Blank/Orphan Records:  orphan records contain data  that refer to
     non-existent records. For  instance, you  may have  aborted the  program
     while deleting  a local security,  and the security was  deleted but the
     open, close, and  distribution records  were not. You  can redefine  the
     local security  and delete it, or  specify YES to this  field so Capital
     Gainz automatically removes it.

   o Resync Local Security & Logs: the local security record maintains totals
     for current  open shares, and they are updated whenever open records are
     accessed,  such  as  during  purchases  and  sales.  If  you  abnormally
     terminate the  program during  an  operation that  manipulates  activity
     records,  the local security's values may  become 'out-of-sync' with the
     actual log  totals. For  instance, if you  terminate a  sale while  open
     records are being  converted to  closed records, some  open records  are
     removed from the log, but the local security values may not get updated.
     This can  be fixed manually from  the Local Security  Table, by choosing
     the  Fixup option's Totals  function. Or,  you can  specify YES  here to
     automatically fix up these discrepancies.

   o Remove Strange Prices: price history records  with invalid types, dates,
     or  values,  or those  that differ  excessively  from prior  values, are
     removed. This will not affect recorded activity.

        You can restrict the consistency check with function keys:

   o F3:Toggle All/Current Portfolio  - toggles the  selection of current  or
     all portfolios, as shown at the top of the form.

   o F4:Select Securities - select local securities to check.

       Function keys at the bottom of the screen are:

   o Form Accept - accept form, the same as hitting Enter on each field.

   o Form Reject - exit immediately, the same as hitting Esc to back out.

   Capital Gainz Users Manual                                            16-3


       After  completing  the  form,   the  consistency  check   procedure  is
   executed. All  data records  for specified  portfolios  and securities  are
   checked. This process  may take awhile, depending  on how much data  you've
   entered.  Upon  completion,  a  log  of  all  suspicious  records  found is
   displayed. Like reports, this log can be sent to the printer or a file.

       Each  suspicious  record  is  identified  in  the  log,  numbered,  and
   associated with  a short message.  The short message,  in turn, includes  a
   reference  number that points to  a longer message. The longer messages for
   all errors found are displayed in order at  the end of the log, and include
   suggestions  for correcting  the problem. Capital  Gainz can  fix many, but
   not all,  problems, since  it's  not always  clear  if  there really  is  a
   problem, or which records should be changed.

   16.1.1  Select Securities


       The  Select Local  Securities Table  allows  you  to tag  securities to
   check. Functions keys at the bottom of the table are:

   o Enter:Toggle  Select - toggle the  tag on the  highlighted security. The
     local security is tagged or cleared, and the next one is highlighted.

   o F2:Select All - tag all local securities.

   o F3:Clear All - clear all tags, such that no local securities are tagged.

   o Esc:Done - exit the table.

   Leaving the table  with no local securities tagged  is the same  as leaving
   it with all of them tagged.

   16.2  Output Log

       The first part  of the output log  identifies a record, and provides  a
   brief message  along with an index  into the list of  long messages at  the
   end of  the report.  In the  log output,  files and records  are identified
   using a logical hierarchy. The general format is:

       Data File:Record Id:Data File:Record Id:...:[Index]Message

       where
         Data File identifies the Capital Gainz data file
         Record Id is a way to identify a record, such as a date or symbol
         Index points to a longer message
         Message is a brief message

   The formats for specific Capital Gainz data files are:

     User Settings File:
           SET::[SETnn]msg

     Portfolio File:
           POR:portfolio id:[PORnn]msg


   Capital Gainz Users Manual                                            16-4


     Broker/Investment Company File:
           BRK:broker/inv co name:[BRKnn]msg

     Global Security File:
           GSC:global symbol:[GSCnn]msg

     Price History File:
           GSC:global symbol:PRC:date:[PRCnn]msg

     Local Security File:
           POR:portfolio id:LSC:local symbol:[LSCnn]msg

     Open Shares File:
           POR:portfolio id:LSC:local symbol:OPN:buy date:[OPNnn]msg

     Closed Shares File:
           POR:portfolio      id:LSC:local      symbol:CLS:sell       date:buy
   date:[CLSnn]msg

     Distributions File:
           POR:portfolio id:LSC:local symbol:DIS:distribution date:[DISnn]msg

   For instance:

       GSC:CPL:PRC: 2/02/87:[PRC02]Adjacent dates differ a lot.

   refers  to  the  Price  History  (PRC)  entry on  2/02/87,  for  the global
   security (GSC) symbol CPL. PRC02 is  the index into the  expanded messages.
   Similarly:

       POR:001:LSC:CPL:OPN:10/01/91:[OPN11]Duplicate purchase.

   refers to  the open  shares  (OPN)  record dated  10/01/91, for  the  local
   security (LSC)  symbol CPL, in the portfolio (POR) number 001. OPN11 is the
   index into the expanded messages.

       Closed shares have two Record  Ids. The first is the  date of the sale,
   and the second is the date of the associated purchase. So:

       POR:001:LSC:VISTA:CLS:  9/03/87:  1/16/87:[CLS06]Closed price  <> price
   history.

   refers to  the  closed  shares (CLS)  record  with 9/03/87  sell  date  and
   1/16/87  purchase date, for the  local security (LSC)  symbol VISTA, in the
   portfolio (POR) number 001. OPN06 is the index into the expanded messages.

   16.3  Example - Consistency Check

       Here's an example of the consistency check output.

       Let's look at each of the messages:

   1-3)  Several  brokers are  defined,  but  no  global  or local  securities
   reference them. I can keep them around or save space by deleting them.


   Capital Gainz Users Manual                                            16-5


   4) Looking  at the Price History Table,  I have an  entry dated 1/03/09 for
   CPL. I made an error entering 1/03/90, and need to remove it.

   5) There's an entry  in the Price History  File for the non-existent global
   security symbol F. This  entry doesn't have  a date, either. Normally,  you
   wouldn't  get such an  error, but  strange things  can happen  during early
   testing of  new features.  There's no  way to  bring up  the Price  History
   Table for a  non-existent global security. So,  to clean up this record,  I
   have  to first define the  global security, then  delete it.  Or, I can let
   Capital Gainz remove it by setting Remove Blank/Orphan Records to YES.

   6-7) These are  serious problems - the open  shares and open  amount totals
   in the Local Security  File for CPL  don't match the totals  calculated for
   the Open Shares  File. More than likely,  this inconsistency was caused  by
   abnormal program  termination while recording  a purchase.  The open shares
   record was written, but  the local security totals  weren't updated. As the
   expanded message suggests, this can  be fixed by running the Fixup Security
   option from the Local Security Table,  and using the Totals function on the
   Fixup Form. Or, I can let  Capital Gainz fix the records  by setting Resync
   Local Security & Logs to YES.

   8) Checking the Open Shares Log, I see  that the 10/01/91 purchase for  CPL
   was incorrectly  entered twice.  Deleting one  of them  fixes the  problem.
   Actually, this problem is related to 6) and 7).

   9)  There  is  a duplicate  purchase  recorded for  GRACE  on  8/12/91, but
   checking the  Open Shares Log  I see that  this is  OK. This is  a dividend
   reinvestment plan, and I sent in two $50 checks that  were both used to buy
   shares on the designated purchase date.

   10) PSNC is a dividend reinvestment plan where dividends are reinvested  at
   a 5% price  discount. I used the  Calc Load/Comm function to determine  the
   price  before discount. I also  had regular cash  purchases on these dates,
   and, because of  rounding, one of the  prices calculated from  the discount
   was  slightly different from the  actual investment price. I  can fix this,
   but it's not a big problem.

   11) Checking the Open Shares Log and Price  History Table, I can see that I
   inadvertently changed the  price in the  Price History  Table for VISTA  on
   11/07/91.   This  change   made  it  different   than  the   price  in  the
   corresponding Open  Shares Log  entry. I  can fix  the entry  in the  Price
   History Table.

       As you  can see, messages  don't always  point out errors.  Here, about
   half of the  messages need to be addressed.  Also, some errors  may trigger
   multiple messages.  Depending on your data and settings, you  may have many
   or few  messages generated by  the consistency check.  There's no point  in
   trying  to  eliminate  them all  if  some aren't  really  problems.  If you
   frequently get  a large number of messages that aren't errors, you may want
   to adjust  the execution parameters. For  example, if you  track the prices
   of a  number of  global  securities, but  only  have  a handful  of  actual
   holdings, you can set Check for Unreferenced Keys to NO,  avoiding messages
   pointing  out global securities with  no local security links. In any case,
   it's a good  idea to periodically run the  consistency checker in  order to
   catch potential problems as early as possible.

   Capital Gainz Users Manual                                            16-6


   16.4  Cleaning Up Problem Data

       As indicated earlier,  you can let Capital Gainz automatically  correct
   some  of the  most clear-cut  problems  reported  by the  Consistency Check
   procedure. However,  the  majority of  messages  may  or may  not  indicate
   actual problems, and it's not clear  which records should be changed to fix
   the problems. So, most of the burden is left up  to you. Here's a suggested
   series of activities you can employ to straighten out problem data:

   1) If the  DOS CHKDSK  command reports errors  such as cross-linked  files,
   fix them  immediately. PC  Tools, Norton  Utilities,  and similar  packages
   offer  excellent disk fixup  software. If you  don't have  access to these,
   use the /F option on DOS CHKDSK.

   2) If you  see strange things in Capital  Gainz, such as table entries  out
   of order, then your  index files may  be damaged. Capital Gainz  files have
   two parts - the  actual data (*.DAT files), and indexes (*.K01 files)  that
   speed  access based on a key.  To fix damaged index files, remove them with
   the CGRESYNC.BAT  batch file. They will be recreated the next time your run
   Capital Gainz.

   3) Run the Consistency  Check procedure, specifying NO to the settings that
   change/fixup data. Be  sure to run it on  all portfolios. It's a good  idea
   to run it this way first, then use the change/fixup options the next time.

   4) Review  the  Consistency Check  log. Mark  the messages  that look  like
   problems, ignoring insignificant ones. If you  notice one or more  specific
   securities that  have many  associated  messages, then  it's possible  that
   abnormal program terminations  occurred while recording their activity. Or,
   a disk failure may have corrupted their activity records.

   5)  Are  there  any  Consistency Check  messages  indicating  that security
   totals are not equal  to activity logs, or that blank/orphan records exist?
   If so, rerun the Consistency Check  procedure, specifying YES to the Resync
   Local Security  & Logs and Remove Blank/Orphan Records. You  can limit this
   run to just the securities in question.

   6) Are there any  Consistency Check messages indicating  that yields or per
   share  values of  distributions are  too  high? If  so,  you may  have been
   entering  the  prices  instead  of   the  distribution  per  share  in  the
   Distribution   Form.  For   each  security   in  question,   bring  up  the
   Distribution  Log and  use the Recalc  Per Share  function to automatically
   recalculate the per share values.

   7)  Now, compare totals  in the Local  Security Table,  or Portfolio Detail
   Report, to your broker  and mutual fund statements.  Do they match? If not,
   print out the  Mixed Detail Report for  each security in question. You  may
   want to set Show Subtotals in  the report parameters to get running totals.
   This report shows a  chronological history of  activity, and you can  check
   it against your statements for erroneous  entries. Add, change, and  delete
   open, closed, and distribution records as necessary.

   8) Did  any  Consistency Check  messages  indicate  problems in  the  Price
   History  File?  For  instance,  messages  may  say  that  open,  closed, or
   distribution log  entries don't match the  price history,  or that adjacent

   Capital Gainz Users Manual                                            16-7


   prices differ  a lot.  Rather than fix  these up  by hand, use  the Rebuild
   Price History function in the Utility Program. Use  the Rebuild Prices from
   Activity Logs option to  first delete the current Price History File,  then
   rebuild it  based  only on  entries in  the open,  close, and  distribution
   logs.  If you  frequently  update  prices, and  don't  want to  lose  prior
   entries not associated with activity, you can at  least trim the number  of
   entries down  by using  Rebuild Price  History to  save  Weekly or  Monthly
   prices.

   9) If you  made changes,  rerun the Consistency  Check procedure to  verify
   them.  If you're  still not  happy  with any  messages, carefully  read the
   associated expanded explanations at the end  of the Consistency Check  log.
   These explanations suggest a course of action to  rectify the problem. Keep
   cycling through this step until you're happy.

   10) Backup your Capital  Gainz data, and label the  disk with the date. You
   now  have  pristine  data for  the  current  date. If  you  experience more
   problems, at least you have good data saved as of this date.

   11)  Implement a  consistent backup strategy.  Chapter 5  suggests a backup
   strategy that rotates multiple disks for maximum safety.

   12) Avoid using Ctrl-Break, the Reset  button, the On/Off switch, and Ctrl-
   Alt-Del  while in  Capital  Gainz.  Applications  and systems  software  is
   getting  more  and  more  complex,  and  abnormal  terminations  should  be
   reserved  as   a  last  resort  only.   Capital  Gainz   accesses  over  20
   interrelated files (counting  data and index files separately). Terminating
   the  program during  file  updates  can corrupt  these  interrelationships.
   Plus, consider  your system  software: a  memory manager  such  as QEMM,  a
   software disk cache such as SMARTDRV, DOS and possibly Windows,  etc. These
   programs  are  already treading  a fine-line  to avoid  harming unprotected
   operating system  areas - don't  make them work harder!  If you started  an
   operation, let  it  complete  and  see  if  you  can  reverse  it.  If  you
   accidentally changed or  deleted a large number  of records, exit  from the
   program  and restore  the most  recent backup  (you have  been  backing up,
   right?)

   13) Be  sure that any disk  cache in use has  flushed its  contents to disk
   before shutting down your PC. This is an  often overlooked culprit in  data
   problems. Disk  caches, like  SMARTDRV.SYS contain  recently accessed  data
   from the  disk. This  is done  to improve performance,  since getting  data
   from  a disk is  much slower  than reading it from  memory. When you update
   the  contents of a disk file,  the data is often held in the cache until an
   'inactive' period. Usually, this  does not pose a problem, but if you  exit
   from Capital  Gainz and immediately  turn off  the PC,  it's possible  that
   some  data  is  not  written  out.  If you  frequently  shut  your  PC down
   immediately  after exiting  applications, it's  a  good  idea to  force the
   cache to  be  flushed to  disk. With  SMARTDRV, this  is accomplished  with
   SMARTDRV /C.  You can  even add  this command  to the   end  of the  CG.BAT
   startup  batch file.  Alternatively, you can  turn off  write caching. With
   SMARTDRV,  this is done by  specifying just the  drive letters  to cache in
   AUTOEXEC.BAT: SMARTDRV C D.




   Capital Gainz Users Manual                                            16-8