Moneytrail

Overview

Moneytrail reads the CSV exports from four Dutch banks and reports who you pay and which payments repeat. It runs locally on DuckDB, so the data never leaves the machine and no connection to a bank account is needed. The core problems are entity resolution (working out who a payment went to) and periodicity detection, both measured against labelled synthetic data.

Architecture

bank export ─► importer ─► transactions ─► DuckDB ─► merchant resolution ─► recurring detection ─► report

Every importer produces the same transaction type, so the stages after import are independent of the bank format. The report lists, per recurring series, the merchant, cadence, current amount, a monthly equivalent, the next expected date and any price changes or signs that the payment has stopped.

Ingestion

Per-bank parsers map each format onto a common transaction schema with full provenance, the source file and row, so any result can be traced back to its line in the export. The bank is detected from the file itself. Formats differ in more than column names: one bank has no counterparty column at all, so the name and IBAN are parsed out of a fixed-width description field. Amounts are parsed into integer cents.

Storage and idempotent imports

Each transaction gets a content-based id, which makes imports idempotent: re-imported files and overlapping exports do not create duplicates. Identical payments within one file, such as two equal purchases on the same day, receive an occurrence number, so they stay distinct within a file while still matching across files.

Merchant resolution

One shop shows up under many names: branch numbers, city suffixes, payment terminal prefixes, provider wrappers and names truncated at 22 characters. The resolver is deliberately rule-based, so that every merge can be explained and every error traced to a specific rule.

Normalisation

Each name is first reduced to a matching key: lower case, with terminal and provider prefixes, "on behalf of" wrappers, legal forms, city names (including ones cut off mid-word by truncation) and tokens containing digits removed. Removing digit tokens discards branch and terminal numbers in one step.

Clustering

The resulting keys are clustered with union-find, which makes merging transitive and order-independent. Merges happen in two passes:

• Shared IBAN. Keys whose transactions share a counterparty IBAN are merged first, as the strongest signal.
• Pairwise similarity. Remaining clusters are merged when one key of at least six characters is a prefix of another (truncation), when one key's words are contained in the other's, ignoring generic words such as "restaurant", when one is an acronym of the other, such as a two to four letter abbreviation of a chain name, or when the RapidFuzz token-sort ratio is 90 or more.

Naming

Each cluster is named after its longest readable variant among those used for at least 15% of its payments, since statement names are abbreviated far more often than padded. Among spellings of that variant, normal casing is preferred over a terminal's capitals. A merchant that switched names part-way through, without the names interleaving, takes the newer name.

Recurring payments

Payments are grouped by merchant, type and direction and split into amount bands: sorted by amount, a new band starts wherever an amount exceeds the previous one by more than a factor of 1.35, which separates, for example, two subscriptions billed under one name. Each band is then tested against five schedules:

Four-weekly is a separate schedule because some services charge every 28 days, which gives 13 payments a year rather than 12. A band fits a schedule when at least three quarters of the gaps fall within its tolerance, and the best fit is the one with the smallest median error. The fraction criterion tolerates the occasional late or early collection, and the median error picks the closest schedule when more than one fits.

Hidden subscriptions

When a band does not fit any schedule, each exact amount is tested separately, which recovers a fixed fee hidden among one-off purchases at the same merchant. Series picked out this way need at least three payments: a single pair of payments a year apart counts as a yearly renewal only when nothing else from that merchant is in the data, since two equal receipts at a supermarket are a coincidence.

Price changes and stopped payments

Amounts within 3% are treated as one price when deciding whether a series is coherent, while price changes are detected on exact amounts, because fixed charges repeat to the cent and a rent increase can be as small as 3%. A new amount counts as a price change when it repeats or is the latest charge. A single deviating amount in the middle, such as a correction or a prorated month, is ignored. A series is marked as stopped when the last payment is overdue by more than half a period, and active series get a predicted next payment date.

Evaluation

Synthetic ground truth

A generator produces 18 months of synthetic transactions for a persona with ground-truth labels, and writes the same history in all four banks' formats, so the importers are exercised as well as the matching rules. Collections and transfers are moved off weekends, as in real statements. There are two sets: a development set written alongside the rules, and a held-out set written separately, without access to the matching code, for a different persona with different shops and additional kinds of noise: several shops paid through one payment provider's account, unrelated businesses with near-identical names, branch numbers attached to shop names and more truncation widths. The held-out set is used only for reporting and is replaced once the rules are changed in response to it.

Metrics

Grouping is scored with B-cubed F1, which credits each transaction for how well its predicted cluster matches its true cluster. Naming is scored separately as the share of transactions given the right merchant name. A detected recurring series counts as correct when at least 80% of its transactions belong to one true series and it covers at least 80% of that series, which gives series-level precision and recall. Price changes and cancellations are scored as recall over the labelled events. Results are over 6 random seeds and 4 bank formats, about 1,050 transactions per file.

The held-out numbers are the reference figures. The gap to the development set shows how far rules written against one set of statements carry over to unseen ones.

Error analysis

• Wrong merges come mainly from payment-provider IBANs shared by unrelated shops, where a shared IBAN is not evidence of one merchant.
• Missed merges come from branch numbers attached to shop names, which remove the whole word as a digit token, and from Dutch abbreviations and spacing variants.
• Naming misses include shops that appear only under a holding company's legal name, which rules cannot map to a trading name and which are left unnamed by design.
• The one missed recurring series is a yearly payment that occurs only once in the 18 months.

These point to the next refinements of the resolution rules: treating IBANs of known payment providers as weak evidence, splitting branch numbers off attached names and normalising Dutch abbreviations, followed by measurement on a new held-out set.