Automated Validation of Trade Data Extracts feeding a Central Risk Database
at a large European Investment Bank
One of the roles of the bank’s trading floor is to provide reliable information on current and closed deals to a growing number of bank branches (currently exceeding 100). This information includes margins, positions etc.
The Bank uses a number of different trading platforms, limits and back office systems (including Kondor+, Prime, IBIS and MUREX) dealing with Bonds, FX Options, Swaps, Derivatives and other trades. Every day, from each system, a number of data extracts are loaded into a Risk database. This enables positions to be assessed and decisions made accordingly.
Unfortunately, the Risk department has noticed that input files sometimes contain invalid entries or missing data records. Undetected errors carry a risk of multiple US$10millions.
Due to the large volumes of data being processed, this problem requires a software solution. This solution must be able to detect errors sufficiently rapidly to ensure that only valid numbers are propagated to the business.
Each day the number of records in the input files will vary due to trades maturing or new trades being created. In addition, since each of the systems exports data in a slightly different format, the solution must be flexible enough to cope with this variety.
- ClusterSeven was selected to provide the validation engine to check the integrity of entries within the input files. This choice was based on its speed and flexibility both in applying easily configurable rules and handling different file types, speed. The process works as follows:
- ClusterSeven checks for the new daily version of each data extract file across multiple (50-100) locations.
- The absence of an expected file is reported. Each input file is transformed into a consistent format. This includes flat files, which may be "tab", "comma" or "semi-colon" delimited.
- This completion of this transformation triggers the activation of ClusterSeven’s file watchers. These identify the next relevant file using in-built file name pattern matching (consolidation).
- Each new file is compared to its previous version. ClusterSeven automatically filters the information to detect changes to trades, including creation, maturation/cancellation and amendment.
- ClusterSeven then applies business rules and logic to detect anomalous changes (or lack of changes) within the information. For example ClusterSeven can confirm that trades are not missing from the input files unless they have reached their maturity date.
- Daily reports are issued to the controllers highlighting errors before invalid positions might be used. The validated extract is then imported into the Risk database.
- Each day an additional custom report is run for each file, detailing deleted items, changes breaching tolerances, as well as trends in the data. An example of tolerance based alerting is on "Mark to Market" values which have changed by more than 5% over night.
- Additional summary reports are provided for the complete file inventory, to ensure that all the extract files were properly created, (e.g. populated with data) and that associated system checks were clean.
There is work under way to further integrate ClusterSeven into the Risk System, beyond this current use as a control gate for the Risk database. This will allow the database to import validated data directly from the ClusterSeven database, allowing for a fully integrated and secure Risk System.Print Version
"ClusterSeven’s software addresses a real business problem and we expect that it will soon become the industry-wide standard, found within every global investment bank"Sanjay Malik, President and CEO of Panache
"When ClusterSeven approached us, it was the first time we had seen such a product. We immediately realized its value to us as a company."George Flynn, business analyst of product control, European Credit Management