Learn Reconciling Transactions with Python | Automating Accounting Workflows

Reconciling transactions is a critical accounting process where you ensure that two sets of records—typically your organization's internal ledger and an external source like a bank statement—match up. This process helps confirm that all transactions are accounted for and that there are no discrepancies due to errors, omissions, or fraud. Manual reconciliation is often tedious and time-consuming, especially when dealing with large volumes of transactions. Accountants must sift through records line by line, comparing dates, amounts, and descriptions, which increases the risk of missing discrepancies or making mistakes. Automating this workflow with Python can dramatically reduce errors and save significant time.


              1234567891011121314151617181920212223242526
            
import pandas as pd

# Sample bank transactions
bank_data = {
    "Date": ["2024-06-01", "2024-06-02", "2024-06-03"],
    "Description": ["Deposit", "Withdrawal", "Payment"],
    "Amount": [1000, -200, -150]
}
bank_df = pd.DataFrame(bank_data)

# Sample ledger transactions
ledger_data = {
    "Date": ["2024-06-01", "2024-06-02", "2024-06-04"],
    "Description": ["Deposit", "Withdrawal", "Fee"],
    "Amount": [1000, -200, -50]
}
ledger_df = pd.DataFrame(ledger_data)

# Merge on all columns to find exact matches
merged = pd.merge(bank_df, ledger_df, how="outer", indicator=True)

# Find transactions not matched in both datasets
unmatched = merged[merged["_merge"] != "both"]

print("Unmatched transactions:")
print(unmatched)

The logic behind identifying unmatched records relies on comparing all key details, such as date, description, and amount, between the bank and ledger datasets. By merging the two DataFrames with the outer option, you can see which transactions exist in both sources and which are unique to either one. The _merge indicator column flags each row as belonging to the left-only (bank), right-only (ledger), or both datasets. Using boolean indexing, you can quickly filter for transactions that do not have a match. Python automates these steps, allowing you to reconcile thousands of transactions in seconds instead of hours, and ensures accuracy by systematically applying the same logic every time.


              1234567891011121314151617181920
            
import pandas as pd

# Example datasets
bank = pd.DataFrame({
    "Date": ["2024-06-01", "2024-06-02"],
    "Amount": [500, -100]
})
ledger = pd.DataFrame({
    "Date": ["2024-06-01", "2024-06-03"],
    "Amount": [500, -50]
})

# Merge and flag discrepancies
merged = pd.merge(bank, ledger, on=["Date", "Amount"], how="outer", indicator=True)
discrepancies = merged[merged["_merge"] != "both"]

# Report discrepancies
for idx, row in discrepancies.iterrows():
    source = "Bank" if row["_merge"] == "left_only" else "Ledger"
    print(f"Discrepancy found in {source}: Date {row['Date']}, Amount {row['Amount']}")

1. What is the primary goal of transaction reconciliation?

2. Which pandas function is useful for comparing two DataFrames to find differences?

3. Fill in the blanks to merge two DataFrames and filter for unmatched transactions.

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 3

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Suggested prompts:

Can you explain how the merge function works in this context?

What should I do if there are partial matches, like the same amount but different dates?

How can I adapt this code for larger datasets or real bank statements?

Swipe to show menu