Multiple currencies support across the database

[Thomasso]

Hi all,



I have been trying to figure out the best way to build a database for my company. I can't seem to figure out how to deal with currencies.

Here is our business model:
We are based in Europe, so our main currency is EUR. But we have suppliers from different areas (USA, they're selling for USD... Switzerland > CHF... Great Britain > GBP) and so on.
I need to have the purchase prices in the correct currency (including field format), then all products will have our own UnitPrice, which we sell the product for (in our main currency EUR) and I also need to do conversions. For example when I pay an invoice to my American supplier, I want to be able to type in the USD amount and the actual exchange rate and have the profits calculated in EUR based on all this.

What would be the best way to approach this?

Thanks in advance for the help.

Best regards,
Tomas

[Minty]

You store a currency field with the supplier / customer.

Your Unit price will I assume be based on a buying price, that buying price should be stored.
There are two ways to store the FX rates : with a date stamp so you know what they were at that time this will give you a historic record, your business rules will dictate if you update it daily, weekly or monthly.
Alternatively directly store the FX rate in the transaction record, buy amount, Buy currency , EuroFXRate as it's your base currency.

This way you can always calculate the rest out.

It is a level of complication that make life a little more interesting.

[Thomasso]

Thanks. So I guess that I should store all financial data as Double, including the base currency, and use the Format() function everywhere?

[Minty]

Yes - always have the currency field as a separate field. Number need to be stored as Numbers.
I wouldn't use double, use decimal (18,4) to avoid rounding issues.

You can add the currency symbol in reports etc as a final formatted output, or better still just include the relevant currency field, if you format it as £1233.23 or $3214.56 you'll get problems if you export to excel, with them being treated as text.

[CJ_London]

when you say profits - do you mean as a general guide to management or do you mean for published accounts? if the latter, you will need to know the exchange rate at year end and any other legally required reporting points so that stock can be revalued based on the latest exchange rate, giving you a profit/loss on exchange. But that does depend on your reporting rules

Other thing to consider is costs of exchange which are typically based on the transaction value and not built into the exchange rate.

[Thomasso]

Yes - always have the currency field as a separate field. Number need to be stored as Numbers.
I wouldn't use double, use decimal (18,4) to avoid rounding issues.

You can add the currency symbol in reports etc as a final formatted output, or better still just include the relevant currency field, if you format it as £1233.23 or $3214.56 you'll get problems if you export to excel, with them being treated as text.

Ok thanks. Or I could have raw data in my tables (like Amount=123.45, Currency=USD) and then display everything correctly on forms and reports (If Currency=USD Then Format= "$" & Amount) ... And so on (this is a simplified version of course :-)

when you say profits - do you mean as a general guide to management or do you mean for published accounts? if the latter, you will need to know the exchange rate at year end and any other legally required reporting points so that stock can be revalued based on the latest exchange rate, giving you a profit/loss on exchange. But that does depend on your reporting rules

Other thing to consider is costs of exchange which are typically based on the transaction value and not built into the exchange rate.

It's actually the former. It's just as a guideline, no rules really. We have our accounts handled externally. But thanks for pointing that out.

[Micron]

Don't know if these issues would be relevant here or even if they still exist wrt double vs decimal.

https://www.fmsinc.com/microsoftacce...type/index.htm

[Minty]

@Micron, that's interesting - I guess because I use SQL server mainly I haven't seen those issues.
I wonder if they are fixed in later versions... Those are very old screen shots.

I'll see if I have time for a quick mess about.

[Minty]

They appear (at least in O365) to sort correctly


BillValue is sorted Descending.

[Micron]

Yes, they are from a time when you could actually choose a colour scheme that had some flair.
Wouldn't your data need some zeros and negative numbers to be conclusive? Perhaps using a query avoids the issue as well (they are referring to tables).

[Minty]

@Micron, good point - I have just tried it with a bigger sample of negative, zero and positive numbers and it sorts fine directly in the table and a query.

Billing_ID	BillingDate	Fee	WriteOff
1002		210	0
2302		0	0
2135		85	-0.17
2796		71	-0.174
1599		93.75	-0.42
1469		55	-0.5
166		50	-0.83
38		40	-0.83
395		35	-1.66
387		35	-1.66

Billing_ID	BillingDate	Fee	WriteOff
126		25
801		165
1988		760	-760
43		1425	-624.33
946		1425	-526.99
1390		1425	-500
1640		1425	-444.66
618		1425	-390.16
2643		1425	-353.59

Billing_ID	BillingDate	Fee	WriteOff
2584		412.75	270.81
2284		25	270.24
484		135	270
2260		106.25	269.67
289		186.25	269.59
644		160	269.33
1066		75	269.17
2397		272	268.24

[Micron]

It would seem that at least the sorting part of that article no longer applies - assuming that your first table is sorted descending. I just tested for constant declaration and that still appears to be true. I don't have any decimal data to test the export issue; possibly because I've avoided the data type for many years because of that article. There is a link there to Allen Browne site where he adds that there is some sort of issue using decimals in aggregate queries but he doesn't expound on that.

Why would you not use Currency data type for financial numbers? IIRC, it has 15 places to left of point and 4 to the right yet it is still an integer (scaled integer) which means it's not supposed to be affected by rounding?

[Minty]

It would seem that at least the sorting part of that article no longer applies - assuming that your first table is sorted descending. I just tested for constant declaration and that still appears to be true. I don't have any decimal data to test the export issue; possibly because I've avoided the data type for many years because of that article. There is a link there to Allen Browne site where he adds that there is some sort of issue using decimals in aggregate queries but he doesn't expound on that.

Why would you not use Currency data type for financial numbers? IIRC, it has 15 places to left of point and 4 to the right yet it is still an integer (scaled integer) which means it's not supposed to be affected by rounding?

I use Currency all the time in local tables where appropriate, but have had some "interesting" issues with the Money data type in SQL server, hence switching to decimal.
Also Currency forces the local currency symbol onto the data which particularly in the OP's case is an undesired "feature".