Updated exchange rate in Excel

I have repeatedly analyzed ways to import data into Excel from the Internet with subsequent automatic updating. In particular:

• In older versions of Excel 2007-2013, this could be done with a direct web request.
• Starting in 2010, this can be done very conveniently with the Power Query add-in.
  

To these methods in the latest versions of Microsoft Excel, you can now add another one – importing data from the Internet in XML format using built-in functions.

XML (eXtensible Markup Language = Extensible Markup Language) is a universal language designed to describe any kind of data. In fact, it is plain text, but with special tags added to it to mark up the data structure. Many sites provide free streams of their data in XML format for anyone to download. On the website of the Central Bank of Our Country (www.cbr.ru), in particular, with the help of a similar technology, data on the exchange rates of various currencies is given. From the Moscow Exchange website (www.moex.com) you can download quotes for stocks, bonds and a lot of other useful information in the same way.

Since version 2013, Excel has two functions for directly loading XML data from the Internet into worksheet cells: WEB SERVICE (WEBSERVICE) и FILTER.XML (FILTERXML). They work in pairs – first the function WEB SERVICE executes a request to the desired site and returns its response in XML format, and then using the function FILTER.XML we “parse” this answer into components, extracting the data we need from it.

Let’s look at the operation of these functions using a classic example – importing the exchange rate of any currency we need for a given date interval from the website of the Central Bank of Our Country. We will use the following construction as a blank:

Here:

• The yellow cells contain the start and end dates of the period of interest to us.
• The blue one has a drop-down list of currencies using the command Data – Validation – List (Data — Validation — List).
• In the green cells, we will use our functions to create a query string and get the server’s response.
• The table on the right is a reference to currency codes (we will need it a little later).
  

Let’s go!

Step 1. Forming a query string

To get the required information from the site, you need to ask it correctly. We go to www.cbr.ru and open the link in the footer of the main page’ Technical Resources’- Getting data using XML (http://cbr.ru/development/SXML/). We scroll a little lower and in the second example (Example 2) there will be what we need – getting the exchange rates for a given date interval:

As you can see from the example, the query string must contain start dates (date_req1) and endings (date_req2) of the period of interest to us and the currency code (VAL_NM_RQ), the rate of which we want to get. You can find the main currency codes in the table below:

A complete guide to currency codes is also available on the Central Bank website – see http://cbr.ru/scripts/XML_val.asp?d=0

Now we will form a query string in a cell on a sheet with:

• the text concatenation operator (&) to put it together;
  
• Features VPR (VLOOKUP)to find the code of the currency we need in the directory;
  
• Features TEXT (TEXT), which converts the date according to the given pattern day-month-year through a slash.

="http://cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(B2;"ДД/ММ/ГГГГ")&  "&date_req2="&ТЕКСТ(B3;"ДД/ММ/ГГГГ")&"&VAL_NM_RQ="&ВПР(B4;M:N;2;0)  

Step 2. Execute the request

Now we use the function WEB SERVICE (WEBSERVICE) with the generated query string as the only argument. The answer will be a long line of XML code (it’s better to turn on word wrap and increase the cell size if you want to see it in its entirety):

Step 3. Parsing the answer

To make it easier to understand the structure of the response data, it is better to use one of the online XML parsers (for example, http://xpather.com/ or https://jsonformatter.org/xml-parser), which can visually format XML code, adding indents to it and highlighting the syntax with color. Then everything will become much clearer:

Now you can clearly see that the course values ​​are framed by our tags …, and dates are attributes Date in tags .

To extract them, select a column of ten (or more – if done with a margin) empty cells on the sheet (because a 10-day date interval was set) and enter the function in the formula bar FILTER.XML (FILTERXML):

Here, the first argument is a link to a cell with a server response (B8), and the second is a query string in XPath, a special language that can be used to access the necessary XML code fragments and extract them. You can read more about the XPath language, for example, here.

It is important that after entering the formula, do not press Enter, and the keyboard shortcut Ctrl+Shift+Enter, i.e. enter it as an array formula (the curly braces around it will be added automatically). If you have the latest version of Office 365 with support for dynamic arrays in Excel, then a simple Enter, and you don’t need to select empty cells in advance – the function itself will take as many cells as it needs.

To extract dates, we will do the same – we will select several empty cells in the adjacent column and use the same function, but with a different XPath query, to get all the values ​​of the Date attributes from the Record tags:

=FILTER.XML(B8;”//Record/@Date”)

Now in the future, when changing the dates in the original cells B2 and B3 or choosing a different currency in the drop-down list of cell B3, our query will be automatically updated, referring to the Central Bank server for new data. To force an update manually, you can additionally use the keyboard shortcut Ctrl+Alt+F9.

• Import bitcoin rate to Excel via Power Query
• Import exchange rates from the Internet in older versions of Excel