Thread: Multiple currencies in a application
I'm working on an application that will eventually need to support various currencies. I've never touched this area before, have dealt with dollars till now. I'm not sure what the regular practices are re mulitple currencies in the same application. The app includes calculations like price per unit, which of course involves division and thus fractional monetary units. I'm more concerned about that stuff than formatting/presentation. Users of the app will be able to identify the currency that is relevant for their use, and each individual user won't be using more than one currency. Do I handle all currency calcs in functions that adjust for currency? -- View this message in context: http://www.nabble.com/Multiple-currencies-in-a-application-tf2605959.html#a7271737 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Bumping this in hopes that someone can give me a bit of input? novnov wrote: > > I'm working on an application that will eventually need to support various > currencies. I've never touched this area before, have dealt with dollars > till now. I'm not sure what the regular practices are re mulitple > currencies in the same application. > > The app includes calculations like price per unit, which of course > involves division and thus fractional monetary units. I'm more concerned > about that stuff than formatting/presentation. > > Users of the app will be able to identify the currency that is relevant > for their use, and each individual user won't be using more than one > currency. > > Do I handle all currency calcs in functions that adjust for currency? > -- View this message in context: http://www.nabble.com/Multiple-currencies-in-a-application-tf2605959.html#a7485709 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
novnov wrote:
> Bumping this in hopes that someone can give me a bit of input?
Wow, nobody replied?...
> novnov wrote:
>> I'm working on an application that will eventually need to support various
>> currencies. I've never touched this area before, have dealt with dollars
>> till now. I'm not sure what the regular practices are re mulitple
>> currencies in the same application.
>>
>> The app includes calculations like price per unit, which of course
>> involves division and thus fractional monetary units. I'm more concerned
>> about that stuff than formatting/presentation.
>>
>> Users of the app will be able to identify the currency that is relevant
>> for their use, and each individual user won't be using more than one
>> currency.
>>
>> Do I handle all currency calcs in functions that adjust for currency?
A few things you'll probably want:
- Store prices in your db with their original currency
- Make sure you have up-to-date conversion rates (how up to date that
needs to be is up to you)
- Calculate actual prices on demand
We are satisfied with daily updates to our conversion rates, which we
store in a table. Conversion isn't too difficult that way.
Say you want to convert the price of a product from dollars (the
original currency) to euros, your query would look something like this:
SELECT price * target.rate / source.rate
FROM products
INNER JOIN rates source ON (products.currency = source.currency),
rates target
WHERE products.id = 1234
AND target.currency = 'euro';
I don't think you'll need any functions, unless to retrieve real-time
conversion rates somehow. Otherwise a cron job will do nicely.
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
Alban Hertroys <alban@magproductions.nl> writes: > A few things you'll probably want: > - Store prices in your db with their original currency > - Make sure you have up-to-date conversion rates (how up to date that > needs to be is up to you) > - Calculate actual prices on demand - Keep the highest precision on prices you can, if you can get sub-cents its better You'll have to worry with rounding / truncation rules (they differ from country to country so you'll have to either state what you do and why this might not be accurate or you'll have to code it some way that you can do the right thing all the time...). If you store up to cents, then you might end up loosing money or charging too much. If you can get to the fourth decimal place you'll have a safe zone to work with cents if you deal with a few thousands units of the product. The more you sell / buy, the more decimal places would be interesting to have. Of course, I'm supposing that cents are important, if you're selling / buying products that cost thousands or millions of <currency here>, then this looses that importance (this is relative to how much monetary units the value represents in the destination currency). > We are satisfied with daily updates to our conversion rates, which we > store in a table. Conversion isn't too difficult that way. With webservices you can get almost real time rates and you don't even have to store these rates on your database (even though it is nice to have it for summaries and reports and also for auditing operations). > Say you want to convert the price of a product from dollars (the > original currency) to euros, your query would look something like this: > > SELECT price * target.rate / source.rate > FROM products > INNER JOIN rates source ON (products.currency = source.currency), > rates target > WHERE products.id = 1234 > AND target.currency = 'euro'; > > I don't think you'll need any functions, unless to retrieve real-time > conversion rates somehow. Otherwise a cron job will do nicely. Yep... There are some apps that request for the rate to be used when you login (they usually suppose you'll be working with two currencies: a reference currency and a local currency). I've also seem projects that have an artifical value for each product and then apply conversion rates from this value to any currency they want. Something like making 1 unit equal to 10 cents, so a product that costs 10 <currency> would be stored as costing "100 units". Then you just have to have a conversion table from the basic unit value to the currency you want to deal with. This makes it easier to update prices and do some historical analisys besides making it easier to make a comparative analisys of each market. I've also seen systems where each "currency" (local, actually) can have a different price. It makes a lot of sense since paying something like 2 dollars for a coffee on the US doesn't sound all that much but it would be a robbery here in Brazil if the value of such product was converted to reais :-) Last but not less important you should also consider how you're going to add / represent S&H costs, import / export taxes, etc. and how this will impact on the value you'll be showing to the user. This is just the tip of the iceberg, but I hope it helps a little to see what is important or not for you. -- Jorge Godoy <jgodoy@gmail.com>
Thanks to the three of you for your thoughts, those are very very helpful perspectives that are going to help me design this. One note, I won't have to worry about multiple currencies on the internal bookeeping side of things, which is a major plus. Jorge Godoy-2 wrote: > > Alban Hertroys <alban@magproductions.nl> writes: > >> A few things you'll probably want: >> - Store prices in your db with their original currency >> - Make sure you have up-to-date conversion rates (how up to date that >> needs to be is up to you) >> - Calculate actual prices on demand > > - Keep the highest precision on prices you can, if you can get sub-cents > its > better > > You'll have to worry with rounding / truncation rules (they differ from > country to country so you'll have to either state what you do and why this > might not be accurate or you'll have to code it some way that you can do > the > right thing all the time...). > > If you store up to cents, then you might end up loosing money or charging > too > much. > > If you can get to the fourth decimal place you'll have a safe zone to work > with cents if you deal with a few thousands units of the product. The > more > you sell / buy, the more decimal places would be interesting to have. Of > course, I'm supposing that cents are important, if you're selling / buying > products that cost thousands or millions of <currency here>, then this > looses > that importance (this is relative to how much monetary units the value > represents in the destination currency). > >> We are satisfied with daily updates to our conversion rates, which we >> store in a table. Conversion isn't too difficult that way. > > With webservices you can get almost real time rates and you don't even > have to > store these rates on your database (even though it is nice to have it for > summaries and reports and also for auditing operations). > >> Say you want to convert the price of a product from dollars (the >> original currency) to euros, your query would look something like this: >> >> SELECT price * target.rate / source.rate >> FROM products >> INNER JOIN rates source ON (products.currency = source.currency), >> rates target >> WHERE products.id = 1234 >> AND target.currency = 'euro'; >> >> I don't think you'll need any functions, unless to retrieve real-time >> conversion rates somehow. Otherwise a cron job will do nicely. > > Yep... There are some apps that request for the rate to be used when you > login (they usually suppose you'll be working with two currencies: a > reference > currency and a local currency). > > > I've also seem projects that have an artifical value for each product and > then > apply conversion rates from this value to any currency they want. > Something > like making 1 unit equal to 10 cents, so a product that costs 10 > <currency> > would be stored as costing "100 units". Then you just have to have a > conversion table from the basic unit value to the currency you want to > deal > with. > > This makes it easier to update prices and do some historical analisys > besides > making it easier to make a comparative analisys of each market. > > > I've also seen systems where each "currency" (local, actually) can have a > different price. It makes a lot of sense since paying something like 2 > dollars for a coffee on the US doesn't sound all that much but it would be > a > robbery here in Brazil if the value of such product was converted to reais > :-) > > > Last but not less important you should also consider how you're going to > add / > represent S&H costs, import / export taxes, etc. and how this will impact > on > the value you'll be showing to the user. > > > This is just the tip of the iceberg, but I hope it helps a little to see > what > is important or not for you. > > -- > Jorge Godoy <jgodoy@gmail.com> > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > > -- View this message in context: http://www.nabble.com/Multiple-currencies-in-a-application-tf2605959.html#a7498357 Sent from the PostgreSQL - general mailing list archive at Nabble.com.