This is where BlackTor Group pulls Excel, XML, VBA, SQL and Python together into a single reporting or analytics solution, and documents how it works so someone else can pick it up.
BlackTor Group LtdDartmoor, UK
A note on what follows
No client names, no client data.
BlackTor's work is covered by client NDAs, so the examples below don't describe a real client, project or dataset. To keep things concrete, they're all set at "Ride Me Cycles": a fictitious multi-branch bike retailer invented for this site. The techniques are real; Ride Me Cycles and everything about it are not.
Status
Status: less a discipline of its own than the pattern behind the other five: most real projects already combine two or three of them (the Access billing reports on the SQL page run on Excel and SQL together, for instance). This page documents that pattern, and how the work gets written up.
A few examples
Three solutions that needed more than one discipline.
Illustrative, per the note above: not real client work.
ExcelSQLVBADocumentation
The problem: Ride Me Cycles tracked performance across six branch spreadsheets, three of them months out of date, and nobody could say which P&L figure was current.
The approach: Pulled the live figures into one workbook via direct SQL queries against the sales database, automated the monthly refresh with a VBA macro, and wrote a one-page data dictionary explaining where every figure comes from.
Sub RefreshAndDistribute()
The result: One P&L workbook, refreshed on a schedule, with a page explaining exactly how it works.
XMLPythonSQL
The problem: Ride Me Cycles' till system and its online store needed to end up in the same stock table, but one produced XML and the other CSV, on different schedules.
The approach: A Python script parses both formats into a common shape, validates them against the same product-code rules, and loads them into the database on a shared schedule via SQL.
def load_feed(path: Path, kind: str) -> None:
The result: Two incompatible exports, one stock table, no manual reconciliation.
ExcelVBADocumentation
The problem: The workbook that ran Ride Me Cycles' entire staff rota and payroll summary had outgrown the one person who understood it, and there was nothing written down.
The approach: Documented every macro, named range and external link into a short handover guide, alongside a change log, so the workbook could survive a change of hands.
1. What each tab does
2. Where the numbers come from
3. What breaks if you rename a sheet
The result: The rota workbook now survives a change of hands, because the knowledge isn't only in one person's head.
Notes in full
Every note above, in full.
The sidebar carries the short version; this is the longer one, for whoever wants the detail behind it.
SEP 2026
Document the source of every figure
A source note does not need to be long. A single line stating which system the number came from, the date it was extracted, and any filter or scope applied, a particular period, a particular category, a particular exclusion, is usually enough to make a figure traceable. The point is not to write documentation for its own sake, it is to capture the small amount of context that exists clearly in the builder's head at the time but nowhere else, and that is the first thing to be lost once time passes.
The failure mode this avoids is a familiar one. A figure appears in a report, someone questions it months later, and the only way to answer is to track down whoever built it and hope they remember. If that person has moved on, changed role, or simply forgotten the detail, the figure becomes unverifiable, and an unverifiable figure has to be treated as unreliable even if it was originally correct. Reproducing it from scratch to check it wastes time that a one-line note would have saved entirely.
The note is most useful kept with the figure itself rather than in a separate document that can drift out of sync or go unread. A comment on the cell, a line in the query, a short annotation in the report footer: any of these work, as long as whoever encounters the figure later encounters the source alongside it, not somewhere else that has to be found.
Reconciliation only works when there are two independently produced views of the same underlying number to compare, so it has to be built into the process rather than treated as an afterthought. In practice this means keeping a second route to a key total, a different query, a different extract, a manual spot check, and comparing it against the main figure before that figure goes anywhere. A one-off difference of a few percent, a date range that does not quite match, or a filter applied on one side and not the other, is easy to spot and cheap to fix at this stage.
The same mismatch found after publication is a different problem entirely. By then the figure has already been read, quoted, or acted on, and correcting it means not just fixing the number but explaining why it changed, which damages confidence in every other figure in the same report regardless of whether those figures were actually wrong. The cost of an error is not fixed, it grows with how far the figure has already travelled.
This is worth automating where the report is recurring: a scripted comparison between the two sources that flags anything outside an expected tolerance is far more reliable than remembering to check manually each cycle, particularly once the report has been running smoothly for a while and the temptation is to assume it will continue to.
The principle is straightforward: the data as it arrived should never be edited in place. Any cleaning, filtering, or transformation happens in a separate copy, built from the raw layer rather than overwriting it, whether that separation is a distinct tab in a workbook, a separate table in a database, or a raw folder versus a processed folder on disk. The raw layer stays untouched and read-only in practice even where it is not enforced technically.
The reason this matters becomes clear the first time a transformation step turns out to be wrong. If the raw data still exists, the fix is to correct the transformation and rerun it, and the output is right again. If the raw data was edited directly, the mistake is now baked into the only copy that exists, there is nothing to rerun against, and there is no way to be certain what was lost. Errors that would otherwise be a five-minute fix become a forensic exercise, or simply go uncorrected because nobody realises they are there.
This applies at small scale as much as large: a single workbook with a raw import tab and a working tab derived from it follows the same principle as a formal data pipeline with separate raw and processed layers. The scale changes, the discipline does not.
Different systems are frequently built by different teams at different times, and they tend to develop their own vocabulary for the same underlying concept: one calls it a branch, another calls it a site, a third calls it a location, and all three mean the same thing. Joining data across those systems requires a mapping between the two naming conventions, and the question is only whether that mapping is built once, deliberately, or rebuilt informally every time a report needs it.
Done once, this is a small reference table: one name per system, linked to a single canonical identifier, maintained in one place and updated when something changes at either end. Done informally, it becomes a manual lookup step repeated in every report that joins the two systems, usually built from memory or from whatever mapping was used last time, which means it silently breaks the moment something is renamed, added, or restructured on either side and nobody remembers to update every copy.
Building the mapping does not require renaming anything in the source systems themselves, which is often not possible anyway. It is a translation layer sitting between the two, maintained once and referenced wherever the join is needed, rather than reconstructed by hand each time.
Version control is well established for code, but a model or report built in a spreadsheet or a query tool benefits from the same discipline even where the tooling makes it less automatic. Keeping a dated copy each time a significant change is made, with a short note on what changed and why, whether that is a formal versioning tool or simply a consistently named archive of prior copies, means the current version is never the only record of how the model has evolved.
The value of this shows up when an assumption changes and someone wants to understand the effect. Without prior versions kept, the only way to answer that is from memory: what did this used to look like, and is the change in the output actually down to the assumption, or something else that changed at the same time. Memory is an unreliable basis for that kind of comparison, particularly months after the fact, and it gets worse as more than one person has touched the model in between.
With prior versions available, the comparison is direct: open the earlier copy, change only the assumption in question, and see what moves. That isolates the effect of the one change from everything else that has happened to the model since, which memory alone cannot do reliably.
A calculation used in more than one place, a margin, a weighted average, a status classification, tends to get implemented separately wherever it is needed: once in a spreadsheet formula, once in a database query, once in a script. Each implementation is written by someone with a slightly different context, at a slightly different time, and each is updated independently whenever a rule changes or an edge case is discovered.
The drift this produces is rarely dramatic at first. One version handles a particular edge case, a zero denominator, a null value, a boundary date, and the others do not, so the three versions agree in most cases and disagree quietly in the cases that matter. Nobody notices until two reports built from the same underlying data show different totals for the same thing, at which point resolving the discrepancy means auditing three separate implementations to work out which one, if any, is currently correct.
A single definition, referenced wherever the calculation is needed rather than reimplemented, removes the drift by removing the duplication. This can be a shared function, a central query others build on, or a named formula referenced across a workbook rather than retyped in each sheet. The specific mechanism matters less than the principle: the calculation exists in exactly one place, and everything else points to it.
A detailed model can be mechanically flawless, every formula correct, every join valid, and still produce the wrong answer if one of its input assumptions is wrong. Reviewing the model's internal logic will not catch this kind of error, because the logic is not where the fault lies; the model is faithfully doing the wrong calculation. What catches it is comparing the output against something built independently of the model, even something crude.
A rough estimate, worked out on the back of an envelope from a different angle entirely, does not need to be precise to be useful. If the detailed model says a figure is around ten times larger or smaller than the rough estimate suggests, that gap is worth investigating before the detailed figure is trusted, regardless of how confident the model's internal mechanics appear. If the two are in the same range, that is not proof the detailed figure is correct, but it is meaningful reassurance that no gross assumption error has occurred.
The value of the rough estimate comes specifically from its independence. An estimate derived from the same assumptions as the detailed model, or checked by re-reading the same model's formulas, will tend to agree with the model even when the model is wrong, because both share the same blind spot. A genuinely separate approach does not share that blind spot, which is exactly what makes it a useful check rather than a formality.
In most reporting cycles, one step is done identically or near-identically every time: the same extract, the same reformatting, the same copy-paste into a template. It is rarely the most technically interesting part of the process, which is exactly why it tends to be left for later in favour of something more engaging. But it is also usually where manual error creeps in fastest, precisely because it is repetitive and monotonous, which makes attention lapse in a way it does not on a task that demands sustained focus.
The case for automating it first, ahead of a more interesting problem elsewhere in the process, comes down to where the risk and the recurring cost actually sit. A step done every cycle carries its error rate every cycle, so a small mistake rate compounds over a year in a way a one-off task never does. Automating it removes both the time cost and the variability in one move, and the effect compounds in the other direction: each cycle it is not manually redone is a cycle where that particular error simply cannot occur.
This does not need to mean full automation from the outset. Even a partial script that removes the most error-prone manual step, the copy-paste, the reformatting, the manual filter, while leaving judgement-based steps in place, delivers most of the benefit and is usually far quicker to build than automating the entire cycle in one attempt.
A recurring report accumulates knowledge in the head of whoever built it and whoever has maintained it since: where the underlying data comes from, which refresh steps have to happen in which order, and which figures tend to look wrong first when something upstream has broken. None of that is visible in the report itself, and none of it is written down anywhere unless someone deliberately writes it down.
A short handover note captures exactly that: the data source, the steps to refresh it, and a short list of what to check first if a number looks off, the usual suspects rather than an exhaustive fault tree. Kept to a page, it takes relatively little effort to write and to keep current, and it means the report does not depend entirely on one person being available and remembering correctly whenever something goes wrong or someone new has to pick it up.
The alternative shows up at the worst possible time: the person who normally maintains the report is on leave, has left the organisation, or is simply unavailable, the report breaks or looks wrong, and whoever is left has no starting point beyond opening the file and guessing. A note that would have taken twenty minutes to write turns into hours of investigation carried out under time pressure by someone unfamiliar with the report.
A model or report that works correctly today but can only be understood and maintained by the one person who built it is not really finished, whatever state its logic is in. That dependency is a liability that sits quietly in the background for as long as that person remains available, and becomes a visible problem the moment they are not, whether through leave, a change of role, or simply moving on to other work.
Treating documentation as part of finishing the work, rather than something to add later if time allows, changes when it gets written and therefore whether it gets written at all. Documentation added at the point of building a model is written by the person with full context, while the reasoning behind each decision is still fresh; documentation deferred to later is written, if it is written at all, by someone reconstructing that reasoning after the fact, or more often not written at all because the moment has passed and something else has become the priority.
This does not require exhaustive documentation of every formula and decision. A proportional amount, the assumptions that are not obvious from the model itself, the reasoning behind the parts that would otherwise look arbitrary to someone else, and how to check the output is behaving as expected, is usually enough to take the model from depending on one person's memory to being maintainable by whoever picks it up next.
