←BACK

The Toolkit: Regex Pipelines, Excel Injection, and the Arsenal

THE METHOD2#productivity#python
TL;DR

Regex pipelines that normalise messy dates, Excel formula injection that makes reports auto-populate, and the complete automation arsenal. The technical playbook behind finishing 10-day sprints on day 1.

This is Part 2 of the THE METHOD series. If you haven't read Part 1 on the automation philosophy and the two-device meeting, start there.

The Regex Date Cleaning Incident

Here's a specific one. We'd get datasets — messy, real-world data — and the dates were an absolute disaster.

I'm talking about Excel files with inconsistent formats like:

2023-04-15
15/04/2023
04/15/2023
Apr 15 2023
15-Apr-2023
April 15, 2023
20230415
15.04.2023

Sometimes all in the same column. Because of course they were.

We wrote a Python script with a list of compiled regex patterns, ranked by confidence. The function tried each pattern, extracted the components, and normalised everything to ISO 8601. If a pattern matched ambiguously (looking at 04/05/2023 — April 5th or May 4th?), it flagged the row for manual review instead of guessing.

import re
from datetime import datetime

DATE_PATTERNS = [
    (r'^(\d{4})-(\d{2})-(\d{2})$', '%Y-%m-%d'),           # 2023-04-15
    (r'^(\d{2})/(\d{2})/(\d{4})$', '%d/%m/%Y'),            # 15/04/2023
    (r'^(\d{2})/(\d{2})/(\d{2})$', '%d/%m/%y'),            # 15/04/23
    (r'^(\w{3})\s+(\d{1,2})\s+(\d{4})$', '%b %d %Y'),     # Apr 15 2023
    (r'^(\d{1,2})-(\w{3})-(\d{4})$', '%d-%b-%Y'),          # 15-Apr-2023
    (r'^(\w{3,9})\s+(\d{1,2}),?\s*(\d{4})$', '%B %d %Y'), # April 15, 2023
    (r'^(\d{8})$', '%Y%m%d'),                               # 20230415
    (r'^(\d{2})\.(\d{2})\.(\d{4})$', '%d.%m.%Y'),          # 15.04.2023
]

def normalize_date(value):
    """Try every regex pattern and return the first clean match."""
    for pattern, fmt in DATE_PATTERNS:
        match = re.match(pattern, value.strip())
        if match:
            try:
                return datetime.strptime(value.strip(), fmt).isoformat()
            except ValueError:
                continue
    return None  # Manual review

We had a version of this for every data cleaning task. Phone numbers, currency strings, inconsistent address formats, messy categorical variables spelled five different ways. Each one got its own script with its own set of patterns. We'd run them in a pipeline, flag the unparsable rows, and finish in minutes what used to take a full day of copy-pasting in Excel.

The Excel Formula Injection Trick

This one I'm actually proud of.

A lot of the people we were delivering reports to? They lived in Excel. They wanted to open a file and see everything: the numbers, the graphs, the conditional formatting. They didn't want to run a script. They didn't want to connect to a database. They wanted a .xlsx file they could double-click and immediately start reading graphs.

So we gave them that. But here's the thing — we cleaned the data in Python, ran the manipulation in Python, and then... we used Python to write Excel formulas into specific cells and inject chart definitions so everything auto-populated on open.

The workflow:

  1. Clean the raw data in Python (regex pipeline).
  2. Transform it into the exact shape our "cheat sheet" required — specific column names in specific positions.
  3. Write it to an Excel file using openpyxl, but also inject formulas into summary cells.
  4. Add Excel-native functions into specific cells — things like =SUMIFS(), =XLOOKUP(), =UNIQUE() — so the Excel power users could trace the numbers themselves.
  5. Embed chart definitions with exact data ranges so the graphs auto-populated when the formulas recalculated on file open.
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active

# Write cleaned data
for row in cleaned_data:
    ws.append(row)

# Inject formulas in summary cells
ws['H1'] = 'Total (Formula)'
for i in range(2, len(cleaned_data) + 2):
    ws[f'H{i}'] = f'=SUM(B{i}:G{i})'

# Add an XLOOKUP lookup table so Excel natives can follow the logic
ws['J1'] = 'Category'
ws['K1'] = 'Lookup Value'
for idx, cat in enumerate(categories, start=2):
    ws[f'J{idx}'] = cat
    ws[f'K{idx}'] = f'=XLOOKUP(J{idx}, A:A, H:H)'

# Create chart
chart = BarChart()
chart.title = "Auto-Generated Report"
data = Reference(ws, min_col=2, max_col=7, min_row=1, max_row=len(cleaned_data))
cats = Reference(ws, min_col=1, min_row=2, max_row=len(cleaned_data))
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "M1")

wb.save("report.xlsx")

The Excel fans opened the file, saw the numbers, saw the graphs, saw the formulas. They nodded approvingly. They understood how the numbers connected because they could click on a cell and trace the formula. We got to automate the grunt work and make it transparent. Best of both worlds.

We were cracked.

The Collection

Eventually we built up a whole arsenal. Python scripts for specific data cleaning tasks. R scripts for statistical analysis with pre-written ggplot2 templates. Excel templates with premade pivot tables. PowerShell scripts for file organisation.

Every script had a name. Every script had a README (eventually, after the third time we forgot what something did). Every script was designed to be run with one command and produce a consistent output.

We stopped writing code for specific tasks and started writing code that generated the deliverable. The difference is subtle but massive: instead of "clean this CSV" you write "clean all CSVs that look like this." The first approach solves today's problem. The second approach solves today's and next week's.

No wonder Brian and I keep getting asked to automate things for people.

The Real Payoff

It's not the time. I mean, it is — saving hours or days per week adds up. But the real payoff is something else.

When you automate something, you understand it. You've decomposed the task into its actual components. You've figured out which parts are patterns and which parts are exceptions. You've built a model of the work in your head, and then you've encoded that model into code.

The people who say "it's faster to just do it manually" are missing the point. The automation isn't just about saving time on this one task. It's about building a reusable understanding. It's about learning to see the patterns. It's about being the person who looks at a messy process and thinks "there's a better way to do this."

That skill — the ability to see the pattern, abstract it, and encode it — that's what actually scales. Not the scripts themselves. The method.

And yeah, also the time. Having time to run errands during work hours because you've already delivered is pretty great too.

It's not being lazy. It's called productivity maxxing.

If you've got something repetitive eating your week and you can't figure out how to automate it — hit me up. We're always free. For the right price.