Reconciliation and Evidence

🔁 Reconciliation and Evidence

📌 Guide, not policy. The README › Run and INSTALLATION.md › ExportEvidence_Silent govern; where this page differs, the repository wins.

K-SACCR can compare a bank's own SA-CCR figures with its results — per netting set and per trade leg — and write the whole run, including both comparisons, to one CSV evidence file. Both arrived with #45. A comparison with a bank's figures is not independent validation of either side (#100).

---

🧭 Overview

Bank figures on	Compared with	Written to	Fingerprint (hidden name)	Status cell
BankNettingSets	Results	Reconciliation	SACCR_ReconInputs	Reconciliation C5
BankTrades	TradeCalc	TradeReconciliation	SACCR_TradeReconInputs	TradeReconciliation C5

Both comparisons run automatically at the end of a completed Run SA-CCR. There is no separate button. Every operation (run, validate, clear) first withdraws both reconciliations, so none outlives the results it compared. The template ships both bank sheets empty; with no bank figure the reconciliation sheet says No bank figures on …: nothing to reconcile.

---

🏦 BankNettingSets → Reconciliation

Settings (top of BankNettingSets)

Cell	Workbook name	Template	Meaning
D4	BankNS_Scale	1	Units of the reporting currency per bank amount: 1, 1000 (thousands) or 1000000 (millions). The multiplier is not scaled
D5	BankNS_AbsTol	1	Absolute tolerance, in reporting-currency units. Not used for the multiplier
D6	BankNS_RelTol	0.0001 (0.01%)	Relative tolerance, as a share of the absolute bank figure; not applied when it is 0. Amounts match within either tolerance; the multiplier only within this one, or when equal

A scale that is not a number or not above zero, or a negative or non-numeric tolerance, stops the reconciliation (Reconciliation not run …: the amount scale (BankNS_Scale) must be greater than zero.); the SA-CCR results stand.

Table (header C8 = BankNettingSets_Header, data from row 9)

NettingSetID · Regime (BCBS / CRR) · EAD · RC · PFE · AddOn aggregate · AddOn IR · AddOn FX · AddOn CR · AddOn EQ · AddOn CO · AddOn OT · Multiplier · Comment

Enter one row per netting set with its regime, in the amount scale above. A blank figure is not compared; enter only what you have. The comment is not read.

Comparison rule

difference          = calculator − bank (bank amount × scale)
relative difference = difference / |bank|
MATCH               : |difference| ≤ absolute tolerance  OR  |relative difference| ≤ relative tolerance

Ratios (the multiplier here) are not scaled and match only exactly or within the relative tolerance. Rows are matched by ID, trimmed and in upper case, and never across regimes.

Reconciliation sheet

Header row 6 (Reconciliation_Header): NettingSetID · Bank row · Bank regime · Calculator regime · Calculator status · Measure · Bank figure (amounts in units) · Calculator figure · Difference (calculator − bank) · Relative difference · Result · Note. First the bank rows in their order — one row per figure compared, or one row saying why the set is not compared — then one row per Results netting set missing from the bank file. C4 holds the summary, for example:

Last reconciliation <time> | bank netting sets 3 | figures compared 2: MATCH 1, DIFFERENCE 1 | missing in calculator 1 | not in bank file … | duplicate rows 0 | regime mismatch 0 | withheld 0 | invalid input 0 | not applicable 0 | scale 1 | tolerance 1 or 0.01%

Results and notes

A netting set is checked in this order; the first reason that applies is the only row for it.

Result	When	Note written
DUPLICATE	The ID is listed more than once on BankNettingSets	Listed N times on BankNettingSets: none of its rows is compared.
INVALID INPUT	Regime not BCBS/CRR; ID missing; a figure that is not a number; a row with no figure	Regime must be BCBS or CRR: the row is not compared. · NettingSetID missing: the row is not compared. · Not a number: … · No bank figure entered on the row.
MISSING IN CALCULATOR	No netting set with this ID on Results	No netting set with this ID on Results.
REGIME MISMATCH	Bank regime differs from the Results regime	Bank regime …, Results regime …: figures of different regimes are not compared.
WITHHELD	The Results status is not VALID	No EAD on Results for this netting set: see its status and the Checks sheet.
NOT APPLICABLE	The calculator has no such figure for the row	The calculator has no … for this row.
MATCH	Within either tolerance	—
DIFFERENCE	Outside both tolerances	—
NOT IN BANK FILE	A Results netting set with no bank row (shown with its EAD)	No row for this netting set on BankNettingSets.

The TOTAL rows of Results are never compared.

Screenshots: BankNettingSets and Reconciliation

The bank figures below were entered for the screenshot: the bank's EAD for BCBS-EX1 copied from Results, 5,400 for BCBS-EX3, and an ID the calculator does not have.

---

🧩 BankTrades → TradeReconciliation

Settings D4:D6 as above, under BankTR_Scale, BankTR_AbsTol, BankTR_RelTol (templates 1, 1, 0.0001). Header C8 (BankTrades_Header), data from row 9:

Measure (bank column)	Compared with TradeCalc	Scaled?	Tolerance
Notional (rep ccy)	Notional (rep ccy)	Yes	Absolute or relative
MtM (rep ccy)	MtM (rep ccy)	Yes	Absolute or relative
M (years)	M (for a settled-to-market trade, from 1.0.2, the time to its next reset date)	No	Exact or relative
Supervisory duration	Supervisory duration	No	Exact or relative
Adjusted notional d	Adjusted notional d	Yes	Absolute or relative
Supervisory delta	Supervisory delta	No	Exact or relative
MF unmargined	MF unmargined	No	Exact or relative
MF margined	MF margined (the trade's own in a CCP set whose CCP retains VM, from 1.0.2)	No	Exact or relative
Effective notional unmargined	Effective notional unmargined	Yes	Absolute or relative
Effective notional margined	Effective notional margined	Yes	Absolute or relative
Stand-alone add-on	Stand-alone add-on	Yes	Absolute or relative

Rows are matched by Trade ID (one row per leg, as on Trades). A trade carries the regime of its netting set, so there is no regime check. The TradeReconciliation sheet (header row 6) has the columns TradeID · Bank row · NettingSetID · Asset class · Calculator status · Measure · Bank figure (amounts in units) · Calculator figure · Difference · Relative difference · Result · Note · Economic trade ID — the last copied from Trades column Z (blank for a whole trade), so legs can be grouped as the bank groups its trades.

Per leg the order is DUPLICATE (listed twice on BankTrades, or the Trade ID repeated on Trades so that TradeCalc lists it more than once), MISSING IN CALCULATOR (No trade leg with this ID on TradeCalc.), EXCLUDED (the trade is excluded on TradeCalc; the note starts Excluded on TradeCalc: and quotes its reasons), then per figure INVALID INPUT, NOT APPLICABLE (for example a margined MF for a leg of an unmargined set, or a supervisory duration outside IR and credit), MATCH or DIFFERENCE. Legs with no bank row are listed as NOT IN BANK FILE with their notional.

---

🔄 Freshness of a reconciliation

When a reconciliation is made, its fingerprint — the input fingerprint plus the bank table and the three settings — is stored in SACCR_ReconInputs or SACCR_TradeReconInputs. C5 of each reconciliation sheet is a formula:

=ReconciliationStatusText(SACCR_ReconInputs, SACCR_RunInputs, BankNettingSets!$1:$1048576, NettingSets!$C:$S, Trades!$C:$AC, Params!$1:$1048576)

TradeReconciliation C5 uses TradeReconciliationStatusText with SACCR_TradeReconInputs and BankTrades!$1:$1048576. Up to 1.0.1 both formulas watched NettingSets!$C:$Q and Trades!$C:$AA; the template of 1.0.2 widens them to the new input columns.

C5 reads	Meaning
Reconciliation current (bank figures and inputs XXXXXXXX)	Matches the bank figures, settings and inputs as they are
OUT OF DATE - bank figures or inputs changed since the last run; press Run SA-CCR (on Results or Trades).	A bank figure, a setting or an input changed after the run
No reconciliation: enter bank figures on BankNettingSets and press Run SA-CCR (on Results or Trades).	None made, or no current results
Status unavailable: <reason>	A workbook name it needs is missing

The run's result line reports each comparison as reconciliation= and trade_reconciliation=: DONE (rows written), NONE (no bank figures), FAILED (settings or headers unusable; C4 says why) or UNAVAILABLE (a workbook built from a template without these sheets).

---

🧾 Export evidence

Export evidence (button on Reconciliation, macro ExportEvidence) writes the current run to one CSV file, offered as K-SACCR_evidence_<yyyymmdd-hhnnss>.csv in the workbook's folder.

Format

Property	Value
Separator	;
Decimal point	., no thousands separator, up to 15 significant digits, whatever Excel's regional settings
Encoding	UTF-8 without byte-order mark
Line end	CR LF
First field of every line	The section name
First line	RUN;format;K-SACCR evidence 1
Logical values	TRUE / FALSE
Error values	Their Excel names (#N/A, #SPILL!, …)
Text	Quoted only when it holds ;, a quote or a line break. Text starting with =, +, -, @, a tab or a carriage return gets a leading apostrophe, so Excel does not read it as a formula; numbers keep their sign

Sections

Section	Content
RUN	format, run_summary (Results C4), results_status, inputs (input fingerprint), outputs (SACCR_RunOutputs), reporting_date, reporting_ccy, regime_default, reference_parameter_set (CRE52-CRR-2026-10-09, the set the template ships), total_ead_crr/total_status_crr and total_ead_bcbs/total_status_bcbs for each TOTAL row present, checks_errors, checks_warnings, checks_info, then for each reconciliation its state (CURRENT or NOT MADE, or UNAVAILABLE), _inputs, _summary and — only when it was made — _scale, _abs_tol, _rel_tol
PARAMS	Every parameter code and its value
SF_TABLE	The factor table as the run read it, header to first blank key (also rows below the named range), columns SF_Key to Regimes
FX_TABLE	The FX table, the same way
RESULTS	The Results table with its header
RECONCILIATION	The Reconciliation table with its header
TRADE_RECONCILIATION	The TradeReconciliation table with its header
CHECKS	The Checks table with its header
LIMITATIONS	Seven numbered statements of what the report does and does not show

The LIMITATIONS lines say, in substance: the report describes one run and is not a regulatory certification; a comparison with a bank is not independent validation (#100); illustrative expected values validate nothing; totals cover one regime's VALID sets and regimes are never added; INVALID, INCOMPLETE and NO TRADES sets have no EAD; bank amounts are scaled as stated in RUN, ratios and times are not; lines holding a run or reconciliation time change at every run, every other line depends only on the inputs. TradeCalc, HedgingSets and Buckets are not exported, and the workbook's file name is not in the report (a Save As would change it for the same run).

Screenshot: the start of an evidence CSV

Refusal rules

The export never describes results that differ from the inputs or from what the run wrote. It is refused, with Evidence not exported: <reason>., when:

Condition	Reason given
Excel has a recalculation pending (manual calculation)	Excel has not finished recalculating; press F9, then Run SA-CCR if the results are out of date
ResultsStatus() is STALE or NONE	the results are not those of the current inputs; press Run SA-CCR first
A reconciliation does not match its bank figures, settings and inputs as they are now — also when every bank figure was removed after the run, or figures were entered that were never compared	the reconciliation of the bank figures on BankNettingSets is not current; press Run SA-CCR first (or BankTrades)
No output fingerprint was recorded	the outputs of the last run were not recorded; press Run SA-CCR first
An output cell on Results, Checks or a reconciliation sheet differs from what the run wrote (ResultsStatus() is EDITED, from 1.0.1)	an output cell on Results, Checks or a reconciliation sheet was changed after the run; press Run SA-CCR first

The file replaces any file of that name (a read-only file is left alone and the write fails). The repository's TEST_Reconcile suite checks that two exports of an unchanged run are identical byte for byte and that an edit to an input, a bank figure or an output makes the export refuse.

ExportEvidence_Silent

For automation and tests, without a dialog:

? M_Main.ExportEvidence_Silent("C:\path\run.csv")

RESULT=OK; lines=…; file=C:\path\run.csv
RESULT=REFUSED; reason=the results are not those of the current inputs; press Run SA-CCR first

The refusal reasons are those of the table above. A file that cannot be written raises to the caller.

---

🔗 Related

• Outputs and Statuses — Results, TradeCalc and freshness
• How It Works — the entry points and the result line
• Validation Approach — reconcile progressively
• Troubleshooting — export refused, reconciliation failed
• Workbook Guide · Home