Excel VBA, Get Data From Web Using MSXML
Previously in the article below I’ve explained how you can get data from the web using query tables in VBA:
The problem with using Query Tables, is that they are slow. A faster method would be using the MSXML object. The downside of using the MSXML object is that you can’t use it on any site. The MSXML object is actually used to connect to an XML file. In this article I will briefly explain how the MSXML object can be used to get data from the internet. For a more in depth explanation about the MSXML object please see the link below:
Some websites offer an API. This API is basically an XML file. For example lets say you want to build an application that can get the exchange rate for different currencies. There are many sites offering an API to the different currency exchange rates, most of them require a subscription. You can find them by googling the term “Forex API”. Or for example lets say you want your program to connect to the internet and get the current time and date. google the term “Time API” and again you will see a list of sites offering an API that provides the current date and time.
API’s, What are They?
In start of the article I mentioned the MSXML object can be used to get data from APIs on the web. But what are these APIs? I’m not going to get into the technical details, but basically an API is an XML file. It will look something like this:
As you can see the API has the following structure. There is a Query tag, then a Result tag inside it, then several Rate tags. Each Rate tage has 6 different tags. You can see the structure of this API below:
RSS feeds are also an XML file and therefor you can connect to them using the MSXML object. The figure below shows the RSS feed to my blog:
As you can see (similar to the API in the previous section), the RSS Feed consists of several different tags.
Connecting to the Web:
Once you have the URL of the webpage you can connect to it using the methods below:
Method 1, Late Binding:
The line below creates the MSXML object:
If the async property is set to false, the program will wait for the XML file to load before executing the next line. If its set to true, the program will continue execution even if the XML file has not been loaded. By default the async property is set to true, which may lead to errors if the XML file hasn’t finished loading and we try to access it:
The last line loads the webpage with the XML API:
Another method would be using early binding, in this method a reference must be created to one of the Microsoft XML Libraries:
We could then use a code similar to the one below to connect to the XML file:
Getting Data Using the MSXML Object:
So now that we have connected to a webpage with an API, how can we get the data we are after? Consider the first example where we were trying to get data from the page USD Forex API. Below I have brought the XML on that page:
Let’s say we want the bid value of the USD to BGN. This has been highlighted above.
object consists of nodes. Each node may consist of several child nodes. Each node represents a tag and the value the tag contains. One method for getting the bid value is to manually go through the nodes until the required node is found. This method is explained in Method 1.
Another method would be to programmatically iterate through the nodes until the required Node is found. This is explained in Method 2.
Copy the following code to the VBA Editor:
object to the watch.
As you can see the First Child Node of the
variable contains several Items. Each of these items are itself a Node. Basically what we are trying to do is to go through all the nodes until we find the node that contains the USD to BGN Exchange Rate Bid price. This value is contained within a Bid tag. The Bid tag is contained within a Rate tag. The Rate tag is within a Result tag and that is within the Query tag:
So we need to work our way from the top Node (Query) down to the Bid node with the USD to BGN exchange rate.
As mentioned in the previous step in the XML Page the highest level tag is the Query Tag:
So basically what we need to find is the Query node. By going through the child nodes you will see that the Query Tag is stored in the second node:
Repeat steps 5 and 6 for the current node. That would mean opening the ChildNodes of the current node and looking for the Node which contains the Result Tag.
It can be seen from the figure below that there is only one node in the Query Node and that is the Result node:
Repeat step 7 to find the Rate Node that has the USD to BGN Exchange rate. This is stored within the 3rd Child Node:
Repeat step 7 to find the Node which contains the Bid data. This is stored in the 6th child node:
As you can see the value of the USD to BGN exchange rate is contained within the nodeTypedValue variable of this Child Node:
Probably wondering why we went through all those previous steps? By going through all those steps we now know that the value we are after can be found:
- In the NodeTypeValue variable of the final Bid Node
- The Bid Node itself is the 6th Node of the Rate Node
- The Rate Node is the 3rd Node of the Result Node
- The Result Node is the 1st Node of the Query Node
- The Query Node is the 2nd Node of the
Keeping in mind that child node collections are zero based (therefore the first node has the index zero “0”) we can reference the USD to BGN Exchange rate using the code below:
As explained in the previous method we are after the Bid tag (Node) with the USD to BGN exchange rate. This Bid tag (Node) is inside a Rate tag which has a Name tag with the value “USD to BGN”.
The Rate tag itself is inside the Results tag, which it is inside the Query tag:
This can be achieved using the code below:
The code below loops through the top level nodes (tags) looking for the query node (tag):
The next block of code loops through the child nodes in the query node searching for the results node:
The last block of code searches the child nodes of the results node (rate nodes). It searches for the rate node that contains the USD to BGN exchange rate:
You can download the file and code used in this article from the link below:
- Excel VBA, Retrieving Data From a Website Using a Query Table
- The Document Object Model in Visual Basic