Exchange rates: the automation bug that never throws an error

The customer paid in full and still got a dunning letter

A logistics operator we work with sends payment reminders every Monday morning. The rule is simple: any open item above zero gets a letter. It ran for years without complaint until the company started invoicing Swiss customers in francs and exchange rates entered the picture.

An invoice for CHF 12,500 was booked at the rate on the invoice date, which made it EUR 12,980 in the ledger. Six weeks later the customer paid on time and in full. The bank credited EUR 12,905, because the rate had moved. The open item dropped by 12,905 and stopped at 75. To the automation that looks exactly like a partial payment, so on Monday it wrote to a customer who owed nothing.

That is what currency bugs look like in automated systems. Nothing crashes. No run turns red. There is no stack trace to read, only numbers that look reasonable and are wrong by a percent or two, plus a support queue full of customers asking why they are being chased for 75 euros.

We spend a good part of our project time on this, usually after a month end that did not add up. What follows is the set of decisions that stops it happening, roughly in the order they bite.

An amount without a currency code is not an amount

The first design mistake sits at the very start of the chain. A webhook delivers `amount: 18400`. The workflow writes it into a field the target system calls "order value" and keeps in euros. Nobody lied and nobody miscalculated, and 18,400 francs just became 18,400 euros.

While a business invoices in one currency this never shows. It starts the day the first foreign customer arrives, and it applies retroactively to the whole dataset, because the older records carry no currency code and you cannot reliably reconstruct one later.

So: an amount is two fields, the value and the ISO 4217 code, and they travel together through every webhook, table and system in the chain. Anything that accepts a bare number is making an assumption, and nobody remembers assumptions two years on.

Source systems disagree about what goes in that field anyway. Stripe sends minor units, so 1840 means 18.40. PayPal sends a string with a decimal point. A German ERP will happily export 18.400,00 with the dot as a thousands separator. Put two of those into the same field without normalising and you get a dataset where a factor of 100 is randomly distributed through it.

Normalise once, immediately after receipt, and never reshape the value again. We store three things at that point: the amount as an integer in minor units, the currency code, and where it came from.

Not every currency has two decimals

Multiplying by 100 to reach minor units works for euros, dollars and francs. The Japanese yen has no decimal places at all, so 1000 JPY is a thousand yen, not ten. The Kuwaiti, Bahraini and Tunisian dinars have three, where the minor unit is a thousandth.

This stays theoretical until somebody bills a supplier in Tunis. Hard-code the hundred and you multiply yen amounts by ten or divide dinar amounts by the same, and because both results are still the right order of magnitude, no approver catches it.

Decimal places are defined in ISO 4217 and effectively never change. Keep them in a small table alongside the currencies you actually handle, rather than in a formula repeated three times across a scenario. The table doubles as an allow list: a currency that is not in it stops the run instead of getting an invented conversion.

Floats are the wrong type for money

Make, n8n and Zapier all do arithmetic the way JavaScript does. In that arithmetic 0.1 plus 0.2 is 0.30000000000000004. On a single amount it does not matter. Loop over two hundred invoice lines and round at the end and the residue walks upward, producing totals that differ by a cent from the source document.

A cent is harmless until something checks it. The moment a payment provider reconciles, or an accounting interface compares the header amount against the sum of the lines, that cent becomes a rejected posting. We have found interfaces quietly pushing three or four documents a day into an error queue for someone to rebook by hand, for years, with nobody ever asking why.

Carry amounts as integers in minor units inside the workflow and divide only on output. Where the target system insists on decimals, round immediately after each multiplication rather than once at the end. And treat the amount printed on the document as the reference: if your automation recalculates a total and disagrees with it, that is a reason to stop, not a reason to overwrite.

There is no single correct rate

Several legitimate rates exist for the same conversion on the same day, and they differ by amounts your accountant will see.

The ECB publishes reference rates on banking days, in the afternoon. They are free, traceable and fine for internal valuation. Nobody buys currency at them. Your bank uses its own rate plus a margin, and the money that actually lands in the account follows that one. Stripe, PayPal and Adyen apply their own rates and report them, so if you read those reports automatically, take the rate they give you instead of recomputing it. German VAT has its own monthly average rates published by the finance ministry, and customs valuation has yet another set, published monthly and deliberately not tracking the daily market.

Four or five rates, each with a purpose. They all exist for good reasons. The problem is that in most automated setups the choice gets made implicitly. Someone wired a free rate API into a Make scenario eighteen months ago because it worked without signing up, and that service has been the company's authoritative source ever since. No one ever decided that.

Pick one source per purpose, write it down, and stop arguing about it at every close.

Freeze the rate and store it

Between quotation and payment, eight weeks pass easily on a trade deal, and EUR/USD moves two to four percent in that time. That gives one transaction five plausible conversion dates: quotation, order confirmation, delivery, invoice, payment.

Automations pick whichever moment the workflow happens to run, which is rarely the right one. A nightly job processing yesterday's orders uses this morning's rate. A retry after an outage runs three days later and gives the same order a different euro value than the first attempt did.

Two rules fix most of it. Fetch the rate for the business date, not the execution date; rate providers serve historical rates and it costs one extra parameter. Then freeze it on the document and store it: the rate itself, the date it applies to, and the source. Three extra fields per record. Without them, a converted amount cannot be audited because the arithmetic cannot be reconstructed. With them, "how did you arrive at this number?" takes thirty seconds instead of four days.

The pattern we keep having to remove is live conversion at display time. A dashboard that converts foreign currency at today's rate whenever someone opens it looks modern and is useless for steering, because last quarter's revenue changes daily without a single new order.

Rounding: per line or per total

Three lines of USD 33.33 at a rate of 0.9134. Convert each line and you get 30.4436 three times, rounded to 30.44, totalling EUR 91.32. Convert the total of 99.99 and you get 91.3306, rounded to EUR 91.33. Both are correct, and they differ.

On one document that is a footnote. In an interface that transfers header and lines separately and validates them against each other, it is a daily failure. Put the rule in the spec before anyone builds: either lines are authoritative and the header is their sum, or the header is authoritative and the lines absorb the difference. Either works, as long as every system involved uses the same one.

Rounding mode matters too. Commercial rounding takes a five up. Some libraries and some anglophone systems round half to even to avoid a systematic bias across large datasets. Between two systems using different modes, the discrepancies appear on roughly every tenth converted value, which looks like randomness and is not.

And splitting an amount deserves a rule of its own. One hundred euros across three cost centres is 33.33 three times plus a residual cent. Automated commission and allocation runs that drop that cent accumulate a number somebody has to explain.

What breaks in the tools themselves

The rate lookup usually hangs off a free service with no availability commitment. Good enough for a prototype, unsuitable for a process converting six figures a month. When it fails, the field comes back empty.

The empty field is the dangerous part. In Make, multiplying by an empty value yields zero or an error depending on the formula. In n8n, multiplying by `undefined` yields NaN, which then lands in the target field as text. Target systems accept both without complaint. An order worth zero never appears in any report, and an order with NaN in the amount field surfaces the first time somebody sorts the column.

So we put a hard check into every currency workflow: if the rate is empty, zero, negative, or implausibly far from yesterday's value, the run stops and the record goes to an exception queue. Someone fixes a stopped run that afternoon. A silent zero gets found in May.

Two smaller ones. Fetch the rate once per currency per day into a small table rather than once per record, which saves quota and, more usefully, leaves the rate documented and auditable. And decide what your provider returns at weekends and on public holidays, when no new reference rate is published: the Friday value, the last available value, or nothing. All three are defensible, they are not the same, and the workflow needs to know which it is getting.

Four queries that tell you whether this is already happening

Before commissioning a project, look for the symptoms in the systems you already have.

Records with an amount of zero that still carry a customer and a line item. In clean data these are almost exclusively samples and free deliveries. A cluster of them usually means a rate lookup came back empty.

Foreign currency orders whose euro amount exactly equals the original amount. If CHF 18,400 sits in the CRM as EUR 18,400, no conversion happened.

Open items under fifty euros that are more than sixty days old. In a business trading abroad, that list is mostly exchange differences, and if it is long, your dunning process is working against your own customers.

Amount fields containing NaN, undefined, or a number with fourteen decimal places. That is direct proof that a conversion ran without a check on it.

Those four queries take a morning. The alternative is finding out at the close, which in the case we opened with took a controller four days.

Decide these seven things before anyone builds

Your reporting currency. The rule that every amount travels with a currency code. The rate source per purpose, operational, tax and payments. The business date that governs conversion. The rounding level and mode. How exchange differences are handled when clearing open items, including a tolerance and an account. And what happens when the rate is missing, which should always be: stop.

That is a ninety minute conversation between finance, sales and whoever is building the workflow. Skip it and the workflow builder makes all seven calls alone, differently in each scenario, and nobody can say afterwards which call was made where.

If you want a quick read on your own situation, take one foreign currency invoice from last quarter, ideally one that has been paid, and follow the amount through every system that touched it. Write down the euro value at each stop and the rate that produced it. If you cannot reconstruct the rate somewhere, you have an auditability problem. If two systems show different euro values, you have a reconciliation problem, and not just on that invoice.