Skip to main content

Account Reconciliation

Account reconciliation ensures your Omise transaction records match your bank statements and accounting systems. This guide covers reconciliation processes, automated matching, and best practices.

Overviewโ€‹

Reconciliation is essential for:

  • Accurate Accounting: Ensure financial records are correct
  • Fraud Detection: Identify unauthorized transactions
  • Compliance: Meet regulatory requirements
  • Cash Flow Management: Track actual vs expected funds
  • Error Identification: Catch processing issues early
  • Audit Trail: Maintain complete financial records

Key Conceptsโ€‹

  • Settlement: Batch of transactions paid to your bank account
  • Settlement Date: When funds arrive in your bank
  • Transaction Date: When customer payment occurred
  • Net Amount: Settlement amount after fees
  • Gross Amount: Total payment amount before fees

Understanding Settlementsโ€‹

Settlement Cycleโ€‹

const omise = require('omise')({
  secretKey: 'skey_test_123456789',
});

// Get recent transfers (settlements)
async function getRecentSettlements(days = 30) {
  const endDate = new Date();
  const startDate = new Date();
  startDate.setDate(startDate.getDate() - days);
  
  const transfers = await omise.transfers.list({
    from: startDate.toISOString(),
    to: endDate.toISOString(),
    limit: 100
  });
  
  console.log(`Found ${transfers.data.length} settlements`);
  
  transfers.data.forEach(transfer => {
    console.log(`Transfer ${transfer.id}:`);
    console.log(`  Amount: ${transfer.amount / 100} ${transfer.currency.toUpperCase()}`);
    console.log(`  Fee: ${transfer.fee / 100} ${transfer.currency.toUpperCase()}`);
    console.log(`  Date: ${new Date(transfer.created).toLocaleDateString()}`);
  });
  
  return transfers.data;
}

// Calculate settlement total
function calculateSettlementTotal(settlement) {
  return {
    gross_amount: settlement.amount,
    fee: settlement.fee,
    net_amount: settlement.amount - settlement.fee,
    currency: settlement.currency,
    formatted: {
      gross: `${settlement.amount / 100} ${settlement.currency.toUpperCase()}`,
      fee: `${settlement.fee / 100} ${settlement.currency.toUpperCase()}`,
      net: `${(settlement.amount - settlement.fee) / 100} ${settlement.currency.toUpperCase()}`
    }
  };
}

// Example usage
getRecentSettlements(30);

Reconciliation Processโ€‹

Basic Reconciliationโ€‹

class ReconciliationManager {
  constructor() {
    this.discrepancies = [];
  }
  
  async reconcileWithBankStatement(bankStatement, startDate, endDate) {
    // Get Omise settlements
    const settlements = await this.getOmiseSettlements(startDate, endDate);
    
    // Get Omise transactions
    const transactions = await this.getOmiseTransactions(startDate, endDate);
    
    // Match settlements with bank entries
    const matchResults = this.matchSettlements(settlements, bankStatement);
    
    // Generate reconciliation report
    const report = this.generateReconciliationReport(
      settlements,
      transactions,
      bankStatement,
      matchResults
    );
    
    return report;
  }
  
  async getOmiseSettlements(startDate, endDate) {
    const transfers = await omise.transfers.list({
      from: startDate.toISOString(),
      to: endDate.toISOString(),
      limit: 100
    });
    
    return transfers.data.map(t => ({
      id: t.id,
      date: new Date(t.created),
      amount: t.amount - t.fee, // Net amount
      gross: t.amount,
      fee: t.fee,
      currency: t.currency,
      status: t.paid ? 'paid' : 'pending'
    }));
  }
  
  async getOmiseTransactions(startDate, endDate) {
    const transactions = await omise.transactions.list({
      from: startDate.toISOString(),
      to: endDate.toISOString(),
      limit: 1000
    });
    
    return transactions.data;
  }
  
  matchSettlements(settlements, bankStatement) {
    const matched = [];
    const unmatchedOmise = [];
    const unmatchedBank = [];
    
    const bankEntries = [...bankStatement];
    
    settlements.forEach(settlement => {
      const match = this.findMatchingBankEntry(settlement, bankEntries);
      
      if (match) {
        matched.push({
          settlement: settlement,
          bankEntry: match.entry,
          confidence: match.confidence
        });
        // Remove matched entry
        bankEntries.splice(match.index, 1);
      } else {
        unmatchedOmise.push(settlement);
      }
    });
    
    unmatchedBank.push(...bankEntries);
    
    return {
      matched,
      unmatchedOmise,
      unmatchedBank,
      matchRate: (matched.length / settlements.length * 100).toFixed(2)
    };
  }
  
  findMatchingBankEntry(settlement, bankEntries) {
    for (let i = 0; i < bankEntries.length; i++) {
      const entry = bankEntries[i];
      
      // Match by amount (allowing small difference for fees)
      const amountDiff = Math.abs(settlement.amount - entry.amount);
      
      // Match by date (allowing a few days difference)
      const daysDiff = Math.abs(
        (settlement.date - new Date(entry.date)) / (1000 * 60 * 60 * 24)
      );
      
      if (amountDiff < 100 && daysDiff <= 3) {
        const confidence = this.calculateMatchConfidence(
          amountDiff,
          daysDiff
        );
        
        return {
          entry: entry,
          index: i,
          confidence: confidence
        };
      }
    }
    
    return null;
  }
  
  calculateMatchConfidence(amountDiff, daysDiff) {
    let confidence = 100;
    
    // Reduce confidence based on amount difference
    confidence -= (amountDiff / 100) * 5;
    
    // Reduce confidence based on date difference
    confidence -= daysDiff * 10;
    
    return Math.max(0, confidence);
  }
  
  generateReconciliationReport(settlements, transactions, bankStatement, matchResults) {
    return {
      period: {
        start: settlements[0]?.date,
        end: settlements[settlements.length - 1]?.date
      },
      summary: {
        total_settlements: settlements.length,
        total_bank_entries: bankStatement.length,
        matched: matchResults.matched.length,
        unmatched_omise: matchResults.unmatchedOmise.length,
        unmatched_bank: matchResults.unmatchedBank.length,
        match_rate: matchResults.matchRate + '%'
      },
      totals: {
        omise_total: settlements.reduce((sum, s) => sum + s.amount, 0),
        bank_total: bankStatement.reduce((sum, e) => sum + e.amount, 0),
        difference: 0
      },
      matched: matchResults.matched,
      discrepancies: {
        unmatched_omise: matchResults.unmatchedOmise,
        unmatched_bank: matchResults.unmatchedBank
      }
    };
  }
  
  printReconciliationReport(report) {
    console.log('\n' + '='.repeat(60));
    console.log('RECONCILIATION REPORT');
    console.log('='.repeat(60));
    console.log(`Period: ${report.period.start.toLocaleDateString()} - ${report.period.end.toLocaleDateString()}`);
    console.log('\nSummary:');
    console.log(`  Omise Settlements:  ${report.summary.total_settlements}`);
    console.log(`  Bank Entries:       ${report.summary.total_bank_entries}`);
    console.log(`  Matched:            ${report.summary.matched}`);
    console.log(`  Match Rate:         ${report.summary.match_rate}`);
    console.log('\nDiscrepancies:');
    console.log(`  Unmatched Omise:    ${report.summary.unmatched_omise}`);
    console.log(`  Unmatched Bank:     ${report.summary.unmatched_bank}`);
    console.log('='.repeat(60));
  }
}

// Example usage
const reconciler = new ReconciliationManager();

const bankStatement = [
  { date: '2024-01-15', amount: 97500, reference: 'OMISE-001' },
  { date: '2024-01-22', amount: 195000, reference: 'OMISE-002' }
];

reconciler.reconcileWithBankStatement(
  bankStatement,
  new Date('2024-01-01'),
  new Date('2024-01-31')
).then(report => {
  reconciler.printReconciliationReport(report);
});

Automated Reconciliationโ€‹

Daily Reconciliation Scriptโ€‹

<?php
require_once 'vendor/autoload.php';

class DailyReconciliation {
    private $omiseKey;
    private $emailRecipients;
    
    public function __construct($omiseKey, $emailRecipients) {
        $this->omiseKey = $omiseKey;
        $this->emailRecipients = $emailRecipients;
    }
    
    public function runDailyReconciliation() {
        // Get yesterday's data
        $yesterday = date('Y-m-d', strtotime('-1 day'));
        
        // Fetch bank statement (from your banking system)
        $bankStatement = $this->fetchBankStatement($yesterday);
        
        // Fetch Omise settlements
        $omiseSettlements = $this->fetchOmiseSettlements($yesterday);
        
        // Reconcile
        $report = $this->reconcile($omiseSettlements, $bankStatement);
        
        // Check for discrepancies
        if ($report['discrepancy_count'] > 0) {
            $this->sendAlertEmail($report);
        }
        
        // Save report
        $this->saveReport($report);
        
        return $report;
    }
    
    private function fetchOmiseSettlements($date) {
        $startDate = date('c', strtotime($date . ' 00:00:00'));
        $endDate = date('c', strtotime($date . ' 23:59:59'));
        
        $transfers = OmiseTransfer::retrieve([
            'from' => $startDate,
            'to' => $endDate,
            'limit' => 100
        ]);
        
        return array_map(function($t) {
            return [
                'id' => $t['id'],
                'date' => date('Y-m-d', $t['created']),
                'amount' => $t['amount'] - $t['fee'],
                'gross' => $t['amount'],
                'fee' => $t['fee']
            ];
        }, $transfers['data']);
    }
    
    private function fetchBankStatement($date) {
        // Implement your bank API integration here
        // This is a placeholder
        return [];
    }
    
    private function reconcile($omiseSettlements, $bankStatement) {
        $matched = 0;
        $unmatched = [];
        
        foreach ($omiseSettlements as $settlement) {
            $found = false;
            foreach ($bankStatement as $entry) {
                if ($this->isMatch($settlement, $entry)) {
                    $matched++;
                    $found = true;
                    break;
                }
            }
            
            if (!$found) {
                $unmatched[] = $settlement;
            }
        }
        
        return [
            'date' => date('Y-m-d'),
            'total_settlements' => count($omiseSettlements),
            'matched' => $matched,
            'discrepancy_count' => count($unmatched),
            'unmatched' => $unmatched
        ];
    }
    
    private function isMatch($settlement, $bankEntry) {
        $amountMatch = abs($settlement['amount'] - $bankEntry['amount']) < 100;
        $dateMatch = $settlement['date'] === $bankEntry['date'];
        
        return $amountMatch && $dateMatch;
    }
    
    private function sendAlertEmail($report) {
        $subject = "โš ๏ธ Reconciliation Discrepancies - " . $report['date'];
        $message = "Found {$report['discrepancy_count']} unmatched settlements.\n\n";
        
        foreach ($report['unmatched'] as $settlement) {
            $message .= "Settlement {$settlement['id']}: " . 
                        number_format($settlement['amount'] / 100, 2) . " THB\n";
        }
        
        foreach ($this->emailRecipients as $recipient) {
            mail($recipient, $subject, $message);
        }
    }
    
    private function saveReport($report) {
        $filename = 'reconciliation_' . $report['date'] . '.json';
        file_put_contents($filename, json_encode($report, JSON_PRETTY_PRINT));
    }
}

// Run daily reconciliation
$reconciliation = new DailyReconciliation(
    'skey_test_123456789',
    ['finance@company.com', 'accounting@company.com']
);

$report = $reconciliation->runDailyReconciliation();
echo "Reconciliation complete. Matched: {$report['matched']}/{$report['total_settlements']}\n";
?>

Best Practicesโ€‹

1. Automate Daily Reconciliationโ€‹

// Schedule daily reconciliation
const cron = require('node-cron');

class AutomatedReconciliation {
  constructor() {
    this.reconciler = new ReconciliationManager();
  }
  
  scheduleDailyReconciliation() {
    // Run every day at 9 AM
    cron.schedule('0 9 * * *', async () => {
      console.log('Starting daily reconciliation...');
      
      const yesterday = new Date();
      yesterday.setDate(yesterday.getDate() - 1);
      
      try {
        const report = await this.runReconciliation(yesterday);
        
        if (report.summary.unmatched_omise > 0 || report.summary.unmatched_bank > 0) {
          await this.sendAlertNotification(report);
        }
        
        await this.saveReport(report);
        
        console.log('Daily reconciliation complete');
      } catch (error) {
        console.error('Reconciliation failed:', error);
        await this.sendErrorNotification(error);
      }
    });
  }
  
  async runReconciliation(date) {
    const bankStatement = await this.fetchBankStatement(date);
    
    return await this.reconciler.reconcileWithBankStatement(
      bankStatement,
      date,
      date
    );
  }
  
  async sendAlertNotification(report) {
    // Send email or Slack notification
    console.log('โš ๏ธ  Discrepancies found in reconciliation');
  }
}

const automated = new AutomatedReconciliation();
automated.scheduleDailyReconciliation();

2. Track Fee Differencesโ€‹

def analyze_fee_discrepancies(omise_settlements, bank_statement):
    """Analyze fee-related discrepancies"""
    
    discrepancies = []
    
    for settlement in omise_settlements:
        # Calculate expected bank amount (settlement amount - Omise fee)
        expected_bank_amount = settlement['gross'] - settlement['fee']
        
        # Find corresponding bank entry
        bank_entry = find_bank_entry_by_date(settlement['date'], bank_statement)
        
        if bank_entry:
            actual_bank_amount = bank_entry['amount']
            difference = expected_bank_amount - actual_bank_amount
            
            if abs(difference) > 10:  # More than 0.10 THB difference
                discrepancies.append({
                    'settlement_id': settlement['id'],
                    'expected': expected_bank_amount / 100,
                    'actual': actual_bank_amount / 100,
                    'difference': difference / 100,
                    'possible_cause': analyze_difference(difference)
                })
    
    return discrepancies

def analyze_difference(difference):
    """Determine possible cause of difference"""
    if abs(difference) < 500:  # Less than 5 THB
        return 'Rounding difference'
    elif difference > 0:
        return 'Missing bank fees or adjustment'
    else:
        return 'Additional bank fees charged'

3. Maintain Audit Trailโ€‹

class ReconciliationAuditLog
  def self.log_reconciliation(report, discrepancies = [])
    log_entry = {
      timestamp: Time.now.iso8601,
      report_date: report[:period][:start].to_s,
      match_rate: report[:summary][:match_rate],
      total_settlements: report[:summary][:total_settlements],
      matched: report[:summary][:matched],
      unmatched: report[:summary][:unmatched_omise] + report[:summary][:unmatched_bank],
      discrepancies: discrepancies.map { |d| d[:settlement_id] }
    }
    
    # Save to audit log file
    File.open('reconciliation_audit.log', 'a') do |f|
      f.puts JSON.generate(log_entry)
    end
    
    # Also save to database for reporting
    save_to_database(log_entry)
  end
  
  def self.save_to_database(entry)
    # Implement database storage
  end
end

FAQโ€‹

How often should I reconcile my account?โ€‹

Daily reconciliation is recommended for businesses with high transaction volumes. Smaller businesses may reconcile weekly or monthly. Automated daily reconciliation helps catch issues early.

What causes reconciliation discrepancies?โ€‹

Common causes include:

  • Timing differences (transaction dates vs settlement dates)
  • Fee mismatches
  • Bank processing delays
  • Failed transactions
  • Manual adjustments
  • Currency conversion differences

Should I match gross or net amounts?โ€‹

Match net amounts (after fees) since that's what appears in your bank account. However, track gross amounts separately to verify fee calculations are correct.

How do I handle multi-day settlement delays?โ€‹

Allow a 2-3 day window when matching transactions to bank entries. Settlements typically take 1-2 business days but can vary by bank and payment method.

What if I can't match a settlement?โ€‹

Investigate by:

  1. Checking if it's still pending
  2. Verifying the date range (may have settled in different period)
  3. Checking for failed or reversed transactions
  4. Contacting Omise support for assistance

Can I automate bank statement imports?โ€‹

Yes, most banks offer APIs or file exports (CSV, OFX) that you can automate. Integrate these with your reconciliation system for fully automated reconciliation.

How long should I keep reconciliation records?โ€‹

Keep reconciliation records for at least 7 years for tax and audit purposes. Store them securely with proper access controls.

What's the best way to handle discrepancies?โ€‹

Document all discrepancies immediately, investigate within 24 hours, and maintain a log of resolutions. Set up alerts for discrepancies above certain thresholds.

Next Stepsโ€‹