DEV Community

M Maaz Ul Haq for DataSort

Posted on Originally published at datasort.app

Consolidating Multi-Currency Stripe Settlement Reports: Beyond Brittle Excel Macros

If your application or SaaS charges customers internationally via Stripe, month-end settlement reconciliation is notoriously error-prone. Customers often pay in their local presentment currencies (e.g., EUR, GBP, JPY), while Stripe settles into designated merchant currency balances.

Downloading individual payout reports yields disjointed CSV files with differing currencies, column layouts, and fee signs. Pasting these into Excel manually or running basic append scripts often leads to silent data corruption.

Common Multi-Currency Stripe Edge Cases

  1. Presentment vs. Settlement Currency: If a user pays 100 EUR, but the account settles in USD ($108), treating the transaction as a single generic amount corrupts your aggregate revenue figures.
  2. Inconsistent Fee Formats: Depending on API versioning and report formats, processing fees can appear either as positive reductions or negative line items.
  3. Point-in-Time FX Rates: Stripe calculates FX rates dynamically at transaction/payout conversion. Using a flat monthly rate in spreadsheets creates persistent rounding discrepancies.
  4. Schema Drift: Exports pulled from different regional accounts or connected accounts often vary in header casing or terminology (e.g., gross vs Gross (EUR)).

The Pitfalls of Basic VBA Merging

A standard VBA folder-loop looks like this:

Sub CombineStripeCSVs()
    Dim FolderPath As String, Filename As String
    Dim MasterSheet As Worksheet, TempWb As Workbook
    Dim NextRow As Long, LastRow As Long

    FolderPath = "C:\StripePayouts\"
    Set MasterSheet = ThisWorkbook.Sheets(1)
    Filename = Dir(FolderPath & "*.csv")

    Do While Filename <> ""
        Set TempWb = Workbooks.Open(FolderPath & Filename)
        LastRow = TempWb.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
        NextRow = MasterSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

        If NextRow = 2 Then
            TempWb.Sheets(1).Range("A1").CurrentRegion.Copy MasterSheet.Range("A1")
        Else
            TempWb.Sheets(1).Range("A2:Z" & LastRow).Copy MasterSheet.Range("A" & NextRow)
        End If

        TempWb.Close SaveChanges:=False
        Filename = Dir()
    Loop
End Sub
Enter fullscreen mode Exit fullscreen mode

This script assumes exact column alignment. If one file has an extra metadata column or swapped headers, row values get placed in the wrong columns with no error thrown.

Robust Processing Pipeline with Pandas

Building an automated Python pipeline provides schema alignment, deduplication, and currency normalization before producing the final Excel sheet.

import os
import glob
import pandas as pd

COLUMN_MAPPING = {
    'Gross': 'gross_amount',
    'gross_amount': 'gross_amount',
    'Gross (EUR)': 'gross_amount',
    'Fee': 'fee_amount',
    'fee': 'fee_amount',
    'Net': 'net_amount',
    'net': 'net_amount',
    'Currency': 'settlement_currency',
    'currency': 'settlement_currency',
    'Presentment Currency': 'presentment_currency',
    'Presentment Amount': 'presentment_amount'
}

def process_stripe_csv(filepath: str) -> pd.DataFrame:
    df = pd.read_csv(filepath)
    df = df.rename(columns={col: COLUMN_MAPPING[col] for col in df.columns if col in COLUMN_MAPPING})

    # Normalize fee values to positive absolute numbers
    if 'fee_amount' in df.columns:
        df['fee_amount'] = df['fee_amount'].abs()

    return df

def build_master_sheet(input_dir: str, output_file: str):
    csv_files = glob.glob(os.path.join(input_dir, "*.csv"))
    frames = [process_stripe_csv(f) for f in csv_files]

    master = pd.concat(frames, ignore_index=True)

    # Derive the exact effective exchange rate
    if 'presentment_amount' in master.columns and 'gross_amount' in master.columns:
        master['effective_fx_rate'] = master.apply(
            lambda r: r['gross_amount'] / r['presentment_amount']
            if r.get('presentment_amount', 0) > 0 else 1.0,
            axis=1
        )

    # Remove potential duplicate records across export batches
    if 'id' in master.columns:
        master = master.drop_duplicates(subset=['id'])

    master.to_excel(output_file, index=False)

if __name__ == "__main__":
    build_master_sheet("./exports", "./consolidated_settlement.xlsx")
Enter fullscreen mode Exit fullscreen mode

Audit-Ready Calculations in Excel

Once standardized data is loaded into Excel, add verification columns to cross-reference settlement data against your bank statements:

  • Effective FX Spread: =[@[Settlement Amount]] / [@[Presentment Amount]]
  • Net Realized Margin: =([@[Settlement Amount]] - [@[Fee Amount]]) / [@[Settlement Amount]]
  • Deposit Reconciliation: =SUMIFS(Table[net_amount], Table[payout_id], PayoutSummary[@id])

Using a defined schema mapper avoids #VALUE! errors and guarantees repeatable, auditable financial exports every billing cycle.

Top comments (0)