Replacing a Fragile Excel Model With a Query and a View

Every organization has a model like this: one workbook, built over years by someone who has left, that produces a number the business depends on. It has forty sheets, formulas that reference other workbooks, a macro nobody opens and three cells that must never be touched. It works until the day it does not, and then nobody can say why the total moved. Replacing it with a query is possible and worth doing, because a query keeps its logic in one place where it can be read, tested and versioned. This post gives the steps, from mapping the model to a parallel run. It is a spoke of doing proper analysis on Excel data with SQL.

What makes a model fragile

A model is fragile when its behavior depends on things nobody can see.

• Hidden logic. Formulas on hidden sheets, hard-coded numbers inside formulas (=B7*1.037), and cells colored to mean something.

• Unclear inputs. Data pasted in by hand each month, from places that are not recorded.

• Dependence on layout. VLOOKUP over a fixed range, formulas that break when a row is inserted, references to cells by position.

• External links. Formulas pointing at other workbooks on a personal drive.

• Macros that rearrange or paste values at specific moments.

• One owner. The only person who understands it, and who is on leave when the board asks a question.

The symptom is that nobody trusts a change. Edits are made with fear and checked by comparing against last month's output and hoping.

Step 1: map the model

Before writing SQL, draw what exists. Open the workbook and for each sheet note whether it holds inputs (data pasted or linked), assumptions (numbers that people set), calculations or outputs (what leaves the model). A simple table does:

Use Excel's tools to follow the dependencies: Formulas, Trace Precedents, Inquire (in editions that have it) and Name Manager. Search for hard-coded numbers in formulas, and list each with what it represents. Search for links to other files (Data, Edit Links). Read the macros, if any, and write down what they do. This mapping is valuable on its own, since it is the first documentation the model has had.

Identify the outputs that matter. Many sheets exist to support three numbers. Start from those and trace backward; ignore calculations that feed nothing anyone reads.

Step 2: move the inputs to a database

The data inputs should live in tables, not be pasted into a sheet. Create a table for each input and load history into it. If the source is an export, load it on a schedule (staging Excel data in a table). Give each table a key and types, and validate on load. Doing this removes the manual paste, the most error-prone step of the old process.

Assumptions that people change belong in a small table with a name, a value, a valid-from date and an owner, not in cells scattered through sheets:

CREATE TABLE assumptions (
    name       text NOT NULL,
    value      numeric NOT NULL,
    valid_from date NOT NULL,
    note       text,
    PRIMARY KEY (name, valid_from)
);

A dated row means a change of assumption does not rewrite history: last year's figures keep the rate that applied. This is a common hidden defect in workbook models, where changing a rate silently restates the past.

Step 3: translate the calculations

Translate the chain, one sheet at a time, into named steps of a SQL view. Each step is a common table expression with a name that says what it computes:

CREATE VIEW model.margin_by_month AS
WITH sales AS (
    SELECT date_trunc('month', order_date)::date AS month, product_id,
           SUM(quantity) AS units, SUM(quantity * unit_price) AS revenue
    FROM orders_clean JOIN order_lines USING (order_id)
    GROUP BY 1, 2
),
rates AS (
    SELECT s.month, s.product_id, s.units, s.revenue,
           (SELECT value FROM assumptions a
             WHERE a.name = 'unit_cost' AND a.valid_from <= s.month
             ORDER BY a.valid_from DESC LIMIT 1) AS unit_cost
    FROM sales s
),
margin AS (
    SELECT month, product_id, revenue,
           units * unit_cost AS cost,
           revenue - units * unit_cost AS gross_margin
    FROM rates
)
SELECT month, SUM(revenue) AS revenue, SUM(cost) AS cost, SUM(gross_margin) AS gross_margin,
       ROUND(100.0 * SUM(gross_margin) / NULLIF(SUM(revenue), 0), 1) AS margin_pct
FROM margin
GROUP BY month;

Each step corresponds to a block of the workbook: sales aggregated, the assumption looked up as of the month, the margin computed. The names (sales, rates, margin) document the logic. The assumption is picked by date. Functions translate in familiar ways: SUMIFS to GROUP BY, VLOOKUP to a join, IF to CASE, running totals to window functions (translating Excel formulas into SQL). Where the workbook has a hard-coded number inside a formula, give it a name in the assumptions table and ask the owner what it is. Several of these turn out to be obsolete.

Step 4: reproduce, then compare

Do not switch until the query reproduces the workbook. Run both on the same inputs for several periods, not one, and compare every output figure:

SELECT m.month,
       m.gross_margin AS sql_value,
       w.gross_margin AS workbook_value,
       m.gross_margin - w.gross_margin AS diff
FROM model.margin_by_month m
JOIN workbook_outputs w USING (month)
ORDER BY ABS(m.gross_margin - w.gross_margin) DESC;

Load the workbook's outputs into workbook_outputs from its saved values. Expect differences at first, and treat each as a finding, not a failure. The cause is one of: a translation mistake in the query; a manual override in the workbook; a bug in the workbook (a range that stops a row short, a formula that was not copied down); or a rounding order. Decide for each which is right, with the model's owner. Bugs in the old model discovered here are the best argument for the change: show the business that the old figure was wrong in March by 12,000, and a conversation about trust starts on the right foot.

Run the comparison for at least a full cycle of the business, such as twelve months of history plus the current month. Include months with unusual events. Stop when the differences are explained to a tolerance everyone has agreed.

Step 5: test the query

Now that the logic is text, test it. Write a handful of cases with known answers: a month with one product, a month with a rate change in the middle, a month with no sales, a refund. Run the view on this small data and assert the results. Keep the tests with the code. A test turns the next change from an act of faith into a check, and lets someone else modify the model without the original author.

Step 6: keep Excel as the front end

People like the workbook because it shows numbers and lets them play. Keep that. Build a thin workbook that connects to the view and presents the results, with charts and a few input cells for scenario assumptions (keeping Excel as the front end to a database). It contains no logic worth hiding: it reads a view and formats. If a user changes a scenario input, it goes through a parameter, not through editing a formula. The model's logic is now in one place, and the workbook can be rebuilt in an hour if it is lost.

Where users need to change official assumptions, give them a controlled path (a form or a script that writes a dated row to the assumptions table, with who and when) and not a free cell.

Step 7: cut over carefully

Announce the date. Run the old and new in parallel for a cycle. Keep the old workbook as an archive, read-only, with a note saying what replaced it and where. Record the validation: the comparison table and who signed it off. Leave a way back for the first month.

Expect pushback about loss of flexibility. A model in a workbook can be bent in the meeting: change a cell and watch the total move. The new arrangement should keep that ability for scenarios through parameters, while closing the ability to change the official logic by accident. If people need to explore, provide a copy-and-play workbook that cannot write back.

Document

Write one page: what the model computes, the inputs and where they come from, the assumptions and owners, the view and its steps, how to run it, how it was validated and how to change it. Keep it with the code in version control. The old model had no such page; the new one is only an improvement if it does.

When the model should stay in Excel

It is exploratory. A what-if model changed daily by its author belongs where the author works.

The users are one or two and the stakes are low. A replacement project costs more than the risk.

It is a presentation. If the workbook is a formatted report and not a calculation, a query does not add much.

The logic is genuinely interactive. Goal seek, scenario managers and data tables are Excel's strengths; replace only the data and the heavy calculation.

See PostgreSQL's pages on views and WITH queries. To convert a small result to JSON, use the CSV to JSON converter.