l1011driver
Board Regular
- Joined
- Dec 26, 2014
- Messages
- 68
- Office Version
- 365
- Platform
- Windows
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
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