银行业看不见的支柱：深入探讨匹配与对账

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 手动编码、重复性任务以及对 Excel 的严重依赖.

金融服务领域的一个特别手动但自动化的流程是 匹配与调节. 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.

尽管有可用的 完善的软件 (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 大多数匹配任务通常依赖于自定义或手动解决方案, 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…​

了解更好的和解需要 剖析它的组成部分，即

• 它始于 收集和转换不同的数据集以进行比较. 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.

• 下一步是定义一个 精准匹配算法. 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.

• 一旦定义了匹配算法，我们就输入 比较阶段. 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.

• 最后， 已发现的差异必须转化为可操作的产出，例如报告、与同事或第三方的沟通或纠正措施（例如生成文件、消息或 SQL 语句来修复差异）。

金融服务匹配的复杂性是多种多样的。 让我们探索一下 一些典型的用例 在金融服务领域：

• 大多数银行都有 证券主文件, 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).

• 在零售银行业务中，显然有必要 唯一地识别并匹配特定的自然人. 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…​

• 一个非常相似的问题是 匹配一家公司或更具体地说是一家商店. 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.

• 另一个有趣的案例是 VISA 卡支付中授权和清算消息的匹配. 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.
  这种匹配也适用于其他支付用例，例如将预授权完成与其之前的预授权进行匹配，或者将退款与之前的购买进行匹配。

• 几乎涉及所有业务的财务用例是 发票和付款匹配. 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.

正如您所看到的，匹配绝非易事，但了解基本步骤有助于更好地匹配。 与此同时，尽管有其局限性，Excel 仍然是一个强大的（手动）匹配工具。 因此一个 quick reminder for everyone who wants
to do matching in Excel:

• 使用 VLOOKUP 进行匹配。 然而，VLOOKUP 有一定的局限性，例如，如果没有匹配项，它会给出错误，并且您只能在第一列上搜索。 一个强大的替代方法是使用 XLOOKUP，这
  does not have these limitations.

• 如果你需要一个 复合搜索键，在搜索数据集中添加一列，使用复合搜索键（即连接不同的属性，例如用“#”作为分隔符），然后使用 VLOOKUP/XLOOKUP 搜索这个新列。

• 有 注意点 使用 VLOOKUP 时：

  • 不要忘记添加“false”作为函数 VLOOKUP 的最后一个参数以确保精确匹配。

  • 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.

  • 不要在 Excel 中使用超过 100.000 行的数据集。 较大的数据集会给 Excel 的性能和稳定性带来问题。
    如果您在大型数据集上使用 VLOOKUP，将计算模式设置为“手动”也可能很有趣，否则每次对数据进行微小更改时，Excel 都会重新计算所有 VLOOKUP。

  • VLOOKUP 将列号作为第三个参数返回。 添加或删除列时，此数字不会动态调整，因此请记住在添加或删除列时进行调整。

  • 如果您只想匹配，可以使用公式“=IF(ISERROR(VLOOKUP( , ,1,假),“不匹配”,“匹配”)”

这些技巧可以帮助 加快您的手动匹配速度，但显然真正的自动化总是更好。

金融服务的匹配是 多方面的挑战, 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.

查看我所有的博客 https://bankloch.blogspot.com/

• SEO 支持的内容和 PR 分发。 今天得到放大。
• PlatoData.Network 垂直生成人工智能。 赋予自己力量。 访问这里。
• 柏拉图爱流。 Web3 智能。 知识放大。 访问这里。
• 柏拉图ESG。 碳， 清洁科技, 能源， 环境， 太阳能， 废物管理。 访问这里。
• 柏拉图健康。 生物技术和临床试验情报。 访问这里。
• Sumber: https://www.finextra.com/blogposting/25297/the-unseen-backbone-of-banking-a-deep-dive-into-matching-and-reconciliation?utm_medium=rssfinextra&utm_source=finextrablogs