Is there a sane way to batch-convert a year of expenses from one currency to another in Excel?

May 29, 2009 3:24 AM

Excel n00b filter: Is there a way to automatically convert an expense sheet from one currency to another by matching the date the cost was incurred with the exchange rate applicable on that specific day?

I need to include a ton of expenses incurred while abroad in my yearly tax report, but all my receipts are in Japanese Yen. Therefore, I have to find out what the JPY - EUR exchange rate was on the day each expense was incurred and convert the amount to Euro. This is terribly time-consuming, so I'm looking for ways to automate the process.

I have managed to find a website that will let me download historical data of the exchange rates in Excel format, which makes things a bit easier. However, this would be much faster still if I could get Excel to automatically look up the exchange rates on that table according to the date and grab the correct rate for each entry.

I'm pretty sure there is a way to do this under Excel, but I can't find it for the life of me. Could one of the experts in the house kindly point me in the right direction? Thanks Hive Mind!

posted by doctorpiorno to computers & internet (6 comments total)

VLOOKUP is what you need. Have a table of dates and expenses and a separate table with dates and exchange rates. Vlookup will look up a date on the second table and get the matching exchange rate.
posted by Frank Grimes at 4:05 AM on May 29 [1 favorite]

Google's spreadsheet can do *amazing* things. I am certain that you can define a formula that looks up an exchange rate on a particular day.

Take a look at "Functions: Using GoogleLookup":

http://docs.google.com/support/bin/answer.py?hl=en&answer=54199

Export and import to interoperate with MSFT Excel.
posted by cmiller at 6:17 AM on May 29

Vlookup will work, but it's not my favorite way.
Assuming your data is such:

Sheet 1
_____A______B_______C________D
1 expense, date, (exchange lookup), (USD)
[input] [input] [formula] [A * D]
2 .... same etc...

Sheet 2
____A_______B
1 Date Exchange

the formula will be
=index(sheet2!$B$1:$B$365,match(B1,sheet2!$A$1:$A$365),0)


All the formula does is look up a value (in this case the exchange rate) using an row value which is found using the match function (which looks up the date, and returns a row value). the 0 indicates an exact match must be made.

There is usually an error trap I put in using an if statement, but we can skip that for now.
posted by defcom1 at 8:11 AM on May 29

You can also use the VLOOKUP as above, what I don't like about it is that the data must be sorted in ascending order, which isn't always convenient, depending on what you're doing.
Also, the people at mrexcel.com are very knowledgeable about all things excel.
posted by defcom1 at 8:13 AM on May 29

VLOOKUP should do the trick. Incidentally, if your data is already organized in a horizontal layout, there is the formula HLOOKUP that would work (V in VLOOKUP meaning vertical; H meaning horizontal)
posted by Eicats at 8:46 AM on May 29

Wow! Excellent advice everyone. Thanks to Frank Grimes for introducing me to the amazing world of VLOOKUP and to defcom1's for his excellent step-by-step tutorial. To Cmiller: I can't figure out how to use GoogleLookup to bring up historical data for currencies, but it still seems like a pretty useful trick to add to my repertoire. Will definitely look into that in more detail for future reference. Thanks everyone!
posted by doctorpiorno at 4:16 PM on May 29