Replicate precise calculation from VBA in Python

Question

I have this code in VBA that needs to be in Python. I'm new to Python, sorry for that.

Sub cdec_test()
Dim val1, val2, result1, result2, x1, x2 As Variant
val = CDec(5930 / 3000) 'val1 : 1.97666666666667 : Variant/Decimal
result = 0.63 + (0.48 - 0.63) * (val1 - 1)'result1 : 0.4834999999999995 : Variant/Decimal 
x = Application.WorksheetFunction.Round(result1, 3) '0.483
End Sub

In Python, however, result for some reason rounded to 0.4835, and x will be rounded further to 0.484. Here is my Python code

def cdec_test():
    getcontext().prec = 28
    val = Decimal('5930') / Decimal('3000')  # val : 1.9766666666666667 , so far so good
    result = Decimal('0.63') + (Decimal('0.48') - Decimal('0.63')) * (val - Decimal('1')) #'0.4835000000000000000000000000'
    return result
result = cdec_test()

Tried changing getcontext().prec = 15 to match numbers of digits, no luck. Later in code this is getting multiplied by 10^6..10^9 numbers so it's a big deal... How do I fix it? Does Python have a regular C-style double format? What modules can help?

Peacetime

In the vba code if you replace CDec(5390/3000) by CDec(5390)/CDec(3000) the result flips from .483 to .484 – Didactic

@JohnColeman not my post, but i am curious if you know why that has an impact on rounding the final result? To my knowledge, the written values 5.390 and 3.000 have no further allocated memory for fractions and requesting an output for type as decimal does not impart added fractions. – Asare

If you compute the expected results by hand using fractions, you'll see that the python code actually gives the right result (the VBA code yields a precision error due to what @JohnColeman mentioned). 1.97666.... is 593/300, so you get 63/100+(48-63)/100 * (593/300-1)= (6300-(15*293))/10000 = 4835/10000 = 0.4835. – Deerdre

Answer 1

We know (or can see) that val results in a repeated decimal 1.976 with 6 repeating. You have confirmed that this is what you expect.

Ultimately, that number is multiplied by -0.15 and the result looks rounded. But is it?

Lets simplify the multiplication a bit and instead of multiplying by -0.15 let's multiply by 15. You can verify that this produces the similar "rounding phenomenon".

    val = decimal.Decimal('5930') / decimal.Decimal('3000')
    bar = decimal.Decimal('15')
    foo = bar * val
    print(foo)

That should give you: 29.650000000000 suggesting a rounding has occurred, but has it?

let's take our var and multiply it by 10 and by 5 and look at what we get and what the ramifications might be of doing var * 10 + var * 5

var * 10 gives us 19.76 with 6 repeating

and

var * 5 gives us 9.883 with 3 repeating.

When you add them together you get 29.649 with 9 repeating which is 29.65

Ultimately something similar is happening with your actual formula here:

(Decimal('0.48') - Decimal('0.63')) * (val - Decimal('1'))

that is:

-0.15 * 0.976 # with 6 repeating.

This result in -0.14649 with 9 repeating which is -0.1465

I believe no amount of additional precision will help here and you can verify this by setting decimal.getcontext().prec = 1000 and get the same result.

Penholder

Just to clarify. This VBA code was produced in 2007 to replace even older C++ code, where 0.63 + (0.48 - 0.63) * (5930.0/3000.0 - 1) would give you 0.4834999999999995. I mean, it probably is not a correct result, but it was like this forever... – Peacetime

Answer 2

The correct equivalent Python function would be:

from decimal import *
def cdec_test():
    getcontext().prec = 15
    val = Decimal(5930) / Decimal(3000)  
    print(val) # 1.97666666666667
    result = 0.63 + (0.48 - 0.63) * (float(val) - 1)
    return result
result = cdec_test()
print(result) # 0.4834999999999995
print(round(result,3)) # 0.483

This should result in exact the same as the VBA Sub.

Backgrounds: Excel floating point precision is 15 only. An exception is if you consistently use CDec. But that is not done in given VBA code. So it falls back to float having double precision. The VBA code result = 0.63 + (0.48 - 0.63) * (val - 1) converts val to float having double precision and not all float values to CDec.

Combo

"Excel floating point precision is 15 only" but Variant/Decimal is 29 digits – Didactic

@John Coleman: You refer .NET Visual Basic. But that differs from Microsoft Office VBA. – Combo

Wrong link see this – Didactic

@John Coleman: OK, but that's only true if you consistently use CDec. But that is not done in given VBA code. So it falls back to float having double precision. – Combo