Financial Reporting at Billion Dollar Scale — Exchange RatesHow Airbnb handles exchange rates for financial reportingWanli YangBlockedUnblockFollowFollowingJun 5Authors: Wanli Yang, Ziheng Deng and Wendy ShiBuilding a financial reporting system is complicated, as it touches almost every aspect of a business.
A financial reporting system’s input data comes from platforms that define pricing and payment handling, and its output data is used for accounting, tax withholding & reporting, reconciliation, forecasting, and other key functions.
With Airbnb’s tremendous business growth, our financial reporting system has evolved substantially over the years.
To shed light on some of the challenges faced, we’re writing a series of blogs on different aspects of this complex financial reporting system.
In this post, we’re discussing exchange rates, since financial reporting cannot be accurate without correct exchange rates.
When a business only operates in one market or uses one currency, it is far easier to build a financial reporting system.
However, a system without the proper design to support different currencies will face numerous issues as the business expands to new international markets.
The first step in handling different currencies in financial reporting is obtaining reliable exchange rates.
Although several credible financial service companies provide foreign exchange data via exchange-rate APIs, reporting systems need to consider many other aspects when deploying these rates:Use Same Exchange Rate Version for Same TransactionIf the online system uses certain exchange rates to create a contract, the offline system should use the same rates reporting the contract.
Although this principle sounds simple, it is surprisingly easy to break this rule.
First of all, there should be a single “source of truth” for exchange rates for all systems.
If an online system uses one source to secure exchange rates, and the reporting system uses another source to get its exchange rates, the rates used by the two systems will likely differ due to rate fluctuations.
Even with a single source of exchange rates, the interpretation of rates must be consistent across different systems.
For example, if the online system updates exchange rates at 2 AM every night, transactions created between midnight and 2 AM essentially use the exchange rates from the previous day.
However, if the offline financial reporting system uses today’s exchange rates to process these transactions, the two systems clearly won’t reconcile.
So instead of relying on transaction date to refer to the exchange rates used for a transaction, it is better to identify a particular exchange rate using a unique version ID that should be used in all systems.
The following graph illustrates this difference: the transaction actually happened on 1/2/2018, but instead used exchange rates from 1/1/2018.
If the Exchange Rates table has version IDs, the transaction can use the version ID to refer to the correct exchange rate utilized.
Ensure Exchange Rate Versions Are ImmutableAfter exchange rates versions are introduced, the Exchange Rates table design becomes more robust because a record is no longer tied to the time it is created.
The system can update the latest rates any time or at any frequency by writing the new rates (along with new version IDs) to the database and managing the online service to spin up this new version.
The Exchange Rates table stores the full history of exchange rates and serves as the “source of truth” for exchange rates used in the system.
Therefore all exchange rates should be immutable.
This means once exchange rates for a version are locked down, they should not be modified or deleted.
In the rare cases when historical rates have to be corrected, a new version should be created to store the corrected rates.
Another benefit of immutable exchange rates is that the exchange rates used in all transactions are auditable.
The exchange rate version number in the Transactions table are guaranteed to be tied back to the Exchange Rates table when any rates are fetched.
This is particularly helpful for debugging or government audit scenarios.
Consider Decimal Place and Rounding issuesThe next decision is how many decimal places the exchange rates should have.
If it is set too low, it is easy to see that the financial reporting will suffer accuracy issues.
For example, 1 XAU = 522630622645.
When the base currency is VEF and the quote currency is XAU, if fewer than 12 decimal places are used, the API will simply return 0.
But setting it too high can also cause issues.
For instance, if the financial reporting system integrates with a third-party system that doesn’t support 15 decimal places, options may be limited.
So a thorough review of all the systems using exchange rates needs to be completed before choosing the number of decimal places to use.
One needs to be careful with rounding, too.
Leading zeros of a number are not significant digits.
A small number rounded to a high number of decimal places can still lose significant digits and introduce inaccuracy.
For example, rounding VEF to XAU exchange rate 0.
000000000001914 to 12 decimal places is 0.
000000000002, which is 4.
5% larger than the unrounded exchange rate.
Carefully Calculate Inverse Exchange ratesFinancial reporting systems usually require exchange rates between all pairs of currencies in both directions.
Again, exchange rates can be pulled directly from exchange rates APIs.
But whether this standard approach is the best strategy depends on the requirements of the exchange rates in the financial reporting systems.
For example, if 4 decimal places are used, to get the exchange rates on 4/17/2019 between USD and JPY, the API will return:JPY to USD: 0.
0089USD to JPY: 112.
0159However, If you calculate USD to JPY rate using JPY to USD rate yourself, you get:1 /0.
0089 = 112.
3596, which is different than the USD to JPY rate pulled directly from the API.
This obviously can be troublesome if your system expects to receive the original amount when currency A is converted to currency B, then back to currency A.
To deal with this issue, an alternative is to only pull exchange rates with a particular base currency — then derive other exchange rates yourself.
So if currency A is converted to currency B, then converted to currency C, and finally converted back to currency A, you still obtain the original (expected) amount.
Plan a Fallback strategyLike all online services, exchange rates APIs can sometimes be unavailable, so it’s important to implement a fallback strategy.
For example, you can use a different service provider to obtain exchange rates when the primary source is unavailable.
Ideally, the initial switch should be automatic and it should automatically revert to the primary source when available again.
Having a single place to pull exchange rates will make implementing the failover much simpler.
Split Architecture for Exchange Rate systemThe exchange rate system can be split into two parts by function: update flow and consumption flow.
The update flow allows the system to fetch the latest rates from an external exchange rate provider and update the rates in the system.
The consumption flow then manages and provides exchanges rates to internal online/offline services.
These two flows run independently and simultaneously.
Update FlowIn the update flow, the Airbnb exchange rate service triggers a request to the external exchange rate provider and fetches the latest rates.
Then, the rates will be tagged with a new version number and stored into the Exchange Rates table.
Once an exchange rate version is created, the exchange rates will never be changed.
They’re stored in the table as the “source of truth” for the system.
Consumption FlowOne of the goals of Airbnb’s internal exchange rate system is to ensure all internal services (online and offline) use a unified set of exchange rates.
The consumption flow design shows how this consistency is guaranteed.
In the consumption flow, the Airbnb Exchange Rate service manages the version and provides the latest exchange rates to online services for calculating the quote.
The exchange rate version is passed along and stored in each Transaction record.
Later when the offline Financial Reporting pipeline processes the transaction, the pipeline queries the Exchange Rates table to get the designated currencies in the specified version.
This way the system ensures that the exact same set of exchange rates are applied on the same transaction.