Automated reconciliation

What to define before automating

In our conversations with finance teams, one situation comes up repeatedly: they get the close done, but have little time left to understand what makes it difficult. In retail, daily volume, stores, e-commerce, and payment methods multiply the work. Peak season brings more sales and more items to review.

The task does not end when totals agree. You also need to know whether each deposit arrived on time, whether fees match the agreement, and what happened to differences from earlier days. Finding an item weeks later means searching files, reconstructing exceptions, and locating the person who knew the case.

Automation coordinates three timelines: when sources arrive, when each payment falls due, and how often you review items. The sale is the starting point. Keeping those timelines separate distinguishes a missing file from a deposit not yet due or one that should already have reached the account.

You can review a sale daily even if its payment term is 45 days. Scheduling needs to respect the provider's calendar, business days, and actual file availability. As new information arrives, the process updates the cutoff and shows what changed.

At Quantum, we approach the process in five steps: collect the information, standardize it, match the items, turn results into useful reports, and manage exceptions through resolution. Automation needs to cover this whole journey so finance can spend more time on decisions.

1. Collect information from every source

Downloading email attachments, opening a provider portal, finding the bank statement, and saving everything to a folder can take up a substantial part of the day. Before automating matching, address that preparation: where each report comes from, who provides it, when it is expected, and which period it covers.

The map includes POS and e-commerce systems, acquirers and card processors, banks, sales or collection platforms, and financing providers. It also covers cash, vouchers, and gift cards. Each operation uses a different mix; identify which sources apply to your business and which document supports each stage. If accounting verification is an objective, include the ERP as well.

For cash, follow the chain from the sale and cash close through handover or collection, deposit, and bank receipt: handing a bag to a cash carrier does not establish that funds reached the account. For financing, distinguish the provider's payment to the merchant from the buyer's installments. Gift card issuance and redemption are different events and must not be counted as two equivalent cash receipts.

• API. Query information or request provider reports. Before connecting, confirm the returned data, available history, update delivery, and required permissions.
• Host to host. A connection between company and bank systems. It can include movement or statement files when supported by the contracted service. Agree formats, frequency, and covered accounts.
• Scheduled files. Files can arrive through a secure channel or internal systems. If a source only offers portal downloads, evaluate how to sustain collection and detect failures before relying on it.

Examples of these options include Mercado Pago's report API and Bancolombia's account information with account-holder authorization. For bank file delivery, Santander Colombia describes its host-to-host service. These are specific product references: coverage depends on country, account, and the services enabled by each provider.

Collection should check that expected sources arrived and avoid loading the same file twice. If information is missing, the report needs to say so. A close based on incomplete data can support a preliminary review, provided it shows its coverage and what remains outstanding.

Automation also requires coverage checks: the file must match the expected cutoff, paginated queries must be complete, and control amounts and counts must agree. An approved-movement report, for example, does not replace all sales records: pending or rejected operations may need another source.

2. Standardize data into a shared structure

One provider supplies Excel, another CSV, and another PDF. A store has one code in the POS and another in settlement data. Standardization maps that information to a shared structure: entity, store, channel, payment method, currency, references, dates, and amounts. Sale, settlement, and deposit dates need to remain separate.

For this transformation to work without reviewing every file, define required fields, date formats, time zone, currency, and signs.

If a column changes or a PDF becomes unreadable, you need an alert and identified affected records. An unexpected structure should not silently become incorrect data.

Renaming a column does not prove that two identifiers refer to the same transaction. You need the relationship connecting them and the original file supporting it. If a contingency caused an incorrect store or payment method, the correction needs to retain the original value, reason, and person who intervened.

This also reduces dependence on knowledge held by one person. Mappings, known contingencies, and rules become documented. At Quantum, we aim to adapt the tool to each company's workflow on a shared foundation: adding a store, bank, or payment method should extend the model without losing history or rebuilding the whole process.

3. Define matching and validate fees

Matching quality depends on rules that represent your business. Relationships can be one-to-one (1:1), one-to-many (1:N), or many-to-many (N:M): one sale may have several payments, and one settlement may group sales from different dates. Before comparing amounts, define the group scope and which entities, merchants, stores, currencies, and accounts each rule can relate.

Before leaving the process running, test rules against historical periods that include refunds, contingencies, and incomplete data. Define priority, the evidence required for automatic closure, and ambiguities requiring review. Rule changes need versions and effective dates so past results remain explainable.

Then reconstruct the net: gross amount less fees, with refunds, withholdings, and adjustments using the appropriate signs.

Explaining the bank receipt is not enough to validate the charges. Compare the applied fee with the agreement applicable to that payment method or card network, its effective date, and calculation basis. An incorrect fee can be fully accounted for in a payout and still require a claim.

Tolerances need a defined limit and reason. Residual analysis can narrow candidates and reveal outstanding combinations, but an agreeing sum alone does not prove a relationship. If several options remain, keep the case open. The same item must not be used in two matches to make the close agree.

In Quantum implementations, we have exceeded 99% of transactions reconciled. That percentage refers to transaction count, not value. Results depend on the sources, rules, and scope of each operation; when evaluating them, always separate automatic resolution from items requiring intervention. Outstanding money still matters even when it represents few items.

4. Build reports and analytics

Reporting needs to distinguish automatically reconciled items, items reconciled with intervention, and items still open. If an incorrectly assigned store was corrected with evidence, that intervention should remain visible even though the money arrived. Records that could not be evaluated because of missing data also need to appear: excluding them can make the reconciliation rate look better than it is.

Excel can remain a useful output for the team. What is missing when each close sits in a folder is a searchable history connecting days, weeks, and months. That continuity reveals whether differences recur, how long they have stayed open, and which provider, store, region, business unit, or channel concentrates the problem.

• Reconciliation by count and value: report both rates over the same scope and distinguish human intervention.
• Outstanding balance and age: separate in-transit funds, overdue items, and missing data; prioritize by amount and expected payment date.
• Fees and withholdings: identify differences against applicable terms, effective dates, and the components explaining them.
• Compliance with payment terms and recurrence: track late payments, recurring differences, and active resolution time by provider, store, and channel.

With that detail, treasury can anticipate missing receipts, procurement or finance can review an agreement, and audit can reconstruct a case.

Compare periods with equivalent coverage and payment terms. A store with a lower reconciliation rate may have a data, configuration, training, or process issue. It is a signal to investigate and a possible input to loss prevention; by itself it proves neither fraud nor a loss.

5. Manage exceptions

An exception needs a case with an owner, reason, amount where calculable, expected date, evidence, and next action. A possible duplicate charge requires validating the transactions before making a claim. A recorded sale without its expected payment requires checking its status in the sources. An overdue deposit needs provider follow-up. Outstanding items do not all have the same resolution.

When automating, configure alerts as well: who receives a collection failure, who reviews overdue items, and what happens when a case stalls. A run may finish successfully while leaving exceptions open. Process status and item status need to remain separate.

When new information arrives, reassess the affected items. That lets a preliminary cutoff move forward without repeating the entire manual review. When rerunning, you need to compare results, know which files and rules changed, and preserve previous decisions without duplicating items.

This is where a financial AI agent can provide practical help. In Quantum, the team can ask the agent to rerun reconciliation, consult reports, or help review data, according to configured processes and permissions. When analyzing a difference, its answer should lead you to the supporting items and documents. Adjustments and case closure follow the company's approval rules.

Resolving a case should also improve the process: document the cause and, if it recurs, evaluate a rule that can detect it earlier. Knowledge remains available when the person handling reconciliation changes, and the team can focus on exceptions that actually require judgment.

Read more about exception handling and financial controls.

How to validate automation before expanding it

Choose a payment method, a set of stores, and a period covering its complete settlement cycle. Gather sales, payments, settlements, and deposits; include accounting records if they are part of the objective. Document rules and measure the baseline: source coverage, reconciled transaction count and value, outstanding balance, and the team's active time.

Start by running alongside the existing process and compare results item by item. Test missing sources, late files, and rerunning the same cutoff. Agree the results needed to expand: full coverage, explainable differences, assigned cases, and a measurable reduction in manual work. Then add stores, sources, and payment methods.

This daily reconciliation plan helps organize the initial scope. To review yours with Quantum, see our payment reconciliation solution.