Reconcile Stripe Disputes and Refunds Against Original Charges
Annual Stripe exports for mid-volume merchants routinely exceed 500,000 rows, mixing charges, refunds, and dispute creations in one monolithic CSV. Cryptic values like 'dispute_warning_under_review' (a pre-dispute inquiry that may or may not escalate) don't link back to the original charge amount in the same row. You must trace each event back via Source ID as a foreign key. Visa's Dispute Monitoring Program (VDMP) threshold is 0.9% dispute-to-transaction ratio; Mastercard's Excessive Chargeback Program (ECP) uses 1.5%. This workflow filters for adjustment events, joins them to original charge rows using Source ID, and computes net realized revenue—all via a blazing-fast local Wasm hash join.
Why This Matters
A Shopify merchant hit a 1.8% dispute rate in Q4, but finance only discovered it in February because the 480K-row export crashed Excel AutoFilter. The 3-month detection delay meant they breached Visa's VDMP threshold (0.9%), incurring $15,000 in penalties and mandatory dispute mitigation planning. Outsourcing the reconciliation required uploading full customer PII to an unvetted cloud bucket. Don't risk data leaks or $25,000 Visa fines—process half a million rows locally in 3 seconds.
Why Excel & Python Fail Here
Excel AutoFilter freezes at 480K+ rows. The 'Source ID' column contains values like 'ch_3MtwBwLkdIwHu7ix28a3tqPa' that must be matched against the charge row's 'ID' column—but Excel VLOOKUP on 500K rows takes 45+ minutes and produces #N/A on any whitespace mismatch. Python pandas works but requires uploading the file to a local environment (fine) or cloud notebook (PII risk). The VDMP reporting window means you need this data within days, not weeks.
| Criterion | Excel / Sheets | Python / Cloud | DataPrep (This Page) |
|---|---|---|---|
| Max Rows | ~150K (crashes) | RAM-limited | 1M+ via Wasm |
| Setup Time | Manual formulas | 30–60 min env setup | 0 seconds |
| Data Privacy | Local but fragile | Uploads to cloud | 100% in-browser |
| Cost | License fees | EC2 / SaaS $49+/mo | Free forever |
Step-by-Step Solution
We will use the SQL on CSV to execute this entirely in your browser.
- 1
Upload your annual Stripe Balance Transactions CSV
Drag and drop the file (typically 400K–600K rows). The tool streams it into DuckDB-Wasm memory without loading the entire file into browser RAM. Processing begins immediately.
- 2
Run the reconciliation SQL template
A pre-written SQL query joins disputes/refunds to original charges via Source ID, computes net revenue per charge, and flags any dispute_warning_under_review events that haven't yet escalated. You can modify the query for custom filters.
- 3
Export reconciliation report
Download a CSV showing: original charge amount, refund amount, dispute amount, net realized revenue, and dispute status. A summary tab shows your current dispute rate vs Visa VDMP (0.9%) and Mastercard ECP (1.5%) thresholds.
Privacy Guarantee: Zero Bytes Uploaded
All processing happens in your browser via WebAssembly. Your data never touches a server. Try it: disconnect your Wi-Fi right now—the tool will still work.
Common Errors & Fixes
| Error / Symptom | Root Cause | Fix |
|---|---|---|
VLOOKUP returns #N/A on 23% of rows | Source ID has leading/trailing whitespace or different prefix format than ID column | This tool's SQL JOIN uses TRIM() and CAST() to normalize IDs before matching |
Dispute rate appears lower than reality | Counting 'dispute_warning_under_review' as non-disputes when they may escalate | Track pre-dispute warnings separately. If >50% escalate within 30 days, include them in your VDMP projection. |
Frequently Asked Questions
What's the difference between 'dispute_warning_under_review' and a filed dispute?
How often should I run this reconciliation?
Ready to clean your data?
100% local processing · Zero uploads · Blazing fast
Trusted by 2,400+ data teams · 18M+ rows processed monthly