How to import any Twitter search into an Excel worksheet and update it automatically
In our article Using Excel (the real one) to read Twitter we talked about importing your incoming and sent tweets into an Excel worksheet. You can do this to disguise Twittering from your boss however there can be good reasons for doing it. Twitter is becoming part of corporate and marketing communication plans and part of that is tracking what’s happening on Twitter.
After you’ve imported Twitter information into Excel you can search, share or re-order the data just like any other data feed.
In this article we’ll show you how to import a list of Twitter references to a particular username into Excel. Twitter has a web page for each user (the @MyUsername) link on the right-side of your personal Twitter page. However it doesn’t have an RSS feed link to that data on the page, as it does for incoming tweets and your updates.
The same methods demonstrated here can be used to automate and import any Twitter search.
Using Excel (the real one) to read Twitter is a practical example of how to import any RSS feed into Excel – whether it’s from Twitter.com or not.
References to you
Your standard Twitter pages don’t show an RSS feed for messages that reference you ( @
- Go to http://search.twitter.com/advanced
- Enter your Twitter username into the ‘Referencing this person’ box
- Change the ‘results per page’ to the number you’d like.
(Of course you don’t need to enter your username – there’s no reason why you can’t enter any Twitter username and track mentions of them)
Getting an RSS feed
The link supplied by Twitter has some problems that are easy to fix if you know the magic spell.
When you click Search, check the results to see if they are what you want. Once you have the search correct go to the address bar and copy the entire url (not the ‘Feed for this query). The search url looks like this for a search of tweets mentioning ‘@MyUserName’:
http://search.twitter.com/search?q=&ands=&phrase=&ors=¬s=&tag=&lang=all&from=&to= &ref=MyUserName&near=&within=15&units=mi&since=&until=&rpp=50
As you can see, there are many empty search parameters. For our modest purpose the important things are &ref= and the last parameter &rpp=50, this specifies the number of results to return (something the default feed won’t let you do) up to maximum of 100.
To change this to an RSS feed simply add .rss after the word ‘search’ and before the first question mark. Here’s the same search as above converted to an RSS feed and unused search parameters removed.
http://search.twitter.com/search.rss?q=&ref=MyUserName&rpp=50
You can easily tinker with the search url manually though generally speaking its easier to use the Twitter Advanced search page to make it for you.
Tip: test your search RSS feed url in a feed reader like Internet Explorer 7 or 8 to make sure it’s correctly formed before entering into Excel.
Formatting
Once you have the feed url working as you’d like import it into an Excel worksheet in the same way as we discussed in our article Using Excel (the real one) to read Twitter.
The column listing is different but the principles are the same ie. hide most of the columns. We hide everything except pubDate, title2, guid and author then format those to suit our taste including:
- Wrap Text on the title2 or tweet column so it can all be read in the space available.
- Middle Align all cells so they line up nicely with the wrapped text cells.
- Change the column headings for example ‘title2’ can become ‘Tweet’.
Excel 2007 inserts the feed as a table, so you can adjust the formatting from the Table Tools | Design tab from the Table Styles gallery or you own style.
Sorting and Filtering
Each column heading has the usual sort and filter pull-down list.
This would be useful on the ‘Mentions’ worksheet to sort or filter the list by author.
Changing ATOM to RSS
Do NOT use the ‘Feed for this query’ link near the top of the right-column. The default Twitter feed for search results is ATOM, a type of RSS feed that Excel can’t cope elegantly with. In addition not all search options are passed into the default search feed url.
If you do want to use the supplied feed it will look like this:
http://search.twitter.com/search.atom?q=+%40MyUserName
Happily its easy to fix by changing .atom to .rss – the same results come through in RSS format:
http://search.twitter.com/search.rss?q=+%40 MyUserName
Anything Else?
The developers of Twitter have cleverly developed a set of broad tools that can be applied to a wide range of situations. For example the Office Watch ‘Office chatter’ pages, that grab the most recent mentions of Office programs from the Twitterverse, is a modest extension of the Twitter API.
Pulling Twitter data into Excel opens up a huge pile of possibilities and we’ve just dipped a small toe in the water. If you have any tweaks or extensions on the basics we’ve described we’d love to hear from you.
Follow Office Watch on Twitter – details or direct link.
See Also
- Singular / Plural text in Excel
- How to Twitter from Excel
- Using Excel (the real one) to read Twitter
- Disguise Twitter as an Excel worksheet
- Office chatter on Twitter
- Office Watch comes to Twitter
