Reconciliation, how to get it right

Reconciliation is the bane of upgrades and migrations. It’s hard work where one needs to go down into details, find patterns and then find solutions. Plus you always have the stress that something new will emerge.

I can’t cover all types of reconciliation, I will focus (at least in this post) on trade reconciliation between 2 versions of Murex (be it upgrade or migration).

To get valuation (risk and PL) for transactions, you need to have static data and market data. Static data is usually very stable and I would recommend not to do any reconciliation on it. If it breaks on an instrument, it will show clearly into the reconciliation report and you can focus on it straight away.

Market data is a different story. It is usually very easy and quick to check: open both environments and check the calibrated values for curves (rates, commodities). For volatility, if you’re using a volX, check that the calibrated values are identical. Check that normal/lognormal and price vols match if you do input your volatility in one nature but consume volatility of a different nature in your models.

When rate curves are not returning the same values when calibrating, you will have a total break on all deals using that curve. Usually the difference is actually quite small (lower than .0001) so with a correct tolerance level in your checks, you should match results easily.

You can then move to trade reconciliation once the market data is ok. To do so, the usual practice is to run dynamic tables on the trade sets (of different types: PL, Cash and sensitivities), put in exclusion criteria less than $100 ignored, .01% ignored. Run the tables on both environments and then using sql compare both results and output breaks into a table.
There are quite a few solutions to do this job but it is actually quite straight forward up to this stage.

Now you have a long list of breaks (and missing entries!) that you need to reconcile. There is no shortcut and you need to get moving. I usually break down by deal type and instrument when relevant. Start then with the largest breaks and try to understand why the 2 are different (market data, change in customization, improved behavior, etc…). The important bit is to find the root cause and with experience you find it more easily.

Then you check if your root cause applies to other trades (usually it does). If it’s an isolated issue, bad luck, move on to the next trade. If it is not and other trades in your list seems to have the same issue, you need to establish a rule. That rule will flag all trades with the criteria to that root cause. There a good knowledge of SQL (and Murex data structure) often helps, if you don’t/can’t, 2 options:

– Write down the root cause of the issue and move on to another trade. Once you have enough root causes, check with someone more knowledgeable to teach you how to build your rule.
– Run a dynamic table outputting the data you believe isolate reconciliation breaking trades from others. In this later case, you might waste a significant amount of time building that dynamic table and it might not even work.

Finally comes the solving/accepting part. Some issues have to be accepted as they’re enhancements or correction. Some others are regressions or changes of behavior that will require correction. Sometimes a simple configuration change can fix these issues. Otherwise, you might need Murex help to figure it out.

The important part is to automate it as much as possible and end up with rules that you can re-use in the next reconciliation re-run. Ideally, the solutions can also be automated and make your reconciliation go smoother.

Now, maybe the most important bit: never underestimate reconciliation. It is a difficult task especially if you want it done well. It is hard to estimate how long it will take and the criteria, in my experience, to take into account: time difference between the 2 Murex versions, complexity of deals, exotic and yield based bonds (lasting scars from these).