Historical Exchange Rate Data

[l1011driver]

Thank you for any help anyone might be able to provide.

I have a company expense form that I would like to automate. This form is used for reimbursement of personal expenses incurred while on company business. Company policy is that for reimbursement of personal expenses all expenses must be converted back to US dollars from the local currency used.

In the first column is the date of the purchase or transaction. In the column adjacent to the date is the amount of the transaction in local currency. The following column is the local currency in 3-letter format, i.e., AED, GBP, EUR, etc. In another column is the (currently manually user-entered) exchange rate. The final column is the amount converted to USD.

Is it possible to write a macro that will look up the exchange rate for a given currency on the date of the transaction and input that rate on the spreadsheet for subsequent conversion to USD? I would like this to work such that the presentation of the current form really doesn't change and the background calculations are basically transparent to the user. For example, if an employee spends 100 GBP, then when completing his expense reimbursement form he would enter the date of the transaction in the "date" column, he would enter in "100" in the transaction in local currency column, "GPB" in the local currency column and then have the program place the exchange rate that existed on the date of the transaction in the "exchange rate" column.

Thanks for any help anyone might be able to provide. Cracking this nut would save a lot of time for several of our employees.

Sincerely,

Mark Barnard

 

[mole999]

as long as you have a master table of daily exchange rates for those currencies a simple index / match should do that for you

 

[l1011driver]

as long as you have a master table of daily exchange rates for those currencies a simple index / match should do that for you

Dear Sir,

Thank you for your reply. I sincerely appreciate the help, but honestly I don't see this solution as practical for a variety of reasons. If it would aid you in helping me create a solution I could delineate the problems I see with it, but for now I don't think creating a master currency table is workable.

I am looking for more of a VBA macro or web query solution, possibly a combination of the two. I do have similar spreadsheets that utilize web queries, but those are limited to the current day rather than seeking historical values. I was thinking perhaps a VBA macro could be developed that would allow a web query to search out the exchange rate for a given currency on a given day. Now I have seen examples on the internet of macros that seek out the exchange rate between two different currencies, but in my case the exchange rate will always be compared to US dollars, so there is one less variable to contend with. I simply don't have the ability to write such a macro or modify one that already exists.

If you have any thoughts along these lines I'd appreciate hearing them. Thank you for your time in considering my problem.

Respectfully,

Mark Barnard