Combine Multi-Currency Stripe Sheets in Excel
If you sell products or subscriptions internationally through Stripe, month-end reconciliation can quickly become a headache. Stripe allows your customers to pay in their local currencies, but payouts arrive in your merchant bank account in your domestic currency or across distinct settlement bank accounts.
When you pull individual payout reports from your Stripe Dashboard, you often end up with half a dozen CSV files containing distinct settlement currencies such as USD, EUR, GBP, and CAD. Trying to paste them into a single master spreadsheet leads to broken formulas, scrambled columns, and miscalculated exchange rates.
The Challenge with Multi-Currency Stripe Exports
Standard spreadsheet tutorials tell you to use Excel Consolidate or simple Power Query append steps. However, standard tutorials ignore the specific architectural quirks of financial settlement data explained in the official Stripe payout reconciliation documentation.
When consolidating multi-currency Stripe payout reports, finance teams hit four major stumbling blocks:
- Presentment vs. Settlement Currency: A customer might pay 100 EUR (presentment currency), but your Stripe account converts it to 108 USD (settlement currency). If column headers are not properly mapped, amounts get aggregated into the wrong currency bucket.
- Stripe Processing Fees: Stripe exports record gross amounts, processing fees, and net payouts in distinct fields. In some export profiles, fees are formatted as positive deductions, while in others they appear as negative values.
- Fluctuating Foreign Exchange Rates: Stripe applies an exchange rate at the exact moment of charge settlement. A fixed monthly average FX rate in Excel will inevitably produce rounding discrepancies against your bank statement.
- Inconsistent Header Schemas: If your business operates across multiple Stripe accounts or connected accounts, column names often diverge, causing standard Excel copy-paste operations to skew entire rows.
The Traditional Way: Power Query and VBA Workarounds
Historically, finance analysts have relied on Excel Power Query to pull multiple CSV files from a single folder. While Microsoft explains the mechanics in their guide to combining data from multiple sources in Power Query, the setup requires continuous maintenance whenever Stripe introduces minor changes to its export formats.
Below is an example of an Excel VBA macro that accountants frequently write to stitch together multiple payout CSV sheets into a single active sheet:
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
' Copy data excluding header on subsequent files
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
While this VBA script aggregates rows, it possesses zero financial intelligence. It does not verify whether column C in the EUR file represents the same metric as column C in the USD file, nor does it normalize decimal formats or eliminate duplicate refund records.
The Automated Way: Using DataSort AI to Merge Settlement Sheets
Instead of writing intricate Power Query transformations or brittle macros, modern accounting teams use AI to clean and combine multi-currency files. The Merge Excel Sheets Tool on DataSort handles column mismatches and currency alignment automatically.
DataSort utilizes Gemini AI to understand the semantic meaning of your headers. When one sheet lists gross_amount and another lists Gross (EUR), the engine identifies that both belong in the unified gross transaction column while preserving currency tags.
Step-by-Step Consolidation Guide
Follow these steps to generate a clean, consolidated reconciliation table in minutes:
- Export Payout CSVs: From your Stripe Dashboard, navigate to Balances, select Payouts, and export your settlement history for each respective bank currency.
- Upload to DataSort: Open the Merge Data Tool and upload your batch of CSV or Excel files simultaneously.
- Define Consolidation Rules: Instruct the AI engine to match columns by financial intent (for example: "Align presentment amounts, isolate Stripe fees into a positive cost column, and retain the original transaction ID").
- Review Currency Columns: The system automatically constructs clean columns for Presentment Amount, Settlement Amount, Fee Amount, and Effective Exchange Rate.
- Clean and Deduplicate: Run the consolidated dataset through the Remove Duplicates Tool to ensure that disputed charges or dual-entry payout transfers are not counted twice.
Reconciling FX Rates and Fees in Your Master Workbook
Once your files are merged, you will want to add calculated fields for audit compliance. The most critical calculation for multi-currency operations is isolating your realized FX spread from Stripe's base fee.
In your newly merged master table, structure your calculation columns using standard Excel formulas:
- Effective FX Rate:
=[@[Settlement Amount]] / [@[Presentment Amount]](valid when presentment differs from settlement currency). - Net Received Ratio:
=([@[Settlement Amount]] - [@[Fee Amount]]) / [@[Settlement Amount]]to verify your true cost of acceptance across global regions. - Discrepancy Check: Compare the sum of your net settlement column against the total bank deposit row from your monthly bank statement.
If you receive messy raw exports with corrupted dates, text numbers, or extra spaces, running them through the CSV Cleaner before merging will eliminate formula errors like #VALUE! entirely.
Streamlining Your Monthly Financial Close
Finance professionals spend dozens of hours every quarter manually cleaning raw exports from payment processors. Moving away from rigid VBA scripts and manual copy-pasting reduces human error and accelerates your closing timeline.
By incorporating intelligent data tools into your workflow, you can convert dozens of chaotic CSV files into an audit-ready master Excel table in seconds. Explore flexible subscription options on the DataSort pricing page, or start cleaning your payout sheets today directly on DataSort.
Stop Reading. Start Cleaning.
Experience the power of AI data cleaning with DataSort. 100% free with all major features and no credit card required.
Start Cleaning Free Now