Py.Var is rounding my resulting tuple of Decimals

[charliegenge]

HI Eric,

I have a python function which returns a tuple of Decimals e.g. (Decimal('4.96218411552347000000'), Decimal('4.95415534319262000000'), Decimal('4.94349678841657000000'), Decimal('4.93623882952235000000'))

When this is returned to Excel through a UDF, the Py.Var VBA function appears to round the results leaving only 4 decimal places.

Is there a way for it to return the full precision?

Thanks

[ericremoreynolds]

Hi Charlie,

this is not strictly speaking a feature of ExcelPython, i.e. ExcelPython is based on PyWin32, and the conversion of Python types to variants is determined by PyWin32's behaviour.

It would seem that PyWin32 converts Decimal objects to the VBA Currency data type, which stores numbers in a 15.4 decimal format (which to me seems like a strange choice).

Let me see if there's anything written on the internet about controlling how PyWin32 converts data and get back to you.

Eric

[charliegenge]

I changed my python function to return a tuple of floats instead, this seems to do the trick.

Thanks for your help,

Charlie

[ericremoreynolds]

Sure... but it's an interesting point, by converting to floats it might lose precision and in some cases you might need high precision.

From a few quick tests it seems that PyWin32 converts VBA decimal variables to string:

?Py.Str(Py.Eval("type(x)", Py.Dict("x", CDec(1.23456789))))
<type 'unicode'>

so on the Python side you can then use the string to construct a Python decimal with it.

In the other direction, you could convert the Python decimals to string, then convert them from a string to a VBA decimal using the CDec function.

Not very convenient really... so I guess if floats are OK that's the best thing to use in your case.

Eric.