Generic improvement for accuracy trouble

Hello,
We started the implementation of this feature, to be released in two weeks in trunk.

We can not use float but use we will replace decimal(16,2) by decimal. (allows every kind of decimal, the fields.float will manage the rounding). We are also working on a configuration table for this, so that you can change and configure on the fly:
  - accounting: 2 digits
  - sale: 4 digits
  ...

----------------------------------------------------------------------------------------------------------
1) price_accuracy should be renamed to floating_precision
2) for money, rounding factor from specific currency should be used. At least for accounting movements, totals in invoices and derived basic documents.
3) for sums in invoice lines it should be configurable (probably in particular product or product group), with fallback possibility.

ferdinand 20091118
IMHO storing accounting values as float is a NO GO - values have to be stored as BCD
and I see "accounting" in a wide sense - about everything OpenERP does is accounting in some sense and values cerated in one module must match values in another module
Example - stock_move values must match account_move_line values (which is solved in
https://code.launchpad.net/~openerp-commiter/openobject-addons/chricar_price_unit and avg_price is always calculated value/qty to have maximum precision)
same is true for HR / analytic accounting etc

Especially many currently used SQL statements "select sum(value) from ..." will not match "sum(round(value,accuracy)) from ..." if floats are stored where as the second term would be returned in python models.
see also
http://stackoverflow.com/questions/61872/use-float-or-decimal-for-accounting-application-dollar-amount

cjalmeida 20091216
Second ferdinand. The correct accounting procedure is to write-off rounding differences - accountants are very serious about this. We should just automate the process like setting a default write-off account for currency exchange.

Wuergler 20100105 [CORRECTED]
Yes, but don't forget to solve the bug "[CORRECTED below]" which is still not solved with latest sources as of today/5.07, which seems to happen even in simple cases with currency conversions and any curr. rounding setting: I haven't checked code etc. responsible for this. In this case, no write-off entry should be necessary!
[CORRECTION below]

Wuergler 20100107 Got that wrong several times, SORRY again:
Error msg. on supplier invoice is "Integrity Error ! You can not validate a non-balanced entry !"
Summary of rounding error I meant (similar to bugs 452854, 328077?): single amounts with entries on different accounts (with or without different tax cat. on each acc.) are each converted to local currency and rounded, then added up (sum of converted and rounded single amounts) => sum is different from total of foreign currency converted and rounded on contra-account (rounded sum of the total converted amount).
Example: supplier invoice with 2 items at 12 EUR, with 0.632783 EUR/CHF, thus 12/0.632783 = 18.9638 = 18.96; -> twice that line = 37.92, but 24/0.632783 = 37.93 (same problem with or without tax on each line).

Lionel 2010-01-27
We use --price_accuracy=3 and we keep hitting rounding errors again an each time something is "fixed" in the "stable" branch.
Please, I beg you for a rounding policy to be decided AND enforced, once and for all, and in time for v5.2.
[EDIT] Also, I would really appreciate if you would all please take some time to write the unit test scenarios which demonstrate the bugs we have already encountered. This would be a valuable guard against further regression. I'm going to write a few myself, of course I'll post on the corresponding bug reports to let you know.

Joel 2010-01-28, @lionel
We already include some test for rounding regression, but it's true it' not enough ! If you provide concrete example and let me now how you encounter the rounding problem, then I can probably help coding some more. This is the way to go, each time we face a new one, we should code the test case.

Mathias 2010-03-02
Third ferdinand - we should use Decimal thoughout: python and postgres provide decimal types. But how big a change is this?

And price_accuracy should be renamed to *decimal_precision* instead of floating_precision, the name float should be avoided.

For rounding, only python should be used. Python's Decimal has configurable rounding policies (e.g. ROUND_HALF_DOWN, ROUND_HALF_EVEN) while I can't find the rounding policy for the postgres decimal type, for float postgres depends on the os libraries.

My german tax advisor tells me he really doesn't care which rounding is used, but that it should be the same throughout the system and should be documented if it's not the one usually used here (half down).

So for rounding I think we should a) use Pythons Decimal b) decide on a rounding policy and set it explicitly and c) document it d) never use postgres for monetary important rounding (e.g. bookkeeping).

This b) is obviously a different kind of rounding policy than Lionel want's. I really do not know the legal implications of when and where to do the rounding, but what I learned seems to be: you can do quite a lot of things, once you *document it and stick to it*. So I'd expand Lionels "decide on it and stick to it (!)" with: "and document it", so my IRS is happy.

For currency rounding errors I vote too for an automatic write-off solution, with configurable account per currency, defaulting to the same account for all currencies.

[gegard 2010-03-04]
I spent the day with a qualified accountant looking at accounting accuracy. He said no knowledgeable accountant in the UK would ever use a system that relied on floats to store accounting transactions. But not every value or calculation needs a fixed-point representation - only those values that are posted transactions. So maybe only a few fields (such as credit and debit in account_move_line) need be affected.

We looked at adding a specialization of float that casts to fixed point when necessary. You would signal that a cast to fixed point is needed by adding a 'fixedpoint' argument beside 'digits(16,2)' in a fields.float. Open ERP would continue to work as it does at present unless the new module was loaded.

We plan to investigate this further unless anyone can think of a good reason why this might not work.

[BTW, he says that the Python implementation of Decimal is untrustworthy, and would also not rely on a system using this route. I'll try to get more details on that.] ... <edited 2010-03-06> but will test whatever turns up!

Raphaël Valyi - March 04

Hello, I hold no official accounting skills, still, I let you know that I tend to agree on Ferdinand and Geoff point: floating point in general is a NO GO as posted entries are forced to be rounded at 2 digits (BTW is that true in all countries? should that '2' be '3' for instance in some country, would be interesting to make sure) and we need lot's of other values/sums to match those posted entries.

For instance this 2 digit legal accounting is the reason OpenERP is right to sum the rounded order lines for totals rather than rounding the total as I first thought (now it might eventually allow the other mode provided lines entries are grouped into the same account move line then, but seems a minor feature), because eventually all lines could be ventilated to different accounts and rounded at 2 digits and should still match the order total.

On the contrary, will very soon show concretely some places where a combination of proper forced rounding to 2 digits and extended precision elsewhere dramatically improves the situation; I'm especially thinking about VAT included mode here were we found some issues and solutions. More precisely, extending the 'price_accuracy' as often been a way to have order prices matching external systems inputs such as ecommerces, but we tend now to think that this current 'price_accuracy' extension is a NO GO too and would better be replaced here by a working tax included system.

Given that posted entries are rounded at 2 digits, I think normal floats (possibly with a larger precision at some places though) are far enough to ensure multiplied numbers are precise enough for that 2 digits bottom line (at least as long as it is 2 and not more). A I missing something here?

As for multi-currency reconciliation, I think an automatic write-off with the rate fluctuation delta is the solution as suggested by some.

José 14/03/2010

I think one thing is the PRICE and another the AMOUNT. In the price you can use as many decimal places as you need and no one will complain. For example we need 5 decimal places because the price is per liter and the client can buy from hundreds to millions liters oil. Also, you don’t have to reconcile prices in accounting.

On the other hand, a monetary amount is represented in a currency and is expected to be paid by someone, so you have to round it to the smallest unit payable in that currency. This is where the current 2 decimal places come from: euro and dollar, and many other currencies, have the cent as the smallest unit. Every invoice line must stand on its own, so you must round every line individually to the number of decimal places required by the currency. In accounting you also have to post amounts with the number of decimal places required by the currency.

Increasing the number of decimal places in intermediary amounts could solve many, but not all, of the problems faced now by the tax included prices, but there is no algorithm to anticipate when the system will fail. The best solution would be to keep all amounts to the number of decimal places used by the currency and write the differences off to a special account. Also, floating point arithmetic will work most of the time, only we cannot predict the situations where it will not work.

In summary: “price_accuracy” should affect only the PRICE, and be configurable. The number of decimal places of monetary amounts must be dependent on the currency.