Is Currency data type really usable?

General
21

Gotchya. I found these on another thread. This might help in your conversions. I was running into major issues converting between doubles. In this app I happen to be using SQLite which doesn’t have a Currency Datatype so I’m using Integer (SQLite is 64 bit integers by default) and convert them to Currency.

[code]Function ToCurrency(extends iValue as Int64) As Currency
dim mb as new MemoryBlock(8)
mb.Int64Value(0) = iValue

return mb.CurrencyValue(0)

End Function
[/code]

[code]Function ToInt64(extends cValue as Currency) As Int64
dim mb as new MemoryBlock(8)
mb.CurrencyValue(0) = cValue

return mb.Int64Value(0)
End Function[/code]

Function ToString(extends cValue as Currency) As String return Format(cValue, "#,##0.00") End Function

22

[quote=126647:@Bob Keeney]Gotchya. I found these on another thread. This might help in your conversions. I was running into major issues converting between doubles. In this app I happen to be using SQLite which doesn’t have a Currency Datatype so I’m using Integer (SQLite is 64 bit integers by default) and convert them to Currency.

[code]Function ToCurrency(extends iValue as Int64) As Currency
dim mb as new MemoryBlock(8)
mb.Int64Value(0) = iValue

return mb.CurrencyValue(0)

End Function
[/code]

[code]Function ToInt64(extends cValue as Currency) As Int64
dim mb as new MemoryBlock(8)
mb.CurrencyValue(0) = cValue

return mb.Int64Value(0)
End Function[/code]

Function ToString(extends cValue as Currency) As String return Format(cValue, "#,##0.00") End Function [/quote]

I quickly tested this in an old version of RB and mb.CurrencyValue(0) seems to go via a double as well. Newer versions of RB / Xojo might be better though.

We use Bob Delaney’s plug-in for some non-currency related large number maths and it works great. I bet it could be used to create an alternative currency class without too many problems.

Kev.

23

Maybe I could help with some C written functions?

Avoiding the doubles in-between makes could be some work.

24

Yes, a working Currency class with fixed point internal representation.

25

I thought more about some helper functions like those for UInt64:
http://monkeybreadsoftware.net/math-mathint64-method.shtml#4

26

You can use an Int64 and apply the scaling yourself (for display).
There are some gotchas when performing math on two fixed if one is scaled by 100 and another by 1000.

Or just count everything in “fractions of currency units” using int64’s - in the US this would be fractions of cents - and scale on display only

I’ve always thought that a BCD type would be ideal for accounting applications
One where you could specify the number of digits before and after the decimal - BCD(18,6) would be 18 digits before the decimal and 6 following
And adding that to another BCD(12,2) would give you the correct BCD result - an BCD(18,6)

27

[quote=126693:@Norman Palardy]You can use an Int64 and apply the scaling yourself (for display).
There are some gotchas when performing math on two fixed if one is scaled by 100 and another by 1000.

Or just count everything in “fractions of currency units” using int64’s - in the US this would be fractions of cents - and scale on display only

I’ve always thought that a BCD type would be ideal for accounting applications
One where you could specify the number of digits before and after the decimal - BCD(18,6) would be 18 digits before the decimal and 6 following
And adding that to another BCD(12,2) would give you the correct BCD result - an BCD(18,6)[/quote]

Yes, and you can also invent a round object, call it wheel and try to patent it. :stuck_out_tongue:
C’mon Norman, the only solution which make sense is having a Currency data type in Xojo which works as it should.

28

Could it be that just val and format are not overloaded with special functions to handle currency?

Because if data is converted to double internally (or variant with double), things fail.

29

I’ll add a CurrencyValueMBS plugin function to convert string to Currency value to replace val() for that case.

30

[quote=126723:@Christian Schmitz]Could it be that just val and format are not overloaded with special functions to handle currency?

Because if data is converted to double internally (or variant with double), things fail.[/quote]

It is not the str or format function that fails. It is the Currency type.

This displays 0.1904 :

dim c4 as Currency = 123456789012345.1234 c4 = c4-123456789012345 msgbox str(c4)

The strange fraction occurs within the variable itself, not at the str stage.

Massimo is right, the Currency type should provide what is described in the LR : “15 digits to the left of the decimal point and 4 digits to the right”.

It seems doubtful this issue will be fixed in the next release. If I was engaged in producing software that needs accounting accuracy, I would start looking for a sensible solution to the local sales tax third digit accuracy described in the discussion above. Since the Xojo Currency type does not do past the 9.9 trillions mark without losing its marbles, other solutions mentioned in this thread seem in order like Bob Delaney large numbers plugin. Yet, not many customer need to manage more than 10 trillions with 1/10th of a cent accuracy.

It is not my project. Good luck, Massimo. I am sure you will get through that eventually.

31

This looks like math is done with double, not Int64.

Maybe Joe or someone else with compiler insight can check what is happening here.

32

For me, if I write:

dim c5 as Currency = 123456789012345

I get in debugger: 123456789012345,0156

33

ah, looks like debugger just uses also doubles…

34

[quote=126752:@Christian Schmitz]For me, if I write:

dim c5 as Currency = 123456789012345

I get in debugger: 123456789012345,0156[/quote]

That is exactly the issue. With 15 digits as exponent, the fractional part becomes erratic. The only way to keep accuracy in the 4 digits fractional part is to limit the number of digits to 13 in the exponent.

This is coherent with a 64 bits format, though.

35

Christian, this is exactly what I said on my original post.
There is no way to assign a value to a Currency property without passing from double.
Even a direct assignment like:

dim c as Currency = 123456789012345.1234

takes the number as a Double, lose precision and THEN it’s converted to Currency.

Again, if the Currency data type handles internally a fixed point value, we need a way to assign a value to a Currency. From string and even with a constant. But the latter would require the compiler to first try to fit a constant value into a Currency and THEN fallback to a Double if it can’t be represented as a Currency. Or having a method to explicitly say to the compiler how the constant should be handled, like in C.

36

[quote=126712:@Massimo Valle]Yes, and you can also invent a round object, call it wheel and try to patent it. :stuck_out_tongue:
C’mon Norman, the only solution which make sense is having a Currency data type in Xojo which works as it should.[/quote]
For accounting having a BCD type is likely much more useful.
Currency having a fixed 4 decimals is too limiting in some cases.
I used to work for a big company and we often worked to 6 decimals for various calculations (when you talk billions of $ 6 decimals makes a difference)
Currency only allows 4.
But wrapping a Int64 in a class and managing the scaling (which is pretty simple) makes it possible to have a decently flexible type.

37

But I think operations still seem to convert all elements to double before processing, so lose precision over full int64…

ie: any value over &h00FFFFFFFFFFFFF4 will cause problems in an operation like…

OneInt64 = OneInt64 * AnotherInt64

I have not tried this on a 2014 release, and I seem to remmeber that there are related feedback cases - no time to check right now, sorry.

39

Would using UInt64 and 1/100th of cents be valid ?

This is simple enough.

dim c as UInt64 = 1234567890123451234 c = c-1 msgbox format(c,"###,###")

Result : 1,234,567,890,123,451,136

Something is terribly wrong…

Seems indeed that the format operation messes up.

dim c as UInt64 = 1234567890123451234 c = c-1234567890123450000 msgbox format(c,"######")

This shows 1234 as expected, which indicates that the subtraction keeps the integrity of the UInt64. The variable is safe.

So the issue is in turning the UInt64 into a string that probably uses a double prior to display.

This converts a Uint64 representing 100th of cents in an amount in dollars :

Function myformat(c as uint64) As string dim expon as UInt64 = floor(c/10000) dim fract as integer = c-(expon*10000) return format(expon,"###,###")+"."+str(fract) End Function

This displays 123,456,789,012,345.1234

dim b as UInt64 = 1234567890123451234 msgbox myformat(b)

40

Looks like case 35189 is fixed and in testing. Any idea when should we expect it?

41

wait for 2014r3? Maybe a nice christmas present.