Extracting Recurring Transactions From a Bank Statement

Finding subscriptions in a statement sounds like grouping by amount. It is not, because the same subscription rarely produces the same descriptor twice and frequently does not produce the same amount either.

The descriptor is the hard part

A statement line’s description is not a merchant name. It is a short, truncated field assembled by whoever originated the payment, and the assembly rules differ by rail.

• Card transactions carry a merchant descriptor limited to a small number of characters, into which the acquirer packs a name, often a city and country, sometimes a store number, and frequently a payment facilitator’s prefix. A facilitator prefix is why one physical coffee shop appears under a payment company’s name with the shop as a suffix — and why the suffix is the part that gets truncated.
• ACH and direct debits carry an originator name and a separate entry description set by the originator, plus a trace number. These are more stable month to month but the originator name is the billing entity, which is often a holding company nobody recognises.
• Standing orders and transfers carry a reference the customer typed, which may contain a date, an invoice number or a typo, and changes whenever they set up a new one.

The result is that the same monthly charge can appear as three different strings across three months, differing by an embedded date, a changed store number, or a truncation point that moved because the descriptor got a character longer.

Normalising a descriptor

Normalisation is a deterministic pipeline, not a model call. Each rule should be individually removable, because you will need to explain a bad grouping later.

RAW   "SQ *PARKSIDE CAFE      SPRINGFIELD IL 03/14"
  1. uppercase, collapse whitespace
  2. strip facilitator prefixes  ("SQ *", "PP*", "TST*", "PAYPAL *")
  3. strip a trailing date       (MM/DD, DD.MM, YYYYMMDD)
  4. strip a trailing city+state or city+country
  5. strip trailing "#" + digits and bare digit runs of 3+
  6. trim
KEY   "PARKSIDE CAFE"

Two rules for the whole exercise. Never overwrite the raw string — store the key alongside it, because normalisation rules get revised and you will want to re-derive keys over history. And never strip digits that could be part of a brand name; a rule that removes all digits merges two products from the same publisher into one subscription.

Cluster on the interval, not the amount

Within a descriptor cluster, recurrence is a property of the dates. Sort the transaction dates and look at the gaps.

• Monthly is not “30 days”. It is the same day of month, which produces gaps of 28 to 31, and which slides when the anniversary day does not exist — a charge on the 31st lands on the 28th in February and on the 30th in April. Test for a matching day of month within a small tolerance, or for the last-day-of-month rule, rather than for a fixed gap.
• Four-weekly is exactly 28 days and is a genuinely different cadence: thirteen payments a year rather than twelve. Any forecast built on treating it as monthly is short by one payment annually, so it is worth distinguishing rather than folding in.
• Weekly and fortnightly are 7 and 14 days with a stable weekday. A weekday match is a stronger signal than the gap because weekend and holiday processing shifts the date forward.
• Annual is around 365 days and needs more than twelve months of statements before it is visible twice. On a single year’s statements an annual subscription is indistinguishable from a one-off purchase, and any tool claiming otherwise is guessing from the merchant.

Amount comes in as a secondary signal with tolerance, not as a grouping key. Subscription prices rise; a monthly charge that goes from 9.99 to 12.99 is one subscription with a change point, and grouping on exact amount splits it into two that each look like a short-lived subscription that stopped and started. Usage-based recurring charges — utilities, metered services, phone bills — never repeat an amount at all, and they are the reason amount cannot be the primary key.

Building it

1. Extract transactions and validate them first. Run the balance identity described in extracting opening and closing balances before any analysis. A recurrence detector run over a statement with a missing page will confidently report that a subscription was cancelled.
2. Normalise descriptors with the deterministic pipeline above, keeping the raw string.
3. Group by key and discard groups with fewer than three occurrences unless the gap between two occurrences already matches a known cadence exactly.
4. Fit a cadence per group. Test monthly-by-day, 28-day, weekly-by-weekday and annual in that order; take the first that explains the observed dates within tolerance, and record which one, with the tolerance used.
5. Detect amount change points within the group rather than splitting on them, and record the effective date of each price.
6. Project the next date and flag a group whose expected occurrence has passed by more than the tolerance. A subscription that stopped is usually more interesting than one that continued.

What this method cannot find

Being explicit about the blind spots is what separates this from a tool that claims to find “all your subscriptions”.

Anything billed to a card that does not appear in these statements is invisible, and that includes the card whose bill appears as a single monthly payment line. A free trial that has not converted yet has no transaction to find. A merchant billing two products to the same descriptor produces one cluster with two overlapping cadences, which looks like an irregular one. A charge that alternates between two amounts — a plan with an add-on billed separately in alternate months — will fail a naive cadence fit. And a merchant that changes its descriptor entirely, which happens when a company rebrands or changes payment processor, breaks the cluster at exactly the point where continuity mattered.

The structural point behind most of that list is that recurrence is a property of a history, not of a statement. A single statement covers one period and contains at most one occurrence of a monthly charge, so detection must run over the accumulated transaction set rather than per-document. That in turn means the deduplication has to be right: reprocessing a statement you already hold, or an overlapping date range from a bank feed, inserts second copies of real transactions that look exactly like a cadence of zero days. Key transactions on account, date, amount and the raw descriptor before any normalisation, and reconcile against the balance identity so that a duplicate import announces itself rather than becoming a subscription.

For each of these the right output is not silence but a lower-confidence candidate with the reason attached. A group of two charges 31 days apart is a candidate; presenting it as a confirmed subscription is what makes people stop trusting the report.