I'm rewriting an old financial app, and have the luxury of rethinking
the table layouts.

Given the rounding problems of TCurrencyField and the space
requirements of TBCDField, I'm trying to decide whether my best choice
might be storing all amounts as "pennies" using TIntegerField.

I'd appreciate hearing any comments about the pitfalls/problems of
doing this.

As for advantages, table space requirements would be small, and
adding/subtracting field Values should be fast & efficient.

I suppose the main inefficiencies will come from converting from
integers to floats, then rounding and converting back to integers,
when I need to do multiplication or division on a financial amount.

But I might just be trading one hassle for another. What am I missing?

Mark Wilsdorf
Flagship Technologies, Inc.
QuickBooks™ Add-Ons and Solutions You Can Use
http://www.goflagship.com

> I suppose the main inefficiencies will come from converting from
> integers to floats, then rounding and converting back to integers,
> when I need to do multiplication or division on a financial amount.

Well, that's where you lost me. Why do you need to convert them to floats
at all? Floats are bad for money calculation. Why can't you just do it all
in integer still, and then all you need to do is change your display
routines to do the divide by 100 bit. I'd say the only gotcha to beware of
is currency changes. Are two digits enough for your safety? If you had to
support exchange rates, would it still be sensible?

BCD is available as a native form, and it may be worth paying the cost.

That said, I've only done a little in the way of money stuff, but I was
very glad to have been able to switch to integers only from the previous
person's floats as it instantly solved the loss of a penny problem we had.

/Matthew Jones/

On Wed, 24 Jan 2007 09:34 +0000 (GMT Standard Time),
mattjones@cix.co.uk (Matthew Jones) wrote:

>> I suppose the main inefficiencies will come from converting from
>> integers to floats, then rounding and converting back to integers,
>> when I need to do multiplication or division on a financial amount.
>
>Well, that's where you lost me. Why do you need to convert them to floats
>at all?

Suppose I have an amount of $555.63 (55563 integer). If I want to
multiply that amount by 0.90 (to calculate the amount discounted by
10%), then calculate sales tax on the discounted amount at 5.725%,
here's how it would work in integer:

55563 * 0.9 * 0.5725 = 2862.8835...and storing that back in an integer
field would truncate the value to 2862, so I'd have to round before
storing, to store the correct amount, 2863.

(I'm sure you already know this--you probably just assumed I was
talking about conversion for some other reason.)

> Why can't you just do it all
>in integer still, and then all you need to do is change your display
>routines to do the divide by 100 bit.

Well, at that point--dividing by 100-- I'd be converting to float
again anyway.

>I'd say the only gotcha to beware of
>is currency changes. Are two digits enough for your safety?

Yeah, I've thought about that a bit since my original post. Still,
different numbers of decimal places could be accomodated for display &
for math so long as the application stored a global variable somewhere
to indicate how to scale the integer (say, to 2, 3, or 4 decimals).


If you had to
>support exchange rates, would it still be sensible?
>
>BCD is available as a native form, and it may be worth paying the cost.
>
>That said, I've only done a little in the way of money stuff, but I was
>very glad to have been able to switch to integers only from the previous
>person's floats as it instantly solved the loss of a penny problem we had.
>
>/Matthew Jones/

Mark Wilsdorf <STOPSPAM_flagship@mcmsys.com> wrote in
news:d1ucr2dt0b4ajfo7vr56s0cg34sf557d6c@4ax.com:

> I'm rewriting an old financial app, and have the luxury of rethinking
> the table layouts.
>
> Given the rounding problems of TCurrencyField and the space
> requirements of TBCDField, I'm trying to decide whether my best choice
> might be storing all amounts as "pennies" using TIntegerField.
>
> I'd appreciate hearing any comments about the pitfalls/problems of
> doing this.
>
> As for advantages, table space requirements would be small, and
> adding/subtracting field Values should be fast & efficient.
>

I thought of doing this for my current app, but decided to go for BCD as
it was sufficient for all normal currency work, and would interact OK
with floats for currency exchange rates.

I feel that space-saving is a false problem, given the rate of progress
in disk storage densities.

I seem to recall reading somewhere around here that EDB uses a lot less
space for BCD, if that is relevant for the near future!

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.24 Build 1)

Mark,

I use TCurrency with no problems, but I always write my values through
custom functions that make sure that the numbers I am storing have been
rounded to 2 decimal places. A custom function gives you the advantage
of control over how the rounding is done (I think the default is bankers
rounding which may not be what you want). You need to watch out for edit
controls that might allow a user to get a currency value into a field
with extra digits even though it displays to 2 digits. For example, a
user types $122.333, the control displays $122.33 because it is
formatted for currency values, TField value is stored as 122.333.

There aren't really rounding issues with TCurrencyField rather there are
validation issues due to the fact that the precision allowed is 2 digits
greater than what most people use. In other words, the only problem is
when you store a number that is not valid because the currency you are
representing only has denominations like pennies in the US. Remember
that the extra precision could be useful in some situations like
representing the price per share of a stock.

I wouldn't waste your time with an integer field. A Currency value is a
scaled 64 bit integer with an implied decimal point before the last 4
digits. The benefit is that there is no rounding error like dealing with
floating point value which uses binary fractions. When I think of issues
with floating point values I think of values like $121.9099999999997267
*after* rounding which are due to the fact that some numbers don't
translate perfectly from binary fractions to pennies. Floating point
values always have to be formated before display. This is not the case
with currency values. The currency type is recognized by report engines
and editing controls and formatted appropriately and accurately. You
won't get that benefit with another type.

One thing that is often overlooked when dealing with money is the loss
of precision in calculations. Floating point values are still needed for
dealing with currency if you do anything but the most basic
calculations. Before a calculation, you should move the currency value
into a more accurate floating point type, do the calculations, and then
round the result and put it back into a currency variable. This so that
you don't accumulate errors due to the limited precision of a currency
field.

HTH,

Sean


Mark Wilsdorf wrote:
> I'm rewriting an old financial app, and have the luxury of rethinking
> the table layouts.
>
> Given the rounding problems of TCurrencyField and the space
> requirements of TBCDField, I'm trying to decide whether my best choice
> might be storing all amounts as "pennies" using TIntegerField.
>
> I'd appreciate hearing any comments about the pitfalls/problems of
> doing this.
>
> As for advantages, table space requirements would be small, and
> adding/subtracting field Values should be fast & efficient.
>
> I suppose the main inefficiencies will come from converting from
> integers to floats, then rounding and converting back to integers,
> when I need to do multiplication or division on a financial amount.
>
> But I might just be trading one hassle for another. What am I missing?
>
> Mark Wilsdorf
> Flagship Technologies, Inc.
> QuickBooks™ Add-Ons and Solutions You Can Use
> http://www.goflagship.com
>

Chris,

<< I seem to recall reading somewhere around here that EDB uses a lot less
space for BCD, if that is relevant for the near future! >>

Yes, it uses the native Delphi Currency type so it only uses 8 bytes for
19-20 significant digits and up to 4 decimal places.  The native TBCD type
used in DBISAM is 30+ bytes or so.

--
Tim Young
Elevate Software
www.elevatesoft.com

Mark,

As I said in the message I just posted, calculations should always be
done in a high precision type like Double and appropriately rounded
before going back into the numeric type required for the result. This
rule applies to any numeric type including currency values.

You have to do calculations and rounding no matter what type you use to
store your currency values... why not just store them in the type that
was designed for it? Why complicate things by having to format all your
values again on read which is what you need to do with an integer or BDC
type?

Use a TCurrencyField - you won't regret it.

Sean


Mark Wilsdorf wrote:
> On Wed, 24 Jan 2007 09:34 +0000 (GMT Standard Time),
> mattjones@cix.co.uk (Matthew Jones) wrote:
>
>>> I suppose the main inefficiencies will come from converting from
>>> integers to floats, then rounding and converting back to integers,
>>> when I need to do multiplication or division on a financial amount.
>> Well, that's where you lost me. Why do you need to convert them to floats
>> at all?
>
> Suppose I have an amount of $555.63 (55563 integer). If I want to
> multiply that amount by 0.90 (to calculate the amount discounted by
> 10%), then calculate sales tax on the discounted amount at 5.725%,
> here's how it would work in integer:
>
> 55563 * 0.9 * 0.5725 = 2862.8835...and storing that back in an integer
> field would truncate the value to 2862, so I'd have to round before
> storing, to store the correct amount, 2863.
>
> (I'm sure you already know this--you probably just assumed I was
> talking about conversion for some other reason.)
>
>> Why can't you just do it all
>> in integer still, and then all you need to do is change your display
>> routines to do the divide by 100 bit.
>
> Well, at that point--dividing by 100-- I'd be converting to float
> again anyway.
>
>> I'd say the only gotcha to beware of
>> is currency changes. Are two digits enough for your safety?
>
> Yeah, I've thought about that a bit since my original post. Still,
> different numbers of decimal places could be accomodated for display &
> for math so long as the application stored a global variable somewhere
> to indicate how to scale the integer (say, to 2, 3, or 4 decimals).
>
>
> If you had to
>> support exchange rates, would it still be sensible?
>>
>> BCD is available as a native form, and it may be worth paying the cost.
>>
>> That said, I've only done a little in the way of money stuff, but I was
>> very glad to have been able to switch to integers only from the previous
>> person's floats as it instantly solved the loss of a penny problem we had.
>>
>> /Matthew Jones/
>

> I'm sure you already know this

I think I'd forgotten about it though! 8-)

/Matthew Jones/

Oops. Got Mark & Matt mixed up.

Anyway... no matter what type of field or variable that a currency value
is stored in the programmer should be performing some kind of rounding
and validation before storage and/or on read if they want accurate data.

Before a Delphi had a Currency type there were good arguments to store a
currency value in a BCD or integer field instead of a floating point
field. Now that there is a dedicated currency type that addresses the
problems inherent in representing a base 10 decimal value in floating
point types (due to binary fractions). I can think of no good reason
outside of compatibility with existing code or the need to have
fractional pricing (widgets at .12218 cents per piece) to shun the use
of a TCurrencyField.

This explains the issue with storing a fixed decimal value like money in
a floating point number:

http://docs.python.org/tut/node16.html

Sean

Sean McCall wrote:
> Mark,
>
> As I said in the message I just posted, calculations should always be
> done in a high precision type like Double and appropriately rounded
> before going back into the numeric type required for the result. This
> rule applies to any numeric type including currency values.
>
> You have to do calculations and rounding no matter what type you use to
> store your currency values... why not just store them in the type that
> was designed for it? Why complicate things by having to format all your
> values again on read which is what you need to do with an integer or BDC
> type?
>
> Use a TCurrencyField - you won't regret it.
>
> Sean
>
>
> Mark Wilsdorf wrote:
>> On Wed, 24 Jan 2007 09:34 +0000 (GMT Standard Time),
>> mattjones@cix.co.uk (Matthew Jones) wrote:
>>
>>>> I suppose the main inefficiencies will come from converting from
>>>> integers to floats, then rounding and converting back to integers,
>>>> when I need to do multiplication or division on a financial amount.
>>> Well, that's where you lost me. Why do you need to convert them to
>>> floats at all?
>>
>> Suppose I have an amount of $555.63 (55563 integer). If I want to
>> multiply that amount by 0.90 (to calculate the amount discounted by
>> 10%), then calculate sales tax on the discounted amount at 5.725%,
>> here's how it would work in integer:
>>
>> 55563 * 0.9 * 0.5725 = 2862.8835...and storing that back in an integer
>> field would truncate the value to 2862, so I'd have to round before
>> storing, to store the correct amount, 2863.
>>
>> (I'm sure you already know this--you probably just assumed I was
>> talking about conversion for some other reason.)
>>
>>> Why can't you just do it all in integer still, and then all you need
>>> to do is change your display routines to do the divide by 100 bit.
>>
>> Well, at that point--dividing by 100-- I'd be converting to float
>> again anyway.
>>
>>> I'd say the only gotcha to beware of is currency changes. Are two
>>> digits enough for your safety?
>>
>> Yeah, I've thought about that a bit since my original post. Still,
>> different numbers of decimal places could be accomodated for display &
>> for math so long as the application stored a global variable somewhere
>> to indicate how to scale the integer (say, to 2, 3, or 4 decimals).
>>
>>
>> If you had to
>>> support exchange rates, would it still be sensible?
>>>
>>> BCD is available as a native form, and it may be worth paying the cost.
>>> That said, I've only done a little in the way of money stuff, but I
>>> was very glad to have been able to switch to integers only from the
>>> previous person's floats as it instantly solved the loss of a penny
>>> problem we had.
>>>
>>> /Matthew Jones/
>>

On Wed, 24 Jan 2007 11:19:12 -0500, Sean McCall
<someone@somewhere.net> wrote:

>Use a TCurrencyField - you won't regret it.

That's about the same conclusion I've reached during this
discussion...thanks for your comments!

Mark Wilsdorf
Flagship Technologies, Inc.
QuickBooks™ Add-Ons and Solutions You Can Use
http://www.goflagship.com