Reconciling Paystack Against an M-Pesa Statement

For Kenyan businesses on Paystack, settlement can be to a bank account or through mobile money. When settlement goes through M-Pesa, Paystack sends the net settlement amount (after Paystack fees) to your registered M-Pesa Paybill or till number.

The settlement appears as an incoming payment on your M-Pesa business account. The description or account reference will typically identify it as a Paystack settlement, though the exact format depends on how Paystack routes the payment.

Your reconciliation job confirms that every Paystack settlement has a corresponding M-Pesa receipt and that the amounts match.

For the general settlement reconciliation approach, see the settlement reconciliation guide. For bank account reconciliation, see the bank statement guide.

There are two ways to get your M-Pesa transaction data:

Safaricom Business Portal: Log into the M-Pesa business portal (previously M-Pesa for Business or Org Portal). Download the transaction statement for the period you want to reconcile. Export as CSV if available.

Daraja API: If you have access to the Safaricom Daraja API with the account balance and statement endpoints, you can fetch transaction data programmatically. This is the better approach for automation but requires API access setup with Safaricom.

// Parse M-Pesa statement CSV
function parseMpesaStatement(filePath) {
  var fs = require('fs');
  var content = fs.readFileSync(filePath, 'utf-8');
  var lines = content.split('
');

  var deposits = [];

  for (var i = 1; i < lines.length; i++) {
    var cols = lines[i].split(',');
    if (cols.length < 5) continue;

    // M-Pesa statement columns vary, adjust indices for your format
    var receiptNo = cols[0] ? cols[0].trim() : '';
    var date = cols[1] ? cols[1].trim() : '';
    var details = cols[2] ? cols[2].trim() : '';
    var paidIn = cols[3] ? cols[3].replace(/[^0-9.]/g, '') : '';

    var amount = parseFloat(paidIn);
    if (isNaN(amount) || amount <= 0) continue;

    deposits.push({
      receipt_number: receiptNo,
      date: new Date(date),
      description: details,
      amount: Math.round(amount * 100), // Convert to cents (KES smallest unit)
    });
  }

  return deposits;
}

The receipt number (e.g., "QHL1234567") is M-Pesa's unique transaction identifier. It is useful for tracing payments through Safaricom support if there are disputes.

Your M-Pesa statement contains all incoming payments: customer payments, Paystack settlements, personal transfers, and other business receipts. You need to filter for Paystack-specific entries.

// Filter M-Pesa entries that are likely Paystack settlements
function filterPaystackEntries(mpesaDeposits, paystackIdentifiers) {
  // paystackIdentifiers might include:
  // - The Paybill business number Paystack uses
  // - Known description patterns
  // - Specific account references

  return mpesaDeposits.filter(function(deposit) {
    var desc = deposit.description.toLowerCase();

    // Check for known Paystack identifiers in the description
    for (var i = 0; i < paystackIdentifiers.length; i++) {
      if (desc.indexOf(paystackIdentifiers[i].toLowerCase()) !== -1) {
        return true;
      }
    }

    return false;
  });
}

// Usage
var paystackDeposits = filterPaystackEntries(allDeposits, [
  'paystack',
  'PSTK',
  // Add other identifiers used by Paystack for your account
]);

If you cannot identify Paystack entries by description alone, match by amount. Large round-number deposits that correspond to your expected settlement amounts are likely from Paystack. This is less reliable but works as a starting point.

// Match M-Pesa deposits against Paystack settlements
async function reconcileMpesa(mpesaDeposits) {
  var settlements = await db.query(
    'SELECT id, total_amount, settled_date, paystack_settlement_id '
    + 'FROM settlements '
    + 'WHERE currency = $1 AND mpesa_matched = false '
    + 'AND settled_date >= NOW() - INTERVAL '30 days' '
    + 'ORDER BY settled_date',
    ['KES']
  );

  var results = {
    matched: [],
    unmatched_mpesa: [],
    unmatched_settlements: [],
  };

  var settlementList = settlements.rows.slice();

  for (var i = 0; i < mpesaDeposits.length; i++) {
    var deposit = mpesaDeposits[i];
    var matchFound = false;

    for (var j = 0; j < settlementList.length; j++) {
      var settlement = settlementList[j];

      // Amount match (M-Pesa amounts in cents, allow small tolerance)
      var amountDiff = Math.abs(deposit.amount - settlement.total_amount);
      if (amountDiff > 500) continue; // More than KES 5 tolerance

      // Date match (M-Pesa is faster than bank, 1-2 day tolerance)
      var daysDiff = Math.abs(deposit.date - new Date(settlement.settled_date));
      if (daysDiff > 3 * 24 * 60 * 60 * 1000) continue; // 3 days max

      results.matched.push({
        mpesa_receipt: deposit.receipt_number,
        mpesa_amount: deposit.amount,
        settlement_id: settlement.paystack_settlement_id,
        settlement_amount: settlement.total_amount,
        amount_diff: amountDiff,
      });

      // Mark as matched
      await db.query(
        'UPDATE settlements SET mpesa_matched = true, '
        + 'mpesa_receipt = $1, mpesa_match_date = $2 WHERE id = $3',
        [deposit.receipt_number, deposit.date, settlement.id]
      );

      settlementList.splice(j, 1);
      matchFound = true;
      break;
    }

    if (!matchFound) {
      results.unmatched_mpesa.push(deposit);
    }
  }

  results.unmatched_settlements = settlementList;
  return results;
}

M-Pesa transactions are generally faster than bank transfers. A Paystack settlement initiated in the morning often arrives on M-Pesa the same day or the next day. The date tolerance can be tighter than for bank reconciliation (1-2 days vs 3-5 days).

Safaricom transaction charges. M-Pesa may charge fees for receiving payments (Paybill fees) or for withdrawing funds. These fees reduce the available balance but may or may not affect the deposited amount. If they do, your matching tolerance should account for them.

Transaction limits. M-Pesa has per-transaction and daily limits. If a Paystack settlement exceeds these limits, it might be split into multiple M-Pesa transactions. Your matching algorithm should consider that one settlement might correspond to two M-Pesa deposits with amounts that sum to the settlement total.

Different statement periods. The Safaricom portal may generate statements based on calendar days in East Africa Time (EAT, UTC+3), while Paystack may record settlement dates in UTC. A settlement recorded as July 20 in Paystack might appear as July 21 on the M-Pesa statement if it was processed late in the evening UTC.

Receipt number format. M-Pesa receipt numbers follow a pattern (e.g., "QHL1234567"). If you can extract the receipt number from the Paystack settlement data (some integrations include it), you can match by receipt number for an exact match instead of fuzzy matching by amount and date.

M-Pesa reconciliation is the final step in a three-layer reconciliation process for Kenyan Paystack merchants:

1. Ledger reconciliation: Your ledger matches Paystack. Run daily.
2. Settlement reconciliation: Paystack settlements match your ledger. Run daily.
3. M-Pesa reconciliation: M-Pesa deposits match Paystack settlements. Run weekly or monthly.

// Complete three-layer reconciliation for Kenya
async function fullKenyanReconciliation(targetDate) {
  // Layer 1: Ledger vs Paystack API
  var ledgerResults = await reconcileLedger(targetDate, 'KES');

  // Layer 2: Settlement matching
  var settlementResults = await reconcileSettlements(targetDate, 'KES');

  // Layer 3: M-Pesa matching (requires statement data)
  // This layer runs when statement data is available (weekly/monthly)

  return {
    ledger: ledgerResults,
    settlements: settlementResults,
    mpesa: null, // Added when M-Pesa statement is processed
  };
}

If all three layers are clean, you have high confidence that every customer payment made it through Paystack, into a settlement, and into your M-Pesa account. If any layer has issues, you know exactly where the discrepancy is and can investigate accordingly.

For the broader month-end process that ties all of this together, see the month-end close guide.

If you have access to the Safaricom Daraja API, you can automate the statement download step. The Account Balance and Transaction Status APIs can provide the data you need without manually downloading CSV files.

// Example: Check M-Pesa account balance via Daraja
// (Requires Daraja API credentials and setup)
async function checkMpesaBalance() {
  // This is conceptual - actual Daraja implementation
  // requires OAuth token, specific endpoints, and certificate setup

  var token = await getDarajaToken();

  var response = await fetch(
    'https://api.safaricom.co.ke/mpesa/accountbalance/v1/query',
    {
      method: 'POST',
      headers: {
        Authorization: 'Bearer ' + token,
        'Content-Type': 'application/json',
      },
      body: JSON.stringify({
        // Daraja request payload
        // See Safaricom documentation for required fields
      }),
    }
  );

  return response.json();
}

// For full M-Pesa API integration details,
// see the M-Pesa integration guides in the mpesa hub

Full Daraja API setup and usage is covered in the M-Pesa Daraja API guides. For Paystack reconciliation, you primarily need the statement or transaction query endpoints.