Formatted hashtags

Paul Clarke speculates here about the usefulness of Twitter, with a specific focus on whether its penetration can be used to local effect: in reporting weather conditions or traffic disruption, for example.

He cites a couple of examples of hashtags (#uksnow, #uktraffic), both of which require(d) a specific syntax following the hashtag.  For example, #uktraffic needs to of the form:

#uktraffic [road] [where: jcts or place] [direction] [description] RT&seewhathappens

By submitting hashtags in that form, it enables them to be parsed for use in other applications, allowing them to be mapped, summarised etc.

With all due respect, I don’t think the Twitter community is sufficiently diligent to obey what seem like simple instructions such as this.  Yes, they can probably remember the hashtag.  But I’m sceptical as to whether they can remember the specific items that follow, let alone the order in which they need to appear.

For syntax-dependent hashtags like this to work, there needs to be a mechanism by which they can be registered, complete with the required syntax.  That way, end users can subscribe to such hashtags, allowing their client applications to prompt them with the required syntax once they’ve entered that hashtag.  Much in the way that Excel prompts you with the arguments needed for a formula that you’ve started to use.  (Hell, even I never remember the order in which SUMIF arguments should be entered.)

Without this, the hashtags might be used, but the data they generate will be next-to-useless.

Monday 6 July, 2009. Statistically significant

I’m sure you’re all aware of the double significance of Monday.  But I thought I’d document it for completeness and for the heathens among you.

Monday 6 July, 2009 will mark the fifth anniversary of this humble little blog.  And it will also be the 40,000th day since the start of the 20th century. *

The latter fact is, of course, significant given that Excel calculates time in days using 1 January, 1900 as its point of reference.  (50,000 will occur on 21 November, 2036, in case you want to book a venue in advance.)

Happy fifth birthday for Monday, little blog.  And happy 40,000th birthday, Excel.

* Actually, Tuesday 7 July, 1900 is the 40,000th day since the start of the 20th century.  But Excel purposely included a bug making 1900 a leap year (it wasn’t; remember?) to comply with other flawed date systems.  So Excel thinks 6 July, 2009 is represented as 40,000, even though it should be 39,999.

Re-categorising: I’m scared

I’ve been putting Google Spreadsheets posts in the All things Excel category for want of a better place to put them.  Should I change the name of the category to Spreadsheets?  It seems a bit like the end of an era to get rid of the Excel word.  But times are achangin’, and I think I need to change with them.  Thoughts?

Maps and Gantts in Google Docs. FTW

I’m not sure when the feature was introduced, but you can now incorporate maps within Google Spreadsheets, maps that display icons driven from mapping search terms (e.g. postcodes) as opposed to latitude/longitude combinations.  You do so by inserting a Gadget.  Thanks to Tom Viner for pointing me to this.

This is a great step forward, as you can publish the spreadsheet and put the script tag into any html page to display it there.  The only issue is: it ain’t working for me. Apparently, my page is missing the Google Maps API.  Google doesn’t give any insight into this error message, either in its Google Documents help or through what I thought was its rather comprehensive search engine.

It’s also got a bunch of other very cool gadgets to insert.  The most useful I’ve found is the Gantt chart one.  You put your MS Project data into one sheet and you can have a very good-looking Gantt chart in another, drawing from that data.  Very cool indeed.  There are some seemingly powerful chart gadgets too.  Lots to explore.

But as for the maps, I’m flummoxed.  And I don’t know what to do.

Twoogle enhanced: Twitter to Google Docs

Google Spreadsheets introduced new functionality the other day allowing sheets to be protected individually.  Previously, you either protected a workbook in its entirety or you didn’t.

In my earlier version of the Twitter to Google Docs feed (now named Twoogle in deference to the continuing trend for Twitter apps to begin with the letters TW), I separated the Friends list from the Feed to save people breaking the latter.

I’ve just created a new, enhanced version, combining the two spreadsheets into a single one, and locking down the Feeds sheet to save people from themselves.  😉

Click here to access the new version.  Please leave comments here to let me hear your feedback.

Google Docs: getting round the IMPORTDATA limitation

There is such power to be harnessed in the link between Google Spreadsheets and Google Maps.  But to me, the power is limited by a fundamental constraint.

For Google Maps to map a location from a spreadsheet, it needs two values: a latitude and a longitude.  It can’t map places directly from the more human address and postcode data that you and I use on a daily basis.

Historically, I’ve used the following formula to get coordinate data into a Google Spreadsheet where all I have is a postcode.


With the postcode in A1, a mini-CSV file is created containing four fields that can each be accessed via the CONTINUE function that I’ve recently lamented: HTTP status code, accuracy level, latitude and longitude.

The trouble is, you’re limited to using a maximum of 50 IMPORTDATA functions in any one spreadsheet.  So if I wanted to create a spreadsheet containing the postcodes of the 500+ Starbucks in the UK to drive a map, I’d have to find the latitudes and longitudes in batches of 50, pasting their values over their formulae to create static data.  Hardly ideal.

Does anyone know of a way of surmounting this problem?  If so, I’d love to know about it.  I’d love to be able to generate maps showing things in common, but having a limit of 50 or having to use the ridiculous manual workaround described makes it not worth the bother.

Twitter to Google Docs: complete

Having done some more playing with Google Spreadsheets, I’ve got something close to a finished product: Twitter integrated with Google Spreadsheets.

Permissions in Google Spreadsheets are global.  People can either view or edit the spreadsheet.  You can’t specify which bits they can and can’t touch.  So I made the tough decision to split the friends sheet from the feed sheet.  If I’d kept them together, then by allowing people to add new friends, they would also have been able to tinker with (screw up) all the logic behind the feed.

So you add your friends here; and view your feed here.  There is a small (20 seconds or so) latency between adding your friends and their tweets appearing in the feeds sheet.  This is because the friends sheet publishes to a web page, from which the feed scrapes the content.  Doesn’t happen in perfect realtime, for whatever reason.  The good thing is that you can’t screw things up by deleting and inserting rows.  Up to 20 feeds can be added.

The CONTINUE formula behaviour for IMPORTFEED and IMPORTRANGE has made this much harder than it should have been—such a stupid idea, Google.  Thanks to Francis for discovering the friendly versions of the RSS URLs.

Anyway, enjoy playing.  I did.

Google Docs does Twitter

I’ve just been futzing around with Google Documents (namely Google Spreadsheets in this instance) to figure out how far one can go in creating Twitter in a Google spreadsheet.  While intellectually enjoyable for me, it would also provide a way of viewing Twitter updates in places where Twitter was banned.

The investigation came courtesy of a tweet from @davebriggs and a reply from @lesteph, each in response to Elliott Kember’s Spreadtweet.

So far, I’ve only managed to replicate the publicly-available feed of a single person.  For want of a better subject, I chose @stephenfry as my proverbial guinea pig.  And here’s the result.

It’s simply a list of the 20 most recent tweets from the subject of choice.  Put the feed URL in C1 and the 20 tweets automatically appear.  The page is best viewed in list view as per the link, but go back to spreadsheet view to see the logic, if that floats your boat.

The annoying thing is that unlike your standard, public-facing URLs (e.g., the RSS feed URLs are rubbish (in this case  So there’s no way that I’m aware of, apart from navigating via Twitter, of predicting a feed’s URL from the user’s name.

Also, the feed doesn’t seem to update live as new tweets come through.  I’ll find out in the morning whether it’s static (and therefore nigh-on useless), or whether it is merely a little behind in picking up users’ more recent tweets.

Next step, we’ll try to aggregate a bunch of users’ tweets into a single feed to replicate what might be a user’s Twitter homepage.

Update: here’s a slightly hacked feed of four Twitterers’ last 20 posts.  The way in which Google’s CONTINUE formula works sucks.The way in which an undefined number of cells relies on the value of the IMPORTFEED function makes artistic use of it difficult to say the least.

Update: the feeds don’t replicate live in Google Spreadsheets, but they do update.  (Refreshing the spreadsheet at 8.30am gave me tweets up to 6.30am.)  This is gonna be big!  Next steps: figure out how to automatically get the RSS feed address from a twitter name; and scale to n names rather than the four currently catered for.  FTW!

Britain’s Got Excel Talent

So, when will this show be airing?  I’m envisioning Ant and Dec hosting a programme where spreadsheet-jockeys good and bad show off their relative Excel talents in front of a hysterical, screaming audience.

Cowell will be disparaging over sloppy VBA, Holden will focus more on spreadsheet aesthetics, while Morgan will be a stalwart for formula efficiency.

Whaddya think?

Redundant SUM

Why do people use SUM when they mean nothing of the sort. I received a spreadsheet recently that contained the following formula.


I think way back when, some highly-read source must have mistakenly advised people that to create a basic arithmetic formula, you needed the SUM, irrespective of the calculation being performed.

Oh, and the 100 is to convert what would be a decimal into a number between 0 and 100 which looks more like a percentage. Schoolboy error.

← Previous PageNext Page →