STD-DDL-LOG-001
The Black Box Standard

A DDL workbook used in production must be able to defend its results — without relying on the operator’s memory, without separate narrative documentation, and without macros.

The Governed Audit Log reduces dependence on memory by capturing formula state, results, and timestamps at each production run. It does not eliminate operator involvement — the operator still declares the run date, describes the event, and enters expected values. The LOG automates the capture of formula provenance inside a governed run process.

Applies to any workbook that meets one or more of the following conditions:

• Used in a recurring production process
• Used for commission calculations, reconciliations, or regulatory reporting
• Shared externally or with downstream systems
• Classified as Operational per STD-EXCEL-002

• Analytical workbooks classified per STD-EXCEL-001
• One-time exploratory analysis
• Scratch or disposable workbooks

Every workbook governed by this standard must declare its classification in the README tab:

Workbook Type:     Operational / Analytical
STD-DDL-LOG-001:   Required / Recommended / Exempt
Trust Phase:       Comfort / Reliance / Evidentiary

• A dedicated tab named LOG
• A single structured Excel Table named tbl_Log
• Protected: all columns locked except the active entry row
• No merged cells anywhere in the LOG tab
• No volatile functions anywhere in the LOG tab

Columns must appear in the order defined above. Column order is a scanner-compatibility requirement. The Anti-Pattern Scanner reads Col D (Formula_Used) by position. Reordering columns breaks scanner output silently. Any columns added beyond H must be appended to the right and documented in the README.

SETUP:
  Config tab, cell B3 (or operator-designated cell)
  Named range: Run_Date
  Value: operator sets manually before each production run
  Format: YYYY-MM-DD text (e.g. 2026-03-28)

VALIDATION (add adjacent to Run_Date on Config tab):
  =IF(AND(LEN(Run_Date)=10,
          ISNUMBER(DATEVALUE(Run_Date))),
       "OK", "FORMAT ERROR — use YYYY-MM-DD")

USAGE IN LOG:
  Col A formula: =Config!Run_Date
  Result: all LOG rows for this run share the same
  intentional, frozen, operator-declared timestamp.

Before every production run: open Config tab → update Run_Date → confirm validation reads OK → proceed. This is a deliberate governance act. The operator is declaring: “This run happened on this date.” That declaration is itself audit evidence.

FORMULATEXT requires Excel 2013 or later. This is the DDL governed baseline. Pre-2013 environments are out of scope for this standard.

If a workbook must operate in a pre-2013 environment: document the formula manually in Formula_Used as plain text, prefix with “MANUAL: ”, note the environment limitation in Notes.

Six scenarios are explicitly defined. All other error conditions should be logged as-is with a note in the Notes column.

Result:          Log the error value as-is (#N/A, #REF!, etc.)
Pass_Fail:       FAIL (automatic)
Notes:           "ERROR — investigate before relying on this run"
Operator action: Do not distribute results until resolved

Formula_Used:    FORMULATEXT returns #N/A
Log entry:       Record "HARDCODE DETECTED" in Formula_Used
Pass_Fail:       FAIL — governance finding
Notes:           "STD-EXCEL-001 violation — hardcode in production cell"
Operator action: Replace hardcode before next production run

Target_Cell:     Log the anchor cell only (e.g. D7)
Formula_Used:    FORMULATEXT of the anchor cell
Result:          Value of the anchor cell
Notes:           "Spill range: D7# — [N] rows × [M] cols"
Rationale:       The anchor formula governs the spill.

No special handling required.
Run_Date + FORMULATEXT captures formula state per run.
Version drift is visible by comparing LOG rows across runs.
This is the standard working as designed.

Applies to: broken Power Query, missing linked CSV,
            failed external workbook link
Result:          Log the error value returned by the target cell
Pass_Fail:       FAIL
Notes:           "EXTERNAL SOURCE FAILURE — [source name].
                 Ref ticket: [ticket ID]"
Operator action: Do not distribute. Restore source and re-run.

Condition:       Config!Run_Date is blank, stale, or invalid format
Col A:           Returns error or stale date
Operator action: Stop. Do not proceed with the run.
                 Update Run_Date. Confirm validation reads OK.
                 Re-run from the beginning.
Rationale:       A run without a declared date cannot be
                 defended. The LOG row would be misleading.

The LOG tab must be designed for scanner compatibility from day one. The Anti-Pattern Scanner and future Excelligence tooling will read LOG tab output to detect formula drift and provenance gaps.

• Column order fixed per §3.2
• No merged cells in the LOG tab
• Structured table formatting (tbl_Log)
• FORMULATEXT output in Col D — consistent, always
• No volatile functions anywhere in the LOG tab
• Any columns added beyond Col H must be appended to the right and documented

This standard is experimental with guardrails. It ships as a comfort layer. Trust builds through sustained use. Phases describe the operator’s relationship with the tool — they are not compliance tiers.

Phase progression is at operator discretion. Declare the phase in the README tab.

Tab:          LOG
Table:        tbl_Log
Config:       Run_Date named range on Config tab
Validation:   IFERROR format check adjacent to Run_Date
Protection:   Sheet protection ON
              All columns locked
              Unlock only: active entry row (first empty row)
              Workbook protection: structure locked
Columns:      A–H per §3.2 in defined order
Scanner cols: Any additions must be appended after H
              Document in README: column name, purpose, added date

A canonical template workbook accompanies this standard. All new production workbooks should begin from the template. The template includes the LOG tab pre-configured, the Config tab with Run_Date named range and validation formula, and README tab with the operator declaration fields.