Xlsx

Requirements for Outputs

All Excel files

Zero Formula Errors

• Every Excel model MUST be delivered with ZERO formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?)

Preserve Existing Templates (when updating templates)

• Study and EXACTLY match existing format, style, and conventions when modifying files
• Never impose standardized formatting on files with established patterns
• Existing template conventions ALWAYS override these guidelines

Brand Standards (Bring Your Own Theme)

Apply the configured brand styling below to all new workbooks unless the file has an established template. This is a default palette, swap it for the client or programme's own colours, logo, and fonts per engagement.

Colour Palette (openpyxl hex — no # prefix)

| Role | Name | Hex | openpyxl constant | |---|---|---|---| | Primary | Dark Navy | 0B3254 | NAVY = "0B3254" | | Secondary | Bright Blue | 13B5EA | BLUE = "13B5EA" | | Tertiary | Light Blue | B1DAF6 | LIGHT_BLUE = "B1DAF6" | | Highlight | Purple | 95358C | PURPLE = "95358C" | | Positive | Dark Green | 006E47 | DARK_GREEN = "006E47" | | Positive (alt) | Green | 009946 | GREEN = "009946" | | Positive (light) | Light Green | 7BC143 | LIGHT_GREEN = "7BC143" | | Neutral BG | Light Grey | E6EAEE | LIGHT_GREY = "E6EAEE" | | Muted | Grey Blue | 8598A9 | GREY_BLUE = "8598A9" | | Text | Black | 000000 | BLACK = "000000" | | Background | White | FFFFFF | WHITE = "FFFFFF" |

Paste these constants at the top of any build script:

NAVY, BLUE, LIGHT_BLUE = "0B3254", "13B5EA", "B1DAF6"
PURPLE = "95358C"
DARK_GREEN, GREEN, LIGHT_GREEN = "006E47", "009946", "7BC143"
LIGHT_GREY, GREY_BLUE = "E6EAEE", "8598A9"
BLACK, WHITE = "000000", "FFFFFF"

Cover / Title Sheet Pattern

Every workbook should include a styled cover sheet as Sheet 1.

Header band is WHITE (no fill). The logo has blue text that clashes with a navy background.

Cover content, in this order:

1. Logo, top-left
2. Report title — large, navy text
3. Synopsis — max 2 plain-English sentences stating the hypothesis and approach. No AI-sounding language ("this analysis leverages...", "comprehensive review..."). Write like you're briefing a project director over coffee. Follow with dot-point key findings.
4. Assumptions — clearly listed. Every assumption must be verified with the user during planning (plan mode or conversation). If unverified, prefix with "⚠ Unverified:". Never assume silently.
5. How to Read This Workbook — list each tab with a one-line description of what it contains and what to look for. This is for stakeholders receiving the handover.

from openpyxl.drawing.image import Image as XLImage

cover = wb.create_sheet("Cover", 0)
cover.sheet_view.showGridLines = False

# White header band (rows 1–6) — no fill, logo has blue text
# (No fill loop needed — default is white)

# Logo, top-left
logo_path = "/Template/logo.png"
if os.path.exists(logo_path):
    img = XLImage(logo_path)
    img.height, img.width = 48, 120
    cover.add_image(img, "B2")

# Report title (navy, large, row 4)
cover["C4"].value = "Report Title"
cover["C4"].font = Font(name="Arial", bold=True, size=24, color=NAVY)

# Subtitle / date (Bright Blue, row 5)
cover["C5"].value = "Subtitle or date"
cover["C5"].font = Font(name="Arial", size=12, color=BLUE)

# Blue rule divider
for row in cover.iter_rows(min_row=7, max_row=7, min_col=1, max_col=20):
    for cell in row:
        cell.fill = PatternFill("solid", fgColor=BLUE)

# Synopsis starts row 9 — plain English, 2 sentences max
cover["B9"].value = "Synopsis: [2-sentence hypothesis and approach]"
cover["B9"].font = Font(name="Arial", size=11, color=BLACK)
# Follow with dot-point findings in rows below

# Assumptions section — label row, then dot points
# assumptions_start_row = after synopsis
# cover.cell(row=X, column=2).value = "Assumptions"
# cover.cell(row=X, column=2).font = Font(name="Arial", bold=True, size=11, color=NAVY)

# How to Read This Workbook — tab schema for stakeholders
# cover.cell(row=Y, column=2).value = "How to Read This Workbook"
# cover.cell(row=Y, column=2).font = Font(name="Arial", bold=True, size=11, color=NAVY)
# List each tab with one-line description

cover.row_dimensions[1].height = 8
for r in range(2, 6): cover.row_dimensions[r].height = 18
cover.row_dimensions[7].height = 4
cover.column_dimensions["A"].width = 2
cover.column_dimensions["B"].width = 18

Logo extraction (one-off setup):

from pptx import Presentation
import os, shutil

prs = Presentation("/Template/MASTER - PowerPoint template.pptx")
logo_dest = "/Template/logo.png"
for slide in prs.slides:
    for shape in slide.shapes:
        if shape.shape_type == 13:  # MSO_SHAPE_TYPE.PICTURE
            with open(logo_dest, "wb") as f:
                f.write(shape.image.blob)
            print(f"Logo saved: {logo_dest}")
            break
    if os.path.exists(logo_dest): break

Table Formatting Standards

| Element | Style | |---|---| | Header row | Dark Navy (0B3254) fill, White text, 11pt bold | | Body rows | Alternating White / Light Grey (E6EAEE), 10pt regular | | Text columns | Left-aligned | | Number columns | Right-aligned | | Totals / key rows | Bright Blue (13B5EA) fill, White text | | Borders | No vertical borders; thin horizontal Light Grey between rows |

def style_header_row(ws, row_num, max_col):
    for col in range(1, max_col + 1):
        cell = ws.cell(row=row_num, column=col)
        cell.fill = PatternFill("solid", fgColor=NAVY)
        cell.font = Font(name="Arial", bold=True, size=11, color=WHITE)
        cell.alignment = Alignment(horizontal="left")

def style_data_rows(ws, start_row, end_row, max_col):
    for row in range(start_row, end_row + 1):
        bg = WHITE if (row - start_row) % 2 == 0 else LIGHT_GREY
        for col in range(1, max_col + 1):
            ws.cell(row=row, column=col).fill = PatternFill("solid", fgColor=bg)

Charts — Mode Selection

Before generating any charts, ask the user: > "Do you need these charts to be editable in Excel, or exec-quality (embedded as images)?"

| Mode | Library | When to use | Trade-off | |---|---|---|---| | Editable | openpyxl chart objects | Working models, iterative analysis | Limited styling | | Exec-quality | matplotlib → PNG embedded | CFO packs, steerco, handovers | Not editable in Excel |

Default recommendation: exec-quality unless the user says the workbook is a working model or needs chart editability.

Colour Series Order (both modes)

| Order | Name | Hex (openpyxl) | matplotlib | |---|---|---|---| | 1 | Dark Navy | 0B3254 | #0B3254 | | 2 | Bright Blue | 13B5EA | #13B5EA | | 3 | Light Blue | B1DAF6 | #B1DAF6 | | 4 | Green | 009946 | #009946 | | 5 | Light Green | 7BC143 | #7BC143 | | 6 | Grey Blue | 8598A9 | #8598A9 |

Positive variance → Green (#009946). Negative variance → Purple (#95358C).

Exec-Quality Charts (matplotlib)

Brand Theme Setup (example palette, swap for your own)

Paste at the top of any build script that uses matplotlib charts:

import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
from matplotlib.patches import FancyBboxPatch
from openpyxl.drawing.image import Image as XLImage
import tempfile, os

# Brand palette, matplotlib format (example, swap for your own)
V_NAVY, V_BLUE, V_LIGHT_BLUE = "#0B3254", "#13B5EA", "#B1DAF6"
V_GREEN, V_LIGHT_GREEN = "#009946", "#7BC143"
V_GREY_BLUE, V_PURPLE = "#8598A9", "#95358C"
V_SERIES = [V_NAVY, V_BLUE, V_LIGHT_BLUE, V_GREEN, V_LIGHT_GREEN, V_GREY_BLUE]

FONT_FAMILY = "Source Sans Pro"
# Fallback if Source Sans Pro not installed
try:
    from matplotlib.font_manager import findfont, FontProperties
    findfont(FontProperties(family=FONT_FAMILY), fallback_to_default=False)
except ValueError:
    FONT_FAMILY = "Arial"

plt.rcParams.update({
    "font.family": FONT_FAMILY,
    "font.size": 10,
    "axes.titlesize": 13,
    "axes.titleweight": "bold",
    "axes.labelsize": 10,
    "axes.spines.top": False,
    "axes.spines.right": False,
    "axes.edgecolor": "#CCCCCC",
    "axes.titlecolor": V_NAVY,
    "axes.labelcolor": V_NAVY,
    "xtick.color": "#666666",
    "ytick.color": "#666666",
    "figure.facecolor": "white",
    "axes.facecolor": "white",
    "legend.frameon": False,
    "legend.fontsize": 9,
})

Embed Helper

Use this to place a matplotlib figure into an Excel sheet:

def embed_chart(fig, ws, anchor="B2", width_cm=16, height_cm=10, dpi=200):
    """Save matplotlib figure and embed as image in worksheet."""
    tmp = tempfile.NamedTemporaryFile(suffix=".png", delete=False)
    fig.savefig(tmp.name, dpi=dpi, bbox_inches="tight", facecolor="white", edgecolor="none")
    plt.close(fig)
    img = XLImage(tmp.name)
    img.width = width_cm * 37.8  # cm to px approx
    img.height = height_cm * 37.8
    ws.add_image(img, anchor)
    return tmp.name  # caller can os.remove() after wb.save()

Supported Chart Types

Horizontal bar (preferred for category comparisons):

fig, ax = plt.subplots(figsize=(8, 5))
bars = ax.barh(categories, values, color=V_SERIES[:len(categories)])
ax.set_xlabel("Spend ($M)")
ax.set_title("Spend by Category")
ax.xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"${x:,.0f}"))
for bar, val in zip(bars, values):
    ax.text(bar.get_width() + max(values)*0.01, bar.get_y() + bar.get_height()/2,
            f"${val:,.0f}", va="center", fontsize=9, color=V_NAVY)
fig.tight_layout()

Stacked bar (composition over time):

fig, ax = plt.subplots(figsize=(8, 5))
bottom = [0] * len(periods)
for i, (label, vals) in enumerate(series.items()):
    ax.bar(periods, vals, bottom=bottom, label=label, color=V_SERIES[i % len(V_SERIES)])
    bottom = [b + v for b, v in zip(bottom, vals)]
ax.set_title("Spend Composition by Period")
ax.legend(loc="upper left", bbox_to_anchor=(1, 1))
ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"${x:,.0f}"))
fig.tight_layout()

Waterfall (variance / bridge):

def waterfall_chart(labels, values, title="Bridge"):
    fig, ax = plt.subplots(figsize=(10, 5))
    cumulative = 0
    bottoms, colors = [], []
    for i, v in enumerate(values):
        if i == 0 or i == len(values) - 1:  # start/end totals
            bottoms.append(0)
            colors.append(V_NAVY)
        elif v >= 0:
            bottoms.append(cumulative)
            colors.append(V_GREEN)
        else:
            bottoms.append(cumulative + v)
            colors.append(V_PURPLE)
        if i != len(values) - 1:
            cumulative += v
    ax.bar(labels, [abs(v) for v in values], bottom=bottoms, color=colors, width=0.6)
    for i, (lbl, v) in enumerate(zip(labels, values)):
        y = bottoms[i] + abs(v) / 2
        ax.text(i, y, f"${v:+,.0f}" if i not in (0, len(values)-1) else f"${v:,.0f}",
                ha="center", va="center", fontsize=9, fontweight="bold", color="white")
    ax.set_title(title)
    ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"${x:,.0f}"))
    fig.tight_layout()
    return fig

Combo bar + line (actuals vs target):

fig, ax1 = plt.subplots(figsize=(8, 5))
ax1.bar(periods, actuals, color=V_NAVY, label="Actual", width=0.5)
ax2 = ax1.twinx()
ax2.plot(periods, targets, color=V_BLUE, marker="o", linewidth=2, label="Target")
ax1.set_title("Actual vs Target")
lines1, labels1 = ax1.get_legend_handles_labels()
lines2, labels2 = ax2.get_legend_handles_labels()
ax1.legend(lines1 + lines2, labels1 + labels2, loc="upper left")
fig.tight_layout()

Donut (use sparingly — only for single-metric share):

fig, ax = plt.subplots(figsize=(5, 5))
wedges, texts, autotexts = ax.pie(
    values, labels=labels, colors=V_SERIES[:len(values)],
    autopct="%1.0f%%", startangle=90, pctdistance=0.75,
    wedgeprops=dict(width=0.4, edgecolor="white", linewidth=2))
for t in autotexts:
    t.set_fontsize(9)
    t.set_fontweight("bold")
ax.set_title("Share of Spend")
fig.tight_layout()

Chart Standards

• No pie charts in full-circle form — use donut or horizontal bar instead
• No 3D effects, no gradients, no shadows
• Always include a clear title — title states the insight, not just the metric (e.g. "Telco dominates discretionary spend" not "Spend by Sector")
• Data labels on bars/waterfall — readers shouldn't need to eyeball the axis
• Currency axis: always formatted $#,##0 with mticker.FuncFormatter
• Legend outside plot area when >3 series
• Figure size: default (8, 5) for full-width, (5, 5) for square/donut
• DPI: 200 for embedded Excel, 300 if also used in slides
• Clean up temp files after wb.save() — collect paths from embed_chart() and os.remove()

Editable Charts (openpyxl)

Use openpyxl chart objects when the user explicitly requests editable charts. Apply the colour series order above. Note: styling control is limited — set chart.style, series fill, and axis labels but expect "default Excel" appearance.

from openpyxl.chart import BarChart, Reference
from openpyxl.chart.series import DataPoint
from openpyxl.drawing.fill import PatternFillProperties, ColorChoice

chart = BarChart()
chart.type = "col"
chart.title = "Chart Title"
chart.y_axis.title = "Values"
data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=10)
cats = Reference(ws, min_col=1, min_row=2, max_row=10)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 4
ws.add_chart(chart, "E2")

---

Financial models

Color Coding Standards

Unless otherwise stated by the user or existing template

Industry-Standard Color Conventions

• Blue text (RGB: 0,0,255): Hardcoded inputs, and numbers users will change for scenarios
• Black text (RGB: 0,0,0): ALL formulas and calculations
• Green text (RGB: 0,128,0): Links pulling from other worksheets within same workbook
• Red text (RGB: 255,0,0): External links to other files
• Yellow background (RGB: 255,255,0): Key assumptions needing attention or cells that need to be updated

Number Formatting Standards

Required Format Rules

• Years: Format as text strings (e.g., "2024" not "2,024")
• Currency: Use $#,##0 format; ALWAYS specify units in headers ("Revenue ($mm)")
• Zeros: Use number formatting to make all zeros "-", including percentages (e.g., "$#,##0;($#,##0);-")
• Percentages: Default to 0.0% format (one decimal)
• Multiples: Format as 0.0x for valuation multiples (EV/EBITDA, P/E)
• Negative numbers: Use parentheses (123) not minus -123

Formula Construction Rules

Assumptions Placement

• Place ALL assumptions (growth rates, margins, multiples, etc.) in separate assumption cells
• Use cell references instead of hardcoded values in formulas
• Example: Use =B5(1+$B$6) instead of =B51.05

Formula Error Prevention

• Verify all cell references are correct
• Check for off-by-one errors in ranges
• Ensure consistent formulas across all projection periods
• Test with edge cases (zero values, negative numbers)
• Verify no unintended circular references

Documentation Requirements for Hardcodes

• Comment or in cells beside (if end of table). Format: "Source: [System/Document], [Date], [Specific Reference], [URL if applicable]"
• Examples:
• "Source: Company 10-K, FY2024, Page 45, Revenue Note, [SEC EDGAR URL]"
• "Source: Company 10-Q, Q2 2025, Exhibit 99.1, [SEC EDGAR URL]"
• "Source: Bloomberg Terminal, 8/15/2025, AAPL US Equity"
• "Source: FactSet, 8/20/2025, Consensus Estimates Screen"

XLSX creation, editing, and analysis

Overview

Create, edit, or analyze Excel spreadsheets with formulas, formatting, and data analysis. Apply this skill for spreadsheet processing using openpyxl and pandas. Recalculate formulas and ensure zero

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: Jiplet
• Source: Jiplet/transformation-os
• License: MIT

Install and usage instructions live in the source repository linked above.