Captools/net comes with scripts which allow it to import data from more than several dozen institutional custodians.  About a dozen of these are widely used by Captools/net users. The remainder are less frequently used, and thus have a smaller experience base.  For most users, one or more of these scripts will be satisfactory to meet their custodian import needs.  However, some users may wish to import data from a custodian data source for which we do not provide a script, and for which the Captools/net "generic" import templates interface provide insufficient flexibility.  In this case you can try to write your own import script, subject to the caveat that Captools Co. will not be providing support to licensees of "self-developed" scripts, other than this documentation, due to personnel resource constraints, and subject to the additional caveat that the following documentation may be subject to inaccuracy due to software changes.  It should also be noted that some of the standard custodian scripts trigger specialized code branching to deal with issues of a particular custodian, so mimicking an existing script may not always produce the same result.

 

Script File

 

Captools/net custodian imports are controlled by "Institutional Data Interface" script files located in the Captools/net "\Webscripts\CTscripts" folder.  These files have an ".idi" file extension, and a filename prefix that usually is an abbreviation of the custodian's name.  You should follow this convention if you build your own script.  Once the script file is placed in the "\Webscripts\CTscripts" folder, it will appear on the Captools/net Server Control Panel, "Configure Options/Interfaces" dialog, where you will specify its import data path.

 

The Captools/net script file is accessed in a similar fashion as Windows "ini" files.  That is it is sub-divided into sections, and within each section it uses an "identifier = value" format. The remainder of this topic will be devoted to each section, describing how each section controls the import.

 

Important Notes:

 

1) If you compare this documentation to existing standard script files, you will note that the "standard" files contain some extraneous lines, not documented here.  This is because they were converted over from our older software where these lines had significance.  They are removed here to avoid confusion).

 

2) When you create and edit a script file, please use MS-Window's "Notepad" program.  This is to ensure that the file is saved in "plain text" format. If you use any other editor, please be sure to save the script in "plain text".  In either case, make sure the filename extension is ".idi" so Captools/net will recognize it.

 

3) Identifiers to the left of the equal sign must appear exactly as indicated, including underscores, e.g. "DOWNLOAD_SERVICE_NAME"

 

File Retrieval Specifications Section

 

A typical file retrieval specification section appears as follows

 

 

DOWNLOAD_SERVICE_NAME - This line specifies the custodian name which will appear in the list of available custodian imports.

 

The third through seventh lines specify the default filename filters that will be used for each type of import:

 

ACCOUNT_SOURCE_FILES - This specifies the filter for files containing data which will go into the client and account tables.  Only files passing this filter will appear to the user in the list of files available for import.

 

TRANSBLOT_SOURCE_FILES - This specifies the filter for files containing transaction data which will be imported into the transaction blotter.

 

RECONCILE_SOURCE_FILES - This specifies the filter for files containing reconciliation or "position" record data.  This will be imported into reconciliation table.

 

SECURITY_SOURCE_FILES - This specifies the filter for files containing security descriptive information to be imported into the security id and statistics table.

 

PRICES_SOURCE_FILES - This specifies the filter for files containing security market pricing data to be imported into the price history tables.  Often the custodian will provide pricing information in either the security file or the reconciliation file, meaning the same file will be imported twice, which is ok.

 

The characters "*" and "?" can be used as "wildcards" in the filter in the same manner as they are used when searching in MS-Windows for files.  As in this example, more than one file filter per table can be specified.  Some custodians put data relevant to several Captools/net tables into one file, in which case the same filter may apply to more than one table.

 

The notation <ON> after each filter means that the default interfaces will have this item checked.

 

DECRYPTION_PW - This line is only needed if a decryption password will be needed for zipped files, otherwise it can be omitted or marked <OFF> as in this example.

 

DECOMPONEATATIME - This line is only needed if the custodian provides data in zipped format, but the files contained therein have the same name as prior days, in which case this parameter must be set to "<ON>", so that each file is decompressed and imported before the next zipped file if the users specifies import of more than one file.

 

 

Global Switches Section

 

The Global Switches section contains settings that control how the data is processed when imported.  In the screen shot below, the switch names are on the left side of the equal sign, with the switch setting on the right.  The comments in the curly brackets indicate how the switch is used, with more detail given below as necessary.

 

[##GLOBAL_SWITCHES##]

{~~~General Switches~~~}

DELIMIT_METHOD=COMMA          {COMMA, TAB, SEMICOLON TILDE PIPE SPACE, FIXED, NONE}

DATE_FORMAT=MM/DD/YYYY {format spec. e.g. "YYMMDD", "MM/DD/YY"

DATE_IN_FILENAME=??YYMMDD        {source date to be taken from the filename}

RENAME_FILE_TO=           {renames source file to specified filename, generally specify in for each table}

UNZIPMASK=*.zm9   {allows specifying alternative file extensions to be unzipped}

EXCLUSIONSFILE=   {allows you to specify a file containing a list of account#'s to be excluded from import}

SOURCE_ID=EXPL            {Data Source Identifier, use custodian abbrev.}

DECIMAL_CHAR="."    {allows specification of decimal character, default assumed is "."}

 

{~~Transaction switches~~}

TRAILER_NOTE=ON                {read ahead for trailing note used for TAC}

TRAILER_TAG="XYZ"@25        {uses trailer only if tag appears at specified position in trailer record}

MULTIPLE_TRAILERS=OFF   {If "ON" assumes can be more than one trailer line}

 

CUSIP_TO_SYMBOL=ON        {replace cusips in symbol field with ticker}

DESCRIP_TO_SYMBOL=OFF        {if no symbol, use descr to look up symb}

 

CASE_SENSITIVE=OFF      {If "On" case is respected in TAC translations}

TAC_WILDCARDS=OFF     {recognizes "?" and "*" characters in TAC translations}

SUPPRESS_NUL_TACS=ON        {does not import transactions with blank TAC fields}

 

IGNOR_SIGNS=OFF                {ignore minus signs in source files}

REVERSE_AMOUNTS=OFF        {reverses sign on amount imports}

GROSS_TO_NET=OFF        {converts gross to net amount by deducting commission}

NET_ACCRUED_INT=OFF     {adjust import amount for accrued interest}

RCV_DLV_PRICING=ON      {Prices RCV/DLV transactions which are missing amounts}

FORCE_OTHER_FEES=OFF

CALC_COMMISSION=OFF        {commissin amount to be imputed from other figures}

CALC_ACCRUED=OFF        {if "ON" willl compute transaction "Accrued" amout as a "plug"}

FORCE_PRICE=OFF                {forces transaction prices to tie out with qty, commis & amt}

NET_TAXWHELD=ON                {adjust import amount for foreign tax withheld}

 

CONSOL_REINVESTMENTS=ON        {convert dividends+buys, etc. to DRI}

CONSOL_TRADES=ON 0.5%        {Consolidates same Date,Acct,Symbol,Tac/Price trades, the percent indicates how large a difference is allowed before records are kept separate}

CONSOL_TAXWHCOMMISFEES=ON     {assigns TaxWH,Commis & Fees to parent records when tax withheld, commission and fees are provided as separate records}

MATCH_TAXWH_SYMB=ON        {When TaxWH transactions are consolidated with the parent transaction this indicates that symbols must match in the two records}

DONT_CONSOL_TACS=MFE,WDF,DPF  {This specifies transaction codes to never consolidate}

 

DEDUPLICATE=OFF                {eliminates duplicate transactions}

PURGE_JOURNALS=OFF        {deletes offsetting journal transactions}

LOOKUP_DESCRIP=OFF        {If "ON" uses sec. record description as transaction descrip}

USE_TRADEIDS=OFF        {If "ON", assigns unique trade #s to each buy/sell transaction, for each import}

DECOMPONEATATIME=ON   {This setting should be "ON" for users with multiple rep codes and interfaces using Zip files, to avoid having data files overwritten during unzipping}

 

{~~Security & Prices ~~}

IGNOR_NOT_HELD=ON        {If "ON" does not import securities or prices for not held symbols}

IMPORT_DISTRIBHIST=OFF   {If "ON" imports distribution records from Price History files (if data exists)}

IMPORT_FINANCIALHIST=OFF {If "ON" imports finacial history data from Price history files (if data exists)}

 

{~~Reconcile/Position Records~~}

CONSOL_RECONRECS=ON        {If "ON" combines Reconciliation records having the same date, acct#, symbol, & AcctId}

 

{~~Client/Account Records}

ACRONYMS=IRA,FBO,JTWROS,CPA,SEP,ESA,"JT TEN",LLC

NON_NAMES=INC.,CO.,CORP.,ASSOC.,CPA,PHD,MD,DR,DDS,JR,II,III,IV,

NON_NAMES1=

NON_NAMES2=

NON_NAMES3=

MORE_NONS=FOR,TTEE,SR,INC,ASSOC,TRUST,AND,CORP,ASSN,CUST,MP,JR.,M.D.,THE,TR,

MORE_NON2=PROPERTY,FAMILY,AS,M_D,OF,LTD,CO,C/F,REV,GROUP,INDIVIDUAL,CUSTODIAN

 

{TransBlotter, Position and Price records local override only}

ISECID_TO_SYMBOL=OFF  {If "ON" uses the "Internal Security Id (ISECID) field for lookup of security symbols in lieu of lookup based on Cusip}

 

Some things to particularly note about the above switches are as follows:

 

DELIMIT_METHOD - This specifies how the data fields in the source data are separated.  Use the word "COMMA", "TAB", "SEMICOLON", "PIPE", or "SPACE" to indicate data separated by one of these characters.  If the field is specified by its absolute character location in the text record, specify "FIXED" or "NONE" here.

 

DATE_IN_FILENAME - Some custodians embed a date in the filename of a data file to indicate the applicable date of the date, e.g. the date of prices in a price file. This specification tells how to read that date.

 

UNZIPMASK - If the applicable data files are contained within a file ending in ".zip", Captools/net will know to unzip the file to a temporary subfolder (called "EXPAND"), and look for the files inside. However sometimes a custodian will apply a non-"zip" extension to a file that is in fact really zipped.  Use this property to indicate that such files are to be in fact unzipped.  Zip files that have been made into self-extracting files can be specified by making the Unzipmask equal to "*.exe".  Note however that if a non-zip compression method was used, you will need to manually decompress your data files.

 

Transaction Switches - These are a subset of the switches that apply to the import of transaction records into the transaction blotter.  The "Trailer Note", "Trailer Tag" and "Multiple Trailers" refer to additional lines of data that some custodians place after the main record lines, principally to further describe or indicate more information about the main record.  Setting "Trailer Note" to "On" tells Captools/net to look for these, so they can be used to help in translating transaction codes and setting other values.  "Multiple Trailers" indicates that there may be 2 or more trailing data lines following the main record.

 

Client and Account Switches - One of the biggest challenge in importing client and account records is parsing the client's name if the custodian does not provide separate data fields for the first and last names.  The "Acronyms", "Non_Names" and "More_Nons" specifications help in removing extraneous terms from name fields and account titles so that the proper name can be extracted.

 

 

Records Import Section

 

The Custodian Script files contain five import sections, one each for Accounts, Transaction Blotter, Position Reconciliation, Security List, and Price History.  The first part of each records section contain some general specifications appearing as follows:

 

[##SECTION_NAME##]

FILE_SPECS=*.TRD      {filter identifying which files can be processed for this section}

FILE_HEADER=        {Identifier of file "Header" (#H) record, & specifies date}

GROUP_HEADER=        {Identifier of group header (#GH} record for files that contain more than one type of data}        

IF_HEADER=                {unused}

START_KEYWORD=        {Identifier indicating start of records to be read}

END_KEYWORD=            {Identifier indicating end of records to be read}

RECORD_ID=                {Identifier(s) & ID positions of lines to be read}

SKIP_ID=                {Identifier(s) & ID positions of lines to skip}

RECORD_LINES=1          {Lines per record}

{--SWITCH OVERRIDES HERE--}

DATE_FORMAT=MMDDYYYY

ISECID_TO_SYMBOL=OFF

<--FIELDS-->   {StartPos,Length (0=delimiter),A=Alpha or Number=Dcml

 

These specifications operate as follows:

 

Section Name - This identifies the section to Captools/net.  The section name must begin with "[##" and end with "##]" and contain one of the five section identifiers as follows: "ACCOUNT_RECORDS", "TRANS_BLOTTER", "POSITION_RECONCILE", "SECURITY_LIST", or "PRICE_HISTORY".  Examples of these are given later.  It is important that these identifiers are spelled exactly as indicated, otherwise the importing will fail for that section.

 

File_Specs - This is a filter that ensures that only files which pass the filter will be processed by this section.  This may be identical to the filter specified for these records in the Global Switches, or may be a subset of those filters.  In this example, it is a subset, because the global filter

 

File_Header - Some custodians use the first line of data in their file to specify information about the file contents.  Typically this may identify the type of data in the file, the date it was generated for, and maybe some other information.  This specification is used to identify the file header record.  This is done using a notation similar to FILE_HEADER="RHR001F"@1,24,6 (this is an actual specification for the file header for a DST transaction file.  The first part of the specification (up to the "@" ) is simply the text that will always appear in the file header line.  The first number after the "@" is the position in which that text will be found.  The last two numbers specify the text fragment that indicates the file date.  In this example the date starts at the 24th character and contains 6 characters.

 

Group_Header - Some custodians place more than one type of data in a given file, with each section of data preceded by a "Group Header".  This specification allows Captools/net to identify the start of a new type of data.  The same type of notation as for the file header is used, i.e. "identifying text" followed by "@" followed by the position that text appears in the data record.

 

If_Header - Currently not used.

 

Start_Keyword - This specifies a text contained in a record that triggers the importing of data.  The notation is "identifying text", followed by an "@", followed by the position of the identifying text in the record.  If there is a comma and a second number, e.g. "startdata"@1,2  then the data importing starts that number of lines after the start keyword is found, e.g. 2 lines down for this specification.  If no "Start Keyword" is specified than data is imported starting with the first record encountered.

 

End_Keyword - This specifies text contained in a record which signals the end of data importing.  It also uses the notation of "identifying text", followed by an "@", followed by the position of the identifying text in the record.  If no "End Keyword is specified, data is imported until the end of file is reached.

 

Record_Id - Many custodians provide data with multiple lines of data per record.  In this case the first record typically contains a unique identifier to indicate it is the first line in a record.  This specification is used to indicate to Captools/net how to recognize this record, using a notation similar to RECORD_ID="DFA001"@1, where the notation "DFA001" at the first position indicates the start of the record.  When a record Id is specified, Captools/net assumes all subsequent lines of data belong to that record until another line of data is encountered containing the record id in the specified position.  If no record Id is specified, then a record is assumed to contain the number of lines of data specified in the "Record Lines" specification (below).  

 

Multiple record Id's - In cases where more than one record identifier is possible, you can specify this using a notation like RECORD_ID="ABC|LMN|XYZ@1" if the identifiers are all in the same position.  This uses the "bar" character to separate the allowable record identifiers.  If the identifiers are at different locations in the record you can use the notation RECORD_ID="ABC"@1  | "XYZ"@25.

 

Skip_Id - This specification, using the same format as for the Record Id specification, indicates records which should be skipped during import.  This is sometimes necessary due to some custodians providing multiple records for a given transaction, only one of which might be needed by Captools/net.  If you have multiple "Skip Ids" you can specify them in the same manner as the for the multiple record Id's described above.

 

Record_Lines - This indicates the number of lines of data per record when a record Id (above) has not been specified.

 

Switch Overrides - Any settings in the "Global Switches" section can be overridden for a particular record import section, simply by repeating the switch in the "Switch Overrides" subsection with the override setting specified to the right of the equal sign.  In the example above, an override is specified for the date format.

 

Fields - The "Fields" sub-section contains specifications for import to all of the available Captools/net fields for the data tables applicable to that import section.  The specification follows the format of "Field Import Name", equal sign, Start Position, Length, and alpha or decimal specification.  E.g. "ACCOUNT_NUMBER=14,0,A" means that the account field is in the 14th position in the data and is an "Alpha" field.  The length specification can be zero if the delimit method is not "fixed", in which case all data is read into the variable until the next delimiter is encountered. Using a "U" in lieu of "A" forces the imported data to be upper case, using a "L" forces lower case except for leading characters, using a "D" indicates the field is a date field, and a "J" indicates a Julian date.

 

If the field is a numeric field, a typical specification for fixed position data could be "NET_AMOUNT=55,13,2" where this means the Net Amount field is found starting at the 55th position, has a length of 13 characters, and needs a decimal inserted before the second character to the right.  The length specification is only needed if the data is not delimited, and the decimal specification is only needed if the data does not contain decimal points (yes some custodians omit the decimal point!).

 

The Field Import Names that appear in the Fields specifications are not the same as the field names used in the Captools/net data base, but are rather mapped to the applicable fields during import.  Since we've endeavored to use self-evident names for the most part, we will not try to define all of these here, except for some which might be nonetheless obscure.  These will be defined following the applicable sections below in which they appear.  Finally, you should note from these examples, many fields typically have no specification applied.  This is because the custodian does not provide any equivalent data.  In these cases blanks are imported, except in the case of some numerical fields where a value can be computed from other numerical fields.

 

Multiple Field Name Instances - Sometimes data must come from or be combined from more than one custodian data field before it is put into the Captools/net destination field.  This is accomplished by specifying the destination field multiple times and using a prefix to indicate how the fields are  to be combined.

 

Blank or null field substitute - Precede the second and subsequent instance of the field with a "bar" character ("|"), e.g.:

 

SYMBOL=4,0,A

|SYMBOL=5,0,A  

 

In this case, the Symbol field is filled from data located at the 4th position, unless that field is blank, in which case the data is taken from the 5th position.  This sometimes is needed if the the 5th field is a Cusip field, and a blank is put in the symbol field if there is no ticker symbol for the security.   This notation can also be used on date and numeric fields, where a "0" is considered null for a numeric field.  (Note: Captools/net will also recognize a repeat field specification without any prefix as an instruction to replace a blank text field, i.e. the bar character is not needed if the field is non-numeric field).

 

Combine fields - Precede the second and subsequent instance of the field with a "+" character, e.g.:

 

ACCOUNT_TITLE=19,0,L

+ACCOUNT_TITLE=20,0,L

 

In this case the contents of the data in field 20 will be appended to field 19 before being placed in Captools/net's account title field.  If the field is a numeric fields the results will be numerically added.

 

Multiply or divide - Precede the second and subsequent instance of a numeric field with a "*" character to indicate multiplication, or a "/" character to indicate division.

 

User Defined Factor - Sometimes a factor needs to be defined, e.g. factor of "100" or "0.01", usually to be used with the multiply or function.  This can be accomplished as follows:

 

NET_AMOUNT=10,0,0

*NET_AMOUNT=  *=0.01  IF="MB"@30

 

In this example, the numeric content imported by the first specification is multiplied by the user defined factor of 0.01 specified in the second instance.  This second instance is user defined because of the notation "*=" followed by the user defined number.  An "additional" conditional "IF" specification also is specified in this example.  These are discussed further in the "Field Additional Specifications" below.

 

User Defined Text - Sometimes you want to fill a text field with fixed text, most often the "data source" field.  This notation is similar to that used for the User Defined Factor above:

 

DATA_SRC=  *=SCWB

 

Line Number Specification - Some custodians use multiple data lines per record.  To indicate that data for a particular field is to be imported from the 2nd line you would use a notation as follows:

 

DATE=4,0,D  #2      where the "#2" indicates to pick up the data from the 4th field in the second line of data.  For the 3rd, 4th, etc. lines you would use "#3", "#4", etc.

 

Header Line - If data for a particular field is to be picked up from a "File Header" or "Group Header" record, you would follow the field specification with "#H" for the File Header and "#GH" for the Group Header.

 

Trailer Line - Some custodians also include an unformatted, "trailing" descriptive line which follows the formatted lines of data.  The notation "#T" is used for data to be extracted from that line.

 

Variable Trailer Line - A custodian may also use one or more formatted record lines which follow the first line of data in a record, but only if needed for a records and in no discernable sequence, to specify additional information.  The notation "#V" is used for these lines, but the import specification must include at least one "IF=" condition to allow Captools/net to identify the correct line from which to extract data. The TDWaterhouse Canada interface uses this notation for transaction imports, for example:

 

COMMISSION=6,0,2 #V IF="CM"@4

ACCR_INTR=6,0,2 #V IF="AI"@4 !IF="T-BILL" !IF="MNY MKT"

 

These specifications indicate that the commission and accrued interest are to be extracted from a line following the first line of the record.  In the case of the commission, the applicable line is identified by "CM" appearing in the 4th field, with the actual commission amount extracted from the 6th field, with a decimal inserted 2 characters from the right.

 

 

Field Additional Specifications - In many cases in the following examples you will see additional information to the right of the "Start Postion, Length, Alpha/Decimal" specification.  These usually either modify the imported data or make the import "conditional".

 

"IF=" - This specification allows you to indicate that you want to import data from the specified location only if the "IF=" specification is satisfied.  The contents to the right of the "=" sign must be in the format "text"@location where the "text" is usually the contents of a field or text contained within a field and the "location" is the position of the field in the data record.  If the data is delimited, the position is the field number, if the data is non-delimited, it is the absolute location in terms of character count from the start of the record.  If the "IF" notation is preceded by a "!", then it is interpreted as "not", i.e. the field contents is not imported if the condition is met.

 

"X="  (Translate) - This specification allows you to indicate that the imported data is to be "translated" using rules contained in another section of the IDI file.  The name of that section is specified at the right side of the equal sign.  Thus the notation "TAC=4,0,A  X=**TACS**" indicates that the data imported from field #4 are to be processed through the translate table with the section header "**TACS**".  Translate table rules are covered later in this topic.

 

"PURGE=" - This specification simply indicates that the specified characters are to be removed from the imported data.  This is most often needed when for dates or for getting rid of prefixes or suffixes from numeric data (e.g. "$", "%").  The characters to be purged follow the equal sign in the format <"/"> where only the characters between the double quotes are purged.

 

"FMT=" - This allows you to specify a field specific date format, e.g. "FMT=YYYYMMDD" means the first 4 characters are the year, the next 2 the month, and the last 2 the day.

 

"@=" and ";=" - This enables you to specify that you want to pull date or numeric data from within a specified text field, usually a description field.  The text fragment to the right of the "@=" specifies a "tag" where Captools/net is to look for the data.  Thus if the notation is "AUXDATE=18,0,D @="VSP" FMT=MM/DD/YYYY" then the AuxDate will be pulled from the 18th field, starting from the first character following the word "VSP" and using the date format specified by the "FMT=" specification.  To specify the end of data embedded in a field use the ";=" notation to specify a tag which ends the desired data.

 

">=" and "<=" - These allow you to specify the start and end position of data embedded in a field, with ">=" specifying the start and "<=" specifying the end position.

 

"&N=N*"  - This enables you to multiply a numeric field by a constant.  This is often used to "turn a sign around" on a number, where the custodian is using a different sign convention.  An example would be NET_AMOUNT=10,0,0 &N=N*-1 IF="MEXP"@4, where the result at the 10th field is multiplied by -1 if the notation "MEXP" is found at the 4th field.

 

";="  - This allows you to truncate the data imported at the character(s) indicated to the right side of the sign.  (A semicolon is to the left of the sign).  This is useful in importing description fields that would otherwise contain extraneous information.  For example,  DESCRIPTION=14,0,A  ;="(" will truncate the description at the point where it encounters the left parentheses.

 

"FILEDATE" - This specifies that a date field is to be populated by the "file modified" date as read from the hard drive.  Sometimes this is necessary if the custodian does not provide dates within the data or in the filename.  The format for this is simply: DATE=0,0,D FILEDATE.

 

"*=" - This allows you to specify that a static text or fixed number be placed in the field.  E.g. the notation "*=10  IF="S"@5 would place the number 10 in the field if the character "S" was in the 5th field.

 

"NOT_FOT" - This applies only to the PRICE_FACTOR field and indicates that Captools is to NOT apply "Factor of Ten" rules to the imported value.

 

"BETWEEN=" - This allows you to specify that data be extracted from between characters in a field.  Thus the notation 'BETWEEN="|"' would extract only the text between the "pipes".

 

 

Account Records Import Section

 

The Account Records import section is identified by the notation "[##ACCOUNT_RECORDS##]" (must be exact!).  It imports data into the Captools/net "Clients" and  "Accounts" records, and contains specifications as follows.

 

**********************

[##ACCOUNT_RECORDS##]

FILE_SPECS=*.TRD

FILE_HEADER=        {Identifier of "Header" (#H) record, & specifies date}

GROUP_HEADER=                

IF_HEADER=

START_KEYWORD=        {Identifier indicating start of records to be read}

END_KEYWORD=     {Identifier indicating end of records to be read}

RECORD_ID=        {Identifier(s) & ID positions of lines to be read}

SKIP_ID=        {Identifier(s) & ID positions of lines to skip}

RECORD_LINES=1          {Lines per record}

{--SWITCH OVERRIDES HERE--}

DATE_FORMAT=MMDDYYYY

EXCLUSIONSFILE=DEADACCOUNTS.TXT

<--FIELDS-->   {StartPos,Length (0=delimiter),A=Alpha or Number=Dcml

ACCOUNT_NUMBER=14,0,A

PF_FILENAME=

FIRST_NAME=3,0,L

LAST_NAME=2,0,L

ACCOUNT_TITLE=19,0,L

SALUTATION=

PRIMEADDRESS_1=4,0,L

PRIMEADDRESS_2=5,0,L

PRIMEADDRESS_3=6,0,L

PRIMEADDRESS_4=10,0,L

PRIMESTATE=11,0,A

PRIMEPOSTAL=12,0,A

WORKADDRESS_1=

WORKADDRESS_2=

WORKADDRESS_3=

WORKADDRESS_4=

WORKADDRESS_5=

WORKSTATE=

WORKPOSTAL=

OTHERADDRESS_1=

OTHERADDRESS_2=

OTHERADDRESS_3=

OTHERADDRESS_4=

OTHERADDRESS_5=

OTHERSTATE=

OTHERPOSTAL=

HOME_PHONE=

WORK_PHONE=

OTHER_PHONE=

HOME_FAX=18,0,0

WORK_FAX=

OTHER_FAX=

E_MAIL_A=

E_MAIL_B=

DOB=34,0,D PURGE=<"/"> FMT=MMDDYYYY

TAX_ID=13,0,0

OPEN_DATE=24,0,D PURGE=<"/"> FMT=MMDDYYYY

CITIZENSHIP=

RESIDENCY=

REP_ID=

ACCOUNT_TYPE=

CASH_MARGIN=

DISCRETIONARY=

SALES_PROCEEDS=

DIV_PROCEEDS=

SEC_DELIVERY=

TAX_WITHHOLD=

JOINT_TENANT=

ACCT_SYMBOL=

SUBACCOUNT=

SUBACCTDESCR=

BASECURRENCY=

 

 

Clarifications

 

PF_FILENAME - This field is mapped to the "Short Name" field in the Accounts table.  If the specification is missing or blank to the right of the equal sign, then the Short Name will be constructed from the First and Last names.

TAX_ID - This field goes into Captools/net "Client Id" field.

SUBACCOUNT - If this field is populated, then a sub-account record is created in Captools/net for this account.

 

Account-Specific Additional Specifications

 

FIRSTMIONLY - Specifies to extract only the first name, middle intial from the import field.

LASTONLY - Specifies to extract only the last name from the import field.

NOADDR - Specifies to exclude the address from the import field.

NOTITLE  - Specifies to exclude titles from the import field.

 

 

Transaction Blotter Import Section

 

The Transaction Blotter import section is identified by the notation "[##TRANS_BLOTTER##]" (must be exact!).  It imports data into the Captools/net "Transaction Blotter" records, and contains specifications as follows.

 

**********************

[##TRANS_BLOTTER##]

FILE_SPECS=*.TRN,*.INI

FILE_HEADER=

GROUP_HEADER=

IF_HEADER=

START_KEYWORD=

END_KEYWORD=

RECORD_ID=

SKIP_ID=

RECORD_LINES=1

{--SWITCH OVERRIDES HERE--}

DATE_FORMAT=MMDDYYYY

<--FIELDS-->

ACCOUNT=3,0,A

ACCT_ID=

CASHMARGIN=

DATE=8,0,D  PURGE=<"/"> FMT=MMDDYYYY

TIME=

TAC=4,0,A  X=**TACS**,**TAC2**

SYMBOL=6,0,U X=**SYMBOLS**

QUANTITY=9,0,0 !IF="MF"@7

QUANTITY=10,0,0 IF="MF"@7

QUANTITY=9,0,0 IF=TAC=RCV

QUANTITY=9,0,0 IF="INT"@4

QUANTITY=9,0,0 &N=N*-1 IF="MFREV"

QUANTITY=9,0,0 IF="CASH"@15

TR_PRICE=

COMMISSION=12,0,0

OTHER_FEES=13,0,0

ACCR_INTR=17,0,0

TAX_WHELD=

HOLD_PERIOD=0,0,0 *="60" IF="5YEAR"

LOCAL_AMT=

EXCH_RATE=

NET_AMOUNT=10,0,0

NET_AMOUNT=10,0,0 &N=N*-1 IF="MEXP"@4

DESCRIPTION=18,0,L

CUSIP=6,0,0

SOURCE=7,0,A

DESTINATION=15,0,A

BDMEMO=18,0,A

QTYSIGN=

AMTSIGN=

SECTYPE=7,0,A

SETTLE_DATE=14,0,D PURGE=<"/"> FMT=MMDDYYYY

SALES_REP_ID=

ORDER_DATE=

ORDER_TIME=

LIMIT_PRICE=

EXPIRES=

PRC_FACTOR=

HOLD_PERIOD=

REVERSAL=5,0,A  IF="Y"@5

AUXDATE=18,0,D @="VSP" FMT=MM/DD/YYYY

AUXAMOUNT=                   {cost basis relief amount}

POOLFACTOR=                  {mortgage backed pool factor}

DESCRIPTION2=                  {second description}

TRNSCURRENCY=                {transaction currency}

BASECURRENCY=                {base currency}        

LOTTYPE=                        {custodian lot type}

ImportSource=                         {import file identifier - will be filename unless otherwise specified}

ClientID=                {client identifier}

CliLastName=               {client last name}

CliFirstName=                 {client first name}

 

 

 

Clarifications

 

SOURCE - Some custodians designate a field to indicate the source of funds for a transaction.  This field is meant to import the contents of that field, which usually is used as a condition qualifier in the translation of transaction codes, covered below.

DESTINATION -  Some custodians designate a field to indicate the destination of funds for a transaction.  This field is meant to import the contents of that field, which usually is used as a condition qualifier in the translation of transaction codes, covered below.

BDMEMO - Sometimes custodians provide an additional descriptive field that provides additional information about a transaction that is useful in translating the transaction code.  This import field is used to temporarily store this information for this purpose, but does not show up in the resulting imported transaction blotter record.

QTYSIGN - This field is used to temporarily import the sign of the quantity field provided by the custodian to be used as a conditional translation modifier for the TAC translation (see examples below).

AMTSIGN - This field is used to temporarily import the sign of the amount field provided by the custodian to be used as a conditional translation modifier for the TAC translation (see examples below).

POOLFACTOR - This is intended for CMO type pool factors, if available in the source transaction data.

 

 

Position/Reconcile Import Section

 

The Position/Reconcile import section is identified by the notation "[##POSITION_RECONCILE##]" (must be exact!).  It imports data into the Captools/net "Reconciliation" records, and contains specifications as follows.

 

**********************

[##POSITION_RECONCILE##]

FILE_SPECS=*.POS

FILE_HEADER=

GROUP_HEADER=

IF_HEADER=

START_KEYWORD=

END_KEYWORD=

RECORD_ID=

SKIP_ID=

RECORD_LINES=1

{--SWITCH OVERRIDES HERE--}

<--FIELDS-->

DATE=0,0,D FILEDATE

ACCOUNT=1,0,0

SYMBOL=4,0,A  X=**SYMBOLS**

QUANTITY=5,0,0

|QUANTITY=6,0,0  IF="MO"@3

|QUANTITY=6,0,0  IF="MF"@3

PRICE=

ACCR_INTR=

LOCAL_VALUE=

EXCH_RATE=

VALUE=6,0,0

REP_ID=

CASHBAL=                 {cash balance}

ACCT_ID=    

Cusip=                      

LongSymbol=                {long form option symbol}

ReconLongShort=          {long vs short identifier}

ReconCostDate=        {reconcile cost date}

ReconCost=                  {reconcile cost}

ReconLotMethod=         {reconcile lot methodology}

ReconWashed=             {loss wash indicator}

 

 

Security Import Section

 

The Security import section is identified by the notation "[##SECURITY_LIST##]" (must be exact!).  It imports data into the Captools/net Security Id and Statistic records as applicable, and contains specifications as follows.

 

**********************

[##SECURITY_LIST##]

FILE_SPECS=*.SEC

FILE_HEADER=

GROUP_HEADER=

IF_HEADER=

START_KEYWORD=

END_KEYWORD=

RECORD_ID=

SKIP_ID=

RECORD_LINES=1

{--SWITCH OVERRIDES HERE--}

<--FIELDS-->

SYMBOL=1,0,U X=**SYMBOLS**

CUSIP=

TYPE=2,0,U X=**TYPES**

GROUP=

CLASS=

CURRENCY=

DESCRIPTION=3,0,U

TAXLOTMETHOD=

MARGINFACTOR=

EXDIVDATES=

PAYMENTDATES=

PRICEFACTOR=2,0,U  X=**PRICEFACTORS**

TAXWITHHFACTOR=

LATESTPRICEDATE=

LATESTPRICE=

DIVINTRATE=11,0,0        

STCGRATE=

LTCGRATE=

ROCRATE=

LASTDIVDATE=

LASTDIVRATE=

ISSUEDATE=7,0,D FMT=MM/DD/YYYY

MATURITYDATE=4,0,D FMT=MM/DD/YYYY

CALLDATE1=5,0,D FMT=MM/DD/YYYY

CALLDATE2=

MATURITYPRICE=

CALL1PRICE=6,0,0

CALL2PRICE=

GNMAFACTOR=

ACCRUALMETHOD=

EARNINGSPERSHR=

SALESPERSHR=

DEPRECPERSHR=

BOOKVALPERSHR=

CASHPERSHR=

CURASSETSPS=

CURLIABPS=

DEBTASSET=

SHARESOUTST=

NUMSHAREHOLDERS=

INSIDERPERCENT=

FCEARNINGSPERSHR=

FCSALESPERSHR=

MUTFUNDFIRMID=

MUTFUNDNUMBER=

MUTFUNDENTRYLOADP=

MUTFUNDEXITLOADP=

MUTFUNDEXITDAYS=

MUTFUNDEXPENSEP=

MUTFUNDMINHOLDING=

MUTFUNDMINHOLDPRC=

MUTFUNDREP12B1=

DOWNMKTRATING=

UPMKTRATING=

ALLMKTRATING=

OPTIONTYPE=

ASSETSYMBOL=

EXPIREDATE=

CONTRACTPRICE=

NEWSYMBOL=            {use for new symbol assignment}

NEWCUSIP=                {use for new cusip assignment}

PAYMENTFREQ=        {dividend/interest payment frequency}

LONGSYMBOL=          {long form option symbol}

ISECID=                  {for custodian internal security identifier to be used when ISECID_TO_SYMBOL lookup is activated for other record types}

 

Clarifications

 

Note in the above example that most fields are not populated, except for symbol, cusip, description and expiration dates.   This is because most custodians do not provide this fundamental information.

 

 

Price History Import Section

 

The Price History import section is identified by the notation "[##PRICE_HISTORY##]" (must be exact!).  It imports data into the Captools/net Security History records, and contains specifications as follows.

 

**********************

[##PRICE_HISTORY##]

FILE_SPECS=*.PRI

FILE_HEADER=

GROUP_HEADER=

IF_HEADER=

START_KEYWORD=

END_KEYWORD=

RECORD_ID=

SKIP_ID=

RECORD_LINES=1

{--SWITCH OVERRIDES HERE--}

<--FIELDS-->

DATE=3,0,D FMT=MM/DD/YYYY

TIME=

SYMBOL=1,0,A X=**SYMBOLS**

OPEN=

HIGH=

LOW=

CLOSE=4,0,0

OPEN_INT=

VOLUME=

PRICEMULT=

POOLFACTOR=5,0,0

EXDATE=                    {used for distribution records}

DIVS=                        {used for distribution records}

SPLITS=                 {used for distribution records}

LT_CGDS=                 {used for distribution records}

ST_CGDS=                 {used for distribution records}

ROCS=                         {used for distribution records}

REINV_PRICE=                 {used for distribution records}

EXCH_RATE=                 {used for distribution records}

END_DATE=                 {used for distribution records}

PRD_SIZE=                 {used for financial stat records}

RPS=                         {used for financial stat records}

EPS=                         {used for financial stat records}

DPS=                         {used for financial stat records}

BVPS=                         {used for financial stat records}

CPS=                         {used for financial stat records}

CFPS=                 {used for financial stat records}

CURRATIO=        {used for financial stat records}  

DARATIO=                 {used for financial stat records}

SHARES_OUT=         {used for financial stat records}

DATA_SRC=  *=EXPL

DESCRIPTION=           {used when a new security entry required in security list}

 

 

Clarifications

 

PRICEMULT - This field is a price multiplier which is immediately applied to the "Open/High/Low/Close" figures imported by this script.

POOLFACTOR - This field is meant for CMO type pool factors.  These are imported into the "OpenInt / Factor" field in the Captools/net price history records, and is pulled into the exchange rate (X-Rate) field of the portfolio position records when the portfolio is valued.

OPEN_INT  - This is "open interest" (usually unavailable).

DIVS - Dividend amount per share

SPLITS - Shares received per existing share

LT_CGDS - Long term capital gains distribution per share

ST_CGDS - Short term capital gains distribution per share

ROCS - Return of Capital per share

REINV_PRICE - Reinvestment price

EXCH_RATE - Exchange rate when applicable

END_DATE - Period ending date for fundamental information

PRD_SIZE - Period size in months for fundamental information

RPS - Revenue per share

EPS - Earnings per share

DPS - Debt per share

BVPS - Book value per share

CPS - Cash per share

CFPS - Cash flow per share

CURRATIO - Current Ratio

DARATIO - Debt/Asset Ratio

%PRICEFACTOR  -   If this notation is placed to the right of the ClosePrice specification the price will be divided by the price factor in the security record

 

Custodian Open Lot Section

 

This section imports custodian provided cost information in the form of open lots.  Not all custodians may provide such information.  In this section the import field identifiers are identical to the database field identifiers, hence the prefix "COL_".

 

**********************

[##OPEN_LOTS##]

FILE_SPECS=CRS*.ULN,CRS*.ULT

FILE_HEADER=

GROUP_HEADER=

IF_HEADER=

START_KEYWORD="H3",1,1

END_KEYWORD=

RECORD_ID="DL"@1

SKIP_ID=

RECORD_LINES=1

{--SWITCH OVERRIDES HERE--}

DELIMIT_METHOD=PIPE

 

COL_RepId=

COL_AsOfDate=7,0,D   FMT=CCYYMMDD

COL_CustodianId=2,0,A

COL_CustodAcctNum=6,0,A

COL_Symbol=10,0,U

COL_Symbol=11,0,U    {cusip}

COL_Symbol=14,0,U     {isin}

COL_Symbol=15,0,U     {sedol}

COL_Symbol=13,0,U     {schwab issued id}

COL_OpenQuantity=23,0,0

COL_MktValue=26,0,0

COL_AccruedInt=27,0,0

COL_AcquisDate=28,0,D  FMT=CCYYMMDD

COL_PurchDate=29,0,D  FMT=CCYYMMDD

COL_OrigPrice=30,0,0

COL_PurchPrice=33,0,0

COL_UnAmortCost=32,0,0

COL_AmortCost=34,0,0

COL_UnAmortGain=

COL_AmortGain=36,0,0

COL_DaysHeld=37,0,0

COL_HoldTerm=38,0,A

COL_CBFullyKnown=39,0,A

COL_CBtype=40,0,A

COL_CBtaxable=41,0,A

COL_CBcertified=42,0,A

COL_OrigFaceVal=43,0,0

COL_LotCostMethod=44,0,A

COL_EquivCTLot=

COL_Washed=45,0,A

COL_CustodTAC=25,0,A

COL_LongShort=24,0,A

COL_SecType=

COL_ProdCode=7,0,A

COL_CategCode=8,0,A

COL_TaxCode=9,0,A

 

 

 

Custodian Realized Gains Section

 

This section imports custodian provided year to date realized gains information.  Not all custodians may provide such information. In this section the import field identifiers are identical to the database field identifiers, hence the prefix "CRG_".

 

*********************

[##REALIZED_GAINS##]

FILE_SPECS=CRS*.RTY

FILE_HEADER=

GROUP_HEADER=

IF_HEADER=

START_KEYWORD="H3",1,1

END_KEYWORD=

RECORD_ID="DT"@1

SKIP_ID=

RECORD_LINES=1

{--SWITCH OVERRIDES HERE--}

DELIMIT_METHOD=PIPE

<--FIELDS-->

 

CRG_RepId=

CRG_AsOfDate=5,0,D   FMT=CCYYMMDD

CRG_CustodianId=2,0,A

CRG_CustodAcctNum=6,0,A

CRG_Symbol=11,0,U

CRG_Symbol=12,0,U   {cusip}

CRG_Symbol=15,0,U   {isin}        

CRG_Symbol=16,0,U   {sedol}

CRG_Symbol=14,0,U   {schwab issued id}

CRG_CloseDate=24,0,D  FMT=CCYYMMDD

CRG_CloseQuantity=25,0,0

CRG_Proceeds=28,0,0

CRG_AcquisDate=30,0,D  FMT=CCYYMMDD

CRG_PurchDate=30,0,D  FMT=CCYYMMDD

CRG_OrigPrice=

CRG_PurchPrice=32,0,0

CRG_UnAmortCost=31,0,0

CRG_AmortCost=33,0,0

CRG_UnAmortGain=37,0,0

CRG_AmortGain=39,0,0

CRG_DaysHeld=35,0,0

CRG_HoldTerm=36,0,0

CRG_CBtype=42,0,A

CRG_CBFullyKnown=41,0,A

CRG_ProcsFullyKnwn=46,0,A

CRG_RGfullyKnown=45,0,A

CRG_RGtaxable=43,0,A

CRG_RGcertified=44,0,A

CRG_OrigFaceVal=47,0,0

CRG_LotCostMethod=48,0,A

+CRG_LotCostMethod=49,0,A

CRG_EquivCTLot=

CRG_Washed=50,0,A

CRG_WashUnAmortCost=51,0,A  {actually "washedUnAmortCost"}

CRG_WashAmortCost=52,0,A     {actually "washedAmortCost"}

CRG_CustodTAC=27,0,A

CRG_LongShort=26,0,A

CRG_SecType=7,0,A

CRG_ProdCode=8,0,A

CRG_CategCode=9,0,A

CRG_TaxCode=10,0,A

 

 

Transaction Code Translate Sections

 

One of the most critical tasks in creating a successful custodian import script is properly setting up the TAC code translation table which determines the TAC code imported into Captools/net transaction blotter records.  The available Captools/net translation codes are documented at Transaction Codes.  The transaction code translate table is normally indicated by the notation "**TACS**" in the section header.  Below that are a list of  "assignment" specifications that translate the code provided by the custodian (on the left of the equal sign) into the proper code for Captools/net (on the right of the equal sign), e.g.  e.g.:  TAC=4,0,A  X=**TACS**

 

[**TACS**]

buy=BUY

sell=SLL

div=DV+

int=IN+

etc.

 

Unfortunately there is rarely a one to one relationship between the custodian source codes and the Captools/net TAC codes as in the above example.   Accordingly Captools/net recognizes "conditional" translations, meaning that the translation occurs only if the condition indicated between the curly brackets on the right side of the translation.  These conditions use "TAC Translate Conditional Keywords".

 

TAC Translate Conditional Keywords

 

TAC conditional keywords are variables which contain values imported with the record.  They are used in the conditions that appear to the right of the "assignment" statement that translates the custodian's code to the Captools/net TAC code.  If the condition is not satisfied the value that was previously assigned is retained.  The available keywords are as follows:

 

SYMB  - Contains the ticker symbol that was imported into the transaction blotter Symbol field.

MMO - Contains the contents imported into the "BDMEMO" field.  If nothing was imported into that field this is assigned the contents of the field assigned to the DESCRIPTION specification.

DSCR - Contains the contents of the field assigned to the DESCRIPTION specification

DEST - Contains the contents of the field assigned to the "DESTINATION" import specification.

SRC - Contains the contents of the field assigned to the "SOURCE" import specification.

QSIGN - Contains the contents of the field assigned to the "QSIGN" import specification.

ASIGN - Contains the contents of the field assigned to the "ASIGN" import specification.

TN  - Contains the contents of the trailing record.  This is only applicable if the "TRAILER_NOTE" specification in the "Global Settings" is turned "On".

A - References the sign of the transaction amount as imported, before the transaction code is applied (this can change the final sign).

Q - References the sign of the transaction quantity as imported, before the transaction code is applied (this can change the final sign).

 

Conditional TAC translations are predicated on the notion that a given custodian source code needs to be translated different ways depending upon other factors in the record being imported.  Thus, frequently one will see a series of translations similar to the following:

 

CDT=DPF

CDT=-DPF {MMO=REVERS}

CDT=-DPF {MMO=CANCEL}

CDT=RCV {A=0}

 

The first assignment, "CDT=DPF" is the default assignment.  All subsequent assignments for the same source ("CDT") have conditions attached.  The first of these conditional assignments whose condition is met will be the overriding assignment.  Thus in this example, if the BDMEMO field (represented by the shorthand "MMO") contains the word "cancel", then the third assignment will prevail and the translation of CDT will be "DPF", but with a negative amount field since the "DPF" was preceded by a negative sign, meaning that the numeric values in the transaction will have reverse the normal sign.

 

Secondary TAC Translations

 

Sometimes more than one translation pass is required to achieve the required results.  This is accomplished by specifying more than one translation table reference in additional specifications of a given field.  E.g.  e.g.:  TAC=4,0,A  X=**TACS**,**TAC2**  where the second translation table will contain translations where the left side contains results from the first pass of translations.

 

[**TAC2**]

DV+=DV+

DV+=DV+ {SRC=MF}

DV+=DV+ {SRC=EQ}

DV+=DRI  {Q<>0}

DV+=DRI {MMO=REINVEST}

NQD=NQD

NQD=NQR {Q<>0}

BUY=BUY

 

 

Symbols Translations

 

For some custodians it may be necessary to translate symbols as they are imported. This is accomplished by specifying the symbol translation table in the "additional specifications" for the symbols field, e.g.  SYMBOL=5,0,A  X=**SYMBOLS**, where the translation table provides the desired assignments.

 

[**SYMBOLS**]

CASH01=(CASH)

CASH02=**OMIT***

CASH03=(CASH)

CASH04=**OMIT***

 ~

IASMRGN=(CASH)

IASSHRT=(CASH)

IASINCM=(CASH)

IASLEGL=(CASH)

IASREOR=(CASH)

IASDVSP=(CASH)

IASEXPS=(CASH)

IASFNTX=(CASH)

IASNRTX=(CASH)

IASTFTX=(CASH)

IASCASH=

IASCASH=(CASH) {DSCR=Initial Position}

WHOCMF=WHOCMF$

WHOMUN=WHOMUN$

WHOWNB=WHOWNB$

A000103=WHOCMF$

A000104=WHOUSG$

A000567=WHOWNB$

A000455=TDWCZ$

 

In the above examples, a number of symbols are either translated to the standard Captools/net symbol for cash "(CASH)" or to an alternate notation for a money market fund.

 

Translations may be used for almost any field, with security "type" and "price factor" translations not uncommon with typical tables shown below.

 

Type Translations

 

[**TYPES**]

MU=MF

MB=FI

STK=CS

 

Price Factor Translations

 

[**PRICEFACTORS**]

EQ=1

MU=1

MB=0.01

FI=0.01

OP=100

OT=1

MF=1

UI=1

GB=0.01