3-Statement Financial Model Template Completion

Complete and populate integrated financial model templates with proper linkages between Income Statement, Balance Sheet, and Cash Flow Statement.

Model Structure

Identifying Template Tab Organization

Templates vary in their tab naming conventions and organization. Before populating, review all tabs to understand the template's structure. Below are common tab names and their typical contents:

Template Review Checklist

• Identify which tabs exist in the template (not all templates include every schedule)
• Note any template-specific tabs not listed above
• Understand tab dependencies (e.g., which schedules feed into the main statements)
• Locate input cells vs. formula cells on each tab

Understanding Template Structure

Before populating a template, familiarize yourself with its existing layout to ensure data is entered in the correct locations and formulas remain intact.

Identifying Row Structure

• Locate the model title at top of each tab
• Identify section headers and their visual separation
• Find the units row indicating $ millions, %, x, etc.
• Note column headers distinguishing Actuals vs. Estimates periods
• Confirm period labels (e.g., FY2024A, FY2025E)
• Identify input cells vs. formula cells (typically distinguished by font color)

Identifying Column Structure

• Confirm line item labels in leftmost column
• Verify historical years precede projection years
• Note the visual border separating historical from projected periods
• Check for consistent column order across all tabs

Working with Named Ranges Templates often use named ranges for key inputs and outputs. Before entering data:

• Review existing named ranges in the template (Formulas → Name Manager in Excel)
• Common named ranges include: Revenue growth rates, cost percentages, key outputs (Net Income, EBITDA, Total Debt, Cash), scenario selector cell
• Ensure inputs are entered in cells that feed into these named ranges

Projection Period

• Templates typically project 5 years forward from last historical year
• Verify historical (A) vs. projected (E) columns are clearly separated
• Confirm columns use fiscal year notation (e.g., FY2024A, FY2025E)

Margin Analysis

Note: The following margin analysis should only be performed if prompted by the user or if the template explicitly requires it. If no prompt is given, skip this section.

Calculate and display profitability margins on the Income Statement (IS) tab to track operational efficiency and enable peer comparison.

Core Margins to Include

Income Statement Layout with Margins

Display margin percentages directly below each profit line item:

• Gross Margin % below Gross Profit
• EBIT Margin % below EBIT
• EBITDA Margin % below EBITDA
• Net Income Margin % below Net Income

Credit Metrics

Note: The following Credit analysis should only be performed if prompted by the user or if the template explicitly requires it. If no prompt is given, skip this section.

Calculate and display credit/leverage metrics on the Balance Sheet (BS) tab to assess financial health, debt capacity, and covenant compliance.

Core Credit Metrics to Include

Credit Metric Hierarchy Checks

Validate that Upside shows strongest credit profile:

• Leverage: Upside < Base < Downside (lower is better)
• Coverage: Upside > Base > Downside (higher is better)
• Liquidity: Upside > Base > Downside (higher is better)

Covenant Compliance Tracking

If debt covenants are known, add explicit compliance checks comparing actual metrics to covenant thresholds.

Scenario Analysis (Base / Upside / Downside)

Use a scenario toggle (dropdown) in the Assumptions tab with CHOOSE or INDEX/MATCH formulas.

Key Drivers to Sensitize: Revenue growth, Gross margin, SG&A %, DSO/DIO/DPO, CapEx %, Interest rate, Tax rate.

Scenario Audit Checks: Toggle switches all statements, BS balances in all scenarios, Cash ties out, Hierarchy holds (Upside > Base > Downside for NI, EBITDA, FCF, margins).

SEC Filings Data Extraction

If the template specifically requires pulling data from SEC filings (10-K, 10-Q), see references/sec-filings.md for detailed extraction guidance. This reference is only needed when populating templates with public company data from regulatory filings.

Completing Model Templates

This section provides general guidance for completing any 3-statement financial model template while preserving existing formulas and ensuring data integrity.

Step 1: Analyze the Template Structure

Before entering any data, thoroughly review the template to understand its architecture:

Identify Input vs. Formula Cells

• Look for visual cues (font color, cell shading) that distinguish input cells from formula cells
• Common conventions: Blue font = inputs, Black font = formulas, Green font = links to other sheets
• Use Excel's Trace Precedents/Dependents (Formulas → Trace Precedents) to understand cell relationships
• Check for named ranges that may control key inputs (Formulas → Name Manager)

Map the Template's Flow

• Identify which tabs feed into others (e.g., Assumptions → IS → BS → CF)
• Note any supporting schedules and their linkages to main statements
• Document the template's specific line items and structure before populating

Step 2: Filling in Data Without Breaking Formulas

Golden Rules for Data Entry

Safe Data Entry Process

1. Identify the exact cells designated for input (usually highlighted or labeled)
2. Enter historical data first, then verify formulas are calculating correctly for those periods
3. Enter assumption drivers that feed forecast calculations
4. Review calculated outputs to confirm formulas are working as intended
5. If a formula cell must be modified, document the original formula before making changes

Handling Pre-Built Formulas

• If formulas reference cells you haven't populated yet, expect temporary errors (#REF!, #DIV/0!) until all inputs are complete
• When formulas produce unexpected results, trace precedents to identify missing or incorrect inputs
• Never delete rows/columns without checking for formula dependencies across all tabs

Step 3: Validating Formulas

Formula Integrity Checks

Before relying on template outputs, validate that formulas are functioning correctly:

Common Formula Issues to Watch For

• Mixed absolute/relative references causing incorrect results when copied across periods
• Broken links to external files or deleted ranges (#REF! errors)
• Division by zero in early periods before revenue ramps (#DIV/0! errors)
• Circular reference warnings (may be intentional for interest calculations)
• Inconsistent formulas across projection columns (use Ctrl+\ to find differences)

Validating Cross-Tab Linkages

• Confirm values that appear on multiple tabs are linked (not duplicated)
• Verify schedule totals tie to corresponding line items on main statements
• Check that period labels align across all tabs

Step 4: Quality Checks by Sheet

Perform these validation checks on each sheet after populating the template:

Income Statement (IS) Quality Checks

• Revenue figures match source data for historical periods
• All expense line items sum to reported totals
• Subtotals (Gross Profit, EBIT, EBT, Net Income) calculate correctly
• Tax calculation logic is appropriate (handles losses correctly)
• Forecast drivers reference assumptions tab (no hardcodes)
• Period-over-period changes are directionally reasonable

Balance Sheet (BS) Quality Checks

• Assets = Liabilities + Equity for every period (primary check)
• Cash balance matches Cash Flow Statement ending cash
• Working capital accounts tie to supporting schedules (if applicable)
• Retained Earnings rolls forward correctly: Prior RE + Net Income - Dividends +/- Adjustments = Ending RE
• Debt balances tie to debt schedule (if applicable)
• All balance sheet items have appropriate signs (assets positive, most liabilities positive)

Cash Flow Statement (CF) Quality Checks

• Net Income at top of CFO matches Income Statement Net Income
• Non-cash add-backs (D&A, SBC, etc.) tie to their source schedules/statements
• Working capital changes have correct signs (increase in asset = use of cash = negative)
• CapEx ties to PP&E schedule or fixed asset roll-forward
• Financing activities tie to changes in debt and equity accounts on BS
• Ending Cash matches Balance Sheet Cash
• Beginning Cash equals prior period Ending Cash

Supporting Schedule Quality Checks

• Opening balances equal prior period closing balances
• Roll-forward logic is complete (Beginning + Additions - Deductions = Ending)
• Schedule totals tie to main statement line items
• Assumptions used in calculations match Assumptions tab

Step 5: Cross-Statement Integrity Checks

After validating individual sheets, confirm the three statements are properly integrated:

Step 6: Final Review

Before considering the model complete:

• Toggle through all scenarios (if applicable) to verify checks pass in each case
• Review all #REF!, #DIV/0!, #VALUE!, and #NAME? errors and resolve or document
• Confirm all input cells have been populated (search for placeholder values)
• Verify units are consistent across all tabs
• Save a clean version before making any additional modifications

Model Validation and Audit

This section consolidates all validation checks and audit procedures for completed templates.

Core Linkages (Must Always Hold)

See references/formulas.md for all formula details.

Sign Convention Reference

Circular Reference Handling

Interest expense creates circularity: Interest → Net Income → Cash → Debt Balance → Interest

Enable iterative calculation in Excel: File → Options → Formulas → Enable iterative calculation. Set maximum iterations to 100, maximum change to 0.001. Add a circuit breaker toggle in Assumptions tab.

Check Categories

Section 1: Currency Consistency

• Currency identified and documented in Assumptions
• All tabs use consistent currency symbol and scale
• Units row matches model currency

Section 2: Balance Sheet Integrity

• Assets = Liabilities + Equity (for each period)
• Formula: Assets - Liabilities - Equity (must = 0)

Section 3: Cash Flow Integrity

• Cash ties to BS (CF Ending Cash = BS Cash)
• Cash Monthly vs Annual: Closing Cash (Monthly) = Closing Cash (Annual)
• NI ties to IS (CF Net Income = IS Net Income)
• D&A ties to schedule
• SBC ties to IS
• ΔAR, ΔInventory, ΔAP tie to WC schedule
• CapEx ties to DA schedule

Section 4: Retained Earnings

• RE roll-forward check: Prior RE + NI + SBC - Dividends = Ending RE
• Show component breakdown for debugging

Section 5: Working Capital

• AR, Inventory, AP tie to BS
• DSO, DIO, DPO reasonability checks (flag if outside normal ranges)

Section 6: Debt Schedule

• Total Debt ties to BS (Current + LT Debt)
• Interest calculation ties to IS

Section 6b: Equity Financing

• Equity issuance proceeds tie to BS Common Stock/APIC increase
• Cash increase from equity = Equity account increase (must balance)
• Equity Raise Tie-Out: ΔCommon Stock/APIC (BS) = Equity Issuance (CFF) (must = 0)
• Year 0 Equity Tie-Out: Equity Raised (Year 0) = Beginning Equity Capital (Year 1)

Section 6c: NOL Schedule

• Beginning NOL (Year 1 / Formation) = 0 (new business starts with zero NOL)
• NOL increases only when EBT < 0 (losses must be realized to generate NOL)
• DTA ties to BS (NOL Schedule DTA = BS Deferred Tax Asset)
• NOL utilization ≤ 80% of EBT (post-2017 federal limitation)
• NOL balance is non-negative (cannot utilize more than available)
• NOL generated only when EBT < 0
• Tax expense = 0 when taxable income ≤ 0

Section 7: Scenario Hierarchy

• Absolute metrics: Upside > Base > Downside (NI, EBITDA, FCF)
• Margins: Upside > Base > Downside (GM%, EBITDA%, NI%)
• Credit metrics: Upside < Base < Downside for leverage (inverted)

Section 8: Formula Integrity

• COGS, S&M, G&A, R&D, SBC driven by % of Revenue (no hardcodes)
• Consistent formulas across projection years
• No #REF!, #DIV/0!, #VALUE! errors

Section 9: Credit Metric Thresholds

• Flag metrics as Green/Yellow/Red based on covenant thresholds
• Summary of any red flags

Master Check Formula

Aggregate all section statuses into a single master check:

• If all sections pass → "✓ ALL CHECKS PASS"
• If any section fails → "✗ ERRORS DETECTED - REVIEW BELOW"

Quick Debug Workflow

When Master Status shows errors:

1. Scroll to find red-highlighted sections
2. Identify which check category has failures
3. Navigate to source tab to investigate
4. Fix the underlying issue
5. Return to Checks tab to verify resolution

Automation Skill

This skill provides 3 tools for creating and managing scheduled automations:

• check_automations - List all or inspect a specific automation
• create_automation - Create a new scheduled automation
• manage_automation - Update, pause, resume, trigger, or delete automations

You should call these tools directly instead of using ExecuteCode tool.

Before Creating an Automation

Always confirm with the user before calling create_automation. Automations run autonomously on a schedule, so getting the details right matters. If the user's request is unclear or underspecified, ask to clarify:

• Schedule — "Every morning" is ambiguous. Confirm the exact time and days (e.g. "Weekdays at 9 AM in your timezone?").
• Thread strategy — If the task involves ongoing analysis or follow-ups, ask whether they want results in a fresh thread each time, a single persistent thread, or the current conversation.
• Instruction — The instruction runs without further user input. If the user gives a vague prompt like "check my portfolio", refine it: what tickers? what metrics? what format?
• Delivery — If the user hasn't mentioned how they want to receive results, ask if they want delivery (e.g. Slack) or just in-app.

Summarize what you're about to create and get a "yes" before calling the tool.

Tool 1: check_automations

List all automations or inspect a specific one with execution history.

Examples

# List all automations
check_automations()

# Inspect a specific automation (includes last 5 executions)
check_automations(automation_id="abc-123")

Tool 2: create_automation

Create a new scheduled automation.

Thread Strategy

Schedule Format

• Recurring (cron): Standard 5-field cron expression
  • 0 9 * * 1-5 — weekdays at 9 AM
  • 0 */4 * * * — every 4 hours
  • 30 8 1 * * — 1st of each month at 8:30 AM
• One-time (ISO datetime):
  • 2026-03-01T10:00:00 — single execution at that time

Examples

# Daily market briefing on weekdays at 9 AM
create_automation(
    name="Morning Market Brief",
    instruction="Summarize overnight market moves, top gainers/losers, and any news for my watchlist.",
    schedule="0 9 * * 1-5",
)

# One-time earnings reminder
create_automation(
    name="AAPL Earnings Reminder",
    instruction="Analyze AAPL ahead of earnings: recent price action, analyst expectations, key metrics to watch.",
    schedule="2026-04-30T08:00:00",
    description="Pre-earnings analysis for Apple Q2 2026",
)

# Daily report delivered to Slack
create_automation(
    name="Morning Market Brief",
    instruction="Summarize overnight market moves for my watchlist.",
    schedule="0 9 * * 1-5",
    delivery="slack",
)

# Automation with persistent thread (all runs share history)
create_automation(
    name="Weekly Portfolio Review",
    instruction="Review my portfolio performance and update the analysis.",
    schedule="0 9 * * 1",
    thread="persistent",
)

# Automation that continues in the current conversation
create_automation(
    name="Hourly Price Check",
    instruction="Check AAPL, MSFT, GOOGL prices and alert if any moved >2%.",
    schedule="0 * * * *",
    thread="current",
)

Price-Triggered Automations

In addition to cron/datetime schedules, automations can trigger when a stock price meets a specific condition. Set trigger_type="price" and provide a trigger_config dict instead of (or alongside) a schedule.

Condition Types

For percentage conditions, reference sets the baseline price:

Retrigger Modes

Examples

# Alert when AAPL drops below $150 (one-shot)
create_automation(
    name="AAPL Price Alert",
    instruction="AAPL has dropped below $150. Summarize recent news and analyst sentiment.",
    trigger_type="price",
    trigger_config={
        "symbol": "AAPL",
        "conditions": [{"type": "price_below", "value": 150}],
    },
)

# Run analysis when TSLA moves up 5% from yesterday's close
create_automation(
    name="TSLA Momentum Alert",
    instruction="TSLA is up 5% from yesterday's close. Analyze volume, technicals, and any catalysts.",
    trigger_type="price",
    trigger_config={
        "symbol": "TSLA",
        "conditions": [
            {"type": "pct_change_above", "value": 5, "reference": "previous_close"},
        ],
    },
)

# Recurring alert with 4-hour cooldown
create_automation(
    name="BTC Volatility Watch",
    instruction="BTC moved more than 3% from today's open. Summarize order flow and sentiment.",
    trigger_type="price",
    trigger_config={
        "symbol": "BTC-USD",
        "conditions": [
            {"type": "pct_change_above", "value": 3, "reference": "day_open"},
        ],
        "retrigger": {"mode": "recurring", "cooldown_seconds": 14400},
    },
)

Agent Guidelines for Price Triggers

• Confirm before creating. Always repeat the symbol, condition, threshold value, and retrigger mode back to the user and get explicit confirmation.
• Default to one_shot retrigger mode unless the user asks for repeated alerts. For recurring, omit cooldown_seconds to default to once per trading day.
• Use flash mode by default for price-triggered automations (lightweight, low-latency execution).

Tool 3: manage_automation

Manage an existing automation.

Action Reference

Examples

# Pause an automation
manage_automation(automation_id="abc-123", action="pause")

# Resume it
manage_automation(automation_id="abc-123", action="resume")

# Trigger an immediate run
manage_automation(automation_id="abc-123", action="trigger")

# Update the schedule to run every Monday at 8 AM
manage_automation(
    automation_id="abc-123",
    action="update",
    schedule="0 8 * * 1",
)

# Switch an automation to a persistent thread
manage_automation(automation_id="abc-123", action="update", thread="persistent")

# Remove delivery from an automation
manage_automation(automation_id="abc-123", action="update", remove_delivery=True)

# Delete an automation
manage_automation(automation_id="abc-123", action="delete")

Catalyst Calendar

Build and maintain a calendar of upcoming catalysts across a coverage universe — earnings dates, conferences, product launches, regulatory decisions, and macro events. Helps prioritize attention and position ahead of events. Triggers on "catalyst calendar", "upcoming events", "what's coming up", "earnings calendar", "event calendar", or "catalyst tracker".

Workflow

Step 1: Define Coverage Universe

• List of companies to track (tickers or names)
• Sector / industry focus
• Include macro events? (Fed meetings, economic data, regulatory deadlines)
• Time horizon (next 2 weeks, month, quarter)

Step 2: Gather Catalysts

For each company, identify upcoming events using platform tools:

• Use macro MCP: get_earnings_calendar(from_date, to_date) for all companies reporting in date range
• Use macro MCP: get_economic_calendar(from_date, to_date) for upcoming macro events
• Use get_company_overview tool for company details
• Use WebSearch / WebFetch for news-driven catalysts

Earnings & Financial Events

• Quarterly earnings date and time (pre/post market)
• Annual shareholder meeting
• Investor day / analyst day
• Capital markets day
• Debt maturity / refinancing dates

Corporate Events

• Product launches or announcements
• FDA approvals / regulatory decisions
• Contract renewals or expirations
• M&A milestones (close dates, regulatory approvals)
• Management transitions
• Insider trading windows (lockup expirations)

Industry Events

• Major conferences (dates, which companies presenting)
• Trade shows and expos
• Regulatory comment periods or rulings
• Industry data releases (monthly sales, traffic, etc.)

Macro Events

• Fed meetings (FOMC dates)
• Jobs report, CPI, GDP releases
• Central bank decisions (ECB, BOJ, etc.)
• Geopolitical events with market impact

Step 3: Calendar View

Step 4: Weekly Preview

Each week, generate a forward-looking summary:

This Week's Key Events:

1. [Day]: [Company] Q[X] earnings — consensus [$X EPS], our estimate [$X], key focus: [metric]
2. [Day]: [Event] — why it matters for [stocks]
3. [Day]: [Macro release] — expectations and positioning

Next Week Preview:

• Early heads-up on important events coming

Position Implications:

• Events that could move specific positions
• Any pre-positioning recommended
• Risk management ahead of binary events

Step 5: Output

Save all outputs to $WORK_DIR/work/{task}/.

• Excel workbook with calendar view and sortable columns
• Weekly preview note (markdown)

For all Excel formatting standards, follow the guidelines in .agents/skills/xlsx/SKILL.md. After generating Excel, run recalculation: python .agents/skills/xlsx/scripts/recalc.py calendar.xlsx 30

Important Notes

• Earnings dates shift — verify against company IR pages and get_earnings_calendar closer to the date
• Pre-announce risk: track companies with a history of pre-announcing (positive or negative)
• Conference attendance lists are valuable — which companies are presenting and which are conspicuously absent?
• Some catalysts are recurring (monthly industry data) — build a template and auto-populate
• Color-code by impact level: Red = high impact, Yellow = moderate, Green = routine
• Archive past catalysts with the actual outcome — builds pattern recognition over time

Chart Annotation Skill

When to use

You want to call out a technical level, a pattern, or an event on a stock's price chart. Drawing directly on the chart is almost always clearer than describing it in prose. Reach for this skill whenever you would otherwise say "look at the level around 205" or "notice the downtrend from October to December".

MarketView is the app's live, TradingView-style price chart page (pan, zoom, switch timeframes). You do not need the user to be on it to annotate. If they are, the drawing appears on their live chart immediately. If they are in any other chat, the same drawing renders as a clickable preview card that expands into MarketView — so annotate freely whenever it helps, then mention the user can click it to open the full chart.

This skill provides two tools:

• draw_chart_annotation — add a single annotation to a chart.
• manage_chart_annotations — list, remove, or clear annotations.

Interactive chart vs. a Python chart (deliverable)

There are two ways to show price information visually — pick by what the user needs:

• This skill (interactive). Annotations land on the live, pannable MarketView chart (or a preview card that opens it). Best when the user just wants to see and explore a level, pattern, or event themselves — quick, in-the-moment, nothing to hand off.
• A Python chart (deliverable). A static image you render with code and embed in a report or document. Best when the output is a deliverable the user keeps, shares, or exports — a research note, PDF, or deck.

The two aren't exclusive: draw on the live chart for a quick look, render a Python chart when it belongs in a written artifact, or do both.

Charts are identified by SYMBOL:timeframe

Every annotation belongs to a chart identified by its ticker + timeframe (e.g. NVDA:1day) — that pair is the chart's id:

• Pass the same symbol + timeframe again to add to / edit that same chart (annotations accumulate on it).
• Use a different ticker or timeframe to start a separate chart — so you can draw several charts in one turn (e.g. AAPL:1day and AAPL:1hour, or AAPL:1day and MSFT:1day), each rendered as its own preview.

Always pass the ticker the user is discussing. timeframe defaults to 1day; set it to match the interval the user is viewing (one of 1min, 5min, 15min, 30min, 1hour, 4hour, 1day). Annotations are scoped to that one chart instance — a line drawn on NVDA:1day does not appear on NVDA:1hour.

Time format (any annotation with a time field). Pass ISO 8601 datetimes (e.g. 2024-11-14T00:00:00Z) aligned to a bar on the chart — for daily bars, midnight UTC of that day is safest. A time that doesn't land on a bar still renders but may look offset. Applies to trendline, marker, vertical_line, text, event, and fib_retracement.

Reacting to a user's chart selection

A <chart-selection> block in the user's turn means they selected something on the chart and sent it to you. Its selection_type is one of:

• region — a time×price box. Bounds come as a time range + price range, with the OHLCV bars inside it.
• price_level — a single horizontal price they tapped.

The user may send several blocks in one turn — treat each independently. A block may carry a User note: line: that is the user's own comment about that selection (separate from their message text) — let it steer what you look for there.

Analyze each bounded area (lean on the supplied bars and/or your market-data path), then, when it helps, draw your read back onto the same symbol + timeframe with draw_chart_annotation — a rectangle over a region, or a price_line at a price_level. Each block already spells out the matching draw_chart_annotation(...) call; adjust it to the level or zone your analysis actually lands on.

Picking the right variant

draw_chart_annotation takes an annotation object discriminated by its type field.

price_line — horizontal level

Use for anything flat on the y-axis: support, resistance, a target, a stop, an analyst price target, a 52-week high.

{
  "type": "price_line",
  "price": 205.0,
  "label": "Resistance 205",
  "style": "dashed"
}

trendline — two anchor points

Use to connect two (time, price) points on the chart: channel tops, pattern boundaries, connecting highs/lows across dates.

{
  "type": "trendline",
  "point1": {"time": "2024-10-16T00:00:00Z", "price": 145.2},
  "point2": {"time": "2024-12-20T00:00:00Z", "price": 138.7},
  "label": "Descending trend"
}

marker — single-bar event

Use for a callout at one specific date: earnings beat, entry signal, news event, grade change.

{
  "type": "marker",
  "time": "2024-11-14T00:00:00Z",
  "shape": "arrowUp",
  "position": "belowBar",
  "text": "Earnings beat"
}

shape options: arrowUp, arrowDown, circle, square. position options: aboveBar, belowBar, inBar.

vertical_line — a moment in time

Use to mark a single date across the whole chart: an earnings date, a split, an FOMC meeting, the start of a move.

{
  "type": "vertical_line",
  "time": "2024-11-14T00:00:00Z",
  "label": "Earnings",
  "style": "dashed"
}

rectangle — a zone

Use for supply/demand zones, consolidation ranges, or any box over a region of the chart. point1 and point2 are two opposite corners (the fill is translucent so candles stay visible).

{
  "type": "rectangle",
  "point1": {"time": "2024-10-16T00:00:00Z", "price": 150.0},
  "point2": {"time": "2024-11-20T00:00:00Z", "price": 140.0},
  "label": "Demand zone"
}

text — a free-floating label

Use for a callout that isn't tied to a marker or level. Anchored at a (time, price) point.

{
  "type": "text",
  "time": "2024-11-14T00:00:00Z",
  "price": 205.0,
  "text": "Breakout"
}

event — news/event badge with detail

Use when a callout needs more than a one-line label: an earnings report, an acquisition, an analyst upgrade, a product launch. Anchored at a (time, price) point, it shows a short title badge on the chart; the detail (a few sentences) is revealed on hover (desktop) or tap (mobile). Prefer this over marker/text when you want to explain why the event matters.

{
  "type": "event",
  "time": "2024-11-14T00:00:00Z",
  "price": 205.0,
  "title": "Q3 earnings beat",
  "detail": "Reported EPS of $1.40 vs $1.25 consensus and raised full-year guidance ~5%. Shares gapped up the next session on the print and the brighter outlook."
}

fib_retracement — Fibonacci levels

Use to map retracement targets of a move. Pass the two ends of the swing (e.g. swing low → swing high); standard levels (0, 0.236, 0.382, 0.5, 0.618, 0.786, 1.0) are drawn between them automatically.

{
  "type": "fib_retracement",
  "point1": {"time": "2024-10-16T00:00:00Z", "price": 100.0},
  "point2": {"time": "2024-12-20T00:00:00Z", "price": 200.0},
  "label": "Oct–Dec move"
}

Managing annotations

manage_chart_annotations covers list / remove / clear_all:

# See what's there
manage_chart_annotations(symbol="NVDA", action="list")

# Remove specific ones (get ids from `list`)
manage_chart_annotations(symbol="NVDA", action="remove", ids=["ann_ab12..."])

# Wipe everything for the symbol
manage_chart_annotations(symbol="NVDA", action="clear_all")

• remove requires a non-empty ids list. The tool will reject an empty call.
• clear_all must not be given ids. Use remove for partial deletion.
• Existing chart primitives the user set up themselves (52W high, analyst target lines, earnings markers) are not managed by this skill and are never touched by clear_all.

Tips

• Short labels. Chart space is tight — aim for a few words ("Resistance 205", "Entry", not "Strong resistance level we should watch"). Put the reasoning in the chat message, not the label.
• One annotation per tool call. If you want three levels, call draw_chart_annotation three times.
• Clean up stale work. If you drew provisional levels and the conversation moved on, offer to clear_all before drawing a fresh set.
• No need to flag your drawings. Agent-drawn items render with a subtle dashed style, so the user can already tell them apart from their own — you don't have to call out which annotations you added.

IB Deck Checker

Perform comprehensive QC on investment banking presentations across four dimensions.

Prerequisites

Extract presentation content before checking:

python -m markitdown presentation.pptx > content.md

For visual inspection, convert to images using the pptx skill workflow.

Check Workflow

1. Number Consistency

Extract numbers with slide references:

python scripts/extract_numbers.py content.md --check

Verify:

• Key metrics match across all slides (revenue, EBITDA, multiples)
• Calculations are correct (totals, percentages, growth rates)
• Units consistent (same scale used: millions vs billions, % vs bps)
• Unit formatting consistent (e.g., $M vs $MM, $B vs $Bn - pick one style throughout)
• Time periods aligned (FY vs LTM vs quarterly)

Flag pattern:

ISSUE: Revenue mismatch
- $500M on Slides 3, 8
- $485M on Slide 15 (DCF input)
ACTION: Reconcile figures

2. Data-Narrative Alignment

Map claims to supporting data:

• Trend statements → chart directions
• Market position claims → revenue/share data
• Factual assertions → verify accuracy

Flag contradictions:

ISSUE: Narrative contradicts data
- Slide 4: "declining margins"
- Slide 7 chart: margins 18% → 22%
ACTION: Update narrative or verify data

Check plausibility (e.g., "#1 player in $100B market" with $200M revenue = 0.2% share).

3. Language Polish

Scan for:

• Casual phrasing ("pretty good", "a lot of")
• Vague quantifiers without specifics
• Contractions, exclamation points
• Inconsistent terminology

See references/ib-terminology.md for replacement patterns.

Flag pattern:

ISSUE: Casual language (Slide 12)
- "This deal is a no-brainer"
→ "The transaction presents a compelling value proposition"

4. Formatting QC

Audit each slide for:

• Charts: Source citations, axis labels, legends
• Typography: Consistent fonts, size hierarchy
• Numbers: Consistent formatting (1,000 vs 1K)
• Dates: Consistent format throughout
• Footnotes: Proper sourcing and disclaimers

Output

Present findings using the template in references/report-format.md.

Categorize by severity:

• Critical: Number mismatches, factual errors
• Important: Language, narrative alignment
• Minor: Formatting inconsistencies

Model Checker

description: Debug and audit financial models for errors — circular references, broken formulas, hardcoded overrides, balance sheet imbalances, cash flow mismatches, and logic gaps. Use when a model isn't tying, producing unexpected results, or before sending to a client or IC. Triggers on "debug model", "model check", "audit model", "model won't balance", "something's off in my model", "check my model", "QA model", or "model review".

Workflow

Step 1: Ingest the Model

• Accept the user's Excel model (.xlsx or .xlsm) from $WORK_DIR/work/{task}/
• Identify model type: DCF, LBO, merger, 3-statement, comps, returns, or custom
• Map the structure: which tabs exist, how they're linked, where inputs vs. outputs live

Step 2: Structural Checks

Tab & Layout Review:

• Are inputs clearly separated from calculations?
• Is there a consistent color-coding convention? (blue = input, black = formula, green = link)
• Are there hidden tabs or rows that could contain overrides?
• Is the model flow logical? (assumptions → IS → BS → CF → valuation)

Formula Consistency:

• Check for hardcoded numbers inside formulas (partial hardcodes)
• Check for inconsistent formulas across row/column ranges (should be the same formula dragged across)
• Identify any #REF!, #VALUE!, #N/A, #DIV/0! errors
• Flag cells that are formatted as formulas but contain hardcoded values

Step 3: Integrity Checks

Balance Sheet:

• Total Assets = Total Liabilities + Equity (every period)
• If imbalanced, quantify the gap and trace where it breaks
• Check that retained earnings rolls forward correctly: Prior RE + Net Income - Dividends = Current RE
• Verify goodwill and intangibles flow from acquisition assumptions (if M&A model)

Cash Flow Statement:

• Ending cash from CF = Cash on BS (every period)
• Operating CF + Investing CF + Financing CF = Change in Cash
• D&A on CF matches D&A on IS
• Capex on CF matches PP&E rollforward on BS
• Working capital changes on CF match BS movements (AR, AP, inventory)

Income Statement:

• Revenue builds tie to segment/product detail
• COGS and gross margin are consistent with assumptions
• Tax expense = Pre-tax income × tax rate (check for deferred tax adjustments)
• Share count ties to dilution schedule (options, converts, buybacks)

Circular References:

• Check for circular references (interest expense → debt balance → cash → interest)
• If intentional (common in LBO/3-statement models), verify the iteration toggle works
• If unintentional, trace the loop and suggest how to break it

Step 4: Logic Checks

Reasonableness:

• Do growth rates make sense? (100%+ revenue growth without explanation = red flag)
• Are margins within industry norms? Flag outliers
• Does terminal value dominate the DCF? (>75% of EV from TV is a yellow flag)
• Are projections hockey-sticking unrealistically?
• Does EBITDA growth compound to an absurd number by Year 10?

Sensitivity & Edge Cases:

• What happens at 0% growth? Negative growth?
• Does the model break with negative EBITDA?
• Do leverage ratios go negative or exceed realistic bounds?
• Are there any divide-by-zero risks?

Cross-Tab Consistency:

• Do linked cells actually match their source? (copy-paste errors are common)
• Are date headers consistent across all tabs?
• Do units match (thousands vs. millions vs. actuals)?

Step 5: Common Bugs by Model Type

DCF:

• Discount rate applied to wrong period (mid-year vs. end-of-year convention)
• Terminal value not discounted back correctly
• WACC uses book values instead of market values
• FCF includes interest expense (should be unlevered)
• Tax shield double-counted

LBO:

• Debt paydown doesn't match cash sweep mechanics
• PIK interest not accruing to debt balance
• Management rollover not reflected in returns
• Exit multiple applied to wrong EBITDA (LTM vs. NTM)
• Fees and expenses not deducted from Day 1 equity

Merger Model:

• Accretion/dilution uses wrong share count (pre- vs. post-deal)
• Synergies not phased in correctly
• Purchase price allocation doesn't balance
• Foregone interest on cash not included
• Transaction fees not in sources & uses

3-Statement:

• Working capital changes have wrong sign convention
• Depreciation doesn't match PP&E schedule
• Debt maturity schedule doesn't match principal payments
• Dividends paid exceed net income without explanation

Step 6: Report

Generate a model audit report:

Summary:

• Model type and overall assessment (Clean / Minor Issues / Major Issues)
• Number of issues found by severity

Issue Log:

Severity Definitions:

• Critical: Model produces wrong output (BS doesn't balance, formulas broken)
• Warning: Model works but has risks (hardcodes, inconsistent formulas, edge case failures)
• Info: Style and best practice suggestions (color coding, layout, naming)

Step 7: Output

Save all deliverables to $WORK_DIR/work/{task}/.

• Issue log table (in chat or Excel)
• Annotated model with comments on flagged cells (if user provides the file)
• Summary assessment with fix priority

Important Notes

• Always check the BS balance first — if it doesn't balance, nothing else matters until it does
• Hardcoded overrides are the #1 source of model errors — search aggressively for them
• Sign convention errors (positive vs. negative for cash outflows) are extremely common
• Models that "work" can still be wrong — sanity-check outputs against industry benchmarks
• If the model uses VBA macros, note any macro-driven calculations that can't be audited from formulas alone
• Don't change the model without asking — report issues and let the user decide how to fix
• Save all output files to $WORK_DIR/work/{task}/

For Excel formatting standards and recalculation, see .agents/skills/xlsx/SKILL.md.

Competitive Landscape Mapping

CRITICAL STANDARDS - APPLY TO EVERY ANALYSIS

Source File Primacy

When source files (Excel/CSV) are provided:

• Extract values DIRECTLY — Do not perform your own calculations; use the numbers as they appear
• Maintain consistency — When the same metric appears in multiple places, ensure identical values throughout
• Verify calculations — If the prompt asks you to calculate something, verify your result matches related data in the source
• Round only as shown — Use the same decimal precision as the source file

Prompt Fidelity

When the prompt specifies exact requirements, follow them verbatim:

Slide Titles & Section Names:

• If prompt says "Overview and Competitive Scope" slide — use EXACTLY that title, not a paraphrase
• If prompt says within the "Segment Mix" section — use EXACTLY "Segment Mix" as the section header
• Never substitute with creative alternatives (e.g., don't use "FY2024 Segment Contribution Analysis" when "Overview and Competitive Scope" was specified)

Chart vs Table:

• If prompt says embedded chart — create an actual PowerPoint chart object, NOT a table
• If prompt says data labels must display — these go on chart elements (bars, slices, lines), not table cells
• Tables and charts are NOT interchangeable — use exactly what's specified

Complete Data Series:

• If prompt lists 7 competitors, include ALL 7 — not 5 or 6
• If prompt shows data for years 2015-2025, include ALL years — not a subset
• If prompt specifies 6 series in a chart (Uber, Lyft, DiDi, Bolt, Grab, Gojek), include ALL 6 — not 4

Exact Values & Phrasing:

• If prompt says Revenue: $43.98B (+18% YoY) — display exactly that format
• If prompt says surpasses DoorDash 4:1, Lyft 8:1 — use those exact ratios, not "7.6x Lyft"
• If prompt gives specific percentages (e.g., "Uber 30%, DiDi 35%"), use exactly those numbers

When in doubt: Re-read the prompt. If it specifies something explicitly, that's not a suggestion — it's a requirement.

Reference Files

This skill includes reference files in the references/ folder. Use them as follows:

• references/schemas.md — Table templates for M&A transactions, scenario analysis, and slide structure. Reference when building financial tables or investment scenarios.
• references/frameworks.md — 2x2 matrix axis pairs by industry. Reference when choosing positioning visualization dimensions.

Source Quality Hierarchy

When sources conflict, prioritize in this order:

1. 10-Ks / Annual Reports — Audited, highest reliability
2. Earnings Calls / Investor Presentations — Management commentary, forward guidance
3. Sell-Side Research — Analyst estimates, useful for private company sizing
4. Industry Reports (McKinsey, Gartner, etc.) — Market sizing, trends
5. News Articles — Use only for recent developments, verify against primary sources

Data Comparability

• Time periods must match — All competitor metrics from same fiscal year. Flag exceptions: "(FY24)" vs "(H1 2024)"
• Metric definitions must match — Same calculation methodology across competitors
• Currency normalization — Convert all figures to USD for international comparisons; note exchange rate and date used
• Use "-" for missing data — Never leave cells blank; for private companies, use "N/A" or estimates with "[E]" flag
• Cite every number — Format: "[Company] [Document] ([Date])"
• Source file fidelity — When Excel/CSV files are provided, use values exactly as given; do not recalculate or round differently than shown

Design & Formatting

• Slide titles = insights — "Scale leaders pulling away from niche players" not "Competitive Analysis"
• Slide titles must fit — One or two lines fine, but no overflow; reduce font size if needed (min 24pt)
• Signposts = quantified — "margin below 40%" not "margins decline"
• Ratings include actuals — "●●● $160B" not just "●●●"
• Slide numbers required — Every slide must have a page number

Presentation-Specific Requirements

• Actual embedded charts required — Pie charts, bar charts, and line graphs must be real PowerPoint chart objects (created via pptx skill), NOT text/ASCII representations
• Match prompt structure — If prompt specifies slide structure, follow it
• Competitor tables — For comprehensive analysis: metrics table + qualitative table per competitor. For rapid assessments: single combined table is acceptable.
• Segment financials — Include both Revenue AND EBITDA when available. For private competitors or limited disclosure, revenue-only tables are acceptable; note "[EBITDA not disclosed]"

Visual Reference

Match professional presentation quality:

Spacing & Overflow Prevention:

• Title-to-content gap — Minimum 0.4" between slide title bottom and first content element
• Section header gaps — Minimum 0.25" between section headers and content below
• Element buffers — Minimum 0.2" between any two elements (tables, text boxes, charts)
• Margin safety — Keep all content at least 0.5" from slide edges
• Text overflow — If text doesn't fit, reduce font size or split across slides; never let text clip or overlap

Slide Titles:

• Must fit within slide width — One or two lines is fine, but text must not overflow or clip
• If title is too long — Shorten wording or reduce font size (minimum 24pt)
• Front-load the insight — Put the key point first, details second

Chart Formatting:

• Legend inside layout — Always set include_in_layout=True so legends don't overlap chart area
• Legend position — Use RIGHT for pie charts (≤6 items), BOTTOM for line/bar charts (≤4 series)
• Too many series — If >6 series, consider splitting into multiple charts or using a table instead
• Data labels — For pie charts, show percentages on slices rather than relying solely on legend

Typography (set explicitly, never use defaults):

• Slide title: 28-32pt bold
• Section headers: 16-20pt bold
• Body text: 11-14pt regular
• Table text: 10-12pt regular
• Sources/footnotes: 8-10pt, gray
• Consistency rule: Same element type = same font size throughout deck

Layout:

• Clean grid alignment — tables and text blocks align to consistent margins
• Generous whitespace — don't crowd slides; let content breathe
• Visual hierarchy — most important insight is largest/most prominent
• One key message per slide — supporting detail below

Color:

• Limited palette — 2-3 colors max (one accent color for emphasis)
• Muted tones — avoid bright/saturated colors; use navy, gray, muted blue
• Consistent application — same color meanings throughout (e.g., accent for key metrics)

Tables:

• Light gray header row with bold text
• Alternating row shading (subtle) or clean white with thin borders
• Right-align numbers, left-align text
• Adequate cell padding — text shouldn't touch borders

Rating visuals:

• ●●● / ●●○ / ●○○ system with actual metric alongside
• Consistent placement in comparative tables

Adapt structure and metrics to fit your industry — but maintain this level of polish.

What's STRICT vs FLEXIBLE

WORKFLOW PHASES

Phase 1: Clarify Requirements

Before starting, confirm:

• Scope: Single company deep-dive or multi-company comparison?
• Output: Presentation or written memo?
• Focus areas: Specific competitors, dimensions, or strategic questions?
• Investment context: Need scenarios/signposts?
• Source files: What data files are provided and what values should be extracted?

Phase 2: Research → Outline → Review → Create

Do NOT create final output until outline is reviewed.

The 10-step Analysis Workflow below (Steps 0-9) is executed during Phase 2. Complete research and outlining before creating final slides or documents.

ANALYSIS WORKFLOW

Step 0: Identify Industry-Defining Metrics

Before diving into analysis, identify 3-5 metrics that matter most for this industry:

For industries not listed, identify the 3-5 metrics that investors and operators use to benchmark performance.

Use these metrics consistently across all competitor comparisons.

Step 1: Market Context

• Market size (current and projected) with source
• Growth drivers and headwinds
• Key trends reshaping the industry

CORRECT: "The embedded payments market is $80-100B in 2024, growing at 20-25% CAGR (McKinsey 2024)" WRONG: "The market is large and growing rapidly"

Step 2: Industry Economics

Map value flows. Approach varies by industry type:

• Vertically-structured — Value chain layers with typical margin at each
• Platform/network — Ecosystem participants and value flows between them
• Fragmented — Consolidation dynamics and margin differences by scale

Step 3: Target Company Profile

| Metric | Value |
|--------|-------|
| Revenue | $4.96B |
| Growth | +26% YoY |
| Gross Margin | 45% |
| Profitability | $373M Adj. EBITDA |
| Customers | 134K |
| Retention | 92% |
| Market Share | ~15% |

For multi-segment companies, add segment breakdown:

| Segment | Revenue | Rev YoY | Rev % | EBITDA | EBITDA YoY | Margin |
|---------|---------|---------|-------|--------|------------|--------|
| Seg A   | $25.1B  | +26%    | 57%   | $6.5B  | +31%       | 26%    |
| Seg B   | $13.8B  | +31%    | 31%   | $2.5B  | +64%       | 18%    |
| Seg C   | $5.1B   | -2%     | 12%   | -$74M  | -16%       | -1%    |
| Total   | $44.0B  | +18%    | 100%  | $6.5B* | -          | 15%    |

*Note corporate costs if applicable

Step 4: Competitor Mapping

Group competitors using the framework that fits:

• By business model — Platform vs. vertical vs. horizontal
• By segment — Enterprise vs. SMB vs. consumer
• By posture — Direct vs. adjacent vs. emerging
• By origin — Incumbents vs. disruptors vs. new entrants

Step 5: Positioning Visualization

Step 6: Competitor Deep Dives

Table 1 — Metrics:

| Metric | Value |
|--------|-------|
| Revenue | $X.XB |
| Growth | +XX% YoY |
| Gross Margin | XX% |
| Market Cap | $X.XB |
| Profitability | $XXXM EBITDA |
| Customers | XXK |
| Retention | XX% |
| Market Share | ~XX% |

Table 2 — Qualitative:

| Category | Assessment |
|----------|------------|
| Business | What they do (1 sentence) |
| Strengths | 2-3 bullets |
| Weaknesses | 2-3 bullets |
| Strategy | Current priorities |

Step 7: Comparative Analysis

| Dimension | Company A | Company B | Company C |
|-----------|-----------|-----------|-----------|
| Scale | ●●● $160B | ●●○ $45B | ●○○ $8B |
| Growth | ●●○ +26% | ●●● +35% | ●●○ +22% |
| Margins | ●●○ 7.5% | ●○○ 3.2% | ●●● 15% |

Step 8: Strategic Context

• M&A transactions (multiples, strategic rationale)
• Partnership and integration trends
• Capital raising patterns
• Regulatory developments

Step 9: Synthesis

Competitive Moat Assessment: Evaluate each competitor's durable advantages using these categories:

Rate each as Strong / Moderate / Weak with supporting evidence.

Required Synthesis Elements:

• Durable advantages (hard to replicate) — map to moat categories above
• Structural vulnerabilities (hard to fix)
• Current state vs. trajectory

For investment contexts:

| Scenario | Probability | Key Driver |
|----------|-------------|------------|
| Bull | 30% | Market share gains, margin expansion |
| Base | 50% | Current trajectory continues |
| Bear | 20% | Competitive pressure, margin compression |

QUALITY CHECKLIST

Before finalizing, verify:

Prompt Fidelity:

• ✅ Slide titles match prompt exactly (not paraphrased)
• ✅ Section names match prompt exactly
• ✅ Charts used where prompt says "chart"; tables where prompt says "table"
• ✅ All competitors/data points included (if prompt lists 7, include 7)
• ✅ All years/periods included (if prompt shows 2015-2025, include all)
• ✅ Exact values and formats used as specified in prompt
• ✅ Commentary uses exact phrasing when prompt specifies it

Source File & Data Consistency:

• ✅ All values from source files extracted directly (not recalculated)
• ✅ Same metric shows identical value across all slides
• ✅ Calculated percentages match source data or related figures
• ✅ Numbers use same decimal precision as source

Layout & Spacing:

• ✅ Minimum 0.4" gap between slide title and first content element
• ✅ No text or elements overlapping
• ✅ All content within 0.5" margin from slide edges
• ✅ Text fits within containers (no clipping or overflow)
• ✅ Slide titles fit within slide width (1-2 lines, no overflow)

Charts:

• ✅ Legends set to include_in_layout=True (no overlap with chart)
• ✅ Legend position appropriate (RIGHT for pie, BOTTOM for line/bar)
• ✅ No more than 6 series per chart; if more, split or use table

Typography:

• ✅ Font sizes explicitly set (not default)
• ✅ Same element type uses same font size across all slides
• ✅ Titles 28-32pt, headers 16-20pt, body 11-14pt, sources 8-10pt

Data & Sources:

• ✅ Every number has a source citation
• ✅ All competitor metrics from same fiscal period (flag exceptions)
• ✅ Same metric definitions across all competitors

Presentation Format:

• ✅ Slide titles state insights, not topics
• ✅ All slides have page numbers
• ✅ Charts are actual embedded PowerPoint objects (not ASCII/text)
• ✅ Segment tables include EBITDA where available; revenue-only acceptable for private companies

Comparable Company Analysis

Overview

This skill teaches Claude to build institutional-grade comparable company analyses that combine operating metrics, valuation multiples, and statistical benchmarking. The output is a structured Excel/spreadsheet that enables informed investment decisions through peer comparison.

ALWAYS ask yourself first:

1. "Do you have a preferred format or should I adapt the template style?"
2. "Who is the audience?" (Investment committee, board presentation, quick reference, detailed memo)
3. "What's the key question?" (Valuation, growth analysis, competitive positioning, efficiency)
4. "What's the context?" (M&A evaluation, investment decision, sector benchmarking, performance review)

Adapt based on specifics:

• Industry context: Big tech mega-caps need different metrics than emerging SaaS startups
• Sector-specific needs: Add relevant metrics early (e.g., cloud ARR, enterprise customers, developer ecosystem for tech)
• Company familiarity: Well-known companies may need less background, more focus on delta analysis
• Decision type: M&A requires different emphasis than ongoing portfolio monitoring

Core principle: Use template principles (clear structure, statistical rigor, transparent formulas) but vary execution based on context. The goal is institutional-quality analysis, not institutional-looking templates.

User-provided examples and explicit preferences always take precedence over defaults.

Core Philosophy

"Build the right structure first, then let the data tell the story."

Start with headers that force strategic thinking about what matters, input clean data, build transparent formulas, and let statistics emerge automatically. A good comp should be immediately readable by someone who didn't build it.

Section 1: Document Structure & Setup

Header Block (Rows 1-3)

Row 1: [ANALYSIS TITLE] - COMPARABLE COMPANY ANALYSIS
Row 2: [List of Companies with Tickers] • [Company 1 (TICK1)] • [Company 2 (TICK2)] • [Company 3 (TICK3)]
Row 3: As of [Period] | All figures in [USD Millions/Billions] except per-share amounts and ratios

Why this matters: Establishes context immediately. Anyone opening this file knows what they're looking at, when it was created, and how to interpret the numbers.

Visual Convention Standards

For all Excel formatting, number formats, and color standards, follow the guidelines in .agents/skills/xlsx/SKILL.md. After generating Excel, run recalculation: python .agents/skills/xlsx/scripts/recalc.py model.xlsx 30

User-provided templates and explicit formatting preferences always override defaults.

Section 2: Operating Statistics & Financial Metrics

Core Columns (Start with these)

1. Company - Names with consistent formatting
2. Revenue - Size metric (can be LTM, quarterly, or annual depending on context)
3. Revenue Growth - Year-over-year percentage change
4. Gross Profit - Revenue minus cost of goods sold
5. Gross Margin - GP/Revenue (fundamental profitability)
6. EBITDA - Earnings before interest, tax, depreciation, amortization
7. EBITDA Margin - EBITDA/Revenue (operating efficiency)

Optional Additions (Choose based on industry/purpose)

• Quarterly vs LTM - Include both if seasonality matters
• Free Cash Flow - For capital-intensive or SaaS businesses
• FCF Margin - FCF/Revenue (cash generation efficiency)
• Net Income - For mature, profitable companies
• Operating Income - For businesses with varying D&A
• CapEx metrics - For asset-heavy industries
• Rule of 40 - Specifically for SaaS (Growth % + Margin %)
• FCF Conversion - For quality of earnings analysis (advanced)

Formula Examples (Using Row 7 as example)

// Core ratios - these are always calculated
Gross Margin (F7): =E7/C7
EBITDA Margin (H7): =G7/C7

// Optional ratios - include if relevant
FCF Margin: =[FCF]/[Revenue]
Net Margin: =[Net Income]/[Revenue]
Rule of 40: =[Growth %]+[FCF Margin %]

Golden Rule: Every ratio should be [Something] / [Revenue] or [Something] / [Something from this sheet]. Keep it simple.

Statistics Block (After company data)

CRITICAL: Add statistics formulas for all comparable metrics (ratios, margins, growth rates, multiples).

[Leave one blank row for visual separation]
- Maximum: =MAX(B7:B9)
- 75th Percentile: =QUARTILE(B7:B9,3)
- Median: =MEDIAN(B7:B9)
- 25th Percentile: =QUARTILE(B7:B9,1)
- Minimum: =MIN(B7:B9)

Columns that NEED statistics (comparable metrics):

• Revenue Growth %, Gross Margin %, EBITDA Margin %, EPS
• EV/Revenue, EV/EBITDA, P/E, Dividend Yield %, Beta

Columns that DON'T need statistics (size metrics):

• Revenue, EBITDA, Net Income (absolute size varies by company scale)
• Market Cap, Enterprise Value (not comparable across different-sized companies)

Note: Add one blank row between company data and statistics rows for visual separation. Do NOT add a "SECTOR STATISTICS" or "VALUATION STATISTICS" header row.

Why quartiles matter: They show distribution, not just average. A 75th percentile multiple tells you what "premium" companies trade at.

Section 3: Valuation Multiples & Investment Metrics

Core Valuation Columns (Start with these)

1. Company - Same order as operating section
2. Market Cap - Current market valuation
3. Enterprise Value - Market Cap ± Net Debt/Cash
4. EV/Revenue - How much market pays per dollar of sales
5. EV/EBITDA - How much market pays per dollar of earnings
6. P/E Ratio - Price relative to net earnings

Optional Valuation Metrics (Choose based on context)

• FCF Yield - FCF/Market Cap (for cash-focused analysis)
• PEG Ratio - P/E/Growth Rate (for growth companies)
• Price/Book - Market value vs. book value (for asset-heavy businesses)
• ROE/ROA - Return metrics (for profitability comparison)
• Revenue/EBITDA CAGR - Historical growth rates (for trend analysis)
• Asset Turnover - Revenue/Assets (for operational efficiency)
• Debt/Equity - Leverage (for capital structure analysis)

Key Principle: Include 3-5 core multiples that matter for your industry. Don't include every possible metric just because you can.

Formula Examples

// Core multiples - always include these
EV/Revenue: =[Enterprise Value]/[LTM Revenue]
EV/EBITDA: =[Enterprise Value]/[LTM EBITDA]
P/E Ratio: =[Market Cap]/[Net Income]

// Optional multiples - include if data available
FCF Yield: =[LTM FCF]/[Market Cap]
PEG Ratio: =[P/E]/[Growth Rate %]

Cross-Reference Rule

CRITICAL: Valuation multiples MUST reference the operating metrics section. Never input the same raw data twice. If revenue is in C7, then EV/Revenue formula should reference C7.

Statistics Block

Same structure as operating section: Max, 75th, Median, 25th, Min for every metric. Add one blank row for visual separation between company data and statistics. Do NOT add a "VALUATION STATISTICS" header row.

Section 4: Notes & Methodology Documentation

Required Components

Data Sources & Quality:

• Where did the data come from? (fundamentals MCP, get_company_overview, SEC filings)
• What period does it cover? (Q4 2024, audited figures)
• How was it verified? (Cross-checked against 10-K/10-Q)

Key Definitions:

• EBITDA calculation method (Gross Profit + D&A, or Operating Income + D&A)
• Free Cash Flow formula (Operating CF - CapEx)
• Special metrics explained (Rule of 40, FCF Conversion)
• Time period definitions (LTM, CAGR calculation periods)

Valuation Methodology:

• How was Enterprise Value calculated? (Market Cap + Net Debt)
• What growth rates were used? (Historical CAGR, forward estimates)
• Any adjustments made? (One-time items excluded, normalized margins)

Analysis Framework:

• What's the investment thesis? (Cloud/SaaS efficiency)
• What metrics matter most? (Cash generation, capital efficiency)
• How should readers interpret the statistics? (Quartiles provide context)

Section 5: Choosing the Right Metrics (Decision Framework)

Start with "What question am I answering?"

"Which company is undervalued?" → Focus on: EV/Revenue, EV/EBITDA, P/E, Market Cap → Skip: Operational details, growth metrics

"Which company is most efficient?" → Focus on: Gross Margin, EBITDA Margin, FCF Margin, Asset Turnover → Skip: Size metrics, absolute dollar amounts

"Which company is growing fastest?" → Focus on: Revenue Growth %, EBITDA CAGR, User/Customer Growth → Skip: Margin metrics, leverage ratios

"Which is the best cash generator?" → Focus on: FCF, FCF Margin, FCF Conversion, CapEx intensity → Skip: EBITDA, P/E ratios

Industry-Specific Metric Selection

Software/SaaS: Must have: Revenue Growth, Gross Margin, Rule of 40 Optional: ARR, Net Dollar Retention, CAC Payback Skip: Asset Turnover, Inventory metrics

Manufacturing/Industrials: Must have: EBITDA Margin, Asset Turnover, CapEx/Revenue Optional: ROA, Inventory Turns, Backlog Skip: Rule of 40, SaaS metrics

Financial Services: Must have: ROE, ROA, Efficiency Ratio, P/E Optional: Net Interest Margin, Loan Loss Reserves Skip: Gross Margin, EBITDA (not meaningful for banks)

Retail/E-commerce: Must have: Revenue Growth, Gross Margin, Inventory Turnover Optional: Same-Store Sales, Customer Acquisition Cost Skip: Heavy R&D or CapEx metrics

The "5-10 Rule"

5 operating metrics - Revenue, Growth, 2-3 margins/efficiency metrics 5 valuation metrics - Market Cap, EV, 3 multiples = 10 total columns - Enough to tell the story, not so many you lose the thread

If you have more than 15 metrics, you're probably including noise. Edit ruthlessly.

Section 6: Best Practices & Quality Checks

Before You Start

1. Define the peer group - Companies must be truly comparable (similar business model, scale, geography)
2. Choose the right period - LTM smooths seasonality; quarterly shows trends
3. Standardize units upfront - Millions vs. billions decision affects everything
4. Map data sources - Know where each number comes from

As You Build

1. Input all raw data first - Complete the blue text before writing formulas

2. Add cell comments to ALL hard-coded inputs - Right-click cell → Insert Comment → Document source OR assumption

   For sourced data, cite exactly where it came from:

   • Example: "fundamentals MCP get_financial_statements(MSFT, 'all', 'annual', 5), accessed 2024-10-02"
   • Example: "Q4 2024 10-K filing, page 42, line item 'Total Revenue'"
   • Example: "get_company_overview(MSFT) — analyst consensus as of 2024-10-02"
   • Include hyperlinks when possible: Right-click cell → Link → paste URL to SEC filing, data source, or report

   For assumptions, explain the reasoning:

   • Example: "Assumed 15% EBITDA margin based on peer median, company does not disclose"
   • Example: "Estimated Enterprise Value as Market Cap + $50M net debt (from Q3 balance sheet, Q4 not yet available)"
   • Example: "Forward P/E based on street consensus EPS of $3.45 (average of 12 analyst estimates)"

   Why this matters: Enables audit trails, data verification, assumption transparency, and future updates

3. Build formulas row by row - Test each calculation before moving on

4. Use absolute references for headers - $C$6 locks the header row

5. Format consistently - Percentages as percentages, not decimals

6. Add conditional formatting - Highlight outliers automatically

Sanity Checks

• Margin test: Gross margin > EBITDA margin > Net margin (always true by definition)
• Multiple reasonableness:
  • EV/Revenue: typically 0.5-20x (varies widely by industry)
  • EV/EBITDA: typically 8-25x (fairly consistent across industries)
  • P/E: typically 10-50x (depends on growth rate)
• Growth-multiple correlation: Higher growth usually means higher multiples
• Size-efficiency trade-off: Larger companies often have better margins (scale benefits)

Common Mistakes to Avoid

❌ Mixing market cap and enterprise value in formulas ❌ Using different time periods for numerator and denominator (LTM vs quarterly) ❌ Hardcoding numbers into formulas instead of cell references ❌ Hard-coded inputs without cell comments citing the source OR explaining the assumption ❌ Missing hyperlinks to SEC filings or data sources when available ❌ Including too many metrics without clear purpose ❌ Including non-comparable companies (different business models) ❌ Using outdated data without disclosure ❌ Calculating averages of percentages incorrectly (should be median)

Section 6: Advanced Features

Dynamic Headers

For columns showing calculations, use clear unit labels:

Revenue Growth (YoY) % | EBITDA Margin | FCF Margin | Rule of 40

Quartile Analysis Benefits

Instead of just mean/median, quartiles show:

• 75th percentile = "Premium" companies trade here
• Median = Typical market valuation
• 25th percentile = "Discount" territory

This helps answer: "Is our target company trading rich or cheap vs. peers?"

Industry-Specific Modifications

Software/SaaS:

• Add: ARR, Net Dollar Retention, CAC Payback Period
• Emphasize: Rule of 40, FCF margins, gross margins >70%

Healthcare:

• Add: R&D/Revenue, Pipeline value, Regulatory status
• Emphasize: EBITDA margins, growth rates, reimbursement risk

Industrials:

• Add: Backlog, Order book trends, Geographic mix
• Emphasize: ROIC, asset turnover, cyclical adjustments

Consumer:

• Add: Same-store sales, Customer acquisition cost, Brand value
• Emphasize: Revenue growth, gross margins, inventory turns

Section 7: Workflow & Practical Tips

Step-by-Step Process

1. Set up structure (30 minutes)

   • Create all headers
   • Format cells (blue for inputs, black for formulas)
   • Lock in units and date references

2. Gather data

   • Use fundamentals MCP: get_financial_statements(symbol, 'all', 'annual', 5) for income statement, balance sheet, cash flow
   • Use fundamentals MCP: get_financial_ratios(symbol) for pre-computed ratios and margins
   • Use fundamentals MCP: get_growth_metrics(symbol) for historical growth rates
   • Use get_company_overview tool for market cap, analyst consensus, price targets, rating distribution
   • Use fundamentals MCP: get_historical_valuation(symbol) for valuation multiples
   • Input all raw numbers in blue
   • Document sources in notes section

3. Build formulas (30 minutes)

   • Start with simple ratios (margins)
   • Progress to multiples (EV/Revenue)
   • Add cross-checks (do margins make sense?)

4. Add statistics (15 minutes)

   • Copy formula structure for all columns
   • Verify ranges are correct (B7:B9, not B7:B10)
   • Check quartile logic

5. Quality control (30 minutes)

   • Run sanity checks
   • Verify formula references
   • Check for #DIV/0! or #REF! errors
   • Compare against known benchmarks

6. Documentation (15 minutes)

   • Complete notes section
   • Add data sources
   • Define methodologies
   • Date-stamp the analysis

Pro Tips

• Save templates: Build once, reuse forever
• Color-code outliers: Conditional formatting for values >2 standard deviations
• Link to source files: Hyperlink to SEC filings or data source references
• Version control: Save as "Comps_v1_2024-12-15" with clear dating
• Collaborative reviews: Have someone else check your formulas

Excel Formatting Checklist

For all Excel formatting, number formats, and color standards, follow the guidelines in .agents/skills/xlsx/SKILL.md. After generating Excel, run recalculation: python .agents/skills/xlsx/scripts/recalc.py model.xlsx 30

• One blank row for separation between company data and statistics rows
• No separate "SECTOR STATISTICS" or "VALUATION STATISTICS" header rows
• Every hard-coded input cell has a comment with either: (1) exact data source, OR (2) assumption explanation
• Hyperlinks added to cells where applicable (SEC filings, data source references)

Section 8: Example Template Layout

Simple Version (Start here):

┌─────────────────────────────────────────────────────────────┐
│ TECHNOLOGY - COMPARABLE COMPANY ANALYSIS                    │
│ Microsoft • Alphabet • Amazon                               │
│ As of Q4 2024 | All figures in USD Millions                │
├─────────────────────────────────────────────────────────────┤
│ OPERATING METRICS                                           │
├──────────┬─────────┬─────────┬──────────┬──────────────────┤
│ Company  │ Revenue │ Growth  │ Gross    │ EBITDA  │ EBITDA │
│          │ (LTM)   │ (YoY)   │ Margin   │ (LTM)   │ Margin │
├──────────┼─────────┼─────────┼──────────┼─────────┼────────┤
│ MSFT     │ 261,400 │ 12.3%   │ 68.7%    │ 205,100 │ 78.4%  │
│ GOOGL    │ 349,800 │ 11.8%   │ 57.9%    │ 239,300 │ 68.4%  │
│ AMZN     │ 638,100 │ 10.5%   │ 47.3%    │ 152,600 │ 23.9%  │
│          │         │         │          │         │        │ [blank row]
│ Median   │ =MEDIAN │ =MEDIAN │ =MEDIAN  │ =MEDIAN │=MEDIAN │
│ 75th %   │ =QUART  │ =QUART  │ =QUART   │ =QUART  │=QUART  │
│ 25th %   │ =QUART  │ =QUART  │ =QUART   │ =QUART  │=QUART  │
├─────────────────────────────────────────────────────────────┤
│ VALUATION MULTIPLES                                         │
├──────────┬──────────┬──────────┬──────────┬────────────────┤
│ Company  │ Mkt Cap  │ EV       │ EV/Rev   │ EV/EBITDA │ P/E│
├──────────┼──────────┼──────────┼──────────┼───────────┼────┤
│ MSFT     │3,550,000 │3,530,000 │ 13.5x    │ 17.2x     │36.0│
│ GOOGL    │2,030,000 │1,960,000 │  5.6x    │  8.2x     │24.5│
│ AMZN     │2,226,000 │2,320,000 │  3.6x    │ 15.2x     │58.3│
│          │          │          │          │           │    │ [blank row]
│ Median   │ =MEDIAN  │ =MEDIAN  │ =MEDIAN  │ =MEDIAN   │=MED│
│ 75th %   │ =QUART   │ =QUART   │ =QUART   │ =QUART    │=QRT│
│ 25th %   │ =QUART   │ =QUART   │ =QUART   │ =QUART    │=QRT│
└──────────┴──────────┴──────────┴──────────┴───────────┴────┘

Add complexity only when needed:

• Include quarterly AND LTM if seasonality matters
• Add FCF metrics if cash generation is key story
• Include industry-specific metrics (Rule of 40 for SaaS, etc.)
• Add more statistics rows if you have >5 companies

Section 9: Industry-Specific Additions (Optional)

Only add these if they're critical to your analysis. Most comps work fine with just core metrics.

Software/SaaS: Add if relevant: ARR, Net Dollar Retention, Rule of 40

Financial Services: Add if relevant: ROE, Net Interest Margin, Efficiency Ratio

E-commerce: Add if relevant: GMV, Take Rate, Active Buyers

Healthcare: Add if relevant: R&D/Revenue, Pipeline Value, Patent Timeline

Manufacturing: Add if relevant: Asset Turnover, Inventory Turns, Backlog

Section 10: Red Flags & Warning Signs

Data Quality Issues

🚩 Inconsistent time periods (mixing quarterly and annual)
🚩 Missing data without explanation
🚩 Significant differences between data sources (>10% variance)

Valuation Red Flags

🚩 Negative EBITDA companies being valued on EBITDA multiples (use revenue multiples instead)
🚩 P/E ratios >100x without hypergrowth story
🚩 Margins that don't make sense for the industry

Comparability Issues

🚩 Different fiscal year ends (causes timing problems)
🚩ixing pure-play and conglomerates
🚩 Materially different business models labeled as "comps"

When in doubt, exclude the company. Better to have 3 perfect comps than 6 questionable ones.

Section 11: Formulas Reference Guide

Essential Excel Formulas

// Statistical Functions
=AVERAGE(range)          // Simple mean
=MEDIAN(range)           // Middle value
=QUARTILE(range, 1)      // 25th percentile
=QUARTILE(range, 3)      // 75th percentile
=MAX(range)              // Maximum value
=MIN(range)              // Minimum value
=STDEV.P(range)          // Standard deviation

// Financial Calculations
=B7/C7                   // Simple ratio (Margin)
=SUM(B7:B9)/3            // Average of multiple companies
=IF(B7>0, C7/B7, "N/A")  // Conditional calculation
=IFERROR(C7/D7, 0)       // Handle divide by zero

// Cross-Sheet References
='Sheet1'!B7             // Reference another sheet
=VLOOKUP(A7, Table1, 2)  // Lookup from data table
=INDEX(MATCH())          // Advanced lookup

// Formatting
=TEXT(B7, "0.0%")        // Format as percentage
=TEXT(C7, "#,##0")       // Thousands separator

Common Ratio Formulas

Gross Margin = Gross Profit / Revenue
EBITDA Margin = EBITDA / Revenue
FCF Margin = Free Cash Flow / Revenue
FCF Conversion = FCF / Operating Cash Flow
ROE = Net Income / Shareholders' Equity
ROA = Net Income / Total Assets
Asset Turnover = Revenue / Total Assets
Debt/Equity = Total Debt / Shareholders' Equity

Key Principles Summary

1. Structure drives insight - Right headers force right thinking
2. Less is more - 5-10 metrics that matter beat 20 that don't
3. Choose metrics for your question - Valuation analysis ≠ efficiency analysis
4. Statistics show patterns - Median/quartiles reveal more than average
5. Transparency beats complexity - Simple formulas everyone understands
6. Comparability is king - Better to exclude than force a bad comp
7. Document your choices - Explain which metrics and why in notes section

Output Checklist

Before delivering a comp analysis, verify:

• All companies are truly comparable
• Data is from consistent time periods
• Units are clearly labeled (millions/billions)
• Formulas reference cells, not hardcoded values
• All hard-coded input cells have comments with either: (1) exact data source with citation, OR (2) clear assumption with explanation
• Hyperlinks added where relevant (SEC EDGAR filings, research reports)
• Statistics include at least 5 metrics (Max, 75th, Med, 25th, Min)
• Notes section documents sources and methodology
• Visual formatting follows conventions (blue = input, black = formula)
• Sanity checks pass (margins logical, multiples reasonable)
• Date stamp is current ("As of [Date]")
• Formula auditing shows no errors (#DIV/0!, #REF!, #N/A)

Continuous Improvement

After completing a comp analysis, ask:

1. Did the statistics reveal unexpected insights?
2. Were there any data gaps that limited analysis?
3. Did stakeholders ask for metrics you didn't include?
4. How long did it take vs. how long should it take?
5. What would make this more useful next time?

The best comp analyses evolve with each iteration. Save templates, learn from feedback, and refine the structure based on what decision-makers actually use.

DCF Model Builder

Overview

This skill creates institutional-quality DCF models for equity valuation following investment banking standards. Each analysis produces a detailed Excel model (with sensitivity analysis included at the bottom of the DCF sheet).

Tools

• fundamentals MCP: get_financial_statements, get_financial_ratios, get_growth_metrics, get_historical_valuation
• macro MCP: get_treasury_rates, get_market_risk_premium
• get_company_overview tool: analyst consensus, growth estimates, company profile
• User-provided data and web search/fetch as supplements

Critical Constraints - Read These First

These constraints apply throughout all DCF model building. Review before starting:

Sensitivity Tables:

• Populate ALL 75 cells (3 tables × 25 cells) with full DCF recalculation formulas
• Use openpyxl loops to write formulas programmatically
• NO placeholder text, NO linear approximations, NO manual steps required
• Each cell must recalculate full DCF for that assumption combination

Cell Comments:

• Add cell comments AS each hardcoded value is created
• Format: "Source: [System/Document], [Date], [Reference], [URL if applicable]"
• Every blue input must have a comment before moving to next section
• Do not defer to end or write "TODO: add source"

Model Layout Planning:

• Define ALL section row positions BEFORE writing any formulas
• Write ALL headers and labels first
• Write ALL section dividers and blank rows second
• THEN write formulas using the locked row positions
• Test formulas immediately after creation

Formula Recalculation:

• Run python .agents/skills/xlsx/scripts/recalc.py model.xlsx 30 before delivery
• Fix ALL errors until status is "success"
• Zero formula errors required (#REF!, #DIV/0!, #VALUE!, etc.)

Scenario Blocks:

• Create separate blocks for Bear/Base/Bull cases
• Show assumptions horizontally across projection years within each block
• Use IF formulas: =IF($B$6=1,[Bear cell],IF($B$6=2,[Base cell],[Bull cell]))
• Verify formulas reference correct scenario block cells

DCF Process Workflow

Execution pattern: build the DCF as a saved Python script (e.g., work/<task_name>/build_dcf.py) rather than inline ExecuteCode. Model building is iterative — you will debug formulas, tweak assumptions, and rerun. Writing to a file + running via Bash lets you Edit specific sections and rerun cheaply; resubmitting the whole openpyxl block inline on every iteration is wasteful.

Step 1: Data Retrieval and Validation

Fetch data from MCP servers, user provided data, and the web.

Data Sources:

• Financial statements (income/balance/cash flow): Use fundamentals MCP: get_financial_statements(symbol, 'all', 'annual', 5)
• Ratios and metrics: Use fundamentals MCP: get_financial_ratios(symbol)
• Growth rates: Use fundamentals MCP: get_growth_metrics(symbol)
• Risk-free rate / treasury yields: Use macro MCP: get_treasury_rates() -- use the 10Y rate
• Market risk premium: Use macro MCP: get_market_risk_premium()
• Analyst growth estimates / consensus: Use get_company_overview tool -- includes analyst consensus and growth estimates
• Historical valuation data: Use fundamentals MCP: get_historical_valuation(symbol)
• User-provided data: Historical financials from their research
• Web search/fetch: Current prices, beta, debt and cash when needed

Validation Checklist:

• Verify net debt vs net cash (critical for valuation)
• Confirm diluted shares outstanding (check for recent buybacks/issuances)
• Validate historical margins are consistent with business model
• Cross-check revenue growth rates with industry benchmarks
• Verify tax rate is reasonable (typically 21-28%)

Step 2: Historical Analysis (3-5 years)

Analyze and document:

• Revenue growth trends: Calculate CAGR, identify drivers
• Margin progression: Track gross margin, EBIT margin, FCF margin
• Capital intensity: D&A and CapEx as % of revenue
• Working capital efficiency: NWC changes as % of revenue growth
• Return metrics: ROIC, ROE trends

Create summary tables showing:

Historical Metrics (LTM):
Revenue: $X million
Revenue growth: X% CAGR
Gross margin: X%
EBIT margin: X%
D&A % of revenue: X%
CapEx % of revenue: X%
FCF margin: X%

Step 3: Build Revenue Projections

Methodology:

1. Start with latest actual revenue (LTM or most recent fiscal year)
2. Apply growth rates for each projection year
3. Show both dollar amounts AND calculated growth %

Growth Rate Framework:

• Year 1-2: Higher growth reflecting near-term visibility
• Year 3-4: Gradual moderation toward industry average
• Year 5+: Approaching terminal growth rate

Formula structure:

• Revenue(Year N) = Revenue(Year N-1) × (1 + Growth Rate)
• Growth %(Year N) = Revenue(Year N) / Revenue(Year N-1) - 1

Three-scenario approach:

Bear Case: Conservative growth (e.g., 8-12%)
Base Case: Most likely scenario (e.g., 12-16%)
Bull Case: Optimistic growth (e.g., 16-20%)

Step 4: Operating Expense Modeling

Fixed/Variable Cost Analysis:

Operating expenses should model realistic operating leverage:

• Sales & Marketing: Typically 15-40% of revenue depending on business model
• Research & Development: Typically 10-30% for technology companies
• General & Administrative: Typically 8-15% of revenue, shows leverage as company scales

Key principles:

• ALL percentages based on REVENUE, not gross profit
• Model operating leverage: % should decline as revenue scales
• Maintain separate line items for S&M, R&D, G&A
• Calculate EBIT = Gross Profit - Total OpEx

Margin expansion framework:

Current State → Target State (Year 5)
Gross Margin: X% → Y% (justify based on scale, efficiency)
EBIT Margin: X% → Y% (result of revenue growth + opex leverage)

Step 5: Free Cash Flow Calculation

Build FCF in proper sequence:

EBIT
(-) Taxes (EBIT × Tax Rate)
= NOPAT (Net Operating Profit After Tax)
(+) D&A (non-cash expense, % of revenue)
(-) CapEx (% of revenue, typically 4-8%)
(-) Δ NWC (change in working capital)
= Unlevered Free Cash Flow

Working Capital Modeling:

• Calculate as % of revenue change (delta revenue)
• Typical range: -2% to +2% of revenue change
• Negative number = source of cash (working capital release)
• Positive number = use of cash (working capital build)

Maintenance vs Growth CapEx:

• Maintenance CapEx: Sustains current operations (~2-3% revenue)
• Growth CapEx: Supports expansion (additional 2-5% revenue)
• Total CapEx should align with company's growth strategy

Step 6: Cost of Capital (WACC) Research

CAPM Methodology for Cost of Equity:

Cost of Equity = Risk-Free Rate + Beta × Equity Risk Premium

Where:
- Risk-Free Rate = Current 10-Year Treasury Yield (use macro MCP: `get_treasury_rates()`)
- Beta = 5-year monthly stock beta vs market index
- Equity Risk Premium = use macro MCP: `get_market_risk_premium()` (typically 5.0-6.0%)

Cost of Debt Calculation:

After-Tax Cost of Debt = Pre-Tax Cost of Debt × (1 - Tax Rate)

Determine Pre-Tax Cost of Debt from:
- Credit rating (if available)
- Current yield on company bonds
- Interest expense / Total Debt from financials

Capital Structure Weights:

Market Value Equity = Current Stock Price × Shares Outstanding
Net Debt = Total Debt - Cash & Equivalents
Enterprise Value = Market Cap + Net Debt

Equity Weight = Market Cap / Enterprise Value
Debt Weight = Net Debt / Enterprise Value

WACC = (Cost of Equity × Equity Weight) + (After-Tax Cost of Debt × Debt Weight)

Special Cases:

• Net Cash Position: If Cash > Debt, Net Debt is NEGATIVE
  • Debt Weight may be negative
  • WACC calculation adjusts accordingly
• No Debt: WACC = Cost of Equity

Typical WACC Ranges:

• Large Cap, Stable: 7-9%
• Growth Companies: 9-12%
• High Growth/Risk: 12-15%

Step 7: Discount Rate Application (5-10 Year Forecast)

Mid-Year Convention:

• Cash flows assumed to occur mid-year
• Discount Period: 0.5, 1.5, 2.5, 3.5, 4.5, etc.
• Discount Factor = 1 / (1 + WACC)^Period

Present Value Calculation:

For each projection year:
PV of FCF = Unlevered FCF × Discount Factor

Example (Year 1):
FCF = $1,000
WACC = 10%
Period = 0.5
Discount Factor = 1 / (1.10)^0.5 = 0.9535
PV = $1,000 × 0.9535 = $954

Projection Period Selection:

• 5 years: Standard for most analyses
• 7-10 years: High growth companies with longer runway
• 3 years: Mature, stable businesses

Step 8: Terminal Value Calculation

Perpetuity Growth Method (Preferred):

Terminal FCF = Final Year FCF × (1 + Terminal Growth Rate)
Terminal Value = Terminal FCF / (WACC - Terminal Growth Rate)

Critical Constraint: Terminal Growth < WACC (otherwise infinite value)

Terminal Growth Rate Selection:

• Conservative: 2.0-2.5% (GDP growth rate)
• Moderate: 2.5-3.5%
• Aggressive: 3.5-5.0% (only for market leaders)

Do not exceed: Risk-free rate or long-term GDP growth

Exit Multiple Method (Alternative):

Terminal Value = Final Year EBITDA × Exit Multiple

Where Exit Multiple comes from:
- Industry comparable trading multiples
- Precedent transaction multiples
- Typical range: 8-15x EBITDA

Present Value of Terminal Value:

PV of Terminal Value = Terminal Value / (1 + WACC)^Final Period

Where Final Period accounts for timing:
5-year model with mid-year convention: Period = 4.5

Terminal Value Sanity Check:

• Should represent 50-70% of Enterprise Value
• If >75%, model may be over-reliant on terminal assumptions
• If <40%, check if terminal assumptions are too conservative

Step 9: Enterprise to Equity Value Bridge

Valuation Summary Structure:

(+) Sum of PV of Projected FCFs = $X million
(+) PV of Terminal Value = $Y million
= Enterprise Value = $Z million

(-) Net Debt [or + Net Cash if negative] = $A million
= Equity Value = $B million

÷ Diluted Shares Outstanding = C million shares
= Implied Price per Share = $XX.XX

Current Stock Price = $YY.YY
Implied Return = (Implied Price / Current Price) - 1 = XX%

Critical Adjustments:

• Net Debt = Total Debt - Cash & Equivalents
  • If positive: Subtract from EV (reduces equity value)
  • If negative (Net Cash): Add to EV (increases equity value)
• Use Diluted Shares: Includes options, RSUs, convertible securities
• Other adjustments (if applicable):
  • Minority interests
  • Pension liabilities
  • Operating lease obligations

Valuation Output Format:

Valuation Component,Amount ($M)
PV Explicit FCFs,X.X
PV Terminal Value,Y.Y
Enterprise Value,Z.Z
(-) Net Debt,A.A
Equity Value,B.B
,,
Shares Outstanding (M),C.C
Implied Price per Share,$XX.XX
Current Share Price,$YY.YY
Implied Upside/(Downside),+XX%

Step 10: Sensitivity Analysis

Build three sensitivity tables at the bottom of the DCF sheet showing how valuation changes with different assumptions:

1. WACC vs Terminal Growth - Shows enterprise value sensitivity to discount rate and perpetuity growth
2. Revenue Growth vs EBIT Margin - Shows impact of top-line growth and operating leverage
3. Beta vs Risk-Free Rate - Shows sensitivity to cost of equity components

Implementation: These are simple 2D grids (NOT Excel's "Data Table" feature) with formulas in each cell. Each cell must contain a full DCF recalculation for that specific assumption combination. See Critical Constraints section for detailed requirements on populating all 75 cells programmatically using openpyxl.

<correct_patterns>

This section contains all the CORRECT patterns to follow when building DCF models.

Scenario Block Selection Pattern - Follow This Approach

Assumptions are organized in separate blocks for each scenario:

CRITICAL STRUCTURE - Three rows per section header:

BEAR CASE ASSUMPTIONS (section header, merge cells across)
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),12%,10%,9%,8%,7%
EBIT Margin (%),45%,44%,43%,42%,41%

BASE CASE ASSUMPTIONS (section header, merge cells across)
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),16%,14%,12%,10%,9%
EBIT Margin (%),48%,49%,50%,51%,52%

BULL CASE ASSUMPTIONS (section header, merge cells across)
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),20%,18%,15%,13%,11%
EBIT Margin (%),50%,51%,52%,53%,54%

Each scenario block MUST have a column header row showing the projection years (FY2025E, FY2026E, etc.) immediately below the section title. Without this, users cannot tell which assumption value corresponds to which year.

How to reference assumptions - Create a consolidation column:

1. Case selector cell (e.g., B6) contains 1=Bear, 2=Base, or 3=Bull
2. Create a consolidation column with INDEX or OFFSET formulas to pull from the correct scenario block
3. Projection formulas reference the consolidation column (clean cell references)
4. Each scenario block contains full set of DCF assumptions across projection years

Recommended consolidation column pattern (using INDEX): =INDEX(B10:D10, 1, $B$6)

NOT this - scattered IF statements throughout: =IF($B$6=1,[Bear block cell],IF($B$6=2,[Base block cell],[Bull block cell]))

The consolidation column approach centralizes logic and makes the model easier to audit.

Correct Revenue Projection Pattern

Create a consolidation column with INDEX formulas, then reference it in projections:

Step 1 - Consolidation column for FY1 growth: =INDEX([Bear FY1 growth]:[Bull FY1 growth], 1, $B$6)

Step 2 - Revenue projection references the consolidation column: Revenue Year 1: =D29*(1+$E$10)

Where:

• D29 = Prior year revenue
• $E$10 = Consolidation column cell for FY1 growth (contains INDEX formula)
• $B$6 = Case selector (1=Bear, 2=Base, 3=Bull)

This approach is cleaner than embedding IF statements in every projection formula and makes it much easier to audit which scenario assumptions are being used.

Correct FCF Formula Pattern

Use consolidation columns with INDEX formulas, then reference them in FCF calculations:

Consolidation column approach:

Item,Formula,Reference
D&A,=E29*$E$21,$E$21 = consolidation column for D&A %
CapEx,=E29*$E$22,$E$22 = consolidation column for CapEx %
Δ NWC,=(E29-D29)*$E$23,$E$23 = consolidation column for NWC %
Unlevered FCF,=E57+E58-E60-E62,E57=NOPAT E58=D&A E60=CapEx E62=Δ NWC

Each consolidation column cell contains an INDEX formula that pulls from the appropriate scenario block based on case selector. This keeps projection formulas clean and auditable.

Before writing formulas, confirm scenario block row locations and set up consolidation columns.

Correct Cell Comment Format

Every hardcoded value needs this format:

"Source: [System/Document], [Date], [Reference], [URL if applicable]"

Examples:

Item,Source Comment
Stock price,Source: get_company_overview 2025-10-12 Close price
Shares outstanding,Source: fundamentals MCP get_financial_statements FY2024
Historical revenue,Source: fundamentals MCP get_financial_statements FY2024
Beta,Source: get_company_overview 2025-10-12 5-year monthly beta
Risk-free rate,Source: macro MCP get_treasury_rates 2025-10-12 10Y yield
Consensus estimates,Source: get_company_overview analyst consensus

Correct Assumption Table Structure

CRITICAL: Each scenario block requires THREE structural elements:

1. Section header row (merged cells): e.g., "BEAR CASE ASSUMPTIONS"
2. Column header row showing years - THIS IS REQUIRED, DO NOT SKIP
3. Data rows with assumption values

Structure:

BEAR CASE ASSUMPTIONS (section header - merge across columns A:G)
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),X%,X%,X%,X%,X%
EBIT Margin (%),X%,X%,X%,X%,X%
Terminal Growth,X%,,,,
WACC,X%,,,,

BASE CASE ASSUMPTIONS (section header - merge across columns A:G)
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),X%,X%,X%,X%,X%
EBIT Margin (%),X%,X%,X%,X%,X%
Terminal Growth,X%,,,,
WACC,X%,,,,

BULL CASE ASSUMPTIONS (section header - merge across columns A:G)
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),X%,X%,X%,X%,X%
EBIT Margin (%),X%,X%,X%,X%,X%
Terminal Growth,X%,,,,
WACC,X%,,,,

WITHOUT the column header row showing projection years (FY2025E, FY2026E, etc.), users cannot tell which assumption value corresponds to which year. This row is MANDATORY.

Then create a consolidation column (typically the next column to the right) that uses INDEX formulas to pull from the selected scenario block based on the case selector. This consolidation column is what your projection formulas reference.

Correct Row Planning Process

1. Write ALL headers and labels FIRST:

Row,Content
1,[Company Name] DCF Model
2,Ticker | Date | Year End
4,Case Selector
7,KEY ASSUMPTIONS
26,Assumption headers
27-31,Growth assumptions
...,...

2. Write ALL section dividers and blank rows

3. THEN write formulas using the locked row positions

4. Test formulas immediately after creation

Think of it like construction:

• Good: Pour foundation, then build walls (stable structure)
• Bad: Build walls, then pour foundation (walls collapse)

Excel version:

• Good: Add headers, then write formulas (formulas stable)
• Bad: Write formulas, then add headers (formulas break)

Correct Sensitivity Table Implementation

IMPORTANT: These are NOT Excel's "Data Table" feature. These are simple grids where you write regular formulas using openpyxl. Yes, this means ~75 formulas total (3 tables × 25 cells each), but this is straightforward and required.

Programmatic Population with Formulas:

Each sensitivity table must be fully populated with formulas that recalculate the implied share price for each combination of assumptions. Do not use Excel's Data Table feature (it requires manual intervention and cannot be automated via openpyxl).

Implementation approach - CONCRETE EXAMPLE:

Table Structure (5x5 grid):

WACC vs Terminal Growth,2.0%,2.5%,3.0%,3.5%,4.0%
8.0%,[B88 formula],[C88 formula],[D88 formula],[E88 formula],[F88 formula]
9.0%,[B89 formula],[C89 formula],[D89 formula],[E89 formula],[F89 formula]
...,...,...,...,...,...

Formula Pattern - Cell B88 (WACC=8.0%, Terminal Growth=2.0%):

The formula in B88 should recalculate the implied price using:

• WACC from row header: $A88 (8.0%)
• Terminal Growth from column header: B$87 (2.0%)

Recommended approach: Reference the main DCF calculation but substitute these values.

Example formula structure: =([SUM of PV FCFs using $A88 as discount rate] + [Terminal Value using B$87 as growth rate and $A88 as WACC] - [Net Debt]) / [Shares]

CRITICAL - Write a formula for EVERY cell in the 5x5 grid (25 cells per table, 75 cells total). Use openpyxl to write these formulas programmatically in a loop. Do NOT skip this step or leave placeholder text.

Python implementation pattern:

# Pseudocode for populating sensitivity table
for row_idx, wacc_value in enumerate(wacc_range):
    for col_idx, term_growth_value in enumerate(term_growth_range):
        # Build formula that uses wacc_value and term_growth_value
        formula = f"=<DCF recalc using {wacc_value} and {term_growth_value}>"
        ws.cell(row=start_row+row_idx, column=start_col+col_idx).value = formula

The sensitivity tables must work immediately when the model is opened, with no manual steps required from the user.

</correct_patterns>

<common_mistakes>

This section contains all the WRONG patterns to avoid when building DCF models.

WRONG: Simplified Sensitivity Table Approximations or Placeholder Text

Don't use linear approximations:

// WRONG - Linear approximation
B97: =B88*(1+(0.096-0.116))    // Assumes linear relationship

// WRONG - Division shortcut
B105: =B88/(1+(E48-0.07))      // Doesn't recalculate full DCF

Don't leave placeholder text:

// WRONG - Placeholder note
"Note: Use Excel Data Table feature (Data → What-If Analysis → Data Table) to populate sensitivity tables."

// WRONG - Empty cells
[leaving cells blank because "this is complex"]

Don't confuse terminology:

• ❌ "Sensitivity tables need Excel's Data Table feature" (NO - that's a specific Excel tool we can't use)
• ✅ "Sensitivity tables are simple grids with formulas in each cell" (YES - this is what we build)

Why these shortcuts are wrong:

• Linear approximation formulas don't actually recalculate the DCF - they just apply simple math adjustments
• The relationships are not linear, so the results will be inaccurate
• Placeholder text requires manual user intervention
• Model is not immediately usable when delivered
• Not professional or client-ready
• Empty cells = incomplete deliverable

Common rationalization to REJECT: "Writing 75+ formulas feels complex, so I'll leave a note for the user to complete it manually."

Reality: Writing 75 formulas is straightforward when you use a loop in Python with openpyxl. Each formula follows the same pattern - just substitute the row/column values. This is a required part of the deliverable.

Instead: Populate every sensitivity cell with formulas that recalculate the full DCF for that specific combination of assumptions

WRONG: Missing Cell Comments

Don't do this:

• Create all hardcoded inputs without comments
• Think "I'll add them later"
• Write "TODO: add source"
• Leave blue inputs without documentation

Why it's wrong:

• Can't verify where data came from
• Fails xlsx skill requirements
• Not audit-ready
• Wastes time fixing later

Instead: Add cell comment AS EACH hardcoded value is created

WRONG: Formula Row References Off

Symptom: The FCF section references wrong assumption rows: D&A: =E29*$E$34 // Should be $E$21, but referencing wrong row CapEx: =E29*$E$41 // Should be $E$22, but row shifted

Why this happens:

1. Formulas written first
2. Then headers inserted
3. All row references shifted
4. Now formulas point to wrong cells → #REF! errors

Instead: Lock row layout FIRST, then write formulas

WRONG: Single Row for Each Assumption Across Scenarios

Don't structure assumptions like this:

Assumption,Bear,Base,Bull
Revenue Growth FY1,10%,13%,16%
Revenue Growth FY2,9%,12%,15%

This vertical layout makes it hard to see the progression across years within each scenario.

Why it's wrong:

• Makes it difficult to see assumptions evolving across years within each scenario
• Harder to compare scenario assumptions across full projection period
• Less intuitive for reviewing scenario logic

Instead:

• Create separate blocks for each scenario (Bear, Base, Bull)
• Within each block, show assumptions horizontally across projection years
• This makes each scenario's assumptions easier to review as a cohesive set

WRONG: No Borders

Don't deliver a model without borders:

• No section delineation
• All cells blend together
• Hard to read and unprofessional

Why it's wrong:

• Not client-ready
• Difficult to navigate
• Looks amateur

Instead: Add borders around all major sections

WRONG: Wrong Font Colors or No Font Color Distinction

Don't do this:

• All text is black
• Only use fill colors (no font color changes)
• Mix up which cells are blue vs black

Why it's wrong:

• Can't distinguish inputs from formulas
• Auditing becomes impossible
• Violates xlsx skill requirements

Instead: Blue text for ALL hardcoded inputs, black text for ALL formulas, green for sheet links

WRONG: Operating Expenses Based on Gross Profit

Don't do this: S&M: =E33*0.15 // E33 = Gross Profit (WRONG)

Why it's wrong:

• Operating expenses scale with revenue, not gross profit
• Produces unrealistic margin progression
• Not how businesses actually operate

Instead: S&M: =E29*0.15 // E29 = Revenue (CORRECT)

TOP 5 ERRORS SUMMARY

1. Formula row references off → Define ALL row positions BEFORE writing formulas
2. Missing cell comments → Add comments AS cells are created, not at end
3. Simplified sensitivity tables → Populate all cells with full DCF recalc formulas, not approximations
4. Scenario block references wrong → Ensure IF formulas pull from correct Bear/Base/Bull blocks
5. No borders → Add professional section borders for client-ready appearance

In addition, be aware of these errors:

WACC Calculation Errors

• Mixing book and market values in capital structure
• Using equity beta instead of asset/unlevered beta incorrectly
• Wrong tax rate application to cost of debt
• Incorrect risk-free rate (must use current 10Y Treasury)
• Failure to adjust for net debt vs net cash position

Growth Assumption Flaws

• Terminal growth > WACC (creates infinite value)
• Projection growth rates inconsistent with historical performance
• Ignoring industry growth constraints
• Revenue growth not aligned with unit economics
• Margin expansion without operational justification

Terminal Value Mistakes

• Using wrong growth method (perpetuity vs exit multiple)
• Terminal value >80% of enterprise value (suggests over-reliance)
• Inconsistent terminal margins with steady state assumptions
• Wrong discount period for terminal value

Cash Flow Projection Errors

• Operating expenses based on gross profit instead of revenue
• D&A/CapEx percentages misaligned with business model
• Working capital changes not properly calculated
• Tax rate inconsistency between years
• NOPAT calculation errors

These errors are the most common. Re-read this section before starting any DCF build.

</common_mistakes>

Excel File Creation

For all Excel formatting, number formats, and color standards, follow the guidelines in .agents/skills/xlsx/SKILL.md.

After generating Excel, run recalculation: python .agents/skills/xlsx/scripts/recalc.py model.xlsx 30

Quality Rubric

Every DCF model must maximize for:

1. Realistic revenue and margin assumptions based on historical performance
2. Appropriate cost of capital calculation with proper CAPM methodology
3. Comprehensive sensitivity analysis showing valuation ranges
4. Clear terminal value calculation with supporting rationale
5. Professional model structure enabling scenario analysis
6. Transparent documentation of all key assumptions

Input Requirements

Minimum Required Inputs

1. Company identifier: Ticker symbol or company name
2. Growth assumptions: Revenue growth rates for projection period (or "use consensus")
3. Optional parameters:
   • Projection period (default: 5 years)
   • Scenario cases (Bear/Base/Bull growth and margin assumptions)
   • Terminal growth rate (default: 2.5-3.0%)
   • Specific WACC inputs if not using CAPM

Excel Model Structure

Sheet Architecture

Create two sheets:

1. DCF - Main valuation model with sensitivity analysis at bottom
2. WACC - Cost of capital calculation

CRITICAL: Sensitivity tables go at the BOTTOM of the DCF sheet (not on a separate sheet). This keeps all valuation outputs together.

Formula Recalculation (MANDATORY)

After creating or modifying the Excel model, run recalculation:

python .agents/skills/xlsx/scripts/recalc.py [path_to_excel_file] [timeout_seconds]

Example:

python .agents/skills/xlsx/scripts/recalc.py $WORK_DIR/work/{task}/AAPL_DCF_Model.xlsx 30

Fix all errors and re-run until status is "success" before delivering the model. See .agents/skills/xlsx/SKILL.md for output format and error handling details.

Formatting Standards

For all Excel formatting, number formats, and color standards, follow the guidelines in .agents/skills/xlsx/SKILL.md.

After generating Excel, run recalculation: python .agents/skills/xlsx/scripts/recalc.py model.xlsx 30

DCF Sheet Detailed Structure

Section 1: Header

Row,Content
1,[Company Name] DCF Model
2,Ticker: [XXX] | Date: [Date] | Year End: [FYE]
3,Blank
4,Case Selector Cell (1=Bear 2=Base 3=Bull)
5,Case Name Display (formula: =IF([Selector]=1"Bear"IF([Selector]=2"Base""Bull")))

Section 2: Market Data (NOT case dependent)

Item,Value
Current Stock Price,$XX.XX
Shares Outstanding (M),XX.X
Market Cap ($M),[Formula]
Net Debt ($M),XXX [or Net Cash if negative]

Section 3: DCF Scenario Assumptions

Create separate assumption blocks for each scenario (Bear, Base, Bull) with DCF-specific assumptions (Revenue Growth %, EBIT Margin %, Tax Rate %, D&A % of Revenue, CapEx % of Revenue, NWC Change % of ΔRev, Terminal Growth Rate, WACC) laid out horizontally across projection years. Each block must include section header, column header row showing the projection years (FY1, FY2, etc.), and data rows. See <correct_patterns> section "Correct Assumption Table Structure" for the exact layout.

Section 4: Historical & Projected Financials

Reference a consolidation column (e.g., "Selected Case") that pulls from scenario blocks, not scattered IF formulas in every projection row.

Income Statement ($M),2020A,2021A,2022A,2023A,2024E,2025E,2026E
Revenue,XXX,XXX,XXX,XXX,[=E29*(1+$E$10)],[=F29*(1+$E$11)],[=G29*(1+$E$12)]
  % growth,XX%,XX%,XX%,XX%,[=E29/D29-1],[=F29/E29-1],[=G29/F29-1]
,,,,,,
Gross Profit,XXX,XXX,XXX,XXX,[=E29*E33],[=F29*F33],[=G29*G33]
  % margin,XX%,XX%,XX%,XX%,[=E33/E29],[=F33/F29],[=G33/G29]
,,,,,,
Operating Expenses:,,,,,,,
  S&M,XXX,XXX,XXX,XXX,[=E29*0.15],[=F29*0.14],[=G29*0.13]
  R&D,XXX,XXX,XXX,XXX,[=E29*0.12],[=F29*0.11],[=G29*0.10]
  G&A,XXX,XXX,XXX,XXX,[=E29*0.08],[=F29*0.07],[=G29*0.07]
  Total OpEx,XXX,XXX,XXX,XXX,[=E36+E37+E38],[=F36+F37+F38],[=G36+G37+G38]
,,,,,,
EBIT,XXX,XXX,XXX,XXX,[=E33-E39],[=F33-F39],[=G33-G39]
  % margin,XX%,XX%,XX%,XX%,[=E41/E29],[=F41/F29],[=G41/G29]
,,,,,,
Taxes,(XX),(XX),(XX),(XX),[=E41*$E$24],[=F41*$E$24],[=G41*$E$24]
  Tax rate,XX%,XX%,XX%,XX%,[=E43/E41],[=F43/F41],[=G43/G41]
,,,,,,
NOPAT,XXX,XXX,XXX,XXX,[=E41-E43],[=F41-F43],[=G41-G43]

Key Formula Pattern:

• Revenue growth: =E29*(1+$E$10) where $E$10 is consolidation column for Year 1 growth
• NOT: =E29*(1+IF($B$6=1,$B$10,IF($B$6=2,$C$10,$D$10)))

This approach is cleaner, easier to audit, and prevents formula errors by centralizing the scenario logic.

Section 5: Free Cash Flow Build

CRITICAL: Verify row references point to the CORRECT assumption rows. Test formulas immediately after creation.

Cash Flow ($M),2020A,2021A,2022A,2023A,2024E,2025E,2026E
NOPAT,XXX,XXX,XXX,XXX,[=E45],[=F45],[=G45]
(+) D&A,XXX,XXX,XXX,XXX,[=E29*$E$21],[=F29*$E$21],[=G29*$E$21]
    % of Rev,XX%,XX%,XX%,XX%,[=E58/E29],[=F58/F29],[=G58/G29]
(-) CapEx,(XX),(XX),(XX),(XX),[=E29*$E$22],[=F29*$E$22],[=G29*$E$22]
    % of Rev,XX%,XX%,XX%,XX%,[=E60/E29],[=F60/F29],[=G60/G29]
(-) Δ NWC,(XX),(XX),(XX),(XX),[=(E29-D29)*$E$23],[=(F29-E29)*$E$23],[=(G29-F29)*$E$23]
    % of Δ Rev,XX%,XX%,XX%,XX%,[=E62/(E29-D29)],[=F62/(F29-E29)],[=G62/(G29-F29)]
,,,,,,
Unlevered FCF,XXX,XXX,XXX,XXX,[=E57+E58-E60-E62],[=F57+F58-F60-F62],[=G57+G58-G60-G62]

Row reference examples (based on layout planning):

• $E$21 = D&A % assumption (consolidation column, row 21)
• $E$22 = CapEx % assumption (consolidation column, row 22)
• $E$23 = NWC % assumption (consolidation column, row 23)
• E29 = Revenue for year (row 29)
• E45 = NOPAT for year (row 45)

Before writing formulas: Confirm these row numbers match the actual layout. Test one column, then copy across.

Section 6: Discounting & Valuation

DCF Valuation,2024E,2025E,2026E,2027E,2028E,Terminal
Unlevered FCF ($M),XXX,XXX,XXX,XXX,XXX,
Period,0.5,1.5,2.5,3.5,4.5,
Discount Factor,0.XX,0.XX,0.XX,0.XX,0.XX,
PV of FCF ($M),XXX,XXX,XXX,XXX,XXX,
,,,,,,
Terminal FCF ($M),,,,,,,XXX
Terminal Value ($M),,,,,,,XXX
PV Terminal Value ($M),,,,,,,XXX
,,,,,,
Valuation Summary ($M),,,,,,
Sum of PV FCFs,XXX,,,,,
PV Terminal Value,XXX,,,,,
Enterprise Value,XXX,,,,,
(-) Net Debt,(XX),,,,,
Equity Value,XXX,,,,,
,,,,,,
Shares Outstanding (M),XX.X,,,,,
IMPLIED PRICE PER SHARE,$XX.XX,,,,,
Current Stock Price,$XX.XX,,,,,
Implied Upside/(Downside),XX%,,,,,

WACC Sheet Structure

COST OF EQUITY CALCULATION,,
Risk-Free Rate (10Y Treasury),X.XX%,[macro MCP: get_treasury_rates()]
Beta (5Y monthly),X.XX,[Input]
Equity Risk Premium,X.XX%,[macro MCP: get_market_risk_premium()]
Cost of Equity,X.XX%,[Calculated blue]
,,
COST OF DEBT CALCULATION,,
Credit Rating,AA-,[Yellow input]
Pre-Tax Cost of Debt,X.XX%,[Yellow input]
Tax Rate,XX.X%,[Link to DCF sheet]
After-Tax Cost of Debt,X.XX%,[Calculated blue]
,,
CAPITAL STRUCTURE,,
Current Stock Price,$XX.XX,[Link to DCF]
Shares Outstanding (M),XX.X,[Link to DCF]
Market Capitalization ($M),"X,XXX",[Calculated]
,,
Total Debt ($M),XXX,[Yellow input]
Cash & Equivalents ($M),XXX,[Yellow input]
Net Debt ($M),XXX,[Calculated]
,,
Enterprise Value ($M),"X,XXX",[Calculated]
,,
WACC CALCULATION,Weight,Cost,Contribution
Equity,XX.X%,X.X%,X.XX%
Debt,XX.X%,X.X%,X.XX%
,,
WEIGHTED AVERAGE COST OF CAPITAL,X.XX%,[Green output]

Key WACC Formulas:

Market Cap = Price × Shares
Net Debt = Total Debt - Cash
Enterprise Value = Market Cap + Net Debt
Equity Weight = Market Cap / EV
Debt Weight = Net Debt / EV
WACC = (Cost of Equity × Equity Weight) + (After-tax Cost of Debt × Debt Weight)

Sensitivity Analysis (Bottom of DCF Sheet)

TERMINOLOGY REMINDER: "Sensitivity tables" = simple 2D grids with row headers, column headers, and formulas in each data cell. NOT Excel's "Data Table" feature (Data → What-If Analysis → Data Table). You will use openpyxl to write regular Excel formulas into each cell.

Location: Rows 87+ on DCF sheet (NOT a separate sheet)

Three sensitivity tables, vertically stacked:

1. WACC vs Terminal Growth (rows 87-100) - 5x5 grid = 25 cells with formulas
2. Revenue Growth vs EBIT Margin (rows 102-115) - 5x5 grid = 25 cells with formulas
3. Beta vs Risk-Free Rate (rows 117-130) - 5x5 grid = 25 cells with formulas

Total formulas to write: 75 (this is required, not optional)

CRITICAL: All sensitivity table cells must be populated programmatically with formulas using openpyxl. DO NOT use linear approximation shortcuts. DO NOT leave placeholder text or notes about manual steps. DO NOT rationalize leaving cells empty because "it's complex" - use a Python loop to generate the formulas.

Table Setup:

1. Create table structure with row/column headers (the assumption values to test)
2. Populate EVERY data cell with a formula that:
   • Uses the row header value (e.g., WACC = 9.0%)
   • Uses the column header value (e.g., Terminal Growth = 3.0%)
   • Recalculates the full DCF with those specific assumptions
   • Returns the implied share price for that scenario
3. All cells must contain working formulas when delivered
4. Format cells with conditional formatting: Green scale for higher values, red scale for lower values
5. Bold the base case cell
6. Leave 1-2 blank rows between tables

No manual intervention required - the sensitivity tables must be fully functional when the user opens the file.

Case Selector Implementation

Three-Case Framework:

Bear Case

• Conservative revenue growth (low end of historical range)
• Margin compression or no expansion
• Higher WACC (risk premium increase)
• Lower terminal growth rate
• Higher CapEx assumptions

Base Case

• Consensus or management guidance revenue growth
• Moderate margin expansion based on operating leverage
• Current market-implied WACC
• GDP-aligned terminal growth (2.5-3.0%)
• Standard CapEx assumptions

Bull Case

• Optimistic revenue growth (high end of projections)
• Significant margin expansion
• Lower WACC (reduced risk premium)
• Higher terminal growth (3.5-5.0%)
• Reduced CapEx intensity

Formula Implementation:

DO NOT use nested IF formulas scattered throughout. Instead, create a consolidation column that uses INDEX or OFFSET formulas to pull from the appropriate scenario block.

Recommended pattern (using INDEX): =INDEX(B10:D10, 1, $B$6) where B10:D10 = Bear/Base/Bull values, 1 = row offset, $B$6 = case selector cell (1, 2, or 3)

Then reference the consolidation column in all projections: Revenue Year 1: =D29*(1+$E$10) where $E$10 is the consolidation column value for Year 1 growth.

This approach centralizes scenario logic, making the model easier to audit and maintain.

Deliverables Structure

File naming: [Ticker]_DCF_Model_[Date].xlsx

Two sheets:

1. DCF - Complete model with Bear/Base/Bull cases + three sensitivity tables at bottom (WACC vs Terminal Growth, Revenue Growth vs EBIT Margin, Beta vs Risk-Free Rate)
2. WACC - Cost of capital calculation

Key features: Case selector (1/2/3), consolidation column with INDEX/OFFSET formulas, color-coded cells, cell comments on all inputs, professional borders

Best Practices

Model Construction

1. Build incrementally: Complete each section before moving to next
2. Test as building: Enter sample numbers to verify formulas
3. Use consistent structure: Similar calculations follow similar patterns
4. Comment complex formulas: Add notes for unusual calculations
5. Build in checks: Sum checks and balance checks where applicable

Documentation

1. Document all assumptions: Explain reasoning behind key inputs
2. Cite data sources: Note where each data point came from
3. Explain methodology: Describe any non-standard approaches
4. Flag uncertainties: Highlight areas with limited visibility

Quality Control

1. Cross-check calculations: Verify math in multiple ways
2. Stress test assumptions: Run sensitivity to ensure model is robust
3. Peer review: Have someone else check formulas
4. Version control: Save versions as work progresses

Common Variations

High-Growth Technology Companies

• Longer projection period (7-10 years)
• Higher initial growth rates (20-30%)
• Significant margin expansion over time
• Higher WACC (12-15%)
• Model unit economics (users, ARPU, etc.)

Mature/Stable Companies

• Shorter projection period (3-5 years)
• Modest growth rates (GDP +1-3%)
• Stable margins
• Lower WACC (7-9%)
• Focus on cash generation and capital allocation

Cyclical Companies

• Model through economic cycle
• Normalize margins at mid-cycle
• Consider trough and peak scenarios
• Adjust beta for cyclicality

Multi-Segment Companies

• Separate DCFs for each business unit
• Different growth rates and margins by segment
• Sum-of-parts valuation
• Consider synergies

Troubleshooting

If you encounter errors or unreasonable results, read TROUBLESHOOTING.md for detailed debugging guidance.

Workflow Integration

At Start of DCF Build

1. Gather market data:

   • Use get_company_overview tool for stock price, beta, shares outstanding, analyst consensus
   • Use macro MCP: get_treasury_rates() for risk-free rate (10Y)
   • Use macro MCP: get_market_risk_premium() for equity risk premium
   • Use web search/fetch as supplement if needed

2. Gather historical financials:

   • Use fundamentals MCP: get_financial_statements(symbol, 'all', 'annual', 5) for income/balance/cash flow
   • Use fundamentals MCP: get_financial_ratios(symbol) for ratios
   • Use fundamentals MCP: get_growth_metrics(symbol) for growth rates
   • Request from user if specific data is needed

3. Begin model construction using the DCF methodology detailed in this skill

During Model Construction

1. Build Excel model using openpyxl with formulas (not hardcoded values)
2. Follow xlsx skill conventions for formula construction and formatting
3. Apply fill colors only if requested by user or if specific brand guidelines are provided

Before Delivering Model (MANDATORY)

1. Verify structure:

   • Scenario blocks for Bear/Base/Bull with assumptions across projection years
   • Case selector functional with formulas referencing correct scenario blocks
   • Sensitivity tables at bottom of DCF sheet (not separate sheet)
   • Font colors: Blue inputs, black formulas, green sheet links
   • Cell comments on ALL hardcoded inputs
   • Professional borders around major sections

2. Recalculate formulas: Run python .agents/skills/xlsx/scripts/recalc.py model.xlsx 30

3. Check output:

   • If status is "success" → Continue to step 4
   • If status is "errors_found" → Check error_summary and read TROUBLESHOOTING.md for debugging guidance

4. Fix errors and re-run recalc.py until status is "success"

5. Spot-check formulas:

   • Test one FCF formula - does it reference the correct assumption rows?
   • Change case selector - does the consolidation column update properly?
   • Verify revenue formulas reference consolidation column (not nested IF formulas)

6. Deliver model

Available Data Sources

• fundamentals MCP: get_financial_statements, get_financial_ratios, get_growth_metrics, get_historical_valuation
• macro MCP: get_treasury_rates, get_market_risk_premium
• get_company_overview tool: stock price, beta, shares, analyst consensus, growth estimates
• Web search/fetch: supplement for additional market data
• User-provided data: historical financials, custom assumptions

Final Output Checklist

Before delivering DCF model:

Required:

• Run python .agents/skills/xlsx/scripts/recalc.py model.xlsx 30 until status is "success" (zero formula errors)
• Two sheets: DCF (with sensitivity at bottom), WACC
• Font colors: Blue=inputs, Black=formulas, Green=sheet links
• Cell comments on ALL hardcoded inputs
• Sensitivity tables fully populated with formulas
• Professional borders around major sections

Validation:

• OpEx based on revenue (not gross profit)
• Terminal value 50-70% of EV
• Terminal growth < WACC
• Tax rate 21-28%
• File naming: [Ticker]_DCF_Model_[Date].xlsx

Equity Research Earnings Update

Create professional EARNINGS UPDATE REPORTS analyzing quarterly results for companies already under coverage, following institutional standards (JPMorgan, Goldman Sachs, Morgan Stanley format).

Key Characteristics:

• Length: 8-12 pages
• Word Count: 3,000-5,000 words
• Tables: 1-3 summary tables (NOT comprehensive)
• Figures: 8-12 charts
• Turnaround: 1-2 days (within 24-48 hours of earnings)
• Audience: Clients already familiar with the company
• Focus: What's NEW - beat/miss, updated estimates, thesis impact
• Font: Times New Roman throughout (unless user specifies otherwise)

When to Use

Use when the user requests:

• "Create an earnings update for [Company] Q3 2024"
• "Analyze [Company]'s quarterly results"
• "Post-earnings report for [Company]"
• "Q1/Q2/Q3/Q4 update for [Company]"

Do NOT use if:

• User requests "initiation report" → Use different skill
• User requests "flash note" or "quick take" → Different format
• Company is not already covered → Need initiation first

Critical Requirements

1. Speed & Timeliness

• Publish within 24-48 hours of earnings release
• Focus on NEW information only
• Don't rehash company background extensively

2. Beat/Miss Analysis

• Lead with whether company beat or missed estimates
• Quantify variances (e.g., "Revenue beat by $120M or 3%")
• Explain WHY results differed from expectations

3. Summary Format

• Keep tables to 1-3 (summary only, not comprehensive)
• No full P&L/Cash Flow/Balance Sheet (just key metrics)
• Assume reader has seen initiation report

4. Citations & Source Attribution ⭐⭐⭐ MANDATORY

CRITICAL: Properly cite all data with SPECIFIC sources and CLICKABLE HYPERLINKS.

Include specific citations WITH CLICKABLE LINKS in every figure and table:

Source: Q3 2024 10-Q filed November 8, 2024; Company earnings release
        [Hyperlink "10-Q" to: https://www.sec.gov/cgi-bin/viewer?accession=...]
        [Hyperlink "earnings release" to: https://investor.company.com/news/q3-2024]

HOW HYPERLINKS SHOULD APPEAR IN WORD:

• Document names appear as blue, underlined clickable links
• Reader can Ctrl+Click to open source directly
• Not plain text URLs - formatted hyperlinks with display text

REQUIRED SOURCES LIST:

Cite in every earnings update:

• ✅ Earnings release (with date and URL)
• ✅ 10-Q filing (with filing date and EDGAR link)
• ✅ Earnings call transcript (with date)
• ✅ Investor presentation/supplemental materials (if available)
• ✅ Consensus estimates source (Bloomberg/FactSet/etc. with date)
• ✅ Prior guidance (from previous quarter's materials)

REFERENCE SECTION WITH CLICKABLE HYPERLINKS:

Include "Sources" section at end of report:

SOURCES & REFERENCES

Earnings Materials (Q3 2024):
• Earnings Release (November 7, 2024)
  [Hyperlink entire line to: https://investor.company.com/news/q3-2024-earnings]

• Form 10-Q (Filed November 8, 2024)
  [Hyperlink to: https://www.sec.gov/cgi-bin/viewer?accession=...]

• Earnings Call Transcript (November 7, 2024)
  [Hyperlink to: https://seekingalpha.com/article/...]

• Investor Presentation (November 7, 2024)
  [Hyperlink to: https://investor.company.com/presentations/q3-2024.pdf]

VERIFICATION CHECKLIST:

• Every figure has source with specific document and date
• Every table has source with document reference
• Beat/miss analysis cites consensus source with date
• Guidance changes cite current and prior guidance sources
• Key statistics have footnotes
• Sources section lists all materials with URLs
• ALL URLs are CLICKABLE HYPERLINKS (not plain text)
• All SEC filings hyperlinked to EDGAR viewer

5. Updated Estimates

• Update forward estimates based on results
• Show old vs. new estimates clearly
• Explain what changed and why

High-Level Workflow

The earnings update process follows 5 phases:

Phase 1: Data Collection (30-60 minutes)

🚨🚨🚨 CRITICAL: TRAINING DATA IS OUTDATED 🚨🚨🚨

BEFORE STARTING - COMPLETE THESE 4 STEPS IN ORDER:

1. CHECK TODAY'S DATE - Write down the current date
2. SEARCH FOR LATEST - Use web search: "[Company] latest earnings results"
3. VERIFY THE DATE - Confirm earnings release is within last 3 months
4. CHECK TRANSCRIPT DATE - Verify transcript date matches release date

COMMON MISTAKE: Using outdated earnings calls from training data instead of searching for the latest.

REQUIREMENTS:

• ✅ Search for latest earnings - do NOT rely on training data
• ✅ Write down today's date and the release date found
• ✅ Verify release date is within 3 months of today
• ✅ Verify transcript date matches release date
• ✅ If dates don't match or are old (>3 months), search again

See references/workflow.md for detailed search procedures and verification steps.

Phase 2: Analysis (2-3 hours)

• Beat/miss analysis for each key metric
• Segment/geographic/product breakdown
• Margin and guidance analysis
• Update financial model and estimates

See references/workflow.md for detailed analysis framework.

Phase 3: Chart Generation (1-2 hours)

Create 8-12 charts focusing on quarterly trends and what's new:

• Quarterly revenue progression
• Quarterly EPS progression
• Quarterly margin trends
• Revenue by segment/geography
• Key operating metrics
• Beat/miss summary
• Estimate revisions
• Valuation charts

See references/workflow.md for chart specifications.

Phase 4: Report Creation (2-3 hours)

Create 8-12 page DOCX report with specific structure.

See references/report-structure.md for complete page-by-page templates and formatting requirements.

High-level structure:

• Page 1: Earnings summary with rating and price target
• Pages 2-3: Detailed results analysis
• Pages 4-5: Key metrics & guidance
• Pages 6-7: Updated investment thesis
• Pages 8-10: Valuation & estimates
• Pages 11-12: Appendix (optional)

Phase 5: Quality Check & Delivery (30 minutes)

Verify content, formatting, accuracy, and timeliness before delivery.

See references/best-practices.md for quality checklist and common mistakes to avoid.

Output Specification

Primary Deliverable: DOCX report (8-12 pages) File Name: [Company]_Q[Quarter]_[Year]_Earnings_Update.docx Example: Nike_Q2_FY24_Earnings_Update.docx

Contents:

• Page 1: Summary with rating, price target, key takeaways
• Pages 2-3: Detailed results analysis
• Pages 4-5: Key metrics and guidance
• Pages 6-7: Updated thesis assessment
• Pages 8-10: Valuation and estimates
• Pages 11-12: Appendix (optional)
• 8-12 embedded charts
• 1-3 summary tables
• Complete sources section with clickable hyperlinks

Optional Deliverable: XLS model update (optional for earnings updates)

Key Differences from Initiation Report

Resources

references/workflow.md

Detailed Phase 1-5 instructions with step-by-step procedures for data collection, analysis, chart generation, and report creation.

references/report-structure.md

Complete page-by-page templates, table formats, and formatting requirements for the DOCX report.

references/best-practices.md

Examples of good/bad headlines, tips for success, common mistakes to avoid, and comprehensive quality checklist.

Dependencies

Required:

• Python (matplotlib, pandas, seaborn) for chart generation
• DOCX skill for report creation

Optional:

• XLS skill for model updates (not required for earnings updates)

Earnings Preview

description: Build pre-earnings analysis with estimate models, scenario frameworks, and key metrics to watch. Use before a company reports quarterly earnings to prepare positioning notes, set up bull/bear scenarios, and identify what will move the stock. Triggers on "earnings preview", "what to watch for [company] earnings", "pre-earnings", "earnings setup", or "preview Q[X] for [company]".

Workflow

Step 1: Gather Context

• Identify the company and reporting quarter
• Use get_company_overview tool — includes earnings history (actual vs estimate), analyst consensus, price targets, rating distribution
• Use get_stock_daily_prices tool for recent price history and to identify the earnings date window
• Use get_sec_filing tool — auto-attaches earnings call transcript for 10-K/10-Q filings (review prior quarter for guidance or commentary)
• Use WebSearch / WebFetch for recent news and sentiment heading into earnings

Step 2: Key Metrics Framework

Build a "what to watch" framework specific to the company:

Financial Metrics:

• Revenue vs. consensus (total and by segment)
• EPS vs. consensus
• Margins (gross, operating, net) — expanding or contracting?
• Free cash flow
• Forward guidance vs. consensus

Operational Metrics (sector-specific):

• Tech/SaaS: ARR, net retention, RPO, customer count
• Retail: Same-store sales, traffic, basket size
• Industrials: Backlog, book-to-bill, price vs. volume
• Financials: NIM, credit quality, loan growth, fee income
• Healthcare: Scripts, patient volumes, pipeline updates

Step 3: Scenario Analysis

Build 3 scenarios with stock price implications:

For each scenario:

• What would need to happen operationally
• What management commentary would signal this
• Historical context — how has the stock moved on similar prints?

Step 4: Catalyst Checklist

Identify the 3-5 things that will determine the stock's reaction:

1. [Metric] vs. [consensus/whisper number] — why it matters
2. [Guidance item] — what the buy-side expects to hear
3. [Narrative shift] — any strategic changes, M&A, restructuring

Step 5: Output

Save all deliverables to $WORK_DIR/work/{task}/. One-page earnings preview with:

• Company, quarter, earnings date
• Consensus estimates table
• Key metrics to watch (ranked by importance)
• Bull/base/bear scenario table
• Catalyst checklist
• Trading setup: recent stock performance, implied move from options

Important Notes

• Consensus estimates change — always note the source and date of estimates
• "Whisper numbers" from buy-side surveys are often more relevant than published consensus
• Historical earnings reactions help calibrate expectations — use get_company_overview for historical actual vs estimate data
• Options-implied move tells you what the market expects — compare to your scenarios
• Save all output files to $WORK_DIR/work/{task}/

HTML Report

Author a styled, self-contained HTML document and write it to results/ (e.g. results/report.html). The file panel renders it with full browser semantics — JavaScript runs, CDN libraries load, relative assets resolve, and the user can view it fullscreen, open it in a new tab, download it, or export it to PDF.

This is the right output when the user wants a deliverable they can keep, share, or print — an equity research note, an earnings recap, a screen writeup — not a throwaway answer.

Read .agents/skills/ui-design/SKILL.md before authoring. It defines the typography, color, and composition standards that keep the report looking like a research desk artifact rather than a generic AI page. This skill covers the mechanics; that one covers the taste.

User preferences override these defaults. Anything the user has told you — in this conversation, in your long-term memory, or in their saved preferences/memos — outranks every rule in this skill. If they want a different structure, no charts, a specific set of sections, or a particular file layout, do that. (Visual taste — fonts, color, accent, light/dark — is .agents/skills/ui-design/SKILL.md's domain; that skill defers to the user's stated style.) Treat the rules here as sensible defaults for when the user hasn't specified.

Decide: Which Output?

A report from this skill can be interactive (sortable tables, tab/filter controls, hover- and zoomable charts — see Interactivity, below). So interactivity is not what separates it from a dashboard. The real divide is self-contained snapshot file vs. live served app:

Self-Contained by Default

Write one complete HTML file. Everything inline — no external CSS/JS files, no build step.

import json

data = {"labels": ["Q1", "Q2", "Q3", "Q4"], "revenue": [2.1, 2.4, 2.6, 3.0]}

html = f"""<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Acme Q4 Revenue Review</title>
  <style>/* all CSS inline here */</style>
</head>
<body>
  <main>...</main>
  <script>const DATA = {json.dumps(data, ensure_ascii=False)};</script>
  <script>/* render charts from DATA */</script>
</body>
</html>"""

with open("results/report.html", "w", encoding="utf-8") as f:
    f.write(html)

Rules:

• Full <!DOCTYPE html> document with <head>/<body> (unlike inline-widget, which is a bare fragment).
• All CSS in a <style> block, all JS in <script> blocks — nothing external except allowlisted CDN libraries.
• Embed data via <script>const DATA = {json.dumps(data, ensure_ascii=False)};</script> — never inline raw Python dicts, never fetch() a local file. ensure_ascii=False keeps non-ASCII (names, currencies, CJK) readable and correctly encoded.
• Sample or aggregate large datasets before embedding. A report doesn't need every tick — downsample to a sensible resolution, aggregate to the reporting period. Keep the embedded payload lean (target well under ~1MB).

Multi-File When Warranted

The viewer serves files with real relative-path semantics, so a report can reference sibling assets and they resolve correctly:

results/
  report.html              # references charts/revenue.png as a relative path
  charts/
    revenue.png
    margins.png

<img src="charts/revenue.png" alt="Quarterly revenue" style="width:100%;max-width:720px;">

Use multi-file for image-heavy reports — e.g. when you've generated high-quality static charts with matplotlib/plotly savefig and want to embed them rather than redraw client-side.

Rules:

• Keep all asset paths relative (charts/revenue.png, not /results/... and not absolute filesystem paths).
• Keep every asset inside the workspace and under results/ (or a subdir of it). Do not reference files outside the workspace.
• Prefer self-contained when the charts can reasonably be drawn client-side from embedded DATA; reach for multi-file when raster images give materially better output.

CDN Allowlist

Only these origins are reachable from the rendered document. Anything else (including arbitrary fetch()) is blocked.

• cdnjs.cloudflare.com
• cdn.jsdelivr.net
• unpkg.com
• esm.sh
• Google Fonts: fonts.googleapis.com + fonts.gstatic.com

Load chart libraries, fonts, and helpers from these only. Do not call out to data APIs from the document — embed the data instead.

Theme Variables (Defensive Fallback Form)

The viewer can inject app --color-* variables so the report themes with light/dark mode. Always author colors in the fallback form so the document also renders correctly standalone, in a downloaded file, and in print:

color: var(--color-text-primary, #1a1a1a);
background: var(--color-bg-card, #ffffff);
border: 1px solid var(--color-border-muted, #e4e1dc);

The literal fallback is what shows when no app vars are injected (downloaded file, PDF, plain open). Never write a bare var(--color-x) without a fallback, and never hardcode a color with no variable — both break one of the surfaces.

Reuse the same variable names as the inline-widget skill:

Charts

Load Chart.js or ECharts from CDN. Canvas pixels cannot read CSS variables, so resolve colors via getComputedStyle with a literal fallback for the standalone/print case:

<div style="position: relative; height: 320px;">
  <canvas id="revChart"></canvas>
</div>
<script src="https://cdn.jsdelivr.net/npm/chart.js@4/dist/chart.umd.min.js"></script>
<script>
  var cs = getComputedStyle(document.documentElement);
  function pick(name, fallback) {
    var v = cs.getPropertyValue(name).trim();
    return v || fallback;
  }
  var accent = pick('--color-accent-primary', '#1f5fb4');
  var border = pick('--color-border-muted', '#e4e1dc');

  new Chart(document.getElementById('revChart'), {
    type: 'line',
    data: { labels: DATA.labels, datasets: [{ data: DATA.revenue, borderColor: accent, backgroundColor: accent + '22', tension: 0.3, fill: true }] },
    options: {
      responsive: true,
      maintainAspectRatio: false,
      animation: { duration: 400 },
      scales: { y: { grid: { color: border } }, x: { grid: { display: false } } }
    }
  });
</script>

Rules:

• Set height on the wrapper <div>, never on the <canvas>.
• responsive: true, maintainAspectRatio: false always.
• Resolve canvas colors with getComputedStyle + literal fallback (the pick() helper above) — never bare var() in canvas color strings.
• Use UMD CDN builds (set the library global).
• For categorical series, follow the restrained palette in .agents/skills/ui-design/SKILL.md — no rainbow defaults.

Interactivity (When It Helps)

The served document runs JavaScript, so a report can be interactive — and should be when interactivity genuinely helps the reader explore the data, not as decoration. All of it runs client-side over the embedded DATA snapshot; there is no server and no live refresh (that's interactive-dashboard).

Reach for interactivity when it earns its place:

• Sortable / filterable tables — let the reader sort a holdings table by weight or P&L, or filter to a sector. Pays off most on tables past ~15 rows.
• Tabbed or accordion sections — segment a long report (Summary / Financials / Valuation / Risks) so the reader isn't scrolling past everything.
• Interactive charts — Chart.js/ECharts hover tooltips, series toggles (click a legend entry to hide a line), range zoom on a long price history.
• Collapsible detail / "show more" — keep the default view tight; let the curious expand methodology, footnotes, or a raw-numbers table.
• In-page search / highlight — for a long screen or a wide comparison.

Rules:

• Wire events with addEventListener, not inline onclick= / on*= attributes. It is the robust pattern across every surface and keeps logic out of the markup.
• Client-side only. Operate on the embedded DATA; never fetch() a data API (the CDN allowlist blocks it). If the data must be live or is too big to embed, that's a dashboard, not a report.
• Default state must be meaningful. The report has to read correctly before any click — a reader (or a PDF export) that never interacts must still see the substance. Never hide the headline finding behind a tab.
• Degrade for print. Interactive controls (tab bars, filter inputs, sort buttons, "show more" toggles) are chrome — give them .no-print, and make collapsed content render expanded when printing so the PDF is complete. The @media print block below already hides button / .no-print.
• Keep it self-contained and lean. Vanilla JS over the embedded data; no framework, no build step. A little event delegation goes a long way.

Match the effort to the data: a one-number recap needs no interactivity; a 40-holding portfolio or a multi-section deep-dive benefits a lot.

Print / PDF

PDF export = the browser's print-to-PDF. Include an @media print block — without it, PDFs come out degraded. It should:

@media print {
  /* hide interactive chrome — buttons, toolbars, nav, anything not part of the document */
  .no-print, button, nav, .toolbar { display: none !important; }

  /* keep logical blocks from splitting across pages */
  section, figure, table, .card, .kpi { break-inside: avoid; page-break-inside: avoid; }
  h1, h2, h3 { break-after: avoid; }

  /* sane page setup */
  @page { margin: 18mm 16mm; }
  body { background: #fff; color: #000; }

  /* never let entrance animations leave content invisible in the PDF */
  *, *::before, *::after { animation: none !important; transition: none !important; opacity: 1 !important; }

  /* collapse side-by-side layouts — paper is ~816px wide; squeezed columns
     overlap charts and crush prose */
  .row, .grid, .columns { display: block !important; }
  .row > *, .grid > *, .columns > * { width: 100% !important; max-width: 100% !important; }
}

If any element starts at opacity: 0 for an entrance animation, the opacity: 1 !important rule above is what stops the PDF from exporting blank — keep it. Test the print path before declaring done.

Multi-column layouts print badly. Print width is ~816 CSS px — a flex/grid row pairing a chart card with a text column does not fit and will overlap or crush. Either keep the document single-column throughout (safest for a report), or include print rules like the collapse block above for every side-by-side container you create. Chart wrappers keep their fixed height either way.

Landscape documents must declare it. If the content is genuinely wide (a comparison matrix, a wide timeline, a dashboard-style sheet), declare the orientation in the print block — PDF export honors it and lays the page out at landscape width (~1056 CSS px), so charts and columns size for the real paper:

@page { size: letter landscape; margin: 14mm 16mm; }

Named sizes (a4, legal, ...) with optional landscape work too. Without a declaration, export is portrait Letter — don't design landscape-wide content and skip the declaration.

Print typography. Screen sizing usually reads too large on paper. Inside @media print, set print-affecting sizes in pt and tighten slightly:

@media print {
  body { font-size: 10.5pt; line-height: 1.45; }
  h1 { font-size: 17pt; }  h2 { font-size: 13pt; }  h3 { font-size: 11pt; }
  .card, section { padding: 10pt 12pt; }
}

Aim for 10–11pt body text — the register of a printed research note. Keep table cell padding compact (4pt 8pt) so wide tables fit. Page margins come from @page { margin: ... }, not body padding.

Authoring Workflow

1. Fetch and validate data first (check for empty/None); sample or aggregate to a sensible size.
2. Read .agents/skills/ui-design/SKILL.md and commit to a typographic pairing + color direction.
3. Build the full document — inline CSS/JS, embed DATA, draw charts from it; add the @media print block.
4. Write to results/report.html (UTF-8). Image-heavy → write assets to results/charts/*.png and reference them relatively.
5. Open it and print-preview, then cite the report to the user as a clickable link.

Use the Quality Checklist below to verify before delivering.

Quality Checklist

• Full <!DOCTYPE html> document; CSS and JS inline; only allowlisted CDNs referenced
• Data embedded via <script>const DATA = {json.dumps(..., ensure_ascii=False)}</script>; large datasets sampled/aggregated
• Multi-file (if used): all asset paths relative, all assets under results/
• Every color in var(--color-role, #literalFallback) form — no bare var(), no unvariabled hardcodes
• Charts: wrapper-div heights, maintainAspectRatio: false, getComputedStyle + literal fallback for canvas colors
• @media print block present: hides chrome, break-inside: avoid, sane @page margins, animations/opacity neutralized
• Interactivity (if any): events via addEventListener (no inline on*=), runs on embedded DATA (no live fetch), default state is meaningful, controls .no-print and collapsed content expands when printing
• User's stated preferences (this chat / long-term memory / saved prefs) honored wherever they differ from this skill's defaults
• Design follows .agents/skills/ui-design/SKILL.md (typography, single accent, profit/loss color discipline, no AI slop)
• Written to results/; numbers correctly formatted; opened and print-previewed; cited to the user as a link

Idea Generation

Systematic stock screening and investment idea sourcing. Combines quantitative screens, thematic research, and pattern recognition to surface new long and short ideas. Use when looking for new ideas, running screens, or conducting thematic sweeps. Triggers on "idea generation", "stock screen", "find ideas", "what looks interesting", "screen for", "new ideas", or "pitch me something".

Workflow

Step 1: Define Search Criteria

Ask the user for parameters:

• Direction: Long ideas, short ideas, or both
• Market cap: Large, mid, small, micro
• Sector: Specific sector or cross-sector
• Style: Value, growth, quality, special situation, event-driven
• Geography: US, international, global
• Theme: Any specific thematic angle (AI, reshoring, aging demographics, etc.)

Step 2: Quantitative Screens

Data sourcing:

• Use screen_stocks tool with filters (market cap, sector, price, volume, beta, dividend, etc.) to generate initial candidate lists
• Use get_company_overview tool for quick company snapshot with ratios, earnings, analyst consensus
• Use fundamentals MCP: get_financial_statements(symbol) for detailed financials (income statement, balance sheet, cash flow)
• Use fundamentals MCP: get_insider_trades(symbol) for insider transactions and buy/sell stats
• Use fundamentals MCP: get_shares_float(symbol) for float data and short interest
• Use fundamentals MCP: get_technical_indicator(symbol, 'rsi'), get_technical_indicator(symbol, 'macd') for technical signals
• Use WebSearch / WebFetch for recent news and catalysts

Run screens based on the style:

Value Screen

• P/E below sector median
• EV/EBITDA below historical average
• Free cash flow yield >5%
• Price/book below 1.5x
• Insider buying in last 90 days
• Dividend yield above market average

Growth Screen

• Revenue growth >15% YoY
• Earnings growth >20% YoY
• Revenue acceleration (growth rate increasing)
• Expanding margins
• High return on invested capital (>15%)
• Strong net retention (>110% for SaaS)

Quality Screen

• Consistent revenue growth (5+ years)
• Stable or expanding margins
• ROE >15%
• Low debt/equity
• High free cash flow conversion
• Insider ownership >5%

Short Screen

• Declining revenue or decelerating growth
• Margin compression
• Rising receivables / inventory vs. sales
• Insider selling
• Valuation premium to peers without justification
• High short interest with deteriorating fundamentals
• Accounting red flags (auditor changes, restatements)

Special Situation Screen

• Recent IPOs / SPACs with lockup expirations
• Spin-offs in last 12 months
• Companies emerging from restructuring
• Activist involvement
• Management changes at underperforming companies

Step 3: Thematic Sweep

For thematic ideas, research the theme and identify beneficiaries:

1. Define the thesis (e.g., "AI infrastructure spending accelerates through 2026")
2. Map the value chain — who benefits directly vs. indirectly?
3. Identify pure-play vs. diversified exposure
4. Assess which names are already "priced in" vs. under-appreciated
5. Look for second-order beneficiaries that the market hasn't connected to the theme

Step 4: Idea Presentation

For each idea that passes the screen, present:

[Company Name] — [Long/Short] — [One-Line Thesis]

Thesis (3-5 bullets):

• Why this is mispriced
• What the market is missing
• Catalyst to realize value

Key Risks:

• What would make this wrong

Suggested Next Steps:

• Build full model? Deep-dive diligence? Expert call?

Step 5: Output

Save all deliverables to $WORK_DIR/work/{task}/:

• Shortlist of 5-10 ideas with one-page summaries
• Screening criteria and methodology documented
• Comparison table across all ideas
• Prioritized list: which ideas to research first

Move final deliverables to $WORK_DIR/work/{task}/results/.

Important Notes

• Screens surface candidates, not conclusions — every screen output needs fundamental work
• The best ideas often come from intersections (e.g., quality company at value price due to temporary headwind)
• Avoid crowded trades — check ownership data, short interest, and how many analysts cover the name
• Contrarian ideas need a catalyst — being early without a catalyst is the same as being wrong
• Track idea hit rates over time — which screens and approaches produce the best ideas?
• Short ideas need higher conviction — timing is harder and risk is asymmetric

Initiating Coverage

Create institutional-quality equity research initiation reports through a structured 5-task workflow. Each task must be executed separately with verified inputs.

Overview

This skill produces comprehensive first-time coverage reports following institutional standards (JPMorgan, Goldman Sachs, Morgan Stanley format). Tasks are executed individually, each verifying prerequisites before proceeding.

Default Font: Times New Roman throughout all documents (unless user specifies otherwise).

⚠️ CRITICAL: One Task at a Time

THIS SKILL OPERATES IN SINGLE-TASK MODE ONLY.

If User Requests Full Pipeline

When user requests:

• "Create a coverage initiation report for [Company]"
• "Write an initiation report for [Company]"
• "Do the entire equity research process for [Company]"
• "Complete all 5 tasks for [Company]"
• Any request that implies running multiple tasks or the entire workflow

REQUIRED RESPONSE:

1. Ask which specific task to perform:

   I can help you create an equity research initiation report for [Company].
   This involves 5 separate tasks that need to be completed individually:
   
   1. Company Research - Research business, management, industry
   2. Financial Modeling - Build projection model
   3. Valuation Analysis - DCF and comparable companies
   4. Chart Generation - Create 25-35 charts
   5. Report Assembly - Compile final report
   
   Which task would you like to start with?
   

2. When user explicitly requests all tasks together:

   I understand you'd like to complete the entire initiation report pipeline.
   Currently, this skill supports executing one task at a time, which allows
   for better quality control and review at each stage.
   
   We're working on a seamless end-to-end workflow that will make this process
   more automated, but for now, we'll need to complete each task separately.
   
   Would you like to start with Task 1 (Company Research)?
   

3. Never automatically assume which task to start - always ask user to confirm.

4. Never execute multiple tasks in sequence - complete one task, deliver outputs, then wait for next user request.

Task Execution Rules

• ✅ Execute exactly ONE task per user request
• ✅ Always verify prerequisites before starting a task
• ✅ Deliver task outputs and confirm completion
• ✅ Wait for user to explicitly request the next task
• ❌ Never chain multiple tasks together automatically
• ❌ Never assume user wants to proceed to next task
• ❌ Never execute Tasks 3-5 without verifying required inputs exist

⚠️ Deliverables Policy: NO SHORTCUTS

DELIVER ONLY THE SPECIFIED OUTPUTS. DO NOT CREATE EXTRA DOCUMENTS.

Each task specifies exact deliverables. Do NOT create:

• ❌ "Completion summaries"
• ❌ "Executive summaries"
• ❌ "Quick reference guides"
• ❌ "Next steps documents"
• ❌ "Task completion reports"
• ❌ Any other "helpful" documentation not explicitly specified

Why: These extras waste context and are not part of the professional workflow.

What TO deliver:

• ✅ Task 1: Research document (.md) — NOTHING ELSE
• ✅ Task 2: Financial model (.xlsx) — NOTHING ELSE
• ✅ Task 3: Valuation analysis (.md) + Excel tabs added to Task 2 file — NOTHING ELSE
• ✅ Task 4: Charts zip file (.zip) — NOTHING ELSE
• ✅ Task 5: Final report (.docx) — NOTHING ELSE

If a deliverable is not listed above, DO NOT CREATE IT.

Task Selection

Select which task to execute:

How to Use This Skill

User Request Patterns and Responses

Pattern 1: User specifies a specific task

User: "Use initiating-coverage, Task 1 for Tesla"
Response: ✅ Execute Task 1 immediately

Pattern 2: User asks for "initiation report" or "full pipeline"

User: "Create a coverage initiation report for Tesla"
Response: ❌ DO NOT start any task automatically
         ✅ Ask which task to start with (see template above)

Pattern 3: User wants to do "all tasks" or "entire workflow"

User: "I want to complete all 5 tasks for Tesla"
Response: ❌ DO NOT chain tasks together
         ✅ Explain one-at-a-time limitation (see template above)
         ✅ Ask if they want to start with Task 1

Correct Usage Examples

Executing a single task:

"Use initiating-coverage skill, Task 1 for Tesla"
"Do Task 2 of initiating-coverage for Tesla"
"Run Task 3 for Tesla using the initiating-coverage skill"

Completing full report (requires 5 separate requests):

Request 1: "Do Task 1 for Tesla" → Complete → Deliver outputs
Request 2: "Do Task 2 for Tesla" → Complete → Deliver outputs
Request 3: "Do Task 3 for Tesla" → Complete → Deliver outputs
Request 4: "Do Task 4 for Tesla" → Complete → Deliver outputs
Request 5: "Do Task 5 for Tesla" → Complete → Deliver outputs

Task Execution Order

For a complete initiation report, tasks must be executed in separate user requests following this order:

Request 1: Task 1 - Company Research (independent)
           ↓ [User reviews outputs and requests next task]
Request 2: Task 2 - Financial Modeling (independent)
           ↓ [User reviews outputs and requests next task]
Request 3: Task 3 - Valuation Analysis (requires Task 2 output)
           ↓ [User reviews outputs and requests next task]
Request 4: Task 4 - Chart Generation (requires Tasks 2 & 3 outputs)
           ↓ [User reviews outputs and requests next task]
Request 5: Task 5 - Report Assembly (requires ALL previous task outputs)

Note: Tasks 1 and 2 can be run in any order. Tasks 3-5 have strict dependencies and must verify inputs before proceeding.

Task 1: Company Research

Purpose: Research company's business, management, competitive position, industry, and risks.

Prerequisites: ✅ None (fully independent)

• Company name or ticker symbol

Process:

1. Verify company name/ticker provided
2. Load detailed instructions from references/task1-company-research.md
3. Execute qualitative research workflow
4. Deliver research document

Output: Company Research Document (6,000-8,000 words)

• Company overview & history
• Management bios (300-400 words × 3-4 execs)
• Products & services analysis
• Industry overview
• Competitive analysis (5-10 competitors)
• TAM sizing
• Risk assessment (8-12 risks)

File name: [Company]_Research_Document_[Date].md

⚠️ DELIVER ONLY THIS 1 FILE. NO completion summaries, no extra documents.

⚠️ DO NOT TAKE SHORTCUTS:

• ✅ Write full 6,000-8,000 words (not summaries)
• ✅ Complete 300-400 word bios for ALL 3-4 executives
• ✅ Analyze ALL 5-10 competitors thoroughly
• ✅ Cover all 8-12 risks across 4 categories
• ❌ Do not abbreviate sections to save time
• ❌ Do not skip any required sections

Verification before proceeding: None required for this task.

Task 2: Financial Modeling

Purpose: Extract historical financials and build comprehensive Excel financial model with projections and scenarios.

Prerequisites: ⚠️ Verify before starting

• Required: Access to company financial data
  • For public companies: Latest 10-K from SEC EDGAR
  • For private companies: Financial statements or available estimates
  • OR: Pre-extracted historical financials provided by user
• Optional: Company research (Task 1) for business context

Input Verification:

BEFORE STARTING - Select approach:

Option A: Extract financials (most common)
- [ ] Have access to 10-K or financial statements?
- [ ] Ready to extract 3-5 years of data?

Option B: User provided pre-extracted financials
- [ ] Historical financials file received?
- [ ] Contains income statement, cash flow, balance sheet (3-5 years)?

Optional:
- [ ] Company research (Task 1) complete for context?

Process:

1. Verify access to financial data
2. Load detailed instructions from references/task2-financial-modeling.md
3. Step 1: Extract historical financials (if needed)
4. Step 2+: Build projection model with 6 essential tabs
5. Deliver Excel model

Output: Excel Financial Model (.xlsx)

• 6 essential tabs:
  1. Revenue Model - Product breakdown (20-30 rows) + Geography breakdown (15-20 rows)
  2. Income Statement - Full P&L with 40-50 line items, historical (3-5 years) + projected (5 years)
  3. Cash Flow Statement - Operating/Investing/Financing activities, historical + projected
  4. Balance Sheet - Assets/Liabilities/Equity, historical + projected
  5. Scenarios - Bull/Base/Bear comparison table
  6. DCF Inputs - Prepared for Task 3 valuation

File name: [Company]_Financial_Model_[Date].xlsx

⚠️ DELIVER ONLY THIS 1 FILE. NO completion summaries, no extra documents.

⚠️ DO NOT TAKE SHORTCUTS:

• ✅ If extracting financials: Extract ALL line items from 3 financial statements (3-5 years)
• ✅ Build ALL 6 projection tabs completely with full detail
• ✅ Create detailed revenue model with 20-30 product rows AND 15-20 geography rows
• ✅ Build complete income statement with 40-50 line items (not abbreviated)
• ✅ Include full cash flow statement and balance sheet with all line items
• ✅ Complete ALL three scenarios (Bull/Base/Bear) with different parameters
• ❌ Do not create simplified/abbreviated versions
• ❌ Do not skip any of the 6 essential tabs
• ❌ Do not skip historical financials extraction if needed

Verification before proceeding to Task 3:

• Historical financials extracted (if needed) or provided
• Excel file created and can be opened
• Model has all 6 essential tabs (Revenue Model, Income Statement, Cash Flow, Balance Sheet, Scenarios, DCF Inputs)
• Historical data (3-5 years) incorporated
• Projections complete (5 years forward)
• Scenarios complete (Bull/Base/Bear)

Task 3: Valuation Analysis

Purpose: Perform comprehensive valuation using DCF, comparables, and precedent transactions.

Prerequisites: ⚠️ Verify before starting

• Required: Financial model from Task 2
  • Projected income statements
  • Projected cash flows
  • Revenue and EBITDA forecasts
  • DCF inputs (unlevered FCF)

⚠️ CRITICAL: DO NOT START THIS TASK UNLESS TASK 2 IS COMPLETE

This task requires the financial model from Task 2. Starting without it will result in incomplete work.

IF TASK 2 IS NOT COMPLETE: Stop immediately and inform the user that Task 2 (Financial Modeling) must be completed first. Do not attempt to proceed or create placeholder valuations.

Input Verification:

BEFORE STARTING:
- [ ] Task 2 complete? (Financial model exists)
- [ ] Model file path/location known?
- [ ] Can access projected financials from model?

Required from model:
- [ ] Projected FCF (5 years)
- [ ] Revenue projections
- [ ] EBITDA projections
- [ ] Terminal year metrics

Process:

1. Verify financial model is accessible
2. Load detailed instructions from references/task3-valuation.md
3. Execute valuation workflow
4. Deliver valuation analysis

Output: Valuation Analysis (4-6 pages + Excel tabs)

• DCF analysis with sensitivity tables
• Comparable companies (5-10 peers with statistical summary)
• Precedent transactions (if applicable)
• Valuation football field
• Price target: $XX.XX
• Recommendation: BUY/HOLD/SELL
• Upside: XX%
• Key catalysts (3-5)

Files:

• [Company]_Valuation_Analysis_[Date].md (written analysis document)
• Excel tabs added to [Company]_Financial_Model_[Date].xlsx (from Task 2)
  • DCF tab with calculations
  • Sensitivity analysis tab
  • Comparable companies tab
  • Valuation summary tab

⚠️ DELIVER ONLY: 1 markdown file + 4 tabs added to existing Excel. NO completion summaries, no extra documents.

⚠️ DO NOT TAKE SHORTCUTS:

• ✅ Complete full DCF analysis with sensitivity matrix (not simplified)
• ✅ Analyze ALL 5-10 comparable companies with full data
• ✅ Include statistical summary in comps table (max/75th/median/25th/min)
• ✅ Create complete sensitivity analysis tab with multiple WACC and terminal growth scenarios
• ✅ Write full 4-6 pages of valuation analysis (not abbreviated)
• ✅ Research and justify price target with specific methodology
• ❌ Do not skip comparable company analysis
• ❌ Do not create simplified DCF without sensitivity

Verification before proceeding to Task 4:

• Price target determined
• Valuation uses multiple methods (DCF + Comps minimum)
• DCF sensitivity table complete
• Comparable companies table includes statistical summary

Task 4: Chart Generation

Purpose: Generate 25-35 professional financial charts for the report.

Prerequisites: ⚠️ Verify before starting

• Required: Company research from Task 1
  • Company history and milestones (for timeline charts)
  • Management team and org structure (for org charts)
  • Product portfolio (for product charts)
  • Customer segmentation (for customer charts)
  • Competitive landscape (for competitive charts)
  • TAM analysis (for market size charts)
• Required: Financial model from Task 2 (with Task 3 valuation tabs added)
  • Revenue by product/geography data (Task 2 tabs)
  • Margin trends (Task 2 tabs)
  • Scenario comparison data (Task 2 tabs)
  • DCF sensitivity table (Task 3 tab in same Excel file)
  • Comparable companies data (Task 3 tab in same Excel file)
  • Valuation ranges (Task 3 tab in same Excel file)
• Required: External market data
  • Historical stock price data (Yahoo Finance, Bloomberg, etc.)
  • Historical valuation multiples (for historical trend charts)

⚠️ CRITICAL: DO NOT START THIS TASK UNLESS TASKS 1, 2, AND 3 ARE COMPLETE

This task requires outputs from all three previous tasks. Starting without them will result in incomplete charts.

IF ANY OF TASKS 1, 2, OR 3 ARE NOT COMPLETE: Stop immediately and inform the user which tasks need to be completed first. The specific requirements are:

• Task 1: Company research document (for 9 charts)
• Task 2: Financial model with all 6 tabs (for 8 charts)
• Task 3: Valuation tabs added to the model (for 6 charts)
• External data access (for 2 charts)

Do not attempt to create placeholder charts or skip charts due to missing data.

Input Verification:

BEFORE STARTING:
- [ ] Task 1 complete? (Company research exists)
- [ ] Task 2 complete? (Financial model exists)
- [ ] Task 3 complete? (Valuation analysis exists)
- [ ] Can access external market data sources?

Required from Task 1:
- [ ] Company history and milestones (for charts 05, 06)
- [ ] Management team structure (for chart 07)
- [ ] Product portfolio details (for chart 08)
- [ ] Customer segmentation data (for chart 09)
- [ ] Competitive landscape analysis (for charts 16, 17, 18)
- [ ] TAM sizing and market data (for chart 15)

Required from Task 2:
- [ ] Revenue by product (historical + projected) - for chart 03 ⭐
- [ ] Revenue by geography (historical + projected) - for chart 04 ⭐
- [ ] Income statement with margins (for charts 02, 10, 11)
- [ ] Cash flow statement (for chart 12)
- [ ] Scenario comparison data (for chart 14)

Required from Task 3:
- [ ] DCF sensitivity matrix - for chart 28 ⭐
- [ ] DCF components (for chart 29)
- [ ] Comparable companies data (for charts 30, 31)
- [ ] Valuation ranges - for chart 32 ⭐

Required from External Sources:
- [ ] Historical stock price data (for chart 01)
- [ ] Historical valuation multiples (for chart 34)

Process:

1. Verify model and valuation outputs are accessible
2. Load detailed instructions from references/task4-chart-generation.md
3. Execute chart generation workflow
4. Package all charts into a zip file
5. Deliver zip file

Output: 25-35 Professional Chart Files (PNG/JPG, 300 DPI) packaged in zip

4 MANDATORY Charts (must be present) ⭐:

• chart_03: Revenue by product (stacked area)
• chart_04: Revenue by geography (stacked bar)
• chart_28: DCF sensitivity (2-way heatmap)
• chart_32: Valuation football field (horizontal bars)

25 REQUIRED Charts (specific list):

• Investment Summary: chart_01
• Financial Performance: charts 02, 03⭐, 04⭐, 10, 11, 12, 14
• Company 101: charts 05, 06, 07, 08, 09, 15, 16
• Competitive/Market: charts 17, 18
• Scenario Analysis: chart 13
• Valuation: charts 28⭐, 29, 30, 31, 32⭐, 33, 34

10 OPTIONAL Charts (for 26-35 range):

• charts 19-27, 35 (customer acquisition, unit economics, product roadmap, etc.)

IMPORTANT: Task 5 embeds ALL charts created (25-35) for visual density (1 chart per 200-300 words).

File naming: chart_01_description.png, chart_02_description.png, etc.

Deliverable: [Company]_Charts_[Date].zip containing all 25-35 chart files + chart_index.txt

⚠️ DELIVER ONLY THIS 1 ZIP FILE. NO completion summaries, no separate chart lists, no extra documents.

⚠️ DO NOT TAKE SHORTCUTS:

• ✅ Create ALL 25 required charts minimum (specific list provided in task4-chart-generation.md)
• ✅ Include ALL 4 mandatory charts:
  • chart_03: Revenue by product (stacked area) ⭐
  • chart_04: Revenue by geography (stacked bar) ⭐
  • chart_28: DCF sensitivity (heatmap) ⭐
  • chart_32: Valuation football field ⭐
• ✅ Optional: Add 1-10 more charts to reach 26-35 total for greater visual density
• ✅ Generate professional-quality charts at 300 DPI (not low-res placeholders)
• ✅ Create unique, well-formatted charts for each visualization
• ✅ Package all charts in zip file with chart index
• ❌ Do not create only 10-15 charts (minimum is 25)
• ❌ Do not skip any of the 4 mandatory charts
• ❌ Do not use low-quality/placeholder images

Verification before proceeding to Task 5:

• Minimum 25 chart files created (required)
• All 4 mandatory charts present:
  • chart_03: Revenue by product ⭐
  • chart_04: Revenue by geography ⭐
  • chart_28: DCF sensitivity ⭐
  • chart_32: Valuation football field ⭐
• All charts open and display correctly
• Charts saved at 300 DPI (print quality)
• Chart index created listing all files with categories
• All charts packaged in zip file
• File naming follows convention: chart_##_description.png

Task 5: Report Assembly

Purpose: Write and assemble the comprehensive final DOCX report.

Prerequisites: ⚠️ Verify before starting

• Required: Company research from Task 1
  • All 6-8K words of content
  • Management bios
  • Competitive analysis
  • Risk assessment
• Required: Financial model from Task 2
  • Excel workbook
  • All projections and scenarios
• Required: Valuation analysis from Task 3
  • Price target and recommendation
  • DCF, comps, precedent transactions
  • All valuation data
• Required: Chart files from Task 4
  • Zip file containing all 25-35 PNG/JPG files
  • Chart index included in zip

⚠️ CRITICAL: DO NOT START THIS TASK UNLESS ALL TASKS 1-4 ARE COMPLETE

This is the final assembly task. It cannot be completed without all previous work products.

IF ANY OF TASKS 1, 2, 3, OR 4 ARE NOT COMPLETE: Stop immediately and inform the user which tasks need to be completed first. The specific requirements are:

• Task 1: Company research document (6-8K words)
• Task 2: Financial model with all 6 tabs
• Task 3: Valuation analysis with price target and recommendation
• Task 4: Charts zip file with 25-35 charts

Do not attempt to create placeholder content, substitute missing sections, or assemble an incomplete report. The report requires ALL inputs to be publication-ready.

Input Verification:

BEFORE STARTING - ALL TASKS MUST BE COMPLETE:

Task 1 Verification:
- [ ] Company research document exists? (6-8K words)
- [ ] Management bios complete? (300-400 words × 3-4 execs)
- [ ] Competitive analysis complete? (5-10 competitors)
- [ ] Risk assessment complete? (8-12 risks)

Task 2 Verification:
- [ ] Financial model exists and can be opened?
- [ ] Model has projections (5 years)?
- [ ] Scenarios exist (Bull/Base/Bear)?

Task 3 Verification:
- [ ] Valuation analysis complete?
- [ ] Price target determined?
- [ ] Recommendation set? (BUY/HOLD/SELL)
- [ ] DCF and comps complete?

Task 4 Verification:
- [ ] Chart zip file exists?
- [ ] Can extract/access all 25-35 chart files from zip?
- [ ] All 4 mandatory charts present?
  - [ ] Revenue by product (stacked area)
  - [ ] Revenue by geography (stacked bar)
  - [ ] DCF sensitivity (heatmap)
  - [ ] Valuation football field
- [ ] Chart files accessible and can be opened?

IF ANY VERIFICATION FAILS: Stop and complete missing task first.

Process:

1. CRITICAL: Verify ALL prerequisites before starting
2. Load detailed instructions from references/task5-report-assembly.md
3. Execute report assembly workflow using Claude's built-in skills:
   • Use DOCX skill to create and manipulate the Word document
   • Use XLSX skill to read Excel data from Task 2/3
   • Use Read tool to read Task 1 and Task 3 markdown files
   • Read Task 1 .md file → Convert to Word formatting → Insert charts inline
   • Read Task 2 .xlsx file → Extract tables → Write quantitative analysis
   • Read Task 3 .md file + Excel tabs → Copy/adapt valuation analysis
   • Insert Task 4 .png chart files throughout using DOCX skill
   • Create text-dense report with charts interspersed every 200-300 words
4. Save and deliver final DOCX report

Key Principles:

• Use Claude's DOCX and XLSX skills (NOT Python libraries)
• Use actual file operations (read .md/.xlsx/.png files, write .docx file)
• Good equity research reports are text-dense with lots of illustrating images (60-80% page coverage, 1+ chart per page)

🔥 CRITICAL: GO ALL OUT ON THIS TASK

THIS IS THE FINAL DELIVERABLE. DO NOT TAKE SHORTCUTS.

• ✅ Use full token budget - This is the culmination of all previous work
• ✅ Write every section completely - Do not summarize or abbreviate
• ✅ Hit ALL minimum requirements - 30+ pages, 10,000+ words, 25+ charts, 12+ tables
• ✅ Be thorough on projection assumptions - 2,000-3,000 words with product-by-product detail
• ✅ Be comprehensive on scenarios - 1,500-2,000 words with specific Bull/Base/Bear parameters
• ✅ Insert ALL charts from Task 4 - Not just a few, ALL 25-35 charts throughout
• ✅ Create ALL tables from Task 2/3 - Extract every financial table, don't skip any
• ✅ Use Task 1 content verbatim - Copy/paste full Company 101 sections (6-8K words)
• ✅ Professional quality only - This must be indistinguishable from JPMorgan/Goldman Sachs research

NEVER:

• ❌ "This section would include..." - WRITE THE ACTUAL SECTION
• ❌ "Charts would be inserted here..." - INSERT THE ACTUAL CHARTS
• ❌ "See financial model for details..." - EXTRACT AND INCLUDE THE DETAILS
• ❌ Skip sections due to length - Every section MUST be complete
• ❌ Abbreviate for token conservation - Use whatever tokens are needed

This is publication-ready institutional research. Spare no effort, tokens, or detail.

Output: Comprehensive Equity Research Report (.docx)

Specifications:

• Length: 30-50 pages (MINIMUM 30)
• Word count: 10,000-15,000 words (MINIMUM 10,000)
• Charts: 25-35 embedded images
• Tables: 12-20 comprehensive tables
• Format: Professional DOCX with clickable hyperlinks

Structure:

• Page 1: Investment Summary (INITIATING COVERAGE format)
• Pages 2-5: Investment thesis & risks
• Pages 6-17: Company 101
• Pages 18-30: Financial analysis & projections
• Pages 31-40: Valuation analysis
• Pages 41-50: Appendices

File name: [Company]_Initiation_Report_[Date].docx

⚠️ DELIVER ONLY THIS 1 DOCX FILE. NO executive summaries, no "highlights" documents, no extra files.

Final Verification:

• Report is 30-50 pages
• Word count is 10,000-15,000
• 25-35 charts embedded
• 12-20 tables included
• All citations are clickable hyperlinks
• Numbers match financial model exactly

Input Verification Protocol

Why Input Verification Matters

Each task has strict prerequisites. Running a task without required inputs will:

• Waste time with incomplete work
• Produce low-quality outputs
• Require rework

Always verify inputs before starting a task.

Verification Checklist by Task

Task 1: ✅ No verification needed (independent)

Task 2: Check historical financials available

Task 3: Check financial model exists and is accessible

Task 4: Check both model (Task 2) AND valuation (Task 3) exist

Task 5: Check ALL previous tasks complete (1, 2, 3, 4)

What to Do if Verification Fails

IF prerequisites are missing:
1. Stop the current task
2. Identify which prerequisite task(s) need to be completed
3. Execute the missing task(s) first
4. Return to current task once prerequisites are met

Example Verification Dialogue

User: "Use initiating-coverage, Task 5 for Tesla"

Claude: "Starting Task 5: Report Assembly for Tesla

VERIFICATION - Checking prerequisites:
- Task 1 (Company research)... ❌ NOT FOUND
- Task 2 (Financial model)... ❌ NOT FOUND
- Task 3 (Valuation)... ❌ NOT FOUND
- Task 4 (Charts)... ❌ NOT FOUND

⚠️ VERIFICATION FAILED

Task 5 requires ALL previous tasks to be complete.

To proceed:
1. Complete Task 1: Company Research
2. Complete Task 2: Financial Modeling
3. Complete Task 3: Valuation Analysis
4. Complete Task 4: Chart Generation
5. Then return to Task 5

Would you like to start with Task 1 first?"

Task Reference Files

Detailed instructions for each task are in separate reference files to keep this skill lean:

• references/task1-company-research.md - Company research workflow
• references/task2-financial-modeling.md - Financial modeling workflow
• references/task3-valuation.md - Valuation methodology
  • Also see: references/valuation-methodologies.md for DCF/comps deep dive
• references/task4-chart-generation.md - Chart generation workflow
• references/task5-report-assembly.md - Report writing workflow
  • Also see: assets/report-template.md for report structure
  • Also see: assets/quality-checklist.md for quality checks

When to load reference files: Load ONLY the reference file associated with the specific task being performed. These files are very large - do not load multiple reference files at once. Read the appropriate task reference file at the start of the task for detailed step-by-step instructions.

Quality Standards

All outputs meet institutional standards from leading investment banks (JPMorgan, Goldman Sachs, Morgan Stanley):

• Comprehensive: Meet all minimum requirements
• Detailed: Specific data and examples, not generic statements
• Quantified: Lead with numbers and metrics
• Cited: Proper sources with clickable hyperlinks
• Professional: Institutional-quality formatting
• Accurate: All numbers verified and cross-checked

Important Notes

Task Independence

• Task 1 can run anytime (no dependencies)
• Task 2 can run anytime (just needs historical data)
• Tasks 1 & 2 can run in parallel
• Task 3 requires Task 2
• Task 4 requires Tasks 2 & 3
• Task 5 requires Tasks 1, 2, 3, & 4

Session Management

Same session: Outputs automatically available to subsequent tasks

Different sessions: Reference previous task outputs explicitly

"Use Task 3 with the model from yesterday at [path]"
"Use Task 5 with the research document at [path]"

File Organization

Recommended structure during workflow:

ProjectFolder/
├── Task1_Research/
│   └── [Company]_Research_Document.md
├── Task2_Model/
│   └── [Company]_Financial_Model.xlsx
├── Task3_Valuation/
│   └── [Company]_Valuation_Analysis.pdf
├── Task4_Charts/
│   ├── chart_01.png
│   └── ... (25-35 files)
└── Task5_Report/
    └── [Company]_Initiation_Report.docx

No End-to-End Execution

This skill does NOT support running all tasks automatically in sequence. Each task must be explicitly requested and verified.

Why: This ensures:

• Quality control at each stage
• Ability to review outputs before proceeding
• Flexibility to pause/resume workflow
• Clear verification of prerequisites

Success Criteria

A successful initiation report workflow should:

1. Complete all 5 tasks in order
2. Pass all input verifications
3. Meet all quality standards
4. Produce all required deliverables
5. Numbers cross-check between outputs
6. Final report is publication-ready

Output quality: Institutional (JPMorgan/Goldman/Morgan Stanley level) Use case: First-time comprehensive coverage of a company

Inline Widget

Render interactive HTML/SVG widgets directly inside the chat conversation using ShowWidget. Widgets appear inline between text — no sandbox, no preview URL, no side panel. They run JavaScript, so lean into making them interactive and explorable where it helps — something the user can sort, filter, toggle, and hover over, not just a static picture.

When to Use

• User wants a quick visualization embedded in the conversation (chart, metric card, data table)
• The visualization is self-contained — all data is embedded in the HTML, no server needed
• User wants interactivity within the chat: buttons, toggles, hover effects, animated charts
• The output is a single view — not a multi-page app or dashboard that needs routing

Use interactive-dashboard instead if: User needs a multi-page web app, server-side data, live data refresh, or complex interactivity requiring React/FastAPI.

Read .agents/skills/ui-design/SKILL.md for design quality — its color discipline, chart restraint, and anti-slop principles apply here too. Its font pairings and type scale, though, are for full documents; a widget sits on the chat surface, so use the host-font typography rules below instead.

ShowWidget API

ShowWidget(html: str, title: str | None = None, data_files: list[str] | None = None)

• html: Raw HTML fragment — no <!DOCTYPE>, <html>, <head>, or <body> tags
• title: Optional metadata (not displayed to user)
• data_files: Optional list of sandbox file paths to make available as window.__WIDGET_DATA__

The HTML is rendered in a sandboxed iframe with:

• CDN libraries: cdnjs.cloudflare.com, cdn.jsdelivr.net, unpkg.com, esm.sh
• CSS theme variables: automatically injected (see Theme section)
• sendPrompt('text'): global function to trigger follow-up chat messages
• window.__WIDGET_DATA__: dict of filename→content for files passed via data_files
• No network to non-CDN origins: fetch() / XMLHttpRequest to arbitrary URLs are blocked by CSP — only CDN domains (cdnjs, jsdelivr, unpkg, esm.sh) are allowed. Use data_files for sandbox files, or embed small data directly in HTML

Layout Rules (CRITICAL)

The widget sits directly on the chat surface inside a transparent iframe. Follow these rules for seamless integration:

Outer Element — Transparent Shell

The outermost HTML element must have:

• NO background (or background: transparent)
• NO border
• NO border-radius
• NO box-shadow
• NO padding — add padding on inner sections only

<!-- CORRECT: transparent outer shell -->
<div>
  <div style="background: var(--color-bg-card); border-radius: 8px; padding: 16px; ...">
    ...inner card content...
  </div>
</div>

<!-- WRONG: styled outer wrapper — will be rejected -->
<div style="background: var(--color-bg-page); border: 1px solid ...; border-radius: 8px; padding: 20px;">
  ...content...
</div>

Inner Elements — Use Theme Variables

Inner cards, sections, and components should use CSS variables for styling:

/* Card */
background: var(--color-bg-card);
border: 0.5px solid var(--color-border-muted);
border-radius: 8px;
padding: 16px;

/* Metric card */
background: var(--color-bg-subtle);
border: 0.5px solid var(--color-border-muted);
border-radius: 8px;

Positioning

• NO position: fixed — breaks iframe auto-sizing (elements collapse to 0 height)
• Use position: relative for chart containers
• No nested scrolling — the iframe auto-sizes to fit all content

Theme Variables

These CSS variables are automatically injected and resolve correctly in both light and dark mode:

Never hardcode colors like #333 or rgb(...) for text, backgrounds, or borders — they break in dark mode. Use CSS variables for everything except chart canvas colors (Chart.js canvas cannot read CSS variables — use computed hex values via getComputedStyle).

Charts (Chart.js)

Load Chart.js from CDN and follow these rules:

<!-- Wrapper div with explicit height — REQUIRED -->
<div style="position: relative; height: 200px;">
  <canvas id="myChart"></canvas>
</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
  // Read CSS variables for chart colors (canvas can't use var())
  var cs = getComputedStyle(document.documentElement);
  var accent = cs.getPropertyValue('--color-accent-primary').trim();
  var border = cs.getPropertyValue('--color-border-muted').trim();

  new Chart(document.getElementById('myChart'), {
    type: 'line',
    data: {
      labels: [...],
      datasets: [{
        data: [...],
        borderColor: accent,
        backgroundColor: accent + '20',
        tension: 0.4,
        fill: true
      }]
    },
    options: {
      responsive: true,
      maintainAspectRatio: false,
      plugins: { legend: { display: true } },
      scales: {
        y: { grid: { color: border } },
        x: { grid: { display: false } }
      }
    }
  });
</script>

Key rules:

• Set height on the wrapper div, never on the canvas
• Always use responsive: true, maintainAspectRatio: false
• Use UMD build from CDN (sets window.Chart global)
• Read CSS variables via getComputedStyle for chart colors

Typography

• Font: inherited from host (-apple-system, BlinkMacSystemFont, 'Segoe UI', sans-serif)
• Weight: favor 400 (regular) and 500 (medium); use 600 sparingly for a key figure or heading. Avoid 700 — it reads heavy on the chat surface
• Heading sizes: h1 = 22px, h2 = 18px, h3 = 16px (all weight 500)
• Body: 14-16px, weight 400
• Use sentence case — no Title Case or ALL CAPS (except short metric labels)

Interactivity

Make it explorable

A widget runs JavaScript, so prefer something the user can poke at, not just read. When the data supports it, reach for:

• Sortable / filterable tables — click a header to sort by P&L or weight; filter to a sector or watchlist.
• Series & metric toggles — show/hide chart series, switch price ↔ % change, flip timeframe (1M / 6M / 1Y).
• Hover detail — tooltips on chart points and table rows that surface the underlying numbers.
• What-if inputs — a slider or field that recomputes a figure live (drag a growth rate, watch the projection update).
• Tabs / segmented views — split a dense widget (Overview / Holdings / Performance) so the reader drills in.

It all runs client-side over the embedded data — never fetch() a non-CDN origin (CSP blocks it; use data_files for anything large). Keep a meaningful default state so the widget reads correctly before any interaction. Use sendPrompt() only when the next step genuinely belongs back in the chat (a new query, a deeper analysis) — handle exploration the widget can do itself in-place.

sendPrompt()

Call sendPrompt('text') from buttons to trigger a follow-up chat message:

<button onclick="sendPrompt('Show detailed revenue breakdown')"
        style="padding: 8px 16px; background: var(--color-accent-primary); color: white;
               border: none; border-radius: 6px; cursor: pointer;">
  Revenue Details ↗
</button>

Add a ↗ arrow on buttons that call sendPrompt() to signal they trigger a chat action.

Refresh / Animation

setInterval and requestAnimationFrame work normally for animations and live tickers:

setInterval(function() {
  // Update prices, rotate data, animate
  updateDisplay();
}, 3000);

File Data

Use data_files to load data from sandbox files instead of inlining everything in the HTML string. This is especially useful for larger datasets.

Workflow

1. Generate data files via Python
2. Pass file paths to ShowWidget via data_files
3. Access data in the widget via window.__WIDGET_DATA__["filename"]

# Step 1: Generate data
import json
data = {"labels": ["Q1", "Q2", "Q3"], "values": [100, 150, 200]}
with open("work/<task_name>/chart_data.json", "w") as f:
    json.dump(data, f)

# Step 2: Agent calls ShowWidget with data_files
ShowWidget(
    html='<div id="chart">...</div><script>var d = JSON.parse(__WIDGET_DATA__["chart_data.json"]); ...</script>',
    data_files=["work/<task_name>/chart_data.json"]
)

Widget access

// Text files (json, csv, txt, etc.) — returned as strings
var data = JSON.parse(window.__WIDGET_DATA__["chart_data.json"]);
var csvText = window.__WIDGET_DATA__["results.csv"];

// Binary files (png, jpg, etc.) — returned as data URLs
document.getElementById("img").src = window.__WIDGET_DATA__["chart.png"];

Supported file types

• Text (returned as strings): .json, .csv, .txt, .html, .xml, .svg, .md, .yaml, .yml, .tsv, .geojson, .topojson
• Binary (returned as data URLs): .png, .jpg, .jpeg, .gif, .webp, .ico

Size limits

Total inline data is capped at 500KB across all files. Keep datasets concise — aggregate or sample large files before passing them.

Blocked Patterns

The following will cause ShowWidget to reject your HTML with an error. Fix and retry:

Design Patterns

Metric Cards Row

<div style="display: grid; grid-template-columns: repeat(auto-fit, minmax(150px, 1fr)); gap: 12px; margin-bottom: 16px;">
  <div style="background: var(--color-bg-subtle); padding: 14px 16px; border-radius: 8px; border: 0.5px solid var(--color-border-muted);">
    <div style="font-size: 11px; color: var(--color-text-tertiary); text-transform: uppercase; letter-spacing: 0.06em; margin-bottom: 6px;">Revenue</div>
    <div style="font-size: 24px; font-weight: 500;">$2.4M</div>
    <div style="font-size: 12px; color: var(--color-profit);">+12.5%</div>
  </div>
  <!-- more cards... -->
</div>

Data Table

<table style="width: 100%; border-collapse: collapse; font-size: 14px;">
  <thead>
    <tr style="border-bottom: 0.5px solid var(--color-border-muted);">
      <th style="text-align: left; padding: 8px; color: var(--color-text-secondary); font-weight: 500; font-size: 12px;">Symbol</th>
      <th style="text-align: right; padding: 8px; color: var(--color-text-secondary); font-weight: 500; font-size: 12px;">Price</th>
    </tr>
  </thead>
  <tbody>
    <tr style="border-bottom: 0.5px solid var(--color-border-muted);">
      <td style="padding: 8px; font-weight: 500;">AAPL</td>
      <td style="text-align: right; padding: 8px;">$213.18</td>
    </tr>
  </tbody>
</table>

Section with Chart

<div style="background: var(--color-bg-card); border-radius: 8px; border: 0.5px solid var(--color-border-muted); padding: 16px; margin-bottom: 16px;">
  <div style="font-size: 16px; font-weight: 500; margin-bottom: 12px;">Performance</div>
  <div style="position: relative; height: 200px;">
    <canvas id="perfChart"></canvas>
  </div>
</div>

Interactive Dashboard

Build interactive web dashboards inside the sandbox and expose them to the user via GetPreviewUrl. Use this skill for any request involving dashboards, trackers, monitors, live visualizations, or interactive web apps.

When to Use

Use this skill for a live, served web app — one that needs a running server, not a single file:

• User asks for a dashboard, tracker, or monitor that refreshes live data (polling, auto-update)
• The app needs server-side logic — filtering/screening over a large dataset, on-demand fetches, computed endpoints
• Multi-page / routed apps, or anything that needs React-level component interactivity
• The dataset is too large to embed in a single HTML file
• User explicitly says "preview", "web view", "web app", or wants it running at a URL

Do NOT use if:

• User wants a self-contained HTML report — even an interactive one (sortable tables, tabs, hover/zoom charts) over a data snapshot. That's .agents/skills/html-report/SKILL.md: one file in results/, keepable, printable, PDF-exportable, share-linkable. Interactivity by itself does not require a dashboard.
• User wants a static chart image → matplotlib/plotly savefig.
• User wants an in-chat figure → inline-widget (ShowWidget).

Dashboard vs. HTML Report

Both can be interactive, so the divide is live served app vs. self-contained snapshot file, not static vs. interactive:

Architecture

Choose the tier based on complexity:

Decision rule: Start with Simple. Escalate to FastAPI + HTML when user needs live data refresh or server-side logic. Escalate to Complex only when user needs React-level component interactivity, client-side routing, or a multi-page SPA.

Port convention: Use port 8050 (default). Range 8050-8059 for dashboards.

CSP / Iframe Safety

The preview iframe enforces Content Security Policy (CSP). Certain patterns are silently blocked — no error banner, just dead UI elements. Always use the safe alternatives:

Quick self-check — run before serving to catch violations:

import subprocess
result = subprocess.run(
    ["grep", "-rnE", r'on(click|input|change|focus|blur|submit|load|error|mouse|key)\s*=',
     "work/dashboard/"],
    capture_output=True, text=True
)
if result.stdout.strip():
    raise RuntimeError(f"CSP-unsafe inline handlers found:\n{result.stdout}")

Template literal hygiene — when building HTML strings in JS template literals, CSS semicolons inside ${} expressions cause silent parse failures:

// BAD — semicolon inside ${} terminates the expression early
const el = `<div style="color:${positive ? 'green' : 'red'; font-weight:600}">`;

// GOOD — close the expression first, then continue the attribute string
const el = `<div style="color:${positive ? 'green' : 'red'};font-weight:600">`;

Rule: never put a CSS semicolon inside ${} — always close } before the semicolon.

How Preview Serving Works

GetPreviewUrl is a platform-level tool available only to the main agent runtime. It is NOT a Python function — do not import it or call it from execute_code. The agent invokes it as a tool call.

When you call GetPreviewUrl(port, command, title):

1. The command is persisted to the database automatically
2. The platform starts the command in a dedicated sandbox session for that port
3. It polls until the port is listening, then generates a signed URL
4. If the port is already reachable, the command start is skipped entirely

Sub-agent fallback: Sub-agents cannot call GetPreviewUrl. Instead, build the dashboard files, start the server for verification, then return the serve details so the orchestrating agent can call GetPreviewUrl.

All tiers — use the Bash tool with run_in_background=true to start the server:

# Simple tier — Bash tool with run_in_background=true
cd work/<task> && python -m http.server 8050 --bind 0.0.0.0

# Docker tiers — Bash tool with run_in_background=true
cd work/<task> && bash start.sh

Then verify it's up in a separate (foreground) Bash call:

for i in $(seq 1 15); do curl -sf http://127.0.0.1:8050/ > /dev/null && echo "Server ready" && exit 0 || sleep 1; done; echo "FAIL"; exit 1

Then return all three fields to the orchestrating agent (it needs the command for DB persistence / restart recovery):

port: 8050
command: "cd work/<task> && python -m http.server 8050 --bind 0.0.0.0"  # or "bash work/<task>/start.sh"
title: "AAPL Stock Dashboard"

The orchestrating agent calls GetPreviewUrl(port=8050, command="...", title="...") which persists the command.

On workspace restart (user closes and reopens later):

• The sandbox filesystem persists (files, installed packages, Docker image cache all survive)
• Only processes die — the platform looks up the saved command and re-executes it
• The preview URL auto-recovers

Implication: Write commands that are idempotent — they must work whether run for the first time or re-run after a restart. The platform handles the rest. Docker image cache survives restart so rebuilds are fast (~2-5s with warm cache).

Sandbox Capabilities

All pre-installed in the Daytona sandbox snapshot — no pip install or apt-get needed:

• Python 3.12 + pandas, numpy, plotly, matplotlib, requests, httpx, yfinance
• FastAPI + uvicorn (available via fastmcp transitive dependency)
• Node.js 24 + npm (host sandbox) / Node.js 20 (Docker apt install nodejs) — scaffold Vite/React projects with npm create vite@latest
• Docker Engine — for complex tier containerized dashboards (backend + frontend in one image)
• Playwright + Chromium — available for verification testing

Workflow

Step 1: Clarify Scope

Before writing any code:

• What data? (specific tickers, sector, portfolio, screener results)
• What visualizations? (price chart, comparison table, heatmap, etc.)
• Static snapshot or live refresh?
• How complex? (determines simple vs complex tier)

Step 2: Fetch Data

Use YF MCP servers as the default financial data source (no API keys needed):

from tools.yf_price import get_stock_history, get_multiple_stocks_history
from tools.yf_fundamentals import get_company_info, compare_valuations
from tools.yf_analysis import get_analyst_price_targets, get_news
from tools.yf_market import get_sector_info, screen_stocks

Always fetch and validate data before writing any HTML/React code. Check for empty responses.

Step 3: Process Data

Use pandas to clean, aggregate, and compute derived metrics:

import pandas as pd
import json

# Fetch
history = get_stock_history("AAPL", period="1y", interval="1d")
info = get_company_info("AAPL")

# Process
df = pd.DataFrame(history)
df['change_pct'] = df['close'].pct_change() * 100

# Prepare for frontend
chart_data = json.dumps({
    "dates": df['date'].tolist(),
    "prices": df['close'].tolist(),
    "volumes": df['volume'].tolist(),
})

Step 4: Build Dashboard

Simple tier — write a self-contained HTML file:

html = f"""<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>AAPL Dashboard</title>
    <script src="https://cdn.jsdelivr.net/npm/chart.js@4/dist/chart.umd.min.js"></script>
    <style>
        /* See references/ui-components.md for dark theme CSS */
    </style>
</head>
<body>
    <script>const DATA = {chart_data};</script>
    <script>
        /* Chart rendering code */
    </script>
</body>
</html>"""

with open("work/dashboard/index.html", "w") as f:
    f.write(html)

FastAPI + HTML tier — write a FastAPI server with API routes + StaticFiles mount, and a single static/index.html with fetch() polling (see FastAPI + HTML Tier section below).

Complex tier — scaffold a FastAPI + Vite/React project (see Complex Tier section below).

Step 5: Verify Before Serving

Tier 1 — Syntax check (required, < 1 second)

Extract <script> blocks from the HTML and check with node --check:

import re, subprocess, tempfile, os

with open("work/dashboard/index.html") as f:
    html = f.read()

scripts = re.findall(r'<script(?![^>]*src)[^>]*>(.*?)</script>', html, re.DOTALL)
for i, src in enumerate(scripts):
    with tempfile.NamedTemporaryFile(suffix=".js", mode="w", delete=False) as tmp:
        tmp.write(src)
        tmp_path = tmp.name
    result = subprocess.run(["node", "--check", tmp_path], capture_output=True, text=True)
    os.unlink(tmp_path)
    if result.returncode != 0:
        raise RuntimeError(f"JS syntax error in script block {i+1}:\n{result.stderr}")
print("Syntax check passed")

Also run the CSP self-check grep from the CSP section above.

Tier 2 — Browser verification (recommended for interactive dashboards)

Run when the dashboard has buttons, filters, or tabs. Skip for static data displays.

After GetPreviewUrl starts the server, run a Playwright check to catch runtime errors:

from playwright.sync_api import sync_playwright

with sync_playwright() as p:
    browser = p.chromium.launch()
    page = browser.new_page()
    js_errors = []
    page.on("pageerror", lambda exc: js_errors.append(str(exc)))
    page.goto("http://127.0.0.1:8050/", wait_until="networkidle", timeout=20000)
    assert not js_errors, f"JS runtime errors: {js_errors}"
    assert len(page.locator("body").inner_text().strip()) > 20, "Page appears blank"
    page.screenshot(path="work/dashboard/verify-screenshot.png", full_page=True)
    browser.close()
print("Browser verification passed")

See references/verification.md for extended templates with button-click testing and API response validation.

Step 6: Serve & Expose

# Simple tier
GetPreviewUrl(port=8050, command="cd work/dashboard && python -m http.server 8050 --bind 0.0.0.0", title="AAPL Dashboard")

# FastAPI + HTML tier / Complex tier
GetPreviewUrl(port=8050, command="bash work/dashboard/start.sh", title="Stock Dashboard")

Local verification before GetPreviewUrl — if you need the server running for Playwright verification, use the Bash tool with run_in_background=true (see sub-agent fallback above for the pattern). Do NOT use subprocess.Popen from execute_code — the process becomes a zombie when the tool-call shell exits.

Step 7: Iterate

After the user sees the preview, adjust layout, data, or charts based on feedback.

Data Integration — YF MCP Servers

Default data sources for common dashboard needs:

Yahoo Finance Field Conventions

Many fields are already in display units — do NOT multiply by 100:

get_predefined_screen Response Structure

Quotes are nested — not at the top level:

result = get_predefined_screen("day_gainers")
quotes = result["data"]["quotes"]  # nested at result["data"]["quotes"], NOT result["quotes"]
# Each quote: symbol, regularMarketPrice, regularMarketChangePercent, marketCap, ...

Direct yfinance Usage (Docker / without MCP)

Inside Docker containers, MCP tool modules are unavailable. Use yfinance directly:

import yfinance as yf

# Stock screener (yfinance 1.2.0+)
result = yf.screen("day_gainers", count=5)
quotes = result.get("quotes", [])

# Stock data
ticker = yf.Ticker("AAPL")
info = ticker.info
hist = ticker.history(period="1y")

UI Design Rules

Read .agents/skills/ui-design/SKILL.md for design quality (typography, color, avoiding generic AI aesthetics).

Dark Theme (Default)

Match the Ginlix platform aesthetic:

Layout

• KPI cards in a row at top (price, change, volume, market cap)
• Charts in a responsive 2-column grid below
• Tables full-width at bottom
• No horizontal scroll — everything fits the iframe width
• Use CSS Grid with auto-fit and minmax() for responsive columns

Typography

font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif;

Financial Data Formatting

• Prices: 2 decimal places with $ prefix ($182.52)
• Percentages: 2 decimal places with % suffix, color-coded green/red (+2.34% / -1.56%)
• Large numbers: Abbreviated with suffix ($2.87T, $142.5B, $3.2M)
• Volumes: Comma-separated (12,345,678) or abbreviated (12.3M)
• Dates: MMM DD, YYYY format (Mar 15, 2026)

See references/ui-components.md for complete CSS and component code.

FastAPI + HTML Tier — Project Structure

For live-data dashboards without React. FastAPI serves API endpoints and static HTML directly — no npm, no build step. Use the copy-ready template files with .fastapi-html suffix in references/:

work/<task>/
├── Dockerfile           # cp references/Dockerfile.fastapi-html Dockerfile
├── start.sh             # cp references/start.sh start.sh
├── server/
│   ├── main.py          # cp references/server-main.fastapi-html.py server/main.py
│   └── requirements.txt # cp references/requirements.txt server/requirements.txt
└── static/
    └── index.html       # Single HTML file with fetch() polling

Setup Workflow

1. Copy template files — all four cp commands above, then add your API routes to server/main.py
2. Add your Python deps to server/requirements.txt (append pandas, yfinance, etc.)
3. Write static/index.html with fetch() calls to your API routes for live data
4. Serve: GetPreviewUrl(port=8050, command="bash work/<task>/start.sh", title="Dashboard")

Template Files

Dockerfile (references/Dockerfile.fastapi-html) — Python 3.12-slim only (no Node/npm). Uses uv pip install for fast deps.

server/main.py (references/server-main.fastapi-html.py) — FastAPI skeleton with CORS, HEAD /, /healthz, and StaticFiles mount for static/ directory. StaticFiles is the last mount (catches all unmatched routes).

start.sh — same references/start.sh template (works unchanged for all Docker tiers).

Key Differences from Complex Tier

• No frontend/ directory — HTML lives in static/
• No npm/Node — Dockerfile uses python:3.12-slim only
• StaticFiles mount — serves static/ directory with html=True (auto-serves index.html)
• fetch() for live data — HTML uses setInterval(() => fetch('/api/data').then(...), 30000) for auto-refresh
• No SPA routing — single index.html, no client-side router needed

Complex Tier — Project Structure

When using FastAPI + Vite/React, scaffold this structure using the copy-ready template files in references/:

work/<task>/
├── Dockerfile           # Copy from references/Dockerfile
├── start.sh             # Copy from references/start.sh
├── server/
│   ├── main.py          # Copy from references/server-main.py, add your API routes
│   ├── requirements.txt # Copy from references/requirements.txt, add your deps
│   ├── routes/          # API route modules (stocks.py, sectors.py)
│   └── models.py        # Pydantic response models
├── frontend/
│   ├── package.json     # Vite + React + chart libraries
│   ├── vite.config.js   # Copy from references/vite.config.js
│   ├── index.html
│   └── src/
│       ├── App.jsx      # Main app with routing/tabs
│       ├── components/  # Chart, KPI, Table components
│       ├── hooks/       # useStockData, useSectorData, etc.
│       └── utils/       # formatters, color helpers
└── verify.py            # Copy from references/verification.md (optional)

Setup Workflow

1. Copy template files from references/ into work/<task>/ — they work with zero modifications for port 8050
2. Add your API routes to server/main.py (the template includes CORS, HEAD /, /healthz, and static file serving)
3. Add your Python deps to server/requirements.txt (template includes fastapi + uvicorn)
4. Write frontend code in frontend/src/ (vite.config.js template proxies /api to backend on port 8051)
5. Serve: GetPreviewUrl(port=8050, command="bash work/<task>/start.sh", title="Dashboard")

Template Files

Dockerfile (references/Dockerfile) — Python 3.12 + Node + uv + tzdata. Builds frontend at image time, serves static files from FastAPI. Uses uv pip install for fast dependency installation.

start.sh (references/start.sh) — Cold-boot safe Docker wrapper. Starts dockerd if needed, builds image (uses layer cache on re-runs), removes old container, starts new one, health-checks with log dump on failure. Env var overrides: PORT (default 8050), NAME (default "dashboard").

server/main.py (references/server-main.py) — FastAPI skeleton with CORS, HEAD / (liveness for platform proxy), /healthz, SPA catch-all route for client-side routing (/tab/news, /stocks/AAPL → index.html), and a 503 fallback if the frontend build is missing.

server/requirements.txt (references/requirements.txt) — Minimal: fastapi + uvicorn. Append project-specific packages (pandas, yfinance, etc.).

frontend/vite.config.js (references/vite.config.js) — Vite + React with /api proxy to backend on port 8051.

Critical: Vite proxy is dev-only. The proxy setting in vite.config.js only applies during npm run dev. The production build outputs plain static files with no proxy. In the Docker image, FastAPI serves both the built SPA from frontend/dist/ and all /api/* routes from the same port. The reference server-main.py template already does this correctly — do NOT use a two-process architecture with separate static file server and API server.

Docker Gotchas

• MCP tools are host-only: The tools/ modules (e.g., from tools.yf_price import ...) exist only in the host workspace Python environment — they are NOT copied into the Docker image. FastAPI server code inside Docker must call yfinance directly. Add yfinance to server/requirements.txt
• --network host: Required for the container to reach external APIs (yfinance, MCP servers). Already set in start.sh template
• tzdata: Required for yfinance timezone handling. Already in Dockerfile template
• Image cache: Persists across workspace restarts. First build: 30-60s. Subsequent builds: ~2-5s (cached layers)
• Logs: Use docker logs dashboard to debug startup failures
• Fallback without Docker: If Docker is unavailable, build the frontend and run FastAPI directly:

  fuser -k 8050/tcp 2>/dev/null || true
  cd frontend && npm install --prefer-offline && npm run build && cd ..
  cd server && uvicorn main:app --host 0.0.0.0 --port 8050
  

Chart Libraries

Simple Tier (CDN-loaded, no install)

Default to Chart.js. Use Plotly for candlesticks/heatmaps. Lightweight Charts only for TradingView-style.

Complex Tier (npm packages)

Default to Recharts. Use Plotly for advanced financial charts.

See references/chart-patterns.md for ready-to-use code snippets.

Common Dashboard Patterns

1. Single Stock Dashboard

Data: get_stock_history, get_company_info, get_analyst_price_targets, get_news

Layout:

• KPI row: current price, day change %, 52-week range, market cap, P/E
• Price chart (line/candlestick) with volume bars
• Analyst price target range (horizontal bar)
• Recent news list

2. Multi-Stock Comparison

Data: get_multiple_stocks_history, compare_valuations, compare_financials

Layout:

• Normalized price overlay chart (base 100)
• Performance bar chart (YTD, 1Y, 3Y returns)
• Valuation comparison table (P/E, EV/EBITDA, P/B, etc.)
• Revenue/earnings growth comparison

3. Sector Heatmap

Data: get_sector_info, screen_stocks with sector filters, get_predefined_screen

Layout:

• Treemap colored by daily/weekly performance
• Sector summary cards (top movers, average P/E)
• Top gainers/losers table
• Sector rotation chart

4. Earnings Tracker

Data: get_earnings_calendar, get_earnings_data, get_earnings_estimates

Layout:

• Calendar view with upcoming earnings dates
• Beat/miss history chart (bar chart with surprise %)
• EPS estimate vs actual trend line
• Revenue estimate revision chart

5. Portfolio Monitor

Data: get_multiple_stocks_history, compare_valuations, get_company_info for each holding

Layout:

• Holdings table (ticker, shares, price, value, weight, day P&L)
• Allocation pie chart (by sector/stock)
• Total portfolio value line chart
• Sector exposure bar chart

Best Practices

General

• Data-first: Fetch and validate ALL data before writing any HTML/React code
• Fail gracefully: If a ticker is invalid or API returns empty, show "No data available" — don't crash
• No console errors: Verify chart rendering works before calling GetPreviewUrl
• Responsive: CSS Grid auto-fit for layouts. No horizontal scroll at any width
• Performance: Resample data if > 1000 rows. Don't load unused chart libraries

Simple Tier

• Embed data as JSON: <script>const DATA = ${json.dumps(data)}</script> — never inline raw Python dicts
• Escape properly: Always use json.dumps() with ensure_ascii=False for safe JSON embedding
• Self-contained: All CSS in <style>, all JS in <script>, libraries via CDN <script src="...">
• One HTML file: Keep everything in a single index.html — eliminates path bugs

Complex Tier

• Separation of concerns: FastAPI = data API, Vite/React = UI rendering
• Pydantic models: Define response schemas for type safety
• Component per widget: One React component per chart/card/table
• Shared hooks: useStockData(ticker), useSectorData(key) for data fetching
• Error boundaries: Wrap chart components so one failure doesn't crash the whole page
• Single-port production: Vite proxy is dev-only. In production Docker builds, FastAPI serves both /api/* and the SPA from one port
• host: '0.0.0.0': Both FastAPI and Vite must bind to 0.0.0.0, not 127.0.0.1 or localhost

Error Handling & Debugging

Quality Checklist

Before calling GetPreviewUrl:

Data & Code

• All data fetched and validated (no empty dataframes or None values)
• Files written to work/<task>/ directory
• JSON data properly escaped with json.dumps()
• All chart containers exist in HTML before JS tries to reference them

CSP Safety

• No inline event handlers (onclick, oninput, onchange, etc.) — all events via addEventListener
• No eval(), new Function(), or string-based setTimeout()
• No javascript: URLs
• CSP self-check grep passes (no matches)

Verification

• Tier 1: JS syntax check passed (node --check on extracted script blocks)
• Tier 2: Playwright verification passed (for interactive dashboards with buttons/filters/tabs)

Serving

• Server binds to 0.0.0.0 (not 127.0.0.1 or localhost)
• Correct port used (default 8050)
• Command passed to GetPreviewUrl is idempotent (works on re-run after restart)
• Complex tier: start.sh and Dockerfile copied from templates
• Complex tier: FastAPI includes HEAD / endpoint (use server-main.py template)

UI Quality

• Dark theme applied consistently (see color table above)
• Responsive layout — no horizontal scroll
• Financial numbers properly formatted (currency, %, abbreviations)
• Title passed to GetPreviewUrl is descriptive (e.g., "AAPL Stock Dashboard", not "Preview")

Model Update

Workflow

Step 1: Identify What Changed

Determine the update trigger:

• Earnings release: New quarterly actuals to plug in
• Guidance change: Company updated forward outlook
• Estimate revision: Analyst changing assumptions based on new data
• Macro update: Interest rates, FX, commodity prices changed
• Event-driven: M&A, restructuring, new product, management change

Step 2: Plug New Data

After Earnings

Update the model with reported actuals:

Segment Detail (if applicable):

• Update each segment's revenue and margin
• Note any segment mix shifts

Balance Sheet / Cash Flow Updates:

• Cash and debt balances
• Share count (buybacks, dilution)
• Capex actual vs. estimate
• Working capital changes

Step 3: Revise Forward Estimates

Based on the new data, adjust forward estimates:

Key Assumption Changes:

• What assumptions are you changing and why?
• Revenue growth rate: old → new (reason)
• Margin assumption: old → new (reason)
• Any new items (restructuring charges, one-time gains, etc.)

Step 4: Valuation Impact

Recalculate valuation with updated estimates:

Step 5: Summary & Action

Estimate Change Summary:

• One paragraph: what changed, why, and what it means for the stock
• Is this a thesis-changing event or noise?

Rating / Price Target:

• Maintain or change rating?
• New price target (if changed) with methodology
• Upside/downside to current price

Step 6: Output

• Updated Excel model (if user provides the existing model)
• Estimate change summary (markdown or Word)
• Updated price target derivation

Important Notes

• Always reconcile your estimates to the company's reported figures before projecting forward
• Note any non-recurring items and whether your estimates are GAAP or adjusted
• Track your estimate revision history — it shows your analytical progression
• If the quarter was noisy, separate signal from noise in your estimate changes
• Check consensus after updating — how do your revised estimates compare to the Street?
• Share count matters — dilution from stock comp, converts, or buybacks can materially affect EPS

Morning Note

Triggers on "morning note", "morning meeting", "what happened overnight", "trade idea", "morning call prep", or "daily note".

Workflow

Step 1: Overnight Developments

Scan for relevant events across coverage universe:

Earnings & Guidance

• Use macro MCP: get_earnings_calendar(from_date, to_date) for companies reporting in date range
• Use get_company_overview tool for consensus estimates and analyst data
• Earnings surprises (beat/miss on revenue, EPS, key metrics)
• Guidance changes (raised, lowered, maintained)

News & Events

• Use WebSearch / WebFetch for market news
• M&A announcements or rumors
• Management changes
• Product launches or regulatory decisions
• Analyst upgrades/downgrades

Market Context

• Use get_sector_performance tool for sector-level daily performance
• Use get_stock_daily_prices tool for pre-market and overnight price moves
• Use macro MCP: get_economic_calendar(from_date, to_date) for upcoming macro events
• Relevant commodity or currency moves

Step 2: Morning Note Format

Keep it tight — a morning note should be readable in 2 minutes:

[Date] Morning Note — [Analyst Name] [Sector Coverage]

Top Call: [Headline — the one thing PMs need to hear]

• 2-3 sentences on the key development and why it matters
• Stock impact: price target, rating reiteration/change

Overnight/Pre-Market Developments

• [Company A]: One-line summary of earnings/news + our take
• [Company B]: One-line summary + our take
• [Sector/Macro]: Relevant sector-wide development

Key Events Today

• [Time]: [Company] earnings call
• [Time]: Economic data release (expectations vs. our view)
• [Time]: Conference or investor day

Trade Ideas (if any)

• [Long/Short] [Company]: 1-2 sentence thesis + catalyst
• Risk: What would make this wrong

Step 3: Quick Takes on Earnings

If a coverage company reported, provide a quick reaction:

Our Take: 2-3 sentences — is this good or bad for the stock? Does it change our thesis?

Action: Maintain / Upgrade / Downgrade rating? Adjust price target?

Step 4: Output

• Save output to $WORK_DIR/work/{task}/ (e.g. morning_note_YYYY-MM-DD.md)
• Markdown text for email/Slack distribution
• Keep to 1 page max — PMs and traders won't read more

Important Notes

• Be opinionated — morning notes that just summarize news without a view are useless
• Lead with the most important thing — don't bury the headline
• "No news" is a valid morning note — say "nothing material overnight, maintaining positioning"
• Distinguish between actionable events (earnings, M&A) and noise (minor analyst notes, non-events)
• Time-stamp your takes — if you're writing at 6am, note that pre-market may change by open
• If you're wrong, own it in the next morning note — credibility matters more than being right every time

Onboarding Skill

Purpose

Help new users set up their investment profile through a natural, conversational flow. The agent gathers preferences and stores them as rich, descriptive text that future conversations can reference for personalized advice.

This skill provides 5 tools:

• get_user_data - Read user data
• update_user_data - Create or update user data
• remove_user_data - Delete user data
• manage_workspaces - Create workspaces (via action="create")
• ptc_agent - Dispatch a research question to a workspace

You should call these tools directly instead of using ExecuteCode tool.

Tool Reference

Tool 1: get_user_data

Retrieve user data by entity type.

# Get complete user data (recommended at start of onboarding)
get_user_data(entity="all")

Tool 2: update_user_data

Create or update user data (upsert semantics). Preference entities merge by default.

All preference fields accept any descriptive string. Extra fields are allowed and persisted.

# Good - rich context that helps future conversations
update_user_data(entity="risk_preference", data={
    "risk_tolerance": "Moderate - comfortable with market swings but avoids concentrated bets",
    "notes": "Lost money in 2022 tech crash, now prefers diversification"
})

# Bad - keyword with no context
update_user_data(entity="risk_preference", data={"risk_tolerance": "medium"})

Tool 3: remove_user_data

Delete user data by entity type.

Tool 4: manage_workspaces (action="create")

Create the user's first workspace. This requires user approval — the user sees a card and must approve.

manage_workspaces(action="create", name="My Portfolio Analysis", description="Track and analyze my stock portfolio")

Returns { success: true, workspace_id: "...", workspace_name: "..." } on approval, or "User declined workspace creation." on rejection.

Tool 5: ptc_agent

Dispatch a personalized research question to a workspace. This requires user approval — the user sees the question and can approve to start the analysis.

ptc_agent(
    question="Analyze my NVDA position — what's the current technical setup and any upcoming catalysts I should watch for?",
    workspace_id="abc-123"
)

Returns { success: true, workspace_id: "...", thread_id: "...", status: "dispatched" } on approval, or "User declined research dispatch." on rejection.

What to Gather

Stocks (Required, Structured)

At least one stock must be added to the watchlist or portfolio before onboarding can complete. Use the structured watchlist_item or portfolio_holding entities.

# Watchlist item
update_user_data(entity="watchlist_item", data={
    "symbol": "NVDA", "notes": "Watching for AI chip growth"
})

# Portfolio holding
update_user_data(entity="portfolio_holding", data={
    "symbol": "AAPL", "quantity": 50, "average_cost": 175.0
})

Risk & Investment Profile (Required, Flexible)

Gather enough context so future conversations can give personalized advice. At minimum, capture risk_tolerance on risk_preference. Topics to explore:

• Risk comfort - How much volatility can they handle? Any past experiences that shaped their risk view?
• Investment style - Growth, value, income, ESG? Any sectors they avoid or focus on?
• Time horizon - Short-term trading, long-term holding, or flexible?
• Analysis preference - Do they care most about growth metrics, valuation, competitive moat, or risk factors?

Store these as descriptive text across risk_preference and investment_preference:

update_user_data(entity="risk_preference", data={
    "risk_tolerance": "Conservative - prioritizes capital preservation, uncomfortable with >10% drawdowns",
    "notes": "Nearing retirement in 5 years, shifting from growth to income"
})

update_user_data(entity="investment_preference", data={
    "company_interest": "Dividend-paying blue chips and REITs for income",
    "holding_period": "Long-term (5+ years), rarely sells",
    "analysis_focus": "Dividend sustainability, payout ratio, and balance sheet strength",
    "avoid_sectors": "Crypto, speculative biotech"
})

Agent Preferences (Optional, Flexible)

How does the user want the agent to behave? Topics to explore:

• Output style - Quick bullet points, balanced summaries, or deep dives?
• Visualization - Always include charts, only when helpful, or prefer text?
• Proactive questions - Should the agent ask before acting, use its judgment, or only ask when critical?
• Anything else - Notes, instructions, preferences the user wants remembered.

update_user_data(entity="agent_preference", data={
    "output_style": "Balanced summary with key numbers highlighted",
    "data_visualization": "Include charts when comparing multiple stocks",
    "proactive_questions": "Use your judgment, only ask when the decision significantly impacts the analysis",
    "instruction": "Always mention if a stock has upcoming earnings within 2 weeks"
})

Conversation Guide

Always Use AskUserQuestion

Present options so the user can tap instead of type. Options are starting points for richer conversation, not rigid mappings. After the user selects an option, capture the full context of their choice (including any follow-up detail) as descriptive text.

Example AskUserQuestion options by topic:

Risk comfort:

• "Conservative - protect my capital"
• "Moderate - balanced risk and reward"
• "Aggressive - maximize growth potential"
• "I have a nuanced view"

Investment style:

• "Growth companies with strong momentum"
• "Stable dividend payers for income"
• "Undervalued opportunities"
• "ESG / sustainable investing"

Time horizon:

• "Short-term (under 1 year)"
• "Medium-term (1-5 years)"
• "Long-term (5+ years)"
• "Flexible - depends on the opportunity"

Analysis preference:

• "Focus on growth metrics (revenue, earnings growth)"
• "Focus on valuation (P/E, DCF)"
• "Focus on competitive moat and market position"
• "Focus on risk factors and downside protection"

Output style:

• "Quick bullet points - just the highlights"
• "Balanced summary with supporting data"
• "In-depth deep dive with full analysis"
• "Data-heavy with charts and numbers"

Visualization:

• "Always include charts and visuals"
• "Include when it helps explain something"
• "Prefer text-only analysis"

Proactive questions:

• "Ask me before making decisions"
• "Use your judgment most of the time"
• "Only ask when it's critical"

Storing Responses

After the user selects an option (or provides a custom answer), store the descriptive text, not a keyword:

# User selected "Moderate - balanced risk and reward" and added
# "but I get nervous during big market drops"
update_user_data(entity="risk_preference", data={
    "risk_tolerance": "Moderate - balanced risk and reward, but gets nervous during big market drops"
})

Conversation Flow

1. Start - Greet the user, explain what you'll set up, and ask about stocks they're watching or own.
2. Stocks - Add their stocks to watchlist/portfolio. Ask follow-up for holdings (quantity, cost basis).
3. Risk & Investment - Use AskUserQuestion for each topic. Follow up naturally for more detail.
4. Agent Preferences - Optional. Ask about output style, visualization, proactive questions.
5. Open-ended - "Anything else I should know about how you like to work?"
6. Complete - Summarize what was set up, mark onboarding complete.
7. Workspace & Question - After completing onboarding, create a workspace using manage_workspaces(action="create", name="...", description="...") with a name and description that fits the user's interests. Then use ptc_agent(question="...", workspace_id="...") with the returned workspace_id to dispatch an actionable starter question based on the user's stocks or interests. The question should be specific and immediately useful (e.g. "Analyze my NVDA position — what are the key technical levels and upcoming catalysts?" rather than "Tell me about stocks").

Don't ask all questions at once. Let the conversation flow naturally. If the user wants to skip optional topics, respect that.

Not Exhaustive

The listed topics are a starting point. If the conversation naturally reveals other preferences (e.g., specific sectors to avoid, earnings season behavior, news sensitivity), store those too. Any extra fields are accepted via extra="allow" on the models.

Completion Requirements

Before marking onboarding complete, verify:

1. At least one stock was added (watchlist or portfolio)
2. Risk preference was set (any truthy value in risk_preference)

# Mark onboarding complete
update_user_data(entity="profile", data={"onboarding_completed": true})

If missing:

• No stocks: "Before we finish, let's add at least one stock you're interested in. What's a stock you're watching or own?"
• No risk preference: "One more thing - I'd like to understand your risk comfort level so I can tailor my advice."

Tips

1. Be conversational - Don't interrogate. Let topics flow naturally and combine related questions.
2. Use AskUserQuestion for choices - Always present options as selectable buttons. Only use plain text for open-ended input (stock symbols, quantities, notes).
3. Handle partial info - If the user says "I own some AAPL", follow up for quantity and cost basis.
4. Confirm entries - After saving, briefly confirm: "Added AAPL (50 shares @ $175) to your portfolio."
5. Capture context, not keywords - The user's words and nuances are more valuable than a one-word category.
6. Use defaults - If user doesn't specify a watchlist, items go to the default one automatically.
7. Respect skips - Investment preferences and agent preferences are optional. Don't push if the user wants to move on.

Error Handling

• If a stock is already in a watchlist, inform the user and offer alternatives
• If a holding already exists, offer to update it instead of creating a duplicate
• If user_id is not available, inform that the user needs to be logged in

PDF Processing Guide

Overview

This guide covers essential PDF processing operations using Python libraries and command-line tools. For advanced features, JavaScript libraries, and detailed examples, see reference.md. If you need to fill out a PDF form, read forms.md and follow its instructions.

Quick Start

from pypdf import PdfReader, PdfWriter

# Read a PDF
reader = PdfReader("document.pdf")
print(f"Pages: {len(reader.pages)}")

# Extract text
text = ""
for page in reader.pages:
    text += page.extract_text()

Python Libraries

pypdf - Basic Operations

Merge PDFs

from pypdf import PdfWriter, PdfReader

writer = PdfWriter()
for pdf_file in ["doc1.pdf", "doc2.pdf", "doc3.pdf"]:
    reader = PdfReader(pdf_file)
    for page in reader.pages:
        writer.add_page(page)

with open("merged.pdf", "wb") as output:
    writer.write(output)

Split PDF

reader = PdfReader("input.pdf")
for i, page in enumerate(reader.pages):
    writer = PdfWriter()
    writer.add_page(page)
    with open(f"page_{i+1}.pdf", "wb") as output:
        writer.write(output)

Extract Metadata

reader = PdfReader("document.pdf")
meta = reader.metadata
print(f"Title: {meta.title}")
print(f"Author: {meta.author}")
print(f"Subject: {meta.subject}")
print(f"Creator: {meta.creator}")

Rotate Pages

reader = PdfReader("input.pdf")
writer = PdfWriter()

page = reader.pages[0]
page.rotate(90)  # Rotate 90 degrees clockwise
writer.add_page(page)

with open("rotated.pdf", "wb") as output:
    writer.write(output)

pdfplumber - Text and Table Extraction

Extract Text with Layout

import pdfplumber

with pdfplumber.open("document.pdf") as pdf:
    for page in pdf.pages:
        text = page.extract_text()
        print(text)

Extract Tables

with pdfplumber.open("document.pdf") as pdf:
    for i, page in enumerate(pdf.pages):
        tables = page.extract_tables()
        for j, table in enumerate(tables):
            print(f"Table {j+1} on page {i+1}:")
            for row in table:
                print(row)

Advanced Table Extraction

import pandas as pd

with pdfplumber.open("document.pdf") as pdf:
    all_tables = []
    for page in pdf.pages:
        tables = page.extract_tables()
        for table in tables:
            if table:  # Check if table is not empty
                df = pd.DataFrame(table[1:], columns=table[0])
                all_tables.append(df)

# Combine all tables
if all_tables:
    combined_df = pd.concat(all_tables, ignore_index=True)
    combined_df.to_excel("extracted_tables.xlsx", index=False)

reportlab - Create PDFs

Basic PDF Creation

from reportlab.lib.pagesizes import letter
from reportlab.pdfgen import canvas

c = canvas.Canvas("hello.pdf", pagesize=letter)
width, height = letter

# Add text
c.drawString(100, height - 100, "Hello World!")
c.drawString(100, height - 120, "This is a PDF created with reportlab")

# Add a line
c.line(100, height - 140, 400, height - 140)

# Save
c.save()

Create PDF with Multiple Pages

from reportlab.lib.pagesizes import letter
from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, PageBreak
from reportlab.lib.styles import getSampleStyleSheet

doc = SimpleDocTemplate("report.pdf", pagesize=letter)
styles = getSampleStyleSheet()
story = []

# Add content
title = Paragraph("Report Title", styles['Title'])
story.append(title)
story.append(Spacer(1, 12))

body = Paragraph("This is the body of the report. " * 20, styles['Normal'])
story.append(body)
story.append(PageBreak())

# Page 2
story.append(Paragraph("Page 2", styles['Heading1']))
story.append(Paragraph("Content for page 2", styles['Normal']))

# Build PDF
doc.build(story)

Subscripts and Superscripts

IMPORTANT: Never use Unicode subscript/superscript characters (₀₁₂₃₄₅₆₇₈₉, ⁰¹²³⁴⁵⁶⁷⁸⁹) in ReportLab PDFs. The built-in fonts do not include these glyphs, causing them to render as solid black boxes.

Instead, use ReportLab's XML markup tags in Paragraph objects:

from reportlab.platypus import Paragraph
from reportlab.lib.styles import getSampleStyleSheet

styles = getSampleStyleSheet()

# Subscripts: use <sub> tag
chemical = Paragraph("H<sub>2</sub>O", styles['Normal'])

# Superscripts: use <super> tag
squared = Paragraph("x<super>2</super> + y<super>2</super>", styles['Normal'])

For canvas-drawn text (not Paragraph objects), manually adjust font the size and position rather than using Unicode subscripts/superscripts.

Command-Line Tools

pdftotext (poppler-utils)

# Extract text
pdftotext input.pdf output.txt

# Extract text preserving layout
pdftotext -layout input.pdf output.txt

# Extract specific pages
pdftotext -f 1 -l 5 input.pdf output.txt  # Pages 1-5

qpdf

# Merge PDFs
qpdf --empty --pages file1.pdf file2.pdf -- merged.pdf

# Split pages
qpdf input.pdf --pages . 1-5 -- pages1-5.pdf
qpdf input.pdf --pages . 6-10 -- pages6-10.pdf

# Rotate pages
qpdf input.pdf output.pdf --rotate=+90:1  # Rotate page 1 by 90 degrees

# Remove password
qpdf --password=mypassword --decrypt encrypted.pdf decrypted.pdf

pdftk (if available)

# Merge
pdftk file1.pdf file2.pdf cat output merged.pdf

# Split
pdftk input.pdf burst

# Rotate
pdftk input.pdf rotate 1east output rotated.pdf

Common Tasks

Extract Text from Scanned PDFs

# Requires: pip install pytesseract pdf2image
import pytesseract
from pdf2image import convert_from_path

# Convert PDF to images
images = convert_from_path('scanned.pdf')

# OCR each page
text = ""
for i, image in enumerate(images):
    text += f"Page {i+1}:\n"
    text += pytesseract.image_to_string(image)
    text += "\n\n"

print(text)

Add Watermark

from pypdf import PdfReader, PdfWriter

# Create watermark (or load existing)
watermark = PdfReader("watermark.pdf").pages[0]

# Apply to all pages
reader = PdfReader("document.pdf")
writer = PdfWriter()

for page in reader.pages:
    page.merge_page(watermark)
    writer.add_page(page)

with open("watermarked.pdf", "wb") as output:
    writer.write(output)

Extract Images

# Using pdfimages (poppler-utils)
pdfimages -j input.pdf output_prefix

# This extracts all images as output_prefix-000.jpg, output_prefix-001.jpg, etc.

Password Protection

from pypdf import PdfReader, PdfWriter

reader = PdfReader("input.pdf")
writer = PdfWriter()

for page in reader.pages:
    writer.add_page(page)

# Add password
writer.encrypt("userpassword", "ownerpassword")

with open("encrypted.pdf", "wb") as output:
    writer.write(output)

Quick Reference

Next Steps

• For advanced pypdfium2 usage, see reference.md
• For JavaScript libraries (pdf-lib), see reference.md
• If you need to fill out a PDF form, follow the instructions in forms.md
• For troubleshooting guides, see reference.md

Secretary Skill

Workflow patterns and operational details for the secretary tools. Basic tool signatures are in the tool descriptions — this covers what they don't.

Operational Details

HITL approval

These actions pause for user confirmation before executing:

• manage_workspaces(action="create"|"delete"|"stop")
• ptc_agent(...) — always, before dispatch
• manage_threads(action="delete")

These run immediately (no approval):

• manage_workspaces(action="list")
• manage_threads(action="list"|"get_output")
• agent_output(...)

ptc_agent dispatch

ptc_agent is asynchronous — it dispatches the question and returns immediately. The PTC agent runs in the background with full code execution, charts, and financial data tools.

Return: { success, workspace_id, thread_id, status: "dispatched", report_back }

• Omit workspace_id → auto-creates a new workspace (blocks ~8-10s for sandbox init)
• Pass workspace_id → dispatches to existing workspace (new thread)
• Pass thread_id → continues an existing conversation (overrides workspace_id)
• report_back=True (default) → when PTC completes, you'll automatically receive the results and should summarize them for the user
• report_back=False → fire-and-forget; the user will check results in the workspace themselves
• The returned report_back field is authoritative, not an echo of your request: it can come back false even when you asked for true (degraded backend). If it does, results will NOT arrive automatically — poll with agent_output.
• Concurrency caps (report-back dispatches only): at most 5 pending analyses per conversation and 10 per user. Over the cap the dispatch fails with an error starting "too many concurrent analyses" — wait for one to finish, or dispatch with report_back=False.

Use the returned thread_id with agent_output to check progress later (only needed when the returned report_back is false).

agent_output

Return: { text, status, thread_id, workspace_id }

• status: "running" — analysis still in progress, text is partial
• status: "completed" — full output available
• status: "error" — something went wrong

turns window (also on manage_threads(action="get_output")): by default you get only the latest turn's output. For a thread continued several times, pass turns=N for the last N turns or turns=0 for recent history (up to the 50 most recent turns) — turns come back oldest→newest, separated by ---. A still-streaming turn always returns just that live turn, regardless of turns.

Workflow Patterns

"What's going on?" — Status overview

When the user asks for a status overview, combine workspace and thread information:

1. Call manage_workspaces(action="list") to get workspace states
2. Call manage_threads(action="list") to get recent thread activity
3. Present a concise summary: running analyses, recently completed work, workspace count

Dispatch + Monitor — Full research cycle

1. User asks a complex question → call ptc_agent(question="...")
2. User asks "what happened?" or "is it done?" → call agent_output(thread_id="...")
3. Summarize the key findings concisely

Continue an existing analysis

When the user wants to follow up on a prior dispatch:

1. Call ptc_agent(question="...", thread_id="...") with the original thread_id
2. The PTC agent continues in the same thread with full prior context
3. To review the whole conversation (not just the newest answer), read it back with agent_output(thread_id="...", turns=0)

Workspace cleanup

When the user wants to tidy up:

1. Call manage_workspaces(action="list") to identify stale workspaces
2. Stop idle sandboxes with manage_workspaces(action="stop", workspace_id="...")
3. Delete workspaces the user no longer needs with manage_workspaces(action="delete", workspace_id="...")

Sector Overview

Workflow

Step 1: Define Scope

• Sector / subsector: What industry and how narrowly defined?
• Purpose: Client report, internal research, pitch material, idea generation
• Depth: High-level overview (5-10 pages) or deep dive (20-30 pages)
• Angle: Neutral landscape vs. thematic thesis (e.g., "AI infrastructure buildout")
• Universe: Public companies only, or include private?

Step 2: Market Overview

Market Size & Growth

• Total addressable market (TAM) with source
• Historical growth rate (5-year CAGR)
• Forecast growth rate and key assumptions
• Market segmentation (by product, geography, end market, customer type)

Industry Structure

• Fragmented vs. consolidated — top 5 market share
• Value chain map — where does value accrue?
• Business model types (subscription, transaction, licensing, services)
• Barriers to entry (capital, regulatory, technical, network effects)

Key Trends & Drivers

• Secular tailwinds (3-5 major trends)
• Headwinds and risks
• Technology disruption vectors
• Regulatory developments
• M&A activity and consolidation trends

Step 3: Competitive Landscape

Company Profiles (for top 5-10 players):

For each company, brief profile:

• Business description (2-3 sentences)
• Strategic positioning and moat
• Recent developments (earnings, M&A, product launches)
• Valuation snapshot (P/E, EV/EBITDA, EV/Revenue)

Competitive Dynamics

• How do companies compete? (price, product, service, distribution)
• Who is gaining/losing share and why?
• Disruption risk from new entrants or adjacent players

Step 4: Valuation Context

• Sector trading multiples (current and historical range)
• Premium/discount drivers (growth, margins, market position)
• Recent M&A transaction multiples
• How does the sector compare to the broader market?

Step 5: Investment Implications

• Where are the best risk/reward opportunities?
• What thematic bets can be expressed through this sector?
• Key debates in the sector (bull vs. bear arguments)
• Catalysts that could change the sector narrative