Reconciliation, Net Settlement, and What Reconciliation Misses

Goal

You reconcile our ledger against the counterparty institution's settlement file to narrow the deviation down to the level of individual items, produce the net settlement position, and even find duplicate transfers that reconciliation, in principle, cannot catch. And you learn to write that result in a form that can leave the customer's premises.

Why it matters

Reconciliation is the work an FDE ends up doing most often at a bank site. It is also the work most often done wrongly.

The first pitfall is the reconciliation scope. Our ledger has items that are only authorized and for which no capture has come, and it is normal for these not to be in the counterparty's settlement file. If you reconcile without distinguishing state, dozens of normal items come up as anomalies and the real deviations are buried among them.

The second is how to read a divergence. You must look at the count and the amount separately. If the count is the same and only the amount differs, it means the two sides know the same item with different amounts, and if the amount is the same and only the count differs, it means one side grouped or split several items. The causes differ, so the window through which you raise it with the customer differs too.

The third is the most important. Reconciliation asks "are the two books the same," not "are the books right." If the same money went out twice through retransmission after a timeout, both our ledger and the counterparty's file hold exactly two items. The total reconciliation passes perfectly. The only place it gets caught is the idempotency key.

This case goes like this. We received the 2026-08-25 settlement file, and word came that the totals do not match. There are three institutions, and two of them diverge in different ways.

Steps

Create and run /root/bank/gen_settle.py to make /root/bank/settle.db and /root/bank/partner_20260825.csv.
Load the settlement file into settle.db as a partner table, and pull out per-institution totals into /root/bank/recon_totals.csv. The header is counterparty,our_count,our_amount,partner_count,partner_amount,count_gap,amount_gap, and on our side you count only the items whose state is SETTLED.
Pull out /root/bank/only_ours.csv and /root/bank/only_partner.csv with the header msg_id,counterparty,amount, and write three lines, only_ours=, only_partner=, and naive_only_ours=, in /root/bank/side_counts.txt. The last value is the number when you reconcile without distinguishing state.
In /root/bank/amount_mismatch.csv, with the header msg_id,our_amount,partner_amount,gap, pull out the items whose amounts differ.
In /root/bank/dup_msg.csv, with the header idem_key,msg_id,amount, keep all the messages attached to duplicated idempotency keys, and write three lines, dup_keys=, dup_msgs=, and extra_amount=, in /root/bank/dup.txt.
In /root/bank/net_position.csv, with the header counterparty,out_amount,in_amount,net, produce the per-institution net settlement position. net is receipts minus payments.
Write six lines in /root/bank/pending.txt: auth_count=, auth_amount=, auth_out_amount=, auth_in_amount=, stale_auth=, and suspense_account=. stale_auth is the authorization items whose sent_at is earlier than 2026-08-22, and suspense_account is the suspense account's account code 2210.
Write a report in /root/bank/recon_report.md with six sections: ## 대사 결과 요약, ## 건수는 같은데 금액이 다른 건, ## 금액은 같은데 건수가 다른 건, ## 대사로는 잡히지 않는 것, ## 차액정산 포지션, and ## 개인정보 취급 (the Korean headings mean: Summary of reconciliation results, Items with the same count but different amounts, Items with the same amount but different counts, What reconciliation does not catch, Net settlement position, and Handling of personal information). Mask account numbers by keeping only the first 3 and last 4 digits, in the form 110-****-**1234, and do not write resident registration numbers or customers' real names in any form.

Notes

To handle the CSV with SQL, create the table first and then load: after CREATE TABLE partner(...), use .import --csv --skip 1 <파일> partner (the placeholder is the file).
If you load the same file twice, the rows double. If you put DROP TABLE IF EXISTS partner before loading, it is the same no matter how many times you run it.
You get the excess of a duplicate transfer with SUM(amount * (건수 - 1)) (the Korean word there is the count). The first transmission is normal, so do not subtract it.
Common mistake 1: not narrowing by state in step 3. The deviation looks like 18 items instead of 2.
Common mistake 2: finding duplicates by msg_id in step 5. A message number is newly assigned on each retransmission, so nothing comes out.
Common mistake 3: copying the account number as it is in step 8 just because it was visible on the investigation screen. Leaving personal information that the report does not need is itself an incident.

Build our ledger and the counterparty settlement file

Create and run /root/bank/gen_settle.py to make /root/bank/settle.db and /root/bank/partner_20260825.csv.

Build /root/bank/settle.db and /root/bank/partner_20260825.csv with python3. Of the 259 messages, 16 are in the authorized-only state.

Load the settlement file and match totals per institution

Load the settlement file into settle.db as a partner table, and pull out per-institution totals into /root/bank/recon_totals.csv. The header is counterparty,our_count,our_amount,partner_count,partner_amount,count_gap,amount_gap, and on our side you count only the items whose state is SETTLED.

If you load the counterparty file into settle.db as a partner table, you can reconcile with a single SQL statement. On our side, count only the items whose state is SETTLED, and look at the count and the amount separately.

Tell apart the items present on only one side

Pull out /root/bank/only_ours.csv and /root/bank/only_partner.csv with the header msg_id,counterparty,amount, and write three lines, only_ours=, only_partner=, and naive_only_ours=, in /root/bank/side_counts.txt. The last value is the number when you reconcile without distinguishing state.

The reconciliation scope is only the captured items. It is normal for authorized-only items not to be in the counterparty file, so if you do not narrow by state, all 16 normal items come up as anomalies.

Find the items with different amounts

In /root/bank/amount_mismatch.csv, with the header msg_id,our_amount,partner_amount,gap, pull out the items whose amounts differ.

Join on the message numbers present on both sides and keep only those whose amounts differ. gap is our amount minus the counterparty's amount, and their sum must equal the amount difference in the institution total.

Find duplicate transfers by idempotency key

In /root/bank/dup_msg.csv, with the header idem_key,msg_id,amount, keep all the messages attached to duplicated idempotency keys, and write three lines, dup_keys=, dup_msgs=, and extra_amount=, in /root/bank/dup.txt.

Group by idempotency key, not by message number. If two or more messages are attached to the same idempotency key, a retransmission was processed in duplicate. The excess is what is left after keeping one item per key.

Produce the net settlement position

In /root/bank/net_position.csv, with the header counterparty,out_amount,in_amount,net, produce the per-institution net settlement position. net is receipts minus payments.

For each institution, sum payments (OUT) and receipts (IN) separately, and net is receipts minus payments. The targets are the settled items.

Count the pending and suspense account balance

Write six lines in /root/bank/pending.txt: auth_count=, auth_amount=, auth_out_amount=, auth_in_amount=, stale_auth=, and suspense_account=. stale_auth is the authorization items whose sent_at is earlier than 2026-08-22, and suspense_account is the suspense account's account code 2210.

Count the authorized-only items split by direction, and count separately the items with old authorization dates. You manage pending not by count but by age.

Write a masked reconciliation report

Write a report in /root/bank/recon_report.md with six sections: ## 대사 결과 요약, ## 건수는 같은데 금액이 다른 건, ## 금액은 같은데 건수가 다른 건, ## 대사로는 잡히지 않는 것, ## 차액정산 포지션, and ## 개인정보 취급 (the Korean headings mean: Summary of reconciliation results, Items with the same count but different amounts, Items with the same amount but different counts, What reconciliation does not catch, Net settlement position, and Handling of personal information). Mask account numbers by keeping only the first 3 and last 4 digits, in the form 110-****-**1234, and do not write resident registration numbers or customers' real names in any form.

You need six sections. And this document leaves the customer's premises — mask account numbers, and do not write resident registration numbers or real names at all.