From the Spreadsheet to the Script

Somewhere in every mature asset there is a spreadsheet that everyone trusts and nobody owns. It holds the decline curves, a set of b exponents that somebody tuned two budget cycles ago, a hard-coded economic limit, and a chart that goes straight into the quarterly deck. It is almost certainly the most consequential piece of software on the asset, and it has never been tested. This note is about moving that workbook into code — not because code is fashionable, but because a forecast that cannot be re-run is not a forecast. It is an opinion with a chart attached.

The argument is deliberately narrow. Decline-curve analysis is the right test case: the method is old, published and unambiguous,^[1][2] the dataset is small enough to hold in your head, and the output feeds directly into reserves and budget. If the discipline of reproducibility cannot be made to pay here, it will not pay anywhere.

The Case for the Spreadsheet, Stated Honestly

It is worth resisting the reflex that says spreadsheets are unprofessional. They are not. A well-built Excel model has one property that most engineering code lacks entirely: every intermediate value is visible. A reserves auditor can click a cell and see the formula that produced it. There is no build step, no environment to install, no dependency that broke last Tuesday. For a one-off material-balance check or a single-well decline, that transparency is a genuine engineering virtue, and it is why this practice still publishes Excel tools alongside code.

The failure is not in the tool. It is in what happens when a spreadsheet built for one analysis is asked to carry a recurring one. The spreadsheet-error literature reports cell error rates of a few percent in developed workbooks — with the consequence that for a large model the practical question is not whether it contains an error but how many — and finds that inspection typically catches only about half of the errors present.^[3] Reservoir work adds a difficulty of its own on top of that. A decline curve that is 15% optimistic does not throw an exception. It produces a smooth, attractive line.

A wrong decline forecast never crashes. It just draws.

Three failure modes account for most of what goes wrong when a DCA workbook becomes an institution rather than an analysis.

None of these is solved by being more careful. They are structural consequences of a medium in which logic and data occupy the same cells, and in which there is no mechanism to assert that something must be true.

The Judgements the Method Requires

Arps derived the three familiar decline forms — exponential, hyperbolic and harmonic — from the behaviour of the loss ratio, the reciprocal of the fractional decline in rate.^[1] Choosing between them is a separate craft: reading the b factor as reservoir physics, selecting the fit window, applying the terminal-decline switch. That craft is set out in the practical guide to Arps decline-curve analysis, and is taken as settled here. This note asks the question that guide does not: what has to be true of the code that runs it.

Only the general relation is needed, to fix the notation used later.

q(t) = qi · (1 + b · Di · t)^(-1/b)        b = 0 → exponential   ·   b = 1 → harmonic

Three parameters, one equation. The apparent simplicity is exactly what makes DCA dangerous in practice, because almost none of the difficulty lives in the arithmetic. It lives in the decisions taken before the fit runs — and every one of those decisions is an engineering judgement that a spreadsheet is structurally poor at recording. Four of them decide the answer.

• Segment selection. Arps decline applies to boundary-dominated flow. Fitting through a transient period, or across a compression installation, or through a workover, produces a mathematically excellent fit to a physically meaningless curve.^[2][4]
• Rate basis. Calendar-day versus operating-day rate is not a cosmetic difference. A well on a 60% uptime cycle has two entirely different decline curves depending on which basis is used, and only one of them forecasts what the facility will actually see.
• Data provenance. Allocated monthly volumes and well-test rates are different measurements of different things. Mixing them inside one regression is common and rarely declared.
• The b exponent. It is not a fitting nuisance parameter. It carries drive-mechanism information, and its permissible range is a physical claim, not a numerical convenience.^[4][5]

Each of those is a decision an engineer must own. The whole case for moving to code is that code forces every one of them to become a named, visible, versioned argument — while a spreadsheet allows all four to be made accidentally.

A Working Definition of Reproducible

"Reproducible" is used loosely enough to mean nothing. For this purpose it means five specific guarantees, and a port that does not deliver all five has not actually bought anything. The five-point formulation below is this practice's own; the underlying principles — organised data, documented steps, a project structured so that the analysis can be re-run by someone else — are the standard ones from the reproducible-computing literature.^[6]

• Deterministic. The same input produces the same output, on a different machine, next year. Any stochastic step — bootstrap intervals, Monte Carlo economic limits — carries an explicit seed.
• Input-addressed. The run identifies its data by content hash, not by filename. production_final_v3_REVISED.xlsx is not an identifier.
• Units declared. Rates, times and cumulative volumes carry their units in the type or the column contract, and a mismatch fails loudly at the boundary rather than quietly in the middle.
• Judgements as parameters. Segment window, exclusions, b bounds, terminal decline and economic limit are function arguments, recorded with the result. If it changes the answer, it goes in the record.
• Performance stated with uncertainty. An error metric plus a blind test, never a single R². A curve fitted through history and scored on that same history reports nothing except that the optimiser converged.

Structure Before Syntax

The port is an architectural exercise, not a translation exercise. The instinct to reproduce the spreadsheet's layout in code should be resisted; what matters is separating stages that the workbook had fused together. Six stages, each a function that takes data and parameters and returns data — no hidden state, no reaching into a global.

ingest(source)            -> raw production records + content hash
                             validate schema, declare units, keep provenance

clean(raw, rules)         -> tidy series
                             deduplicate, reconcile basis (calendar vs operating),
                             flag zero and null periods rather than deleting them

segment(series, window)   -> analysis interval + exclusion mask
                             boundary-dominated period only; exclusions explicit

fit(interval, bounds)     -> (qi, Di, b) + covariance + convergence status
                             bounded optimisation; refuses rather than guesses

forecast(params, limits)  -> rate profile + EUR
                             terminal-decline switch, economic limit, cut-off date

report(fit, forecast)     -> metrics, blind-test error, parameter record

The value of that separation is not tidiness. It is that each stage can be tested in isolation against a known answer, which is impossible when ingestion, cleaning, fitting and reporting all live in the same sheet. The second value is that a stage becomes replaceable: swapping Arps for a reciprocal-rate or type-curve method touches fit and nothing else.

One further discipline is worth stating explicitly, because it is the one most often skipped: flag bad data, do not delete it. A deleted zero-rate month is indistinguishable from a month that never existed. A flagged one is a decision the reviewer can see and disagree with.

The Fit, and the Constraint You Must Declare

An unconstrained least-squares fit to the hyperbolic form will, on real data, quite often return b > 1. That is not a numerical curiosity — but the consequence has to be stated precisely, because it is routinely overstated. For b ≥ 1 the cumulative-production integral does not converge as time goes to infinity, so the model has no finite ultimate recovery of its own. It does not follow that the forecast is infinite in practice: impose an economic limit rate and the EUR is finite at any b. What actually goes wrong is subtler and more damaging. The late-time tail is extrapolated far past the period the data supports, the forecast reaches the economic limit only after an implausible number of years, and the EUR is inflated by precisely the region for which there is no evidence.^[4][5][7]

Conventional boundary-dominated flow is generally treated as bounded by 0 ≤ b ≤ 1. Where a higher apparent b is genuinely supported by the data — as is common in low-permeability systems still in extended transient flow — the accepted remedy is not to accept the unbounded forecast but to switch the model to exponential decline once the instantaneous decline rate falls to a stated terminal value, which restores a finite EUR.^[5][7]

fit_arps(t, q, b_bounds=(0.0, 1.0), method="rate_time"):

    guard: len(t) >= MIN_POINTS            else raise InsufficientData
    guard: t strictly increasing           else raise UnsortedSeries
    guard: q > 0 over interval             else raise NonPositiveRate

    params, covariance = bounded_least_squares(
        model   = arps_hyperbolic,
        bounds  = { qi: (0, inf), Di: (0, inf), b: b_bounds },
        initial = coarse_grid_search(t, q),      # not a magic number
    )

    if not converged:  raise FitDidNotConverge   # never return a silent default
    if b at bound:     warn("b pinned at bound — declare or revisit segment")

    return params, covariance, diagnostics


forecast(params, D_terminal, economic_limit):
    # hyperbolic until instantaneous decline reaches D_terminal,
    # exponential thereafter — bounds the EUR
    t_switch = time_where(D_instantaneous(params, t) == D_terminal)
    ...

Two behaviours in that sketch matter more than the mathematics. The function raises rather than returns a default when its preconditions fail, and it warns when a parameter is pinned at its bound — the signal that the constraint, not the data, is determining the answer. In a spreadsheet both conditions are invisible: a failed fit and a good fit render identically.

The Validation Harness

This is the section that gets cut when a project runs late, and it is the only section that distinguishes a port from a rewrite of the same untested logic in a new language. Three tiers, in increasing order of honesty.

test_recovers_known_parameters():
    for b_true in [0.0, 0.25, 0.5, 0.75, 1.0]:
        t, q = synthetic_arps(qi=1500, Di=0.18, b=b_true, noise=0.03, seed=42)
        fitted = fit_arps(t, q)
        assert close(fitted.b,  b_true,  tol=0.05)
        assert close(fitted.Di, 0.18,    tol=0.02)

test_blind_hindcast():
    train, held_out = split_by_date(series, cutoff=last_date - 12_months)
    forecast = fit_and_forecast(train, horizon=12)
    report(NRMSE(forecast, held_out))      # reported, not asserted away

On reporting, the standard is worth being blunt about. A single R² computed on the fitting interval is not evidence of forecasting skill; it is evidence that a three-parameter curve can be drawn through a smooth series, which was never in doubt. The pair that carries information is an error metric normalised to the rate scale, plus the blind-test error — and where the reserves consequence matters, the parameter uncertainty behind them. That specific pairing is a house standard, not a quotation from any source below. What the resource-classification framework does require is that an estimate rest on a documented, auditable technical basis and that its uncertainty be represented explicitly rather than collapsed into a single number.^[9]

When Not To Port

The honest counterweight: this migration is not free, and it is not always right. Three cases where the spreadsheet should stay.

• The analysis genuinely runs once. A single-well screening decline for a meeting next week does not repay a test suite. Reproducibility pays through repetition; without repetition it is pure cost.
• The reviewer cannot read code. If the person who must challenge the number can only audit it in a cell, moving it into Python does not increase scrutiny — it removes it, and replaces an inspectable model with a trusted black box. That is a net loss even if the code is better.
• Nobody will maintain it. An unmaintained script is worse than an unmaintained workbook, because at least the workbook still opens. If there is no owner, do not create the dependency.

Where the port does pay is the recurring case: the same method, run across many wells, refreshed every month, feeding a number that somebody will eventually have to defend in a reserves review. That is precisely the DCA case, which is why it is the natural place to start.

What This Unlocks

The stages defined above have a property that is not obvious until the next step: because each is a pure function with declared inputs and outputs, each is already callable by something other than a human. The same fit function that a monthly batch job calls is the function a browser dashboard calls when an engineer drags a segment window, and it is the function an agentic layer calls when asked which wells changed decline behaviour this month.

That progression is the subject of the two notes that follow this one. It only works in that order. A dashboard built on untested logic renders wrong answers faster, and an agent given access to unvalidated functions produces confident, well-written, wrong recommendations at scale. The engineering has to be trustworthy before the interface is worth building, and trustworthy has a specific meaning: bounded, tested against known answers, and scored on data the model never saw.

Frequently Asked Questions

Is Python better than Excel for decline-curve analysis?

Not inherently. Excel is better when the analysis is done once, read by non-programmers, and small enough that every formula is visible. Python becomes better when the same analysis is repeated across many wells or refreshed every month, because only then does the cost of writing tests get repaid by the errors those tests catch.

What does reproducible actually mean for a decline forecast?

Five things: the same input produces the same output; the input is identified by content hash rather than filename; units are declared rather than assumed; every engineering judgement is a named parameter instead of a hidden cell; and performance is reported as an error metric plus a blind test rather than a single R².

Should the Arps b exponent be bounded during fitting?

Yes, and the bound must be declared. An unconstrained hyperbolic fit can return b above one, for which the cumulative integral does not converge as time goes to infinity. An economic limit still makes the EUR finite, so the real problem is not an infinite number but an implausibly long tail extrapolated beyond the data. Conventional boundary-dominated flow is usually bounded at b ≤ 1; where a higher apparent b is genuinely supported, switch the forecast to exponential at a stated terminal decline rate.

How should a decline model be validated?

In three tiers. Recover known parameters from synthetic data generated by the Arps equations themselves. Then hindcast — fit on history with the last twelve months withheld and report error against the withheld period. Then check cross-method agreement between rate–time, rate–cumulative and type-curve estimates, reporting disagreement as a range rather than averaging it away.

What is the single most common mistake in porting a DCA workbook?

Reproducing the spreadsheet's structure instead of separating its stages. If ingestion, cleaning, fitting and reporting remain fused in code, the port has changed the language without changing anything that mattered — and has removed the one advantage the workbook had, which was visibility.