Multiple Currencies

Introduction

One of the most important points of a finance software is the proper handling of a different currencies. To enable openpetra to do the valid calculations the columns of the database table “a_currency” shall be enhanced. Therefore let us have a look onto the actual table referred in http://openpetraorg.sourceforge.net/dbdoc/ and the topic main.

This table manages the handling of the different money units in different countries.

1. a_currency_code_c –This defines an international code which describes the currency like USD for the us-American dollar or EUR for the European currency
2. a_currency_name_c – A free text string containing the name of the currency like “United States Dollar” or “European Money Unit”
3. a_currency_symbol_c – This is the symbol used to show a currency, e.g. $US or EUR
4. p_country_code_c – Name of the country the currency is used in like “United States” or “99” with a reference to the table p_country
5. a_display_format_c – The currency format used to display and to accept input currency (decimal values)
6. a_in_emu_l – Indicates whether currency is part of the european exchange rate mechanism/ European Monetary Union or not
7. s_date_created_d – The date the record was created
8. s_created_by_c – User ID of who created this record with a reference to the table s_user
9. s_date_modified_d – Date the record was modified
10. s_modified_by_c – User ID of who last modified this record with a reference to the table s_user
11. s_modification_id_c – This identifies the current version of the record

Remarks to the actual database table

1. a_currency_code_c – ok.
2. a_currency_name_c – This name is a free text and there may be the requirement to translate this column respective to get access to different language version at the same time. So this column shall be swapped out into a multilanguage table.
3. a_currency_symbol_c – The real symbols are $ and €. By changing from Courier formated reports to graphical reports we shall change this values too.
4. p_country_code_c – As seen in europe a currency is not used in a specific country. First thougt is to skip this column. Second thought: The value 99 is used for Europe but 99 is also used as a "error-default". So the 99 in this table shall be changed to something else.
5. a_display_format_c – Actually this are there are four different format strings which are developed for Progress. This string shall be changed to a .net compatible format string.
6. a_in_emu_l – In this case only the problem of the Euro and the european union is solved. But there are some other curreny unions like the East Caribbean dollar.
7. s_date_created_d – Not used and may be skipped
8. s_created_by_c – Not used and may be skipped
9. s_date_modified_d – Not used and may be skipped
10. s_modified_by_c – Not used and may be skipped
11. s_modification_id_c – Not used and may be skipped

Multiple Currency Basics

Different Accountancy Curreny Types

Openpetra knows three different currency types.

1. B is the base currency which shall be defined once at installation time and shall be used for the Lifetime of the accountancy. An Exception may be the change from "Deutsche Mark" to Euro but such changes are not handled in this document.
2. I is international currency and this actually is the US$. This currency can be changed at any time and the only result is a set of management reports producing different numbers and different currency names. No official reports like a tax report uses this currency.
3. T is a transaction currency which is different from B. The reason to handle such accounts is foremost a money cash in different currencies. A guest from England has paid a book in Germany using pounds or something else. Sometimes there may be a gift which is done in a non-B-currency.

More than 99% of all Transactions in an OM-office are B-transactions. Each Month the offices send its money to the different fields in the foreign countries and here a normal bank transaction is used. The source-office sends their money from a B-ledger, the bank changes this into the currency of the target-office and at this place it will be a B-Transaction too. No T-ledgers is involved in this process. So the T-ledgers are something like a millstone round the neck of this offices.

Common Mathematical Rules

Requirement: For all this transactions only the data type decimal is allowed.

Each national office has to manage its own book keeping process based on its own base currency or primary currency. Furthermore they can handle some assets defined in other currencies. In the very first step this is petty cash and this is the only form of foreign currencies which shall be managed by openpetra.

Requirement: Only one of the following transactions are allowed

1. primary currency to primary currency ledger (B2B-Transfers)
2. primary currency ledger to any secondary currency ledger and vice versa (T2B and B2T-Transfers)

The same Requirement is: Direct secondary currency to secondary currency transactions are forbidden (T2T-Transfers are forbidden)

For example in the German office, the primary currency is euro and the secondary may be the English pound and the Swiss franc. A transaction from the English pound to the Swiss franc is not allowed. This are two transactions.

In the finance market two different exchange rates are defined. In our example his is the rate for the exchange of the € to $ (exch€$) and this is the vice versa exchange rate to € (exch$€).

Requirement: In order to avoid rounding differences in a book keeping system holding the primary currency Euro, it is not allowed to calculate and to use the value of exch$€. Only the value of exch€$ is allowed to use. The value exch$€ is reserved for systems holding $ as its primary currency.

To be used:

amountIn$ = amountIn€ * exch€$ and

amountIn€ = amountIn$ / exch€$

Not to be used:

exch$€ = 1 / exch€$

amountIn€ = amountIn$ * exch$€

Requirement: A currency exchange rate has to be defined for each currency say exchPrimCurToSecCur

In the next step there exist a smallest currency value. In case of the euro and the dollar it is 1 cent, in case of CHF it is 0.05 cent.

Requirement: Any of this calculation shall be rounded to a multiple number of the smallest currency value after a division.

A german example may be the prices for fuel and one Liter may cost 1.329 Euro. The smallest currency value is 0.01 Euro and if you by 15 Liters, you have to pay 19.935 Euro which may be rounded to 19.94 Euro. This rounding is done before any other calculation.

If you want to refuel two cars in two refueling processes you have to round two times, only if you refuel the cars in one step, the refuelling is done one time

Requirement: Only rounded numbers are allowed to be added up.

Requirement: Ledgers which represents a cash value only can handle multiple whole number amount of the smallest currency value.

Actually you get 2,167.195,020 Russian Rubels for one euro. In this case you can define a multiplier to reduce the size of the number. For example you can set this value to 1,000.00 and then you can say that you get 2.167,195,02 (*1,000 Rubel) for one Euro.

Requirement: Each Currency shall handle it's own currency multiplier.

Exchange from primary currency to a secondary currency or vice versa

Let someone exchange some money of primary currency into money of secondary currency. Independently if he uses cash or a bank account, he gets a transaction report holding the data for

1. the money he has spent in primary currency
2. the relevant exchange rate
3. the sum of the exchange fee
4. the money he has got in secondary currency

The exchange fee shall be accounted against a ledger like administration costs or else.

But in normal cases there is a difference between the internal value of exchPrimCurToSecCur and the value used in the transaction report. So the result will be an exchange rate profit or an exchange rate loss.

Requirement: The accounting software shall have the option to define a ledger for the exchange rate profit and a ledger for the exchange rate loss.

Open Question: One ledger for each foreign currency or one ledger for all?

Requirement: The exchange rate profit or the exchange rate loss of this transaction shall be accounted simultaneously if an exchange transaction is accounted.

Accounting of expenses and incomes

If someone spent some foreign currency money he gets a record holding a value based in the foreign currency. The cash ledger is defined in the foreign currency and the offset ledger in primary currency.

In the first case the amount can be accounted directly.

In the second case the amount can shall be converted into primary currency by using the value of the actual exchange rate and rounding to a whole number multiple of the smallest currency value.

Requirement: Expenses and incomes shall be accounted by using the actual exchange rate rounded to a whole number multiple of the smallest currency value.

Currency update

The currency rate shall be updated each month manualy or automaticaly by an office staff member. Any update implicitly creates an exchange rate profit or an exchange rate loss.

Requirement: By updating the exchange rate the exchange rate profit or the exchange rate loss value shall be calculated and accounted automatically

TODO-List

Column Name	Status	ToDo
a_currency_code_c	Ok	Use unchanged
a_currency_name_c	Not Ok	Shall be prepared for International Language Support
a_currency_symbol_c	Not Ok	Change to correct currency symbols
p_country_code_c	Not Ok	Decide to remove or to repair the 99 for Europe
a_display_format_c	Not Ok	Update the format strings
a_in_emu_l	Not Ok	Change to the possibility to add or remove a currency to any other monetary union
s_date_created_d, s_created_by_c, s_date_modified_d, s_modified_by_c, s_modification_id_c	Not Ok	Shall be removed