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

tomas@tuxteam.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