Question about multiple currencies

[Charles Day]

On Wed, Oct 15, 2008 at 3:05 PM, Charles Day <cedayiv at gmail.com> wrote:

> On Wed, Oct 15, 2008 at 1:35 AM, John M Collins <jmc at xisl.com> wrote:
>
>>  Please CC to jmc AT xisl DOT com as I'm not subscribed.
>>
>> On Tue, 2008-10-14 at 19:35 -0700, Charles Day wrote:
>>
>> On Tue, Oct 14, 2008 at 8:05 AM, John M Collins <jmc at xisl.com> wrote:
>>
>>  Please CC to jmc AT xisl DOT com as I'm not subscribed.
>>
>> I'm having a bit of a problem with GNUcash in that the trial balance is
>> out of balance when various transactions are done in multiple
>> currencies.
>>
>> I've got most of the accounts in £ sterling with some in Euros and some
>> in US$
>>
>> I've been careful to account for sums in US$ including accounts
>> receivable etc. But the problem is when the bank converts between
>> currencies and I use "transfer" it lets me put in whatever rate the bank
>> has given but keeps all the other accounts showing the original
>> conversion rate. I think that something should be posted to a "currency
>> variations" account.
>>
>> I've ended up with a significant difference in the columns of the "trial
>> balance" account.
>>
>> Can anyone suggest how I can get the trial balance into order without
>> deleting all the transactions?
>>
>>
>> What is the reporting currency used for the trial balance report? (I
>> assume it's GBP.)
>>
>> Yes it is GBP
>>
>>  Once you buy USD and EUR, where do they go? Do they ever get changed
>> back into GBP or another currency?
>>
>> The EUR transactions were a little bit complicated as in the case of Spain
>> the customer withholds 10% which I then have to claim back out of my next
>> payment to the UK tax people. So we end up wrestling with:
>>
>>    1. The GBP/EUR rate when the invoice was raised.
>>    2. The rate when the customer pays 90%
>>    3. The rate when that is converted to GBP by the bank which is
>>    different even if done on the same day as 2.
>>    4. The rate when I do the tax return.
>>
>> Of course these are all different.
>>
>> I think the GBP/USD problems are limited to just one point back in August.
>> I'd kept all USD receipts in a $ bank account whilst the £ was sky-high
>> against the $. When the £ sunk against the dollar I transferred most of the
>> $ account to a £ account (of course I did that too early because the £ sank
>> a lot more - sigh!). I put in the transaction using "transfer" and gave the
>> rate that the bank converted it at which was different (worse of course -
>> and they charge a commission on top) from what Finance::Quote thought.
>>
>> I think that caused the accounts imbalance to arise as the value of all
>> the USD accounts were calculated at the value given by "Finance::Quote" but
>> the GBP bank account was credited by a different amount. A sum should have
>> been debited to a "currency conversions" account to make the accounts
>> balance GBP-wise.
>>
>> A more complicated version of the same thing happened with the EUR
>> accounts.
>>
>
> The imbalance is not caused by the exchange rate per se, but rather by not
> accounting for realized currency gains/losses. When you transfer from a
> non-GBP account into a GBP account, you are doing a currency sale and the
> realized currency gain needs to be accounted for. It is the same concept as
> when you sell a stock.  It would be nice if those entries could be helped
> along in some automated way, but presently they aren't.
>
> Just to illustrate the concept, try starting a brand new GnuCash file with
> two accounts: one in GBP and one in EUR. Change some GBP into EUR. The trial
> balance still balances. Now IN THE SAME REGISTER change all the EUR back to
> GBP at a different exchange rate without accounting for the gain/loss. The
> trial balance fails and the balance sheet will show the realized gain or
> loss as unrealized. Instead, what you want to see is a good trial balance
> and a balance sheet showing income from the currency gain/loss and no
> unrealized gain/loss.
>
> (Note: I say "in the same register" above because I have just discovered a
> bug in the reports that needs fixing. Unfortunately, that may be part of the
> imbalance you see!)
>

I have committed a fix for the reports in r17627. It may or may not make a
difference in your case. If you want to try it out, you can find the patch
here: http://svn.gnucash.org/trac/changeset/17627

You would just need to apply the patch to your installed copy of
commodity-utilities.scm (make a copy first) and restart GnuCash.


>
>>
>> Also, please look over the exchange rates in the Price Editor. Do you see
>> entries at all that have an exchange rate of exactly 1? If so, make a note
>> of the dates. On those dates you have at least one transaction with an
>> incorrect exchange rate, probably due to a bug. (Upgrading to 2.2.7 should
>> help prevent further errors.)
>>
>> Yes there were several bogus entries.
>>
>> I have downloaded and installed 2.2.7 and played with it but it seems
>> there is still a problem - which I suspect is a bug - in this area. If you
>> fill in the transfer with a specified amount that the bank actually
>> converted it for rather than the rate which Finance::Quote believes in you
>> will get an imbalance in the trial balance.
>>
>> I think the only way is to do the conversion at the current
>> "Finance::Quote" rate, see what that credits to the GBP bank account and
>> then put in an appropriate (GBP of course) transaction between the GBP bank
>> account and a "currency conversions to GBP" P&L account to make the net
>> amount credited to the GBP bank account agree with what the bank actually
>> credits.
>>
>> The problem I have now is how to get the trial balance report balancing.
>> It looks as if I'll have to delete all the USD/GBP and EUR/GBP transactions
>> and re-enter them in the way I've described.
>>
>
> You don't have to delete anything, but to get balanced books you may need
> to add entries to account for your realized currency gains or losses. Do you
> have any EUR or USD accounts that have a zero balance?
>
>
>> Or is there any better way? I'd be grateful if you could advise before I
>> do - thanks.
>>
>>
>>   John Collins Xi Software Ltd www.xisl.com
>>
>> Tel: +44 (0)1707 886110 (Direct)
>>      +44 (0)7799 113162 (Mobile)
>>
>> Registered in England & Wales Company Number 1977148 VAT: GB 403 9239 64
>>
>> Trading Address: 3 Mandeville Rise, Welwyn Garden City, Herts, AL8 7JT, UK
>> Reg Office: 2 Mill Road, Haverhill, Suffolk, CB9 8BD, UK
>>
>
> Cheers,
> Charles
>