Batch currency conversion
Thread poster: Samuel Murray

Samuel Murray  Identity Verified
Netherlands
Local time: 14:21
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


Direct link Reply with quote
 

Soonthon LUPKITARO(Ph.D.)  Identity Verified
Thailand
Local time: 20:21
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


Direct link Reply with quote
 

FarkasAndras
Local time: 14:21
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.


Direct link Reply with quote
 

Jaroslaw Michalak  Identity Verified
Poland
Local time: 14:21
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.


Direct link Reply with quote
 

Samuel Murray  Identity Verified
Netherlands
Local time: 14:21
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.


Direct link Reply with quote
 

Samuel Murray  Identity Verified
Netherlands
Local time: 14:21
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.


Direct link Reply with quote
 

LEXpert  Identity Verified
United States
Local time: 07:21
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]


Direct link Reply with quote
 


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







memoQ translator pro
Kilgray's memoQ is the world's fastest developing integrated localization & translation environment rendering you more productive and efficient.

With our advanced file filters, unlimited language and advanced file support, memoQ translator pro has been designed for translators and reviewers who work on their own, with other translators or in team-based translation projects.

More info »
SDL Trados Studio 2017 Freelance
The leading translation software used by over 250,000 translators.

SDL Trados Studio 2017 helps translators increase translation productivity whilst ensuring quality. Combining translation memory, terminology management and machine translation in one simple and easy-to-use environment.

More info »



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