Excel currency interpretation
I am using python to read currency value from excel. Returns out of range. The Value method is a tuple that I don't know how to parse.
For example, a cell appears as $ 548 982, but in python the value is returned as (1, 1194857614).
How can I get a numeric value from excel or how can I convert the value of this tuple to a numeric value?
Thank!
Try the following:
import struct
try: import decimal
except ImportError:
divisor= 10000.0
else:
divisor= decimal.Decimal(10000)
def xl_money(i1, i2):
byte8= struct.unpack(">q", struct.pack(">ii", i1, i2))[0]
return byte8 / divisor
>>> xl_money(1, 1194857614)
Decimal("548982.491")
Money in Microsoft COM is an 8-byte integer; it is a fixed point with 4 decimal places (that is, 1 is represented by 10000). What my function does is take a tuple of 4 byte integers, make an 8 byte integer using a struct to avoid any sign issues, and then divide by the constant 10000. The function uses decimal.Decimal if available otherwise it uses a float.
UPDATE (based on a comment): So far, only COM currency values are returned as dual-target tuples, so you can test this, but there is no guarantee that this will always succeed. However, depending on the library used and its version, it is quite possible that later, after some update, you will receive decimal.Decimal
, and not two whole sets.
source to share
I tried this with Excel 2007 and VBA. It gives the correct meaning.
1) Try pasting this value into a new Excel workbook 2) Press Alt + F11. Gets you in the VBA editor.
3) Press Ctrl + G. Allows you to go to the nearest window.
4) In the next window, enter? Cells ("a1"). The value
here "a1" is the cell where you inserted the value.
I doubt the cell has some meaning or character that is causing it to be interpreted this way.
Post your observations here.
source to share