1 Topic by jonathan46 (edited by jonathan46 08/24/2013 02:00:52 pm)

  • From: UK, Uxbridge
  • Registered: 09/23/2011
  • Posts: 41

Topic: Incorrect application of exchange rate

Version 2.3.17 with new customer_payments.php and supplier_payment.php installed.

When entering French sales I enter direct invoices in Euros, and then the customer payment, all with the same exchange rate (our currency is GBP)

The Customer balance report shows the payment much too large, and the extra is entered as foreign exchange gain.

The Error is as if the conversion was done dividing by the rate instead of multiplying.

Is this my set up?

This is probably not significant, but when I go Sales>Customer payments the button at the bottom says "Update payment", but when I go Sales>Customer transaction Enquiry and edit an entry the button at the bottom says "Add payment". I would expect them to be the other way round.

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,106

Re: Incorrect application of exchange rate

This is a bug that has been fixed in the repo.

Please download the fixed files here

Joe

  • From: UK, Uxbridge
  • Registered: 09/23/2011
  • Posts: 41

Re: Incorrect application of exchange rate

Joe,

Thanks for the quick reply, but I installed the two files yesterday. As I said at the beginning.

So the problem remains.

Jonathan

  • Registered: 12/25/2007
  • Posts: 2,448

Re: Incorrect application of exchange rate

Is there any exchange variance posted in GL related to Customer Payment?
Janusz

5 Reply by jonathan46 (edited by jonathan46 08/25/2013 02:52:11 pm)

  • From: UK, Uxbridge
  • Registered: 09/23/2011
  • Posts: 41

Re: Incorrect application of exchange rate

Janus,

If the foreign customer payment is not related to in invoice the customer balance increases by an amount as if  the exchange rate is applied the wrong way round. There are no foreign exchange gains or losses.

When a direct invoice, or an order- dispatch-invoice is entered there are no foreign exchange entries.

When a payment is made, directly for an invoice, or by allocating from the customer balance (which is artificially high) then the foreign exchange entries appear. They are almost twice the difference between the euro sum and the Pound equivalent.

All the above is done with no change in rate between the actions, and no bank charges.

I wondered if the wrong value was being sent from the payment form, but the problem is still there when the allocate payment form is used.

Hope this helps

Jonathan

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,106

Re: Incorrect application of exchange rate

Hello again.

Janusz has fixed the problem and the repo is updated. You can download the file fix here

Joe

  • From: UK, Uxbridge
  • Registered: 09/23/2011
  • Posts: 41

Re: Incorrect application of exchange rate

Joe and Janusz,

Problem solved, very quickly, and over the bank holiday weekend.

Many thanks
Jonathan

  • From: UK, Uxbridge
  • Registered: 09/23/2011
  • Posts: 41

Re: Incorrect application of exchange rate

My mistake, NOT SOLVED

The problem is half way to being solved.

A payment now results in the custommer's balance ( in pounds, our currency ) being credited with the euro figure, unconverted. This is not as bad as it was, ie converted in the wrong direction.

So now the sums in the foreign exchange gain are half what they were, but should be zero.


Jonathan

  • Registered: 12/25/2007
  • Posts: 2,448

Re: Incorrect application of exchange rate

Oh, yes you are right. There is another bug in the Customer Balances report (it appears only when 'No currency filter' is selected).
I will fix it asap.
Janusz

10 Reply by jonathan46 (edited by jonathan46 08/29/2013 06:45:07 pm)

  • From: UK, Uxbridge
  • Registered: 09/23/2011
  • Posts: 41

Re: Incorrect application of exchange rate

True, the customer balance report makes sence when I select Euros.
True , it is wrong when no currency filter is used,

But

It's not just the Customer Balance report, because when the payment is allocated the euro value is used instead of pounds, and the difference is recorded as Foreign Exchange gain.

Jonathan

  • Registered: 12/25/2007
  • Posts: 2,448

Re: Incorrect application of exchange rate

The Customer Balances and Supplier Balances reports are fixed in mercurial.
But I cannot reproduce the problem problem with the amounts recalculation. When the EUR and GBP amounts are not selected exactly as they would result from exchange rate stored in application, you will always end with some posting to Foreign Exchange Gain GL account. If you are sure there is still some bug here, please provide exact scenario step by step, with all currency details.

Janusz

12 Reply by jonathan46 (edited by jonathan46 08/30/2013 01:27:18 pm)

  • From: UK, Uxbridge
  • Registered: 09/23/2011
  • Posts: 41

Re: Incorrect application of exchange rate

Home currency GBP,  customer currency EUR, exchange rate 0.8 fixed, vat 20%.

Take a sale of 120 euro, = £96. Payment of 120 euro = £96. (no exchange gain)

Invoice ...... 100 EUR + 20EURvat = 120 euro in all. This is shown on the invoice correctly.

G/L  4000     Sales                                                 80.00    
       1100      Debtors Control Account           96.00        
       2200      VAT (20%)                                         16.00    
                          Total                                 96.00        96.00    
This is correct, in GBP.
The G/L for Delivery is also correct , and in GBP

Customer payment  = 120 EUR      96 GBP

G/L   1200      Bank Current Account            96.00        
         1100     Debtors Control Account                          96.00    
         1100     Debtors Control Account             24.00                 Sales Invoice 5
         4920      Foreign Exchange Gain                          24.00     Sales Invoice 5
                                    Total                         120.00      120.00    

Here £120 has come out of the Debtors account, £96 to the Bank, and £24 to Exchange gain.  (120 was the Euro amount paid in)

Summary:
Invoice         £96  ... 120 euro
payment       £96  ... 120 euro
Allocation    £120 ...  150 euro

The final result is £24 Foreign exchange gain, and £24 deficit in the Debtors control account, where both should be nill.
The payment has allocated £120, not £96.

  • From: UK, Uxbridge
  • Registered: 09/23/2011
  • Posts: 41

Re: Incorrect application of exchange rate

Janusz

I think payment_db.inc line 49 is wrong.

$bank_amount = $amount/$rate;     should be        $bank_amount = $amount*$rate;

This does not cure the problem, because as the comment says it is not used here, but somewhere a similar mistake is causing my problem.

Another anomaly I have noticed, which probably is insignificant:
The Customer Payment  Entry form ends with the button "Update payment"
When modifying a customer payment the button reads "Add Payment"
Something has bee swapped round.

Jonathan

  • Registered: 12/25/2007
  • Posts: 2,448

Re: Incorrect application of exchange rate

Yes, you are right again. I have finally found the original problem. Simply the calculated exchange rate should not be stored in customer Payment record (bank to home currency is stored here, and not bank to customer currency exrate; this is done later in the code).
I have run a couple of tests covering various currencies combinations on the write_customer_payment function, and now all looks OK (repo updated).
Thank you very much for your help smile.

Janusz

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,106

Re: Incorrect application of exchange rate

Please download the fixed file here .

Joe

  • From: UK, Uxbridge
  • Registered: 09/23/2011
  • Posts: 41

Re: Incorrect application of exchange rate

Janusz and Joe,

Thanks, I've tried it with customer payments, and supplier payments and not a foreign exchange gain in sight.


Jonathan

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,150

Re: Incorrect application of exchange rate

Further changes done.

Last Git commit here.

apmuthu's Website