Reconciliation Best Practice

Source : Watson Wheatley  
Author :  Duncan Wheatley – MD, Watson Wheatley

Very little accessible information on this topic appears on the web. Much of the wisdom surrounding the design of reconciliation software is inevitably proprietary and protected by the vendor unless exchanged for your email address. Comparative analyses of different systems against a benchmark of key requirements do exist but are usually only available for purchase at significant cost. This paper aims to redress this in part by providing one vendor’s views about best practice and by making it available within the public domain.

This paper takes as context the typical scenario of the asset manager needing to reconcile trades, cash flows and the resulting cash balances and positions between their internal portfolio accounting system and data from an external service provider; a prime broker, custodian, clearer, counterparty or administrator. The principles discussed below however are to an extent generic and are equally applicable to other scenarios where trading data is held in disparate systems where there is a potential for difference e.g. order management to portfolio accounting, or risk system to outsourced operations function. The goal for the reconciliation system in this context is simply to find the differences and provide an environment where these differences can be managed through to resolution.

Our views are presented as a walkthrough of the steps of the reconciliation process, starting with where the reconciliation data comes from and culminating by deriving maximum value from the reconciliation data contained within the system.

DATA RETRIEVAL

The process of obtaining data for reconciliation is changing. Not all organisations have access to SWIFT, particularly hedge funds where the settlement process is outsourced and smaller companies that do not want the expense. Custodians are increasingly guiding their clients towards their web portals for data access; more of a self service approach, or making reconciliation data files available to their client to be collected by secure ftp. None adhere to standards other than their own and a plethora of file shapes, formats and content have to be dealt with.

The reconciliation system must therefore have tools to enable any shape or data to be read, parsed, interpreted and delivered to the reconciliation tables. Specifically coded interfaces require programming skills, are costly to prepare and are rigid, only fitting the data set they were designed for. Not a viable or sustainable solution.

A generic import tool needs to be provided so that the end user company can manage their own data with people who primarily understand the meaning of the data, not code development. Systems to do this are available on the market, but one that is integrated with the reconciliation tool itself provides an excellent solution allowing both automatic data retrieval and a user controlled drag and drop interface.

The functional demands for this tool are considerable. At a basic level it must be able to read interpret and cross reference data contained in the files. Security identifiers (e.g. ISIN, Bloomberg Unique, SEDOL etc.) must be converted to the reconciliation system’s internal security id. Account ids may need to be looked up. In many instances data must be aggregated to present summary totals to the reconciliation system. Sometimes complex logic is needed to correctly interpret the information, requiring simple access to Boolean logic operators and in some cases powerful mathematical functions.

Processing data for reconciliation in this way must be fully auditable. Any one data item that ends up in the reconciliation system could be critical to the process, so tracing it back to its precise source is a must. The import tool must provide this facility including securing the source data file from which it was obtained.

VALIDITY CHECKING

The first step, before any reconciliation can take place, is to validate the accuracy and completeness of the data received. There is little value in reconciling data where the very process of transmitting it between systems has introduced errors. Whilst these should be picked up in the reconciliation process itself, they could be attributed to real differences in the data rather than simply a misinterpretation; potentially time wasting and costly.

Once the data is contained in the reconciliation system’s relational database, validation is relatively straightforward. Referential integrity rules can be applied to the database to ensure related data (e.g. positions and prices) passes muster. A more valuable approach is available if the system understands the accounting nature of the data it has received. At a simple level, do the trades received in the latest file account for the change in security position from the previous file? Do the cash flows sum to the change in the cash balances received? If these basic criteria are not met, then there is something wrong with the source data and the reconciliation process cannot continue. The reconciliation system should help the user to determine where the discrepancies lie, and guide them on what action to take, perhaps to repair the data or reject it completely; again straightforward if the system understands the accounting principles of the data.

If the reconciliation system is being used to determine differences in market value data, then there may be a further opportunity to validate the data as it is being received. If the accounting framework of the system understands the calculation of market value from the underlying components then this information can be used to validate the received market value figures against the position, prices, fx rates and cash balances provided. This provides a very powerful means of checking the completeness and accuracy of the data received. If the data can be verified to this level, then the reconciliation process that follows is made much simpler.

ACCOUNTING STRUCTURE

A reconciliation system that understands the accounting structure of the data it receives is not only well placed to identify discrepancies in received data, but critically is best placed to express the difference in the data sets in accounting terms. A list of unmatched trades and cash flows identified by the reconciliation system is useful but only as a starting point. A list of break items that are measured according to their materiality takes the process of reconciliation to a new level. If each break can be assigned a value, and this value is normalised to a base currency, then a means of prioritising breaks emerges, and a means of measuring objectively where aggregate risk lies.

This is very powerful information. The operations manager can have a simple measure of the status of the reconciliation at any one time, can drill to the material items and see who is dealing with them. The ability to pinpoint process weaknesses is substantially enhanced; perhaps a prime broker that constantly miscalculates trade charges or books trades late. The focus of limited resources on research and remedial actions can be easily determined, and reconciliation staff can see instantly their own clear up rate.

How this information is used by the business is up to the business. What is certain is that without the understanding of the accounting attributes of the data being reconciled and the ability to measure break value or materiality, none of this could take place.

MATCHING ENGINE

The matching engine is the hub of the reconciliation system and its required characteristics are well documented. It has to be configurable by the business to match any kind of data in any kind of way. The usual one to one, one to many, many to many etc. requirements are obvious. Matching any field, or a calculation based on one or more fields is necessary. Matching within tolerances is required; perhaps tolerance based on other data (e.g. fx rates) being common requests.

The match engine needs to be fast and be invoked through a scheduler, through events such as the receipt of data from a custodian or manually under the control of the users.

The rules engine can be viewed simply as a tool to return groups of records that meet the criteria defined in the rule. The action taken on these groups of records need not be restricted to simply creating match sets. A rule may be required to undo match transactions, perhaps where a trade’s strategy or book has been amended. Rules could be constructed to annotate groups of mismatching trades and assign them to specific users or groups for review and remedial action. Other rules may be needed to export unmatched transactions that need to be posted back to the internal portfolio accounting system, or perhaps to send them via email the prime broker for review.

Rules used in this way are clearly extremely powerful. How they are configured determines whether the system delivers clarity or chaos. It goes without saying that these rules need to be protected. Permissioning in the system can allocate the responsibility for their maintenance to specific personnel. Rules also need to be time stamped. If a modification is made to a rule then all the items matched by this rule prior to the amendment were matched with different criteria and the system needs to know this.

As with all parts of the system a full audit trail of activities needs to be obtained and retained. The information stored with the matched records needs to include the version of the rule used, when it was run and who ran it. If items are unmatched, then the match information needs to be retained although disabled.

RESEARCH AND REMEDY

If the preceding steps have been handled correctly, the users of the system will be provided with a list of break items that need to be reviewed, understood and actioned. The quality of the functionality to support the user in this process is critical to the success of the system. This is important data, being the sum total of the differences between the two systems being reconciled. The capabilities of the system to help and guide the user at this point in the process are critical.

Perhaps the most important piece of information that should be presented by the system at this point is a measure of the risk associated with each break. Having this information allows the user to prioritise their actions and alert others to the event where appropriate. If this information is not available, then the user has no meaningful framework within which to conduct the research and remedial activity.

If the system has an accounting framework, the measurement of materiality of the break is straightforward, and can be expressed as a value, and a percentage of the total market value of the portfolio. Without this, breaks would need to be grouped by type and reviewed by a knowledgeable user able to assess the importance from the presented details. Because of the potential complexity of this, it is best measured and presented by the computer where, with the help of the user interface and reporting, it can be grouped, filtered and used as a criterion to control export of data to service providers or internal systems.

Other key aspects of the functionality to support the research and remedial activity are primarily to do with workflow capabilities of the system. Where a number of funds or portfolios are being reconciled by the system, and perhaps with multiple prime brokers, the distribution of breaks to individuals or teams becomes important. It is inevitable that the same break occurs across multiple portfolios and is most efficiently dealt with as a set by one user or team.

The system should therefore have the capability, either automatically or under user control, to direct or distribute sets of breaks according to a flexible set of criteria, in this instance, by break type or instrument. Items allocated within the workflow will need to be annotated with a reason, a proposed action, and other attributes including priority.

The workflow capability should ideally extend outside the confines of the reconciliation tool and provide data in a variety of formats via email to internal parties or secure ftp to those outside the company typically back to the custodian or administrator.

Finally the users should be able to teach the reconciliation system to automatically process events that occur frequently and which lend themselves to automation. The logic of the workflow system should therefore be available to permissioned users, and be simple to program. In this way, the productivity of the process is continually enhanced, and the risk associated with the breaks dealt with in a timely manner.

REPORTING

Reporting is clearly important to all users of the system for a variety of requirements. The general requirements around reporting systems are well known. What is perhaps more interesting is the value of the data held within the reconciliation system, and its application to the measurement of operational efficiency, an even to the measurement of the quality of service delivered by custodians or administrators.

The reconciliation system occupies a unique and privileged position in the asset manager’s middle and back office operation. Within the mass of data that it processes is key information about the strengths and weaknesses of the operation within which it sits, which if made accessible would enable process enhancements to be made and their effectiveness to be directly and objectively measured.

A clear benefit of the reconciliation system is to deliver objective measures of service quality of the prime brokers or administrators, providing valuable input to the regular service reviews that many firms conduct. Once again, the measure of risk, discussed above, is an important variable, along with other measures including the amount of rework generated by errors.

Whilst software vendors can provide some guidance and stock reports in this area, the information that is important to the asset manager is likely to be peculiar to their own environment, and the vendor needs to provide open and flexible reporting tools to allow the users to extract and present this information.

The system should therefore have the capability, either automatically or under user control, to direct or distribute sets of breaks according to a flexible set of criteria, in this instance, by break type or instrument. Items allocated within the workflow will need to be annotated with a reason, a proposed action, and other attributes including priority.

The workflow capability should ideally extend outside the confines of the reconciliation tool and provide data in a variety of formats via email to internal parties or secure ftp to those outside the company typically back to the custodian or administrator.

Finally the users should be able to teach the reconciliation system to automatically process events that occur frequently and which lend themselves to automation. The logic of the workflow system should therefore be available to permissioned users, and be simple to program. In this way, the productivity of the process is continually enhanced, and the risk associated with the breaks dealt with in a timely manner.

REPORTING

Reporting is clearly important to all users of the system for a variety of requirements. The general requirements around reporting systems are well known. What is perhaps more interesting is the value of the data held within the reconciliation system, and its application to the measurement of operational efficiency, an even to the measurement of the quality of service delivered by custodians or administrators.

The reconciliation system occupies a unique and privileged position in the asset manager’s middle and back office operation. Within the mass of data that it processes is key information about the strengths and weaknesses of the operation within which it sits, which if made accessible would enable process enhancements to be made and their effectiveness to be directly and objectively measured.

A clear benefit of the reconciliation system is to deliver objective measures of service quality of the prime brokers or administrators, providing valuable input to the regular service reviews that many firms conduct. Once again, the measure of risk, discussed above, is an important variable, along with other measures including the amount of rework generated by errors.

Whilst software vendors can provide some guidance and stock reports in this area, the information that is important to the asset manager is likely to be peculiar to their own environment, and the vendor needs to provide open and flexible reporting tools to allow the users to extract and present this information.