Daily Exchange Rate Sync to Your ERP and EAM

Manual exchange-rate maintenance is one of those tasks that everyone agrees is silly, and that everyone keeps doing. Someone in finance opens an authoritative website each morning, copies the rates, and types them into the ERP. Most days that works. The days it doesn't are the days you find out how much the ERP relies on it. This is the story of replacing that ritual with a small, well-behaved cron job that pulls from a chosen rate source every morning and pushes rates into the ERP and EAM in parallel.

The hidden cost of stale FX rates

Stale or wrong exchange rates do not produce a single dramatic failure. They produce a steady drip of small wrong numbers across the entire general ledger, every transaction that touches a foreign currency, every revaluation at period close, every vendor invoice booked against an old rate. By the time finance notices, the damage is spread across hundreds of rows.

The concrete symptoms on the deployment I'll describe were familiar: vendor disputes over invoice values, GL balances that didn't reconcile against bank statements, period close routinely delayed by an hour or two while someone walked back through entries to figure out which rate had been used where. None of it catastrophic. All of it noise that should not have been there.

The other cost was attention. A senior accountant was spending the first thirty minutes of every working day on a copy-paste job. That's eight to ten hours a month of expensive labour on a task that should be invisible.

Where the source of truth lives

An authoritative daily-rate source is the right place to start. That source can be a central monetary authority, a regulated treasury portal, a licensed wire feed, or a third-party rate aggregator with a reliable SLA, whatever fits your jurisdiction and contractual constraints. Most publish daily reference rates against a base currency, available on a stable URL each working morning.

The pattern across sources is similar but not identical. Some publish a clean HTML table, some a CSV download, some expose a JSON endpoint, some only offer a PDF. Some publish once a day at a fixed time, some update intra-day. Some skip weekends and public holidays cleanly, some leave the previous day's rates up without timestamping.

For this deployment, the chosen source published rates on a stable URL as an HTML table, updated every working morning at a predictable time. That made the engineering tractable. The lesson generalises: pick the most authoritative publication available to you, verify how and when it updates, and design the scraper around its actual behaviour.

The three layers: scrape, normalise, push

The integration architecture is deliberately split into three layers, each independent.

1. Scrape layer. Pulls the source page, parses the rate table, and writes a daily snapshot to a staging table with a publication timestamp. This layer knows about HTML and the source's table structure, nothing else.
2. Normalisation layer. Reads the staging row, applies rounding rules, computes any cross-rates the downstream systems need, and writes a clean canonical record to a published rates table. This layer knows about precision and conventions, not about scraping or about specific ERPs.
3. Push layer. Reads the published canonical record and dispatches updates to each downstream system: ERP, EAM, and any other consumers. One subroutine per target system. This layer knows about target APIs, nothing else.

The split mattered because each layer changed for different reasons. The scraper changed when the source adjusted its page markup. The normalisation changed when a new currency was added or a precision requirement shifted. The push changed when a target system upgraded its API. Keeping them separated meant a change in one didn't risk regressions in the others.

Scraping reliably

The scrape layer is the layer that breaks. Rate-source websites are not designed for machine consumption. They get redesigned, they get redirected, they get migrated to new platforms with no notice. The scraper has to assume each of these things will happen and behave well when they do.

The non-negotiables for this layer:

• A clear user agent identifying the request. Not because it makes scraping more reliable, but because it makes the request honest. Publishers have legitimate operational reasons to know who's pulling their data.
• Retry with exponential backoff. Transient 5xx errors and timeouts happen. Three tries with backing-off delays handles most of them. Past three retries, alert and bail.
• Change detection. Compare the publication date against the most recent staging row. If the page still shows yesterday's date (because the morning publication is late or skipped), do not reprocess yesterday's rates as today's. Wait, alert if too long.
• Schema validation. Before writing to staging, verify the parsed table has the expected columns and at least the expected core currencies. A page that loaded but has the wrong shape is more dangerous than a page that didn't load at all.
• Polite cadence. One request per morning is sufficient. There is no need to hammer the source.

The thing that broke first was schema validation. The page redesign moved the rate table inside a new wrapper element, and the parser silently returned an empty list. The cron logged "success", finance saw no errors, and rates stopped updating for two days before someone noticed. Schema validation was added the same week.

Normalisation: where bugs hide

The normalisation layer looks trivial. It is not. Three places bugs cluster:

• Base currency convention. The source publishes rates in one direction (e.g., domestic currency per unit of foreign currency). The ERP may want the opposite. Get this wrong and every foreign-currency transaction is off by a factor of the rate squared. We hard-coded the convention in the canonical record and flipped per target system in the push layer, not in normalisation.
• Decimal precision. Central banks publish to a fixed precision. ERPs may store rates to a different precision. Round once at normalisation, store the rounded value, and push that value. Rounding twice (once on import, once on each push) introduces drift across systems.
• Cross-rates. If the system needs USD to EUR but only base-to-USD and base-to-EUR are published, compute the cross-rate explicitly in normalisation, store it, and push it. Don't let each consumer compute its own cross-rate or you will get drift between systems that should agree.

The normalisation layer is where the "obvious" mistakes live, and the ones that produce the loudest finance complaints when they surface.

Pushing into the ERP and EAM

The push layer is the simplest layer but the one that touches the most surface area. Each target system has its own API and its own quirks. The deployment in question pushed into two systems in parallel:

• The ERP (Dynamics 365 Business Central in this case). A single API call per currency pair to upsert the rate record for the day. The transaction-date field had to match the rate's effective date, not the date of the import. Period-locking caveat: if the period is closed, the push fails fast and alerts rather than silently skipping.
• The EAM (Hexagon EAM). EAM doesn't strictly need daily FX rates for work-order processing, but cost roll-ups for projects that cross currencies do. A simpler push: insert into the rate table, key on date plus currency pair.

Three details mattered.

First, idempotency. If the cron runs twice (because someone manually triggered a re-sync), the push should not produce two records or double the rates. Both targets handled this naturally with an upsert pattern, but it had to be verified explicitly.

Second, transaction-date semantics. The rate published this morning is for today. But the time the cron runs is also today. The push has to be explicit: rate effective from today, valid until the next publication. Implicit "use the time of the push" produced subtle off-by-one errors at boundaries.

Third, partial failure handling. If the ERP push succeeds and the EAM push fails, the next morning's cron should retry only the failed target, not both. We logged success state per target so the retry path knew exactly what to re-do.

Cron and observability

The job runs once a day, twenty minutes after the expected source publication time. Twenty minutes is enough margin for the source to actually update if the publication is briefly late, while still landing well before the working day for users in the same time zone.

Observability needs are modest but non-optional:

• Structured audit log. Every run writes a row: started_at, finished_at, scrape_status, normalisation_status, push_status per target, currencies pulled, rate hashes. The audit log is what gets queried when finance asks "why did this rate come out this way".
• Success ping. A daily "all green" message to a Slack channel or email recipient. Silence is alerting: no ping by 30 minutes after expected run time means someone investigates.
• Failure alert. Any non-success state triggers an immediate alert with the failure reason and the run id. The alert message says exactly what to do (re-run, check page, check target API), not just "something failed".
• Lock file. Two concurrent runs would race on the staging table. A simple lock prevents this. Stale locks expire after a reasonable timeout so a crashed previous run doesn't block tomorrow.

Edge cases (the ones that catch you)

Five categories of edge case that needed explicit handling:

• Weekends. Most rate sources don't publish on weekends. The cron should not run those days, or if it does, it should detect "no new publication" and exit clean without alerting.
• Public holidays. National holidays. Same handling as weekends, but the calendar is per-jurisdiction and can move. Maintain the holiday list and update it annually.
• Late publications. Some mornings the source publishes 30-90 minutes late. Without a retry window, the cron would either alert prematurely or miss the rate entirely. Build in a wait-and-retry pattern around the expected publication window.
• Currency adds and removes. Once a year or so, the source adjusts the list of currencies it publishes. The scraper should not silently drop currencies that disappear or accept new ones without finance sign-off.
• Retroactive corrections. Very rarely, a published rate gets corrected the next day. The integration needs a manual override path for finance to apply the correction with a clear audit trail.

What to measure

Four metrics, all easy to instrument, all worth knowing:

• Sync uptime. Working days in the period where rates were successfully pulled and pushed by the expected time. Should be 100% or close.
• Time-to-detect-failure. When a run fails, how long before the right human is alerted. Target: minutes, not hours.
• Daily rate deviation magnitude. The day-over-day percentage move on each currency pair. Sudden outliers are sometimes real market events and sometimes scraping bugs. Knowing the distribution helps catch the second category.
• Manual override frequency. How often finance has to intervene. Trending up means something upstream is degrading.

Where this would break

The pattern is robust but not universal. Four situations where I would not recommend it as designed:

• Sources with terms of service forbidding scraping. Some publishers (including some commercial wire feeds) explicitly disallow programmatic access to their pages. Use their official API if they offer one, or license a wire feed (Bloomberg, Reuters) instead.
• Operations needing intra-day rates. Trading and treasury contexts need rates updated multiple times per day, not once. The architecture extends but the source of truth changes, you can't get intra-day rates from a daily-reference publication.
• Multi-source consensus needs. Some jurisdictions or contracts require an average of two or three published rates. This is a layer above what's described here. Pull from each, normalise, average, push.
• Sanctioned-currency restrictions. If your organisation cannot transact in certain currencies, the push layer needs an allowlist filter so disallowed pairs are not propagated to operational systems where they might be used by mistake.

Natural extensions

Once the daily-rate cron is operating cleanly, several extensions become inexpensive:

• Historic rate backfill. Pull the source's archive on the same parsing pattern to populate any gaps in historical rates. Useful for retrospective reporting.
• FX exposure reporting. Now that rates are reliable, build a daily dashboard of total FX exposure by currency pair. Treasury cares.
• Rate alert thresholds. When a currency pair moves more than X percent day-over-day, alert finance. Helps catch real market moves and scraping bugs together.
• Other operational pulls. The same three-layer pattern handles other authoritative publications: interest rates, regulatory filings, market indices. Same scraper skeleton, different normalisation.

Related reading on integration and finance pipelines: CAFM and Business Central integration, three-way matching, and AI-assisted cost coding, which sits one layer above this in the same finance integration story.