Chapter one: An overview of the reconciliation system

What is reconciliation?

1. Checking accounts in life — the story of a pancake stall owner

Checking accounts is very common in our life.

Here’s an example: On your way home from work, you’re a little hungry and you see a pancake by the side of the road. So you go over and buy a pancake. The boss hands you the pancake and points to the QR code sticker on the wall and says “8 yuan”.

You take out your mobile phone, scan the code to pay 8 yuan, and the boss said “8 yuan, past”. Then the boss heard his mobile phone said: “WeChat collection: 8 yuan.” The boss was mentally assured that the money had arrived. You declare “$8 paid”, and the boss’s account confirms “$8 received”. This is the simplest reconciliation.

2. Payment reconciliation system, a common account reconciliation scene in Internet companies

In Internet companies, as long as you carry a transaction, you need to reconcile accounts. Whether it is a Taobao shop selling physical goods, an online course for virtual goods, or a video website selling various membership services, accounts checking is the last line of security in the whole transaction process.

Let’s take an example of a Taobao shop. For the convenience of understanding, this Taobao shop only sells one kind of cup, and each cup is 20 yuan. There is only one business in a month, and one order sells one cup.

When a user places an order, the printer automatically prints out a delivery order. The boss takes the paper order to the warehouse to pick up the goods, and then gives it to the Courier company for delivery. The user receives the cup and confirms receipt. $20 was transferred to the boss’s account.

(1) The next day after delivery, the boss took inventory of the warehouse and found that one cup was missing. At the same time, I found that I had 20 yuan in my account, which was exactly the price of one cup. This process of reconciliation is called an actual reconciliation.

(2) The boss checked the account according to the printed order. He had a paper order in his hand and the total amount of the order was 20 yuan. Then he looked at the amount in his account and found that it was 20 yuan too much. This reconciliation process is called account reconciliation.

(3) when checking at the end of the month, found that the account was only 10 yuan more, so the boss turned over all the books, found that the order account was 20 yuan more, and the express delivery account was reduced by 10 yuan due to delivery (express delivery fee). One calculation, income 20 yuan (increase), express fee 10 yuan (decrease), namely account account should increase 10 yuan. This reconciliation is called an account reconciliation.

3. Account reconciliation — Three account reconciliation methods in cup shops

(1) Actual reconciliation of accounts: it refers to the reconciliation of accounts recorded by us with the actual quantity of physical assets.

(2) Account reconciliation: it refers to the verification of their own books and accounting vouchers. Generally, the billing vouchers are provided by the third party company that does business with you. In the case of the cup shop above, the billing vouchers are provided by Taobao (the order).

(3) Account reconciliation: it refers to the reconciliation between the upstream and downstream interconnected accounts. In the whole transaction process, multiple sets of accounts are generally involved in the upstream and downstream. The upstream accounts include external purchase accounts and internal purchase accounts, while the downstream accounts include express delivery accounts and third-party service fees. There is a great deal of correlation between these accounts and the general ledger, so general reconciliation is usually used to correct inconsistencies in the internal accounts.

“Account confirmation” is the basis of reconciliation, and we will explain in combination with examples in the reconciliation system construction process, how to “account confirmation” into the reconciliation system.

II. Why reconciliation

1. Checking accounts is the last line of security in the whole payment system. It is an important error-correcting mechanism in the transaction process.

2. Avoid accidents and human error

(1) Unexpected errors: network stability, robustness of internal systems (order, payment, risk control).

(2) Human error: human operation error, upstream, internal malicious modification and other behaviors.

3. Reconciliation is an important part of the financial process, especially when the transaction volume is tens of thousands per day and manual reconciliation is impossible. In order to avoid the accumulation of order errors and become muddied accounts, we need to settle every day, and reconciliation is also a necessary link to ensure the financial health of the company.

Chapter two: the structure of account checking system

No matter how complex and how grand the account checking system is, it is composed of the simplest comparative check between each “account and account”. We just need to figure out how each set of accounts is checked, and then apply that logic to multiple sets of accounts. Everything changes.

In order to reduce the complexity of the case and facilitate everyone’s understanding, this case uses two payment channels, “Alipay” and “WeChat”, which will be accessed by most e-commerce companies, for example.

In fact, no matter it is the “Alipay” that connects to the Internet, the “bank” that connects to the state-owned enterprise, or the overseas payment channel “Paypal”, it is all similar. In addition to the differences in interface, file format, authentication and other details, at the abstract level, the reconciliation logic is consistent and universal.

I. How to build an account reconciliation system



1. Set the target account for reconciliation

The core of reconciliation is finding books that record the same events in different systems and comparing them to find errors.

Such as

(1) Compare the daily settlement amount in our back-end order system with that in the third-party payment system.

(2) The inventory in our stock system is compared with the quantity of goods shipped in the order in our order system

2. Obtaining bill data (bill acquisition module)

(1) enter an item of expenditure in the accounts time: the daily settlement bill generation time of each channel is different, some are generated in the early morning, and some are generated at 9 am. Make clear the enter an item of expenditure in the accounts time, set the crawl/download time, let the system automatically download (or manually download and then upload to the account checking system).

(2) The format of the bill file: The format of the bill of each channel is not uniform, including CSV, XML, TXT, JSON, etc. For different channels, the corresponding format parser is set.

3. Standardization of bill format (data standardization module)

Each channel bill has different fields and names for the same data. For example, the same “order number”, Alipay is called “merchant order number”, and the same data in our system is called “shopping mall order number”. For another example, the payment amount of the same order is called “merchant paid” in Alipay and “settlement amount” in our system. Although they have different names, they are actually the reflection of the same data in two systems.

Therefore, our Prime Minister should unify the billing data from Alipay, WeChat, banking system, UnionPay, Paypal and other three institutions to make the data readable and comparable. This process is called billing data standardization.

4. Bills check (Bills check module)

This step must be designed according to the business logic of your company. Different business logic and financial management methods will have different design methods. But the basic tenet will remain the same: find out the data of the same order in the two systems, compare whether the two data are consistent, find out the differences, mark the differences, and deal with them in the next module. The details of bill checking are covered in Chapter 6.

5. Deal with billing errors (Difference Handling Module)

If the error is predictable and automatically fixable, we can use a machine to handle it automatically, such as the classic “interday payment” problem, where the order is created at 23:59 on the same day and the payment time is 00:01 the next day. At this time, the payment information of the third-party payment channel corresponding to the order in our system will appear on T+1. In this case, wait to get the next day’s bill, then check again can be solved.

If the error cannot be handled automatically, go to the manual process. The manual process first compares the two sets of data to find the problem, and then manually executes the solution so that the two sides are aligned. If it cannot be leveled at the moment, you can choose to “hang” to temporarily file the error, and then solve it at a suitable time in the future. We will discuss error handling in more detail in Chapter 7.

Chapter 3: Acquisition of reconciliation documents

The acquisition of reconciliation files is the starting point of the whole reconciliation system. We first need to download the statements of Alipay, WeChat, banks, UnionPay, third-party payment and other payment channels to the local area, and then analyze and store them before proceeding with subsequent reconciliation actions. Each payment channel has its own settlement cycle and settlement document generation time, as well as the file format. First of all, we need to check the payment channel documents to clarify these issues.

I. Download the reconciliation file

At present, there are several common ways to download channel statements

  • Call API to download the bill: this way is simple and clean, set the interface authentication and daily download time can be. Very friendly, Alipay, WeChat are this way.
  • SFTP/FTP download bill: is also a relatively simple and direct way to get, set the local directory and naming logic, directly download can be.
  • Manual download: a few channels still need to be manually downloaded, although you can write front-end automatic code to get it, but it is not always very direct.

II. Time to obtain reconciliation files

Each payment channel will agree on the time for daily statement generation according to its own situation. We should make clear the time for each payment channel to generate the bill, and then pull the bill for a period of time after this time, so as to make the account reconciliation system run efficiently.

For example: WeChat payment, the bill is usually issued at around 9 o ‘clock the next morning, and it will be more appropriate for us to pull the statement paid by WeChat after 10 o ‘clock.

For more information, please go to the corresponding official website document for confirmation. There are always some special cases, such as UnionPay clearance time is different from everyone, so be sure to read the documents. See the extended information section at the end of this article for the documentation of each payment channel.

III. Format of reconciliation file

The format of reconciliation files in different channels is also different, generally divided into CSV, JSON, TXT, XML and other formats. Here are two common file formats for your reference.

1. WeChat statement: TXT file

2. Alipay statement: CSV format

IV. API access to account reconciliation documents

The payment institution reconciliation file is obtained through API. The payment channel document is attached at the end of this document

WeChat pays to download the API document of the transaction bill

Chapter Four: Standardization of the reconciliation documents into storage

The reconciliation files obtained from the third-party payment channels every day are the original reconciliation data. These original files must be kept well so that in the future, when the whole payment or reconciliation system goes wrong, the source can be traced back to.

I. Standardized naming of the original reconciliation file

We need to re-name the original reconciliation files of various channels according to their own attributes, so as to facilitate future search and use.

Example: “Type of business _ payment channel _ settlement date _ serial number. File format: alipay_20210721_03.csv”

Of course, we need to find a set of appropriate naming methods more macroscopically according to the situation of all the payment channels our company has access to.

II. Unified standardization of reconciliation file data

Since each payment channel has its own set of field system, we need to unify the fields in the statement of each channel and store them in the database after standardization.

We can design the transformed field based on the field used by our own internal system as the origin. For redundant and temporary less than used fields can be directly discarded, reducing redundancy. When needed in the future, we can find the source data from the reconciliation file storage manager.

General statement field reference



Note: The above field is referencedInvincible yards farmersThank you for your selfless sharing. Click here for textdownload

If the company’s future business needs to access more payment channels, it can consider the expansion of the reconciliation system in advance and design a set of analysis process. The financial staff can set the corresponding relationship between the fields of the newly added reconciliation bill and the fields of the company’s internal order system in the background.

III. Check the reconciliation data in the warehouse

The reconciliation data of each channel is cleaned and the redundant information is unified in format, after being analyzed and stored in the database. We can easily check every day in the background, the reconciliation data of various channels, clear and clear. When there is a problem in any link of the reconciliation, financial personnel can check the reconciliation system here, and the data used in the reconciliation is easy to locate the problem.

Chapter 5: logical understanding of bill checking

After the local reconciliation data and the third-party payment reconciliation data are ready, we can check the data on both sides. There are generally only four states of reconciliation, the two sides are the same (leveling), the payment channel is overcharged (long money), the payment channel is undercharged (short money), the local and the payment channel are both sides, but the number is different (the amount is not consistent). Other errors are extensions of these states.

I. Check several error states and processing methods of the module



1. Receiving transaction reconciliation

  • Short payment error: it is recorded in our order but not in the payment channel statement. Simply put, it’s less money. Such errors are usually encountered in the “cross-day transaction”, the user placed an order at 23:59 minutes and paid at 00:01 minutes.
  • Long payment error: not recorded in our order, but the payment channel received the money. In short, it is overcharged. In general, such errors are mainly caused by our system not correctly accepting the payment success return information sent by the payment channel. This manual adjustment of the trade status can be.
  • Misaccount: There are records on both sides, but the amounts do not match.

2. Refund reconciliation

The error of the account reconciliation of the refund type is, in fact, much the same as the collection. There are several cases in general.

  • Local no refund, channel refund: usually the channel return data is abnormal, according to the channel status can modify the local refund status.
  • Local refund, channel no record (or vice versa, local no record, channel has been returned) : may be the “cross-day transaction” problem, if not, can only be handled manually.
  • The refund status is both local and local, but the amount on both sides is different: it needs to be checked and processed manually.

If the accounting idea is difficult to understand, we can also divide it into data groups. The following table

Let’s take a look at these two tables. The left table shows the data of our orders, and the right table shows the data of payment channels. The two tables are related by order IDs.

We can see that the data on both sides of ID1 are consistent, that is, “parallel”; ID2 and ID4 are missing respectively, that is, “unilateral”; Although ID3 has data on both sides, the single transaction amount is not consistent, namely “wrong account”.

The reconciliation system automatically flags “one-sided” and “mis-accounted” orders, which need to be manually processed through “error processing”.

Chapter 6: Logical design of account checking engine

First, the effect of the end date in the account reconciliation system

1. Reconcile your accounts chronologically

Because of the order payment refund related order, cross-day and other factors, the reconciliation must be in chronological order, sequential reconciliation, not cross-day reconciliation. If the actual date of the project is 1st, even though it is 15th today, the reconciliation must start from 1st. Since it is a one-sided account in t days, it needs to be checked in t+1 days. Jumping reconciliation can cause a lot of unnecessary trouble.

2. Reconciliation engine design



Be sure to follow the arrow direction from “start” through this diagram. This diagram is very informative. Please look at the flow chart carefully before reading further.

“Our reconciliation data” is taken as the basis point, and “order number” is used as the key to compare our reconciliation data with the channel party’s reconciliation data. The comparison results include “counterbalance”, “unilateral (short and short money)” and “wrong account”.

3. Creation and query of account reconciliation tasks

The whole reconciliation engine runs inside the server, and the front end is invisible and does not need to be seen. When creating tasks, financial personnel only need to set the start date and end date of reconciliation, tick the order library that needs reconciliation, and leave the rest to the reconciliation engine to complete. After reconciliation, financial personnel continue to deal with errors.

Chapter seven: reconciliation error handling

After the completion of automatic reconciliation, there will always be some errors that the system cannot automatically match. These errors are flagged during the reconciliation process. As we have said in the last chapter, the discrepancy errors include three categories: “long payment”, “short payment” and “wrong account”. In the actual reconciliation process, there are various reasons for the discrepancy, but no matter how strange the discrepancy is, the error processing process designed by us should be able to cover it.

I. Design of error processing logic

For common and regular error lists, we can design some rules to deal with them automatically, such as cross-day trading issues, subtle differences in calculation rules for three-party channel coupon waivers, currency conversion and other issues.

Each has its own unique transaction process, financial management methods, business characteristics, so the errors in the reconciliation are also strange, but these errors can be eliminated, we only need to classify the errors encountered, design a good solution, a problem is solved, this kind of problem will be solved.

When the automatic rules cannot balance the account, we need to deal with it manually. If you can’t deal with it now, consider suspending the bill to deal with it at a more appropriate time in the future.

II. Systematize error handling business rules

When errors cannot be handled automatically and need to be handled manually, we need to write a set of standardized procedures and standard steps into the system.

Example: When the reconciliation error is “long payment”, the payment channel shows that the payment is successful, and our order inquiry is empty, so we cancel the order. At this time, the financial personnel need to initiate the “replenishment”, the “replenishment” review process, we can regard it as a processing option, put in the “manual processing”.

Three, check the four states of the module

  • Alteration normal: no abnormality in comparison between the two sides, marked as normal.
  • Error not handled: compare the two sides of the exception, mark the exception to wait for manual processing.
  • Error handled: mark handled after manual processing.
  • Error Hanged: Some problems that cannot be handled temporarily or ignored permanently are marked as hanging.

Chapter 8: How to quickly set up an account reconciliation system

I used Kalayun to build a set of account checking system according to the idea of this article. It took a few months to finish in 5 days. Kara Cloud helps back-end programmers solve problems such as database access and API calls, while front-end components can be used drag-and-drop to quickly build enterprise internal tools.

Cara cloud access database and API page, support common database and API, just simply fill in a form to complete the complex data access.

Chapter 9: Extended Information

I. Rapid construction tools for payment reconciliation system

  • Cara cloud – support fast access to databases, APIs, front-end components drag and play. Can quickly set up account checking system.

II. Payment channel access documents

  • WeChat pays for new access documents
  • QQ wallet access documents
  • Alipay open platform documents
  • Wing-paid development documentation
  • Bank of China Open Platform
  • China Merchants Bank One Network Payment
  • Industrial and Commercial Bank of China open platform
  • China Construction Bank opens the bank
  • Agricultural Bank of China opens the bank

III. Setting up reference materials for account reconciliation system and extended reading

  • Talk about reconciliation (I)
  • Talk about reconciliation (2)
  • MapReduce implements bill statistics
  • Exploration and practice of Youzan business account checking platform
  • Meituan distribution fund security management of account checking system construction
  • Midea payment – reconciliation system implementation
  • How to design the payment reconciliation system?

In this paper, the author

Jiang Chuan, Kara Yun CMO, B-end product manager, focusing on the implementation and construction of internal efficiency tools in enterprises

My personal WeChat: Hijiangchuan, welcome to join me WeChat.