Sunday, 2 December 2012

My multicoloured spreadsheet

WARNING - the text below contains levels of geekiness that some readers will find offensive and all readers will find boring.

I’ve been actively keeping a patch list for 5 years now, although it was only recently that I decided to keep it all in my amazing multicolured spreadsheet. This is different from the ‘Patchwork’ spreadsheet (that contenders have received, or will receive soon) in that it doesn’t calculate any scores. Instead, it allows me to compare the ‘anatomy’ of each year’s list,  noting how many species I see every year, which species I’ve seen in one year, two years, three years etc, and showing which months have been most productive.

Over the course of the last five years I’ve averaged 127.2 species per year (assuming I finish on what I’m on now). This is a little lower than perhaps it should be as for the first few years I posted relatively low scores due to the fact that I lived a 45 minute walk away, rather than the 3 minute walk I have these days (….that house move was no coincidence by the way). So, for the sake of argument I’ll say that my average is 130 spp/year.

Of these 130, the vast majority, over 90, are birds I see every year. These ‘boring’ birds get a boring grey highlight on the spreadsheet, but include some species that could well be missed – eg redstart and black guillemot (tystie is not common up here at all). So of these 90+, perhaps 80 are totally guaranteed.

Pinky red birds are birds I’ve missed on one year out of the five years. Essentially, I see these birds as ‘grey’ species that I’ve managed to miss by being rubbish, or, in the example of barnacle goose this year, being away from home during the peak passage period. The blue birds are ones I’ve missed twice, green the ones I’ve missed three times (probably more sensible to think of them as rarer birds I’ve seen twice), and the peachy birds are ones that I’ve only recorded on one year. This is what it looks like at the business end...

You’d expect the peach and green species to be the rarest birds, but there’s plenty in there that only a patch birder could get enthusiastic about – little grebe, bullfinch, stock dove, coal tit for example, sitting nicely alongside desert wheatear, white-billed diver and surf scoter. One thing that surprised me initially is that the peach zone is bigger than the green zone. The peach birds are the rarest birds on the patch, but the pool of potential new species is much larger than the pool of potential second records, hence more single record birds.  The screengrab below shows the full set of 1, 2, 3, 4 and every year birds - look at the gulf in quality in the right hand column...!      

As well as giving me something to do while the missus is watching Eastenders, my amazing technicolour spreadsheet allows me to target certain species – which grey, red and blue species I need and have a chance of seeing, etc. It also gives me a chance to work up some pretty pointless graphs, the likes of which I will show you below.....

I have to warn you that there is nothing earth shattering coming up....

 The graph above shows the accumulation of species per month. It will surprise no-one that apart from January of course, the largest gains are made in spring and autumn. What this doesn't show is that May perhaps is not the most valuable month...most of the birds seen in May are common migrants that would easily be seen at any point during the summer and autumn. This year, despite being away for a week at the beginning of the month, October has been the most valuable month in terms of adding the most 'unusual' species...

October has also been the best month in terms of point scoring. Two 6 pointers (yellow-browed and barred warblers self found) as well as RBfly (3 points, found by 'the other guy'), and a few 2 pointers.

So, stop the press, October is good for birds in general, and especially finding rarer birds. I did warn you not to expect too much!

I should make it clear that this level of geekery is not necessary to take part in the competition - the spreadsheet we'll send you is as simple as adding species to a list, some fancy lookup queries do the rest. It would be reassuring to know that I'm not the only one out there who does this sort of thing. Please, now I've come out as the geekiest type of patch geek, I need some support. Anyone else have a multicoloured spreadsheet?



  1. I have produced similar breakdowns for Galley (but it was aaaaaaaaages ago (pre time-sapping kids) and I don't think they were multi-coloured (or maybe I'm just deluding myself!).

    I never did the graphs tho! That's just weird and obsessive!

  2. Had a deliciously multi-coloured spreadsheet when I lived in Shanghai but by site (within city). Now do a range of borderline geeky patch analyses via BirdTrack Explore My Records - reporting rate (% of complete lists that feature a species) always fascinating... to me! Best e.g. so far is Little Egret: 4% of complete patch lists in 2010, 12% in 2011 and 34% this year! More on all this very soon...

  3. I'll bet you know that May will run October close in 2013... I get many more migrants and more waif and strays in May as long as there is a run of SE'lys