Re: [BUGS] dividing money by money
to...@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 -- Sent via pgsql-bugs mailing list (pgsq
Re: [BUGS] dividing money by money
Hi Chris, Many thanks for your comments. On Mon, Apr 5, 2010 at 10:16 AM, Chris Browne 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 hel
Re: [BUGS] dividing money by money
Chris Travers wrote: Hmm... Back to the drawing board on that helper module :-). like I said, its a big tarpit. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] dividing money by money
John R Pierce 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] dividing money by money
On Mon, Apr 5, 2010 at 4:37 PM, Kevin Grittner wrote: > John R Pierce 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] regexp_matches illegally restricts rows
Severity: major (data loss)
Versions Tested: 8.4.2, 9.0 HEAD
Test Case:
create table regex_test ( id serial not null primary key, myname text );
insert into regex_test ( myname )
values ( 'josh'),('joe'),('mary'),('stephen'), ('jose'),
('kelley'),('alejandro');
select id, regexp_matches(myname, $x$(j[\w]+)$x$)
from regex_test;
The above will return 4 rows, not the 7 which are in the table.
I can't see how this is anything but a bug; as far as I know, nothing in
the target list is allowed to restrict the number of rows which are
returned by the query. We should get 7 rows, 3 of which have an empty
array or a NULL in the 2nd column.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Re: regexp_matches illegally restricts rows -- just a documentation issue?
On 4/5/10 9:16 PM, Josh Berkus wrote: > I can't see how this is anything but a bug; as far as I know, nothing in > the target list is allowed to restrict the number of rows which are > returned by the query. We should get 7 rows, 3 of which have an empty > array or a NULL in the 2nd column. Just noticed it's a SETOF[] function. Which makes it odd that I can call it in the target list at all, but explains the row restriction. It's still confusing behavior (three regulars on IRC thought it was a bug too) and users should be warned in the documentation. Not sure exactly where, though ... maybe in 9.7? --Josh Berkus -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
