Technology, life, programming, travel and money.

Batch conversion of historical foreign currency values into a native currency

Maybe you are in the situation where you have received payments in a variety of currencies, and now you need to convert the payment values into another currency (perhaps your own native currency).

But your task is made more complicated by the fact that you need to use the exchange rate at the date the payment was received, rather than totalling up all the values in each currency and then using a single exchange rate from a specific date.

This could happen to you if for example you receive PayPal payments in a multiple currencies and want to produce a report showing how much you earnt each month. For the monthly totals to make sense you would have to have all the values in a single currency.

You could do all the conversions manually, but that would be very tedious if there are more than a few to convert. Using an Excel spreadsheet to do it for you is much more fun.

Download payment and currency data

First of all I downloaded my transaction history from PayPal. Here I’ve set it to download a year’s worth of data.

currency conversion by date 1

Then I downloaded some historical exchange rate data for the same period. I got my data from Oanda (http://www.oanda.com/currency/historical-rates/), but there are plenty of other sites offering similar data.

currency conversion by date 2

I set the ‘Currency I Want’ to be my native currency (GBP) and then used the ‘Currency I Have’ to get the currency data for each of the currencies I’ve received payments in. Which in my case is USD and EUR.

Using the spreadsheet

The spreadsheet contains some example payment data, and historical currency prices for USD and EUR so you can see how it works.

The areas that you’ll need to edit have yellow column header, and the areas with orange column headers are auto-calculated for you and shouldn’t be edited.

Download the example historical batch currency conversion spreadsheet – XLS (20kb Zip file)

Enter the payments

On the ‘Payments’ tab I entered the details of the payments received from Paypal (I filtered out any payments that I made). The data should nicely fit into the existing columns if you downloaded it as ‘Comma Deliminated – All Activity’. If your data is not from Paypal, or is in a different format you may have to re-order your columns to fit my spreadsheet.

currency conversion by date 4

The important bits to enter are the dates in column A, currency in column G, and amount in Column H.

currency conversion by date 5

You should then enter your native currency tag into cell P3. E.g. my native currency is GBP which means that I don’t want any payments received in GBP to be converted. Payments in the native currency can just be used as-is.

currency conversion by date 6

Columns L-N contain calculated values. You shouldn’t need to edit them, but you may need to copy down the formulas if you have a large list of transactions.

Enter the historical currency data

Next you should enter the historical currency data into the ‘FXRates’ tab.

currency conversion by date 3

Enter it from row 6 onwards, using a separate column for each currency. Make sure the dates for each currency match up.

Above the data in row 3 you should enter a currency tag for each currency. This tag should match the currency tag that is in column G of the ‘Payments’ sheet. E.g. you may have USD, HKD, EUR etc.

Cell A3 contains the native currency tag that you entered on the ‘Payments’ sheet, you don’t need to edit this cell.

Also don’t modify the numbers that are highlighted in orange on row 4. These are column numbers which are used to help the formulas pick the correct currency.

I use a HLOOKUP to get the column number of the currency:

=HLOOKUP(G4,FXRates!$A$3:$Z$4,2,FALSE)

And then I do a VLOOKUP to get the exchange rate. You’ll see the first condition sets the exchange rate to ‘1’ if the payment is in the native currency.

=IF(G4=$P$3,1,VLOOKUP(A4,FXRates!$A$6:$Z$10000,L4,FALSE))

Simple?

If you’d done it all correctly you should see the converted values in column N of the ‘Payments’ sheet. You can see a total in Q3.

I’d recommend you cross check a few random values to make sure that you have set the spreadsheet up correctly.


Leave a Reply

Do NOT fill this !