Data Consistency Check

eZine lover (@eZine)

Published in 

Capital Gainz

 · 27 Jul 2023

Chapter 17 - 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, instead of reacting to odd symptoms.
[[Screen not in online version of documentation.]]

The Consistency Check Form lets you tweak the default parameters to your preferences. Each value is described below, along with examples:

• Maximum Days Between Prices: The number of days between consecutive price records in the Price History File before reporting a message. The main use for this setting is to catch price entry errors with incorrect year values, such as a price with a date of 1/1/1. Stray dates such as this will significantly lower total return calculations. You usually won't have consecutive prices recorded more that a year apart. (Default = 365)
• Maximum Security Yield: The maximum yield of a global security allowed before reporting a message. One of the most common data entry errors is incorrectly using the security's price for the per share value when recording distributions. This will cause overinflated yield calculations. Dividend yields should rarely exceed 20% or so. (Default = 50.00%)
• 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, since 3500/2000 is an excessive 175% commission. 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%)
• 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 $11, and correctly enter subsequent purchases, then the $1 purchase should trigger a message. If the current price is $11, a 1000% gain is excessive. But, if a speculative, low-priced stock takes off, or you hold shares for several decades, or you use a warrant price for the purchase price, then the gain percentage can get fairly high. (Default = 999.99%)
• Maximum Calculated Amount Difference: The maximum allowed difference between the purchase/sale amount and the amount calculated by multiplying shares by the buy/sell price before triggering a message. 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 stored in the local security record are also compared to the total open values calculated by summing the activity logs. A mismatch in the two is a serious problem. (Default = 1.50)
• Maximum Adjacent Price Diff: The maximum allowed percentage difference between consecutive prices in the Price History File before triggering a message. 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, and the first time the difference exceeds this limit a warning is issued. The reason only the first instance is reported is that most non-income mutual funds pay erratic dividends. (Default = 50.00%)
• Maximum Selling Price Diff: The maximum allowed 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)
• 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 a local security linked to a non-existent global security. Unreferenced keys are perfectly valid, and you may want to disable this check. (Default = YES)
• Check For Too Many Div/Fees: If you have more dividend or interest payments in a year than specified in the global security, or make pay more than one fee, then a message is triggered. If you specified annual dividend payments for the security, but receive quarterly 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. An exception is a security that quit paying dividends. (Default = YES)
• 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)
• 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 make multiple purchases at different prices on a given date, then this message is not a problem. (Default = YES)

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

• Negative/Zero: negative or zero amounts for number of shares or amount triggers a message. These errors are usually caused by corrupted data files or internal program bugs. They can also be caused by incorrect importing of data. Return of principal and uncovered short sales are skipped.
• Invalid Dates: the date fields in a record could be set to 0 due to data corruption or internal program bugs. Also, incorrect importing of data can cause this.
• Open/Close Dates: it's usually an error if the open date in a closed shares record is later than the sell date. Short sales are an exception. Other than short sales, this situation can be caused by incorrect program use, incorrect importing of data, or incorrect closed shares record modification.
• Settings: suspicious values are checked for, such as a very high or low holding period. Sometimes, you may want these odd settings. Otherwise, they may be caused by data corruption or internal program bugs.
• Orphans: these are references to non-existent records, such as broker names in securities that have no corresponding broker defined, global security links in local securities with no corresponding global security defined, or activity logs records for non-existent local securities. Orphans are usually errors, caused by data corruption, internal program bugs, incorrect data importing, or abnormal program termination.

You can check the consistency of one or all portfolios:

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

Function keys at the bottom of the screen are:

• Form Accept - accept all entries, the same as hitting Enter on each field.
• Form Reject - exit immediately, the same as hitting Esc to back out of the form.

After completing the form, the consistency check procedure is executed. All data records for specified portfolios 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 DOS 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. Of course, it would be nice if Capital Gainz could automatically correct the errors, but it's not always clear if there is a problem, and even then it's not clear which records should be changed.

17.1 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 some 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]message

Portfolio File:

   POR:portfolio ID:[PORnn]message

Broker/Investment Company File:

    BRK:broker/inv co name:[BRKnn]message

Global Security File:

  GSC:global symbol:[GSCnn]message

Price History File:

  GSC:global symbol:PRC:date:[PRCnn]message

Local Security File:

  POR:portfolio ID:LSC:local symbol:[LSCnn]message

Open Shares File:

   POR:portfolio ID:LSC:local symbol:OPN:buy date:[OPNnn]message

Closed Shares File:

  POR:portfolio      ID:LSC:local      symbol:CLS:sell     date:buy        date:[CLSnn]message

Distributions File:

     POR:portfolio         ID:LSC:local        symbol:DIS:distribution        date:[DISnn]message

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:

       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.

17.2 Example - Consistency Check

Here's an example of the consistency check output.
[[Report not in online version of documentation.]]

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.
• 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 some 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.
• 6) FPEUR is a foreign stock mutual fund, and dividends are erratic. The per share amounts differ considerably, but it's not a problem. Only the first instance is shown for a given security.
• 7) Checking the Price History Table, it looks like I entered the reinvestment price instead of the dividend per share when I recorded a dividend for GRACE. I need to change the entry in the Distribution Log, which will also fix the price history entry for that date. If I consistently made this mistake, I'd also see a message indicating that the yield is too high.
• 8) HTG is a growth fund, and dividends are erratic. The per share amounts differ considerably, but it's not a problem.
• 9) NUVNC is a unit trust I bought at the end of the first distribution period. I only received partial dividends, so the per share amount calculated was lower than it should be. I need to adjust this.
• 10) The same thing happened to TCTXI as described in 9). The initial dividend is the culprit, and should be adjusted.
• 11-12) 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 that likely, this inconsistency was caused by abnormal program termination while recording a purchase. This caused the open shares record to be 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. This resynchronizes the values in the local security and the open shares records.
• 13) 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 11) and 12).
• 14) 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.
• 15) 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 calculated prices was slightly different from the actual investment price. I can fix this, but it's not a big problem.
• 16) 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 need to fix the entry in the Price History Table.

As you can see, messages don't always point out errors. Here, less than 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.