Posts Tagged ‘currency’

Batch conversion of historical foreign currency values into a native currency

Friday, June 8th, 2012

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.

Thomas Exchange UK Maddox Street

Monday, March 19th, 2012

I’ve previously reviewed Thomas Exchange Global on The Strand, and Thomas Exchange Global on Victoria Street which are both part of the same company. This time I’m reviewing Thomas Exchange UK on Maddox Street, which despite the very similar name is (as far as I can tell) a completely separate company. As before I needed to buy Yen for one of my trips to Japan.

It is located on a fairly quiet street in central London so you probably aren’t going to come across it unless you make a special trip. Like the other two places I’ve reviewed it is usually listed as one of the best places on the Martin Lewis Travel Money Max website.

thomas exchange uk maddox street

On arrival I told the man at the counter how many Yen I wanted to buy and gave him my cash. Unlike in the other two places where my money was counted in front of me, he went into a back room to do the counting.

This meant that I was left without my Pounds, Yen or any proof that I’d given him my money whilst he was dealing with it. It took him 2-3 minutes to come back. When he did he gave me the correct amount of Yen which I double checked before leaving.

The two Thomas Exchange Global’s I’ve visited were both in very busy locations with a lot of customers. This foreign exchange shop was quieter both in terms of location, and in the shop itself.

Apart from the slight worry caused by my money being taken out of my sight everything went smoothly and I’d be happy to use this branch again.

Thomas Exchange UK is at 13 Maddox Street, London, W1S 2QG.


View Larger Map

Thomas Exchange Global Victoria Street

Monday, April 18th, 2011

I needed another load of Yen so I decided to go to the bestforeignexchange.com currency shop which is located on Victoria Street. I found it listed as one of the best foreign exchange places on the Martin Lewis travelmoneymax.com website.

thomas exchange global victoria street

Looking more closely at the bestforeignexchange.com website it turns out that this is another Thomas Exchange Global like the one I visited and reviewed on The Strand the last time I needed Yen. I’m guessing they are using different names and websites for each branch to get more coverage on the search engines and travel money comparison sites. That doesn’t matter to me though – I just want a good rate.

To get the best rates they recommend you order your money in advance with their fast track service. I did this on the same day via their website. You just enter how much of each currency you want and then submit the order. You can specify that you will pay in cash so that you don’t have to pay any money until you get to the branch.

best foreign exchange fast track order

The branch is at 141 Victoria Street, a short walk from Victoria station. As Victoria Street is a busy area I’d recommend you bring your cash in a hidden money belt. The main branding on the outside lists the shop is Thomas Exchange Global.

When I arrived I told the man behind the counter that I wanted to collect an order – and gave my name which was the order reference. As my order was worth less than £2000 they didn’t ask for any ID. He retrieved the Yen very quickly and then put my pile of £20 notes through a bank note counting machine. He then counted out my Yen by hand twice, and gave it to me with my receipt, and a small amount of British change.

In my case I didn’t request any particular bank note denominations so I can’t say what they are like for special requests – but there is a box on the order form for messages, so if you do have requests you could try putting them in the form.

From arriving at the Victoria Thomas Exchange Global branch to receiving my Yen must have taken less than three minutes. They are very quick and seem to keep any verbal communication to the bare minimum.

Both this and the Strand Branch of Thomas Exchange Global are very similar. They are very fast, and consistently appear near the top of the travelmoneymax.com list for Yen, so I’d happily use either of them again for cash orders.

I’ll have another currency exchange review coming up soon. I split my Yen order into two batches so that 1) I wouldn’t be carrying/exchanging too much money at once and 2) so that I could try out two different travel money branches.

Thomas Exchange Global in London

Monday, April 19th, 2010

Where do you go when you need foreign currency for your holiday? Do you go to your bank, post office, a high street currency dealer, or order online?

Before deciding where to get your holiday money I highly recommend you check out Martin Lewis’s TravelMoneyMax.com. He’s the guy who runs the Money Saving Expert website, and is regularly on UK TV giving advice on saving money.

On the TravelMoneyMax website you enter how much of what currency you want, and the site will tell you the best place to get it. One of the places that is always near the top of the list is the Thomas Exchange Global currency dealer at 402 Strand, WC2R 0NE in London.


View Larger Map

I’ve been to the Thomas Exchange Global quite a number of times to get various currencies. Here is my review of what their service is like when you visit the exchange in person to buy currency. I’m not reviewing any of their other services.

The shop itself is on the North side of the road on the Strand. It is a very small premises so you’ll have to be careful not to walk past it.

Thomas Exchange Global foreign currency exchange London

There are about three counters in the shop, but this seems to be enough as they are very quick. Ask them what price they will give you, and if you are happy with the rate, give them your money.

Once you tell them how much foreign currency you need, the person serving you passes this information onto a backroom member of staff. The backroom staff get the foreign currency ready while the counter staff member counts up your own money.

When I’ve requested smaller denomination notes they often haven’t been able to get them for me. But as long as you are happy with whatever denomination bank notes they give you Thomas Exchange Global do a good job.

They always count your original money out in front of you. If you give them a reasonable wad of notes they’ll then put it through a note counting machine to count it again.

They’ll then bring your foreign currency over, and count it out in front of you. They’ll do this at least twice. They don’t accidental want to give you too much!

To finish up they’ll give you a receipt, and give you your foreign money in an envelope.

If you are exchanging a large amount of money then I’d recommend you carry your money in a money belt to make sure you don’t lose it. And to make it less likely that it will get stolen. This is a very busy area of London and you could easily get mugged if an opportunist thief saw you walking out with a big bundle of bank notes.

Links:
Thomas Exchange Global official website.
TravelMoneyMax.com