It’s true that Excel has been a decreasing part of my toolkit for several years now, and that I never quite had the love for it that I do for various database managers. But I’m guessing that’s the exception, not the rule, in the broader journalism community. So when it came time to propose a lightning talk for the 2010 CAR Conference last week, I chose to pull out the ol’ spreadsheet and show how you could get started with the NYT’s Congress API with a familiar tool.
To do this, I had to not only drag out Excel but also do it on Windows, since Excel’s Web Query feature isn’t available on the Mac. (You could also do this, albeit in a slightly different manner, using OpenOffice and Google Spreadsheets.) Here’s how it works using Excel.
First, you’ll need an API key. To get one, go to The Times Developer Network and register (note: you’ll need to be a registered user of nytimes.com first).
You’re registering an “application”, and then you can add specific API keys to that account. Let’s add one for the Congress API. The key itself is a longish string of letters and numbers that gets appended to every API request URL, including the ones we’ll make from Excel. Let’s copy the API key so we can easily grab it (note that this particular key has been disabled, so using it won’t work).
Let’s find an API call that we can use be looking at the Congress API’s documentation. Let’s pick the “members leaving office” response, otherwise known as the casualty list. All that’s required is the chamber (‘house’ or ‘senate’) and the congress (currently only the 111th-forward is supported). If we choose the House, the URL will look like this, except that you’ll need to specify your Congress API Key.
The version number should be “v3″ and you don’t need to specify a format after leaving (xml is the default). You should quickly get an xml file that looks roughly like this:
To get that xml into Excel, we’re going to use Excel’s Import Data feature. I’m not one of those cool kids who has Excel 2007 at their fingertips, so I’m going to use Excel 2002. Import Data can be found at Data -> Get External Data -> Import Data.
Then change the file type to xml and paste the full API url into the box just above the file type.
It works for local files and Web urls. Then click on “Open” to start the process. The import process consists of Excel asking you where to put the file. Just click “OK” and you should soon see something like this:
The header row in row 2 isn’t perfect, but it should suffice. You probably don’t need the copyright statement in column A. But now you’ve got a way to pull data into Excel from an API! If you have questions or comments, please don’t hesitate to post them below. If you’re having issues with the API, the forum is the best place to head.