Need a function for Currency Converter

[xlsx]

I need kind of function to normalize the Expense Report and converty all the different currency to ONE desired one.


DATE Description Currency Amount

1/1/2009 Buy Books AED 8

1/2/2009 Lunch AED 12

1/3/2009 Airticket USD 120

1/6/2009 Accomodation USD 350

1/7/2009 Choclates EUR 5


So all the above different currency should be converted to USD and give me expense total in USD.......i tried to use the Web Query feature of Excel to get the RATES of given date from Yahoo.finance.com. but couldn't success


Can anyone point me to right way to achieve this?


Thank you,

Murtaza

 

[keymaster]

Hi xlsx.. good username btw..


here is a general approach for currency translation.


1. In one sheet get the latest currency rates for one USD from say yahoo finance. You need to update this sheet every day. One smart way to handle this is by writing a macro to refresh the webquery on file open.


2. In another sheet based on the currency code, find the exchange rate and convert it in to USD.


I know it sounds simple, but it might be very complicated to implement due to reasons like incosistent data formats.


Another option is to try using one of the webservices of yahoo finance to fetch live currency rates. This involves some bit of macro coding.


good luck.

 

[keymaster]

Also, you can get the php version of this code here: http://www.wait-till-i.com/2008/06/21/currency-conversion-api/


the VBA version should be straight forward...