Excel Workbook Risk Diagnostic Tool

Prototype — Proof of Concept
A local, browser-based tool for life insurance actuaries to upload Excel workbooks and receive a structured PDF diagnostic report identifying risks across 24 categories.

---

⚠️ Prototype Disclaimer

This tool is a proof of concept and has not been through full or formal testing. It is provided for educational and reference purposes only.

• Do not rely on its outputs for production, regulatory, or commercial decisions.
• Results may be incomplete, inaccurate, or misleading.
• Use entirely at your own risk. See LICENSE for full terms.

---

Overview

The tool runs entirely on your local machine. No data is sent to any external server. It analyses .xlsx and .xlsm workbooks and produces:

• An interactive summary within the browser (RAG rating, finding counts, top findings)
• A downloadable structured PDF report per workbook

What it checks

Tier	Category	Checks
1 — Automated	Formulas	Hardcoded literals, VLOOKUP approximate match, volatile functions, inconsistent ranges, paste overrides, mixed $ anchors
1 — Automated	Errors	Cell errors (#REF!, #N/A, #DIV/0!, etc.)
1 — Automated	Links	Broken external links, broken named ranges, circular references
1 — Automated	Structure	File size, excessive cells, hidden sheets, sheet protection
1 — Automated	VBA	VBA presence, hardcoded parameters, missing error handling, On Error Resume Next, dangerous commands, hardcoded file paths
2 — Heuristic	Actuarial	High-sensitivity sheet detection, VLOOKUP escalation, assumption consistency, isolated hardcoded values

---

Installation

Requirements: Python 3.9 or later.

# 1. Clone the repository
git clone https://github.com/chadwickkcc/Excel-Risks-Checker---Prototype.git
cd "Excel-Risks-Checker---Prototype"

# 2. (Recommended) create a virtual environment
python -m venv .venv
source .venv/bin/activate        # macOS / Linux
.venv\Scripts\activate.bat       # Windows

# 3. Install dependencies
pip install -r requirements.txt

---

Running the Application

streamlit run app.py

The app will open automatically in your default browser at http://localhost:8501.

---

Using the Settings Panel

The sidebar contains a Settings panel where you can adjust:

Setting	Default	Description
File size warning (MB)	20	Files above this receive a Medium finding
File size critical (MB)	50	Files above this receive a High finding
Max cells per sheet	50,000	Sheets with more populated cells trigger a finding
Actuarial keywords	See below	Sheet names containing these words are flagged as sensitive

Default actuarial keywords: assumption, mortality, lapse, discount, reserve, capital, reinsurance, premium, claim, exposure, liability, asset

Click Apply Settings to activate changes before running analysis.

---

Understanding the Report

RAG Rating

Rating	Score	Meaning
🟢 Green	0–10	Low risk — no immediate action required
🟠 Amber	11–40	Moderate risk — review High and Medium findings before use
🔴 Red	41+	High risk — significant issues require resolution

Risk Score

Each finding contributes to a total risk score:

• High severity: 10 points each
• Medium severity: 3 points each
• Low severity: 1 point each

PDF Report Sections

1. Cover page — RAG badge, finding counts, file metadata
2. Executive Summary — auto-generated plain-English summary
3. AI Workbook Intelligence (only if AI commentary is enabled) — workbook purpose, sheet-by-sheet narratives, key formula explanations, and assumption commentary; all content labelled "AI-Generated — Interpretive Only"
4. Findings by Category — one section per checker type
5. High-Sensitivity Sheets — findings specific to actuarially sensitive sheets
6. Appendix — complete findings table sorted by severity

---

Architecture

app.py                    ← Streamlit UI (entry point)
settings.py               ← All configurable constants
models.py                 ← Finding / WorkbookAnalysisResult / AI data classes
workbook_analyser.py      ← Orchestrator (callable without UI)
checkers/
    formula_checker.py    ← Checks 1–6
    error_checker.py      ← Check 7
    link_checker.py       ← Checks 8–10
    structure_checker.py  ← Checks 11–14
    vba_checker.py        ← Checks 15–20
    actuarial_checker.py  ← Checks 21–24
reporter.py               ← ReportLab PDF generation
digest_builder.py         ← Extracts token-efficient workbook digest for AI
ai_analyser.py            ← Anthropic API calls; returns AICommentary

workbook_analyser.analyse_workbook(Path(...)) can be called independently of Streamlit for batch processing or scripted use.

---

AI Commentary (Optional)

The tool includes an optional AI-powered commentary layer that calls the Anthropic API to produce plain-English analysis alongside the deterministic findings. All four features are opt-in and gated behind a sidebar toggle — the deterministic checks run entirely offline whether or not AI is enabled.

Enabling AI Commentary

1. Open the AI Commentary (Optional) expander in the sidebar.
2. Check Enable AI commentary.
3. Enter your Anthropic API key in the password field and click Set API Key. The field clears immediately on submission and a confirmation message is shown in its place. The key is held in Streamlit session state only and is never written to disk. Click Remove API key to clear it at any time.
4. Select which features to include (all are enabled by default).
5. Run the analysis — expect an additional 30–60 seconds per workbook.

AI Features

Feature	Description
Findings narrative	Plain-English synthesis of risk findings for the PDF executive summary
Workbook purpose & sheet summaries	What the workbook does and what each tab contributes
Key formula explanations	Plain-English explanation of the most structurally complex formulas
Assumption / input commentary	Discovery and plausibility review of input parameters

Model

AI features use claude-sonnet-4-6 (configurable via AI_MODEL in settings.py).

AI Output in the PDF

If AI commentary was generated, the PDF report gains an "AI Workbook Intelligence" section between the Executive Summary and the Findings by Category section. All AI-generated content is labelled "AI-Generated — Interpretive Only" and carries a disclaimer footer.

---

Limitations

• Password-protected files cannot be analysed. Remove the password before uploading.
• VBA analysis requires .xlsm format. .xlsx files never contain VBA and this is handled gracefully.
• Formula checks use openpyxl's formula string representation, which may not match Excel's display in all cases (e.g. array formulas).
• Circular references are inferred from iterative calculation settings rather than traced directly — some edge cases may be missed.
• External link accessibility is checked relative to the temp upload path; external files will almost always appear inaccessible during upload.

---

Test Files

To validate key checks, create these three test workbooks:

test_formulas.xlsx

• Sheet named Assumptions: formula =A1*0.045, a VLOOKUP with no 4th argument, a plain value surrounded by formula cells
• Sheet named Mortality: cells with =NOW() and =INDIRECT("A1")

test_structure.xlsx

• A hidden sheet named Hidden_Reserves
• A protected worksheet
• 50,000+ populated cells on one sheet

test_macros.xlsm (requires Excel to create)

• VBA module containing:
  • On Error Resume Next
  • Kill "C:\temp\file.txt"
  • A Sub with no On Error statement
  • Dim dRate As Double: dRate = 0.045

---

Customising Checks

All thresholds are in settings.py. Edit that file to:

• Add new actuarial keywords
• Change severity point weights
• Modify the list of dangerous VBA commands
• Adjust the "safe" numeric constants excluded from literal checks

---

License

This project is not open source. Source code is made available for educational and reference purposes only. No rights to use, copy, modify, distribute, or commercialise this software are granted.

See LICENSE for full terms.

Copyright © 2026 chadwickkcc@gmail.com. All rights reserved.