Matching exchange rates to dates

[Don in South Africa]

Living overseas, the IRS insists that I must convert all of my expenses and income in foreign currency into US $ at the exchange rate for the date I made the expenditure or earned the income. For years I have been downloading a table of exchange rates for every day of the year from www.oanda.com and then copying and pasting into Excel the exchange rate for each day that I spent or earned.

I want to know whether it is possible to copy the whole year's worth of data into my spreadsheet and then let Excel find the exchange rate for the appropriate date with any copying and pasting. The exchange rate table can be downloaded in html, ascii and csv.

As I am not an expert in Excel, I would appreciate step-by-step instructions.

Thank you for your time.

A Loyal Taxpayer

 

[BrianB]

Should not be a problem. I don't have time to trawl the web site, but hopefully you can at least get the data in a text file.

1. Copy the file from the website on to your hard drive.
2. File Open from Excel and choose the file Type Textfile. Follow the automated wizard. Hopefully you end up with a worksheet with the data.
3. A good way of getting the appropriate day's rate would be to use the VLOOKUP() Function (see Excel Help) to look up the date of your entry and show the rate in the cell. You can then have another cell which multiplies that cell times your amount.

 

[Don in South Africa]

Solution to getting Excel to lookup exchange rates by date

Thank you, BrianB, for pointing me in the right direction. I have it working perfectly now.

For anyone who might want complete instructions for importing and using historical exchange rates in Excel, here is the complete process I now use for expense reports, IRS Schedule C, etc.:

1. in www.oanda.com, go to FXHistory, get the table of dates and currencies you desire, download as CSV file

2. save web page in browser as .txt file

3. open that txt file in Excel, click on “delimited” and tell it to start at the row number where the exchange rates start. (49 in my case, scroll down to check) Click “Next”

4. Check “comma”, click “next”

5. click “date”, click “finished”

6. copy the two-column table and paste it into the workbook (preferably at the bottom) in which you need to use the exchange rates.

7. highlight the two-column table in the workbook and give this range a name in the “Name Box,” (The box immediately above the A column), press ENTER. I named my table of exchange rates, “Rates”.

8. My expenses are arranged in this order: Column A:Name of expense; B: Date, C: Cost in foreign Currency, D: Exchange rate, E: Resulting cost in dollars.

9. Using row 5 as an example, in D5, I place the following formula: =VLOOKUP(B5,Rates,2)
This tells Excel to take the date in B5, look for the same date in the table named “Rates” and use the exchange rate found in the 2nd column, next to that date in the table named “Rates.” Fill down.

10. In E5, the formula is =C5*D5. (Or =C5/D5, depending on whether your table shows dollars per unit of foreign currency or vice versa.) Fill down.

It works for me. I only despair to think of the hours I could have saved if I had learned this trick years ago.