Re: dividing money by money

Andy Balholm wrote:

> The ability to divide money by money would be useful for finding
> what percent one money value is of another.

That certainly sounds useful and natural to me. I don't think it
rises to the level of a *bug*, but it's a reasonable request for
enhancement. If there are no objections I'll add it to the TODO
list.

-Kevin

On 2010-03-30, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Andy Balholm wrote:
>
>> The ability to divide money by money would be useful for finding
>> what percent one money value is of another.
>
> That certainly sounds useful and natural to me. I don't think it
> rises to the level of a *bug*, but it's a reasonable request for
> enhancement. If there are no objections I'll add it to the TODO
> list.

That'd also make it easy to convert money to numeric or double by dividing
by '1'::money.

Kevin Grittner írta:
> Andy Balholm wrote:
>
>
>> The ability to divide money by money would be useful for finding
>> what percent one money value is of another.
>>
>
> That certainly sounds useful and natural to me. I don't think it
> rises to the level of a *bug*, but it's a reasonable request for
> enhancement. If there are no objections I'll add it to the TODO
> list.
>

How about improving the money type so it can store
values in different currencies?

=# create table money1 (x money);
CREATE TABLE
=# insert into money1 values ('1');
INSERT 0 1
=# select * from money1;
x
--------
Ft1,00
(1 sor)

=# insert into money1 values ('$1');
ERROR: invalid input syntax for type money: "$1"

pg_dump -t money1:

==================
Ft1,00
\.
==================

Loading this dump into another database that happens
to have a different locale than hu_HU fails.

But then any operator between two money values would
only work if both values have the same currency.

Best regards,
Zoltán Böszörményi

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

Boszormenyi Zoltan wrote:
> Loading this dump into another database that happens
> to have a different locale than hu_HU fails.
>
> But then any operator between two money values would
> only work if both values have the same currency.
>

indeed, its all a big tarpit. next, you'll want currency conversion
tables. and, are there still any currenccies like old style UK where
the subunits aren't 100ths? schillings or whatever

John R Pierce írta:
> Boszormenyi Zoltan wrote:
>> Loading this dump into another database that happens
>> to have a different locale than hu_HU fails.
>>
>> But then any operator between two money values would
>> only work if both values have the same currency.
>>
>
> indeed, its all a big tarpit. next, you'll want currency conversion
> tables. and, are there still any currenccies like old style UK where
> the subunits aren't 100ths? schillings or whatever

Yeah, and we could also switch PG numeric type to base 12
to count in dozens or base 60 to deal with the mayan calendar... ;-)

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

Boszormenyi Zoltan wrote:

> How about improving the money type so it can store
> values in different currencies?

Have you seen "taggedtypes"?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

John R Pierce <pierce(at)hogranch(dot)com> wrote:
> Boszormenyi Zoltan wrote:

>> But then any operator between two money values would
>> only work if both values have the same currency.

That sounds like a sane limitation.

> and, are there still any currenccies like old style UK where
> the subunits aren't 100ths? schillings or whatever

I'm not sure if you're arguing for or against the database type
knowing how to divide those to get a percentage, versus putting the
onus on the application programmer. Where does it make the most
sense to you to put such logic?

-Kevin

Forgot to send to the list....

On Tue, Mar 30, 2010 at 8:25 AM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> John R Pierce <pierce(at)hogranch(dot)com> wrote:
>> Boszormenyi Zoltan wrote:
>
>>> But then any operator between two money values would
>>> only work if both values have the same currency.
>
> That sounds like a sane limitation.
>
>> and, are there still any currenccies like old style UK where
>> the subunits aren't 100ths?   schillings or whatever
>
> I'm not sure if you're arguing for or against the database type
> knowing how to divide those to get a percentage, versus putting the
> onus on the application programmer.  Where does it make the most
> sense to you to put such logic?

With due respect, this sort of thing is rather difficult to get right
all at once.  I would suggest at some point having a modified MONEY or
maybe to avoid conflicts let's call it a CURRENCY datatype on
Pg-foundry where we can experiment and get these details right.  I am
thinking of doing a rough draft in SQL and PLPGSQL so that someone can
convert to C once everything works properly :-).

If folks are interested, I might make a simple approximation of this
that would require 8.4 or higher.  It might come in handy for
LedgerSMB too.....

Best Wishes,
Chris Travers

Chris Travers <chris(at)metatrontech(dot)com> wrote:

> With due respect, this sort of thing is rather difficult to get
> right all at once.

The existing type is fixed point and we know how to add and subtract
two of them. I don't think it's all that difficult to add division,
yielding some non-money numeric type (like perhaps float8).

Neither do I see it as a particularly slippery slope. If someone
has a list of other things they want to do for monetary types -- I
don't see that it has anything to do with this particular request,
unless some convincing argument can be made that adding this would
make the other features harder to implement.

Do we have to take a simple request for something useful and blow it
up into something grand and complicated? Well, I mean, *every*
time? ;-) The grander aspects of this thread would make more sense
as a separate thread for a longer-term effort, probably for a new
type (or set of types).

-Kevin

I wrote:

> yielding some non-money numeric type (like perhaps float8).

Hmmm... Given that we've already had a couple posts on the idea
that dividing by '1'::money could convert money to something more
general, I guess it would be safer to stick to numeric.

-Kevin

On Tue, Mar 30, 2010 at 12:22 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> [Did you mean to take this off-list, or was that accidental?]

Accidental.
>
> Chris Travers <chris(at)metatrontech(dot)com> wrote:
>
>> With due respect, this sort of thing is rather difficult to get
>> right all at once.
>
> Division of two fixed-point numbers we already know how to add, or
> the whole suite of all features one might possibly want in a
> monetary data type?  (I get the feeling that some of the posts on
> this thread involve a straw man going down a slippery slope.... ;-)

Ok. Here is my application: I write a multi-currency accounting
program backed by PostgreSQL. After 1.3 is released (2Q this year),
we expect to be doing a full redesign.

What I am thinking about is having a custom data type, something like:

CREATE DOMAIN curr VARCHAR(3);
CREATE TYPE monetary AS (amount NUMERIC, currency CURR, multiplier
NUMERIC); This reduces into two basic components: a value (amount *
multiplier) and a currency identifier (USD, etc).

One could also then store monetary[] arrays for addressing specific
denomination storage. I.e. "When closing the till we had 26 pennies,
53 nickles, 12 quarters, 25 $1 bills, 35 $5 bills, 15 $10 bills, and 5
$20 bills."

Then we can allow NUMERIC arithmetic on monetary amounts provided that
the CURR field is the same. We could also store things like the cash
counted from a till at the end of the day by denomination. One could
have easy monetary::numeric casts as well.

Anyway, that's my basic thinking. One could further add currency
conversion tables to an application if necessary.

Just thinking about the more general problem and how things could be
handled more gracefully...

Best Wishes,
Chris Travers

Chris Travers <chris(at)metatrontech(dot)com> wrote:

> Just thinking about the more general problem and how things could
> be handled more gracefully...

Sure, but in the meantime, consider:

test=# select '12'::money * '2'::numeric;
?column?
----------
$24.00
(1 row)

test=# select '24'::money / '2'::numeric;
?column?
----------
$12.00
(1 row)

test=# select '24'::money / '12'::money;
ERROR: operator does not exist: money / money
LINE 1: select '24'::money / '12'::money;
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.

So we support:

a * b = c
c / b = a

but don't even *think* about c / a = b ???

The OP just wanted to add some symmetry to this, so that the
existing class could handle a not-uncommon use case more easily. As
far as I can see, the implementation of this operator could convert
two int64 values to numeric values and perform numeric division to
get the result. (I was going to mark the TODO as an easy one.) I
don't see how this change would affect what you want to do, one way
or the other.

-Kevin

On Mar 31, 2010, at 7:07 AM, Kevin Grittner wrote:

> (I was going to mark the TODO as an easy one.)

I thought it would be pretty simple, too, so I decided to go ahead and write and test it as an external module.

I think the function definition could be pasted directly into an appropriate place in src/backend/utils/adt/cash.c, if someone wants to add it to the main code base. The SQL to load it would need to be modified somewhat to fit into postgres.bki.

Here is the C source:

#include <postgres.h>
#include <fmgr.h>
#include <utils/cash.h>

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(cash_div_cash);

/* cash_div_cash()
* Divide cash by cash, returning float8.
*/
Datum
cash_div_cash(PG_FUNCTION_ARGS)
{
Cash dividend = PG_GETARG_CASH(0);
Cash divisor = PG_GETARG_CASH(1);
float8 quotient;

if (divisor == 0)
ereport(ERROR,
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg("division by zero")));

quotient = (float8)dividend / (float8)divisor;
PG_RETURN_FLOAT8(quotient);
}

-------------------------------------------------------------------------------------

And here is the SQL to load it (assuming it has been compiled as a dynamically loadable module named divide_money and placed in the library directory on the server):

CREATE FUNCTION cash_div_cash(money, money) RETURNS double precision
LANGUAGE c IMMUTABLE
AS '$libdir/divide_money', 'cash_div_cash';

CREATE OPERATOR / (
PROCEDURE = cash_div_cash,
LEFTARG = money,
RIGHTARG = money
);

Andy Balholm <andy(at)balholm(dot)com> wrote:

> quotient = (float8)dividend / (float8)divisor;
> PG_RETURN_FLOAT8(quotient);

That was my first inclination, but the fact that two different
people talked about using division by '1'::money as a way to convert
money to another type has me nervous about using an approximate
type. Any chance you could rework it using numeric? I know it's
less trivial that way, but unless we provide a cast to numeric, I'm
afraid people will use the above trick, assign it to a numeric
variable or column, and then wonder why they've lost precision.

Or I guess we could leave this as you've written it and add support
for a cast from money to numeric.

-Kevin

On Mar 31, 2010, at 11:01 AM, Kevin Grittner wrote:

> That was my first inclination, but the fact that two different
> people talked about using division by '1'::money as a way to convert
> money to another type has me nervous about using an approximate
> type. Any chance you could rework it using numeric? I know it's
> less trivial that way, but unless we provide a cast to numeric, I'm
> afraid people will use the above trick, assign it to a numeric
> variable or column, and then wonder why they've lost precision.
>
> Or I guess we could leave this as you've written it and add support
> for a cast from money to numeric.

It probably is wiser to rewrite it with the numeric type. A cast from money to numeric is theoretically ambiguous (the result could be either dollars or cents), although most people would expect dollars.

I'll see if I can rewrite it with a return type of numeric.

> Or I guess we could leave this as you've written it and add support
> for a cast from money to numeric.

I tried rewriting my function to use numeric, but I discovered that numeric division is not exact. (Otherwise SELECT 1::numeric / 3::numeric would result in an infinite loop.) So I went back to my float8 version and wrote a cast from money to numeric.

Here is my C source code now:

#include <postgres.h>
#include <fmgr.h>
#include <utils/cash.h>
#include <utils/numeric.h>
#include <utils/pg_locale.h>

PG_MODULE_MAGIC;

extern Datum int8_numeric(PG_FUNCTION_ARGS);
extern Datum numeric_div(PG_FUNCTION_ARGS);
extern Datum numeric_mul(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(cash_div_cash);

/* cash_div_cash()
* Divide cash by cash, returning float8.
*/
Datum
cash_div_cash(PG_FUNCTION_ARGS)
{
Cash dividend = PG_GETARG_CASH(0);
Cash divisor = PG_GETARG_CASH(1);
float8 quotient;

if (divisor == 0)
ereport(ERROR,
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg("division by zero")));

quotient = (float8)dividend / (float8)divisor;
PG_RETURN_FLOAT8(quotient);
}

PG_FUNCTION_INFO_V1(cash_numeric);

/* cash_numeric()
* Convert cash to numeric.
*/
Datum
cash_numeric(PG_FUNCTION_ARGS)
{
Cash money = PG_GETARG_CASH(0);
int fpoint;
int64 scale;
int i;
Numeric result;
Datum amount;
Datum numeric_scale;
Datum one;

struct lconv *lconvert = PGLC_localeconv();

/*
* Find the number of digits after the decimal point.
* (These lines were copied from cash_in().)
*/
fpoint = lconvert->frac_digits;
if (fpoint < 0 || fpoint > 10)
fpoint = 2;
scale = 1;
for (i = 0; i < fpoint; i++)
scale *= 10;

amount = DirectFunctionCall1(&int8_numeric, Int64GetDatum(money));
one = DirectFunctionCall1(&int8_numeric, Int64GetDatum(1));
numeric_scale = DirectFunctionCall1(&int8_numeric, Int64GetDatum(scale));
numeric_scale = DirectFunctionCall2(&numeric_div, one, numeric_scale);
result = DatumGetNumeric(DirectFunctionCall2(&numeric_mul, amount, numeric_scale));

result->n_sign_dscale = NUMERIC_SIGN(result) | fpoint; /* Display the right number of decimal digits. */

PG_RETURN_NUMERIC(result);
}

------------------------------------------------------------------------------------

And here is the SQL it takes to load it:

CREATE FUNCTION cash_div_cash(money, money) RETURNS double precision
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/divide_money', 'cash_div_cash';

CREATE FUNCTION cash_numeric(money) RETURNS numeric
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/divide_money', 'cash_numeric';

CREATE OPERATOR / (
PROCEDURE = cash_div_cash,
LEFTARG = money,
RIGHTARG = money
);

CREATE CAST (money AS numeric) WITH FUNCTION cash_numeric(money) AS ASSIGNMENT;

Andy Balholm <andy(at)balholm(dot)com> writes:
> I tried rewriting my function to use numeric, but I discovered that numeric division is not exact. (Otherwise SELECT 1::numeric / 3::numeric would result in an infinite loop.) So I went back to my float8 version and wrote a cast from money to numeric.

That's hardly an improvement if you're concerned about lack of
exactness.

regards, tom lane

> That's hardly an improvement if you're concerned about lack of
> exactness.

I know; I lose a couple of digits by using float8 instead of numeric, but it's much simpler and faster, and if it returned numeric people would _think_ it was exact.

And if we have a cast to numeric, people who want those extra digits can cast to numeric before dividing.

But I do still have the numeric code that I tried, so if that's how people want to do it, I can provide it.

Andy Balholm <andy(at)balholm(dot)com> wrote:

>> That's hardly an improvement if you're concerned about lack of
>> exactness.
>
> I know; I lose a couple of digits by using float8 instead of
> numeric, but it's much simpler and faster

It also has the advantage of being symmetrical with the other
operators.

> and if it returned numeric people would _think_ it was exact.

Well, I don't know how many people would expect an *exact* decimal
representation of dividing a number by three. The case which had me
concerned was specifically division by one as a "back door" cast.
With numeric we could guarantee *that* was exact.

> And if we have a cast to numeric, people who want those extra
> digits can cast to numeric before dividing.

And nobody has much reason to do the divide-by-one trick.

> But I do still have the numeric code that I tried, so if that's
> how people want to do it, I can provide it.

I'm inclined to think it's better to have an explicit cast from
money to numeric, as long as it is exact, and leave the division of
money by money as float8. It does sort of beg the question of
whether we should support a cast back in the other direction,
though. I think that would wrap this all up in a tidy package.

-Kevin

On Apr 1, 2010, at 7:57 AM, Kevin Grittner wrote:
> I'm inclined to think it's better to have an explicit cast from
> money to numeric, as long as it is exact, and leave the division of
> money by money as float8. It does sort of beg the question of
> whether we should support a cast back in the other direction,
> though. I think that would wrap this all up in a tidy package.

OK. Here is the whole thing in C:

#include <postgres.h>
#include <fmgr.h>
#include <utils/cash.h>
#include <utils/numeric.h>
#include <utils/pg_locale.h>

PG_MODULE_MAGIC;

extern Datum int8_numeric(PG_FUNCTION_ARGS);
extern Datum numeric_div(PG_FUNCTION_ARGS);
extern Datum numeric_mul(PG_FUNCTION_ARGS);
extern Datum numeric_int8(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(cash_div_cash);

/* cash_div_cash()
* Divide cash by cash, returning float8.
*/
Datum
cash_div_cash(PG_FUNCTION_ARGS)
{
Cash dividend = PG_GETARG_CASH(0);
Cash divisor = PG_GETARG_CASH(1);
float8 quotient;

if (divisor == 0)
ereport(ERROR,
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg("division by zero")));

quotient = (float8)dividend / (float8)divisor;
PG_RETURN_FLOAT8(quotient);
}

PG_FUNCTION_INFO_V1(cash_numeric);

/* cash_numeric()
* Convert cash to numeric.
*/
Datum
cash_numeric(PG_FUNCTION_ARGS)
{
Cash money = PG_GETARG_CASH(0);
int fpoint;
int64 scale;
int i;
Numeric result;
Datum amount;
Datum numeric_scale;
Datum one;

struct lconv *lconvert = PGLC_localeconv();

/*
* Find the number of digits after the decimal point.
* (These lines were copied from cash_in().)
*/
fpoint = lconvert->frac_digits;
if (fpoint < 0 || fpoint > 10)
fpoint = 2;
scale = 1;
for (i = 0; i < fpoint; i++)
scale *= 10;

amount = DirectFunctionCall1(&int8_numeric, Int64GetDatum(money));
one = DirectFunctionCall1(&int8_numeric, Int64GetDatum(1));
numeric_scale = DirectFunctionCall1(&int8_numeric, Int64GetDatum(scale));
numeric_scale = DirectFunctionCall2(&numeric_div, one, numeric_scale);
result = DatumGetNumeric(DirectFunctionCall2(&numeric_mul, amount, numeric_scale));

result->n_sign_dscale = NUMERIC_SIGN(result) | fpoint; /* Display the right number of decimal digits. */

PG_RETURN_NUMERIC(result);
}

PG_FUNCTION_INFO_V1(numeric_cash);

/* numeric_cash()
* Convert numeric to cash.
*/
Datum
numeric_cash(PG_FUNCTION_ARGS)
{
Datum amount = PG_GETARG_DATUM(0);
Cash result;
int fpoint;
int64 scale;
int i;
Datum numeric_scale;

struct lconv *lconvert = PGLC_localeconv();

/*
* Find the number of digits after the decimal point.
*/
fpoint = lconvert->frac_digits;
if (fpoint < 0 || fpoint > 10)
fpoint = 2;
scale = 1;
for (i = 0; i < fpoint; i++)
scale *= 10;

numeric_scale = DirectFunctionCall1(&int8_numeric, Int64GetDatum(scale));
amount = DirectFunctionCall2(&numeric_mul, amount, numeric_scale);
amount = DirectFunctionCall1(&numeric_int8, amount);

result = DatumGetInt64(amount);
PG_RETURN_CASH(result);
}

--------------------------------------------------------------------------------------

And the SQL to load it:

CREATE FUNCTION cash_div_cash(money, money) RETURNS double precision
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/divide_money', 'cash_div_cash';

CREATE FUNCTION cash_numeric(money) RETURNS numeric
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/divide_money', 'cash_numeric';

CREATE FUNCTION numeric_cash(numeric) RETURNS money
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/divide_money', 'numeric_cash';

CREATE CAST (money AS numeric) WITH FUNCTION public.cash_numeric(money) AS ASSIGNMENT;

CREATE CAST (numeric AS money) WITH FUNCTION public.numeric_cash(numeric) AS ASSIGNMENT;

CREATE OPERATOR / (
PROCEDURE = cash_div_cash,
LEFTARG = money,
RIGHTARG = money
);

Andy Balholm <andy(at)balholm(dot)com> wrote:

> OK. Here is the whole thing in C

Cool! I'll take it for a spin when I get a little time.

I guess there's not much point adding that TODO item now. ;-)

-Kevin

Chris Travers <chris(at)metatrontech(dot)com> writes:
> Ok. Here is my application: I write a multi-currency accounting
> program backed by PostgreSQL. After 1.3 is released (2Q this year),
> we expect to be doing a full redesign.
>
> What I am thinking about is having a custom data type, something like:

I'd be interested into participating into such a project, in particular
a multi-currencies support type coded in C would be useful for me.

> CREATE DOMAIN curr VARCHAR(3);
> CREATE TYPE monetary AS (amount NUMERIC, currency CURR, multiplier
> NUMERIC); This reduces into two basic components: a value (amount *
> multiplier) and a currency identifier (USD, etc).
>
> One could also then store monetary[] arrays for addressing specific
> denomination storage. I.e. "When closing the till we had 26 pennies,
> 53 nickles, 12 quarters, 25 $1 bills, 35 $5 bills, 15 $10 bills, and 5
> $20 bills."
>
> Then we can allow NUMERIC arithmetic on monetary amounts provided that
> the CURR field is the same. We could also store things like the cash
> counted from a till at the end of the day by denomination. One could
> have easy monetary::numeric casts as well.

Sounds a good starting point, but it sounds like we'll have to think
about it to see how it survive a more detailed approach.

> Anyway, that's my basic thinking. One could further add currency
> conversion tables to an application if necessary.

That's where it become interesting. Finding a nice way to solve the
problem of more than one currency in the same table, with dated
(timestamped?) conversion rates that are possibly unknown at INSERT
time…

--
dim

On Fri, Apr 2, 2010 at 9:51 AM, Dimitri Fontaine <dfontaine(at)hi-media(dot)com> wrote:

>> One could also then store monetary[] arrays for addressing specific
>> denomination storage.  I.e. "When closing the till we had 26 pennies,
>> 53 nickles, 12 quarters, 25 $1 bills, 35 $5 bills, 15 $10 bills, and 5
>> $20 bills."
>>
>> Then we can allow NUMERIC arithmetic on monetary amounts provided that
>> the CURR field is the same.  We could also store things like the cash
>> counted from a till at the end of the day by denomination.  One could
>> have easy monetary::numeric casts as well.
>
> Sounds a good starting point, but it sounds like we'll have to think
> about it to see how it survive a more detailed approach.

Sure. See below.
>
>> Anyway, that's my basic thinking.  One could further add currency
>> conversion tables to an application if necessary.
>
> That's where it become interesting. Finding a nice way to solve the
> problem of more than one currency in the same table, with dated
> (timestamped?) conversion rates that are possibly unknown at INSERT
> time…

Well, you have another problem (this may be wandering far afield from
the original question but here it goes):

Suppose I live in Canada and I have two checking accounts for my
business, one in CAD and one in USD. In essence I have to account for
a floating balance of a foreign currency. Consequently, I don't think
you can just suggest "convert at insert time" as a way to handle that.
In cases where you do (payments converted on deposit), that's a subset
of the more general problem, which is converting at an arbitrary point
in time. However, even where you do (suppose instead I live in the US
and someone pays me in CAD), there is no guarantee that the conversion
rate when you enter the payment into your system as received and when
you convert it is the same. The check could be deposited the next
day, for example and converted at that point. OTOH, if it is an
incoming wire transfer in AUD, I would expect it to be converted on
receipt.

So conversion between currencies is something which has to be done at
a specified point in time. While some of this could be automated to
an extent, it would really be business-specific.

In essence, I think you would need a function like
convert_currency(source monetary, target curr, date) to do the
conversion. Furthermore this would require currency tables, and would
be probably outside the core data type definition.

In essence, to handle exchange rates, I think you would need
additional tables and the like, and UDF's to do the actual
conversions. For simplicity's sake, I think this would be broken off
into a separate module although I would be happy to collaborate on
that as well.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, Apr 02, 2010 at 10:18:24AM -0700, Chris Travers wrote:

> Suppose I live in Canada and I have two checking accounts for my
> business, one in CAD and one in USD. In essence I have to account for
> a floating balance of a foreign currency [...]
> there is no guarantee that the conversion
> rate when you enter the payment into your system as received and when
> you convert it is the same. The check could be deposited the next
> day, for example and converted at that point.

It isn't even clear that the exchange rate for a given point in time is
well-defined. Typically you get differing exchange rates depending on
where (and how much!) you try to realize the conversion.

> So conversion between currencies is something which has to be done at
> a specified point in time. While some of this could be automated to
> an extent, it would really be business-specific.
>
> In essence, I think you would need a function like
> convert_currency(source monetary, target curr, date) to do the
> conversion. Furthermore this would require currency tables, and would
> be probably outside the core data type definition.
>
> In essence, to handle exchange rates, I think you would need
> additional tables and the like, and UDF's to do the actual
> conversions. For simplicity's sake, I think this would be broken off
> into a separate module although I would be happy to collaborate on
> that as well.

Hm. An expert would have to decide whether such a simplification is
useful (it could, e.g. help in estimating the money amount held in
different currencies at some point in time) -- but some exchange rate
seems to be well-defined only when you actually *do* the conversion.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFLtkjNBcgs9XrR2kYRAr++AJwKkDt6/RBbfK/KOz8ZNIK4RjYy6QCcCPPK
S+gOzayBMJh+2n9sFgbTI64=
=oeqv
-----END PGP SIGNATURE-----

tomas(at)tuxteam(dot)de writes:
> On Fri, Apr 02, 2010 at 10:18:24AM -0700, Chris Travers wrote:
>
>> Suppose I live in Canada and I have two checking accounts for my
>> business, one in CAD and one in USD. In essence I have to account for
>> a floating balance of a foreign currency [...]
>> there is no guarantee that the conversion
>> rate when you enter the payment into your system as received and when
>> you convert it is the same. The check could be deposited the next
>> day, for example and converted at that point.
>
> It isn't even clear that the exchange rate for a given point in time
> is well-defined. Typically you get differing exchange rates depending
> on where (and how much!) you try to realize the conversion.

Indeed.

You can only be certain of there actually being a conversion if the
transaction directly involved a conversion between currencies.

Thus...

1. If I buy materials using $USD on the $USD checking account, it's not
evident what conversion *ever* takes place for this transaction.

Expressing that transaction in $CDN will *always* reflect an
estimate, never a "reality."

2. In contrast, a funds transfer from the $CDN account to the $USD
account will indicate some kind of "spot rate" because there will be
two specific amounts:

- The amount of $CDN currency taken out of the one account, and
- The amount of $USD currency put into the other account.

You may or may not know both values immediately; as Chris Travers
observes, there may be some time separation.

It seems like an awfully bad idea to try to model this as if you
immediately know the exchange rate at the time the transaction is
recorded.

To the contrary, it seems to me that rate conversion shouldn't be
treated as being at all tightly integrated into this.

I actually have a similar situation to this, albeit not for business; I
have a $USD denominated account that earns interest.

I am expected to report on interest earnings on an annual basis by the
tax authorities. There tend to be three approaches considered readily
acceptable:

1. Use an annual average exchange rate (I presume it's a geometric
mean, but am not sure) on a total amount.

This is the easiest, and is what I do.

2. Use monthly average exchange rates, applying the appropriate
one to each month's earnings.

3. Use spot rates as reported by an authority, applying them to each
transaction. (For a bank account, this is actually pretty nearly
equivalent to #2, just with a different way of picking the
exchange rate!)

The fact that there are multiple policies like this points to the
conclusion that it's inappropriate to try to capture exchange rates as
something tightly coupled inside each transactions. It's something
you'd want to have the option to change later, because the reporting
policy could well change.

>> So conversion between currencies is something which has to be done at
>> a specified point in time. While some of this could be automated to
>> an extent, it would really be business-specific.
>>
>> In essence, I think you would need a function like
>> convert_currency(source monetary, target curr, date) to do the
>> conversion. Furthermore this would require currency tables, and
>> would be probably outside the core data type definition.
>>
>> In essence, to handle exchange rates, I think you would need
>> additional tables and the like, and UDF's to do the actual
>> conversions. For simplicity's sake, I think this would be broken off
>> into a separate module although I would be happy to collaborate on
>> that as well.
>
> Hm. An expert would have to decide whether such a simplification is
> useful (it could, e.g. help in estimating the money amount held in
> different currencies at some point in time) -- but some exchange rate
> seems to be well-defined only when you actually *do* the conversion.

My very little bit of exchange rate conversion takes place the day when
I'm working on my tax return :-).

There's some use in having a convenient way to capture conversion rates,
to help with the analysis, but it isn't necessarily tied to the
transactions themselves. And there's more information that likely needs
to get captured, such as whether the rate reflects:

- Actual conversion that took place (e.g. - as happens when you
transfer between your own accounts denominated in different
currencies)

- Spot rates published by financial institutions

- Average rates (for some form of "average", for some period of time),
as published.

There may be more kinds of conversion rates than those three, but I know
those three are of interest.
--
"The real romance is out ahead and yet to come. The computer
revolution hasn't started yet. Don't be misled by the enormous flow of
money into bad defacto standards for unsophisticated buyers using poor
adaptations of incomplete ideas." -- Alan Kay

Hi Chris,

Many thanks for your comments.

On Mon, Apr 5, 2010 at 10:16 AM, Chris Browne <cbbrowne(at)acm(dot)org> wrote:

> Indeed.
>
> You can only be certain of there actually being a conversion if the
> transaction directly involved a conversion between currencies.
>
> Thus...
>
>  1. If I buy materials using $USD on the $USD checking account, it's not
>    evident what conversion *ever* takes place for this transaction.

Right. That's a major problem with the way LSMB (and SQL-Ledger)
currently handle this. There are plenty of other issues that come up
here as well....
>
>    Expressing that transaction in $CDN will *always* reflect an
>    estimate, never a "reality."

Furthermore, since there is no conversion, there isn't any realized fx
gain or loss. In other words, any fx gain or loss is a mere estimate
necessary for accounting reports. IMO, any estimated unrealized gains
or losses should be dynamically calculated anyway.
>
>  2. In contrast, a funds transfer from the $CDN account to the $USD
>    account will indicate some kind of "spot rate" because there will be
>    two specific amounts:
>
>    - The amount of $CDN currency taken out of the one account, and
>    - The amount of $USD currency put into the other account.
>
>    You may or may not know both values immediately; as Chris Travers
>    observes, there may be some time separation.
>
> It seems like an awfully bad idea to try to model this as if you
> immediately know the exchange rate at the time the transaction is
> recorded.

Thinking through this further, I have concluded that at least two ways
are necessary:

1) Conversion of currencies as an intrinsic process at a known rate.
I.e. if I transfer money from a USD to a CDN account, and I have the
numbers and the rates, I tell it the appropriate rate.

2) Conversion of currencies as an extrinsic process at a discovered
rate. I.e. if I am running an income statement for 2009, I look up
rates for converting my totals from USD to CDN at the end points and
calculate estimated, unrealized fx gains and losses on that basis.

My initial reasoning was different, namely that data types shouldn't
depend on database tables to be usable. However, this then squarely
addresses the other concern. So I suppose that's a good thing :-)

Obviously any extrinsic elements shouldn't be tightly coupled with the
intrinsic elements (meaning you have monetary types with intrinsic
operators and functions, and then you have a separate, optional
business logic module which can provide those extrinsic elements along
with tables to store the values).
>
> To the contrary, it seems to me that rate conversion shouldn't be
> treated as being at all tightly integrated into this.

Agreed. If you'd be interested in seeing the first draft of the spec
I came up with, I would be happy to forward it along. A few things in
it will need to be changed due to what you have noted regarding looked
up exchange rates, but the basic type definitions and base functions
seem solid.
>
> I actually have a similar situation to this, albeit not for business; I
> have a $USD denominated account that earns interest.
>
> I am expected to report on interest earnings on an annual basis by the
> tax authorities.  There tend to be three approaches considered readily
> acceptable:
>
>  1.  Use an annual average exchange rate (I presume it's a geometric
>      mean, but am not sure) on a total amount.
>
>      This is the easiest, and is what I do.

This is a published rate by the tax authorities?
>
>  2.  Use monthly average exchange rates, applying the appropriate
>      one to each month's earnings.

Are these published as well by tax authorities?
>
>  3.  Use spot rates as reported by an authority, applying them to each
>      transaction.  (For a bank account, this is actually pretty nearly
>      equivalent to #2, just with a different way of picking the
>      exchange rate!)
>
> The fact that there are multiple policies like this points to the
> conclusion that it's inappropriate to try to capture exchange rates as
> something tightly coupled inside each transactions.  It's something
> you'd want to have the option to change later, because the reporting
> policy could well change.

Right. There's also the following issue:
1) I send an invoice in CDN
2) 1 month later that invoice is paid in CDN.
3) I have to report relavant fx gains and losses.

The only way I can see of doing this is to look up an accepted rate as
of date of invoice, convert that, and then use the spot rate on the
conversion of the payment. So one has (in this case) a realized gain
or loss which is in part estimated (and extrinsic to the "conversion"
which isn't "real) and in part known (and intrinsic to a real
conversion). If conversions at specified rates are seen as a part of
the core module, then any lookup logic can be neatly uncoupled :-).

>> Hm. An expert would have to decide whether such a simplification is
>> useful (it could, e.g. help in estimating the money amount held in
>> different currencies at some point in time) -- but some exchange rate
>> seems to be well-defined only when you actually *do* the conversion.
>
> My very little bit of exchange rate conversion takes place the day when
> I'm working on my tax return :-).
>
> There's some use in having a convenient way to capture conversion rates,
> to help with the analysis, but it isn't necessarily tied to the
> transactions themselves.  And there's more information that likely needs
> to get captured, such as whether the rate reflects:
>
>  - Actual conversion that took place (e.g. - as happens when you
>   transfer between your own accounts denominated in different
>   currencies)
>
>  - Spot rates published by financial institutions
>
>  - Average rates (for some form of "average", for some period of time),
>   as published.
>
> There may be more kinds of conversion rates than those three, but I know
> those three are of interest.

Hmm... Back to the drawing board on that helper module :-).

Best Wishes,
Chris Travers

Chris Travers wrote:
> Hmm... Back to the drawing board on that helper module :-).
>

like I said, its a big tarpit.

John R Pierce <pierce(at)hogranch(dot)com> wrote:
> Chris Travers wrote:
>> Hmm... Back to the drawing board on that helper module :-).
>
> like I said, its a big tarpit.

If you start up on this again, you might want to start a new thread
with a more descriptive subject. Those who weren't interested in
Andy's issue might not be following your discussion. The proposed
new types and conversion capabilities really have nothing to do
with the original topic, which makes it kinda confusing.

-Kevin

On Mon, Apr 5, 2010 at 4:37 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> John R Pierce <pierce(at)hogranch(dot)com> wrote:
>> Chris Travers wrote:
>>> Hmm... Back to the drawing board on that helper module :-).
>>
>> like I said, its a big tarpit.
>
> If you start up on this again, you might want to start a new thread
> with a more descriptive subject.  Those who weren't interested in
> Andy's issue might not be following your discussion.  The proposed
> new types and conversion capabilities  really have nothing to do
> with the original topic, which makes it kinda confusing.

Yeah - it should probably be on -hackers, too, not here.

...Robert