# Format a Currency value

How I can format a currency value to display on screen?
The Format() method unfortunately handle Doubles and this lead to rounding errors with big quantities.

Example:

```dim v as variant = "999999999999.9999" dim s as string = Format(v.CurrencyValue, "0.0000")```

the resulting string in s is `"1000000000000,0000"`

The interesting thing here … is it is VARIANT that is messing you up…

``````  dim v as string = "999999999999.9999"
msgbox Format(val(v), "0.0000")``````

gives the correct value… surprisingly

``````  dim v as currency = 999999999999.9999
msgbox Format(v, "0.0000")``````

while even this DOES NOT … which REALLY surprised me

[quote]dim v as currency = 999999999999.9999
msgbox Format(v, “0.0000”)
while even this DOES NOT … which REALLY surprised me[/quote]

I remember reading somewhere that currency is internally treated as a int64 data type, multiplied by 1000. dim’ing v as currency = 999999999999.9999 probably rounds it up to the nearest integer.

to test my theory, I tried:

``` dim v as currency = 999999999999.999 ' notice just 3 decimals msgbox Format(v, "0.0000")```

The result is correct on 15R22.

But I agree that this is counterintuitive.

Wow!

I used a variant because this is the sanctioned method (sic!) to fill a Currency with a number.
Language Reference suggest this

but doing:

``dim c as Currency = 999999999999.9999``

won’t work because the const is interpreted as a Double by the parser.

Haven’t we been through that before ?

For what its worth,

``` dim c as Currency = 999999999999.9999 dim mantis as currency = c-floor(c) system.debuglog format(floor(c),"###") // 99999999999 system.debuglog format(mantis,"####") // 9998 system.DebugLog c.totext // 999999999999.9998```

It looks as if some rounding happens within c itself the other way.

With 10 digits to the left the result is right. But Currency is indubitably broken. According to the LR :

With 15 nines to the left, the result becomes completely incoherent. Totext gives -0.5808 !

The result is the same using Xojo.Core.

Now, UInt64 gives a much better result :

``` dim uu as UInt64 = 9999999999999999999 system.DebugLog uu.totext // 9999999999999999999 system.DebugLog uu.totext.Left(15)+"."+uu.totext.right(4) // 999999999999999.9999 ```

Maybe time to dump Currency for good…

And fixing it instead of dumping?

If I had the choice between an unreliable car and a van that runs, I would not hesitate much.

From what I see, your report from September 2014 has a status of Fixed and Verified. Since the last update stating that status is from June 4, 2015, the current state of madness of the type may indeed be fixed in 2015R3.

Now I am not currently needing a stable and reliable type for accounting, but if I were, for the time being and pending the release of 2015R3, plus a thorough verification, I would not touch currency.

Problem is I need currency how is implemented (including the support in Variant), and should not be so difficult to fix it if we can build a substitute using UInt64.

I just re read <https://xojo.com/issue/35189> very carefully. It lists two different issues. One is the rounding error, the other is the possibility to assign a numeric value directly.

I do not know which one has been fixed or both.

More terrible, after the test I conducted with series of nine, I started to wonder if the test in that report would render different results if instead of 123456789012345.1234 I used 999999999999999.1234.

[code] dim value as string = “999999999999999.1234”

dim c1 as currency = val(value)
dim c2 as currency = ctype(val(value), Currency)
dim v as variant = value
dim c3 as currency = v.currencyValue
break[/code]

I do hope Xojo verified very thoroughly the fix. For the time being, C3 gives 922337203685477.5807 … That is unusable.

I had not tested iOS new framework, and hoped it was more reliable. Well…

``````[b]39894 - iOS Currency does not support 15 digits to the left as described in the LR[/b]
``````

Status: Needs Review Rank: Not Ranked Product: Xojo Category: Compiler

Michel Bujardet Today at 1:18 PM
OS: OS X 10.10.3

Xojo: Xojo 2015r2.2

Steps: See attached project.

-

[code]dim c3 as currency = 999999999999999.1234
system.DebugLog c3.totext

dim value as Text = “999999999999999.1234”
dim c4 as currency = Currency.FromText(value)
system.DebugLog c4.totext[/code]


When the 15 digits to the left are assigned directly to the variable, the result is incoherent :
-0.5808

When Currency.FromText is used, the error The text is not a valid currency is raised.

With only 14 digits :
Direct : 99999999999999.1296
FromText : 99999999999999.1234

It is necessary to go down to 11 digits to obtain the correct value in both cases.

Either the LR is wrong, or Currency does not comply to the specs.

Expected Result:
Currency retains the values entered

Actual Result:
Results are completely out of this world with 15 digits to the left
File attached: currencyios.xojo_binary_project.zip

In the above you talk about 14 digits and you seem to be only counting those to the left of the decimal point. In fact you have 18 digits since 4 of them are to the right of the decimal. If I remember correctly 32 bit floating point can only handle a max of 15 digits within its numbers. That, by itself helps to lead to your errors. But, as you say, currency seems to have some severe problems when numbers reach a certain value and you attempt to display them.

Fortunately the little budgeting program that I wrote for myself that, uses Currency values, will never encounter those problems since I won’t live long enough to accumulated wealth to those number of digits.

Currency values are 64 bit values
But there are only 63 bits available for the “value” since one is the sign bit
This means the max is somewhere about 922337203685477.5807
So 15 9’s followed by 4 fractional digits literally overflows the currency max value & like all signed values a positive on wraps around to a negative one

*I’m not sure what the min negative is

[quote=196477:@Norman Palardy]Currency values are 64 bit values
But there are only 63 bits available for the “value” since one is the sign bit
This means the max is somewhere about 922337203685477.5807
So 15 9’s followed by 4 fractional digits literally overflows the currency max value & like all signed values a positive on wraps around to a negative one

*I’m not sure what the min negative is[/quote]

Indeed it looks very much like an overflow. Problem is the LR states very clearly :

Is it not time to correct the LR to indicate the actual practical limit of the current Currency type ?

nine hundred twenty-two trillion, three hundred thirty-seven billion, two hundred three million, six hundred eighty-five thousand, four hundred seventy-seven dollars and fifty-eight cents

range of a INT64 is
-9223372036854775808 to 9223372036854775807

UINT64
0 to 18446744073709551615

[quote=196483:@Michel Bujardet]Indeed it looks very much like an overflow. Problem is the LR states very clearly :
[/quote]
It should state a valid range as well like most of the other numeric data types do
Documentation error

[quote=196490:@Dave S]range of a INT64 is
-9223372036854775808 to 9223372036854775807

UINT64
0 to 18446744073709551615[/quote]

In theory then the max negative for a currency should be -922337203685477.5808

There’s no “unsigned currency” type

Coincidentally, that’s also the amount Donald Trump is filing as his net worth.

And when you do conversions to other currencies? If you are converting from Euros to something like Vietnamese currency it becomes 37,748,105,251 Euros to overflow.

Personally on that scale I would probably feel safer using a bug number library instead.

I thougth that was the point of this topic, it was a “bug number” LOL