Sunday, January 4, 2009

Interpreting Excel Currency Values - Stack Overflow

Interpreting Excel Currency Values - Stack Overflow: "
Problem Scenario:

For example, the cell appears as $548,982, but in python the value is returned as (1, 1194857614).
How can I get the numerical amount from excel or how can I convert this tuple value into the numerical value?

import struct
try: import decimal
except ImportError:
divisor= 10000.0
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)

Money in Microsoft COM is an 8-byte integer; it's fixed point, with 4 decimal places (i.e. 1 is represented by 10000). What my function does, is take the tuple of 4-byte integers, make an 8-byte integer using struct to avoid any issues of sign, and then dividing by the constant 10000. The function uses decimal.Decimal if available, otherwise it uses float.

UPDATE (based on comment): So far, it's only COM Currency values being returned as a two-integer tuple, so you might want to check for that, but there are no guarantees that this will always be successful. However, depending on the library you use and its version, it's quite possible that later on, after some upgrade, you will be receiving decimal.Decimals and not two-integer tuples anymore."

No comments: