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 using
Google Apps Script
more and more.
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 100+ Google Sheets - A Google Drive folder listing practical and creative uses of my function/formula knowledge. 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! Giving it a miss for 2021
Below are examples of automated dashboards and analytical tools I've published online from Google Sheets.
COVID-19 UK Vaccination progress dashboard - Imports GOV.UK data at c.5:00 PM UTC 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 UTC, daily
Citymapper Mobility Index for London - Dashboard uses data from an imported Citymapper CSV file and visualises the impact the pandemic has had upon travel in London. Key lockdown milestones indicated. Updates c.8:00 AM UTC, daily
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
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 - This live chart is built from a CSV imported directly 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
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 and movements in the World Rugby rankings for every week since 2014. Pulls data via the API and auto-updates each Monday, c.1:00 PM UTC
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
Analysis of Wales' 2021 Six Nations Squad - As I'm not doing a full-blown Glide app for this year's championship, this a top-level dashboard I created analysing the composition of the squad announced on 20 Jan 2021
Information Management: Apps & Services
A power user of both macOS (16 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 folk get the most out of it, I'm now one of a select band of
for it. Here you'll find some tools and resources I've produced, specifically designed to enhance the Todoist experience. All are fully-documented to aid ease of use.
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 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
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. You can make your own copy of it here