Numeric data question
terry
I'm a Python newby, but I have read several books, documentation,
and email list archives, and I have not found a direct reference
to how money calculations can be handled in Python.
Specifically, I'ld like to use a Postgres (or any database)
numeric data column that represents money with 'normal' Python
math operators. I am *very* aware of the floating point
problems, and know that I must avoid even using floating point
for accounting applications.
Would someone kindly either give me a short explaination or point
me toward an information source.
Thanks in advance,
terry
Tim Lavoie
Well, PostgreSQL does have a money type, but IIRC it's just a float with a
fixed number of decimal places. You could just use pennies as the unit and
integer math though.
--
"The sign said, 'Jesus is the answer.' Which is kinda weird, since my
question was 'What the hell's this stuff on my sandwich?'"
-- Phil Bacon
terry
>> Well, PostgreSQL does have a money type, but IIRC it's just a
>> float with a fixed number of decimal places. You could just
>> use pennies as the unit and integer math though.
Thanks Tim, it is a more complex representation than just float
as it also contains the decimal places. I haven't seen the exact
internal format in Postgres, but it's a very common datatype in
many databases. In general it's not appropriate/legal to use
integer or floating point math when dealing with money. It
creates too many coding dependent variations in results - not to
mention maintenance nightmares.
I'm guessing now (from lack of real experience) that being a
'typeless' language that I would be forced into contriving a
method for handling money such that I could never code something
straightforwardly like:
TotalCost = Quantity * UnitCost
(where Quantity is integer and the others money).
Am I loonie, or is Python just not inherently suitable for
accounting applications for this reason?
terry
Alex Martelli
...
> I'm guessing now (from lack of real experience) that being a
> 'typeless' language that I would be forced into contriving a
> method for handling money such that I could never code something
> straightforwardly like:
> TotalCost = Quantity * UnitCost
> (where Quantity is integer and the others money).
If you were working with a typeless language, that might be the case.
Fortunately, Python is strongly typed, so that's no problem, of
course. This statement would probably be equivalent to
something like:
TotalCost = type(UnitCost).__rmul__(Quantity(
for example (or UnitCost.__class__.__rmul__ if UnitCost was an
instance of an old-fashioned class, but that comes to much the
same thing). Just code your __rmul__ (and __mul__, etc) methods
and voila, whatever numeric type you want is there.
http://starship.python.net/crew/aahz/Decimal.py is an example,
though Aahz still doesn't consider it finished.
> Am I loonie, or is Python just not inherently suitable for
> accounting applications for this reason?
If you're saying that Python is "typeless", then I guess
"loonie" might be applicable, yes.
Alex
Terry Grogan
Thanks Alex. Probably, typeless was not the right word. You
have given me hope and direction.
terry
--
Terry Grogan
Datamark Systems
703-975-6250
Chris Barker
> I'm guessing now (from lack of real experience) that being a
> 'typeless' language
Python is "dynamically, but strongly typed" which is an important distinction.
> that I would be forced into contriving a
> method for handling money such that I could never code something
> straightforwardly like:
> TotalCost = Quantity * UnitCost
> (where Quantity is integer and the others money).
You might have to contrive something, but it would allow you to code:
> something straightforwardly like:
> TotalCost = Quantity * UnitCost
> (where Quantity is integer and the others money).
To keep the buzzwards rolling, Python is an Object Oriented language
that supports Operator Overloading. What you can do is create a class,
"money" that defines the arithmetic operators the way you want them to
be used. You can do this is such a way that it interacts appropriately
with Python numeric types, and uses all the standard operators.
> Am I loonie, or is Python just not inherently suitable for
> accounting applications for this reason?
Do any programming languages have a built in "money" type? How is this
problem solved in them? If this really is a problem that can't be
handled by using floating point and rounding appropriately where
required, I imagine someone out there has written a money class already.
If not you will be doing a service to the community if you do it.
no comment on whether you are loonie or not.
-Chris
Here is a trivial example to get you started. You would obviuosly have
to be very carefull about rounidng and all that to do it right:
#!/usr/bin/env python2
import string
class money:
def __init__(self,amount, flag = "dollars"):
if flag == "dollars":
self.micropennies = long(round(amount,2)*100000000)
elif flag == "pennies":
self.micropennies = long(round(amount)*1000000)
elif flag == "micropennies":
self.micropennies = long(amount)
def __str__(self):
return "$%i."%(self.micropennies /100000000)+
string.zfill(int(((self.micropennies /
1000000) % 100)),2)
def __mul__(self,other):
return money(self.micropennies * other,"micropennies")
def __rmul__(self,other):
return money(self.micropennies * other,"micropennies")
UnitCost = money(23.45)
print "UnitCost is: ", UnitCost
Quantity = 5
TotalCost = Quantity * UnitCost
print "TotalCost is: ",TotalCost
--
Christopher Barker, Ph.D.
Oceanographer
NOAA/OR&R/HAZMAT (206) 526-6959 voice
7600 Sand Point Way NE (206) 526-6329 fax
Seattle, WA 98115 (206) 526-6317 main reception
terry
>> Do any programming languages have a built in "money" type?
>> How is this problem solved in them?
Hmmm, my question is getting answered - by the time it is, I will
have found out how to properly ask it.
I've been programming in MS Access VB since V1, so I've come to
accept that a variable that references data that's Access's
Currency data type always gets calculated with properly - i.e. no
floating point problems. Clearly VB is using that data type
information and resolving it internally. But also, there is a
declarable data type of Currency for other variables. I believe
that Cobol has one too.
You're example is what is not acceptable (to the accounting
community) - although I did have to use that back in the 60s to
write FORTRAN accounting programs and everyone just accepted
being a little off. I've also used routines that are based on
strings and integers. They all share the common trait of being
ugly.
>> I imagine someone out there has written a money class already.
>> If not you will be doing a service to the community if you do
>> it.
Alex gave me a source ... I wouldn't know where to start yet.
>> no comment on whether you are loonie or not.
Thanks, and thanks for your comments.
terry
Mark McEahern
the original poster checkout Tim Peters' FixedPoint class. I use that for
shoving money data into PostgreSQL and it works just fine. I don't have a
link to it, but that's what google's for, right?
Cheers,
// mark
-
terry
Thanks!!!!
--
terry
James J. Besemer
Chris Barker wrote:
> Do any programming languages have a built in "money" type?
Visual Basic does, as I am sure do several others. Curency was not part of VB for
quite a while. I believe currency eventually was introduced to VB mainly in order
to facilitate interfacing with various database engines, most all of which have an
explicit currency type (distinct from real and int).
The Python interfaces to MySql and MSql implement a currency type for this very
reason. Time and Date also. These in fact are not arbitrary implementations of
currency, et al., but necessarily track the database native format and ease
conversion to/from native Python formats.
Amongst the various attributes that may be counted on the list of benefits of an
explicit currency type are (a) it is a good place to isolate locale issues and (b)
accuracy requirements generally prohibit use of floating point (which is
intrinsically inaccurate for decimal fractions) or integers (which lack the
necessary precision).
Of course, such a thing could be implemented in Python as you suggest, probably
with longs or strings emulating fixed-point numbers.
All those cute anecdotes about round off error -- they're not actually tolerated
in real commerce applications.
Regards
--jb
--
James J. Besemer 503-280-0838 voice
http://cascade-sys.com 503-280-0375 fax
mailto:j...@cascade-sys.com
terry
>> Of course, such a thing could be implemented in Python as you
>> suggest, probably with longs or strings emulating fixed-point
>> numbers.
James,
Do you know if the database internal representations of currency
are consistent across many/most databases? It would seem like
that would have to be true for a Python to have a chance for an
intrinsic implementation that would be relatively universally
useful by ODBC connections, wouldn't it? However, interface
packages like Pypgsql could translate from Postgres internal to
Python internal representations without a problem - if it existed
that is :).
terry
Terry Reedy
"terry" <tg5...@citlink.net> wrote in message
news:mailman.1027526808...@python.org...
>
> >> Well, PostgreSQL does have a money type, but IIRC it's just a
> >> float with a fixed number of decimal places. You could just
> >> use pennies as the unit and integer math though.
> Thanks Tim, it is a more complex representation than just float
> as it also contains the decimal places. I haven't seen the exact
> internal format in Postgres, but it's a very common datatype in
> many databases. In general it's not appropriate/legal to use
> integer or floating point math when dealing with money. It
> creates too many coding dependent variations in results - not to
> mention maintenance nightmares.
If the amounts of money you are dealing with are well less than the
max, and if you are only doing + and - (accounting in the strict
sense) and maybe * by int amounts, ints using the lowest denomination
(cents in the US) work just fine. +/- 2 billion cents is +/- 20
million dollars. Python's integration of longs with ints should
remove the upper limit constraint. Financial calcs with interest and
other rates requires more care.
TJR
James J. Besemer
terry wrote:
> Do you know if the database internal representations of currency
> are consistent across many/most databases?
I dunno but I expect they are different. Common encodings are as
strings, as "BCD" (2 decimal digits per byte) and "fixed point"
(integer with implied decimal point).
> It would seem like
> that would have to be true for a Python to have a chance for an
> intrinsic implementation that would be relatively universally
> useful by ODBC connections, wouldn't it?
Not really. Each different Python <--> database interfaces would
handle conversion.
> However, interface
> packages like Pypgsql could translate from Postgres internal to
> Python internal representations without a problem - if it existed
> that is :).
I'm not sure there IS a 'Python internal representation'. The
different interfaces handle conversions between database native
format and Python base types, such as integers, reals and strings.
James J. Besemer
Terry Reedy wrote:
> "terry" <tg5...@citlink.net> wrote in message
> > In general it's not appropriate/legal to use
> > integer or floating point math when dealing with money. It
> > creates too many coding dependent variations in results - not to
> > mention maintenance nightmares.
>
> If the amounts of money you are dealing with are well less than the
> max, and if you are only doing + and - (accounting in the strict
> sense) and maybe * by int amounts,
Accounting per se cannot be limited to the above operations. Think of
tax tables, withholding percentages, asset depreciation, interest
calculations, etc., etc.
"Accounting" generally is far from trivial and is way more complicated
than balancing your checkbook.
> ints using the lowest denomination
> (cents in the US) work just fine. +/- 2 billion cents is +/- 20
> million dollars. Python's integration of longs with ints should
> remove the upper limit constraint. Financial calcs with interest and
> other rates requires more care.
Note that "currency" is more than just dollars and cents. First off,
even in US currency there is need to talk about fractions of a penny.
E.g., in the stock market, 1/8 "point" is $0.125. Also you quickly run
into scale problems with non-US currencies, e.g., 2 billion Lira is too
harsh an upper limit. (My last hotel bill in Venice was over L
1.000.000).
FWIW, Microsoft COM defines a currency type as an 8-byte, two's
complement integer, scaled by 10,000. This is the format VB uses. This
gives a fixed point decimal value with 15 digits of whole number and 4
digits of fraction. A lot of databases allow the designer to explicitly
specify field size and precision to better control the trade-off of value
range vs. space consumed.
Luckily nobody I know of still uses a non-decimal currency, like the old
English system. Lesse, 5 shillings to the crown, 4 crowns to the pound,
2 shillings per florin, two sixpence (12 pennies) per shilling, two
tuppence per groat, two pennies per tuppence, 2 farthings to the
ha'penny, and they actually minted coins as small as 1/4 farthing.
Basically 12 pennies to the shilling, and 20 shillings to the pound, or
240 pennies per pound. THAT was when money was worth something, when you
minted coins worth 1/16 of a cent or 1/3840th of your basic monetary
unit!
Regards
--jb
Terry Reedy
"James J. Besemer" <j...@cascade-sys.com> wrote in message
news:mailman.102762725...@python.org...
>
> Terry Reedy wrote:
> > If the amounts of money you are dealing with are well less than
the
> > max, and if you are only doing + and - (accounting in the strict
> > sense) and maybe * by int amounts,
>
> Accounting per se cannot be limited to the above operations.
Account: "to furnish a reckoning of money received and paid out" says
my Websters New World Dict. If this is all one wants or needs a
program to do, then it is sufficient.
> Think of tax tables, withholding percentages, asset depreciation,
interest
> calculations, etc., etc.
I did, which is why I wrote
>> Financial calcs with interest and other rates requires more care.
Calculations of amounts to seek or pay are not the same as actual
flows themselves
Terry J. Reedy
Alex Martelli
...
> E.g., in the stock market, 1/8 "point" is $0.125. Also you quickly run
> into scale problems with non-US currencies, e.g., 2 billion Lira is too
> harsh an upper limit. (My last hotel bill in Venice was over L
> 1.000.000).
Not a problem any more since Jan 1. Unless you travel to Turkey,
obviously, where a million (Turkish) liras will NOT buy you a night
at a hotel, even though it's a cheap (and wonderful) country.
> Basically 12 pennies to the shilling, and 20 shillings to the pound, or
> 240 pennies per pound. THAT was when money was worth something, when you
> minted coins worth 1/16 of a cent or 1/3840th of your basic monetary
> unit!
A pound was originally a pound (roughly half a kilogram; same for
'lira', coming from 'libra', meaning 'scales' and also 'a pound')
of SILVER -- hardly a "basic monetary unit", more like an accounting
abstraction. Starting from roughly equal valuations in the 8th/9th
century (wherever Charlemagne reigned, plus places, such as Britain,
where he didn't but his writ still had prestige), currency then
underwent wild gyrations, best narrated imho in the works of Carlo
Cipolla (mostly in English, don't worry -- although Italian, he
taught in the US for most of his career). Inflation was slow and
sluggish in Britain (mostly because, as Cipolla shows, British
economy was in turn dormant and deeply feudal), up to wild and
furious in the booming Italian merchant-republics (exactly because
their centuries-long mercantile boom demanded far more currency
than the then-limited supplies of species could satisfy).
Then of course the whole system was forever uprooted in the 16th
century as unprecedented amounts of gold and silver flowed into
Europe (outpacing for the first and only time the growth rate of
the real underlying economies). It IS a tribute to Charlemagne's
lasting influence on Europe that his "pound", "lira" or "livre"
still remained at least in name (and in some cases in the classic
1:20:12 ratios, at least in Britain) despite all of these
turmoils (French noblemen still accounted their rents in "livres"
to the late 18th century, even if no currency of that name had
been minted for many centuries).
But of course the whole field of numismatics is chock full of
such fascinating fossils -- starting from "money" itself (and
its counterparts "moneta", "monnaie") from the attribute of
Goddess Juno Moneta (Juno the Warning-giver -- the famous
sacred geese were apparently in Juno's temple) in whose temple
the Roman Republic's mint (same root) was established in the
3rd century BC. -- all the way to "dollar" with its strange
connection to Joachimstal (today called Jáchymov)...
Alex
Chris Barker
terry wrote:
> I will have found out how to properly ask it.
That is the first step in a newsgroup...how do I ask my question?
> You're example is what is not acceptable (to the accounting
> community)
My example was just that, an example. It was not intended to be a decent
implimenation, just an example of how one could write such a class, and
an example of how such a class could interact with other Python numeric
types, which is how I read your original question. However, asside from
being very incomplete, I wander what was unacceptable about it? It used
python long integer micropennies for internal storage, so you would have
no floating point problems and unlimited size. Maybe not a well
optimized solution, but it sounds more accurate that what VB uses,
according to another poster's description.
Anyway, you would have some work to do, and have to know what you're
doing to do it right, maybe Tim Peters' FixedPoint class will fit your
needs (and Tim Peters does know what he is doing).
> I've also used routines that are based on
> strings and integers. They all share the common trait of being
> ugly.
If you wrote a python class that way, the uglyness would be hidden from
the user. Fortran did not offer that level of encapsulation.
Good luck,
-Chris
James J. Besemer
Alex Martelli wrote:
> Not a problem any more since Jan 1.
Sad to think of an Italy without Lira -- the distinctive currency was part of
the charm.