Reconciliation that runs on a spreadsheet at month end

The situation

Money arrives through several providers, each settling on its own schedule with its own file format. Internal records say what was expected. Matching them is a monthly exercise in exports and lookups, unmatched items are chased by email, and a duplicate settlement file processed twice has, at least once, produced a duplicate entry.

These are illustrative scenarios showing how the products are used, not customer case studies.

What gets modeled

The structure as it would actually be defined — sections that repeat are marked.

How it gets built

In the order somebody would actually do it. Each step names the capability it rests on, so it can be checked against the product pages.

1. 1

   Model a settlement as its entries

   A settlement is a header and a variable number of entries, so entries repeat and each carries its own status and matched reference. Reference is unique per provider, which is the field the duplicate rule will use.

   What it uses: Repeating sections, with per-field rules including unique

2. 2

   Take the files in without a bespoke parser each time

   Every published version can be imported as CSV or JSON, and a sample file shows the expected shape. The model’s own rules — required, unique, typed, validated — apply on the way in rather than being restated in a parser per provider.

   What it uses: Import per published version, with per-field rules enforced on every path in

3. 3

   Refuse the file you already processed

   Duplicate rules on the schema catch an entry that has already been recorded, and a repeated trigger for the same settlement returns the run already in progress rather than starting a second one. Processing a file twice stops being an incident.

   What it uses: Duplicate detection, and one run per subject with repeat-event recognition

4. 4

   Match against what the system says now

   A step reads the internal record for each entry at the point of use, so a match is made against the current position rather than a snapshot taken when the file landed. The result is written back onto the entry.

   What it uses: A step that reads and writes the record mid-run

5. 5

   Route only the exceptions to a person

   Conditional links separate matched, partially matched and unmatched, so a person only opens the exceptions. Anything the conditions do not cover stops and waits for an operator; nothing is filed on a guess.

   What it uses: Conditional links with an explicit default, and a run that stops rather than guesses

6. 6

   Pull the provider files on a schedule

   Fetching each provider’s settlement is a scheduled trigger, on a cron expression or a fixed interval. A feed that stops arriving raises a notification on the day it stops. Month end is a late time to learn that one provider went quiet three weeks ago.

   What it uses: Schedule triggers with alerting on failure and stalled runs

7. 7

   Stay inside each provider’s limits

   Each provider is registered by importing its API description, with its own authentication, retry policy and throttling. Being held back by a rate limit is not a failure: the step waits, and the wait does not count against its retries. A provider that starts degrading can be slowed down or stopped there and then, without a release.

   What it uses: Any API becomes a step, with per-connection throttling and live overrides

8. 8

   Close the month continuously

   Because matching runs when the file arrives, the month-end position is the current position. Unmatched items are a filtered view with an age against each one rather than a spreadsheet somebody rebuilds.

   What it uses: Tables with server-side paging and typed filters

Numbers used above, and where they come from

Every figure on this site is counted from the product itself. Nothing here is a performance or customer claim, because there is no measurement to cite for one.