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
- Presentment vs. Settlement Currency: If a user pays 100 EUR, but the account settles in USD ($108), treating the transaction as a single generic
amountcorrupts your aggregate revenue figures. - Inconsistent Fee Formats: Depending on API versioning and report formats, processing fees can appear either as positive reductions or negative line items.
- 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.
- Schema Drift: Exports pulled from different regional accounts or connected accounts often vary in header casing or terminology (e.g.,
grossvsGross (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 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") 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.