Import exchange rates from the Internet

Importing the rate of a given currency from the Internet with automatic updating is a very common task for many Microsoft Excel users. Imagine that you have a price list that must be recalculated every morning according to the exchange rate. Or the project budget. Or the cost of the contract, which must be calculated using the dollar exchange rate at the date of the conclusion of the contract.

In such situations, you can solve the problem in different ways – it all depends on which version of Excel you have installed and what add-ons are on top of it.

Method 1: A simple web request for the current exchange rate

This method is suitable for those who still have old versions of Microsoft Office 2003-2007 on their computer. It does not use any third-party add-ons or macros and only operates on built-in functions.

Нажмите кнопку From the Internet (Web) tab Data (Date). In the window that appears, in the line Address (Address) enter the URL of the site from which the information will be taken (for example, http://www.finmarket.ru/currency/rates/) and press the key Enter.

When the page loads, black and yellow arrows will appear on tables that Excel can import. Clicking on such an arrow marks the table for import.

When all the necessary tables are marked, click the button Import (Import) at the bottom of the window. After some time required to load the data, the contents of the marked tables will appear in the cells on the sheet:

For additional customization, you can right-click on any of these cells and select the command from the context menu. Range properties (Data range properties).In this dialog box, if desired, it is possible to configure the update frequency and other parameters:

Stock quotes, as they change every few minutes, you can update more often (checkbox Refresh every N min.), but exchange rates, in most cases, it is enough to update once a day (the checkbox Update on file open).

Note that the entire imported range of data is treated by Excel as a single unit and given its own name, which can be seen in the Name Manager on the tab formula (Formulas — Name Manager).

Method 2: Parametric web query to get the exchange rate for a given date range

This method is a slightly modernized first option and gives the user the opportunity to receive the exchange rate of the desired currency not only for the current day, but also for any other date or date interval of interest. To do this, our web request must be turned into a parametric one, i.e. add two clarifying parameters to it (the code of the currency we need and the current date). To do this, we do the following:

1. We create a web request (see method 1) to the page of the website of the Central Bank of Our Country with the archive of courses: http://cbr.ru/currency_base/dynamics.aspx

2. In the form on the left, select the desired currency and set the start and end dates:

3. Click the button To get data and after a couple of seconds we see a table with the course values ​​we need for a given date interval. Scroll the resulting table all the way down and mark it for import by clicking on the black and yellow arrow in the lower left corner of the web page (just don’t ask why this arrow is there and not next to the table – this is a question for the site designers).

Now we are looking for a button with a floppy disk in the upper right corner of the window Save Request (Save Query) and save the file with the parameters of our request to any suitable folder under any convenient name – for example, in My documents under the name cbr. iqy.  After that, the Web Query window and all of Excel can be closed for now.

4. Open the folder where you saved the request and look for the request file cbr. iqy, then right-click on it – Open With – Notepad (or select it from the list – usually it is a file Notepad.exe from folder C: Windows). After opening the request file in Notepad, you should see something like this:

The most valuable thing here is the line with the address and the query parameters in it, which we will substitute – the code of the currency we need (highlighted in red) and the end date, which we will replace with today’s one (highlighted in blue). Carefully edit the line to get the following:

http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=[“Currency code”]&date_req1=01.01.2000&r1=1&date_req2=[«Date»]&rt=1&mode=1

Leave everything else as it is, save and close the file.

5. Create a new book in Excel, open the sheet where we want to import the archive of the Central Bank rates. In any suitable cell, enter a formula that will give us the current date in text format for query substitution:

=TEXT(TODAY();”DD.MM.YYYY”)

or in English version

=TEXT(TODAY(),»dd.mm.yyyy»)

Somewhere nearby we enter the code of the currency we need from the table:

The required code can also be peeped in the query string directly on the Central Bank website.

6. We load the data on the sheet, using the created cells and the cbr.iqy file as the basis, i.e. go to tab Data – Connections – Find Others (Data — Existing Connections). In the data source selection window that opens, find and open the file cbr. iqy. Before importing, Excel will clarify three things with us.

First, where to import the data table:

Secondly, where to get the currency code from (you can check the box Use this default value (Use this value/reference for future refreshes), so that later each time this cell is not specified during updates and the checkbox Automatically update when cell value changes (Refresh automatically when cell value changes):

Thirdly, from which cell to take the end date (you can also check both boxes here so that tomorrow you don’t have to set these parameters manually when updating):

Click OK, wait a couple of seconds and get a complete archive of the exchange rate of the desired currency on the sheet:

As in the first method, by right-clicking on the imported data and selecting the command Range properties (Data range properties), you can adjust the refresh rate When opening a file (Refresh on file open). Then, if you have access to the Internet, the data will be automatically updated every day, i.e. The table will automatically be updated with new data.

It is easiest to extract the rate for the desired date from our table using the function VPR (VLOOKUP) – if you are not familiar with it, then I strongly advise you to do this. With such a formula, for example, you can select the dollar exchange rate for January 10, 2000 from our table:

or in English =VLOOKUP(E5,cbr,3,1)

where

• E5 – the cell containing the given date
  
• cbr – name of the data range (automatically generated during import and usually the same as the name of the query file)
• 3 – the serial number of the column in our table, where we get the data from
• 1 – an argument that includes an approximate search for the VLOOKUP function so that you can find courses for those intermediate dates that are not actually present in column A (the nearest previous date and its course will be taken). You can read more about approximate search using the VLOOKUP function here.

• Macro to get the dollar rate for a given date in the current cell
• PLEX add-on function to get the exchange rate of the dollar, euro, hryvnia, pound sterling, etc. for any given date
• Insert any currency rate on any date in PLEX add-on