Numeric limitations in Xojo, Ideas?

For quite some time I always have avoided Doubles, and until now, Currency was good enought fot the job with its limit of 4 decimal places.

But as @Michel Bujardet state on <https://xojo.com/issue/39907>, that limit is really useless For accounting applications in many countries. For example, now I need such an app with calculations that must be done with 6 decimal places, this is a goverment requirement for tax calculation, so, not optional.

So, can’t be done with Currency, (it only support 4 decimals), can’t be done with double, (nasty rounding problems), so, it is true that Xojo cant actually do simple math with better accuracy???

Is there a way make Currency work with 6 decimal places? Or any other option to create such behavior?

Or the only option is using plugins like the “Decimal Plugin” by @Robert Delaney?

I found a feature request <https://xojo.com/issue/3280> asking for a Decimal data type similar to the one in visual studio, but is from more than a decade ago, any news on that?

Also another request for the quadruple precision data type already in llvm

Just FYI, the “nasty rounding problems” are inherent to the standard way doubles are stored and a quadruple precision type would do nothing to alleviate that.

(my best guess is that) Currency is not subject to those rounding problems because they are actually integers that are being presented with a decimal point. You can do that the same thing by storing your values as multiples of 10^6, doing integer math on them, then presenting them to the user with the decimal in the right place.

Or use Bob’s plug-in is that does what you need.

MBS Xojo Plugins got BitNumberMBS (320-bit float) and LargeNumberMBS (up to 4128 bits integer).

With both you could just define your own currency data type and keep values there with factor 1000000.

Just an FYI, even if your factor is 10^7 so you can “keep” an extra place for rounding, Int64 will let you work with values in the 900 billion range. If you might need more than that, MBS or Bob’s plugins will do the job.

You can create a class that will mostly work like the native Currency type using the Operator_* methods. Internally it only needs to do standard integer math against any value multiplied by your factor. When it returns a double or integer, it needs to divide by factor first, and you have to implement your own Str and Format functions. I’d say this would take about 2-3 hours with testing, but you’d have something reusable for future projects.

this may make a nice new example for my new class. Maybe I make a sample project for a BigCurrency class.

I probably don’t need to tell you this, but since I’m me… just be sure to make the class immutable so it will function like any other native type.

If you like, take a look:

https://www.dropbox.com/sh/extwtpj4d185brp/AADqXPSyb5oLgEH-3i2kp255a?dl=0

Still work in progress and comments welcome.

[quote=427801:@Kem Tekinay]Just FYI, the “nasty rounding problems” are inherent to the standard way doubles are stored and a quadruple precision type would do nothing to alleviate that.

(my best guess is that) Currency is not subject to those rounding problems because they are actually integers that are being presented with a decimal point. You can do that the same thing by storing your values as multiples of 10^6, doing integer math on them, then presenting them to the user with the decimal in the right place.

Or use Bob’s plug-in is that does what you need.[/quote]

Shure, Quadruple are not perfect, but I think those have better accuracy. That was just a interesting feature request I foud. The ideal should be a decimal data type.

I was ready to give Bob’s plug-in a try, but, I also use the Ribbon Control bay @Jeremie Leroy and turns out that those cant be in the same project :s

Is there a sample for a native type class anywere?

thanks

A “native type” refers to the intrinsic types, i.e., string, double, integer, currency, and boolean, basically anything you don’t need to Dim with “New”. These are all immutable and a class can be crafted to emulate that behavior.

[quote=427878:@Christian Schmitz]If you like, take a look:

https://www.dropbox.com/sh/extwtpj4d185brp/AADqXPSyb5oLgEH-3i2kp255a?dl=0

Still work in progress and comments welcome.[/quote]

Looks pretty good. Do you need the “double” versions of the Operator_* methods?

I still have a better double conversion to LargeNumberMBS on the todo list.
I think that would help getting double into BigCurrency.

How so? The “errors” are due to not having enough combination of mantissa/exponent bits to maintain a greater precision. Doubles can hold a 53bit mantissa (52+sign) and 11bit exponent only, but Quads holds 113 and 15; so “rounding errors” occurs far away from our digits of interest, so we just don’t see them when presenting the data. Like we wanting a resulting value like 1234162836341.4263, but having a resulting double like 1234162836341.4263001212, But if it was a Quad it should be 1234162836341.42630000000000001212. The numbers here a fictitious, not calculated, used only for an explanation.

Based on the context of the conversation. I assumed the rounding errors he meant were well within the limits of the values a double can hold, e.g., the inability to properly represent some whole numbers. If we’re talking about values even in the millions, for example, 64 bits is more than enough to hold those out to six decimal places or more.

He is talking about an accounting software, that if it is supposed to support foreign currencies, we must be prepared to handle thousand of trillions of x currency plus 6 decimal places. But I understand your argument better now in your context, but, generically, an fp128 changes completely the game of decimal precision. Things can go really bad, for example, when calculating, an interest rate in a huge number with 6 decimals when dealing with a int64 fixed math (the intermediate math probably occurs in Doubles anyway, currently).

Quadruples, as well as many other types discussed, arent somethign that works well with storage in a db using existing API’s - and a lot of db’s dont deal with them so queries to get sums etc wont be optimal.
You’d end up turning a fair number into strings for storage in a db.

BCD is a different possibility - except that you have to convert from existing types to them at some point for some things.
Depending on the implementation they could be based on memoryblocks or strings or some other representation that has sufficient addressability.
And again you end up having toi likely store them in strings in a db

[quote=427915:@Norman Palardy]Quadruples, as well as many other types discussed, arent somethign that works well with storage in a db using existing API’s - and a lot of db’s dont deal with them so queries to get sums etc wont be optimal.
You’d end up turning a fair number into strings for storage in a db.

BCD is a different possibility - except that you have to convert from existing types to them at some point for some things.
Depending on the implementation they could be based on memoryblocks or strings or some other representation that has sufficient addressability.
And again you end up having toi likely store them in strings in a db[/quote]

At least MySQL and SQL Server have decimal data Type and SQLite can store numeric values with a maximum precision of 15 significant decimal digits, can’t those data types be used to store a decimal value?

Database types like decimal have no direct counterpart in Xojo and a fair number of other programming languages.
So you convert from the DB form to some other internal form.
And each time you do this you run the risk of the conversion introducing some small imprecision.
I’ve worked in languages that were far more database centric that had direct & exact counterparts like BCD which was supported both by the DB and the language.
It made doing financial applications trivial since they were done with absolutely correct accounting principles and you could define a numeric(15,2) (15 digits 2 decimal places) or numeric(15,6) if needed

Being able to represent a numeric value in memory is one thing
Being able to get it into and out of a db is another
Yet for most business applications both are likely to be required

I can emutalte the functionality using the Operator_Convert However, with a Operator_Convert with Integer and other with Double, xojo says “There is more than one item with this name and it’s not clear to which this refers” Even if doing with a DECLARED variable Like:

'This could be confusing for the compiler
Dim tCurrency As  xCurrency = 1.1

'But why this is not clear?
Dim dVal As Double =  1111
Dim tCurrency As  xCurrency = sValor

Looks like a bug, found a FB <https://xojo.com/issue/41697>. Also a post with the same question, but no response.

Is that another bug/limitation on Xojo or I am missing something?

No, I don’t think you’re missing anything. Best to leave the Double version as that will cover all the bases.

As a not-that-great alternative, implement using Variant and determine the type internally. The upside: you can easily convert a string if you want. The downside: the compiler won’t stop you from assigning, say, a Boolean or Dictionary.

Another alternative: do not implement Operator_Convert at all, use individual Constructors instead. (I don’t love this one either.)

Final thought: Use a combination of Operator_Convert(Double) and Constructor(integer/double) for when you need to be precise.

That’s why we store everything as integers with a decimal offset.