Data Analysis & Information Management
With a background in business analysis, moving, interpreting and sharing data is a big part of my life. Consequently, I just love a good spreadsheet and creating robust and dynamic solutions with them.
Mention spreadsheets and Microsoft Excel springs to mind. Indeed, I'm as enthusiastic about Excel's Power Query as the next person. However, in recent years I've become a power user of
It's a superb tool for collaboration and/or remote working, has a clean UI and for 95% of Excel users, is "good enough." Having used SQL, I find Google Sheets' built-in
to be extremely powerful and use it extensively. I'm also finding more and more uses for
Google Apps Script
You can get in touch with me here.
Data Analysis: I've probably got a spreadsheet for that
Here you can access live examples of how I can import, transform and visualise data using Google Sheets.
Portfolio of 110+ Google Sheets - A Google Drive folder listing practical and creative uses of my function/formula knowledge. Note, the vast majority of the techniques demonstrated are directly transferable to Microsoft Excel. Click on an example to open up a sheet and review in your browser
Wales 6N Rugby App - Disappointed by the lack of a decent Welsh-focused app for the 2020 Six Nations rugby tournament, I made my own using the Glide platform, powered by data held in Google Sheets. It got a bit out of hand with the amount of data I ended up crunching! Gave it a miss for 2021
Below are examples of automated dashboards and analytical tools I've published online from Google Sheets. Just click on the links to view them in your browser.
COVID-19 UK Vaccination progress dashboard - Imports GOV.UK data at c.5:00 PM BST each day and utilises the SEQUENCE function to visualise progress made between those receiving just one jab of the vaccine compared to those fully-vaccinated as a % of total UK population
COVID-19 UK Daily Cases & Deaths - An all too topical example of
publicly available data being imported into Sheets with a scheduled Apps Script, parsed with QUERY & used to power a fully-automated dashboard. Updates c.8:30 AM BST, daily
Citymapper Mobility Index for London - Data imported from a Citymapper CSV file helped to visualise the impact the pandemic had upon travel in London. Key lockdown milestones indicated. Citymapper ceased updating the daily data as of 6 Sep 2021
Weekly UK road fuel prices analysis - This dashboard imports GOV.UK data on pump prices, conducting analysis on taxation composition and providing historical context for the most recent prices. Updates every Tuesday
Google Finance chart with dynamic labels - This chart of the GBP to USD exchange rate for a rolling 13-week period, illustrates how employing dynamic labels can help provide insights on the chart's data. For some reason, a few reloads of the page may be required should 'no data' appear at first
RATP Dev London Bus Route Analysis - An example of how live website content can be analysed in real time
US Presidents Age Analysis - Wikipedia data imported, analysed and charted for the ages of the 46 Presidents of the United States upon first assuming office
UK General Election 2019 - This re-models and visualises live data imported from a publicly shared Google Sheet illustrating the MPs that stood down in that year's election
Current UK Parliamentary seats by party - Live chart built from a CSV imported from
the UK Parliament's website
Sheets v Excel - Which one has the most functions? Well, there's only one way to find out. Make a live dashboard using IMPORTHTML, QUERY & INDEX to find the winner!!
Dynamic analysis of current month - An example of the kind of dynamic date analysis that can be easily built into business planning dashboards. Extending this approach further, this dynamic schedule, details first work day, total work days and bank holidays per month
5 years of Todoist - Combining data from each of the "your year in review," summaries Todoist has issued since 2016, Sheets helped me gain some further insights on my usage of this excellent task management application
Google map of Welsh SIMM summit peaks - Built from both online data and the what3words API
PDF Data Extraction - An example of how data held within PDFs can be extracted as CSV files for use in spreadsheets using Tabula
Rugby Union - Wales v England - All results from 2000 analysed - References live Wikipedia data and updates its insights automatically whenever the base data gets edited
RWC 2019 pool stage analysis - Transforms Wikipedia data to deliver insights on the pool stage of the Rugby World Cup held in Japan
Six Nations - England v Wales - Analysis of the 22 halves of rugby played between the two in the Six Nations at HQ
Six Nations - France v Wales - A more conventional dashboard, this time for Wales in Paris, illustrating the types of analysis & data visulaisation that can be achieved with Google Sheets
Six Nations - Wales Win/Loss analysis - A creative use of Sparklines to visualise Wales' performance over the full 22 years of the Six Nations tournament, up to and including its most recent match
Wales World Rugby ranking at start of 6N - This charts and analyses the World Rugby ranking for Wales at the start of each Six Nations tournament, going back to 2004 when rankings were first available
Wales World Rugby rankings analysis (live) - Tracks Wales' position in the World Rugby rankings for every week since 2014. Pulls data from the API using this IMPORTJSON script
and auto-updates each Monday, c.1:00 PM BST
Shackleton's Endurance Expedition - I'm fascinated by Sir Ernest Shackleton. Arguably one of the most inspirational leaders in a time of crisis, but also likely, through bad planning and questionable decision making, to be the instigator of said crisis! Here I've mapped out the timeline of Endurance's ill-fated voyage into the pack ice of the Weddell Sea and the details of each of the 28 crew members
Information Management: Apps & Services
A power user of both macOS (17 years) and iOS, I automate tasks using a variety of apps and services.
Highlighting my preference for portability, information relating to how I integrate the services I use with each other, is presented in differing formats below.
myMindNode - An interactive, online mind map made with MindNode on macOS and iOS
Web page - Mind map above exported as Markdown and converted inline with this script
PDF presentation - Mind map exported as Markdown and converted to slides using Deckset
Dropbox Paper - Mind map exported as Markdown and rendered as an online outline
Workflowy - Mind map exported as OPML and published as an outline online
I'm a long-time user of
for task management. In recognition of me helping folks get the most out of it, since June 2020 I've been one of a select band of Ambassadors
for this service which has over 25 million users globally. Below you'll find some tools and resources I've produced, specifically designed to enhance the Todoist experience. All are fully-documented.
My own guide to the natural language syntax for Due Dates & Times in Todoist - Todoist's own website is far from exhaustive in detailing the natural language entries that can be used for setting due dates and times. I've documented the extra inputs I've learnt over the years, including recurring/repeating task structures
Todoist Filter guide - In a similar vein as above, here I share some of my knowledge on how to create really handy Filter queries in Todoist
Alfred sub task workflow - An overview on an Alfred for Mac workflow I created for generating sub tasks in Todoist
Todoist Template Generator - This Google Sheet streamlines the production of project templates, also ensuring timelines can be changed dynamically before importing into Todoist. Here's a
blog post in French about it written by a fellow Todoist Ambassador. You can make your own copy of it here. Now includes task descriptions!
Sequential Tasks - A quick & easy means of creating a list of sequential tasks in Sheets ahead of copying into Todoist. You can make your own copy of it here
Recurring tasks 'n' days from end of each month - You can use this Google Sheet to quickly and reliably create the syntax for recurring tasks that repeat on a cycle of a specified number of days before the end of each month. You can make your own copy of it here
Relative Due Dates Tool - A further development of the previous tool, this one overcomes some of the shortcomings of the recurring tasks natural language syntax by dynamically creating multiple due dates formatted for easy pasting-in to Todoist.
Here's short video of the workflow in action. You can make your own copy of it here
If Todoist was a country - As a bit of fun, I built this dashboard to show how Todoist's user base (based on best info) would sit in the rankings of countries by population. Extrapolates growth rate of r/todoist too.