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...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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 yahoo.finance.quotes 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.
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.
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.
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.
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.)
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.
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.
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.
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.
