Roll-up provides a generic mechanism for grouping records in a data set. This allows you to match groups of records rather than individual records. This is sometimes also known as "one to many", "many to many" or "many to one" matching. Some applications include:
- Adding up allocated trades and then matching them against a block trade on the other side
- Rolling up the value of trades per currency and performing an aggregate comparison of the total value per currency
- Grouping multiple trade legs into a single trade before comparison
Watch this video for an introduction to roll-up rules.
Let's now look at a practical example. Consider the following data sets:
The data sets represent simple payments, but we don't want to match the payment on a one-to-one basis. We want to group the payments with the same payer, receiver and currency. We then want to consider the sum of the amounts of the individual payments. The table above indicates with different colours the payments that belong to the same group.
- The January 15 payment in data set 1 matches with January 16 payment in data set 2. This is a simple one-to-one match.
- The February 1 payment in data set 1 matches with the group of payments that includes the February 2 and February 3 payment in data set 2. (70,000 = 20,000 + 50,000)
- Group of payments that includes March 1 and March 2 payments in data set 1 matches with the group of payments that includes the March 5, March 6, and March 7 payments in data set 2. (100,000 + 30,000 = 50,000 + 40,000 + 40,000)
Setting up a process with roll-up rules
In Duco roll-up rules allow you to group records according to some criteria. They are called "roll-up" rules because they "roll up" multiple real records into one a single artificial record.
- Create a process and set up the data inputs as usual.
- In the Match fields screen, align the fields as usual.
In this example, we align all fields except "Payment Date", because two matching records, or group of records, don't necessarily have the same payment date.
Adding some reported fields
Unless instructed otherwise, Duco shows only matched fields in the match results.
In this case, even if we are not matching the "Payment Date" fields, we still want to report them. To see what happens to the values of these fields when records are rolled-up.
- Click on Settings → Reported fields. This will take you to the Reported fields screen.
- Select "Payment Date" in the Unreported fields table for Data Set 1 and use the arrow button to add the value to the Reported fields table. Repeat for Data set 2.
The resulting set-up should be as follows:
Setting up roll-up rules
- Click on Settings → Roll-up rules to go to the Roll-up rules screen.
- In the Roll-up rules screen, click on the On button to enable grouping for each data set.
- Click on the Add Rule button, to add a roll-up rule.
A roll-up rule has the form: "Group items by ... and sum up these fields: ... ". You can then fill the blanks via the drop-down menus.
- For the first "blank", use the drop-down menu, or type the field names directly, to enter the field names "Payer", "Receiver", and "Currency".
- For the second "blank", use the drop-down menu and choose "sum up"
- For the third "blank", enter in the same way the name of the field whose values you want to sum up. In this case "Amount".
The roll-up rule should look as follows:
Note that the two buttons on the right side of the rule that the blue arrows indicate. You can use them to enable or disable a particular rule for each data set. The process is now configured and ready to run.
Viewing the results
- Click on Exit settings
- From the Process overview screen, click on the Run process button to run the process.
The match result will look as follows:
Results that include roll-ups can be identified by a tick in the Roll-ups column:
- Click on the match result ID to see the details of the match result.
- Click on the link below the result details, to see in details how the records were rolled up.
Because of the roll-up rule, Duco has grouped all the records with the same payer, receiver, and currency. For the match result above that was "Sphere Inc", "Poligon Securities", and "GBP". The "Amount" value for the new artificial record is, as you would expect, the sum of the "Amount" field values of the rolled up records. Fields that have not been added up or do not have a value that can be grouped together e.g the Payment Date in the above example, are assigned the value of the first trade in the list of rolled up records.
Roll-up rules for Multi-pass
Normally, the roll-up rules you configure will be applied to the entire data set for one or both sides, depending on your configuration. It may, however, be necessary to have different suites of roll-up rules applied to specific sub-sections of the data. A simple example would be roll-up rules that differed by currency. Or perhaps your file has multiple asset classes in it, which need to roll-up differently.
It is not possible to create a new suite of roll-up rules directly from the roll-up menu. First you will need to create additional passes for the process, using Multi-pass.
Once you have created an additional pass of the process, you will see an option to add roll-up rules to that pass.
Selecting the Roll-up rules drop-down will present the option to add "Roll-up rules". This will simply be whatever roll-up rules your have already created on the Roll-ups menu. You will, however, also have the option to select "Create new roll-up rules".
If you select this, a pop-up will be displayed asking you to provide a name for the new roll-up rules.
Select Save changes
Now return to the Roll-ups menu. You will see that Duco will now allow you to create a new suite of roll-up rules specific to the new pass you have just created.