Analyzing Twitter with Excel, Part 2

After looking at the update of a product that Mark Gibbs was, to put it mildly, rather critical of, he continues his self-appointed task of using Excel to analyze Twitter data.

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.

In that piece I noted how SAP had added a ton of features to the 4.5 version of Xcelsius but threw out stability, reduced usability and apparently considered documentation to be optional. The phrase "epic fail" came to mind. Since then SAP has released a number of updates, the most recent being Xcelsius 2008's Fix Pack 3.

Having beaten this update to death I conclude the following: Stability is much improved, bugginess has been reduced but is still a problem, usability is slightly better but still has an engineering feel, and documentation and error messages are still abysmal. That said, Xcelsius 2008 is somewhat improved and gets a score of 3.5 out of 5.

Learn more about this topic

Analyzing Twitter with Excel, Part 4

Analyzing Twitter with Excel, Part 3

Analyzing Twitter with Excel, Part 1

From CSO: 7 security mistakes people make with their mobile device
Join the discussion
Be the first to comment on this article. Our Commenting Policies