Blog article
See all stories »

The Unseen Backbone of Banking: A Deep Dive into Matching and Reconciliation

Last year I celebrated two decades of immersion in IT, specifically within the Financial Services sector. During this period I have been a witness to remarkable transformations in banking and technology. The emergence of Fintech companies and their customer-centric approach, along with significant advancements in software engineering like Agile methodologies, microservices, and cloud computing, have reshaped the landscape. Yet, intriguingly, the back-office operations of many financial service companies have remained relatively static over these years, still grappling with manual encoding, repetitive tasks, and heavy reliance on Excel.

A particularly manual and yet automatable process in the Financial Services sector is matching and reconciliation. This process arises in various forms, i.e. from identifying and addressing discrepancies (typically occurring due to issues or gaps with the integrations) in master-slave integrations to correcting or removing duplicates and semi-automated updates of operational systems with data from external sources.

Despite the availability of sophisticated software (e.g. FIS IntelliMatch, Calypso Confirmation Matching, Misys CMS, Temenos T24 Confirmation Matching…​) for specific reconciliation tasks, such as payment and trade confirmation matching (often based on SWIFT messages), the majority of matching tasks often rely on custom or manual solutions, including Excel or even paper-based methods. Very often automation is also not pertinent, as matching is often involved in one-time actions like marketing campaigns, data clean-ups, alignment with partners…​

Understanding better reconciliation requires dissecting its components, i.e.

  • It starts with collecting and transforming the disparate data sets for comparability. This consists of recuperating 2 data sets, which can be delivered in different formats, different structures, different scopes and with different names or enumerations. The data needs to be transformed to make them comparable and loaded into the same tool (e.g. a database or Excel), so that they can be easily compared.

  • The next step is defining a precise matching algorithm. This can be a simple unique key, but it can also a combination of multiple attributes (composite key), a hierarchical rule (i.e. match first on key 1, if no match try on key 2…​) or a fuzzy rule (if key of data set 1 resembles key of data set 2 it is a match). Defining this matching algorithm can be very complex, but it is crucial in the ability to automate the matching and reach a good output quality.

  • Once the matching algorithm is defined, we enter the comparison phase. For small data sets, this can be done quite simple, but for very large data sets, it can necessitate all kinds of performance optimizations (like indices, segmentation, parallelism…​) in order to execute the comparison in a reasonable time.

  • Finally, identified discrepancies must be translated into actionable outputs, such as reports, communications to colleagues or third parties or corrective actions (e.g. generation of files, messages or SQL statements to fix the differences).

The intricacies of matching in financial services are diverse. Let us explore some typical use cases in the financial services landscape:

  • Most banks have a Securities Master File, describing all securities which are in position or can be traded at the bank. This file needs to be integrated with a lot of applications, but also needs to be fed by multiple data sources, like Telekurs, Reuters, Bloomberg, Moody’s…​ This means a security needs to be uniquely matched. Unfortunately, there is not 1 unique identifier describing all securities. Publicly traded instruments have a commonly agreed ISIN code, but private and OTC products like e.g. most derivatives usually do not. Banks have therefore invented internal identifiers, use fake ISIN codes (typically starting with an "X") or use composite keys to uniquely identify the instrument (e.g. for a derivative this can be combination of ticker of underlying security, strike price, option type and expiration date).

  • In Retail banking it is obviously essential to uniquely identify and match a specific physical person. However even in a developed country like Belgium, this is easier said than done. Every individual in Belgium has a National Register Number, so this seems the obvious choice for a matching key. Unfortunately, Belgian laws restrict the usage of this number to specific use cases. Additionally this identifier is not existing for foreigners and can change over time (e.g. foreign residents receive first a temporary National Register number which can change to a definitive, other one later or in case of gender change the National Register Number will change as well). Another option is to use the identity card number, but this is also different for foreigners and will change every 10 years. Many banks therefore use more complex rules, like a matching based on first name, last name and birth date, but obviously this comes also with all kinds of issues, like duplicates, spelling differences and errors in the names, use of special characters in the names…​

  • A very similar problem is matching a company or more specifically a store. In Belgium, each company has a company number, which is similar to the VAT number (without the "BE" prefix), but this is again very national and 1 VAT number can have multiple locations (e.g. multiple stores). There exists a concept of a "branch number" ("vestigingsnummer" in Dutch), but this concept is not very well known and rarely used. Similar there exists the LEI code (Legal Entity Identifier) which is a code of a combination of 20 letters and codes, which uniquely identifies a company worldwide. Unfortunately, only large companies have requested a LEI code, so for smaller companies this is not really an option.
    Again more complex matchings are often done, like a combination of VAT number, postal code and house number, but obviously this is far from being ideal. In search for a unique and commonly known identifier, the Google ID becomes also more and more in use, but the dependency with a commercial company might also poses a big operational risk.

  • Another interesting case is the matching of an authorization and the clearing message in a VISA card payment. Normally a unique identifier should match both messages, but due to all kinds of exception cases (e.g. offline authorizations or incremental authorizations), this will not always be correct. Therefore a more complex rule is required, looking at several identifiers, but also to other matching criteria like acquirer ID, merchant ID, terminal ID, PAN (card number), timestamp and/or amount.
    This kind of matching applies also to other payment use cases, like e.g. matching a pre-authorization completion with its preceding pre-authorization or a refund with a prior purchase.

  • A financial use case which concerns almost any business is invoice and payment matching. When a company issues an invoice, it needs to be able to see when the invoice can be considered as paid. This is important for the accounting, but also to see if reminders for unpaid invoices should be sent out.
    To uniquely match the payment with the invoice, in Belgium typically a structured comment is used in the payment instruction. This unique code with check digit provides a unique matching reference. Unfortunately, customers often forget to put the structured comment or use the wrong one (e.g. copy/paste of a previous invoice). This means a company needs to have a fallback matching rule in case the unstructured comment is missing or wrong. Typically a combination of payment amount, payment date, IBAN of counterparty and/or name of counterparty can give an alternative way to match those invoices.

As you can see matching is far from easy, but understanding the basic steps can help in better matchings. In the meantime, despite its limitations, Excel remains a powerful tool for (manual) matching. Therefore a quick reminder for everyone who wants to do matching in Excel:

  • Use VLOOKUP to perform matching. VLOOKUP has however certain limitations, like the fact it gives an error if there is no match and that you can only search on the first column. A powerful alternative is to use XLOOKUP, which does not have these limitations.

  • If you need a composite search key, add a column in your search data set, with the composite search key (i.e. concatenate the different attributes, with e.g. "#" as a separator) and then use VLOOKUP/XLOOKUP to search on this new column.

  • Some attention points when using VLOOKUP:

    • Do not forget to add "false" as the last argument of the function VLOOKUP to ensure an exact match.

    • Ensure that data formats are the same. E.g. a number "123" and the text "123" will not match, so it is important to convert them to the same format first. Idem for identifiers starting with leading 0’s. Often Excel will convert those to numbers, thus removing the leading 0’s and not resulting in a match.

    • Do not use data sets of more than 100.000 rows in Excel. Larger data sets are problematic for the performance and stability of Excel.
      It can also be interesting to put calculation mode to "Manual" if you are working with VLOOKUP on large data sets, otherwise Excel will recalculate all VLOOKUPs each time you make a minor change to the data.

    • VLOOKUP has the column number to return as third argument. This number is not dynamically adapted when adding or removing columns, so remember to adapt when adding or removing columns.

    • If you just want a match, you can use formula "=IF(ISERROR(VLOOKUP(<SearchValue>,<DataSet>,1,false),"NO MATCH","MATCH")"

These tricks can help to speed up your manual matchings, but obviously real automation is always better.

Matching in financial services is a multifaceted challenge, but understanding its fundamental steps is key to improving outcomes. While tools like Excel offer temporary solutions, the future lies in intelligent automation, which can significantly streamline these processes. For those seeking to delve deeper into matching complexities or automation, leveraging advanced tools and platforms, including AI-driven solutions like ChatGPT, can provide both insights and practical solutions.

Check out all my blogs on https://bankloch.blogspot.com/

 

5367

Comments: (5)

Ketharaman Swaminathan
Ketharaman Swaminathan - GTM360 Marketing Solutions - Pune 04 December, 2023, 10:10Be the first to give this comment the thumbs up 0 likes

Great post! Kudos for covering an oft-ignored topic.  

Your last use case of matching payments with invoice is a big pain area for many of our customers. Like in other countries, I'm guessing Belgium also faces this common problem where supplier raises invoice for 100 but customer pays only 80 out of it. Does the present "checksum method" in Belgium handle that - or does this cause yet another RECON challenge?

Joris Lochy
Joris Lochy - Capilever - Brussels 04 December, 2023, 16:50Be the first to give this comment the thumbs up 0 likes

Thanks for the feedback. 

The "checksum method" in Belgium unfortunately does not solve the issue that customers pay the incorrect amount. The structured comment put on a lot of wire transfers in Belgium is a 12 digit code, for which the checksum is just a modulo 97 of the first 10 digits. So checksum guarantees quite well that the right structured comment is entered, but it does not guarantee if right amount is used.
For this, many companies work with QR codes on their invoice (and in future SEPA Request-to-Pay could be used), but a lot of customers don't use this QR code, so issue with incorrect amounts remains indeed a big hassle for most companies.

Ketharaman Swaminathan
Ketharaman Swaminathan - GTM360 Marketing Solutions - Pune 05 December, 2023, 08:44Be the first to give this comment the thumbs up 0 likes

TY for the clarification. 

Would your answer be any different if I clarified that customer didn't make an incorrect payment?

I meant that they consciously paid 80 out of 100 due to short payment, part payment, and other legit reasons covered by the payment schedule. For example, in computer hardware industry, the moment I ship the server, I am eligigible to raise the invoice for the full 100 but customer may pay 80% on delivery and 20% upon installation, which can take days or weeks after delivery. 

Joris Lochy
Joris Lochy - Capilever - Brussels 07 December, 2023, 21:23Be the first to give this comment the thumbs up 0 likes

If the payment amount is not matching in full with the invoice for a legitimate reason, it can still not be handled by the structured comment, but it should be possible for the matching to correctly handle this (and expect / anticipate this).
If invoice indicates that 80% should be paid on delivery and 20% on installation, it is possible to use 2 times the same structured comment, but obviously you will expect then 2 payments on different payment dates. 

Ketharaman Swaminathan
Ketharaman Swaminathan - GTM360 Marketing Solutions - Pune 08 December, 2023, 10:17Be the first to give this comment the thumbs up 0 likes

TY. Using the same structured comment twice should work. Sure, two different dates for the two payments is a feature, not bug, in this situation. 

Now hiring