Financial Reconciliation and Ledger Matching

What you will learn

• Perform two-way ledger reconciliation using pd.merge() with how='outer' and indicator=True
• Isolate unmatched records across system boundaries using merge indicator filters ('left_only', 'right_only')
• Detect financial variance anomalies on matched transaction keys by computing column differences
• Generate executive reconciliation exception summaries detailing missing deposits and fee discrepancies

What you need first

import pandas as pd
df1 = pd.DataFrame({'k': [1]})
df2 = pd.DataFrame({'k': [2]})
print(list(pd.merge(df1, df2, on='k', how='outer', indicator=True)['_merge']))

['left_only', 'right_only']

import pandas as pd
s1 = pd.Series([1000.0])
s2 = pd.Series([950.0])
print(float(s1 - s2))

50.0

Architecture and data flow

The data

Internal payment transaction ledger and external bank settlement statement.

Step by step

Read each step, run its code, then check your output against the one shown.

1. 01

   Load internal ledger and bank statement DataFrames

   We initialize the internal_ledger (5 transactions totaling 90,500.00 rupees) and external bank_statement (4 deposits totaling 59,500.00 rupees). Inspecting the raw data reveals clear differences: internal transaction TXN-105 is missing from the bank, bank deposit TXN-106 is missing from internal records, and TXN-102 has an amount discrepancy. In financial data engineering, verifying source dataset shapes establishes baseline reconciliation benchmarks before joining. Comparing source row counts and financial totals early ensures that subsequent merge operations account for every transactional dollar across complex payment ecosystems.

   PythonStep 1Copy

   import pandas as pd
   
   internal = pd.DataFrame({
       "txn_id": ["TXN-101", "TXN-102", "TXN-103", "TXN-104", "TXN-105"],
       "amount": [15000.0, 22000.0, 8500.0, 14000.0, 31000.0],
       "status": ["SETTLED", "SETTLED", "PENDING", "SETTLED", "SETTLED"]
   })
   
   bank = pd.DataFrame({
       "reference": ["TXN-101", "TXN-102", "TXN-104", "TXN-106"],
       "settled_amount": [15000.0, 21500.0, 14000.0, 9000.0]
   })
   
   print("Internal records:", len(internal), f"Sum: {internal['amount'].sum():.2f}")
   print("Bank records:", len(bank), f"Sum: {bank['settled_amount'].sum():.2f}")

   Show outputGuess what it prints first

   Internal records: 5 Sum: 90500.00
   Bank records: 4 Sum: 59500.00

   Common mistake

   Failing to check source row totals before merging, making it difficult to detect dropped records during join operations.

   How you will know

   You will see 5 internal records totaling 90500.00 and 4 bank records totaling 59500.00.

2. 02

   Attempt naive inner merge join

   Wrong turn first: this step shows the mistake before the fix

   Here is the naive mistake: writing pd.merge(internal, bank, left_on='txn_id', right_on='reference', how='inner'). Running an inner join silently drops all unmatched records: TXN-103, TXN-105, and TXN-106 disappear completely from the output. In financial accounting, an inner join creates a false impression that all records match, completely concealing the 31,000 rupee missing bank deposit and failing audit review. Inner joins silently discard non-matching keys on both sides, masking reconciliation discrepancies and failing audit scrutiny and exposing institutions to severe regulatory penalties during annual external compliance audits.

   PythonStep 2Copy

   import pandas as pd
   
   internal = pd.DataFrame({
       "txn_id": ["TXN-101", "TXN-102", "TXN-103", "TXN-104", "TXN-105"],
       "amount": [15000.0, 22000.0, 8500.0, 14000.0, 31000.0],
       "status": ["SETTLED", "SETTLED", "PENDING", "SETTLED", "SETTLED"]
   })
   bank = pd.DataFrame({
       "reference": ["TXN-101", "TXN-102", "TXN-104", "TXN-106"],
       "settled_amount": [15000.0, 21500.0, 14000.0, 9000.0]
   })
   
   inner_result = pd.merge(internal, bank, left_on="txn_id", right_on="reference", how="inner")
   print("Naive inner join row count:", len(inner_result))
   print("Matched IDs:", list(inner_result["txn_id"]))

   Show outputGuess what it prints first

   Naive inner join row count: 3
   Matched IDs: ['TXN-101', 'TXN-102', 'TXN-104']

   Common mistake

   Using inner merge for financial reconciliation, which silently discards unmatched rows on both sides.

   How you will know

   Observe that the inner join returns only 3 rows, completely omitting unmatched transactions TXN-103, TXN-105, and TXN-106.

3. 03

   Execute full outer merge with indicator tracking

   We resolve the missing record hazard by executing a full outer join with indicator=True: pd.merge(internal, bank, left_on='txn_id', right_on='reference', how='outer', indicator=True). The resulting _merge column explicitly labels the origin of each row: 'both' indicates matched transactions, 'left_only' identifies internal records missing from the bank, and 'right_only' identifies unlinked bank deposits. Outer joins with indicator tags provide transparent categorization of financial discrepancies across system boundaries. The indicator feature allows engineering teams to segment unmatched transactions into actionable operational queues for immediate accounting resolution.

   PythonStep 3Copy

   import pandas as pd
   
   internal = pd.DataFrame({
       "txn_id": ["TXN-101", "TXN-102", "TXN-103", "TXN-104", "TXN-105"],
       "amount": [15000.0, 22000.0, 8500.0, 14000.0, 31000.0],
       "status": ["SETTLED", "SETTLED", "PENDING", "SETTLED", "SETTLED"]
   })
   bank = pd.DataFrame({
       "reference": ["TXN-101", "TXN-102", "TXN-104", "TXN-106"],
       "settled_amount": [15000.0, 21500.0, 14000.0, 9000.0]
   })
   
   merged = pd.merge(internal, bank, left_on="txn_id", right_on="reference", how="outer", indicator=True)
   print("Outer join row count:", len(merged))
   print("Merge status breakdown:")
   for status, count in merged["_merge"].value_counts().items():
       print(f"  {status}: {count}")

   Show outputGuess what it prints first

   Outer join row count: 6
   Merge status breakdown:
     both: 3
     left_only: 2
     right_only: 1

   Common mistake

   Omitting indicator=True in outer merges, which forces analysts to write manual null checks across multiple columns.

   How you will know

   The output confirms 6 total rows: 3 matched in 'both', 2 in 'left_only', and 1 in 'right_only'.

4. 04

   Calculate amount variance on matched transaction records

   For transactions present in both ledgers (_merge == 'both'), we evaluate financial variances by subtracting settled_amount from internal amount. On TXN-101 and TXN-104, the difference is exactly 0.00 rupees. On TXN-102, however, the bank credited 21,500.00 against an internal expectation of 22,000.00, isolating a 500.00 rupee discrepancy caused by payment gateway processing deductions. Calculating numerical variances on matched records highlights partial settlements and unrecorded processing fee deductions. Identifying these micro-discrepancies ensures that ledger balances agree to the exact penny before financial statements are signed.

   PythonStep 4Copy

   import pandas as pd
   
   internal = pd.DataFrame({
       "txn_id": ["TXN-101", "TXN-102", "TXN-103", "TXN-104", "TXN-105"],
       "amount": [15000.0, 22000.0, 8500.0, 14000.0, 31000.0],
       "status": ["SETTLED", "SETTLED", "PENDING", "SETTLED", "SETTLED"]
   })
   bank = pd.DataFrame({
       "reference": ["TXN-101", "TXN-102", "TXN-104", "TXN-106"],
       "settled_amount": [15000.0, 21500.0, 14000.0, 9000.0]
   })
   
   merged = pd.merge(internal, bank, left_on="txn_id", right_on="reference", how="outer", indicator=True)
   matched = merged[merged["_merge"] == "both"].copy()
   matched["variance"] = matched["amount"] - matched["settled_amount"]
   
   for _, row in matched.iterrows():
       print(f"Matched {row['txn_id']}: Expected={row['amount']:.2f}, Bank={row['settled_amount']:.2f}, Variance={row['variance']:.2f}")

   Show outputGuess what it prints first

   Matched TXN-101: Expected=15000.00, Bank=15000.00, Variance=0.00
   Matched TXN-102: Expected=22000.00, Bank=21500.00, Variance=500.00
   Matched TXN-104: Expected=14000.00, Bank=14000.00, Variance=0.00

   Common mistake

   Failing to check for partial payments or fee deductions on matched keys, assuming key presence guarantees dollar equality.

   How you will know

   Output shows TXN-102 has a 500.00 rupee variance, while TXN-101 and TXN-104 have 0.00 variance.

5. 05

   Synthesize executive reconciliation exception report

   In the final step, we assemble an executive exception summary categorizing all financial discrepancies. We flag missing bank deposits (TXN-105 for 31,000.00 rupees), unlinked bank credits (TXN-106 for 9,000.00 rupees), pending transactions (TXN-103 for 8,500.00 rupees), and fee deductions (TXN-102 for 500.00 rupees). This actionable report provides treasury teams with the exact items required for month-end audit sign-off. Classifying reconciliation exceptions by root cause equips accounting teams with the specific details needed to post adjusting journal entries and clear audits.

   PythonStep 5Copy

   import pandas as pd
   
   internal = pd.DataFrame({
       "txn_id": ["TXN-101", "TXN-102", "TXN-103", "TXN-104", "TXN-105"],
       "amount": [15000.0, 22000.0, 8500.0, 14000.0, 31000.0],
       "status": ["SETTLED", "SETTLED", "PENDING", "SETTLED", "SETTLED"]
   })
   bank = pd.DataFrame({
       "reference": ["TXN-101", "TXN-102", "TXN-104", "TXN-106"],
       "settled_amount": [15000.0, 21500.0, 14000.0, 9000.0]
   })
   
   merged = pd.merge(internal, bank, left_on="txn_id", right_on="reference", how="outer", indicator=True)
   
   missing_in_bank = merged[merged["_merge"] == "left_only"]
   bank_only = merged[merged["_merge"] == "right_only"]
   matched = merged[merged["_merge"] == "both"].copy()
   matched["variance"] = matched["amount"] - matched["settled_amount"]
   fee_discrepancies = matched[matched["variance"] != 0]
   
   print(f"Missing in Bank: {list(missing_in_bank['txn_id'])} (Total: {missing_in_bank['amount'].sum():.2f})")
   print(f"Bank Only Credits: {list(bank_only['reference'])} (Total: {bank_only['settled_amount'].sum():.2f})")
   print(f"Fee Discrepancies: {list(fee_discrepancies['txn_id'])} (Variance: {fee_discrepancies['variance'].sum():.2f})")

   Show outputGuess what it prints first

   Missing in Bank: ['TXN-103', 'TXN-105'] (Total: 39500.00)
   Bank Only Credits: ['TXN-106'] (Total: 9000.00)
   Fee Discrepancies: ['TXN-102'] (Variance: 500.00)

   Common mistake

   Failing to segment exceptions by financial cause, handing accountants an undifferentiated list of discrepancies.

   How you will know

   The output categorizes missing bank transactions (TXN-103, TXN-105), bank-only credits (TXN-106), and fee discrepancies (TXN-102).

Build it yourself

Write your solution below, run it in your browser, and pass every check.

What the result means

Interpretation

The reconciliation pipeline pinpoints the exact drivers of the financial closing imbalance. Out of 90,500.00 rupees in internal records, 39,500.00 rupees did not appear in the bank statement: TXN-103 (8,500.00) was still pending gateway settlement, while TXN-105 (31,000.00) represented an unbatched transmission. Furthermore, a 500.00 rupee fee deduction was isolated on TXN-102. Identifying these specific exceptions enabled treasury to resolve all items prior to audit review.

The message you would send

Hi Finance and Accounting Team, the automated Pandas reconciliation pipeline has identified all discrepancies between the internal ledger and bank statements. The 31,000 rupee missing deposit was traced to an unbatched settlement file, and the 500 rupee fee variance was documented for journal entry. All accounts are now reconciled for audit sign-off.

Put it on your CV

Resume or LinkedIn bullet

Built an automated financial reconciliation pipeline in Pandas utilizing full outer joins, merge indicators, and fee variance auditing.

README for GitHub

# Financial Reconciliation and Ledger Matching

An enterprise financial data engineering pipeline in Pandas reconciling internal payment ledgers against bank clearing statements.

## Features
- Two-way ledger reconciliation via pd.merge(how='outer', indicator=True)
- Automated isolation of unmatched transactions across system boundaries
- Variance calculations on matched keys to detect hidden gateway fee deductions
- Executive audit exception reporting satisfying regulatory compliance standards

Keep going

• LessonPandas combining DataFrames
• Case studyDo the Two Systems Agree?
• Case studyPayWave Failed Recharge Reconciliation