Batch currency conversion
Thread poster: Samuel Murray

Samuel Murray  Identity Verified
Netherlands
Local time: 20:57
Member (2006)
English to Afrikaans
+ ...
Jan 27, 2011

G'day everyone

I have an Excel spreadsheet with two columns... column A has a date and column B has a USD amount. I want column C to contain the EUR amount... as it was on the date in column A. The list is about 150 items long. Does anyone know of a free service or solution that might help with this? I don't want to sit here spending my time doing the conversions one by one by one using a historical exchange rate web site.

Thanks
Samuel


 

Soonthon LUPKITARO(Ph.D.)  Identity Verified
Thailand
Local time: 01:57
Member (2004)
English to Thai
+ ...
Samples of Excel data? Jan 27, 2011

If you can show the MS Excel data format, many people can offer you macro/formula to copy & paste and convert currencies for you at a batch. That is, relationship among column A, B and C in your spreadsheet.

Soonthon Lupkitaro


 

FarkasAndras
Local time: 20:57
English to Hungarian
+ ...
Simple is best Jan 27, 2011

Just get a table of the exchange rates from whatever source is relevant for the situation and drop it in the excel spreadsheet. Say, you have the date in column A, the dollar amount in B. You need to paste the day's exchange rate in C and use a formula in D to calculate the euro amount.
For 150 records, I would probably just paste in the rates manually, but you could also use the VLOOKUP function discussed elsewhere in this forum, or even just use some smart manual sorting/pasting/filtering of dates to bring the two data sets in sync, if you're in the mood for that.


 

Jaroslaw Michalak  Identity Verified
Poland
Local time: 20:57
Member (2004)
English to Polish
But what rates? Jan 27, 2011

Do you have any specific rates in mind? They might differ wildly between banks, exchanges, national authorities etc.

One service I found is this:

http://www.oanda.com/currency/historical-rates

You can pull out easily CSV with the dates and rates, using that in Excel should be fairly simple.


 

Samuel Murray  Identity Verified
Netherlands
Local time: 20:57
Member (2006)
English to Afrikaans
+ ...
TOPIC STARTER
I honestly don't care about which rate Jan 27, 2011

Jabberwock wrote:
Do you have any specific rates in mind? They might differ wildly between banks, exchanges, national authorities etc.


Yes, I'm aware of that, but as long as I can show which rate I used (e.g. point to the web site), it would be okay.


 

Samuel Murray  Identity Verified
Netherlands
Local time: 20:57
Member (2006)
English to Afrikaans
+ ...
TOPIC STARTER
Soonthon Jan 27, 2011

Soonthon LUPKITARO(Ph.D.) wrote:
If you can show the MS Excel data format, many people can offer you macro/formula to copy & paste and convert currencies for you at a batch.


Column A contains a date. This date can be in Excel's date format or it can be converted (by me) to any date format that the service requires.

Column B contains a number with 2 decimal places, e.g. "123.45". This represents a dollar amount.

Column C (or D or whatever) should end up with the corresponding euro amount.

So if column A has "1 January 2010" and column B has "100.00" and the USDxEUR exchange rate on 1 January 2010 was 1:1.2, then column C should end up with "120.00".

Does this make sense? The stumbling block isn't so much the Exce formula as it is the ability to query a web site of historical exchange rates in bulk fashion.


 

LEXpert  Identity Verified
United States
Local time: 13:57
Member (2008)
Croatian to English
+ ...
Try this, Samuel Jan 27, 2011

The formula/conversion issue should be straightforward, once you obtain historical data in a format you can paste into the spreadsheet for the respective historical data points (which is where I assume the problem lies). Try here:

http://www.forexrate.co.uk/forexhistoricaldata.php

Scroll down past the ticker symbols to USD>EUR, select the parameters at the top (daily rates, etc.) and you will get an Excel file with a row for each data point. With some minor tweaking and a formula fill you should be able to paste the rates to use as a conversion factor.
Hope it helps.

[Edited at 2011-01-27 18:55 GMT]

[Edited at 2011-01-27 19:10 GMT]


 


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

Batch currency conversion

Advanced search







PerfectIt consistency checker
Faster Checking, Greater Accuracy

PerfectIt helps deliver error-free documents. It improves consistency, ensures quality and helps to enforce style guides. It’s a powerful tool for pro users, and comes with the assurance of a 30-day money back guarantee.

More info »
SDL MultiTerm 2019
Guarantee a unified, consistent and high-quality translation with terminology software by the industry leaders.

SDL MultiTerm 2019 allows translators to create one central location to store and manage multilingual terminology, and with SDL MultiTerm Extract 2019 you can automatically create term lists from your existing documentation to save time.

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search