CSV troubleshooting
How to Combine Multiple Currencies in a CSV Without Corrupting the Total
Do not add amounts from different currencies until every row has an explicit currency code and a documented conversion rule. Changing $100 to RM100 changes only the label, not the value. Keep the original amount and currency, join each row to an approved exchange rate for the correct date and rate type, calculate a separate reporting-currency amount, and reconcile samples and totals. If the file lacks currency or transaction-date evidence, report separate subtotals instead of inventing one converted total.
Short answer
Do not add amounts from different currencies until every row has an explicit currency code and a documented conversion rule. Changing $100 to RM100 changes only the label, not the value. Keep the original amount and currency, join each row to an approved exchange rate for the correct date and rate type, calculate a separate reporting-currency amount, and reconcile samples and totals. If the file lacks currency or transaction-date evidence, report separate subtotals instead of inventing one converted total.
Upload
Bring the CSV you already use.
Understand
Start with the brief and evidence.
Act
Ask, edit, filter, and export.
A practical starting point
Do not add amounts from different currencies until every row has an explicit currency code and a documented conversion rule. Changing $100 to RM100 changes only the label, not the value. Keep the original amount and currency, join each row to an approved exchange rate for the correct date and rate type, calculate a separate reporting-currency amount, and reconcile samples and totals. If the file lacks currency or transaction-date evidence, report separate subtotals instead of inventing one converted total.
Concrete examples
Example 1: A symbol change creates a false total
A file contains 100 USD and 100 MYR. Someone formats both cells as Ringgit and reports RM200. No conversion occurred. The corrected workflow retains both original rows, applies the approved dated USD-to-MYR rate to the USD row, and then sums only the resulting MYR reporting amounts.
Example 2: The exchange-rate pair is reversed
A rate table records 4.70 MYR per USD. A formula divides 100 USD by 4.70, producing about 21.28 MYR instead of multiplying to produce 470 MYR. The team documents the quote direction in the column name, checks a known transaction, and rejects rates outside an expected review range.
Example 3: One row has no currency code
Marketplace exports are appended into one file, but a row contains 250.00 with a blank currency. The analyst does not assume it is MYR from the seller's location. The row is excluded from the converted grand total, included in an exception count, and traced back to its source export.
Example 4: A rate join duplicates transactions
The rate table has two USD rows for the same date and reporting currency. A many-to-many join duplicates each matching sale, doubling revenue. A uniqueness check on the intended rate key catches the problem before aggregation; the approved rate record is selected under a documented rule.
Example 5: A refund is converted twice
A payment export already provides both originalamount = -50 USD and settlementamount = -235 MYR. A cleanup process converts the settlement amount again because it ignores the column definition. The team uses the documented settlement field as the reporting amount, keeps its booked rate for reconciliation, and avoids a second conversion.
Examples are illustrative and are not customer results.
Common questions
What is the safest way to work with this CSV problem?
Do not add amounts from different currencies until every row has an explicit currency code and a documented conversion rule. Changing $100 to RM100 changes only the label, not the value. Keep the original amount and currency, join each row to an approved exchange rate for the correct date and rate type, calculate a separate reporting-currency amount, and reconcile samples and totals. If the file lacks currency or transaction-date evidence, report separate subtotals instead of inventing one converted total.
Keep exploring
See what your own CSV is saying.
Try the browser workspace with a real file and keep the public guide open when you need help.
Start free