Foreign Currency Transactions

Transactions between base currency account and base currency account are as trivial as transactions between foreign currency accounts of the same currency. The most complex process is how to move above the border.

If we change the currency exchnage rate not for a transaction but an existing account, we have a Revaluation.

An Example Transaction

The best way is to get a look onto a voucher I've got by changing some money. I beg your pardon for the bad quality but is was meant to be for private use only. That was no business money.

This voucher tells us: I have changed € 30.00 into £ 21.82. The exchange rate was 1.3752 and there was a fee of £ 2.50. I'm comming form Germany and so let us assume that the base currency of my accounting system is Euro.

In order to run this accounting problem properly open petra provides an account named "Internal Transfer" and the account number is 9800.

Let us assume that we have two accounts

1. 6000 Petty Cash Euro
2. 6001 Petty Cash British Pound

Then we have to create a batch containing a journal in EUR and a journal in GBP. Then we have to account:

1. 6000 Petty Cash € - Credit: € 30.00
2. 9800 Internal Transfer € - Debit: € 30.00

This moves the € 30.00 to the Transfer-Account and then

1. 9800 Internal Transfer € - Credit: £ 21.82
2. 6001 Petty Cash £ - Debit: £ 21.82

moves the "pound" to the £ Petty Chash Account.

The internal currency exchange rate 1.3752 is the value for the other direction because I've changed the money in England. So the correct value is 0.727167. Actually the precision which is used for that value is 4 if USD, EUR or CHF are exchanged but I've found a 7 digit value for the exchange of USD in JPY (1.00 JPY[Japan Yen] = 0.0121800 USD).

The maximal precision defined by the openpetra data base is 10 digits and so I prefer to use this precision as default. I the future this value may be overridable by currency dependent values but not now.

Let us start only by the exchange itself - no exchange fees are accounted - will result in the following data base entries.

In this cases it is important to store the value of a_transaction_amount_n in the precision defined by the currency. This is done automatically. In case of switzerland the smalles coin is CHF 0.05 and so you cannot get only a multiple of it. No internal roundings are necessary.

Requirement: Do not round transaction amount values in any way

The next step is to calculate the accouting value base on the exchange rate and in our case this is the value of EUR 30.0068622476. Using the correct mathematical rounding rules I should have to pay EUR 30.01 for the GBP 21.82.

Now GBP 21.82 is somewhere in a petty cash and EUR 30.01 is the value which is used by the accounting system to rate this money in base currency value. So the value of € 0.01 has to be additionaly accounted. The best way is to interpret this as the very first revaluation.

So the complete accounting set in the EUR-Journal are the following transactions:

1. 6000 Petty Cash € - Credit: € 30.00
2. 9800 Internal Transfer € - Debit: € 30.00
3. 5003 Currency Revaluation - Credit: € 0.01
4. 9800 Internal Transfer € - Debit: € 0.01

and replieng the GBP-Journal

1. 9800 Internal Transfer € - Credit: £ 21.82
2. 6001 Petty Cash £ - Debit: £ 21.82

Rounding the calculated base currency values

The next question is how to round the value of a_amount_in_base_currency_n. Refering to different literatur in the web, the first preliminary answer is: Do not round base amount values in any way

But this is wrong: Let us assume, that three persons have changed some money. For simplicity let us assume that they have got exact the same exchange conditions only on three different days. This will result in three identical batches - only different dates and different cost centres. The cost centre numbers are marked with c.

1. Edith - c9000 - EUR 30,-
2. Charly - c9001 - EUR 30,-
3. Mary - c9002 - EUR 30,-

If you have to produce a detailed report, then you have to produce the output ...

Person	Cost Centre	Foreign currency	Base currency	Exact Base currency
Edith	c9000	21.82	30.01	30.0068622476
Charly	c9001	21.82	30.01	30.0068622476
Mary	c9002	21.82	30.01	30.0068622476
All	.	65.46	90.02	90.0205867428

This means: The correct base currency value of GBP 21.82 is EUR 30.01, but if this has been accounted three times, the value of the sum is not EUR 90.03 but EUR 90.02.

Furthermore: The problem arises after the second accountig process, not the third. So if you store precise data (e.g. 10 digits) you have the problem that an accidental need of a revaluation will get necessary after the accounting has been done.

The only way to leave this trap is to store the data in the precision which is defined by the base curreny and this means 2 digits for USD, GBP and EUR and 0 digits for JPY.

In this case you have to calculate the balance sheets by adding the base currency and the foreigen currency values independently of the each others and you must not run any transaction calculates using the sums. In our case the accounting value of GBP 65.46 based on one accounting process is valued by EUR 90.02 and Edith, Charly and Mary (three accounting processes) may have EUR 30.01 each and the sum in a balance report is EUR 30.03.

Furthermore let us assume that Edith will change EUR 30 three times at three different days. This will result in GBP 3*21.82 and EUR 3*30.01. This problem easily can be resolved in the revaluation at the end of the actual period as shown below.

Getting the correct number of digits

Actually there is one efficient way to get the information about the maximal precision of a currency. The table a_leder provides the column a_country_code_c. The value in this column shall be used to create a .net CultureInfo class. Acutually the values are two character codes for example "DE".

               try
               {
                   numberFormatInfo =
                       new System.Globalization.CultureInfo(
                           ledger.CountryCode, false).NumberFormat;
               }
               catch (System.NotSupportedException)
               {
                   numberFormatInfo =
                       new System.Globalization.CultureInfo(
                           String.Empty, false).NumberFormat;
               }

If the countryCode is "DE" then the NotSupportedException will be thrown. You realy need a code in the syntax <languagecode2>-<country/regioncode2> where the two digits for the language code are based on ISO 639-1 and the regicode is based on ISO 3166. Then numberFormatInfo.NumberDecimalDigits holds the correct value.

TODO: We have to investigate if an automatic conversion of this values may be necessary.

The CHF 0.05 Problem

In Switzerland the smallest coin is CHF 0.05. This my yield to the idea that the base currency values shall be rounded up to a multiply of CHF 0.05 and not CHF 0.01. But the real money which is physical availiable is GBP 21.82. The EUR value or in case of Switzerland the CHF value is only a valuation of the money.

The Revaluation at the End of the Month

Revaluation Accounts

In Openpetra (or better in all types of accounting software) there exists the account types

1. Income
2. Expense
3. Asset
4. Equity and
5. Liability

Here accounts of the type Income and Expense will never be revaluated. This is money which was earned or spend and no one will change the value of a paid and accounted bill because an exchange rate has changed.

The only accounts for revaluation are Asset, Equity and Liability.

Asset is the case of petty cash and this is clear. Money in the currency of GBP change its value if the value shall be defined in EUR or USD. So Equity and Liability are left. Equity for example can hold a set of stocks based in GBP and so the value may change to from month to month. But in German you have to run a revaluation only after you have sold it. So depending of different laws there may exist an enhances ruleset for the revaluation. I've asked for in the the forum and as long as we get no answer and as long nobody cries this accounts will be revaluated in the same process.

So the revaluation shall be done on accounts of the types (of course only the foreign currency types):

1. Asset
2. Equity and
3. Liability