CODEWITHSUNDEEP
phpexcelms-officeautomatic-updatescurrency-exchange-rates
Denis Giffeler
Denis Giffeler

Reputation: 1509

Current exchange rates for Excel

Is there a way to import current exchange rates in MS-Excel and keep them updated? Excel provides a way of calling a URL and import the response into a cell.

There are several prof. services on the internet but I wanted to make sure that no data is logged and the information provided is up-to-date. And it should be free of charge...

Upvotes: 2

Views: 1213

Answers (2)

Mike
Mike

Reputation: 14606

Here is a coding-free solution. You can 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?

  1. Go to Data → From Web.

  2. 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

  3. Press Load. The new sheet with the required data will be automatically created.

  4. 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:

Upvotes: 1

Denis Giffeler
Denis Giffeler

Reputation: 1509

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 step-by-step solution including screenshots can be found here: https://eves.genia.berlin/excelquery_e.html

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.

Selection of external data

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

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)

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.

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

Here is the code snipped (PHP) on the server side:

<?php if ($p= substr ($_SERVER['QUERY_STRING'], 0, 3)) { $pu= strtoupper ($p); if (preg_match ('/^(?: BGN|CHF|CZK|DKK|EEK|EUR|GBP|HRK|HUF|ISK|LTL|LVL|MDL|MKD|NOK|PLN|RON|RSD|RUB|SEK|SKK|TRY|UAH| BDT|BND|CNY|FJD|HKD|IDR|INR|JPY|KRW|LKR|MVR|MYR|NPR|PHP|PKR|SGD|THB|TWD|VND|IQD| ANG|ARS|BOB|BRL|CAD|CLP|COP|CRC|DOP|HNL|KYD|MXN|NIO|PEN|PYG|SVC|TTD|UYU|VEF| BWP|DZD|EGP|KES|MAD|MUR|NAD|NGN|PGK|SCR|SLL|TZS|UGX|XOF|ZAR|ZMK| AED|AUD|BHD|ILS|JOD|KWD|KZT|LBP|NZD|OMR|QAR|SAR|TND|UZS|YER )$/x', $pu)) { $pl= strtolower ($p); if ($x= simplexml_load_file ("http://xxxxx/$pl.xml", null, LIBXML_NOCDATA)) { if (preg_match ("/(\d+)\.(\d+)\s*$pu/", (string) $x->channel->item->description, $currency)) { header ('Content-type: text/plain'); header ('Cache-Control: no-cache, must-revalidate'); echo "$currency[1],$currency[2]"; //Excel braucht Komma statt Punkt exit; } } } } header ('HTTP/1.1 204 No Content', true, 204);

Upvotes: 0

Related Questions