Captools/net Documentation

Data Reconciliation

Data Reconciliation

Previous topic Next topic  

Data Reconciliation

Previous topic Next topic  

Following import of custodian data, you should use Captools/net Reconciliation table to determine whether the custodian data matches the data that resides in your Captools/net position records.


The Reconciliation table is a working table in that newer data, when imported, replaces older data.  The left side of this table shows the custodian-assigned account numbers and the custodian reported shares and values.  The right side shows the user's assigned portfolio account numbers (at user's option they may be the same as the custodian's) and the Captools/net share quantities and values.  On the far right any quantity and value discrepancies are highlighted in red, as shown in the example below for the second account group.  Scrolling through these records allows you to easily identify reconciliation problems:




Reconciliation Accounts - As noted above, these records can employ both custodian and user-assigned account numbers.  Furthermore, a given portfolio account may be associated with more than one custodian account as is the case in the above example where two custodian accounts are held for each of the portfolio accounts.  These custodian accounts are connected to the client accounts via the Sub-Account records.


Orphan Record Resolution - If you have not assigned a custodian account to a Captools/net client account, it will appear as an orphan reconciliation record, i.e. without any associated portfolio data on the right side of the record.  Often such orphan records may be related to other reconciliation records which contain portfolio data, but are missing custodian data.  The reason such records are not presented as a single record is that the custodian account numbers have not yet been assigned to a portfolio account through the sub-account records. This may be resolved by manually filling the portfolio account number field with the appropriate value in each reconciliation record which is missing a portfolio account number.  Upon doing so and moving to the next or prior record, the portfolio data will be merged from the other corresponding reconciliation record and the record will be removed.  The appropriate sub-account record will also be generated by this process, eliminating the possibility of orphan reconciliation records being generated for this account in the future.


Reconciliation & Portfolio Dates - These fields indicate the dates of the source data upon which the reconciliation is being performed.  These should generally be identical, unless no transaction or market activity has occurred between the dates as during a weekend or holiday.  If the dates are different other than due to the weekend or holiday effect, the reconciliation will not have much significance.  In this case, in order to obtain a meaningful reconciliation you should run a batch valuation for the indicated reconciliation date, then return to this view to complete the reconciliation process.


Account Id - This field is pulled from the Sub-Account records based upon the Custodian account number.  It is not essential to the reconciliation process and thus may be blank as in this example.  However if you assign a meaningful Account Id in the Sub-Account records (e.g. "CS" for Charles Schwab, TDW for TD Waterhouse, etc.), it may be of assistance in identifying the custodian.


Prices - Prices and exchange rates are editable so as to permit the user to fill in pricing which may be missing from or incorrect in the source data.  Portfolio prices or exchange rates, if modified are updated back to the source position records provided they match the corresponding custodian data.


View-Only Fields - Many of the fields in the Reconciliation table are View Only, meaning that you are not allowed to edit them.  This is because the values are imported from the custodian files or retrieved from the Captools/net database for comparison, and any change would invalidate that comparison. Exceptions are price, exchange rate or accrued amount fields.  Data for these fields are not always provided by all custodians, so you are allowed to enter data in these fields if they are initially empty.



Multiple Custodians


If your portfolio accounts contain data from more than one custodian, you may import position records from all of the custodians prior to examining the reconciliation records, or you may import and reconcile custodians sequentially.  The disadvantage of the latter approach is that orphan portfolio-side reconciliation records will be generated, making it more difficult for you to affirm that each account reconciles in its entirety.



Resolving Reconciliations Discrepancies


Reconciliation discrepancies can arise for a number of reasons as follows:


Orphan Reconciliation Records -  These can be easily resolved by setting up the proper sub-account records or by using the Orphan Record Resolution method described above.


Pricing Differences - If the custodian (reconcile) pricing does not match the portfolio pricing, you may manually copy/paste the custodian pricing over the portfolio price to force the underlying portfolio to use the same pricing as the custodian.


Quantity Differences - These usually arise due to incorrect or missing transactions in the underlying portfolio.  To resolve these discrepancies you must examine the data in the underlying portfolio transactions.  Select a record containing the discrepant portfolio account number and click on the Portfolio tab to open that portfolio's transactions.  Set a filter on the applicable symbol and use the portfolio transaction Share Balance field to help identify when the balance diverged from the custodian records.  Things to look for are mis-coded transaction codes, incorrect signs on quantity values or transactions dated earlier or later than expected.



Cost Reconciliation