Excel-based site map generator
A good six or seven years back, I developed an Excel spreadsheet that took a hierarchical data feed of pages (page name, level in the IA, page type) and generated a visual representation for you on the fly. A crop of the visual is shown below.
It used conditional formatting up the ying-yang, with particular complexities around developing the interconnects between the boxes. While there were some macros to re-hash the incoming data, the visuals were all based on conditional formats which were at the time limited to a maximum of three (plus the default format) per cell.
All in all, it was awesome. (If I may say so.) If anyone out there has a use for it, let me know.
The UK’s tax distribution
Someone on Question Time the other night—I forget who—stated that the only way to recoup significant levels of tax was to tax the poor. I decided to do some analysis, and found that it’s largely true.
As of 2004/5, according to HMRC, the mean income of taxpaying individuals was £22,800, the median being a good deal lower than this—61% of taxpayers earned £20,000 or less. If every pound of income generated the same amount of tax, then 52.3% of tax would have come from those earning £30,000 or less six years ago. And less than 25% of tax would have come from those earning £50,000 or more.
Now arguably, the greater one’s disposable income, the more tax those pounds generate—some of the essentials in life are exempt from VAT. But this effect will be marginal.
So the panelist was pretty much right. To have a significant bearing on the amount of tax brought into HM Treasury (via the front door of 100 Parliament Street), you have to go after the low-end of the pay scale.
Twitter determines my aroma
I undertook my first piece of crowd-sourcing today. It related to my armpits.
Over the course of my adult life, I’ve gradually settled on the regular contents of my bathroom cabinet. Dax is my hair gel of choice. I use Boots’ range of moisturisers and shave gel—the latter rather rarely. Euthymol has become my toothpaste of choice. And the Groom Mate Platinum Xl Nose has a lifetime guarantee—a fabulous product, if I may say so.
But I’ve rarely been happy with my underarm deodorant. In the States, I settled on something called Speed Stick, which was fabulous. But they converted it from a stick to a gel after we left, and after trying the latter I deemed it not worth importing.
So I used Gillette’s gel for a while, not being overly happy with it but neither being happy with any of its obvious competitors. So on the way to Boots this morning, I turned to Twitter. I asked for advice. And advice I received:
- Seany_G: mitchum roll on. It’s all rubbish, but this much less so.
- gregnbaker: Mitchum!
- simoneverest: slightly delayed but ‘Clinique rollerball’ my pref… (sadly too late to inform the decision)
- Anonymity respected: I use the women’s stuff. Mum liquid roll on in particular. Those women don’t fuck about in this department.
I’ve gone for what I believe is a unisex Mitchum. Three, actually. It will be aired for the first time tomorrow, and the Twitter community will be kept informed as to its progress and my satisfaction. Meanwhile, if any of my colleagues suffer any adverse effects, please update me accordingly.
SEOs: they don’t O SEs at all
I was called by an SEO the other day. That’s a Search Engine Optimizer [sic] to those fortunate enough to be unware of their existence. The call ended with me hanging up on him, such were his cock-like credentials.
I’ve never been happy with the concept of an SEO. It’s basically someone who understands enough about how the internet works—or more specifically how search engines work—to advise on how best to write, tag and structure your web pages to get them naturally to the top of search rankings.
The offline equivalent would be some form of location specialist, advising companies where best to position their shops to maximise footfall. But the online version has adopted somewhat legendary status, seeming to me to have invented an industry where one was not particularly needed. The very existence of SEOs means that SEOs have to exist, to compete with their counterparts.
Seth Godin today wrote tangentially to this very topic, signing off with:
That’s one reason I resist the temptation to optimize this blog for traffic and yield. I’d rather force myself to improve it by having the guts to write better posts instead.
I’ve always agreed. I need to make my service better and more attractive through its attributes and my reputation, as opposed to artificially improving my perceived quality by bumping my results up the rankings through clever tagging.
The very term search engine optimization makes my blood boil. It isn’t about optimizing search engines. It’s about frigging search engines such that they think you’re better than you are. To me, SEOs are the scourge of the internet.
Changing space/time for fire engines
The world is an inefficient place. If fire stations are positioned such that all places can be reached within a specified length of time, then their circular catchment areas necessarily overlap, assuming that distance from the fire station is directly proportional to time taken to get there.
It would be much better if space/time was warped such that fire engines could reach every point around the perimeter of a square within a specified length of time. That would make for a much more efficient fire service.
Distances as the crow flies: the solution
Yesterday I posted about joyful Tuesday, a day I spent lost in Excel, data and maps. And as requested by @lesteph, here’s the solution I developed. I’ve sanitised it, but the principles still stand.
The spreadsheet takes six places: Edinburgh, Bristol, Cambridge, London, Cardiff and Halifax. It shows their latitude and longitude and uses the following formula to calculate the distance in kilometres to one decimal place between each pairing.
=ROUNDDOWN(6371.0072*ACOS(COS(RADIANS(90-lat_from))*COS(RADIANS(90-lat_to))+SIN(RADIANS(90-lat_from))*SIN(RADIANS(90-lat_to))*COS(RADIANS(long_from-long_to))),1)
lat_from is the latitude of the “from” location, and you can guess the other three such references. The references in the spreadsheet itself are cell references to allow easy copy/paste action.
Conditional formatting hides the lower half of the square, and emphasises those places that are closest together.
I made the schoolboy error of hardcoding the earth’s radius (6,371.0072km), meaning that the model does not scale to other planets or moons in our solar system. Apologies.