Re: [GNC] Rounding discrepancy in payment processing [SOLVED]
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 Sent: Monday, December 26, 2022 11:30 PM To: 'Murugan Muruganandam' Cc: gnucash-user@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:”, e.g. “Trading:CURRENCY:EUR” and “Trading:CURRENCY:
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:”, 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