Interpreting Excel Currency Values

Question

I am using python to read a currency value from excel. The returned from the range.Value method is a tuple that I don't know how to parse.

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?

Thanks!

Answer 1

I tried this with Excel 2007 and VBA. It is giving correct value.

1) Try pasting this value in a new excel workbook
2) Press Alt + F11. Gets you to VBA Editor.
3) Press Ctrl + G. Gets you to immediate window.
4) In the immediate window, type ?cells("a1").Value
here "a1" is the cell where you have pasted the value.

I am doubting that the cell has some value or character due to which it is interpreted this way.

Post your observations here.

Answer 2

Try this:

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'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.