Exchange Rates in Excel

Call the URL http://genia.berlin/usd.php to get the actual exchange rate in comparison to US-Dollar. The accuracy is 4 decimal places. An erroneous call returns an empty result, no error message. One parameter given in ISO 4217 notation, e.g.: http://genia.berlin/usd.php?eur

The information is provided without warranty of any kind!

The screenshots are from version Microsoft® Excel for Mac 15.11.2; However, the method should work in principle, regardless of operating system used with current versions.

excelquery.txt

Create a text file. The name can be freely selected. Please make sure that there is a blank line after the URL.

WEB
1
http://genia.berlin/usd.php?["Currency","Enter currency code (ISO 4217)"]

Selection=EntirePage
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=True

Select a stored request

Start Excel and display the following menu: Data / Get External Data / Run Saved Query ...
Type the location and name of the text file (for example, excelquery.txt) from the previous section.
The query is treated as a formula. The assigned cell can be stored in another worksheet.

Import Data
Selection of external data

In selecting the properties different settings such as automatic updating can be set when opening the Excel document.

Extern Data Range Properties
Selection of currency as a parameter

There are three possible ways to select the currency as a parameter.

  1. Input by user (Prompt for value using this string)
  2. Specifying a string, for example EUR (Use this value)
  3. Specifying an Excel cell containing a string (Get the value from this cell)

In the following screenshot option 1 was chosen.

Parameters
Input by user

The user is prompted in a dialog to specify the currency. He can also determine whether the input is to be used again in future updates of the same document.

Enter Parameter Value

Next time you call the Excel file a Warning is issued. The connection for data retrieval must be confirmed by the user.