To log in, type or paste the secure URL for Duco that you have been given into your web browser.
This should take you straight to the log in screen.
Enter your user name (i.e. your email address) and password.
After successfully logging in, you will see your Dashboard on Duco. For more information about the dashboard, refer to Duco 101.
Reviewing the Duco 102 Dataset
In this tutorial, you will be setting up and running a slightly more complex process. Below is the data set for this reconciliation. As you can see, there are some challenges that will make the build a bit complicated.
Both files contain payments. However, on side A (noted in orange and yellow), each line item captures the counterparty in two columns: Counterparty Name and Counterparty Symbol. On side B (noted in blue), Counterparty is captured in a single column that contains either the Counterparty name or symbol (but never both).
Therefore, in considering this reconciliation, sometimes we will need to match column D on side A with column C on side B, and other times we will need to match column E on side A with column C on side B.
The second challenge we face with this data set is the requirement for aggregation of some of the data for side A. This requirement is sometimes referred to as matching a block versus allocations, a parent item against child items, or one-to-many matching. Aggregation refers to the process of gathering data and expressing it in a consolidated format.
Now, let’s get into the build itself.
How to Set up the Duco 102 Process
As you will notice as we move forward, many of the steps are the same as Duco 101. The steps for creating a general process, or reconciliation, are:
- Import the test data
- Match the relevant fields
- Run the process
- Examine the results
- Update matching rules, reported fields, etc.
- Repeat from step 3 until the results are as expected.
To set up a process you need to:
- Click on Processes on the navigation bar at the top of the screen. This will take you to the Processes screen. If this is the first process that you are setting up, the screen will be empty. Otherwise it will show all the processes that you have permission to view.
- To create a new process, click, create process.
- Enter the process name. Use a descriptive name for your processes, not "Proc 1" or "Rec A". For this example, I will be calling this process “Payments 102”.
- Enter a unique short code for your process. We suggest that you choose a code no longer than 8 characters or so. The short code can never be changed, so if doing a sample build, do not use a short code that you may want in the future.
- Enter a Data label in the singular and plural form for the items you are matching. For example, "transaction" and "transactions". This label is then applied in the UI to make use more intuitive (eg. on menus).
- Leave "Mask sensitive fields" un-ticked. This is an advanced feature covered in section Masked processes.
- You can optionally enter a description for the process.
- Finally, create the process.
As with a normal reconciliation, upload the data on to each side. As a best practice rule, try to keep the data that is most familiar to you on the left side of the process.
For this example, I am going to match the data manually.
I am going to first start with matching the obvious fields. Deal ID on Side A does not have a clear match on Side B, so I am going to leave it out.
- To match a field, select the category from each side and click “Add field”.
After matching the obvious categories, you are left with the following:
In this example, I want to match both Counterparty Name and Counterparty Symbol on side A with Counterparty on Side B.
To do so, I first match Counterparty Name to Counterparty. In order to see the full menu, I can click “Show all fields” to see all of my matches.
I can now double match Counterparty Symbol on side A to Counterparty on Side B. When fields are already matched, a check mark appears next to the column name.
Now that our fields are matched, we can run the reconciliation for the first time.
Examining the First Run
- To run the process for the first time, return to the top of the screen and select process.
- Select the process from the menu.
- Click the green “Run process” button.
- After the process completes, the first run will look like so:
As you can see, the build still needs work. Click on All transactions to get a closer look at the build.
Counterparty Name and Counterparty Symbol are clearly matching on several of the items; the problem is when Counterparty Name matches, Counterparty Symbol is returning a break, and vice versa.
What we want Duco to do is to first attempt a match under Counterparty Name, and subsequently match in Counterparty Symbol. Let’s get to configuring the build.
Configuring Rules and Rule Sets
To begin configuring rules and rule sets, go to the settings menu and select “Rules & rule sets”.
You will then land on the default rules menu. The Default Rules will always apply to every run of the reconciliation. For this build, I want to leave the default rules alone. Instead, I am going to add in two rule sets. To do so, click the blue “ADD” button next to rule set.
- Label the first rule set “Match Name” and click “Create”.
The first rule set is going to be used to capture the matches where Counterparty Name on side A of our dataset is used against Counterparty on side B. The way we are going to achieve a match is by having this rule set ignore the symbol field. This is very simple to configure!
- To do so, hover over “Counterparty Symbol” and click the blue “Add Rule” button on the right.
- Then, under “Matching Control” hover over “Counterparty Symbol” and select “Ignore”.
- Click the blue “Save” button to complete the rule set.
As you can see, the rule now appears under the rule set in grey text.
We are now going to repeat the same process for our next rule set:
- Click the blue “Add” button on the left side of the screen next to “Rule Sets”.
- Name this rule set “Match Symbol”.
- For this rule set, we are asking Duco to do the opposite: look for matches by ignoring the name and matching the symbol category.
- Repeat the initial process, except we are now going to ignore the Name field.
You will end up with the second rule set looking like so:
The next step is we need to decide how we want the rule sets to be applied. We don’t want them to be applied at the same time because they would end up contradicting one another.
In order to do that, we are going to need to set up Multi-Pass.
Setting up Multi-Pass
Setting up Multi-Pass allows Duco to sweep through the data more than one time during a run and apply different matching criteria each time.
In this example, we are going to have Duco sweep through the data twice, and each time apply one of our rule sets.
- To begin, go to the settings menu and select “Multi-pass”.
- First, select “Add Pass”.
- We will also call this pass “Match Name”. When naming a pass, make sure to name it something descriptive and not just “Pass 1…2…3.. etc.” This allows anyone looking at the build to get a sense of what is happening when that pass is applied.
- Press “Create”.
We are now going to configure the sequence in which Duco runs the reconciliation, and we want the first pass to attempt to match the name category. We are now going to combine our rule set we made earlier that ignores the symbol into the first pass of the run.
- To do that, click “Edit” on the left and look at the matching rules box.
- The Default rules will always be applied, but what we need to add is the match name ruleset (this rule set will ignore symbol).
- Click “save changes” once the rule has been added.
If you guessed that we need to repeat the same process and create another pass that will only match the symbol, you are correct!
- Return to Multi-pass settings and select Add Pass.
- Name this pass “Match Symbol” and press Create.
- Select edit on the left hand side, and under “Matching rules”, add in the “Match symbol” rule and press save changes.
The settings should now look like so:
We are now asking Duco to complete two passes of the data- the first time only focusing on matching the name, and the second time focusing only on matching the symbol.
Let’s run the reconciliation again and see how it turns out this time.
Run 2 of the Process
- Return to the processes screen, select the green run process button and re-run using previous data.
Already, this pass of the data is much improved. We now have 12 matched categories, 5 partial matches, and 17 unmatched.
Let’s look under “All transactions” for more detail on this run. You can tell which pass has achieved the match by looking closer under the “matching pass” column.
As you can see, some of these matches were matched under our “Match Name” run, and others under our “Match Symbol” run.
We still have some work to do before the reconciliation is complete.
Looking at the overview of the reconciliation, we can see in the data submission window that side A is twice as large as side B.
As mentioned in the beginning of this tutorial, one of the challenges with this build is that we are going to need to aggregate some of this data. Below, I have isolated a subset of this data that more clearly shows this need:
Here, we have an unmatched item and 9 unmatched items. We can see that the trade data, currency, pay receive, etc. are all the same except for the amount. The amount for the partially matched item is showing 10,000 for side A vs. 100,000 for side B, which is why the software is returning a break.
Looking at the 9 unmatched items below for 10,000 each, it is clear that some aggregation of this data could lead to a match.
What we are now going to build into this reconciliation is a feature called roll=ups. Roll-ups allow us to aggregate line items and collect them into one single line item for the purpose of matching.
- We are going to start by going into settings and selecting “Roll-up rules”
- The first step when creating a roll-up rule is switching it on for the side you want to perform the aggregation on. You can turn it on for one or both sides. In this case, let’s turn it on for side A since that is our larger data set.
- The next step is to click the blue “Add Rule” button in the upper right corner.
Duco doesn’t require that you have a unique key to identify items that should be grouped together, instead we can perform the aggregation based on any fields present in the data set that line up across multiple items.
- For this example, let’s select currency, pay, receive, and trade date. What this means is that every time Duco detects that every one of those key indicators is exactly the same, it will automatically perform the desired calculation.
- Based on the data set that we have been looking at, we want to sum up the amount.
The next step is to apply it to our reconciliation. In our data set, we know that we have one-to-one matches, and now we have one-to-many matches with the roll-up rules applied.
In order to capture all of our matches, we are once again going to return to Multi-pass.
Adding Roll-up Rules into Multi-pass
Let’s use the settings menu again to return to Multi-pass. What we need to do is add passes to this reconciliation that apply our new roll-up rules.
The first pass that we need to add will be applying the match name ruleset we created earlier, but this time it will apply the roll-up rule we just created.
- First we are going to add a new pass by clicking the blue “Add Pass” button, and let’s name this one “Match Name: Roll-ups”.
- Click the blue “Create” button.
- Next, let’s edit the rules of this pass. We are going to add the ruleset for Match Name, and then add in the roll-up rules that we just created.
I am going to run this reconciliation again to show you how this roll-up rule has applied in this pass.
As you can see, this run is looking the best of all the runs so far. We can see in the data submission box that the data on side A has been successfully aggregated using our roll-up rules. A new line item has appeared, and now we can see the original subtitle and the subtitle after roll-up.
We can also see the effects of our roll-up rules by looking in the green auto-match bucket.
As you can see, a check mark appears where a match has been made possible due to the roll-up effect. While these may look like one to one matches, we can look closer inside of the item by selecting it to see what items have been included in this roll-up. Get a closer look by selecting the ID number on the left hand side.
When selecting the match, you can see a blue notice appears below that the match contains rolled-up transactions. Click on this indicator to view the individual line items that contributed to the aggregation.
In short, our roll-up rules have successfully allowed us to aggregate a much larger data set to compare it against a smaller set. For advanced roll-up rules, visit our helpcentre.
Finishing up the Reconciliation
Looking back at the dashboard, we now have no unmatched line items and only three partial matches.
These three partially matched items have roll-up rules applied to them, as we can tell by the checkmark. However, the counterparty name is not matching against the symbol. We can fix this by adding a final pass to the reconciliation.
- To finish this reconciliation, let’s head back into Multi-pass settings.
- Add a final pass to the reconciliation, and name it “Match symbol: Roll-ups” and click “Create”.
- Edit the pass, add “Match Symbol” ruleset to “Matching Rules”.
- Add the “Roll-up rules” that have already been created.
- Finalize it by pressing “save changes”.
A note about Multi-pass: you can always reorder the sequence of the passes by clicking the arrow buttons. You can also toggle the pass on/ off by pressing enable/ disable. This feature is particularly useful for troubleshooting. To learn more on advanced multi-pass features, visit our helpcentre.
Running the reconciliation for the last time should produce a fully matched data-set, as seen below.
Congratulations on completing Duco 102!