Update to Daloopa

{
  "name": "initiate",
  "description": "Initiate coverage — generate both research note (HTML) and Excel model (.xlsx)",
  "included_files": [
    {
      "relative_path": "agents/openai.yaml",
      "size_in_bytes": 253
    }
  ],
  "skill_md_contents": "---\nname: initiate\ndescription: Initiate coverage — generate both research note (HTML) and Excel model\n  (.xlsx)\n---\n\nInitiate coverage on the company named in the user's request. If no ticker or company is provided, ask for one before proceeding.\n\n**Before starting, read `../data-access.md` for data access methods and `../design-system.md` for formatting conventions.** Follow the data access detection logic and design system throughout this skill.\n\nThis is the capstone skill that produces both a research note (styled HTML) and an Excel model (.xlsx) from a single comprehensive data gathering pass.\n\n## Strategy\nRather than running the research-note and build-model skills independently (which would duplicate data gathering), this skill gathers a superset of data once, then renders both outputs.\n\n## Phase 1 — Company Setup\nLook up the company by ticker using `discover_companies`. Capture:\n- `company_id`\n- `latest_calendar_quarter` — anchor for all period calculations (see `../data-access.md` Section 1.5)\n- `latest_fiscal_quarter`\n- Firm name for report attribution (default: \"Daloopa\") — see `../data-access.md` Section 4.5\n\nGet market data using the 3-step resolution: (1) MCP market data tools if available, (2) web search, (3) sensible defaults (see `../data-access.md` Section 2):\n- Current price, market cap, shares outstanding, beta\n- Trading multiples (P/E, EV/EBITDA, P/S, P/B)\n- Risk-free rate (for DCF)\n\nInitialize context: `context = {company_name, ticker, date, price, market_cap, firm_name, ...}`\n\n## Phase 2 — Comprehensive Data Gathering\nCalculate 8-16 quarters backward from `latest_calendar_quarter`. Pull:\n\n**Income Statement — search and pull all available:**\n- Revenue / Net Sales\n- Cost of Revenue / COGS\n- Gross Profit\n- Research & Development\n- Selling, General & Administrative\n- Total Operating Expenses\n- Operating Income\n- Interest Expense / Income\n- Pre-tax Income\n- Tax Expense\n- Net Income\n- Diluted EPS\n- Diluted Shares Outstanding\n- EBITDA (or compute from Op Income + D&A, label \"(calc.)\")\n- D&A\n\n**Balance Sheet — search and pull all available:**\n- Cash and Equivalents\n- Short-term Investments\n- Accounts Receivable\n- Inventory\n- Total Current Assets\n- PP&E (net)\n- Goodwill\n- Total Assets\n- Accounts Payable\n- Short-term Debt\n- Long-term Debt\n- Total Liabilities\n- Total Equity\n\n**Cash Flow — search and pull all available:**\n- Operating Cash Flow\n- Capital Expenditures\n- Depreciation & Amortization\n- Acquisitions\n- Dividends Paid\n- Share Repurchases\n- Free Cash Flow (compute if not direct: OCF - CapEx, label \"(calc.)\")\n\n**Segments:**\n- Revenue by segment\n- Operating income by segment (if available)\n\n**Geographic:**\n- Revenue by geography\n\n**KPIs:**\n- All company-specific operating metrics (subscribers, units, ARPU, retention, etc.)\n\n**Guidance:**\n- All guidance series and corresponding actuals\n\n**Share Activity:**\n- Share count, buyback amounts\n\n**For every value returned by `get_company_fundamentals`, record its `fundamental_id` (the `id` field).** Store each data point as `{value, fundamental_id}` so citations can be rendered in both outputs.\n\nCompute margins, YoY growth rates, and ratios for each quarter.\n\n### Cost Structure & Margin Analysis\nAfter the core financial pull:\n- **COGS driver identification**: Search for cost-related series (\"cost of goods\", \"materials\", \"manufacturing\", \"input cost\"). Identify 3-5 biggest cost line items and their trends.\n- **OpEx breakdown**: Pull R&D and SG&A separately. Compute R&D % of revenue and SG&A % of revenue trends.\n- **Margin driver analysis**: For each major margin (gross, operating, net), identify what's driving expansion or compression — pricing power, cost leverage, mix shift, or one-time items.\n\n## Phase 3 — Industry-Specific Deep Dive\nDetermine the company's sector and apply the relevant analysis template:\n\n- **Manufacturing/Industrial**: Bookings & backlog, book-to-bill ratio, pipeline by geography, capacity utilization\n- **SaaS/Technology**: ARR/MRR trajectory, net retention rate, customer cohort analysis, RPO/deferred revenue trends\n- **Retail/Consumer**: Same-store sales, store count trajectory, traffic vs ticket decomposition, inventory health\n- **Financials/Banks**: NIM trajectory, provision trends, loan growth by category, capital ratios (CET1, TCE)\n- **Healthcare/Pharma**: Pipeline summary (drug, indication, phase, milestone), product revenue breakdown, patent cliff timeline\n- **Energy**: Production volumes, realized pricing vs benchmark, proved reserves, breakeven analysis\n\nSearch for relevant series using `discover_company_series` with sector-appropriate keywords. Pull available data and build the narrative.\n\nBuild `context.industry_deep_dive` (string) — sector-specific analysis narrative with Daloopa citations, organized by the relevant template above.\n\n## Phase 4 — Peer Analysis\nIdentify 5-8 comparable companies.\nGet peer trading multiples using the 3-step resolution: (1) MCP market data tools if available, (2) web search, (3) sensible defaults (see `../data-access.md` Section 2).\nIf consensus forward estimates are available (`../data-access.md` Section 3), include NTM estimates.\nPull peer fundamentals from Daloopa where available (revenue growth, margins).\n\nBuild `context.comps` and `context.comps_table`.\n\n## Phase 5 — Projections\nBuild forward estimates using the following methodology:\n- **Revenue:** Start with latest guidance (if available), then decay to long-term growth rate (industry average or historical trend). Apply quarterly seasonality patterns from trailing data.\n- **Gross Margin:** Mean-revert to trailing 8-quarter average, with adjustment for recent trends or guidance commentary.\n- **Operating Expenses:** Project as % of revenue, trending toward trailing averages. R&D and SG&A may have different trajectories.\n- **CapEx:** Project as % of revenue based on trailing 4-8 quarter average and guidance.\n- **D&A:** Project based on trailing average as % of revenue or PP&E.\n- **Tax Rate:** Use trailing effective tax rate or guidance.\n- **Share Count:** Project dilution/buyback based on trailing trends and guidance.\n- **Working Capital:** Project DSO, DIO, DPO based on trailing averages.\n\nCalculate all quarterly projections, then sum to annual. Project 4-8 quarters forward. Describe methodology inline and perform calculations directly.\n\n## Phase 6 — DCF Valuation\nCalculate:\n- **WACC:** Use CAPM for cost of equity (Rf + Beta × ERP, where ERP = 6.0%). Cost of debt = Interest Expense / Total Debt. WACC = (E/V × Re) + (D/V × Rd × (1 - Tax Rate)).\n- **5-year FCF projections:** Annualize from quarterly projections (FCF = Op Cash Flow - CapEx).\n- **Terminal Value:** Use perpetuity growth at 2.5-3.0%.\n- **Implied Share Price:** (PV of FCFs + Terminal Value - Net Debt) / Shares Outstanding\n- **Sensitivity Matrix:** WACC (7 values: -3% to +3% from base) × Terminal Growth (6 values: 1.5% to 4.0%).\n\nBuild `context.dcf` and `context.dcf_summary` (set `context.has_dcf = true`).\n\n## Phase 7 — Qualitative Research + News & Catalysts\n\n### SEC Filing Research\nSearch SEC filings across multiple queries:\n- \"risk\" / \"uncertainty\" / \"challenge\" for risk factors\n- \"growth\" / \"opportunity\" / \"expansion\" for growth drivers\n- \"competition\" / \"market share\" for competitive dynamics\n- \"outlook\" / \"guidance\" for management's forward view\n- Company-specific strategic topics (e.g., \"AI\", \"cloud\", etc.)\n\nExtract and organize into:\n- `context.risks` — ranked list of risks with impact/probability\n- `context.investment_thesis` — variant perception, thesis pillars, catalysts\n- `context.company_description` — 2-3 sentence business description\n\n### News & Catalysts via WebSearch\nRun 4 WebSearch queries to gather recent external context:\n1. `\"{TICKER} {company_name} news {year}\"` — recent headlines and developments\n2. `\"{TICKER} analyst upgrade downgrade price target\"` — sell-side sentiment shifts\n3. `\"{TICKER} catalysts risks\"` — forward-looking events and risk factors\n4. `\"{company_name} industry outlook {sector}\"` — macro and industry trends\n\nOrganize results into:\n- `context.news_timeline` (string) — 6-10 key events from the last 6-12 months in reverse chronological order. Each event: date, headline, 1-sentence impact, sentiment tag (Positive / Negative / Mixed / Upcoming). Format as a numbered list.\n\n- `context.forward_catalysts` (string) — Organized by timeframe:\n  - **Near-term (0-3 months, HIGH priority)**: earnings dates, product launches, regulatory decisions\n  - **Medium-term (3-12 months, MEDIUM priority)**: strategic milestones, contract renewals, industry events\n  - **Long-term (1-3 years, LOW priority)**: secular trends, market expansion, competitive dynamics\n\n- `context.policy_backdrop` (string) — Macro/regulatory context affecting the company. Tariffs, regulation, interest rates, sector-specific policy. Leave empty string if not material.\n\n## Phase 8 — Guidance Track Record\nSearch for guidance series (\"guidance\", \"outlook\", \"forecast\", \"estimate\", \"target\").\nPull guidance and corresponding actuals. Apply +1 quarter offset rule for quarterly guidance, same-year rule for annual guidance from Q1/Q2/Q3, next-year rule for annual guidance from Q4.\nCompute beat/miss rates and patterns.\nBuild `context.guidance` and `context.guidance_table` (set `context.has_guidance = true/false`).\n\n## Phase 9 — What You Need to Believe\nBuild falsifiable bull/bear beliefs:\n\n### Bull Beliefs (To Go Long)\nWrite 4-6 numbered beliefs, each with:\n- One **bold statement** (the belief itself)\n- 2-3 sentences of **evidence** with Daloopa citations supporting why this could be true\n- Each belief must be **falsifiable** — testable with observable data within 6 months\n\nExample format: \"1. **Revenue growth re-accelerates to 15%+ as AI monetization scales.** Cloud segment grew [$X.Xbn](link) last quarter, up X% YoY, with management noting...\"\n\n### Bear Beliefs (To Go Short)\nSame format — 4-6 numbered falsifiable beliefs with evidence for the downside case.\n\n### Valuation Math\nFor each side:\n- Bull target: forward multiple × forward earnings estimate = price target. Show the math.\n- Bear target: same structure with bear-case multiple and earnings.\n\n### Risk/Reward Assessment\n- Compare bull upside % vs bear downside % from current price\n- If asymmetry is significant (e.g., 30% upside vs 40% downside), flag it explicitly\n- State which side has the better risk/reward and why\n\nBuild `context.bull_beliefs`, `context.bull_target`, `context.bear_beliefs`, `context.bear_target`, `context.risk_reward_assessment`.\n\n## Phase 10 — Capital Allocation\nPull buyback, dividend, share count, FCF data.\nCompute shareholder yield, FCF payout ratio, net leverage.\nBuild `context.capital_allocation_commentary`.\n\n## Phase 11 — Synthesis + Tensions + Monitoring\nThis is the most judgment-intensive step. Be honest and critical — the reader is a professional investor who needs your real assessment, not a balanced summary.\n\n### Core Synthesis\nWrite:\n- **Executive Summary**: 3-4 sentence TL;DR covering current state, key thesis, valuation view. Include a clear directional view — is this stock attractive, fairly valued, or overvalued at the current price?\n- **Variant Perception**: What does the market think vs what do you see in the data? Where is the consensus wrong? If you agree with consensus, say that too — but explain what could change.\n- **Key Findings**: Top 3-5 most notable data points or trends — prioritize what changes the investment thesis, not just what's interesting\n- **Red Flags & Concerns**: Any quality-of-earnings issues, sustainability questions, or risks the market may be underpricing\n- Build `context.executive_summary`, `context.variant_perception`\n\n### Five Key Tensions\nIdentify the 5 most critical bull/bear debates for this stock. Each tension is a single line that frames both sides. Alternate between bullish-leaning and bearish-leaning tensions. Every tension must reference a specific data point from the analysis.\n\nFormat as a numbered list:\n1. \"[Bullish factor] vs [Bearish factor]\" — cite the specific metric\n2. \"[Bearish factor] vs [Bullish factor]\" — cite the specific metric\n...etc.\n\nBuild `context.five_key_tensions` (string).\n\n### Monitoring Framework\nBuild two monitoring lists for ongoing tracking:\n\n**Quantitative Monitors** — 5-7 specific metrics with explicit thresholds:\n- Format: \"Metric: current value → bull threshold / bear threshold\"\n- Example: \"Gross Margin: 45.2% → above 46% confirms pricing power / below 43% signals cost pressure\"\n\n**Qualitative Monitors** — 5-7 factors to watch:\n- Management tone shifts on earnings calls\n- Competitive dynamics (new entrants, pricing pressure)\n- Regulatory developments\n- Customer concentration changes\n- Capital allocation pivots\n\nBuild `context.monitoring_quantitative` and `context.monitoring_qualitative` (strings, numbered lists).\n\n### Structured Tables\nBuild structured tables for both outputs:\n- `context.key_metrics_table` — [{metric, value, vs_prior}] for the exec summary table\n- `context.financials_table` — [{metric, q1, q2, ...}] for the financial analysis section\n- `context.segments_table`, `context.geo_table`, `context.shares_outstanding_table`\n- `context.opex_breakdown_table` — [{metric, q1, q2, ...}] for R&D, SG&A, % of revenue rows\n- `context.guidance_table`, `context.comps_table`, etc.\n\n## Phase 12 — Render Research Note (HTML)\n\nUsing the HTML Report Template from `../design-system.md`, generate a styled HTML report with full CSS inlined. The report should include:\n\n**Header Section:**\n- Company name and ticker\n- Report date and firm attribution\n- Five Key Tensions (numbered list)\n\n**Section 1: Executive Summary**\n- Key metrics table\n- Executive summary narrative\n- Variant perception\n\n**Section 2: Company Overview**\n- Business description\n- Investment thesis\n\n**Section 3: Recent News & Catalysts**\n- News timeline\n- Forward catalysts\n- Policy backdrop\n\n**Section 4: Financial Analysis**\n- Financials table (8-16 quarters)\n- Cost structure & margin analysis\n- OpEx breakdown table\n- Segment and geographic tables\n- Share count table\n\n**Section 5: Industry-Specific Analysis**\n- Industry deep dive narrative\n\n**Section 6: Guidance Track Record**\n- Guidance table and beat/miss analysis (if available)\n\n**Section 7: What You Need to Believe**\n- Bull beliefs with valuation target\n- Bear beliefs with valuation target\n- Risk/reward assessment\n\n**Section 8: Catalysts**\n- Forward catalysts\n- Policy backdrop\n\n**Section 9: Capital Allocation**\n- Capital allocation commentary\n\n**Section 10: Valuation**\n- DCF summary and sensitivity (if available)\n- Comps commentary (if available)\n\n**Section 11: Risks**\n- Risks summary\n\n**Section 12: Monitoring Framework**\n- Quantitative monitors\n- Qualitative monitors\n\n**Appendix:**\n- Additional context or data\n\n### Context Key Checklist\nVerify these keys exist before rendering (set empty string if data unavailable):\n\n**Cover & Summary:**\n`company_name`, `ticker`, `date`, `price`, `market_cap`, `five_key_tensions`, `executive_summary`, `key_metrics_table`\n\n**Thesis & Overview:**\n`investment_thesis`, `variant_perception`, `company_description`\n\n**News:**\n`news_timeline`\n\n**Financials:**\n`financials_table`, `cost_margin_analysis`, `opex_breakdown_table`, `segments_table`, `geo_table`, `shares_outstanding_table`\n\n**Industry:**\n`industry_deep_dive`\n\n**Guidance:**\n`has_guidance`, `guidance_track_record`\n\n**What You Need to Believe:**\n`bull_beliefs`, `bull_target`, `bear_beliefs`, `bear_target`, `risk_reward_assessment`\n\n**Catalysts:**\n`forward_catalysts`, `policy_backdrop`\n\n**Capital Allocation:**\n`capital_allocation_commentary`\n\n**Valuation:**\n`has_dcf`, `dcf_summary`, `has_comps`, `comps_commentary`\n\n**Risks:**\n`risks_summary`\n\n**Monitoring:**\n`monitoring_quantitative`, `monitoring_qualitative`\n\n**Appendix:**\n`appendix_content`\n\n**Citation enforcement:** Every financial figure from Daloopa in the HTML report must use citation format: `[$X.XX million](https://daloopa.com/src/{fundamental_id})`. If a number came from `get_company_fundamentals`, it must have a citation link. No exceptions.\n\n## Phase 13 — Render Excel Model\n\nGenerate the `.xlsx` file directly using the best available spreadsheet-generation workflow. For Codex, prefer bundled spreadsheet tooling or Python/openpyxl when available. The workbook should:\n\n1. Create 8 tabs with the following structure:\n\n**Tab 1: Income Statement**\n- Rows: Revenue, COGS, Gross Profit, R&D, SG&A, Total OpEx, Op Income, Interest, Pre-Tax Income, Tax, Net Income, Diluted EPS, Shares\n- Columns: Historical periods (8-16Q) + Projected periods (4-8Q)\n- Sub-rows: YoY growth %, margin % where applicable\n- Header: Company name, ticker, report date\n- Formatting: Numbers with commas/decimals, percentages, bold headers, frozen panes\n\n**Tab 2: Balance Sheet**\n- Rows: Assets section (Cash, Investments, AR, Inventory, Current Assets, PP&E, Goodwill, Total Assets), Liabilities section (AP, ST Debt, LT Debt, Total Liabilities, Equity)\n- Columns: Historical + Projected periods\n- Sub-rows: % of Total Assets for key line items\n- Same formatting standards\n\n**Tab 3: Cash Flow**\n- Rows: Op Cash Flow, CapEx, Free Cash Flow, Acquisitions, Dividends, Buybacks, Net Change in Cash\n- Columns: Historical + Projected periods\n- Sub-rows: FCF yield %, CapEx as % Revenue\n- Same formatting standards\n\n**Tab 4: Segments**\n- Rows: Revenue by segment, Op Income by segment (if available)\n- Columns: Historical + Projected periods\n- Sub-rows: Segment as % of total, segment growth rates\n- Same formatting standards\n\n**Tab 5: KPIs**\n- Rows: All company-specific operating metrics discovered\n- Columns: Historical + Projected periods\n- Sub-rows: YoY growth or relevant unit economics\n- Same formatting standards\n\n**Tab 6: Projections**\n- Editable assumption inputs (yellow highlighting): Revenue growth %, Gross margin %, Op margin %, CapEx % revenue, Tax rate %, Buyback rate QoQ\n- Calculated outputs: Projected P&L, BS, CF driven by assumptions\n- Commentary box explaining methodology\n- Same formatting standards\n\n**Tab 7: DCF**\n- Inputs: WACC, Terminal Growth, Risk-Free Rate, ERP, Beta, Cost of Debt\n- FCF Projection (5 years annualized)\n- Terminal Value calculation\n- PV calculations\n- Enterprise Value → Equity Value → Implied Share Price\n- Sensitivity table: WACC (rows) × Terminal Growth (cols) showing implied price\n- Color scale: green (upside) to red (downside) vs current price\n- Same formatting standards\n\n**Tab 8: Summary**\n- Company overview (name, ticker, sector, description)\n- Current market data (price, market cap, shares, beta)\n- Valuation summary: DCF implied price, peer-implied range, current price, upside/downside %\n- Peer trading multiples table\n- Key model outputs: Trailing revenue, Projected revenue growth, Trailing/Projected margins\n- Same formatting standards\n\n2. Apply `../design-system.md` formatting conventions:\n- Number format: $X.Xbn for large numbers, X.X% for percentages, X.Xx for multiples\n- Color palette: Navy #1B2A4A (headers), Steel Blue #4A6FA5 (sub-headers), Gold #C5A55A (highlights), Green #27AE60 (positive), Red #C0392B (negative)\n- Bold headers, frozen top row and left column\n- Yellow fill (#FFEB3B) for editable input cells\n\n3. Save the workbook as `reports/{TICKER}_model.xlsx`\n\n## Output\nPresent both deliverables to the user:\n\n**Research Note (HTML):**\n- Save the styled HTML report to `reports/{TICKER}_initiate_report.html`.\n- Tell the user where the HTML file was saved and that it can be opened in a browser for full formatting.\n\n**Excel Model:**\n- Save the generated Excel model to `reports/{TICKER}_model.xlsx`.\n- Tell the user where the `.xlsx` file was saved.\n- Note that yellow cells in the Projections tab are editable inputs.\n\n**Summary:**\n- 3-4 sentence executive summary\n- Key valuation range (DCF implied price + comps range)\n- Top 3 findings\n- Bull upside % vs bear downside % risk/reward assessment\n\nAll financial figures must use Daloopa citation format: [$X.XX million](https://daloopa.com/src/{fundamental_id})\n"
}