yogi_Convert Specified Currency To US Dollar Using www.oanda.com

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

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):

• Grandtrunk (=importData("http://currencies.apps.grandtrunk.net/getrate/"&text(I39,"yyyy-mm-dd")&"/"&I41&"/"&I42); and
• Google Finance (=Index(googlefinance("currency:myrusd","high",E31),2,2); and
• Yahoo Finance (=index(importData("http://finance.yahoo.com/d/quotes.csv?s=KHRUSD=X&f=a"),1,1)

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:

• =index(importhtml("http://www.oanda.com/currency/table?date=I39&date_fmt=us&exch=USD&sel_list=LAK&value=1&format=CSV&redirected=1","table",2),2,1)

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:

• =index(importHTML("http://www.oanda.com/currency/historical-rates-classic?date_fmt=normal&date="&CELL-WITH-START-DATE-IN-DD/MM/YY&"&date1="CELL-WITH-END-DATE-IN-DD/MM/YY&"&exch="&CELL-WITH-FROM-RATE-CODE&"&exch2="&CELL-WITH-FROM-RATE-CODE&"&expr="&CELL-WITH-TO-RATE-CODE&"&expr2="&CELL-WITH-TO-RATE-CODE&"&margin_fixed=0&format=ASCII&redirected=1","table",3),1,2)

...as posted here: http://productforums.google.com/d/topic/docs/cO8lVyBpwyE/discussion

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

Thanks!!!

--------------------------------------------------------------------------------------

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