Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

What kind of spreadsheets support a formula like the following:

=exchangeCurrency("USD"; "EUR"; 3000)

The result should be 3000 USD exchanged into Euros. Possibly a date could be provided for a historic exchange rate. Alternatively, the average for the last month or so.

I would use such a feature all the time and it has to be relevant for so many other users. I prefer working in Excel, but online spreadsheets like Google or Zoho is also fine.

share|improve this question

10 Answers 10

up vote 21 down vote accepted

Here are some options for you:

GOOGLE SPREADSHEETS

There's a Google Spreadsheet script that returns the exchange rate between EURO and a currency that you specify. Data is supposedly from the European Central Bank.

Setup:

Open a Google Docs Spreadsheet.

Click Insert > Script > Search for ECBExchangeRate

Install it.

Usage:

=ECBExchangeRate("mm.dd.yyyy", "USD")

** With quotes on the date

This will return the exchange rate between USD and EUR for the date mm/dd/yyyy. Unfortunately, the data source isn't updated.

Calculating between 2 Currencies:

For example, we want to get the USD/JPY rate for today. Get EUR/USD and EUR/JPY using the formula above. Divide the EUR/JPY rate by the EUR/USD rate. You get USD/JPY.

EXCEL

I don't believe such a function exists for Excel (at least by default). You can, however, set up a sheet that imports data from an online exchange rate table like the one on the Yahoo Finance page (finance.yahoo.com > Investing > Currencies > Currencies Investing > Cross Rates).

You can then setup a VLOOKUP or INDEX/MATCH formula to get the relevant exchange rates for you.

Setup:

Create a new sheet (recommended).

Click Data > From Web > Enter the following URL:

http://finance.yahoo.com/currency-investing#cross-rates

Click the yellow arrow beside the exchange rate table. The arrow should turn into a green check mark. Click Import.

Click Properties. Under Refresh Control, specify how often you want Excel to retrieve new data, or pick Refresh data when opening the file. Click OK then wait for Excel to load the data.

To refresh the data, just hit Ctrl-Alt-F5.

Here's a sample spreadsheet:enter image description here

The data on the Yahoo Finance Page is pretty limited, but it covers the most popular currencies. You can use other sources, such as:

http://www.ecb.int/stats/exchange/eurofxref/html/index.en.html

The website http://www.xe.com/ucc/ has historical rate tables which you can consolidate.

share|improve this answer

In Google Spreadsheets, getting current exchange rates is very easy with the built-in Google Finance querying function. E.g, to get the current rate for USD/EUR:

=GoogleFinance("CURRENCY:USDEUR")

This will work for all exchange rates Google Finance is tracking. See http://googledocstips.com/2011/03/09/how-to-calculate-foreign-exchange/

share|improve this answer
    
woooow this is amazing, thanks!!! –  Davide Piras Dec 15 at 21:53

The expression:

=GoogleFinance("CURRENCY:USDEUR")

works pretty fine in old Google Spreadsheets, but in New Google Spreadsheets this formula returns an error:

Error: When evaluating GOOGLEFINANCE, the query for the symbol: 'CURRENCY:USDEUR' returned no data.

The worked solution: http://stackoverflow.com/a/20609318/462347

share|improve this answer

This Excel spreadsheet connects to a web service (Oanda) to automatically download historical exchange rates.

http://investexcel.net/222/automatically-download-historical-forex-data-into-excel/

The VBA is exposed, so you can hack away at the code I wrote. You should be able to create a function like

=exchangeCurrency("USD"; "EUR"; 3000)

which uses an 30-day average exchange rate or something.

share|improve this answer

I have been using the plugin below for quite some time. Works great for me. It uses Yahoo API and MSN money website to get live currency rates in Excel exposed as custom functions. i.e. =Fx_Convert(100,"USD","EUR")

http://technitya.com/content/Live-Currency-in-Excel

share|improve this answer

You won't find anything built in to any spreadsheet for something which changes constantly like exchange rates. Thankfully, there are solutions out there already that others have created for this kind of thing, such as WikiHow, Yahoo Answers, OzGrid, EHow.

There are many other solutions, but most of them follow the same general design patterns. If none of the above links are what you're looking for, I would suggest searching on the OzGrid site. Their forums are often one of the best for finding Excel solutions.

share|improve this answer
    
None of the links are helpful. I would still be better off copying and pasting in the updated currency rate. There has to be a better solution. –  David May 20 '11 at 13:25

There is an Excel UDF that scrapes exchange rate data from Yahoo Finance here

This is the syntax

=FXRate("currency1", "currency2",["close", "open", "bid", "ask"])

So to convert 30 GBP to USD, you'd write

=30*FXRate("GBP", "USD","bid")

I think that's what you wanted.

share|improve this answer

You may use Spearian for MS Excel, http://spearianforexcel.com, to work and to download all history of any exchange rate. It's very comfortable for obtaining access to all major market data, including Yahoo Finance!

share|improve this answer

There is a free currency conversion tool available for excel which converts between over 160 currencies. The pro version can also use historical data:

http://www.office-plugins.net/free-excel-currency-converter/

share|improve this answer

You can use built-in GOOGLEFINANCE function which fetches current or historical securities information from Google Finance. You can use it with the following syntax:

GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

Sample usages:

=GoogleFinance("CURRENCY:EURUSD")
=GOOGLEFINANCE("GOOG", "marketcap", DATE(2012,1,1), DATE(2012,12,31), "DAILY")
=Index(GoogleFinance("CURRENCY:EURUSD", "close", DATE(year($A1), month($A1), day($A1)), DATE(year($A1), month($A38), day($A38)+1), "DAILY"), 2, 2)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.