Use pairs in one VLOOKUP with historical exchange rates from Datastream
One of the most useful Excel functions I help students with is VLOOKUP – extremely useful to automatically bring in data from one table to another. If you have company accounts data from Compustat Global (via WRDS) for multiple companies in multiple currencies, you will need to bring in the change rate for each currency to show every field in US Dollars.
|1||Company||Currency||Total Assets Local||Exchange rate||Total Assets USD|
|2||Co. X||AUD||100||=VLOOKUP(B2, currencies, 2, FALSE)||=C2*D2|
|3||Co. Y||PLN||400||=VLOOKUP(B3, currencies, 2, FALSE)||=C3*D3|
Where ‘currencies’ is a named range elsewhere in the workbook:
The VLOOKUP command looks for the currency code in the first column and returns the rate from the second column (use ‘FALSE’ to ensure an exact match). The conversion is simply a multiplication. (Note that you can convert from USD to USD at a rate of 1.0.) But what about making sure that the exchange rate is correct for the date of the data, and what if you have data from multiple years?
Performing VLOOKUP when matching a pair of variables
Thanks to Professor Marie Dutordoir for suggesting this kind of technique!
Usually you will want to look at accounts data over several years and for several companies. If you are working with several currencies then you will need a more complicated solution to this VLOOKUP exchange rate table, considering a day/month/year for each rate-currency pair. You do not need a second lookup table or other Excel functions, you just need to have another variable of date and a variable of currency-date pairs.
The new column F (mapping) is formed from currency and year, so F2 contains “=G2&H2” (see image above). The ‘currencies’ named range now begins with a different first column produced in the same way (see image below). A VLOOKUP always matches on the first column of its range, and now this one pulls the rate from the fourth column, so rate is “=VLOOKUP(F2, currencies, 4, FALSE)” for row 2.
In the ‘currencies’ named range, I have combined the exchange rate calculation and lookup into one formula (column K). Repeat this for each data type that you wish to convert the currency for. Remember to have an entry for USD at rate 1 for each year.
This example only has the average exchange rate for each year, you may wish to extend this approach for monthly or even daily rates. If you do this, you will need to take extra care with the format of the mapping cells, but this exceeds the scope of the post.
Where to find historical exchange rates?
- Datastream (as shown in the screen above).
- The documentation for Compustat suggests using WRDS Cloud, but this requires knowledge of Unix and SAS, similar to the S&P 500 constituents example.
- Other sources of historical exchange rates (last updated July 2015).