Decimal arithmatic, was Re: Python GUI app to impress the boss?

[Steve Holden]

"Stuart D. Gathman" <stuart at bmsi.com> wrote in ...
> On Sat, 21 Sep 2002 23:10:09 -0400, Christian Tismer wrote:
>
> > Dan Bishop wrote:
>
> >  > Please name one other real-world situation in which decimal
> >  > arithmetic is actually *needed*.
> >
> > There is no reason to argue about that. Decimal arithmetic is exact for
> > most of the operations bankers require. They have been demanding for it
> > since decades, and they will not drop it.
>
Strangely enough, my experience has been somewhat different. You can perform
the arithmetic by mixing chickens' blood with short sticks if you want, the
thing that interests the financial guys is *correct answers*.

> Let me add a horror story of what happens when financial
> applications do *not* demand fixed point/decimal arithmetic.
>
> We took on a client whose accounting system used binary floats for all
> dollar amounts.  And I mean *dollar* amounts, not penney amounts.  Since
> all pennies were approximate, the total due at the bottom of the invoices
> would rarely match what the customer would get adding up the line items
> on a calculator.  It was only off by a few cents, but it tended to make
> customers lose faith in the system.  Statements also did not add up, and
> the Balance Sheet didn't balance.
>
In point of fact it is perfectly possible to use floating-point for such
purposes. The important thing is not to allow rounding errors to accumulate,
which effectively means rounding to 2DP or whatever is required for each
figure that will be presented as part of any total.

> We converted their system to use fixed point, and now everything balances
> and totals to the penney.  Unfortunately, their CPA keeps his own version
> of their General Ledger on Excel with binary floating point.  The CPA
> balance sheets do not balance.  The CPA General Journal transactions do
> not balance.  I demonstrated with a calculator that the CPA figures did
> not balance, but they insisted that we had to go with the CPA figures
> because he is a CPA, and I'm just a programmer.
> This is tricky since their new General ledger does not accept unbalanced
> transactions.
>
Although Excel will use binary floating point, the problem is precisely its
failure to round the figures it's presenting, which *will* allow rounding
errors to creep in. Excel has a calculation option to use "precision as
displayed", shich will help a lot. Unfortunately you are pissing in the wind
trying to get a CPA to admit there may be things they don't understand about
arithmetic. Make sure that if you *can* get him(?) to try this option he
uses a copy iof his spreadsheet, since it will change the precision of
constants permanently.

> My solution was to create a 'CPA adjustment' account to contain
> all the discrepancies, and manually decide how to allocate the
> extra/missing pennies for the CPA transactions.
>
I remember having to explain why it was never going to be possible to
reconcile a multi-currency sales ledger when daily rate changes meant that a
payment arrived having a different value than it had when it set off. At the
same time the accountant educated me as to why columns of figures such as
invoices really must "add up".

I discovered the funniest thing when a few years later I worked with a bunch
of five CPAs to build a combined sales/purchase/general ledger with
integrated job costing, job pricing and payroll. Obviously we had several
interesting discussions about what signs should have which meaning in every
possible context. I discovered that even experienced accountants frequently
use soem rule relating back to the physical circumstances of their training,
like "debits go in the column nearest the window". So they don't actually
think all the time in abstract concepts.

> There ought to be severe penalties for idiots that use floating point
> dollars for financial applications.  If forced to use floating point
> (e.g. because customer demands BASIC), then keep money amounts in
> whole pennies (or whatever the smallest currency unit for the country
> is), and divide by 100 (or whatever) for printing only (or just add the
> decimal point yourself).
>
I disagree. The penalties should be reserved for those who undertake this
kind of work without understanding what they are getting into. The methods
used must satisfy the customer, but the customer isn't usually bothered how
the results are achieved. In my particular case everything was done in BASIC
Plus, so any kind of decimal computation was right out. Floats
(appropriately rounded at every step) were perfectly acceptable.

regards
-----------------------------------------------------------------------
Steve Holden                                  http://www.holdenweb.com/
Python Web Programming                 http://pydish.holdenweb.com/pwp/
Previous .sig file retired to                    www.homeforoldsigs.com
-----------------------------------------------------------------------