Match Records on Nearby Dates with a Tolerance

When to use this skill

• You’re reconciling two lists where the dates should line up but rarely do exactly: bank deposits vs. cash receipts, trade date vs. settlement date, invoice date vs. payment posting date.
• An exact-date VLOOKUP or merge leaves you with a pile of “unmatched” items that a human can see are obviously the same transaction, just a day or three apart.
• You need to pull the record “in effect as of” a cutoff date — the latest rate, address, or price on or before a given date.

The tool is pd.merge_asof: for each row on the left, it grabs the closest row on the right (within a key like account ID), looking backward, forward, or nearest — and you can cap how far it’s allowed to look with a tolerance. Document that tolerance; it’s a judgment call an auditor will ask about.

Inputs it expects

• Left table: the records you’re trying to match (e.g., GL cash receipts) with a datetime column and a key column (account or entity ID).
• Right table: the candidate matches (e.g., bank deposits) with its own datetime column and the same key.
• A matching direction: backward (closest on-or-before), forward (closest on-or-after), or nearest.
• A tolerance: the maximum allowable date gap, e.g. pd.Timedelta(days=3). Agree on this up front — it is your matching policy.
• Both tables must be sorted by the date column before the merge (merge_asof requires it).

Steps

1. Load both files and convert the date columns with pd.to_datetime — merge_asof will not work on text dates.
2. Sort both DataFrames by their date column (ascending). Sorting by the key isn’t required, but the dates must be in order.
3. Record the starting row counts of both tables — you’ll tie back to these.
4. Run pd.merge_asof with by= your key column, left_on=/right_on= the date columns, your chosen direction, and the agreed tolerance.
5. Rows that found no partner within the tolerance come back with NaN in the right-side columns — split those into an exceptions file.
6. Tie out control totals (below), then hand the exceptions to a reviewer.

Code

import pandas as pd

# Left: GL cash receipts. Right: bank deposits.
gl = pd.read_excel('gl_cash_receipts.xlsx')        # account_id, receipt_date, gl_amount
bank = pd.read_excel('bank_deposits.xlsx')         # account_id, deposit_date, bank_amount

gl['receipt_date'] = pd.to_datetime(gl['receipt_date'])
bank['deposit_date'] = pd.to_datetime(bank['deposit_date'])

# merge_asof requires both sides sorted by the date column
gl = gl.sort_values('receipt_date')
bank = bank.sort_values('deposit_date')

rows_in = len(gl)

# Documented matching policy: nearest deposit within 3 calendar days, same account
matched = pd.merge_asof(
    gl,
    bank,
    left_on='receipt_date',
    right_on='deposit_date',
    by='account_id',
    direction='nearest',            # or 'backward' / 'forward'
    tolerance=pd.Timedelta(days=3)  # the tolerance — agree on this and write it down
)

# Split matched vs. unmatched (no deposit found within tolerance -> NaN)
unmatched = matched[matched['deposit_date'].isna()]
found = matched[matched['deposit_date'].notna()]

print(f"GL receipts in:        {rows_in:,}")
print(f"Matched to a deposit:  {len(found):,}")
print(f"No match within 3 days:{len(unmatched):,}")

# Flag matches where the amounts still differ — closest date is not proof of same item
found = found.assign(amount_diff=(found['gl_amount'] - found['bank_amount']).round(2))
amount_exceptions = found[found['amount_diff'] != 0]

unmatched.to_excel('EXCEPTIONS_no_date_match.xlsx', index=False)
amount_exceptions.to_excel('EXCEPTIONS_amount_differences.xlsx', index=False)

Validation (control totals)

• Row counts: rows out of merge_asof must equal rows in on the left (len(matched) == rows_in). merge_asof never duplicates or drops left rows — if the count moved, something upstream changed.
• Matched + unmatched = total: len(found) + len(unmatched) == rows_in. State all three numbers in your workpaper.
• Amount tie-out: gl['gl_amount'].sum() must equal found['gl_amount'].sum() + unmatched['gl_amount'].sum() to the penny.
• Tolerance check: (found['receipt_date'] - found['deposit_date']).abs().max() must not exceed your documented tolerance.

Exceptions to surface

• Every row in EXCEPTIONS_no_date_match.xlsx — no candidate existed within the tolerance window for that account. These are potential deposits in transit, missing postings, or wrong-account items.
• Rows in EXCEPTIONS_amount_differences.xlsx — a date-close match was found, but the amounts differ. Nearest-date is a heuristic, not evidence; a human decides if it’s the same transaction with a fee/FX difference or a false match.
• Any right-side (bank) rows that were never used as a match — build this by anti-joining bank against found; unused deposits are just as interesting as unmatched receipts.
• Cases where one bank deposit matched multiple GL rows (check with found['deposit_date'].groupby(found['account_id']).count() or a duplicated-key check) — merge_asof allows many-to-one, and a reviewer must confirm that’s the real-world relationship.