[GNC] Rounding discrepancy in payment processing [SOLVED]

Tue Dec 27 10:43:32 EST 2022

Thanks Alaa for the detailed testing and sharing the same with all of us, it is really helpfull.  The decimal places , i was aware of as i use multi currencies and the fraction column.  Neverthless i learnt from you the setting in the account can affect the whole rounding, which i always leave it as commodity. thanks for the same

Really happy you were able to debug the issue and solve the same, happy holidays

Saludos Cordiales

Murugan

________________________________
From: Alaa Zeineldine <alaa at expertwell.com>
Sent: Monday, December 26, 2022 11:30 PM
To: 'Murugan Muruganandam' <m.muruganandam at hotmail.com>
Cc: gnucash-user at gnucash.org <gnucash-user at gnucash.org>
Subject: RE: [GNC] Rounding discrepancy in payment processing [SOLVED]

Hello Murugan,

(This issue was resolved)

I did a lot of research and played with a number of test case combinations. Happily, all the issues were resolved by correcting a couple of settings. Below are some details for the benefit of anyone who may encounter similar issues.

A. Japanese Yen. Let's first get this subtle issue out of the way. That's the issue of rounding non-default currency values, which you reproduced when you used a Japanese Yen client for testing. It turns out that the JPY currency is fraction-less by definition. All JPY values have to be whole numbers. So, rounding here is required and it appears to override other settings for fractions. This is reflected in the JPY entry under the "Security Editor", where the Fraction column is set to 1. It seems that Gnucash has this built-in somehow. You may have been unfortunate to choose JPY as the currency for your sample customer, leading you in the wrong direction. But as a result, we also learned an important fact. So, it was fortunate after all.

B. Rounding discrepancies in general.

The main issue I originally reported was unexpected rounding of invoice amounts; this led to discrepancies in payment processing and A/R Aging reports.

Here is what I found:

*  The problem was caused by an incorrect setting of the “Smallest Fraction” in the “Edit Account” settings (Accounts>Edit Account>Smallest Fraction).

During initial setup, I had selected "1" as the smallest fraction for A/R accounts. This was several years ago when I switched from QuickBooks to Gnucash.  I was following the QuickBooks convention where the number of decimal places was expressed as “one” and “two”.

*  This setting was the problem because, in Gnucash, setting “1” indicates "whole number" not “one decimal place”. Decimal places in GC account setting are expressed as 1/10, 1/100, 1/1000, etc. Alternatively, the recommended (default) setting is "Use Commodity Value", which will set the smallest fraction to the built-in value as displayed in the Security Editor. (The term “Commodity” put me off in the beginning, as I am a freelancer who uses Accounts Receivable for invoicing my clients and had nothing to do with commodities. I had not realized what the term meant in Gnucash then).

* Now, I tried setting the Smallest Fraction to 1/100 and to “use Commodity Value” in two different tests. In both cases, the problem was resolved, and I was getting accurate invoice values with fractions for Payment Processing and A/R Aging, and for all other tasks for that matter. No spurious prepayments were created as was the case before.

* To clear discrepancies with previously completed invoice/payment pairs, I ran  “Check & Repair all” on the A/R account (Actions>Check & Repair>All transactions). I followed this by running an A/R Aging report, and all previous rounding, discrepancies and spurious prepayments were cleared. The Aging report now exactly reflected outstanding customer invoices.

C. Discrepancies with non-default currencies

* In addition to the default USD, I have EUR and GBP accounts. While the above fix for the rounding issue also worked for those currencies, their A/R accounts still showed strange small and large imbalances.

* By further research, I realized that to work with multiple currencies, I had to activate “Use Trading Accounts”. This was another term which I always skipped because my business activities did not include trading. Once again, I did not realize the meaning of the term in Gnucash, which I suppose considers currency conversion between accounts of different currencies a form of trading. That’s fair although not intuitive (perhaps it is for an accountant).

* So, I activated Trading Accounts (File>Properties>check “Use Trading Accounts”) and also ran “Check & Repair All transactions” on the EUR and GBP A/R accounts. And voilà, all discrepancies were cleared and the A/R Aging report for those accounts was completely accurate.

* Note:  With “Use Trading Accounts” activated, two additional splits appeared for each non-default currency transaction. The accounts for these splits were “Trading:CURRENCY:<currency symbol>”, e.g. “Trading:CURRENCY:EUR” and “Trading:CURRENCY:USD” for a EUR account. These seem to account for the currency conversion between the non-default currency and the default currency. The simplest way to understand how this works is to look at the register with “Transaction Journal set”.

Note: The above does not explain why the spurious prepayments did not appear with Gnucash V4.6 and earlier. With V4.6, when I also had “Smallest Fraction” set to “1”, the rounding did occur, but prepayments resulting from the fractional differences did not appear. It seems that some change applied from V4.7 onward exposed this issue; maybe a correction to the way fractions were handled or something else. For me, this does not matter, as my purpose was to be able to upgrade to the current version with my books in good order. That is possible now with these corrected settings.

Summary:

1. Rounding was caused by setting “Smallest Fraction” to “1”. Unless whole numbers are required, this should be set to “Use Commodity Value” or 1/10, 1/100, etc., depending on the number of decimal places required. (Accounts>Edit Account>Smallest fraction).

2. For multiple currencies, “Use Trading Accounts” must be activated. (File>Properties>check “Use Trading Accounts”)

3. Applying “Actions>Check & Repair>All Transactions” on the relevant A/R accounts may be needed to fix previous discrepancies.

4. Some currencies, like JPY, have zero decimal places and will always be rounded. The Security Editor lists those with a Fraction value of “1”. (Tools>Security Editor>Check “Show National Currencies”>Expand Currencies>Click the Fraction column to sort on the Fraction value”. They can also be looked up here “ISO 4217 - Wikipedia<https://en.wikipedia.org/wiki/ISO_4217#Active_codes>”.

I hope this helps.

Alaa

-----Original Message-----
From: gnucash-user <gnucash-user-bounces+alaa=expertwell.com at gnucash.org> On Behalf Of Alaa Zeineldine
Sent: Sunday, December 25, 2022 2:50 AM
To: 'Murugan Muruganandam' <m.muruganandam at hotmail.com>
Cc: gnucash-user at gnucash.org
Subject: Re: [GNC] Rounding discrepancy in payment processing

Hello Murugan,.

Removing data from the live setup may not be trivial, it’s several years’ worth of transactions, 30 customers and several checking and PayPal accounts in three currencies.

The good news is that I am playing with a number of test cases and may have pinpointed the problem with default dollar accounts. I will post my conclusion when I have more definitive results. But it may be an error on my part.

If these findings are confirmed, then my problem will have been resolved for the majority of my transactions which are dollar based. However, discrepancies with the non-dollar accounts will still be an issue. Which brings me to a question. For multiple currencies, should I activate “Trading Accounts”?

Regards,

Alaa Zeineldine

From: Murugan Muruganandam <m.muruganandam at hotmail.com<mailto:m.muruganandam at hotmail.com>>

Sent: Friday, December 23, 2022 10:15 PM

To: Alaa Zeineldine <alaa at expertwell.com<mailto:alaa at expertwell.com>>

Subject: Re: [GNC] Rounding discrepancy in payment processing

hi Alaa

I am not able to simulate your case, unless i can access your setup. is it possible to send me without data your actual setup?

Saludos Cordiales

Murugan

  _____

From: Alaa Zeineldine <alaa at expertwell.com <mailto:alaa at expertwell.com<mailto:alaa at expertwell.com%20%3cmailto:alaa at expertwell.com>> >

Sent: Thursday, December 22, 2022 8:37 PM

To: 'Murugan Muruganandam' <m.muruganandam at hotmail.com <mailto:m.muruganandam at hotmail.com<mailto:m.muruganandam at hotmail.com%20%3cmailto:m.muruganandam at hotmail.com>> >

Cc: gnucash-user at gnucash.org<mailto:gnucash-user at gnucash.org> <mailto:gnucash-user at gnucash.org>  <gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org<mailto:gnucash-user at gnucash.org%20%3cmailto:gnucash-user at gnucash.org>> >

Subject: RE: [GNC] Rounding discrepancy in payment processing

Hello Murugan,

On my live setup rounding happens for all currencies, including the default currency. That is the problem for which I started this thread in the beginning. In addition, this rounding leads to discrepancies in payment processing.

Regards,

Alaa

From: Murugan Muruganandam <m.muruganandam at hotmail.com <mailto:m.muruganandam at hotmail.com<mailto:m.muruganandam at hotmail.com%20%3cmailto:m.muruganandam at hotmail.com>> >

Sent: Thursday, December 22, 2022 3:01 PM

To: Alaa Zeineldine <alaa at expertwell.com <mailto:alaa at expertwell.com<mailto:alaa at expertwell.com%20%3cmailto:alaa at expertwell.com>> >

Cc: gnucash-user at gnucash.org<mailto:gnucash-user at gnucash.org> <mailto:gnucash-user at gnucash.org>

Subject: Re: [GNC] Rounding discrepancy in payment processing

hi Alaa

this is the behaviour i also observerd, so the rounding is controlled by the currency of the customer. so probably you can try a new customer in your existing install and check

Saludos Cordiales

Murugan

  _____

From: Alaa Zeineldine <alaa at expertwell.com <mailto:alaa at expertwell.com<mailto:alaa at expertwell.com%20%3cmailto:alaa at expertwell.com>> >

Sent: Wednesday, December 21, 2022 5:05 PM

To: 'Murugan Muruganandam' <m.muruganandam at hotmail.com<mailto:m.muruganandam at hotmail.com>>

Cc: gnucash-user at gnucash.org<mailto:gnucash-user at gnucash.org> <mailto:gnucash-user at gnucash.org>  <gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org<mailto:gnucash-user at gnucash.org%20%3cmailto:gnucash-user at gnucash.org>> >

Subject: RE: [GNC] Rounding discrepancy in payment processing

Hello Murugan,

I went ahead and created a Yen A/R account under the default A/R account. I then added an invoice for your Yen based Customer 1, with a value of 236.78. The value of this invoice is rounded up in the totals and the A/R Aging report. On the other hand, values of the dollar-based customers are not affected.

I also added an invoice to a dollar client. No rounding or discrepancies exhibited in this case.

This is closer to the behavior of my actual live setup, except that in my setup rounding happens for all clients, including dollar clients.

The resulting test .gnucash file is attached for your perusal.

Regards,

Alaa

-----Original Message-----

From: gnucash-user <gnucash-user-bounces+alaa=expertwell.com at gnucash.org <mailto:gnucash-user-bounces+alaa=expertwell.com at gnucash.org<mailto:gnucash-user-bounces+alaa=expertwell.com at gnucash.org%20%3cmailto:gnucash-user-bounces+alaa=expertwell.com at gnucash.org>> > On Behalf Of Alaa Zeineldine

Sent: Wednesday, December 21, 2022 4:28 PM

To: 'Murugan Muruganandam' <m.muruganandam at hotmail.com <mailto:m.muruganandam at hotmail.com<mailto:m.muruganandam at hotmail.com%20%3cmailto:m.muruganandam at hotmail.com>> >

Cc: gnucash-user at gnucash.org<mailto:gnucash-user at gnucash.org> <mailto:gnucash-user at gnucash.org>

Subject: Re: [GNC] Rounding discrepancy in payment processing

Hello Murugan,

Thanks. I opened the file you sent and saw the new customer with the new invoice you created, which seems to be empty.

I think the problems begin when an invoice with an amount is created. In this case, GC will require an A/R account with the same currency as the customer. That is when discrepancies begin to appear.

I have a question before I proceed with adding test invoices. In my original live setup, I have the non-dollar A/R accounts as children of the dollar (default) A/R account, so that the parent account always shows the total A/R amount.

• My question is whether this is the correct setup, and if not, where should the A/R accounts for the secondary currencies be placed?

Regards,

Alaa

From: Murugan Muruganandam <m.muruganandam at hotmail.com <mailto:m.muruganandam at hotmail.com<mailto:m.muruganandam at hotmail.com%20%3cmailto:m.muruganandam at hotmail.com>> >

Sent: Wednesday, December 21, 2022 3:41 PM

To: Alaa Zeineldine <alaa at expertwell.com <mailto:alaa at expertwell.com<mailto:alaa at expertwell.com%20%3cmailto:alaa at expertwell.com>> >

Subject: Re: [GNC] Rounding discrepancy in payment processing

hi Alaa

i have created a customer called customer 1 with their currency as Yen.  i have created a invoice also for your reference. you can try creating new invoices with franctions for them and you can see the rounding

Saludos Cordiales

Murugan

  _____

From: Alaa Zeineldine <alaa at expertwell.com <mailto:alaa at expertwell.com<mailto:alaa at expertwell.com%20%3cmailto:alaa at expertwell.com>> >

Sent: Tuesday, December 20, 2022 9:27 PM

To: 'Murugan Muruganandam' <m.muruganandam at hotmail.com <mailto:m.muruganandam at hotmail.com<mailto:m.muruganandam at hotmail.com%20%3cmailto:m.muruganandam at hotmail.com>> >

Subject: RE: [GNC] Rounding discrepancy in payment processing

Hello Murugan,

I did lookup a number of customers, each of which had a USD currency, and their invoices were also USD, but rounding still occurs for them.

Can you elaborate on what you mean by “a customer currency with no fractions”?

Regards,

Alaa

From: Murugan Muruganandam <m.muruganandam at hotmail.com <mailto:m.muruganandam at hotmail.com<mailto:m.muruganandam at hotmail.com%20%3cmailto:m.muruganandam at hotmail.com>> >

Sent: Wednesday, December 21, 2022 1:58 AM

To: Alaa Zeineldine <alaa at expertwell.com <mailto:alaa at expertwell.com<mailto:alaa at expertwell.com%20%3cmailto:alaa at expertwell.com>> >

Subject: Re: [GNC] Rounding discrepancy in payment processing

Alaa

The rounding occurs based on the customer currency , so if your base currency and customer currency are USD , it will not have any problem , but in case you have a customer currency with no fractions , even when you create USD invoice it will round it ,

Can you look up one customer and tell me the currency of the customer and invoice currency

Regard

Murugan

On 20-12-2022, at 18:22, Alaa Zeineldine <alaa at expertwell.com <mailto:alaa at expertwell.com<mailto:alaa at expertwell.com%20%3cmailto:alaa at expertwell.com>> > wrote:

Hello Murugan,

Thanks for looking into this. I checked a number of customers, and each shows the currency that was entered when I created the customer initially. I also checked a number of invoices and found that the currency for the invoice was consistent with that of the customer.

Please note the reply to David Kirkby that I sent to the list. I created a minimal test .gnucash file with only one currency and tried a number of experiments with it. This did not exhibit the problem. This of course is much simpler than my real setup where I have clients with different currencies, and also bank accounts with different currencies.

Regards,

Alaa

From: Murugan Muruganandam <m.muruganandam at hotmail.com <mailto:m.muruganandam at hotmail.com<mailto:m.muruganandam at hotmail.com%20%3cmailto:m.muruganandam at hotmail.com>> >

Sent: Tuesday, December 20, 2022 9:25 PM

To: Alaa Zeineldine <alaa at expertwell.com <mailto:alaa at expertwell.com<mailto:alaa at expertwell.com%20%3cmailto:alaa at expertwell.com>> >

Subject: Re: [GNC] Rounding discrepancy in payment processing

hi Alaa

Can you please do a quick check

1.      find customer

2.      view/edit

3.      Billing information

4.      What currency is being shown in this .

i was able to recreate the issue in a test file and i think this could be the issue.

Saludos Cordiales

Murugan

  _____

From: gnucash-user <gnucash-user-bounces+m.muruganandam=hotmail.com at gnucash.org <mailto:gnucash-user-bounces+m.muruganandam=hotmail.com at gnucash.org<mailto:gnucash-user-bounces+m.muruganandam=hotmail.com at gnucash.org%20%3cmailto:gnucash-user-bounces+m.muruganandam=hotmail.com at gnucash.org>> > on behalf of Alaa Zeineldine <alaa at expertwell.com <mailto:alaa at expertwell.com<mailto:alaa at expertwell.com%20%3cmailto:alaa at expertwell.com>> >

Sent: Tuesday, December 20, 2022 8:21 AM

To: 'Dr. David Kirkby' <drkirkby at kirkbymicrowave.co.uk <mailto:drkirkby at kirkbymicrowave.co.uk<mailto:drkirkby at kirkbymicrowave.co.uk%20%3cmailto:drkirkby at kirkbymicrowave.co.uk>> >

Cc: gnucash-user at gnucash.org<mailto:gnucash-user at gnucash.org> <mailto:gnucash-user at gnucash.org>  <mailto:gnucash-user at gnucash.org>  <gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org<mailto:gnucash-user at gnucash.org%20%3cmailto:gnucash-user at gnucash.org>> >

Subject: Re: [GNC] Rounding discrepancy in payment processing

Hello Dave,

Sounds reasonable. I will do this as soon as I get some free time this evening.

Regards,

Alaa

From: Dr. David Kirkby <drkirkby at kirkbymicrowave.co.uk <mailto:drkirkby at kirkbymicrowave.co.uk<mailto:drkirkby at kirkbymicrowave.co.uk%20%3cmailto:drkirkby at kirkbymicrowave.co.uk>> >

Sent: Tuesday, December 20, 2022 4:38 AM

To: Alaa Zeineldine <alaa at expertwell.com <mailto:alaa at expertwell.com<mailto:alaa at expertwell.com%20%3cmailto:alaa at expertwell.com>> >

Cc: gnucash-user at gnucash.org<mailto:gnucash-user at gnucash.org> <mailto:gnucash-user at gnucash.org>  <mailto:gnucash-user at gnucash.org>

Subject: Re: [GNC] Rounding discrepancy in payment processing

On Tue, 20 Dec 2022 at 01:04, Alaa Zeineldine <alaa at expertwell.com <mailto:alaa at expertwell.com<mailto:alaa at expertwell.com%20%3cmailto:alaa at expertwell.com>> > wrote:

This is the first time I email to the list. So, I don't know if screenshots

will show up, but I am including below a succession of screenshots for a

fake customer invoice to demonstrate the problem.

Yes they do show up. It would probably be worth your while creating a test .gnucash file, emailing that, and let people see if they get the problem with your file.

Thanks for your help.

Dave

--

Dr. David Kirkby,

Kirkby Microwave Ltd,

drkirkby at kirkbymicrowave.co.uk<mailto:drkirkby at kirkbymicrowave.co.uk> <mailto:drkirkby at kirkbymicrowave.co.uk>  <mailto:drkirkby at kirkbymicrowave.co.uk>  <mailto:drkirkby at kirkbymicrowave.co.uk>

https://www.kirkbymicrowave.co.uk/

Telephone 01621-680100./ +44 1621 680100

Registered in England & Wales, company number 08914892.

Registered office:

Stokes Hall Lodge, Burnham Rd, Althorne, Chelmsford, Essex, CM3 6DT, United Kingdom

_______________________________________________

gnucash-user mailing list

gnucash-user at gnucash.org<mailto:gnucash-user at gnucash.org> <mailto:gnucash-user at gnucash.org>  <mailto:gnucash-user at gnucash.org>

To update your subscription preferences or to unsubscribe:

https://lists.gnucash.org/mailman/listinfo/gnucash-user

-----

Please remember to CC this list on all your replies.

You can do this by using Reply-To-List or Reply-All.

_______________________________________________

gnucash-user mailing list

gnucash-user at gnucash.org<mailto:gnucash-user at gnucash.org> <mailto:gnucash-user at gnucash.org>

To update your subscription preferences or to unsubscribe:

https://lists.gnucash.org/mailman/listinfo/gnucash-user

-----

Please remember to CC this list on all your replies.

You can do this by using Reply-To-List or Reply-All.

_______________________________________________

gnucash-user mailing list

gnucash-user at gnucash.org<mailto:gnucash-user at gnucash.org>

To update your subscription preferences or to unsubscribe:

https://lists.gnucash.org/mailman/listinfo/gnucash-user

-----

Please remember to CC this list on all your replies.

You can do this by using Reply-To-List or Reply-All.