Captools/net Documentation

Custodian Imports (how to)

Custodian Imports (how to)

Previous topic Next topic  

Custodian Imports (how to)

Previous topic Next topic  

Overview - Performing an import through the Captools/net custodian interface will import data into Captools/net's Account List (client data), Transaction Blotter (transactional data), Security List (account securities data), Price Blotter (pricing data) and Reconciliation Blotter (position data), subject to your custodian providing the applicable data files


Client/Account, Security, Pricing and Reconciliation data are imported directly into the applicable Captools/net data tables at the time of import.  Transaction data is imported into the "Transaction Blotter" table.  This is a temporary holding area to permit you to review the data in one place before transferring the data to each account's portfolio transactions table.  After review, you can either manually trigger a transfer of the data to the account portfolio records, or you can let this occur automatically during a pre-scheduled occurrence of the portfolio valuation process.   The portfolio valuation process computes holdings, cost bases, and values at a specific point in time so they are available later use for data review and reports.


The step-by-step procedure for importing is as follows:



1) Download Data from your Custodian - Run your Custodian download system to retrieve the latest set of data files accumulated since your last download.   Consult with your Schwab representative for details on setting up and obtaining your downloads, including information on how to setup to automatically download your data unattended.



2) Set Captools/Net Directory Path (First time only) - When you first set up your Custodian download, you will need to indicate to Captools/net the directory path on your system that contains the downloaded custodian files.   This is done through Captools/net Server Control panel, via the "Configure Options" control (see below).  You will need access to your server to set this path.




If you do not have access to your server because you are not the server system administrator, you may still import custodian files, however, you will need to first upload these files to the server.  This is done via the upload button appearing on the import interface:




Once files are uploaded they will appear in the interface under a separate section under your log-in Id or in a "common" area if you choose to make these visible to other users of the system.



3) Import Data - From the Captools/net Desktop program, use the "Interfaces/Custodian Interfaces" command to open the custodian import interface.  Since files are moved to an "Archive" area once they are imported, the files you see will generally be only those not previously imported.  You can generally select and import all of these files subject to the caveat that it is generally not a good idea to import more than a weeks worth of data at a time without reviewing the data just imported.  If you need to view and/or re-import "Archived" data files this can be done by using the "Show Only Archived" file button on the interface.


Scheduled Imports - You can schedule automatic imports of custodian data through the Captools/net "Home/Schedule Tasks" command.  This function is available in some higher level versions of the software:




In order for the scheduled imports to be operative, you must enable the Captools/net Tasker (check box at top, above), enable the "Custodian Import" function (line #1 above) and have checked the import path for your custodian (see prior graphic above) to indicate that you want the automatic imports operative for that custodian.


4) Review Data - Newly imported data should be always reviewed for accuracy.  Records such as account and security records are added to the appropriate Captools/net data tables if they are new.  If existing records are found for a given client or account identifier or security ticker symbol, then the existing record may be updated for new information.  Portfolio transactions are imported into the transaction blotter in order to give you an opportunity to review them before moving them into the portfolio records.  It is easier to identify and correct errors in the transaction blotter than to have to do so later one portfolio at a time.


When reviewing data, please bear in mind that sometimes the custodian does not provide complete information or provides extraneous data that is not needed in Captools/net.  If you see transactions which are missing an essential piece of data such as share quantity or price, date or net amount, you may enter any missing information directly into the Transaction Blotter by selecting the field that has missing data and typing it in.  In other cases, some Transaction Blotter transactions may have missing TAC codes because they are non-relevant to Captools/net, such as journal entries. These are imported in any case to give you a chance to view and verify them. These will not be moved into the portfolios, but will remain on the Blotter for the you to delete after data transfer.  Finally, please note that, Captools/net often imports fewer records into the transaction blotter than exists in the source file.  This is because Captools/net sometimes combines two transactions into one, such as a dividend and its reinvestment, in order to minimize the number of records required in the system.


Note:  If you are in doubt about imported data, you may often view the raw data from source files by opening them in MS-Notepad.  Please contact Captools Co. support if you think that data is being imported incorrectly.  



5) Perform Batch Valuation - At the end of each week or month (or more frequently if desired) you should perform a "Batch Valuation", using the Captools/net "Batch Ops/Valuation" command.  Specify a new date, typically the end of the week or month.  This will move transactions from the Transaction Blotter into the underlying portfolios and will create a corresponding valuation for each portfolio as of the date specified.  These valuations are used in Captools/net reporting. You can also move transactions to the portfolios without performing a Batch Valuation by executing a "Transfer from Blotter" command from the "Tools" menu.   In higher levels of the software you can schedule Batch Valuations to be performed automatically via the Captools/net Tasker as for the imports in the example above.


During the Batch Valuation, pricing data will be pulled in from the Price records.  If no matching prices are found the "Market Price" field will be left empty. In this case you should either manually enter prices, or download prices.   After the Batch Valuation, the Transaction Blotter should be blank and indicating that all data has transferred into the underlying portfolios. If there are still records in the Transaction Blotter, there is data missing from the source records that needs to be manually entered into the Transaction Blotter. You may enter the missing information in the Transaction Blotter and rerun the Batch Valuation to move the transactions to the portfolios, or select "Transfer from Blotter" from the "Batch" menu.



6) Review Reconciliation Records - Captools/net fills its Reconciliation records with the most recent account position data from the custodian, and then matches it with the Captools/net data to identify any discrepancies. The reconciliation process is automatically triggered when the batch valuation is performed.  To view the Reconciliation records select the "Auxiliary" tab under the "Accounts" tab on the Captools/net Desktop.   Records showing discrepancies will be highlighted in red. You should examine these highlighted records to determine where the discrepancy lies.  To examine a portfolio associated with a reconciliation record, select that record and "tab" to the portfolio view.



Data Initialization Using Reconciliation Records - If you are just getting started with a custodian, you can initialize your accounts by importing the earliest available position records from your custodian into the Captools/net Reconciliation records.  Then highlight the "Portfolio Account" field in those records for as many accounts as you wish to initialize, and click on "Data/Compute" or the "Compute" icon.  This will establish initial transactions records in the portfolios belonging to those accounts.



Custodian-Specific Information


Captools Co. builds its custodian import interfaces based upon information provided by Captools software licensees.  This is supplemented by information provided by the custodian firm themselves, when this is available.  Because Captools Co. as a matter of policy to maintain independence does not enter into contractual relationships with custodians,  the degree of cooperation by custodians varies from being extremely helpful to being of no assistance.  Regardless, we are usually able to build and maintain interfaces relying, if necessary, upon sample data provided by Captools licensees.


The following contact information for various custodians is provided to assist you.  However, because we may not always be notified of changes, you may sometimes find this information outdated.  In these cases we would appreciate your notification of this fact.  If the custodian name is colored as a link, you can click on this link to find additional instructions that are specific to that custodian:


AGEdwards  (314) 955-3000  (files by e-mail or FTP)


American Funds (Electronic Data Services) - (800)421-0180 x7283.


AmeriTrade Financial Services - (866) 268-3247   (files by email)


Associated Securities - 310.670.0800 x285.  (files by website)


Bear Stearns - (888) 473-3819   (files by proprietary B.S. Polar System)


CANNEX - (800) 387-1269 ( files downloaded from )


Charles Schwab Institutional - (800) 647-5465  (files from proprietary SchwabLink web system)


First Trust Datalynx - (800)DATALYNX  (files by Datalynx system)


DST Fanmail - (800)435-4112 x2.


DAZL (PFPC) - (617)535-0300  (Data Access Zip Link system)


Fidelity's Investment Advisor Group - (800) 854-4772  (proprietary Fidelity Advisor Channel system, also used for National Financial)


First Southwest


First Union


Fiserv - (215)636-5044


FundSERV - (800) 267-3526


Goldman Sachs


Interactive Brokers - (203) 618-5700


Independent Trust Co. - (708) 873-5300


Key Bank Financial Services - (216) 813-1729


Lehman Brothers - (800) 392-5000 (Lehman Live proprietary system)


LPL's - (619) 450-9606 x6806  (LPL's Branch Plus system)


Mellon Securities - (617) 722-6836


Merrill Lynch - (201) 557-2442


Mutual Service (Network Services Dept.) - (800) 443-4342


National Advisors' Trust - (913) 234-8207  (ftp download)


Nathan & Lewis - (800) 873-7702


Oppenheimer Funds - (800) 858-9813




Penson Financial Services - (800) 696-3585.


Pershing - (800)443-4342 (Pershing "Info Direct" system)


PFPC Dazl - (617)535-0300


Prudential Financial Services - (212) 778-1043


Paine Webber/CSC (Correspondent Clearing Corp) - (201) 352 5867


Regal Discount Securities - (847)375-6050


Raymond James - 800.248.8863. (Powerlink system)


Royal Alliance - (800) 821-5100 (Vision 20/20 system).


Royal Bank - 800.561.3389 or 416.955.5353


Dain Rauscher.  Contact Dain Rauscher directly for a site certificate and the site URL.


Rydex (800)820-0888. (request Captools format data)


Salomon Smith Barney - (212)723-4565  (prop. download program)


ScotTrade  (314) 965-1555   (prop. download program)


SEI - (800) 734-1003  (use Centerpiece format)


Spear, Leeds & Kellogg - 212.433.7000


Sterne, Agee & Leach Financial Services - (800) 264-4863




Southwest Securities - (214) 651-1800 (BPS System)


TDWaterhouse Canada - (416) 413-3379  (ISM system, UFF format files)


TIAA-CREF - (800) 842-2733


Union Bank of Calif. (415) 296-6532 (DirectData AT&T Passport software needed)


Waterhouse Securities - (800) 895-0777  (proprietary VEO website)


U.S. Clearing