E-Commerce · Operations · Global
Replacing a broken manual close with an automated reconciliation engine
Global eCommerce Conglomerate
Payment Reconciliation Automation & Month-End Close Infrastructure
Industry: eCommerce — Payment Processing, Fulfillment, Customer Support Structure: Multi-entity, privately held, 30+ countries Engagement: Late 2025 – March 2026
The Problem
A global eCommerce conglomerate routes all payment processor transactions through a proprietary internal platform, which feeds Microsoft Dynamics NAV as the ERP. Every month, the finance team had to reconcile every payment processor against the internal platform — confirming that all transactions were captured, merchant deposit amounts matched NAV, and merchant account balances were accurate.
The process was entirely manual, and a current-state discovery session documented exactly how broken it was.
The monthly consolidation took 3–4 hours and required manually copy-pasting a Mapped Trial Balance from Dynamics into a custom Excel workbook. By month-end, the finance lead was re-importing data from Dynamics up to five times in a three-hour window — because late entries from teammates invalidated her work mid-process. The consolidation couldn't start until the prior reconciliation step was complete, and that reconciliation was consistently delayed by inconsistent data exports from the development team. A single person handled all bank postings for all entities, which pushed the close back 1.5–3 days per entity, every month.
Dynamics was being severely underutilized — bank reconciliations and balance sheet work were done in external Excel files, bypassing the ERP entirely. P&L analysis required manual line-by-line cost classification in a separate workbook because transaction descriptions in Dynamics were too vague to classify automatically.
The target: a 5-day close executed concurrently across sub-entities, with exception-based review replacing the brute-force manual process.
The Work
I led the engagement — scoping, process documentation, close infrastructure, and project coordination with the engineering team. Two engineers built the technical components of the reconciliation engine.
Automated 3-Way Merchant Reconciliation Engine
A production-grade Electron desktop application with a Python/Pandas backend — built by the engineering team to my specification — that replaced the entire manual merchant matching process.
The engine compares each payment processor's transaction data against the internal platform records at the gross level, classifies transactions into the correct accounting categories, then validates that platform totals agree to bank deposits. Designed to shift reconciliation from a monthly close activity to a daily process, with discrepancies surfacing in real time via an exception dashboard rather than being discovered at month-end.
Payment processors covered: PayPal (including chargeback categorization logic, confirmed against manual results), Stripe (transaction-level order ID matching), Braintree, and NMI — which also served as the integration model for the Phase 2 expansion.
The finance team confirmed the tool's output matched their manual reconciliation results. Code delivered at v37 via Teams, fully owned by the client with no proprietary dependencies.
Month-End Close Workbooks and Dynamics Connection
A direct Dynamics NAV connection was built into the close workbooks — GL and trial balance data pulled live from Dynamics instead of manually exported and pasted. The five-times-reimport loop was eliminated at the source. Standardized month-end close workbooks replaced the ad hoc Excel consolidation process. Power BI was connected to Dynamics NAV with a twice-daily refresh schedule, giving the finance team a live view of the GL without manual exports.
Current-State Documentation and Future-State Recommendations
Before any build work, I conducted a full walkthrough of the existing reconciliation and close workflow — every step, input source, bottleneck, and compliance risk. Key findings delivered to the client: the single-person bank posting bottleneck as the structural cause of close delays; Dynamics underutilization, with native bank rec and reconciliation features unused; future-dated cost risk in P&L workbooks; and sequencing problems as the root cause of month-end scramble — a process design issue, not a capacity issue.
The proposed future state: exception-based review, Dynamics as the single source of truth for real-time collaboration across the accounting team, and flagging logic for prepaid and future-dated cost items.
Phase 2 Scoped
Expansion of the reconciliation engine to a second business unit — six additional payment processors: CBD, Coinflow, First Data, Fiserv Me, Fiserv VIP, and Talus. Scoped and documented for execution.
Outcomes
A 3–4 hour manual reconciliation replaced with an automated engine. Four payment processors covered, 3-way match logic validated against the existing manual process, exception dashboard replacing month-end scramble with real-time visibility.
The copy-paste loop eliminated. Direct Dynamics connection removed the manual GL export step and the 5x-reimport problem. The finance team works from live data instead of stale exports.
Process root causes documented. The discovery and future-state deliverable gave the client team a clear diagnosis — what was broken, why, and in what order to fix it.
Phase 2 scoped and ready. Second business unit expansion documented for execution whenever the client chooses to proceed.
Why It Mattered
This client's architecture is the natural end state of a growing eCommerce platform: multiple payment processors, a proprietary internal transaction layer, a central ERP, and a finance team trying to reconcile all of it manually at month-end. The reconciliation problem doesn't get easier as the platform adds merchants or business units — it compounds.
The engine built here — 3-way match, exception dashboard, daily refresh cadence — is directly replicable for any eCommerce business running multiple payment processors against a central ERP.
Engagement Lead: Michael Cohen Engineering: Engineer 1 (API pipelines, processor integrations), Engineer 2 (Python backend, Electron/React application) Stack: Python · Pandas · Electron · React · TypeScript · Microsoft Dynamics NAV · Power BI · Excel · Google Drive
Working on something similar?
Every engagement starts with a conversation about where your finance or data function is breaking down. No intake forms, no pitch.