Validation of Fixed Income Bonds and Equities using ETL Tools

ADC Data have created some routines which will hopefully benefit anyone working in the finance industry and dealing in fixed income or equity securities. Effectively these securities have identifiers which serve as the primary means of identification, the prominent standards are:


  1. cusip – Stands for Committee on Uniform Security Identification Procedures and is a North American Standard. This is 9 digits, the last digit of which is a check digit. The algorithm is described in this pdf.

  2. isin – This is the international standard and is 12 digits, the last of which is a check digit.

  3. sedol – A European standard which is 7 characters long, the last of which is a check digit.


The check digit helps to identify invalid codes, which you may receive for any number of reasons including invalid keying by an end user.


There are 6 informatica routines available for download with 3 of them being based on a reusable expression and an user defined function and the remaining 3 being implemented using the informatica java transformation. initial tests seem to suggest that for a small volume the expression is quickest but for a larger volume the java expression is quicker.

The informatica expressions take as an input the security as a string and outputs the security, an integer indicating the error code and an error string.


Having created the informatica routines I decided I would also recreate them in the two main open source ETL tools, mainly Pentaho Data Integration and Talend.


For Talend we implemented the logic as functions or as Talend refer to them routines, which can be called from components such as tMap.


The PDI object most closely resembles the informatica transformations in that they add additional rows to the output.



Those of you who are interested in using these transformations can download them from the places listed below:

Link to download Informatica Routines
Link to download Talend Routines
PDI Routines

 

Software Downloads

  1. Rapid cube prototying tool


  1. Rapid excel report generation tool


  1. Securities validation routines (Isin, Cusip, Sedol)


  1. Easy Dimension Maintenance Tool