Tweet
During historical data analysis of quote records, the current approach of storing monetary values exclusively in their transaction currency presents significant limitations for accurate aggregation and reporting (e.g. total revenue).
Problem
At present, each quote stores only the converted total in the quote’s currency context, without retaining the original conversion metadata (specifically the exchange rate used at the time of conversion). This leads to several issues:
As a result, large-scale data mining (e.g. annual revenue analysis) becomes unreliable or effectively unusable.
Proposed Solution
Introduce dual-storage of financial values at the point of quote creation:
This change may require updates to:
Problem
At present, each quote stores only the converted total in the quote’s currency context, without retaining the original conversion metadata (specifically the exchange rate used at the time of conversion). This leads to several issues:
- Inability to reliably aggregate revenue across multiple currencies
- Loss of auditability for historical FX conversions
- Inconsistent and potentially non-reproducible financial reporting
- Reduced usefulness of data for long-term analytics and forecasting
As a result, large-scale data mining (e.g. annual revenue analysis) becomes unreliable or effectively unusable.
Proposed Solution
Introduce dual-storage of financial values at the point of quote creation:
- Base system currency total
- Store the total value converted into the system’s base currency (e.g. GBP or USD)
- Transaction currency total (existing behaviour retained)
- Continue storing the original quote currency amount for display and operational use
- Exchange rate metadata
- Store the FX rate used for conversion at the time of quote creation
- Optionally include source/reference of rate if available (e.g. provider or rate table version)
- Enables accurate historical revenue reporting in a single base currency
- Preserves original transaction context for auditing and compliance
- Allows recalculation or validation of historical conversions
- Improves reliability of BI and data mining processes
- Future-proofs financial reporting against FX fluctuations
This change may require updates to:
- Quote creation logic
- Existing reporting pipelines
- Historical data migration strategy (if retroactive consistency is desired)