Friday, 30 August 2013

An experiment: How much do we need Google, Facebook, Microsoft...?

It's time for a project. The house is decorated, the motorbike is running sweetly, my football data sources have dried up (for the moment, anyway) and the British weather isn't playing ball with paragliding conditions.

Spare time alert. I need a new project.

A few different things have consolidated together into Wallpapering Fog's next occasional series of articles.

Plus a family member asked how to avoid all this online tracking and I said that beyond a few anonymising and blocking tools like Adblock, Disconnect and Ghostery, I didn't really know.

What kind of a data monkey gets asked a question like that and doesn't really know? Poor form, that. Very poor form.

Today, @Wired flagged up a new website which lists some open source tools and services that can be trusted. You can take a look at

So how easy is it to run your own tools, instead of taking the commercial software and social network routes, in return for being tracked and advertised at?

Don't get me wrong, I'm a big fan of Gmail's interface and I like Google+ for its photo galleries. I don't really like Facebook but the paragliding community has started to organise itself on it, so my account needs to stay. This isn't going to be a toys-out-the-pram abandonment of the corporate web, but I want to know how easy it is to get along without the big boys' toys. Can a fairly techy analyst do it, or is it hardcore geekery, reserved for the people who wear Linux t-shirts and code their own printer drivers? How far can you stretch a £35 Raspberry Pi? Do we really need Facebook as a platform for sharing, or are we just lazy?

A few things I know I want to do are...

  • Set up a Raspberry Pi to run my own cloud storage (replacing Google Drive)
  • Stick Linux (which one? So much to learn...) on our old laptop at home, replacing Windows
  • See if I can run my own email (replacing Gmail)
  • Build a photo gallery website (replacing Google+ photos)
And there are bound to be more projects that start to reveal themselves along the way.

I've got an idea that centralising web services is, in many cases, stupid. One Facebook data centre has got so big, it generates its own indoor rain clouds... Why not take back your data, onto your own small-scale system that you control? The only reasons I can see why not, would be if it's hard, unstable, or expensive. Let's find out if it's hard, unstable, or expensive. If nothing else, it will be a fun journey!

Raspberry Pi ordered and when it arrives we'll set about finding out and documenting what can be achieved by an enthusiastic amateur with a few hours to kill.

Monday, 12 August 2013

Top 10 Excel Sins

If you work in a marketing agency, you see some horrific Excel abuses. Here are my top ten.

Do you do any of these? For the love of God, stop it. Just stop it, right now.

Typing numbers straight into a spreadsheet, with no hint of where they came from
Number 1 deadly sin. Anyone doing this deserves to lose a finger. Maybe their left hand.

Wonderful things happen in Excel when you type "=". You can add stuff together! You can multiply! The next person who comes along after you, can understand what you did! It's marvellous and you should definitely try it.

An ex-colleague used to use Excel like a piece of graph paper and work out all his sums separately with a calculator, then type the answers onto an Excel worksheet. This is second only to using Tippex on your computer monitor. If you type numbers straight into cells, instead of leaving a trail by working them out with a formula, you're just as bad.

Hiding cells
To be fair, this is sort of Microsoft's fault. The hide cells functions shouldn't exist, or if they must exist, it should be incredibly in-your-face obvious that something has been hidden.

Barclays offered to buy 179 contracts that they didn't actually want, from the bankrupt Lehman Brothers, due to hidden rows. You have been warned.

If you absolutely have to hide things, use Group. It's not so well known, but it's much more obvious what you've done.

Shrinking column widths, until the column disappears
A favourite of people who don't know how to hide cells. This is so monumentally stupid, you shouldn't be allowed to use Excel ever again.

Colouring in cells to represent data
Want to piss an analyst off? Do this. You've got a big list of something - maybe a list of customers - and you want to highlight some of them as being your best customers, so what do you do?

You could type "best customer", or even better "TRUE" in a new column next to the customer names. That would be good, because then you can filter them, or use that column in formulas, or pivot tables.

Or if you're evil, you could colour all the best customers in yellow, so that anybody who wants to work with only the best customers, has to do it by hand.

Guess which one most marketing people pick?

Using Excel's default charts
Grey and two shades of purple either screams "newbie", or "incapable". Which one would you prefer?

Using Excel's 'exotic' charts
Step away from the 3D pie charts. Here's why.

Using loads of named ranges
In moderation, named ranges are mostly ok. Excel has a bad habit of corrupting them without you realising, but they're not so terrible.

Opening a workbook that has hundreds of the things in it is horrible though. Unpicking how a number is calculated, when at every step you have to look up a name, then find out what that name refers to, can really ruin your day. Names are great while you're building a spreadsheet. Six months later, when you can't remember what you did, they're a proper pain in the neck.

Inconsistent logic
This is how big mistakes happen. Really big, expensive mistakes.

Sometimes you've got a big grid of numbers - 1000 or more rows of calculations and a few rows need to be "fixed". The tracking was out of line that week and needs to be adjusted downwards 10%, or certain rows don't have VAT added, while others do.

You could manually edit those numbers that need changing, or alter the formula in those cells, to add on VAT.

Now what you've got is a big column of numbers that look like they're all calculated the same way. Except that starting from row 800, the formula changes.

You will forget that you did this. It is inevitable.

At some point, somebody - probably you - will want to add some more rows to the data and when you do, you'll copy the formula downwards, assuming that everything below it is the same. At this point your carefully edited "VAT" rows will disappear, your final answer will change and you'll have no idea why, or what happened, or how to get back to where you were.

You're screwed. And you deserve to be.

Macros for everything
Excel Macros are tremendously useful. They're the the tool that brought IT capabilities to massed ranks of analysts and even if it's getting on a bit, I still think Visual Basic for Applications (VBA) is brilliant.

But. And it's a big But. Most people who get good with VBA go through a few stages. First, you can't make it do very much. Then you get better and you can build macros to do almost anything, so that's what you start to do.

Stage three is where you realise that Excel actually had functions and shortcuts all along, to achieve the same as many of your macros, only faster and better. Don't let yourself get stuck on stage 2! You'll waste tons of time programming and the workbooks you build will only ever function properly on your own PC, where your macro library lives.

Whatever the problem, Excel is the solution
Excel's great, everybody's got a copy and it's so flexible, you can do almost anything with it. But that doesn't mean you should...

Excel isn't a word processor, an illustration package, a dashboard designer, a database, a calendar and it also isn't many other things, even though you can usually make some passable looking output with it.

When Excel starts to get frustrating, there's probably a better piece of kit out there for the job and that better piece of kit is very often free. Stop abusing Excel and go and look for it!