Office Watch

Office 2013

Office Mobile / iPad

Office 2010

Office 2007

Office 2003

Office XP

Office for Mere Mortals

Access

Email

Buying Office

Office 365

Winks

Office News Wire

Join us!

Our Ebooks

Mobile | PDA

RSS


Search

Command Finder


Microsoft Office Bookshop

About

Home




Bring Twitter searches into Excel

How to import any Twitter search into an Excel worksheet and update it automatically

by Office Watch

Bookmark and Share

  | Mobile | click for more article services     


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 ( @<username> tweets) but you can make a custom RSS feed using the Twitter advanced search page.

  1. Go to http://search.twitter.com/advanced
  2. Enter your Twitter username into the ‘Referencing this person’ box
  3. Change the ‘results per page’ to the number you’d like.

Twitter advanced search to show mentions of a user image from Bring Twitter searches into Excel at Office-Watch.com

(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=&nots=&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 - Formatted Search results image from Bring Twitter searches into Excel at Office-Watch.com

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.

Article posted: Thursday, 23 April 2009

there's more ...

If you liked this article you'll LOVE our new ebooks.

Office 2013: the real startup guide

OFFICE 2013: the real startup guide Everything you need to know about Office 2013 but Microsoft won't tell you.

How to save money, install, configure and use the new features in Office 2013.  Get it today - click here.

Windows 8 for Microsoft Office users

Windows 8 for Microsoft Office users A practical guide the new, changed and unfamiliar in Windows 8

A focused and unvarnished look at Windows 8, especially written for the many people who use Microsoft Office  Get it today - click here.

ORGANIZING OUTLOOK EMAIL - tame your Outlook 2010 Inbox

100+ pages of practical tips and help to streamline, automate and search your Inbox.  Get more than you ever thought possible from Outlook.  Read it today - click here.

More from Office Watch:



Article Services sponsored by: Office Watch Ebooks - available now to download and read today.
RSS feed for this category Subscribe

Translate | Mobile | Links
 Add to: Bookmarks | | DiggThis | Yahoo! My Web


New & Popular
» OneNote for Windows updated
» Office for iPhone v1.2 secrecy
» Hanx Writer and Office for iPad
» Windows 8.1 update disappoints
» Adding the Ruble to Office
» Why PivotTables get confused and how to fix them


Office Watch, Office for Mere Mortals, Access Watch and all titles used within the publications are Copyright © 1996-2014 Office Watch.
Microsoft Office, Microsoft Word, Microsoft Excel, Microsoft Outlook, Microsoft Powerpoint and doubtless many other names are registered trademarks of Microsoft Corporation.

Search  |  Sitemap |  Popular Topics | Privacy Statement |  Advertising |  Twitter |  Feedback / Contact Us
Office Watch is definitely not affiliated with Microsoft - and that's just one reason why we are so useful to Microsoft Office users around the world J (Erko).