Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 15 total |
Anybody using TIntegerField to store currency values? |
Tue, Jan 23 2007 4:17 PM | Permanent Link |
Mark Wilsdorf | 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 |
Wed, Jan 24 2007 4:40 AM | Permanent Link |
> 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/ | |
Wed, Jan 24 2007 10:25 AM | Permanent Link |
Mark Wilsdorf | 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/ |
Wed, Jan 24 2007 10:41 AM | Permanent Link |
Chris Erdal | 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) |
Wed, Jan 24 2007 11:07 AM | Permanent Link |
Sean McCall | 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 > |
Wed, Jan 24 2007 11:24 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.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 |
Wed, Jan 24 2007 11:24 AM | Permanent Link |
Sean McCall | 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/ > |
Wed, Jan 24 2007 11:28 AM | Permanent Link |
> I'm sure you already know this
I think I'd forgotten about it though! 8-) /Matthew Jones/ | |
Wed, Jan 24 2007 11:56 AM | Permanent Link |
Sean McCall | 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/ >> |
Thu, Jan 25 2007 6:55 AM | Permanent Link |
Mark Wilsdorf | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Friday, April 19, 2024 at 07:09 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |