Multi-Currency Best Practice & Implementation

Question

I'm finding it difficult to find any discussion on best practices for dealing with multiple currencies. Can anyone provide some insight or links to help?

I understand there are a number of ways to do this - either transactionally where you store the value entered as is, or functionally where you convert to a base rate. In both cases the exchange rate is needed to be stored that covers that transactions time for each currency that it may need to be converted to in the future.

I like the flexibility of the transactional approach, which allows old exchange rate info to be entered at a later date, but probably has more overhead (as you have to store more exchange rate data) than the functional approach.

Performance & Scalability are major factors. We have (all .net) a win & web client, a reports suite and a set of web services that provide functionality to a database back-end. I can cache the exchange rate information somewhere (e.g. on client) if required.

EDIT: I would really like links to some documents, or answers that include 'gotchas' from previous experience.

Answer 1

Since exchange rates fluctuate, one approach is as you mentioned - store an "entered as is" amount that is not converted but display a companion field which is display only and shows the converted amount. In order to do the conversion, a table of exchange rates and their applicable date ranges would be required. If the size of this is small, caching on the client is an option. Otherwise, a remote call would be required in order to perform the conversion.

Answer 2

(I'm assuming you already know that you definitely shouldn't store currency data as float and why)

In my opinion, working with a single base currency might be easier; however, you should save the original amount, original currency, conversion rate, and base currency amount - otherwise your Accounting dept. might eat you alive, as they're likely to keep different currencies sort of separately.

Answer 3

There is no single answer, because it very much depends on the way a business handles the transactions in those currencies. Some companies use fairly sophisticated ways to manage foreign currencies. I suggest you read up on multi-currency accounting.

The main thing to do is to capture the data in the unit, value & date in which the business transaction is done without any conversion, or you risk losing something in translation. For display & reporting, convert on demand, using either the original exchange rate, or any other exchange rate depending on the intent of the user.

Store & compute with values as the 'Decimal' (in C#) type - don't use float/double or you leave yourself vulnerable to rounding errors.

For instance, the way I did a multi currency app in a previous life was:

• Every day, the exchange rates for the day would be set and this got stored in a database and cached for conversion in the application.
• All transactions would be captured as value + currency + date (ie. no conversion)
• Displaying the transaction in a users' currency was done on the fly. Make it clear this is not the transaction currency, but a display currency. This is similar to a credit card statement when you've gone on holiday. It shows the foreign transaction amount and then how much it ends up costing you in your native currency.

Answer 4

Our company deals with multiple currencies accounting and budgeting. The solution we implemented is quite straight-forward, and includes the following:

1. one currency table, with a few fields including numbers of decimals to be considered for the currency (yes, some currencies have to be managed with 3 decimals ...) and a exchange rate value, which has no other meaning than being an 'proposed/default exchange rate' when evaluating 'non-executed' or 'pending' financial transactions (see infra)

2. In this currency table, one of the records has an exchange rate of 1. This is the main/pivot currency in our system

All financial transactions, or all operations with a financial dimension (what we call commitments in our language), are either sorted as 'pending' or 'executed':

1. Pending transactions are for example invoices that are expected to be received for a certain amount at a certain date. In our budget follow-up system, these amounts are always reevaluated according to the 'proposed/default exchange rate' available in the currency table.

2. Executed transactions are always saved with the execution date, amount, currency AND exchange rate, which has to be confirmed/typed in when entering the execution data.

Answer 5

I couldn't find any definitive discussion, so I post my findings, I hope it helps someone.

The currency table should include the culture code to make use of any Globalisation Classes.

Transactional Method

• Store in currency local to customer and store multiple conversion rates for the transaction currency that applied when the transaction occurred.
• Requires multiple exchange rates for each currency
• Site Settings table would store the input currency
• Input & Output of values at client level would have no overhead as it can be assumed the value is in the correct currency
• To apply exchange rates, you would need to know the currency of the entered values (which may be different for cross client reports), then multiply this by its associated entity exchange rate that was valid during the transactions time period.

Functional Method

• Store in one base currency, hold conversion rates for this currency that apply over time
• Consideration needs to be given at point between front end and database is the best place to convert values
• Input performance is marginally affected as a conversion to the base currency would need to take place. Exchange rate could be cached on the client (note each entity may use a different exchange rate)
• This required one set of exchange rates (from base to all other required currencies)
• To apply exchange rates, every transaction would need to be converted between the base and required currencies

Composite

• At point of transaction, store transactional value and functional value, that way no exchange rate information would need to be stored. (This would not be suitable a solution as it effectively restricts you to two currencies for any given value)

Comparison

Realistically, you have to choose between function and transactional methods. Both have their advantages & disadvantages.

Functional method does not need to store local currency for transaction, needs to convert current db values to base currency, only needs one set of exchange rates, is slightly harder to implement and maintain though requires less storage.

Transactions method is much more flexible, though it does require more exchange rate information to be held and each transaction needs to be associated with an input currency (though this can be applied to a group of customers rather than each transaction). It would generally not affect code already in production as local currencies would still be used at the local level making this solution easy to implement and maintain. Though obviously any reports or values needing to be converted to a different currency would be affected.

In both cases, each transaction would need exchange rates for the time of transaction for each currency it needs converting to – this is needed at point of transaction for functional method, however the transactional method allows more flexibility as past exchange rate data could be entered at any time (allowing any currency to be used), i.e. you lose the ability to use other exchange rates in the functional method.

Conclusion

A transactional method of currency management would provide a flexible approach, avoiding any negative impact on client performance and zero client code modification. A negative performance impact would likely occur in reports where all will need rework if different currencies are required. Each client site will need to store a currency reference that states what their input currency is. It should be possible to get away with storing exchange rates at a high level (e.g. a group of customer sites etc), this will minimise the amount of data stored. Problems may occur if exchange rate information is required at a lower level.