ThomsonDataLoader Utility

The ThomsonDataLoader is a utility program that loads flat file data, provided by Thomson Financial, into the afstrade database.  Thomson provides mostly customer position data, however, they also supply all associated security and account information for these positions.  

This program loads two general types of information

  • The Thomson Descriptive Data is mapped to, and loaded into, the afstrade lookup tables.
  • The Thomson Primary and Supplemental Files are mapped to and loaded into afstrade reference (e.g. product and other party tables) and position tables.

The ThomsonDataLoader is actually a very generic interface, that has the format of the data defined through two separate configuration files 1) the lookup configuration file and 2) the primary configuration file.  The names of these files is specified in the afstrade.ini file (see below).  This application also requires the afstrade.ini file to determine to which database records are saved.

Configuration files

This program uses two configuration files to define the format of the data to be imported. If the format of the Thomson data is changed or enhanced, it is the intent that only these configuration files need to be changed (as opposed to having to change this application).  The format of the Thomson data files is defined in the Fixed Income Datafeed Manual from Thomson Financial.   

The individual configuration files are validated when the program starts to identify any potential errors, which are reported back to the user.  The two configuration files are described next.

Lookup Configuration File

Lookup files contain Thomson Lookup data, and are loaded to later map Thomson codes to AFS codes.  All lookup data is loaded into the lookups_ext table in the afstrade database.

The lookup configuration file contains one line for each Thomson Lookup file.  The format of a line in the lookup configuration file is defined as:

<afs_table_name>, <thomson_filename>, <start_position>, <field_width>, <description_start>, <description_width>

afs_table_name:  the name of the lookup table in the afstrade database
thomson_filename: the name of the descriptive file
start_position: the beginning position of the code field in the data file
field_width: the width of the code field in the data file
descriptive_start: the beginning position of the description field in the data file
descriptive_width: the width of the description field in the data file

Example lookup configuration file:

opsubtype, acct_type.txt, 3, 2, 6, 20
posnform, bond_form.txt, 3, 2, 6, 50
insuran, bond_ins.txt,3, 2, 6, 30

Primary Configuration File

This configuration file defines the format of the Thomson Primary and Supplemental Files and how this data is mapped to afstrade tables.  This file is broken up into blocks, each block defining how data from a file is loaded into an afstrade table.  The format of a block is as follows:

Line 1: [<afs table name>]
Line 2: <thomson filename> - <can_insert>
Lines 3,4...: <afs_column_name>, <is_primary_key>, <field_start_pos>, <field_width>, <callout_function>, <thom_lookup_table>

<afs table name>: the name of the afstrade database table into which the data is to be loaded.  Note this table name is enclosed in square brackets.
thomson_filename: the name of the Thomson data file (see FileDirectory below)
<can_insert>: defines if the records in the data file can be inserted (value = 1) or only updated (value = 0).
afs_column_name: the column name (in <afs table name>)
is_primary_key: defines if this field is part of the table's primary key (1 = True, 0 = False)
field_start_pos: the beginning position of the corresponding field in the data file
field_width: the width of the field in the data file
callout_function: a specific function written to convert the data in the field.  For example, all cusips come in as 8 characters.  We have a function that computes the 9th digit (check digit) for this field.  This function also can provide a default value where the data is not passed in.  For this example, there is a function which returns the date and time that this record was modified.
thom_lookup_table: maps the data in this field to the afs_code in the lookups_ext (mapping) table, which was loaded in the previous section.

For example

[opparent]
mgmtco.txt - 1
parent_code, 1, 3, 8, N/A, N/A
parent_name, 0, 12, 40, N/A, N/A
last_update, 0, 1, 1, Last_Update, N/A
last_update_id, 0, 1, 1, Default_User, N/A

[oppardtl]
mgmtco.txt - 1
parent_code, 1, 3, 8, N/A, N/A
address_line_1, 0, 83, 40, N/A, N/A
address_line_2, 0,134, 40, N/A, N/A
city, 0,185, 25, N/A, N/A
state, 0, 211, 2, N/A, state.txt
zip_code, 0, 214, 30, N/A, N/A
country, 0, 245, 3, N/A, N/A
voice_phone, 0, 249, 30, N/A, N/A
fax_phone, 0, 280, 20, N/A, N/A

afstrade.ini

Other than the database parameters, the following entries are required for the program:

[ThomsonDataLoader]
FileDirectory=C:\Temp\Thomson_Data ;the directory where the data files reside
SchemaFileName=ThomsonSchema.cfg ;the Primary configuration file
LookupFileName=LookupSchema.cfg ;the Lookup configuration file

Running the Application

After double clicking on the ThomsonDataLoader icon, the main form is displayed.  

To process the Thomson data, simply click on the Yes button and select the file to process. If the Insert Thomson Lookups flag is set, this program will validate the lookup configuration file.  The lookups are then loaded into the database, and compared to the existing records.  If there are any records added, the user is asked if they want to edit the lookups.  If the answer is OK, the following form opens up to allow the user the modify the mapping:

Only the AFS Code value is editable.  When complete, the user should select the Save button to write the changes to the database.  Selecting Revert will discard any changes.  

If the Insert Primary Files flag is selected, the primary configuration file is first validated.  Each block in the configuration file is then processed, where each record in the data file is read, parsed, and then inserted directly into a specified database table (only for the can_insert case).  If the record already exists, the program updates the record. If the block is set to update only, the insert case is bypassed and the database record is updated.

The progress bars indicate the progress of both the individual data file and the overall load progress.  A log file is also generated and saved to the file system.

The process can be paused or stopped at any time by selecting the Cancel button.

Notes

  • The database design from SDesigner provides the complete list of tables and available column names for each table.
  • Any line in the configuration file that is blank, or begins with a semi-colon (;) is ignored.

Release Notes