I've also hit the same problem and have come up with a solution. Its in python rather than VB, but should be easy to port over.vrdiver wrote:Crude, but it works. Just remember to keep an eye on 42TE in case it increases in value 100-fold some day
I've got a function to determine the magnitude of a number in a cell ie 9=1, 99=2, 999=3, etc
Code: Select all
def GetMagnitude(Price):
return int(math.log10(Price)+1)
Code: Select all
CurrentCellValue = sheet.getCellRangeByName(cell).Value
ExpectedMagnitude = GetMagnitude(float(CurrentCellValue))
Code: Select all
def CheckPoundsPence(Price, ExpectedMagnitude):
"""
Somethimes Yahoo returns price in GBP instead of GBX. Correct for this.
Shift price so as it reflects the expected magnitude.
"""
# Determine magnitude of price ie 9.00=1, 99.0=2, 999.0=3
PriceMag = GetMagnitude(Price)
# Handles both Left and Right shifts. Honest.
if PriceMag != ExpectedMagnitude:
Price *= 10**(ExpectedMagnitude - PriceMag)
return Price
-0x3F