Back to Projects

Payment Gateway Reconciliation Pipeline

An automated accounting pipeline that reconciles transactions from multiple payment gateways with Romanian ERP software, handling currency conversion, refunds, and chargebacks.

The Problem

A digital services company processing thousands of monthly transactions across multiple payment gateways needed to reconcile everything with their accounting system-NexusERP, a Romanian ERP that requires specific import formats. Each gateway has its own data structure, refunds and chargebacks need separate accounting documents, and every USD transaction requires conversion to EUR using official Romanian National Bank exchange rates from the transaction date. Doing this manually meant days of spreadsheet work each month and constant risk of errors.

The Solution

I built an end-to-end reconciliation pipeline that pulls transaction data from all payment gateways, enriches it with order details from the company's internal systems, converts currencies using official exchange rates, and generates the exact Excel formats that NexusERP expects for bulk import. The system handles the full lifecycle: creating partner records for new customers (with proper country-code formatting for international clients), generating invoices grouped by gateway and VAT status, processing refunds and chargebacks as separate credit documents, and posting payment entries to match bank deposits. What used to be a multi-day manual process now runs in minutes.

How It Works

  1. 1 Data Collection: Scripts pull charges, refunds, and disputes from each gateway's API. Transaction data is enriched with payout dates to match when funds actually hit the bank.
  2. 2 Enrichment: Transaction IDs are matched against the company's order system via SSH to a remote server, adding customer details, platform breakdown, and service types.
  3. 3 Currency Conversion: The system fetches exchange rates from the Romanian National Bank's XML feed, applying the rate from the day before each transaction (per Romanian accounting rules).
  4. 4 Document Generation: Transactions are grouped by gateway, VAT status, and document type, then exported as NexusERP-compatible Excel workbooks with proper document numbering sequences.
  5. 5 ERP Integration: Partner records and payment entries are posted directly to NexusERP's API, completing the reconciliation loop.

Tech Stack

  • Language: Python
  • Data Sources: Multiple payment gateway APIs
  • Exchange Rates: Romanian National Bank (BNR) XML API
  • Data Processing: OpenPyXL, BeautifulSoup, Paramiko (SSH)
  • Target System: NexusERP (Romanian accounting software)
  • Output Formats: Excel (XLS/XLSX), JSON

Results

  • Monthly reconciliation reduced from days to minutes
  • Multiple payment gateways unified into single accounting workflow
  • 4,000+ transactions processed per month
  • Automated currency conversion using official exchange rates
  • Zero manual data entry for invoice and refund imports
  • Full audit trail with JSON exports for each processing run