Saturday, May 19, 2012

yogi_Convert Specified Currency To US Dollar Using

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #546    May 18, 2012

user Pete Cutter said:
Single date historical currency exchange rate from OANDA
I'm looking for a spreadsheet function to bring a single historical exchange rate for a particular currency into a single spreadsheet cell.
I've tried some of the more user-friendly strings such as (apologies for my cell references--I've checked and these are all valid):
But have run into problems because:
  • Grandtrunk and Google Finance don't support one of the currencies I need: Laos Kip (LAK) (!?); and
  • Yahoo Finance doesn't seem to have a mechanism to call historical rates
I have thus used the following function to call up data from OANDA:
However, using this, I end up getting all 8 values from the resulting table (Currency,Code,USD/1 Unit,Units/1 USD, Lao Kip,LAK,0.0001269,8163.46) all in one cell.
I've tried a =right([cell ref],7) function to isolate just the last number, but it turns out that the length of this number differs for different currencies so I can't use it dependably.
I've also tried:
...but get a parse error every time.
Can anyone suggest a way to get just one isolated number from OANDA representing an exchange rate for a given day?
Any thoughts/techniques greatly appreciated. Specific function string examples are even more appreciated.
I have requested Pete Cutter to share his spreadsheet with me so I can see how his data is laid out and specifically what is housed in cell I39 and some other cells ... in the mean time following is my convoluted solution