Some may say that reconciliation is just matching two numbers, but that would be an over-simplification. There are many more actions involved.

For a start, operations professionals need to identify the candidate data to be compared from portals of the counterparties. They then need to extract information from those records and may have to format them. After which, they need to create spreadsheets to perform look-ups and highlight unmatched items which are known as breaks. When comparing numbers, they also need to consider the degree of tolerance for matching and the relationships between numbers e.g. net proceeds need to take commissions into account and these also need to be addressed in order. Difference in the time of arrival for each report can also cause interruption in the workflow.

What can go wrong with manual reconciliation?

With each step in the reconciliation process, there is more room for error. Here is just a list of a few common examples:

1. Acquisition of the wrong data

While downloading the correct report is not a difficult task per se, we all have times when we make a fat-finger error or read the wrong line. One small mistake here can ruin all the work to follow.

2. Extracting the wrong data

Similar to above, it is possible to accidentally copy and paste the wrong section, especially when the specialist’s job becomes repetitive.

3. Formatting error

Format of the data from various sources can be different e.g. regional settings of the computer being in the US or UK can sometimes cause the date to be misinterpreted. If the specialist fails to standardise the formats, there may be false mismatches.

4. Mapping error

Different identifiers may be used by you and the counterparties, e.g. you may use Bloomberg open symbology while your counterpart uses SEDOL, while also dealing with custom identifiers for OTC products. Any error in mapping can cause errors in matches.

5. Incomplete story

Sometimes reports are based on trade date rather than activity date so backdated trades or amendments are missed.

Other problems:

6. Scale problem

The increase in scale is usually two-fold — the increase in trade volume and the increase in the number of instruments. If you only trade listed instruments and your trade volume increases from 20 trades to 200 a day, you are likely to have 5-10% breaks. This means more security tickers to map before reconciliation can be run, and potentially more people needed to do it. OTC trading also requires more operational domain knowledge and has more parameters to match on which the specialist may need to take some time to acquire.

7. Delays and downtime

The reconciliation process is dependent on the time of arrivals of the reports to start. Time may be wasted in continuously checking for the files and the wait may lead to downtime for the specialist and other inefficiencies.

8. Turnover

Accuracy of the reconciliation resides on the knowledge of the specialist and any spreadsheets they build – intellectual property is not owned by the fund so if they leave, the relevant knowledge may transfer with them.

How can a reconciliation solution help?

1. Automating the data acquisition process

Not only does automating file acquisition help to avoid mistakes, overtime clients can see trends of when the data is usually available, helping them to plan their workflow.

2. (Partial) automated mapping

For example, a security master can be used as the central mapping of all security identifiers. For OTC, a system may be able to pick out parts of the name, and suggest possible matching. These can remove formatting and mapping errors.

3. No turnover risk

While the manual process is solely dependent on the knowledge of the specialist, a reconciliation system is usually built based upon numerous years of collective industry experience from the developing team and user feedback. As the reconciliation software gathers more users, each user can benefit from the improvements made by learning from other users’ scenarios.

Errors in a reconciliation software are likely to be systematic and can be easily identified in the development and testing stage, while manual mistakes are likely to be more random and harder to identify.

4. Improved communication

Whether the reconciliation process is manual or automated, errors are inevitable. Therefore multi-level approval is always a good practice. A good reconciliation system should optimise the approval process by laying out clearly on the dashboard what is awaiting approval and from whom. Coupled with the comments feature, it helps to organise communication to remove the need for multiple emails

5. Scale

New financial instruments can be created conveniently by setting up rules and they are easy to backtest. Systems should not be constrained by volume or compute resources, so they can handle an increase in trade volume with a marginal increase in cost.

Factors to consider when selecting a reconciliation solution

1. Industry specificity

You need to consider the coverage of the system. Rather than looking at general-purpose data aggregation tools, you need a system that understands trades. Look for industry-related features such as trade stores. For hedge funds, asset managers and family offices, trade reconciliation, stock reconciliation, asset reconciliation, activity reconciliation, cash balance reconciliation and cash activity reconciliation are more relevant.

Age of breaks serves as a great metric for the effectiveness of your operation and counterparties. The older the break, the longer a file has been missing or it has simply not been corrected upstream. It also helps reconciling across time. Reconciliation software that understands activity will match data today against a pool of unmatched data from the past — a concept I called “pool-based reconciliation”.

2. Intuitive format

A good system does not need to be augmented by spreadsheets, rather, it should be able to aggregate and pivot data in different ways for analysis. It should have a fluid structure that follows the user’s thoughts and workflow.

3. Ability to scale up and down

When people talk about scale, it is always about scaling up. But in the real world, there may be times you also need to scale back. It is therefore worth considering:

• Modularity of the system: Can you add or remove different features as you scale or have a new scenario?
• Pricing model: Do they price by volume, number of users or AUM?
• Lock-ins: Beware of long-term contacts, you may be liable to pay for a high-volume package that you are no longer utilising fully.

4. Management information

An excellent reconciliation system should offer management insights that can also assist the due diligence process for investors. Metrics such as the accuracy of booking, accuracy of the source data, consistent time of arrival for reports can be used to judge internal and external parties and help inform improvement plans.