Re: dividing money by money - Mailing list pgsql-bugs

Hi Chris,

Many thanks for your comments.

On Mon, Apr 5, 2010 at 10:16 AM, Chris Browne <cbbrowne@acm.org> wrote:

> Indeed.
>
> You can only be certain of there actually being a conversion if the
> transaction directly involved a conversion between currencies.
>
> Thus...
>
> =A01. If I buy materials using $USD on the $USD checking account, it's not
> =A0 =A0evident 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....
>
> =A0 =A0Expressing that transaction in $CDN will *always* reflect an
> =A0 =A0estimate, 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.
>
> =A02. In contrast, a funds transfer from the $CDN account to the $USD
> =A0 =A0account will indicate some kind of "spot rate" because there will =
be
> =A0 =A0two specific amounts:
>
> =A0 =A0- The amount of $CDN currency taken out of the one account, and
> =A0 =A0- The amount of $USD currency put into the other account.
>
> =A0 =A0You may or may not know both values immediately; as Chris Travers
> =A0 =A0observes, 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. =A0There tend to be three approaches considered readily
> acceptable:
>
> =A01. =A0Use an annual average exchange rate (I presume it's a geometric
> =A0 =A0 =A0mean, but am not sure) on a total amount.
>
> =A0 =A0 =A0This is the easiest, and is what I do.

This is a published rate by the tax authorities?
>
> =A02. =A0Use monthly average exchange rates, applying the appropriate
> =A0 =A0 =A0one to each month's earnings.

Are these published as well by tax authorities?
>
> =A03. =A0Use spot rates as reported by an authority, applying them to each
> =A0 =A0 =A0transaction. =A0(For a bank account, this is actually pretty n=
early
> =A0 =A0 =A0equivalent to #2, just with a different way of picking the
> =A0 =A0 =A0exchange 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. =A0It'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. =A0And there's more information that likely needs
> to get captured, such as whether the rate reflects:
>
> =A0- Actual conversion that took place (e.g. - as happens when you
> =A0 transfer between your own accounts denominated in different
> =A0 currencies)
>
> =A0- Spot rates published by financial institutions
>
> =A0- Average rates (for some form of "average", for some period of time),
> =A0 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