0904 Trade Reconciliation

• Background

When replacing a settlement system, we make sure that the new one is able to handle the existing business correctly. Operational wise, it means work-flow remains familiar and the efficiency improves. Technical wise, it means the system takes the same input and produce the same output with a higher speed.

To make sure the new system books produces the same set of data, our PM had a good plan.

1. setup the base-camp interfaces to access the same reference data as the old system.

2. split the frond/middle office data stream into two copies, feed them into the two system currently, run the two system parallel for a period and compare trades on a day to day basis.

3. once we resolve all issues in the new system and become sufficiently confident, we remove the old system from the picture.

Reconciliation is critical. It discovers issues, provides accountable bug reports, and offers numeric proof of how prepared the new system is.

• The two level reconciliation work flow

During the parallel running phase, the details of a trade should be compared to their counter party in the old system. A detailed reconciliation is therefore needed to ensure the 70+ columns.

Level 2 exists because that the Intellimatch team refuses to reconcile data with more than 70 columns. Ironically, when implementing level 2 in Excel/VBA, we know we wouldn't need level 1 at the first place. Nevertheless, as any corporation, level 1 still remains as "a process abiding to existing infrastructure" and costs more than my yearly salary for nothing.

The level 2 rec program was implemented with Perl scripting and VBA.

• Perl, to mark the differences in CSV

The Perl script takes the matched.csv file from Intellimatch. The matched file shows pairs of unique trade IDs from the two systems that math.

1. For each pair, the Perl script find trade line A from the old system file, and line B from the new system file.
2. The two lines are spitted into fields, each of which are then cleaned and formatted properly so that they can be directly compared. The charges are even ordered, so that certain charge only appear in certain columns.
3. After ordering, cleaning and formatting the fields, fields are compared as strings. If they differs, a "@" mark is noted in the match result for the difference.

1. Line A, B, and the match result row are then write into the output file in sequence. e.g. user can see the following rows for one pair of trades:

• VBA, for advanced analysis

The Perl output has everything about the matches and difference, but it's hard for user to read. And the match/diff it self doesn't provide much information when the trades counts to tens of thousands. Rather, user needs a tool that :-

1. help them to undertand the statistics of differences on each item;
2. allow them to drill down to the trades details during mis-mtach investigation;
3. allow them to configure and ignore certain differences as "acceptable differences";
4. produce report and the end of each day's reconciliation operation.

Given their familiarity of Excel, VBA is the best tool to realize the above.

The first page of the program looks like below:

CONFIGURE

The CSV files are all pre-stored in a defined Windows path. User key in the date that he needs to reconcile. The file names are automatically patterned with the given date.

2ndRound Match

The work flow graph contains a few buttons that user can click on to perform a series of operations. Clicking on the 2nd Round Match calls the Perl script previous mentioned, and generate the aligned CSV file with "@" marks as indicator of differences. In about 30 min, the script processes about 20,000 rows of records.

Load

This button load the "@" marked file into the workbook.

Enrich

"@" is not good enough for user to spot the differences. Enrich is a button that go through the "@" marked files and highlight the differences with colors.

The highlighting is rule based, and the rules are configurable by users in side the rule worksheet in the tool.

For example, the following rule tells the program that

- if column 43 of the two files differ, and

- if the column from system A has a value of 40045772, and

- if the column from system B has a vlaue of 4093874,

the difference can be ignored. This is a SSI configuration issue. The difference comes from the static data differences for the two system. From the business point of view, static data from both sides are correct.

Once the enrich step is finished, most of the mismatches will be marked as explained. The output looks something like below.

Report:

Once user has manually analyzed the above found differences, and marked them with track number, they click on the report button. It will report for the counts of each identified issue, categorized by issue types and regions.

A typical report looks like below:

Rank:

This button will generate a list of issues, with their occurrence. So that management can focus on the top issues. A typical issue rank looks like below.

NOTE: The code is company asset, therefore will not be revealed.