XLSX To Python Why Parametric Variations Are Required

Why Parametric Variations Are Required (+4)

Why Parametric Variations Are Required

A single test point (the original Excel values) proves the implementation matches at one configuration. Engineering calculations must be valid across their input range. Parametric variations catch:

• Off-by-one errors in unit conversions
• Boundary condition failures (zero, negative, extreme values)
• Formula implementation bugs that happen to pass at one point
• Missing edge case handling

The 10-Variation Rule

For every extracted calculation, generate 10 parametric test cases that vary inputs within reasonable engineering limits. The variations should cover:

1. Original values (baseline — from Excel cached values)
2. All-minimum inputs at lower typical range
3. All-maximum inputs at upper typical range
4. One-at-a-time low — each input at its minimum while others stay nominal
5. One-at-a-time high — each input at its maximum while others stay nominal
6. Mid-range — all inputs at 50% of range
7. Stress combination — inputs at toughest combination (e.g., max load + min strength)
8. Near-zero — inputs near zero where division-by-zero or sign issues appear
9. Large values — inputs at 10x typical to catch overflow/precision issues
10. Random within range — random valid combination for regression testing

Implementation Pattern

import pytest

# Input ranges from extraction (typical_range from dark-intelligence archive)
INPUT_RANGES = {
    "diameter": {"min": 0.1, "max": 5.0, "nominal": 1.0, "unit": "m"},
    "wall_thickness": {"min": 0.005, "max": 0.1, "nominal": 0.025, "unit": "m"},
    "pressure": {"min": 0.0, "max": 50.0, "nominal": 10.0, "unit": "MPa"},
}

def make_variation(overrides: dict) -> dict:
    """Create a test case from nominal values with overrides."""
    case = {k: v["nominal"] for k, v in INPUT_RANGES.items()}
    case.update(overrides)
    return case

# Parametric test cases
VARIATIONS = [
    pytest.param(make_variation({}), id="nominal"),
    pytest.param(make_variation({k: v["min"] for k, v in INPUT_RANGES.items()}), id="all-min"),
    pytest.param(make_variation({k: v["max"] for k, v in INPUT_RANGES.items()}), id="all-max"),
    # One-at-a-time variations for each input...
]

@pytest.mark.parametrize("inputs", VARIATIONS)
def test_calculation_parametric(inputs):
    """Parametric variation — verify calculation across input range."""
    result = calculate(**inputs)
    # At minimum: check result is finite and within physical bounds
    assert result is not None
    assert not (isinstance(result, float) and (result != result))  # NaN check
    # Tighter assertions added once reference values are computed

How to Get Reference Values for Variations

Since the Excel file only contains ONE set of values, reference values for parameter variations come from:

1. formulas library — compile the workbook and evaluate at new inputs
2. Manual calculation — for simple formulas, compute expected values by hand
3. Cross-validation — if the same calculation exists in digitalmodel/assetutilities, run both and compare
4. Physical bounds only — when exact reference is unavailable, assert output is within physically meaningful bounds (e.g., stress > 0, efficiency 0-1)

# Using formulas library for parametric reference values
import formulas

xl_model = formulas.ExcelModel().loads("calculation.xlsx").finish()

for variation in VARIATIONS:
    # Set input cells to variation values
    inputs = {"'Inputs'!B2": variation["diameter"], ...}
    solution = xl_model.calculate(inputs=inputs)
    expected = solution["'Results'!C5"]
    # Use as reference value in parametric test

Generating Variations from Archive YAML

The dark-intelligence archive's inputs[].typical_range field provides the min/max for each parameter. Use this to auto-generate variations:

def generate_variations(archive: dict, n: int = 10) -> list[dict]:
    """Generate n parametric variations from archive input ranges."""
    inputs = archive.get("inputs", [])
    nominal = {inp["name"]: inp["test_value"] for inp in inputs}
    ranges = {
        inp["name"]: inp.get("typical_range", [inp["test_value"] * 0.5, inp["test_value"] * 2.0])
        for inp in inputs
        if inp.get("test_value") is not None
    }

    variations = [nominal]  # Case 0: original values

    # All-min, all-max
    variations.append({k: r[0] for k, r in ranges.items()})
    variations.append({k: r[1] for k, r in ranges.items()})

    # One-at-a-time for each input
    for key in ranges:
        low = {**nominal, key: ranges[key][0]}
        high = {**nominal, key: ranges[key][1]}
        variations.append(low)
        variations.append(high)

    # Trim or pad to n
    import random
    while len(variations) < n:
        rand_case = {k: random.uniform(r[0], r[1]) for k, r in ranges.items()}
        variations.append(rand_case)

    return variations[:n]