{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cell-000",
   "metadata": {},
   "source": [
    "# Lesson 7 — Automated Excel Engineering with openpyxl\n",
    "\n",
    "**Companion notebook** for the Lesson 7 slides. You build a small version of the\n",
    "underwriting panel step by step and save it to `../output/`. Open the file in Excel\n",
    "(or LibreOffice) after each step to see what changed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-001",
   "metadata": {},
   "outputs": [],
   "source": [
    "def check(label, got, expected, tol=1e-6):\n",
    "    \"\"\"Compare your answer with the expected value; prints ✅ or ❌ (never raises).\"\"\"\n",
    "    import math\n",
    "    try:\n",
    "        if isinstance(expected, float) or isinstance(got, float):\n",
    "            ok = math.isclose(float(got), float(expected), rel_tol=tol, abs_tol=tol)\n",
    "        elif isinstance(expected, (list, tuple)) and expected and isinstance(expected[0], str):\n",
    "            ok = list(got) == list(expected)\n",
    "        elif hasattr(expected, \"__len__\") and not isinstance(expected, str):\n",
    "            import numpy as np\n",
    "            ok = np.allclose(np.asarray(got, dtype=float), np.asarray(expected, dtype=float), rtol=tol, atol=tol)\n",
    "        else:\n",
    "            ok = got == expected\n",
    "    except Exception:\n",
    "        ok = False\n",
    "    print((\"✅ \" if ok else \"❌ \") + f\"{label}: got {got!r}, expected {expected!r}\")\n",
    "import shutil\n",
    "import subprocess\n",
    "from pathlib import Path\n",
    "\n",
    "import pandas as pd\n",
    "from openpyxl import Workbook, load_workbook\n",
    "from openpyxl.formatting.rule import CellIsRule\n",
    "from openpyxl.styles import Alignment, Border, Font, PatternFill, Side\n",
    "from openpyxl.workbook.defined_name import DefinedName\n",
    "\n",
    "OUT = Path(\"../output\")\n",
    "OUT.mkdir(exist_ok=True)\n",
    "TEAL, AMBER, INK = \"0E6B6B\", \"C26A1B\", \"1B2330\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-002",
   "metadata": {},
   "source": [
    "## 1. Segment data from the ABT\n",
    "Experience by vehicle style (the full panel uses territory × age band from Lesson 6)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-003",
   "metadata": {},
   "outputs": [],
   "source": [
    "abt = pd.read_parquet(\"../data/processed/abt.parquet\")\n",
    "seg = (abt.groupby(\"vehicle_style\")\n",
    "          .agg(car_years=(\"exposure\", \"sum\"), claims=(\"n_claims\", \"sum\"),\n",
    "               incurred=(\"incurred\", \"sum\"), earned_premium=(\"earned_premium\", \"sum\"))\n",
    "          .reset_index().round(2))\n",
    "seg"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-004",
   "metadata": {},
   "source": [
    "## 2. A workbook with a Data sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-005",
   "metadata": {},
   "outputs": [],
   "source": [
    "wb = Workbook()\n",
    "data = wb.active\n",
    "data.title = \"Data\"\n",
    "data.append(list(seg.columns))\n",
    "for row in seg.itertuples(index=False):\n",
    "    data.append(list(row))\n",
    "data.freeze_panes = \"A2\"\n",
    "n_rows = len(seg) + 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-006",
   "metadata": {},
   "source": [
    "---\n",
    "## Your turn 1 — a header-styling helper\n",
    "\n",
    "Write `style_header(ws, row, first_col, labels)`: white bold text on a teal fill,\n",
    "centered, with thin borders."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-007",
   "metadata": {},
   "outputs": [],
   "source": [
    "HEADER_FONT = Font(bold=True, color=\"FFFFFF\")\n",
    "HEADER_FILL = PatternFill(\"solid\", fgColor=TEAL)\n",
    "THIN = Side(style=\"thin\", color=\"E2DDD0\")\n",
    "BOX = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)\n",
    "\n",
    "\n",
    "def style_header(ws, row, first_col, labels):\n",
    "    for i, label in enumerate(labels):\n",
    "        cell = ws.cell(row=row, column=first_col + i, value=label)\n",
    "        ...  # your code here\n",
    "\n",
    "\n",
    "style_header(data, 1, 1, list(seg.columns))\n",
    "check(\"header is bold\", data[\"A1\"].font.bold, True)\n",
    "check(\"header fill color\", data[\"A1\"].fill.fgColor.rgb, \"00\" + TEAL)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-008",
   "metadata": {},
   "source": [
    "## 3. A Panel sheet with named parameter cells"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-009",
   "metadata": {},
   "outputs": [],
   "source": [
    "panel = wb.create_sheet(\"Panel\", 0)\n",
    "panel.sheet_view.showGridLines = False\n",
    "panel[\"B2\"] = \"Underwriting panel\"\n",
    "panel[\"B2\"].font = Font(size=16, bold=True, color=INK)\n",
    "panel[\"B4\"], panel[\"C4\"] = \"Target loss ratio\", 0.68\n",
    "panel[\"C4\"].number_format = \"0.0%\"\n",
    "panel[\"C4\"].fill = PatternFill(\"solid\", fgColor=\"F8E7D4\")\n",
    "wb.defined_names[\"target_lr\"] = DefinedName(\"target_lr\", attr_text=\"Panel!$C$4\")\n",
    "style_header(panel, 6, 2, [\"Style\", \"Car years\", \"Earned premium\", \"Incurred\", \"Loss ratio\", \"Indicated change\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-010",
   "metadata": {},
   "source": [
    "## Your turn 2 — live formulas\n",
    "\n",
    "For each style (rows 7 onwards), write **formulas** (strings starting with `=`):\n",
    "\n",
    "* C: car years via `SUMIFS` from the Data sheet\n",
    "* D: earned premium via `SUMIFS`; E: incurred via `SUMIFS`\n",
    "* F: loss ratio `=IFERROR(E/D,\"n/a\")`\n",
    "* G: indicated change `=IFERROR(F/target_lr-1,0)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-011",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, style in enumerate(seg[\"vehicle_style\"]):\n",
    "    r = 7 + i\n",
    "    panel[f\"B{r}\"] = style\n",
    "    crit = f\"Data!$A$2:$A${n_rows},$B{r}\"\n",
    "    ...  # your code here\n",
    "    for col, fmt in zip(\"CDEFG\", [\"#,##0.0\", \"#,##0\", \"#,##0\", \"0.0%\", \"+0.0%;-0.0%\"]):\n",
    "        panel[f\"{col}{r}\"].number_format = fmt\n",
    "last = 6 + len(seg)\n",
    "panel.conditional_formatting.add(f\"F7:F{last}\", CellIsRule(operator=\"greaterThan\", formula=[\"target_lr\"],\n",
    "                                                           fill=PatternFill(\"solid\", fgColor=\"F6DCD8\")))\n",
    "path = OUT / \"style_panel.xlsx\"\n",
    "wb.save(path)\n",
    "check(\"F7 formula\", panel[\"F7\"].value, '=IFERROR(E7/D7,\"n/a\")')\n",
    "check(\"G7 formula\", panel[\"G7\"].value, \"=IFERROR(F7/target_lr-1,0)\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-012",
   "metadata": {},
   "source": [
    "## 4. openpyxl never calculates — so test by recalculating\n",
    "\n",
    "Reading back with `data_only=True` gives `None` for formulas until a spreadsheet\n",
    "program has calculated and saved the file. If LibreOffice is installed, we can do that\n",
    "headlessly and compare with pandas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-013",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"before recalculation:\", load_workbook(path, data_only=True)[\"Panel\"][\"F7\"].value)\n",
    "soffice = shutil.which(\"soffice\") or shutil.which(\"libreoffice\")\n",
    "recalc_lr = None\n",
    "if soffice:\n",
    "    subprocess.run([soffice, \"--headless\", \"--calc\", \"--convert-to\", \"xlsx\", \"--outdir\", str(OUT / \"recalc\"),\n",
    "                    str(path)], capture_output=True, timeout=120)\n",
    "    ws = load_workbook(OUT / \"recalc\" / path.name, data_only=True)[\"Panel\"]\n",
    "    recalc = {ws[f\"B{r}\"].value: ws[f\"F{r}\"].value for r in range(7, last + 1)}\n",
    "    recalc_lr = recalc.get(\"Sports\")\n",
    "    print(recalc)\n",
    "else:\n",
    "    print(\"LibreOffice not found — open the file in Excel to see the values.\")\n",
    "\n",
    "sports = seg.set_index(\"vehicle_style\").loc[\"Sports\"]\n",
    "if soffice:\n",
    "    check(\"Sports loss ratio (recalculated)\", recalc_lr, sports[\"incurred\"] / sports[\"earned_premium\"], tol=1e-6)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}