alphaspirit - Fotolia

How do I query data directly from an API?

Making data available from the Web via an API may be simpler than you think. Brad Irby shows you how to query data directly from an API with Excel.

Each passing day, more companies make their data available on the Web via an API. It may seem like accessing and...

using that data is something reserved for the "cool kids" that know how to program. However, with Microsoft Excel and a little determination, you too can query data directly from an API and analyze it as much as you like.

Find the data

The first step to query data from an API is to find the data you want to analyze.

A convenient place to look is the Yahoo YQL console. Yahoo provides Web-based tools to query data from a large list of data sources, and view the results in the browser. Let's look at an example of stock prices using the table.

If you go to the YQL console, you will see a box where you can enter the YQL query. Enter the text shown below, choose XML and click the Test button.

Test button
Entering a YQL query.

In the box below, you can see the results of this query, and buried in that text is a lot of data on the Microsoft stock price. Below that text, there is another box with a label that says "The REST Query." This is the full query to run to get the data in the box. Copy it to the clipboard.

Query results
The results of a data query.

If you paste this URL into a new browser window (I'll refer to this window as the XML display window), it will give you the same data as shown in the Yahoo screen, but with a better format that is easier to read (see below). I'm using Chrome, but all browsers will give you something similar to the screenshot. Also note that I clicked on the diamond next to <diagnostics to close that node because we are not interested in that data.

XML display window
Viewing data in the XML display window.

Narrow down the data for querying

The next step is to find the piece of data you want to use and separate it from the information you don't care about. Open another browser window and go to XPath tester. This is a page that will help you explore XPath, which is a query language used for querying XML.

In the Option 1 box, paste in the URL you copied from the Yahoo YQL console, then put a forward slash (/) in the XPath expression box and click the Test XPath button (see below). In the XPath result box, you should see the same text as is in the screen shot. If you have a different XML data source you would like to use, put that in the Option 2 box instead.

XML input
Using XPath to query SML.

If you are not an XPath expert, the easiest way of finding the data you want to use is to "sneak up" on it by trying various strings to see what you get. If you look at your XML display window, you'll note that the first line has the text "<query xmlns:…." Strings that start with "<" denote elements in XML terminology, and it is an element that we are trying to find. Change your XPath expression to "/query" and click Test XPath, and you will see the XPath result change to show the text in your XML display window.

By looking through the XML display window, we find that the ask price for Microsoft is located inside the "quote" element, which is inside the "results" element, which is in turn inside the "query" element. Changing our XPath expression to "/query/results/quote/Ask" gives us the data we are looking for. (Note the XPath expression is case sensitive.)

This expression will show the data you need
This expression will show the data you need

You can play around with the XPath expression as much as you like to experiment with getting other data.

Pull the data into Excel

Now that we have all the pieces in place, we can finally start to pull this data into Excel for analysis. Open a new spreadsheet and in cell A1 type "=WEBSERVICE("PutWebServiceURLHere")", while putting your Web service endpoint address between the double quotes. Excel will pause for about 1 second, and then it will show a long line of XML text -- the same XML text that we have been working with.

You now have all the raw XML in Excel. This value will update each time you open the spreadsheet.

Now we just need to whittle down the data coming back
Now we just need to whittle down the data coming back

This is great headway, but the result coming back from the API is too much data to work with. We want to limit it to just the data we are concerned about.

In cell A3, type "=FILTERXML(A1,"/query/results/quote/Ask")". The first part of this command refers to the full XML data we queried in cell A1, and the second part in quotes is the XPath query we constructed using the XPath tester. You should now see just the ask price for Microsoft stock at the close of the day.

This is the view of the data we want
This is the view of the data we want

If you want to use multiple values from the same XML feed, just add another FILTERXML command. Here I'm also pulling out the volume for Microsoft.

The volume for Microsoft is pulled from the same XML feed
The volume for Microsoft is pulled from the same XML feed

Now you no longer have to rely on a developer to build tools for data access. You can build your own tool in a matter of minutes.

Next Steps

What CIOs should know about the API economy

How to cultivate the API economy

An intro to REST APIs in SDN

The advantages of using a build server

Is SOA the answer to business data management risks?

Dig Deeper on Distributed application architecture