Constant-currency consolidation engine that splits multi-entity growth into scope, organic, and FX translation impact, with a bridge that ties to reported figures exactly (max residual 0.000001 measured at 1,000 entities).
- "Is growth real or is it the dollar?" gets answered with an exact three-way bridge (scope, organic, FX) instead of a hand-built spreadsheet that never quite ties.
- The bridge reconciles to reported figures by construction: no plug line, no residual bucket, integrity-checked at half a cent on every row and measured at 1e-06 across 1,000 entities.
- One command turns entity-level actuals plus monthly rates into a consolidated P&L, bridge CSVs, an executive Excel workbook, and Power BI-ready extracts.
Every multinational finance team publishes growth numbers, and every month someone asks how much of that growth is business performance versus currency translation. The standard answer is a constant-currency analysis assembled by hand in Excel: translate this at that rate, restate that column, and hope the bridge ties. When it does not, a plug line labeled "other" absorbs the difference, and the analysis quietly stops being trustworthy. In a global payments or banking group where entities enter and exit the portfolio mid-year, the spreadsheet version also conflates acquisition effects with organic growth.
fx-impact-analyzer does the decomposition properly. It loads entity-level monthly actuals (local currency) and monthly average rates into a SQLite store, validates them relationally (duplicate keys, missing rates, entities reporting in two currencies), then decomposes each period's year-over-year change into three components: scope (entities that entered or exited, valued at prior-year rates), organic (like-for-like local growth at prior-year rates), and FX (current volumes repriced from prior-year to current rates). The three components sum to the reported change identically, in exact arithmetic, because the decomposition is algebraic rather than plugged. An integrity check enforces this at half a cent per row and the run fails loudly if it ever breaks.
The methodology is the one large reporters use for "currency-neutral" growth (current period at prior-year rates; ADR-001 explains why over the alternatives), and the new-market edge case, a currency with no prior-year quote at all, is handled by a documented convention rather than a crash: the entity lands in scope at the current rate, FX is zero by definition, and the affected currencies are reported on the bridge row itself.
flowchart LR
A[actuals.csv\nentity, currency, period] --> S[(SQLite store\nvalidation in SQL)]
R[rates.csv\nmonthly averages + source] --> S
S -->|reject with reasons| X[exit 1, no partial output]
S --> C[consolidate.py\nreported P&L in USD]
S --> B[bridge.py\nscope + organic + fx\nexact identity]
B --> I{integrity check\nresidual <= 0.005?}
I -->|no| F[exit 3, run fails]
I -->|yes| O1[bridge.csv + entity_bridge.csv]
I -->|yes| O2[fx_bridge.xlsx\nSummary, Bridge, Rates]
I -->|yes| O3[fact/dim CSVs for Power BI]
| Technology | Role in this project | Why chosen here |
|---|---|---|
| Python 3.10+ | Engine, CLI | The decomposition reads like the algebra it implements |
| pandas | Bridge math | Entity-level vector math with currency mapping stays close to the formulas in ADR-001 |
| SQLite (stdlib) | Input store and validation | Validation is five readable SQL queries; --db persists the exact inputs as a one-file audit artifact (ADR-002) |
| openpyxl | Executive workbook | The consumers of a bridge live in Excel; meeting them there is part of the job |
| pytest + pytest-cov | 22 tests, 96% measured coverage | Includes a 100-population randomized property test of the tie-out identity |
| ruff + GitHub Actions | Lint and tests on 3.10/3.11/3.12 | Keeps every push honest |
Prerequisites: Python 3.10+, pip, git.
git clone https://github.com/Vanithanallamothu/fx-impact-analyzer.git
cd fx-impact-analyzer
pip install -e ".[dev]"
# generate synthetic multi-entity actuals and rates (24 months, 8 currencies)
python data/generator.py --entities 40 --months 24 \
--actuals /tmp/actuals.csv --rates /tmp/rates.csv
# consolidate, bridge, and export
fxia run --actuals /tmp/actuals.csv --rates /tmp/rates.csv --output-dir /tmp/fx_out
# outputs
ls /tmp/fx_out
head -4 /tmp/fx_out/bridge.csv
# run the tests
pytest --cov=fxiaFor real use, export entity actuals and treasury monthly-average rates to
the two CSV layouts in src/fxia/store.py. The bundled rates are synthetic
(labeled as such in rate_source); the engine is rate-source agnostic.
Methodology: benchmark/run_benchmark.py runs the full CLI (process start, load, validation, consolidation, bridging, all exports) 5 times per size on a 2-CPU Linux container, Python 3.11. Raw output in benchmark/results/throughput.json.
xychart-beta
title "Full-run wall clock vs portfolio size (5 runs per size)"
x-axis ["50e x 24m", "200e x 36m", "500e x 36m", "1000e x 36m"]
y-axis "seconds" 0 --> 5
line "p50" [0.63, 1.43, 2.34, 3.99]
line "p99" [0.64, 1.52, 2.37, 4.14]
| Entities x months | Actuals rows | p50 | p95 | p99 | Max bridge residual |
|---|---|---|---|---|---|
| 50 x 24 | 2,122 | 0.63 s | 0.64 s | 0.64 s | 0.0 |
| 200 x 36 | 12,736 | 1.43 s | 1.51 s | 1.52 s | 0.0 |
| 500 x 36 | 31,890 | 2.34 s | 2.37 s | 2.37 s | 0.000001 |
| 1000 x 36 | 63,412 | 3.99 s | 4.12 s | 4.14 s | 0.000001 |
The residual column is the point: at 1,000 entities the worst tie-out error across every bridge row is a millionth of a currency unit, which is float dust, not a plug. Where it degrades: the per-entity bridge loop is Python-level row iteration, so runtime grows roughly linearly with entity count and would become noticeable in the tens of thousands of entities; vectorizing that loop is the known fix and the consolidation itself is already set-based SQL.
Two ADRs in docs/adr/:
- ADR-001: Current period at prior-year rates. Why this constant-currency convention over restating the prior period or using budget rates, and what the new-market fallback means.
- ADR-002: SQLite over a client-server database. The boring choice: a monthly single-writer batch does not need a database server, and the --db flag turns the validated inputs into a one-file audit artifact.
- Transaction FX (realized and unrealized gains, hedging effects). This tool measures translation impact on reported P&L only. Trigger to add: treasury asking for exposure analysis rather than reporting analysis, which is a different data model (transactions, not monthly actuals).
- Balance sheet translation (closing rates, CTA). Trigger: extending beyond P&L bridging into full statement consolidation.
- Rate sourcing. The engine consumes rates; it does not fetch them. Wiring a treasury system or ECB reference feed is deliberately an integration task, not an engine feature.
- No secrets: the tool reads two local CSVs and writes local files. Configuration is CLI flags plus two optional env vars (FXIA_REPORTING_CURRENCY, FXIA_TIEOUT_TOLERANCE).
- Entity-level financials are sensitive. The tool makes no network calls; run it where the data already lives. Logs carry row counts and residuals, never amounts by entity.
- The --db artifact contains full inputs by design (it is the audit trail); treat it with the same handling class as the source extracts.
- CI is lint and tests only, with default GitHub token scope.
| Failure | Detection | Behavior | Recovery |
|---|---|---|---|
| Missing columns, empty files | Structural checks on load | Exit 1, named columns in the error | Fix the export layout |
| Duplicate entity/period rows or duplicate rates | SQL validation before any math | Exit 1 with counts per problem | Deduplicate upstream; usually a double export |
| Actuals rows with no matching rate | Referential SQL check | Exit 1, count of uncovered currency/periods | Complete the rates file for the missing months |
| Entity reporting in two currencies | SQL validation | Exit 1 naming the problem | Split into one entity per functional currency, which is how consolidation systems model it anyway |
| Currency with no rate in either period | Bridge guard | Exit 1; this is incomplete input, not a new market | Add the currency to the rates file |
| New-market currency (no prior-year quote) | Bridge fallback | Run continues; constant rate pinned to current, currencies named on the row, warning logged | None needed; the convention is documented in ADR-001 |
| Bridge identity breaks (logic regression) | Integrity check on every row | Exit 3, max residual and tolerance in the log, outputs not trusted | This is a defect; the randomized property test in tests/test_bridge.py is where to reproduce it |
| No prior-year periods at all | Bridge orchestrator | Consolidation still written; bridge outputs skipped with a warning | Provide at least 13 months for a year-over-year bridge |
The first version of the bridge treated "no prior-year rate for this currency" as a data error and refused to run. That felt rigorous, until an adversarial test simulated the group's first Indian entity: ten months of INR actuals, INR rates quoted from the entry month onward, and no INR quote anywhere in the prior year because the group had never held the currency. One legitimate new-market entity crashed consolidation for the entire portfolio, every period, every other entity.
The root cause is methodological, not mechanical: constant currency is genuinely undefined for a currency with no prior-year rate, so there is no correct number to compute silently, but failing the whole run treats a normal business event (entering a market) as corrupt input. The resolution is the convention FP&A teams actually use, made explicit: pin that currency's constant rate to the current rate, which places the new entity entirely in scope with zero measurable FX, then surface the decision instead of hiding it. The affected currencies are written onto the bridge row itself (cc_fallback_currencies), and the run logs a warning naming them (fix commit 5641d4b, verified by rerunning the exact crashing scenario).
The lesson that generalizes: the strictness boundary matters as much as the strictness. A currency missing from both periods is still a hard failure, because that is incomplete input; a currency missing only from the prior year is a business event with a documented convention. Distinguishing the two is the difference between a tool finance teams trust and one they route around.
- Vectorize the per-entity bridge loop before portfolios reach tens of thousands of entities (the measured knee described under Performance).
- Month-to-date and quarter-over-quarter bridging alongside year-over-year, which is the same algebra with a different prior-period selector.
- A rates adapter for ECB reference rates and treasury system exports, kept outside the engine on purpose.
- Balance sheet translation with CTA once P&L bridging is embedded in a real close cycle.
- First metric to watch in real use: how often the tie-out check fires after input mapping changes, which measures whether upstream extracts are drifting.