Automate Bank Reconciliation with Python

Build a Python tool that reconciles two Excel files and flags discrepancies.

Introduction

30 Second Summary

If you have ever spent hours squinting at two spreadsheets, manually hunting for transactions that do not match up, you know how painful bank reconciliation can be.

In this project, you will build a Python script that automatically compares a company ledger against a bank statement, identifies discrepancies, and generates a professional reconciliation report in Excel.

What You'll Build

A Python-powered reconciliation tool that reads two Excel files, normalizes and matches transactions using reference IDs and date-plus-amount logic, and outputs a multi-sheet Excel report categorizing every transaction as matched or unmatched.

By the end of this project, you'll have:

  • A working Python script that reconciles two Excel files and flags discrepancies in seconds.
  • A multi-sheet Excel reconciliation report with a summary dashboard, matched transactions, and unmatched items from each source.
  • A reusable data comparison pattern using pandas that you can adapt to any two-source reconciliation task.
  • Secret Mission: Add fuzzy description matching to catch transactions where merchants appear differently across systems.

Are there any prerequisites?

You should be comfortable with basic Python concepts like variables, functions, and loops. No experience with pandas or Excel automation is required.

Create Sample Excel Data

Your Python environment is ready with pandas and openpyxl installed.

Before you can reconcile anything, you need two spreadsheets to compare. In this step, you will write a script that generates realistic sample data with deliberate discrepancies baked in.

In this step, get ready to:
  • Write a Python script that generates two Excel files with sample transaction data.
  • Run the script and verify both files contain realistic data with intentional mismatches.
Write the sample data generation script

Real reconciliation projects start with two data sources that should match but rarely do perfectly. Your script will create a company ledger with 20 transactions and a bank statement with only 18 transactions.

Why create fake data?

Working with sample data lets you control exactly which discrepancies exist. This means you can verify your reconciliation logic catches every type of mismatch before running it on real financial data.

  • In VS Code, create a new file called create_sample_data.py in your project folder.
  • Add the imports and company ledger data by pasting the following code:
import pandas as pd
from datetime import datetime, timedelta

# Create company ledger data
ledger_data = {
    "Date": [
        datetime(2026, 5, 1), datetime(2026, 5, 2), datetime(2026, 5, 3),
        datetime(2026, 5, 4), datetime(2026, 5, 5), datetime(2026, 5, 6),
        datetime(2026, 5, 7), datetime(2026, 5, 8), datetime(2026, 5, 9),
        datetime(2026, 5, 10), datetime(2026, 5, 11), datetime(2026, 5, 12),
        datetime(2026, 5, 13), datetime(2026, 5, 14), datetime(2026, 5, 15),
        datetime(2026, 5, 16), datetime(2026, 5, 17), datetime(2026, 5, 18),
        datetime(2026, 5, 19), datetime(2026, 5, 20),
    ],
    "Description": [
        "Office Supplies", "Client Payment - Acme Corp", "Internet Service",
        "Employee Payroll", "Software License", "Client Payment - Beta Inc",
        "Rent Payment", "Utility Bill", "Marketing Campaign",
        "Client Payment - Gamma LLC", "Travel Expense", "Insurance Premium",
        "Office Catering", "Client Payment - Delta Co", "Phone Bill",
        "Equipment Purchase", "Consulting Fee", "Client Payment - Echo Ltd",
        "Outstanding Check #1042", "Outstanding Check #1043",
    ],
    "Amount": [
        -150.00, 5000.00, -89.99,
        -12000.00, -299.00, 3500.00,
        -2500.00, -175.50, -1200.00,
        7500.00, -450.00, -800.00,
        -125.00, 2800.00, -65.00,
        -3200.00, -500.00, 4200.00,
        -750.00, -1100.00,
    ],
    "Reference": [
        "TXN001", "TXN002", "TXN003",
        "TXN004", "TXN005", "TXN006",
        "TXN007", "TXN008", "TXN009",
        "TXN010", "TXN011", "TXN012",
        "TXN013", "TXN014", "TXN015",
        "TXN016", "TXN017", "TXN018",
        "TXN019", "TXN020",
    ],
}

What does this code do?

This defines a dictionary with four columns of data for the company's internal transaction ledger.

  • Date holds 20 dates spanning May 1 to May 20, 2026.
  • Description contains human-readable transaction names like "Client Payment - Acme Corp" and "Rent Payment".
  • Amount uses negative values for money going out and positive values for money coming in.
  • Reference assigns a unique ID (TXN001 through TXN020) to each transaction for matching.

You won't see a change yet. The next chunk adds the bank statement data that this ledger will be compared against.

  • Below the ledger_data dictionary, add the bank statement data:
# Create bank statement data (18 transactions - missing 2 outstanding checks, has 1 bank fee)
bank_data = {
    "Date": [
        datetime(2026, 5, 1), datetime(2026, 5, 2), datetime(2026, 5, 3),
        datetime(2026, 5, 4), datetime(2026, 5, 5), datetime(2026, 5, 6),
        datetime(2026, 5, 7), datetime(2026, 5, 8), datetime(2026, 5, 9),
        datetime(2026, 5, 10), datetime(2026, 5, 11), datetime(2026, 5, 12),
        datetime(2026, 5, 13), datetime(2026, 5, 14), datetime(2026, 5, 15),
        datetime(2026, 5, 16), datetime(2026, 5, 17), datetime(2026, 5, 18),
    ],
    "Description": [
        "OFFICE SUPPLIES STORE", "ACME CORP PAYMENT", "ISP MONTHLY",
        "PAYROLL BATCH", "SOFTWARE CO", "BETA INC TRANSFER",
        "RENT - LANDLORD", "ELECTRIC COMPANY", "MKTG AGENCY",
        "GAMMA LLC WIRE", "TRAVEL BOOKING", "INSURANCE CO",
        "CATERING SERVICE", "DELTA CO TRANSFER", "PHONE CARRIER",
        "EQUIPMENT VENDOR", "CONSULTING FIRM", "ECHO LTD TRANSFER",
    ],
    "Amount": [
        -150.00, 5000.00, -89.99,
        -12000.00, -299.00, 3500.00,
        -2500.00, -175.50, -1200.00,
        7500.00, -450.00, -800.00,
        -125.00, 2800.00, -65.00,
        -3200.00, -500.00, 4200.00,
    ],
    "Reference": [
        "TXN001", "TXN002", "TXN003",
        "TXN004", "TXN005", "TXN006",
        "TXN007", "TXN008", "TXN009",
        "TXN010", "TXN011", "TXN012",
        "TXN013", "TXN014", "TXN015",
        "TXN016", "TXN017", "TXN018",
    ],
}

What does this code do?

This defines the bank's version of the same transactions. Notice two key differences from the company ledger:

  • Only 18 transactions instead of 20. The bank has not yet processed TXN019 and TXN020 (the outstanding checks). These are your first deliberate discrepancies.
  • Descriptions are different. The bank uses its own names like "ACME CORP PAYMENT" instead of "Client Payment - Acme Corp". This mirrors real-world data where each system labels transactions differently.

You won't see a change yet. The next chunk adds the code that turns these dictionaries into Excel files.

  • Below the bank_data dictionary, add the code that creates DataFrames and saves them to Excel:
# Create DataFrames
ledger_df = pd.DataFrame(ledger_data)
bank_df = pd.DataFrame(bank_data)

# Save to Excel
ledger_df.to_excel("company_ledger.xlsx", index=False)
bank_df.to_excel("bank_statement.xlsx", index=False)

print("Sample data created successfully!")
print(f"  company_ledger.xlsx: {len(ledger_df)} transactions")
print(f"  bank_statement.xlsx: {len(bank_df)} transactions")

What does this code do?

  • pd.DataFrame() converts each dictionary into a pandas DataFrame, which is a table-like data structure optimized for analysis.
  • .to_excel() writes each DataFrame to an Excel file. The index=False parameter prevents pandas from adding a row number column.
  • The print() statements confirm how many transactions were written to each file.
  • Save create_sample_data.py.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

import pandas as pd
from datetime import datetime, timedelta

# Create company ledger data
ledger_data = {
    "Date": [
        datetime(2026, 5, 1), datetime(2026, 5, 2), datetime(2026, 5, 3),
        datetime(2026, 5, 4), datetime(2026, 5, 5), datetime(2026, 5, 6),
        datetime(2026, 5, 7), datetime(2026, 5, 8), datetime(2026, 5, 9),
        datetime(2026, 5, 10), datetime(2026, 5, 11), datetime(2026, 5, 12),
        datetime(2026, 5, 13), datetime(2026, 5, 14), datetime(2026, 5, 15),
        datetime(2026, 5, 16), datetime(2026, 5, 17), datetime(2026, 5, 18),
        datetime(2026, 5, 19), datetime(2026, 5, 20),
    ],
    "Description": [
        "Office Supplies", "Client Payment - Acme Corp", "Internet Service",
        "Employee Payroll", "Software License", "Client Payment - Beta Inc",
        "Rent Payment", "Utility Bill", "Marketing Campaign",
        "Client Payment - Gamma LLC", "Travel Expense", "Insurance Premium",
        "Office Catering", "Client Payment - Delta Co", "Phone Bill",
        "Equipment Purchase", "Consulting Fee", "Client Payment - Echo Ltd",
        "Outstanding Check #1042", "Outstanding Check #1043",
    ],
    "Amount": [
        -150.00, 5000.00, -89.99,
        -12000.00, -299.00, 3500.00,
        -2500.00, -175.50, -1200.00,
        7500.00, -450.00, -800.00,
        -125.00, 2800.00, -65.00,
        -3200.00, -500.00, 4200.00,
        -750.00, -1100.00,
    ],
    "Reference": [
        "TXN001", "TXN002", "TXN003",
        "TXN004", "TXN005", "TXN006",
        "TXN007", "TXN008", "TXN009",
        "TXN010", "TXN011", "TXN012",
        "TXN013", "TXN014", "TXN015",
        "TXN016", "TXN017", "TXN018",
        "TXN019", "TXN020",
    ],
}

# Create bank statement data (18 transactions - missing 2 outstanding checks, has 1 bank fee)
bank_data = {
    "Date": [
        datetime(2026, 5, 1), datetime(2026, 5, 2), datetime(2026, 5, 3),
        datetime(2026, 5, 4), datetime(2026, 5, 5), datetime(2026, 5, 6),
        datetime(2026, 5, 7), datetime(2026, 5, 8), datetime(2026, 5, 9),
        datetime(2026, 5, 10), datetime(2026, 5, 11), datetime(2026, 5, 12),
        datetime(2026, 5, 13), datetime(2026, 5, 14), datetime(2026, 5, 15),
        datetime(2026, 5, 16), datetime(2026, 5, 17), datetime(2026, 5, 18),
    ],
    "Description": [
        "OFFICE SUPPLIES STORE", "ACME CORP PAYMENT", "ISP MONTHLY",
        "PAYROLL BATCH", "SOFTWARE CO", "BETA INC TRANSFER",
        "RENT - LANDLORD", "ELECTRIC COMPANY", "MKTG AGENCY",
        "GAMMA LLC WIRE", "TRAVEL BOOKING", "INSURANCE CO",
        "CATERING SERVICE", "DELTA CO TRANSFER", "PHONE CARRIER",
        "EQUIPMENT VENDOR", "CONSULTING FIRM", "ECHO LTD TRANSFER",
    ],
    "Amount": [
        -150.00, 5000.00, -89.99,
        -12000.00, -299.00, 3500.00,
        -2500.00, -175.50, -1200.00,
        7500.00, -450.00, -800.00,
        -125.00, 2800.00, -65.00,
        -3200.00, -500.00, 4200.00,
    ],
    "Reference": [
        "TXN001", "TXN002", "TXN003",
        "TXN004", "TXN005", "TXN006",
        "TXN007", "TXN008", "TXN009",
        "TXN010", "TXN011", "TXN012",
        "TXN013", "TXN014", "TXN015",
        "TXN016", "TXN017", "TXN018",
    ],
}

# Create DataFrames
ledger_df = pd.DataFrame(ledger_data)
bank_df = pd.DataFrame(bank_data)

# Save to Excel
ledger_df.to_excel("company_ledger.xlsx", index=False)
bank_df.to_excel("bank_statement.xlsx", index=False)

print("Sample data created successfully!")
print(f"  company_ledger.xlsx: {len(ledger_df)} transactions")
print(f"  bank_statement.xlsx: {len(bank_df)} transactions")
Run the script and verify the output

With the script saved, you can now generate your sample Excel files. Running the script will create both files in your project folder.

  • Run the script by entering this command in your terminal:
python3 create_sample_data.py
  • You should see this output:
Sample data created successfully!
  company_ledger.xlsx: 20 transactions
  bank_statement.xlsx: 18 transactions
  • Confirm the files exist by running:
ls
  • You should see company_ledger.xlsx, bank_statement.xlsx, and create_sample_data.py in the output.
  • Open company_ledger.xlsx in your file browser or Excel to verify it contains 20 rows of transaction data.
  • Open bank_statement.xlsx and confirm it has 18 rows with different description names from the ledger.

Seeing a ModuleNotFoundError?

If you see an error mentioning pandas or openpyxl not being found, your virtual environment might not be active. Check that your terminal prompt shows (venv) at the beginning. If not, activate it again and retry.

Help me fix a ModuleNotFoundError when running create_sample_data.py

What should I see in the Excel files?

The company ledger has 20 transactions from May 1 to May 20 with descriptive names like "Client Payment - Acme Corp". The bank statement has 18 transactions from May 1 to May 18 with abbreviated names like "ACME CORP PAYMENT".

The two missing transactions (TXN019 and TXN020) are outstanding checks that the bank has not yet processed. Your reconciliation script will detect these as discrepancies in the next steps.

Your sample data is ready. Next up, you will load these Excel files into pandas and normalize the data so it is clean enough for accurate matching.

Load and Normalize the Data

Your sample Excel files are ready with realistic transaction data. Now you need a way to read both files into Python so you can compare them programmatically.

In this step, you'll create the main reconciliation script. It will load both Excel files into pandas DataFrames and normalize every column so that matching works reliably across both data sources.

In this step, get ready to:
  • Load both Excel files into pandas DataFrames.
  • Normalize string, date, and amount columns for consistent matching.
  • Verify the data loaded correctly with summary output.
Create the reconciliation script and load the data

The reconciliation logic will live in its own file, separate from the data generation script. This keeps your project organized and lets you re-run the reconciliation without regenerating data.

  • Create a new file called reconcile.py in your project folder.
  • Paste the following code to load both Excel files:
import pandas as pd

# --- Step 1: Load Excel files ---
print("Loading Excel files...")
company_df = pd.read_excel("company_ledger.xlsx")
bank_df = pd.read_excel("bank_statement.xlsx")

print(f"Company ledger: {len(company_df)} transactions")
print(f"Bank statement: {len(bank_df)} transactions")

What does this code do?

  • pd.read_excel() reads an .xlsx file and returns a DataFrame. A DataFrame is a table-like structure with rows and columns.
  • company_df holds the 20-row company ledger. bank_df holds the 18-row bank statement.
  • The print() lines confirm how many rows loaded from each file.
  • Save reconcile.py.

You won't run the script yet. The next substep adds normalization logic that prepares the data for matching.

Normalize columns for consistent matching

Raw data from two different systems rarely looks identical. The bank might store descriptions in uppercase while the ledger uses title case. Trailing spaces, inconsistent date formats, and floating-point rounding can all cause false mismatches.

Why normalize before matching?

Without normalization, "TXN001" and "txn001 " would be treated as different references. That single trailing space or case difference would cause the match to fail. Normalization eliminates these cosmetic differences so the matching logic can focus on real discrepancies.

  • In reconcile.py, add the following normalization code below the loading section:
# --- Step 2: Normalize data ---
print("\nNormalizing data...")

# Strip whitespace from string columns
for col in ["Description", "Reference"]:
    company_df[col] = company_df[col].astype(str).str.strip()
    bank_df[col] = bank_df[col].astype(str).str.strip()

# Uppercase reference IDs for consistent matching
company_df["Reference"] = company_df["Reference"].str.upper()
bank_df["Reference"] = bank_df["Reference"].str.upper()

# Ensure dates are datetime
company_df["Date"] = pd.to_datetime(company_df["Date"])
bank_df["Date"] = pd.to_datetime(bank_df["Date"])

# Round amounts to 2 decimal places
company_df["Amount"] = company_df["Amount"].round(2)
bank_df["Amount"] = bank_df["Amount"].round(2)

print("Data normalized successfully.")
print(f"  Company date range: {company_df['Date'].min().date()} to {company_df['Date'].max().date()}")
print(f"  Bank date range: {bank_df['Date'].min().date()} to {bank_df['Date'].max().date()}")

What does this code do?

  • .astype(str).str.strip() converts each value to a string and removes leading/trailing whitespace.
  • .str.upper() forces all reference IDs to uppercase so "txn001" and "TXN001" are treated as the same value.
  • pd.to_datetime() ensures all dates are proper datetime objects, which allows date arithmetic later.
  • .round(2) ensures amounts like -150.004999 don't cause false mismatches against -150.00.
  • Save reconcile.py.
Run the script and verify the output
  • Run the reconciliation script by entering this command in your terminal:
python3 reconcile.py

You should see output confirming both files loaded and normalized:

Loading Excel files...
Company ledger: 20 transactions
Bank statement: 18 transactions

Normalizing data...
Data normalized successfully.
  Company date range: 2026-05-01 to 2026-05-20
  Bank date range: 2026-05-01 to 2026-05-18

What should I see?

The company ledger shows 20 transactions spanning May 1 to May 20. The bank statement shows 18 transactions spanning May 1 to May 18. The 2-transaction difference (TXN019 and TXN020) represents the outstanding checks that have not cleared the bank yet.

Seeing a FileNotFoundError?

  • Make sure you ran create_sample_data.py in the previous step. The Excel files must exist in the same folder as reconcile.py.
  • Check that your terminal is in the project folder. Run ls and confirm you see company_ledger.xlsx and bank_statement.xlsx listed.
  • Make sure your virtual environment is still active. You should see (venv) at the start of your terminal prompt.

Still stuck? Help me debug my FileNotFoundError when running reconcile.py

✔️ Awesome, I've got everything!

Great. Double check you've saved reconcile.py before moving on.

ⓧ I'd like to double check the full code

Here is the complete reconcile.py file so far:

import pandas as pd

# --- Step 1: Load Excel files ---
print("Loading Excel files...")
company_df = pd.read_excel("company_ledger.xlsx")
bank_df = pd.read_excel("bank_statement.xlsx")

print(f"Company ledger: {len(company_df)} transactions")
print(f"Bank statement: {len(bank_df)} transactions")

# --- Step 2: Normalize data ---
print("\nNormalizing data...")

# Strip whitespace from string columns
for col in ["Description", "Reference"]:
    company_df[col] = company_df[col].astype(str).str.strip()
    bank_df[col] = bank_df[col].astype(str).str.strip()

# Uppercase reference IDs for consistent matching
company_df["Reference"] = company_df["Reference"].str.upper()
bank_df["Reference"] = bank_df["Reference"].str.upper()

# Ensure dates are datetime
company_df["Date"] = pd.to_datetime(company_df["Date"])
bank_df["Date"] = pd.to_datetime(bank_df["Date"])

# Round amounts to 2 decimal places
company_df["Amount"] = company_df["Amount"].round(2)
bank_df["Amount"] = bank_df["Amount"].round(2)

print("Data normalized successfully.")
print(f"  Company date range: {company_df['Date'].min().date()} to {company_df['Date'].max().date()}")
print(f"  Bank date range: {bank_df['Date'].min().date()} to {bank_df['Date'].max().date()}")

Your data is loaded, clean, and ready for comparison. Next up, you'll write the matching logic that compares every transaction across both files and identifies discrepancies.

Match Transactions and Identify Discrepancies

Your data is loaded, cleaned, and ready for comparison. The core challenge of reconciliation is answering one question: which transactions appear in both sources, and which are missing from one side?

In this step, you'll implement two matching strategies. First, exact matching on reference IDs and amounts. Then, tolerance-based matching to catch transactions that differ slightly due to processing delays.

In this step, get ready to:
  • Merge both DataFrames and categorize transactions as matched or unmatched.
  • Add tolerance matching to catch timing differences between the two sources.
Implement exact matching with pd.merge()

The simplest way to compare two datasets is to join them on shared columns. pandas provides pd.merge() with an indicator=True parameter that labels every row as appearing in both DataFrames, only the left, or only the right.

  • In reconcile.py, add the following code at the bottom of the file, after the normalization section:
# --- Step 3: Exact matching on Reference + Amount ---
print("\nPerforming exact matching...")

merged = pd.merge(
    company_df,
    bank_df,
    on=["Reference", "Amount"],
    how="outer",
    suffixes=("_company", "_bank"),
    indicator=True,
)

# Categorize results
matched = merged[merged["_merge"] == "both"].copy()
unmatched_company = merged[merged["_merge"] == "left_only"].copy()
unmatched_bank = merged[merged["_merge"] == "right_only"].copy()

print(f"  Exact matches: {len(matched)}")
print(f"  Unmatched (company only): {len(unmatched_company)}")
print(f"  Unmatched (bank only): {len(unmatched_bank)}")

What does this code do?

pd.merge() joins the two DataFrames on the columns they share: Reference and Amount. The how="outer" parameter keeps all rows from both sides, even if they don't have a match.

The indicator=True parameter adds a _merge column with values both, left_only, or right_only. You then filter on that column to split results into three categories.

The .copy() calls create independent DataFrames. This is important in pandas 3.0 where Copy-on-Write is enabled by default.

  • Save reconcile.py.
  • Run the script in your terminal to check the matching results by running:
python3 reconcile.py
  • You should see 18 exact matches, 2 unmatched company transactions, and 0 unmatched bank transactions in the output.

Why 18 matches and 2 unmatched?

Your company ledger has 20 transactions (TXN001 through TXN020). The bank statement only has 18 (TXN001 through TXN018). The two outstanding checks (TXN019 and TXN020) haven't cleared at the bank yet, so they appear only on the company side.

Seeing 0 exact matches?

Make sure the normalization code from the previous step is still in your file above this new code. The Reference columns need to be uppercased before the merge can find matches.

Help me debug my exact matching code

Add tolerance matching for timing differences

Exact matching catches most transactions, but real-world data has timing differences. A payment recorded on Monday by the company might clear at the bank on Tuesday. For these edge cases, you'll compare remaining unmatched items using relaxed criteria: dates within 1 day and amounts within $0.01.

  • Add the following code at the bottom of reconcile.py, after the exact matching section:
# --- Step 4: Tolerance matching for remaining unmatched ---
print("\nPerforming tolerance matching...")

tolerance_matches = []

for idx_c, row_c in unmatched_company.iterrows():
    for idx_b, row_b in unmatched_bank.iterrows():
        date_diff = abs((row_c["Date_company"] - row_b["Date_bank"]).days)
        amount_diff = abs(row_c["Amount"] - row_b["Amount"])

        if date_diff <= 1 and amount_diff <= 0.01:
            tolerance_matches.append({
                "Company_Date": row_c["Date_company"],
                "Bank_Date": row_b["Date_bank"],
                "Company_Description": row_c["Description_company"],
                "Bank_Description": row_b["Description_bank"],
                "Amount": row_c["Amount"],
                "Match_Type": "Tolerance",
            })
            unmatched_company = unmatched_company.drop(idx_c)
            unmatched_bank = unmatched_bank.drop(idx_b)
            break

tolerance_df = pd.DataFrame(tolerance_matches)
print(f"  Tolerance matches found: {len(tolerance_df)}")

What does this code do?

This nested loop compares every remaining unmatched company transaction against every remaining unmatched bank transaction. For each pair, it checks whether the dates are within 1 day of each other and the amounts are within $0.01.

When a tolerance match is found, it gets recorded and both rows are dropped from their respective unmatched DataFrames. The break ensures each company transaction matches at most one bank transaction.

Why two matching strategies?

Exact matching is fast and reliable, but it misses transactions with minor discrepancies. Tolerance matching acts as a safety net for timing differences or rounding errors. In production reconciliation systems, you'd often stack 3-4 matching strategies from strictest to most relaxed.

  • Save reconcile.py.
  • Run the script again to see the full matching output by running:
python3 reconcile.py
  • You should see the tolerance matches line at the bottom of the output showing Tolerance matches found: 0. This is expected because our sample data's unmatched transactions (the outstanding checks) have no close counterpart in the bank statement at all.

Seeing a KeyError for Date_company or Date_bank?

These column names are created by the suffixes=("_company", "_bank") parameter in the merge above. Make sure your exact matching code includes that suffixes argument. Without it, pandas uses default suffixes and the column names won't match what the tolerance loop expects.

Help me fix my tolerance matching KeyError

✔️ Awesome, I've got everything!

Great. Double check you've saved reconcile.py and that your terminal output shows the matching results.

ⓧ I'd like to double check the full code

Here is the full reconcile.py file up to this point:

import pandas as pd

# --- Step 1: Load Excel files ---
print("Loading Excel files...")
company_df = pd.read_excel("company_ledger.xlsx")
bank_df = pd.read_excel("bank_statement.xlsx")

print(f"Company ledger: {len(company_df)} transactions")
print(f"Bank statement: {len(bank_df)} transactions")

# --- Step 2: Normalize data ---
print("\nNormalizing data...")

# Strip whitespace from string columns
for col in ["Description", "Reference"]:
    company_df[col] = company_df[col].astype(str).str.strip()
    bank_df[col] = bank_df[col].astype(str).str.strip()

# Uppercase reference IDs for consistent matching
company_df["Reference"] = company_df["Reference"].str.upper()
bank_df["Reference"] = bank_df["Reference"].str.upper()

# Ensure dates are datetime
company_df["Date"] = pd.to_datetime(company_df["Date"])
bank_df["Date"] = pd.to_datetime(bank_df["Date"])

# Round amounts to 2 decimal places
company_df["Amount"] = company_df["Amount"].round(2)
bank_df["Amount"] = bank_df["Amount"].round(2)

print("Data normalized successfully.")
print(f"  Company date range: {company_df['Date'].min().date()} to {company_df['Date'].max().date()}")
print(f"  Bank date range: {bank_df['Date'].min().date()} to {bank_df['Date'].max().date()}")

# --- Step 3: Exact matching on Reference + Amount ---
print("\nPerforming exact matching...")

merged = pd.merge(
    company_df,
    bank_df,
    on=["Reference", "Amount"],
    how="outer",
    suffixes=("_company", "_bank"),
    indicator=True,
)

# Categorize results
matched = merged[merged["_merge"] == "both"].copy()
unmatched_company = merged[merged["_merge"] == "left_only"].copy()
unmatched_bank = merged[merged["_merge"] == "right_only"].copy()

print(f"  Exact matches: {len(matched)}")
print(f"  Unmatched (company only): {len(unmatched_company)}")
print(f"  Unmatched (bank only): {len(unmatched_bank)}")

# --- Step 4: Tolerance matching for remaining unmatched ---
print("\nPerforming tolerance matching...")

tolerance_matches = []

for idx_c, row_c in unmatched_company.iterrows():
    for idx_b, row_b in unmatched_bank.iterrows():
        date_diff = abs((row_c["Date_company"] - row_b["Date_bank"]).days)
        amount_diff = abs(row_c["Amount"] - row_b["Amount"])

        if date_diff <= 1 and amount_diff <= 0.01:
            tolerance_matches.append({
                "Company_Date": row_c["Date_company"],
                "Bank_Date": row_b["Date_bank"],
                "Company_Description": row_c["Description_company"],
                "Bank_Description": row_b["Description_bank"],
                "Amount": row_c["Amount"],
                "Match_Type": "Tolerance",
            })
            unmatched_company = unmatched_company.drop(idx_c)
            unmatched_bank = unmatched_bank.drop(idx_b)
            break

tolerance_df = pd.DataFrame(tolerance_matches)
print(f"  Tolerance matches found: {len(tolerance_df)}")

Your reconciliation logic is working. You can now identify exactly which transactions match and which are missing from either side. Next up, you'll turn these results into a professional multi-sheet Excel report.

Generate the Reconciliation Report

Your matching logic is working. The script can identify which transactions appear in both sources and which are missing from one side. Now you need to turn those results into something a human can actually read and act on.

In this step, you will clean up the matched and unmatched DataFrames into report-friendly formats, calculate summary metrics, and write everything into a multi-sheet Excel reconciliation report.

In this step, get ready to:
  • Clean up the matched and unmatched DataFrames for clean report output.
  • Build a summary dashboard with key reconciliation metrics.
  • Write a multi-sheet Excel report and run the script end-to-end.
Clean up the output DataFrames

The merge operation created DataFrames with suffixed column names like Date_company and Description_bank. Before writing to a report, you need to rename these columns so they read cleanly.

  • In reconcile.py, find the line that says print(f" Tolerance matches found: {len(tolerance_df)}"), then add the following code below it:
# --- Step 5: Clean up output DataFrames ---
# Clean matched output
matched_output = matched[["Date_company", "Description_company", "Amount", "Reference"]].copy()
matched_output.columns = ["Date", "Description", "Amount", "Reference"]

# Clean unmatched company output
unmatched_company_output = unmatched_company[["Date_company", "Description_company", "Amount", "Reference"]].copy()
unmatched_company_output.columns = ["Date", "Description", "Amount", "Reference"]

# Clean unmatched bank output
unmatched_bank_output = unmatched_bank[["Date_bank", "Description_bank", "Amount", "Reference"]].copy()
unmatched_bank_output.columns = ["Date", "Description", "Amount", "Reference"]

What does this code do?

Each block selects only the columns relevant to that category (matched, unmatched company, or unmatched bank) and renames them to simple, clean headers.

The .copy() call ensures you get an independent DataFrame rather than a view that references the original. This prevents unexpected side effects when you modify the data later.

  • Save reconcile.py.

You won't see a change yet. The next chunk uses these cleaned DataFrames to calculate summary metrics.

Build the summary metrics

A reconciliation report needs a dashboard that answers the big questions at a glance: how many transactions matched, what percentage reconciled, and what dollar amounts remain unresolved.

  • Add the following code directly below the cleanup section you just wrote:
# --- Step 6: Generate reconciliation report ---
print("\nGenerating reconciliation report...")

total_company = len(company_df)
total_bank = len(bank_df)
total_matched = len(matched) + len(tolerance_df)
match_rate = (total_matched / max(total_company, total_bank)) * 100
unmatched_company_total = unmatched_company_output["Amount"].sum()
unmatched_bank_total = unmatched_bank_output["Amount"].sum()

summary_data = {
    "Metric": [
        "Total Company Transactions",
        "Total Bank Transactions",
        "Exact Matches",
        "Tolerance Matches",
        "Total Matched",
        "Match Rate (%)",
        "Unmatched Company Transactions",
        "Unmatched Bank Transactions",
        "Unmatched Company Amount",
        "Unmatched Bank Amount",
        "Reconciliation Status",
    ],
    "Value": [
        total_company,
        total_bank,
        len(matched),
        len(tolerance_df),
        total_matched,
        f"{match_rate:.1f}%",
        len(unmatched_company_output),
        len(unmatched_bank_output),
        f"${unmatched_company_total:,.2f}",
        f"${unmatched_bank_total:,.2f}",
        "BALANCED" if len(unmatched_company_output) == 0 and len(unmatched_bank_output) == 0 else "DISCREPANCY FOUND",
    ],
}
summary_df = pd.DataFrame(summary_data)

What does this code do?

  • total_matched combines exact matches and tolerance matches into a single count.
  • match_rate divides the total matched by whichever side has more transactions, giving you a percentage of how well the two sources reconcile.
  • The final status line checks whether any unmatched transactions remain. If both sides are fully reconciled, it reports BALANCED. Otherwise it flags DISCREPANCY FOUND.
  • Save reconcile.py.

You won't see a change yet. The next chunk writes these metrics and DataFrames into the final Excel file.

Write the Excel report and run the script

pandas ExcelWriter lets you write multiple DataFrames to separate sheets in a single Excel file. This is where everything comes together into the final deliverable.

  • Add the following code directly below the summary_df line you just wrote:
# Write to Excel with multiple sheets
with pd.ExcelWriter("reconciliation_report.xlsx", engine="openpyxl") as writer:
    summary_df.to_excel(writer, sheet_name="Summary", index=False)
    matched_output.to_excel(writer, sheet_name="Matched", index=False)
    if not tolerance_df.empty:
        tolerance_df.to_excel(writer, sheet_name="Tolerance Matches", index=False)
    unmatched_company_output.to_excel(writer, sheet_name="Unmatched Company", index=False)
    unmatched_bank_output.to_excel(writer, sheet_name="Unmatched Bank", index=False)

print("\n" + "=" * 50)
print("RECONCILIATION COMPLETE")
print("=" * 50)
print(f"Match Rate: {match_rate:.1f}%")
print(f"Total Matched: {total_matched}")
print(f"Unmatched Company: {len(unmatched_company_output)} (${unmatched_company_total:,.2f})")
print(f"Unmatched Bank: {len(unmatched_bank_output)} (${unmatched_bank_total:,.2f})")
print(f"\nReport saved to: reconciliation_report.xlsx")

What does this code do?

  • pd.ExcelWriter("reconciliation_report.xlsx", engine="openpyxl") opens a new Excel file for writing. The with block ensures the file is saved and closed automatically when the block finishes.
  • Each .to_excel(writer, sheet_name=...) call writes one DataFrame to its own named sheet.
  • The if not tolerance_df.empty check skips creating a Tolerance Matches sheet if no tolerance matches were found.
  • The print statements give you a quick terminal summary so you can confirm the results without opening the file.
  • Save reconcile.py.
  • Run the complete reconciliation script by running this command in your terminal:
python3 reconcile.py

You should see output showing loading, normalization, matching results, and a final summary ending with Report saved to: reconciliation_report.xlsx.

What should I see?

The terminal output should show 18 exact matches, 0 tolerance matches, 2 unmatched company transactions (the outstanding checks), and 0 unmatched bank transactions. The match rate should be 90.0%.

  • Confirm reconciliation_report.xlsx was created by running:
ls

You should see reconciliation_report.xlsx listed alongside your other project files.

  • Open reconciliation_report.xlsx in your file browser or a spreadsheet application (Excel, Google Sheets, or Numbers).
  • Verify that the file contains sheets named Summary, Matched, Unmatched Company, and Unmatched Bank.

Seeing a FileNotFoundError or empty report?

  • Make sure you ran create_sample_data.py first. The reconciliation script needs company_ledger.xlsx and bank_statement.xlsx to exist in the same folder.
  • Check that your terminal is in the project folder. Run ls and confirm you see both Excel files before running the script again.
  • If you see an indentation error, make sure the code inside the with pd.ExcelWriter(...) as writer: block is indented by exactly 4 spaces.

Help me debug my reconciliation report generation.

✔️ Awesome, I've got everything!

Great. Double check you have saved reconcile.py and confirmed the report opens with all expected sheets.

ⓧ I'd like to double check the full code

import pandas as pd

# --- Step 1: Load Excel files ---
print("Loading Excel files...")
company_df = pd.read_excel("company_ledger.xlsx")
bank_df = pd.read_excel("bank_statement.xlsx")

print(f"Company ledger: {len(company_df)} transactions")
print(f"Bank statement: {len(bank_df)} transactions")

# --- Step 2: Normalize data ---
print("\nNormalizing data...")

# Strip whitespace from string columns
for col in ["Description", "Reference"]:
    company_df[col] = company_df[col].astype(str).str.strip()
    bank_df[col] = bank_df[col].astype(str).str.strip()

# Uppercase reference IDs for consistent matching
company_df["Reference"] = company_df["Reference"].str.upper()
bank_df["Reference"] = bank_df["Reference"].str.upper()

# Ensure dates are datetime
company_df["Date"] = pd.to_datetime(company_df["Date"])
bank_df["Date"] = pd.to_datetime(bank_df["Date"])

# Round amounts to 2 decimal places
company_df["Amount"] = company_df["Amount"].round(2)
bank_df["Amount"] = bank_df["Amount"].round(2)

print("Data normalized successfully.")
print(f"  Company date range: {company_df['Date'].min().date()} to {company_df['Date'].max().date()}")
print(f"  Bank date range: {bank_df['Date'].min().date()} to {bank_df['Date'].max().date()}")

# --- Step 3: Exact matching on Reference + Amount ---
print("\nPerforming exact matching...")

merged = pd.merge(
    company_df,
    bank_df,
    on=["Reference", "Amount"],
    how="outer",
    suffixes=("_company", "_bank"),
    indicator=True,
)

# Categorize results
matched = merged[merged["_merge"] == "both"].copy()
unmatched_company = merged[merged["_merge"] == "left_only"].copy()
unmatched_bank = merged[merged["_merge"] == "right_only"].copy()

print(f"  Exact matches: {len(matched)}")
print(f"  Unmatched (company only): {len(unmatched_company)}")
print(f"  Unmatched (bank only): {len(unmatched_bank)}")

# --- Step 4: Tolerance matching for remaining unmatched ---
print("\nPerforming tolerance matching...")

tolerance_matches = []

for idx_c, row_c in unmatched_company.iterrows():
    for idx_b, row_b in unmatched_bank.iterrows():
        date_diff = abs((row_c["Date_company"] - row_b["Date_bank"]).days)
        amount_diff = abs(row_c["Amount"] - row_b["Amount"])

        if date_diff <= 1 and amount_diff <= 0.01:
            tolerance_matches.append({
                "Company_Date": row_c["Date_company"],
                "Bank_Date": row_b["Date_bank"],
                "Company_Description": row_c["Description_company"],
                "Bank_Description": row_b["Description_bank"],
                "Amount": row_c["Amount"],
                "Match_Type": "Tolerance",
            })
            unmatched_company = unmatched_company.drop(idx_c)
            unmatched_bank = unmatched_bank.drop(idx_b)
            break

tolerance_df = pd.DataFrame(tolerance_matches)
print(f"  Tolerance matches found: {len(tolerance_df)}")

# --- Step 5: Clean up output DataFrames ---
# Clean matched output
matched_output = matched[["Date_company", "Description_company", "Amount", "Reference"]].copy()
matched_output.columns = ["Date", "Description", "Amount", "Reference"]

# Clean unmatched company output
unmatched_company_output = unmatched_company[["Date_company", "Description_company", "Amount", "Reference"]].copy()
unmatched_company_output.columns = ["Date", "Description", "Amount", "Reference"]

# Clean unmatched bank output
unmatched_bank_output = unmatched_bank[["Date_bank", "Description_bank", "Amount", "Reference"]].copy()
unmatched_bank_output.columns = ["Date", "Description", "Amount", "Reference"]

# --- Step 6: Generate reconciliation report ---
print("\nGenerating reconciliation report...")

total_company = len(company_df)
total_bank = len(bank_df)
total_matched = len(matched) + len(tolerance_df)
match_rate = (total_matched / max(total_company, total_bank)) * 100
unmatched_company_total = unmatched_company_output["Amount"].sum()
unmatched_bank_total = unmatched_bank_output["Amount"].sum()

summary_data = {
    "Metric": [
        "Total Company Transactions",
        "Total Bank Transactions",
        "Exact Matches",
        "Tolerance Matches",
        "Total Matched",
        "Match Rate (%)",
        "Unmatched Company Transactions",
        "Unmatched Bank Transactions",
        "Unmatched Company Amount",
        "Unmatched Bank Amount",
        "Reconciliation Status",
    ],
    "Value": [
        total_company,
        total_bank,
        len(matched),
        len(tolerance_df),
        total_matched,
        f"{match_rate:.1f}%",
        len(unmatched_company_output),
        len(unmatched_bank_output),
        f"${unmatched_company_total:,.2f}",
        f"${unmatched_bank_total:,.2f}",
        "BALANCED" if len(unmatched_company_output) == 0 and len(unmatched_bank_output) == 0 else "DISCREPANCY FOUND",
    ],
}
summary_df = pd.DataFrame(summary_data)

# Write to Excel with multiple sheets
with pd.ExcelWriter("reconciliation_report.xlsx", engine="openpyxl") as writer:
    summary_df.to_excel(writer, sheet_name="Summary", index=False)
    matched_output.to_excel(writer, sheet_name="Matched", index=False)
    if not tolerance_df.empty:
        tolerance_df.to_excel(writer, sheet_name="Tolerance Matches", index=False)
    unmatched_company_output.to_excel(writer, sheet_name="Unmatched Company", index=False)
    unmatched_bank_output.to_excel(writer, sheet_name="Unmatched Bank", index=False)

print("\n" + "=" * 50)
print("RECONCILIATION COMPLETE")
print("=" * 50)
print(f"Match Rate: {match_rate:.1f}%")
print(f"Total Matched: {total_matched}")
print(f"Unmatched Company: {len(unmatched_company_output)} (${unmatched_company_total:,.2f})")
print(f"Unmatched Bank: {len(unmatched_bank_output)} (${unmatched_bank_total:,.2f})")
print(f"\nReport saved to: reconciliation_report.xlsx")

Secret mission

Add Fuzzy Description Matching

Your reconciliation tool catches transactions with matching references and amounts. But what about real-world data where the same merchant appears differently across systems? "AMZN Marketplace" in your ledger might show up as "Amazon.com" on the bank statement. Add fuzzy string matching to catch these near-misses and push your match rate even higher.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • Project folder on your Desktop (contains create_sample_data.py, reconcile.py, Excel files, and virtual environment).

Keep everything running

No action needed. Choose this if you're still actively building or want to experiment with the reconciliation script further.

  • Your project folder, virtual environment, and all generated Excel files remain on your Desktop.
  • Python, pandas, openpyxl, and thefuzz remain installed in the virtual environment.

Pause - I'll come back to this later

Deactivate the virtual environment to free up your terminal session. Your files stay intact for when you return.

  • Deactivate the virtual environment by running:
deactivate
  • When you come back, navigate to your project folder and reactivate the environment with source venv/bin/activate (macOS/Linux) or venv\Scripts\activate (Windows).

Delete - I don't want to use this again

Remove all project files and start fresh. This deletes your scripts, Excel data, virtual environment, and all installed packages.

  • Deactivate the virtual environment first (if active) by running:
deactivate
  • Navigate to your Desktop and delete the project folder by running:

macOS

cd ~/Desktop
rm -rf [[PROJECT_FOLDER="your project folder name"]]

Windows

cd ~\Desktop
rmdir /s /q [[PROJECT_FOLDER="your project folder name"]]
  • This removes all Python scripts, generated Excel files, and the virtual environment (including pandas, openpyxl, and thefuzz).

Nice Work!

Nice Work!

You just built a Python reconciliation engine that automates one of finance's most tedious tasks. What used to take hours of manual spreadsheet comparison now runs in seconds and produces a professional report.

Here's what you learned:

  • Loaded and normalized Excel data using pandas DataFrames, including whitespace stripping, date conversion, and amount rounding.
  • Implemented multi-strategy transaction matching (exact matching on reference IDs, plus tolerance matching on date and amount) to catch discrepancies across two data sources.
  • Generated a professional multi-sheet Excel reconciliation report with a Summary dashboard, Matched transactions, and Unmatched items for both sides.
  • Added fuzzy string matching to handle real-world description inconsistencies where merchants appear differently across systems.

Ready to quiz yourself?