Inter Row Match in an IDEA Database - Case Study on Circular Market Trades

Hello Group Members,

You have been presented with a database of market trades with the key fields ~

Instrument Number

Transaction Number

Transaction Date

Buy Party

Sell Party

Quantity Traded

Rate

Value

Transaction Type (Buy/Sell)

Now the single file has both buy and sell trades for counter-party entities.

If you need to look at circular trades between the same counter-party entities - taking a hypothetical case - Company A sells Instrument B to Company C on 11th Sept 18 - 1000 Units at 100000. On the same date of 11th Sept 18, Company C sells Instrument B to Company A - 1000 Units at 100000.

Now this data is available within the single file across multiple rows. The counter-party entries need not necessarily come one below the other in the base data and may appear many lines later after the first instance of the Sell or Buy Trade.

In this scenario if you need to capture circular trades using IDEA, following is a possible procedure you may employ -

A. Split the base data into two files using Key Value Extraction where the Key Group Field will be Transaction Type (Buy/Sell). So Key Value Extraction will give you 2 files - one for Buy and one for Sell.

B. Now open the Buy file arrived at in Step A above. Since you need to match a buy with a sell trade across files, perform a Join. In the Join dialog box select the Sell file as your Secondary database. Click on Match and select the fields - Instrument Number, Transaction Date, Quantity as the matching key fields in the Primary and Secondary files in the first 3 lines. Now to ensure it is a circular trade for the same counter-party match in the 4th and 5th line  - Buy Party with Sell Party and Sell Party with Buy Party.

Using the 5 matching key fields above in Point B and by using the Join option Matches Only you can arrive at potential circular trades in the base data.

Regards

Group Admin Team