One Payment, Two Rows

Pay $500 from checking to a credit card and two statements record it. Checking shows $500 going out. The card shows $500 coming in, a day or two later. If both rows reach the totals, that one payment inflates both income and expenses.

LedgerLift is my personal finance ETL. It parses statement PDFs and CSVs on my laptop into one SQLite ledger, and a small Next.js dashboard reads from it. Before the dashboard can add anything up, the pipeline has to decide which rows are two views of the same event.

Hard filters, then a score

Two rows only get scored if they come from different accounts and move in opposite directions, with amounts within one cent and dates within five days by default.

A pair that clears the filters gets a score:

def _score_candidate_pair(txn_a, txn_b, *, date_tolerance_days, amount_tolerance):
    if (txn_a.institution, txn_a.account_name) == (txn_b.institution, txn_b.account_name):
        return None
    if txn_a.direction == txn_b.direction:
        return None
    if abs(abs(txn_a.amount) - abs(txn_b.amount)) > amount_tolerance:
        return None
    date_diff = abs((txn_a.transaction_date - txn_b.transaction_date).days)
    if date_diff > date_tolerance_days:
        return None
    description_similarity = fuzz.token_set_ratio(txn_a.description_normalized, txn_b.description_normalized) / 100
    ipc_mismatch = txn_a.is_internal_payment_candidate != txn_b.is_internal_payment_candidate
    score = 1.0 - (0.1 * date_diff) - (0.2 if ipc_mismatch else 0.0)
    score += 0.1 * description_similarity
    return score, date_diff

ipc is "internal payment candidate", the flag for a row that looks like a card payment. Scored pairs are sorted best first and committed greedily until the score drops under 0.6, skipping any row already used. Ties break on date gap and then on transaction id, so the same input always produces the same pairs.

The weights decide which signal counts most. In one synthetic test, a checking debit flagged as a payment has two possible partners. One has an identical description but no flag, and it scores 0.9. The other has the flag and the description zzz, and it scores about 1.0. The flag wins. Description text can only break a tie. The arithmetic also caps how far apart a pair can be: with mismatched flags, four days apart tops out at 0.5 and never reaches 0.6.

I picked those constants by feel and tuned them by trial and error. If there was a deeper reason for 0.2, I've since lost it.

Keeping the debit side was wrong

An earlier version kept the checking side of a matched pair and dropped the card side. That seemed reasonable, since the checking debit is when money actually left. Then the dashboard totals came out far too high to be right. The purchases on that card were already counted as expenses when they happened, and the kept debit counted the same money again.

Both sides are excluded now. The comment in the dashboard query says it directly: the money "never left the user's network". Paying off a card is not spending. The spending happened at checkout.

The rows that can't pair

The scoring only works when both rows exist. Sweeps between linked cash accounts appear on only one statement, so the engine marks them as never expecting a pair. Card payments to a card whose old statements I don't have hit the same wall. The checking debit is all I have for that card, and there are no purchases to double-count. For that one case the dashboard still counts the unpaired debit as an expense, which is the only place the old keep-the-debit rule was right.

The hard part is knowing which rows you'll never get.