Storing Money Using Int64 and Multiple Currencies

General
1

Hi,
I would like to store money as Integer64 in an SQLite database to avoid potential rounding errors. I do not want to use the Currency data type.

In the app, the user may change currencies depending on the record being created, so I cannot rely the users locale for formatting. The app will only support currencies with 2 decimal places.

  1. Since I can’t rely on locale, what’s the best way to modify my CurrencyVal function so that it respects when a decimal point or comma is used as a decimal separator?
  2. Is it ok to give the Format function an Int64?
  3. Why does my current code format Euros and Pounds as .00€000 and 0£0.00 for a value of 0? Yet the formats for other territories work fine. Is this a bug?
  4. Is there a better way to do this? I was wondering if my assumptions in my code are correct and would greatly appreciate any critiquing.

Thank you

Example from the LostFocus event of a TextField:

me.text = CurrencyFormat(me.text.CurrencyVal, theCurrencyCode)

[code]Function CurrencyVal(Extends s As String) As Int64
// Takes a string and gets rid of any character that’s not a number, comma or decimal point.
// This includes removing spaces as some currencies use a space as the thousands separator.
// Return the result passed through CDbl since CDbl is international-savvy and will recognize
// which character is the decimal separator and if that separator is a decimal point or a comma.
// CDbl converts the string based on the user’s local numeric format settings.
// EDIT: Cant rely on CDbl

dim i as Integer
dim t as String
Dim ch As String

for i = 1 to len( s )
ch = Mid(s, i, 1)
if isnumeric( ch ) _
or ( ch = “.” ) _
or ( ch = “,” ) _
then t = t + ch
next i

// CDbl will return just the numeric form of the currency string with
// decimal separator in place and any thousands separators removed.
// Multiply by 1000 to remove decimal and return an Integer64.
return t.CDbl * 1000
End Function[/code]

[code]Function CurrencyFormat(amt as Int64, code as String) As String

// Divide by 1000 to get back the decimal
amt = amt / 1000

select case code

case kCurCodeUSD // US, Canada, Canada French
// Decimal Point: . Example: 1,234.12
// Local currency: $ International currency: USD
return Format( amt, kCurFormatUSD )

case kCurCodeAUD // Australia
// Decimal Point: . Example: 1 234.12
// Local currency: $ International currency: AUD
return Format( amt, kCurFormatAUD )

case kCurCodeEUR // Austria, Belgium French, France, Germany, Italy, Belgium Dutch, Netherlands, Spain, Ireland
// Decimal Point: . Example: 1,234.12
// Local currency: € International currency: EUR
return Format( amt, kCurFormatEURO )

case kCurCodeCHF // Switzerland
// Decimal Point: , Example: 1.234,12
// Local currency: SF International currency: CHF
return Format( amt, kCurFormatCHF )

case kCurCodeGBP // UK
// Decimal Point: . Example: 1,234.123
// Local currency: ? International currency: GBP
return Format( amt, kCurFormatGBP )
else
return Format( amt, “###,##0.00;-###,##0.00;###,##0.00” )
end select

End Function[/code]

Const kCurFormatUSD = "\\$###,##0.00;-\\$###,##0.00;\\$###,##0.00" Const kCurFormatAUD = "\\A\\U\\D\\ ###\\ ##0.00;\\A\\U\\D\\ -###\\ ##0.00;\\A\\U\\D\\ ###\\ ##0.00" Const kCurFormatEURO = "\\€###,##0.00;-\\€###,##0.00;\\€###,##0.00" Const kCurFormatCHF = "\\C\\H\\F\\ ###.##0,00;\\C\\H\\F\\ -###.##0,00;\\C\\H\\F\\ ###.##0,00" Const kCurFormatGBP = "\\£###,##0.00;-\\£###,##0.00;\\£###,##0.00"

2

Certainly odd.
But as far as I can tell, your formatting needs are just this:

Const kCurFormatGBP = “-\£###,##0.00”

And in your shoes, I would probably have kept the currency character out of it, like this:

case kCurCodeGBP // UK
// Decimal Point: . Example: 1,234.123
// Local currency: ? International currency: GBP

return “£” + Format( amt, “-###,##0.00” )

because if you do, kCurFormatGBP and USD and EUR are all the same, aren’t they?

3

I think your format codes are wrong. You have:

Const kCurFormatUSD = "\\$###,##0.00;-\\$###,##0.00;\\$###,##0.00" Const kCurFormatAUD = "\\A\\U\\D\\ ###\\ ##0.00;\\A\\U\\D\\ -###\\ ##0.00;\\A\\U\\D\\ ###\\ ##0.00" Const kCurFormatEURO = "\\€###,##0.00;-\\€###,##0.00;\\€###,##0.00" Const kCurFormatCHF = "\\C\\H\\F\\ ###.##0,00;\\C\\H\\F\\ -###.##0,00;\\C\\H\\F\\ ###.##0,00" Const kCurFormatGBP = "\\£###,##0.00;-\\£###,##0.00;\\£###,##0.00"
The Format function takes three parameters:

  1. The positive format
  2. The negative format
  3. The zero value format

I think your formats should be:

Const kCurFormatUSD = "\\$#,###,##0.00;-\\$#,###,##0.00;\\ " Const kCurFormatAUD = "\\A\\U\\D\\ ###\\ ##0.00;\\A\\U\\D\\ -###\\ ##0.00;\\ " Const kCurFormatEURO = "\\€#,###,##0.00;-\\€#,###,##0.00;\\€###,##0.00" Const kCurFormatCHF = "\\C\\H\\F\\ #,###.##0,00;\\C\\H\\F\\ -#,###.##0,00;\\ " Const kCurFormatGBP = "\\£#,###,##0.00;-\\£#,###,##0.00;\\ "
Out of interest I write a lot of financially based software and have found the Currency object to be perfect. I have never had a rounding error in my software and the storage in a SQLite database has always been perfect.

Your code is emulating the currency object anyway. The only difference that I can see is that you are using 3 decimal places and not the four that the Currency variable uses. If you really want to use an Int64 value just run the user input through a converter. Something like this:

Function FixToCurrency (Num As Double) As Currency dim ret As Currency = 0.0 dim i As Int64 = Num * 1000 ret = i / 1000 Return ret End Function
or

Function FixToCurrency (Num As Currency) As Currency dim ret As Currency = 0.0 dim i As Int64 = Num * 1000 ret = i / 1000 Return ret End Function
The above functions will return a Currency value that will be accurate.

4

Oh, I forgot to include a code function that I found on Xippets that will filter your input string better. I reproduce below the whole Xippet:

[code]textFilter( theText as string, theFilter as string ) as string

Returns from theText only those characters specified in theFilter, in the order they were originally entered in theText.

textFilter ( “(407) 555-1212”, “0123456789” ) = 4075551212

// Returns from theText only those characters specified in theFilter, in the order they were originally entered in theText.
//
// textFilter( theText as string, theFilter as string ) as string
//
// textFilter ( “(407) 555-1212”, “0123456789” ) = 4075551212

dim theChar, outputText as string
dim i as integer = 0

do until i > len( theText )

i = i + 1
theChar = mid( theText, i, 1 )

if InStr( theFilter, theChar ) > 0 then
  outputText = outputText + theChar  // Found theChar so include it for output 
end if

loop

return outputText[/code]
I use this to get only the numeric data from an input field as follows:

dim myVal As Currency = textfilter(TextField1.Text, "0123456789-.")

I have also run in to the local currency issues with “,” and “.” and " " all being used to separate thousands and decimals. I have created two functions that will give me the local user’s international settings. These are:

[code]Function DecimalSeparator As String
dim tst As String = Format(1000.01, “####.00”)
Return tst.Mid(5, 1)
End Function

Function ThousandsSeparator As String
dim tst As String = Format(1000, “#,###”)
Return tst.Mid(2, 1)
End Function[/code]
Hope this helps.

5

[quote=70207:@Simon Berridge]

[code]Function DecimalSeparator As String
dim tst As String = Format(1000.01, “####.00”)
Return tst.Mid(5, 1)
End Function

Function ThousandsSeparator As String
dim tst As String = Format(1000, “#,###”)
Return tst.Mid(2, 1)
End Function[/code]
Hope this helps.[/quote]

There can be an issue as this assumes that thousand & decimal IS always and forever 1 character
OS X will let you set it to more than 1 if you want - I found that one the hard way :slight_smile:

dim s as string
// gives you a string using the proper locale settings for thousands
s = format(1111,“0,000”)
// replace all 1’s and whatever is left over is the thousands (and it could be more than 1 char)
dim thousands as string = replaceall(s,“1”,"")

// gives you a string using the proper locale settings for decimal
s = format(111.1,“000.0”)
// replace all 1’s and whatever is left over is the decimal (and it could be more than 1 char)
dim decimal as string = replaceall(s,“1”,"")

6

Thanks for all the great information guys. Simon, thanks for showing me those great examples. Jeff, pulling the currency symbol out of the format string has solved that problem. However, I’m still a little confused on a few things.

I created all of these formatting constants for different currencies, however I believe now that they are useless when using the Format function since it’s going to use the user’s locale setting anyway. Please correct me if I’m wrong.

So what is the proper behavior when, for example, there’s a user using the app in the United States and he is creating a record that uses currency in Euros. Should the app format Euros as most Europeans see them, such as 1.234,12 or should it format according to the user’s locale setting, which in this case would be the United States?

I now see a benefit to forcing currency formats and not using locale settings for later text filtering purposes.

Norman, how do you write your filter method to strip the formatting when you have to consider the decimal or thousands separator could be more than one character?

Also, I am considering using the Currency data type again, but I’m throughly confused on it.

How do you save a currency to the database? When retrieving it using .CurrencyValue and displaying to the user, will format round it correctly?

Thank you

7

I believe the best is to display according to the user local settings. Doing otherwise would break the system interface paradigm. A European would have these settings made for 1.234,56 and expect it that way. A U.S. user with a system set to 1,234.56 would appreciate being able to manipulate euros with his usual format.

In general, I tend to consider that apps should behave like spreadsheets. As default, spreadsheets respect user system settings for currency format.

8

Read a currency value with the .CurrencyValue form and write it with the DatabaseRecord.CurrencyColumn form.

9

Basically the same as if it were one character
If people input values with the decimal & thousands separators then you need to strip them out - just be careful NOT to assume those are always and forever one character
A regex or replace all will let you find & replace substrings

10

Currency is a int 64 with some scaling applied to getting & setting the value
You could do this manually and scale up / down as much or as little as you wanted to handle more decimal places
And even if you scaled down to represent things as thousandths or ten thousandths of a cent you should have lots of room
since a int64 can represent a pretty large value 9223372036854775807
So even scaled to hundred thousandths of a cent you can hold something like 92 trillion dollars

11

With reference to int64, I very recently had some funnies in division using int64 when it contains very big numbers,
so, trying to isolate the issue…

code like…

dim bignum as int64 = &h7FFFFFFFFFFFFFFF dim ten as int64 = 10 dim at as integer for at = 1 to 10 self.myTextArea.appendtext( str( bignum) + EndOfLine ) bignum = bignum / ten next at

Generates o/p…

9223372036854775807
922337203685477632
92233720368547760
9223372036854776
922337203685477
92233720368547
9223372036854
922337203685

I guess 2nd and 3rd result lines may warrrant a feedback case, unless I’m doing something really daft.

12

Try it with CStr instead of Str. I think the Str command may be converting it to a Double before making it a string.

13

It is exactly the same with cstr.
and if I put breakpoint in the ide on the divide by 10 line, the debugger displays the bad values too.

14

What was seeming like a trivial task, is proving difficult for me. This is what I got, but I feel like there must be a cleaner way to do this. Is there?

I have no mask on the text field.

Thank you

[code]Function CurrencyVal(Extends s As String) As Currency
dim i as Integer
dim t as String
dim ch As String
dim s1 as string
dim decPos as Integer
dim decLen as Integer

// gives you a string using the proper locale settings for thousands
s1 = format(1111,“0,000”)
// replace all 1’s and whatever is left over is the thousands (and it could be more than 1 char)
dim thousands as string = replaceall(s1,“1”,"")

// gives you a string using the proper locale settings for decimal
s1 = format(111.1,“000.0”)
// replace all 1’s and whatever is left over is the decimal (and it could be more than 1 char)
dim decimal as string = replaceall(s1,“1”,"")

s = ( ReplaceAll( s, thousands, “”) )
decPos = s.InStr( decimal )
decLen = Len( decimal )

if decPos = 0 then decPos = len( s ) + 1

// Get only the numbers that are before the decimal
for i = 1 to decPos - 1
ch = Mid(s, i, 1)
if isnumeric( ch ) then
t = t + ch
end if
next i

// Put the decimal back in
t = t + decimal

// Get only the numbers that are after the decimal
for i = decPos + decLen to len( s )
ch = Mid(s, i, 1)
if isnumeric( ch ) then
t = t + ch
end if
next i

return t.val
End Function
[/code]