Skip Links

Analyzing Twitter with Excel, Part 2

By , Network World
April 09, 2009 08:31 PM ET
Gibbs

Network World - So, last week I set myself a problem: Use Microsoft Excel to capture Twitter messages that mention the word "rovio", pretending that I worked for the company that makes the Rovio Wi-Fi-controlled camera and I wanted to see what people were saying about it.

I set this all up to demonstrate Excel's XML Maps feature. The concept was to define an XML Map that retrieved XML data from Twitter's search feature. This feature offers to generate for your queries a news syndication feed in Atom format (that's a format much like RSS but slightly different for no particularly good reason). This is, in theory, great as Atom is a format that Excel understands.

After finishing last week's column I had to drop the project for a couple of days while I put out other fires. When I returned to it I discovered that things weren't working as I had expected.

If you recall the feed URL as provided by Twitter was:

http://search.twitter.com/search.atom?q=+rovio+since%3A2009-03-01+until%3A2009-03-25

I modified this as Excel didn't want anything to do with it for no obvious reason (the error reporting in XML Maps is, to say the least, poor).

I found that by replacing the '+'s (which stand for spaces in a URL encoded string) with "&"s (which separate variable/value pairs in HTTP GET requests) Excel would create an XML Map. I also added other arguments to set the number of results per page (rpp) and which page of the results should be returned and this is what I wound up with:

http://search.twitter.com/search.atom?q=rovio&since%3A2009-02-01&until%3A2009-03-24&rpp=50&page=1

Unfortunately this only appeared to work when used in Excel. The Twitter search just ignored everything after the first '&' and returned the last results available for the given search.

What this comes down to is four issues: The Twitter search interface is poorly documented; the Atom version of the search doesn't support all of the additional arguments (such as results per page); Excel is too fussy about which URLs it is willing to interrogate; and Excel also gives you precious little information about what it doesn't like.

This is all very annoying because I've used Excel XML Maps with all sorts of XML sources before and never had problems like these. I now hate both the Twitter and the Excel developers with a passion.

So, it looks like I'm going to have to get cunning. I still want to use Excel because it allows me to create summaries, pivot tables (which I discussed some time ago) and graphs, but it looks like using XML Maps to get the data directly isn't going to work.

Here's an idea: Let's use cURL to retrieve the raw XML rather than relying on the vagaries of what Excel might or might not consider acceptable.

And that's just what I'll do next week when I have enough space to wax lyrical on the topic.

But to close this week I want to go back to a column I wrote about SAP's Xcelsius.

Xcelsius is a dashboard development tool that ingests an Excel spreadsheet and lets you attach graphical meters, sliders and graphs to the underlying data and generate a Flash movie that can be used on Web pages.

Our Commenting Policies
Latest News
rssRss Feed
View more Latest News