Formula for updated currency exchange rates in Excel or other spreadsheets?

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.

Solution 1:

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 https://www.ablebits.com/office-addins-blog/2017/11/30/currency-conversion-google-sheets/ for more examples.

Solution 2:

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.

Solution 3:

Google Spreadsheets:

https://stackoverflow.com/a/20716922/462347

Microsoft Excel:

Download data in CSV format from Yahoo Finances and import it to Excel as a query.

How to import the data from Yahoo Finances to Excel?

Go to Data → From Web.
Specify the data URL in the field URL, e.g. for the USD to EUR rate: http://download.finance.yahoo.com/d/quotes.csv?s=USDEUR=X&f=b
Press Load. The new sheet with the required data will be automatically created.
You can hide the header and the style of the cell by unselecting the Header Row and Banded Rows respectively.

That's all, now you have a cell with the desired data. You can referece to the cell like to any other cell in Excel.

Good to know:

You can manually trigger the data refresh by clicking on Data → Refresh All. For automatic update when a workbook is opened: https://support.office.com/en-US/article/Refresh-an-external-data-connection-in-Excel-2016-for-Windows-1524175f-777a-48fc-8fc7-c8514b984440
The URL in the example above contains the &f=b fragment, which means bid. For the detailed Yahoo Finances query data parameters, please see: http://www.financialwisdomforum.org/gummy-stuff/Yahoo-data.htm

Solution 4:

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.