Update to Daloopa

{
  "name": "comp-sheet",
  "description": "Build an industry comp sheet Excel model with deep operational KPIs",
  "included_files": [
    {
      "relative_path": "agents/openai.yaml",
      "size_in_bytes": 240
    }
  ],
  "skill_md_contents": "---\nname: comp-sheet\ndescription: Build an industry comp sheet Excel model with deep operational KPIs\n---\n\nBuild a multi-company industry comp sheet Excel model for the company named in the user's request. If no ticker or company is provided, ask for one before proceeding.\n\nThis produces an interactive `.xlsx` workbook — the kind of comp sheet every analyst on a coverage team maintains. Multi-company, multi-tab, with deep operational KPIs alongside standard financials.\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\nFollow these steps:\n\n## 1. Company & Peer Setup\n\nLook up the target company by ticker using `discover_companies`. Capture `company_id`, `latest_calendar_quarter` (anchor for all period calculations — see `../data-access.md` Section 1.5), and `latest_fiscal_quarter`. Note the firm name for report attribution (default: \"Daloopa\") — see `../data-access.md` Section 4.5.\n\nThen identify 6-10 comparable companies using the same logic as the comps skill:\n- **Direct competitors** in the same market\n- **Business model peers** (similar revenue model)\n- **Size peers** (similar market cap range)\n- **Growth profile peers** (similar growth rate)\n\nLook up all peer company_ids via Daloopa. If a peer isn't available in Daloopa, include it with market data only and note the limitation.\n\nList the full peer group with brief justification for each.\n\n## 2. Deep Data Gathering\n\nFor each company (target + all peers), pull from Daloopa:\n\n**Calculate 8 quarters backward from `latest_calendar_quarter`. Pull financials:**\n- Revenue, Gross Profit, Operating Income, Net Income, Diluted EPS\n- Operating Cash Flow, Capital Expenditures, D&A\n- Free Cash Flow (compute as OCF - CapEx)\n- R&D Expense, SG&A (where available)\n\n**Segment revenue breakdown** (all available segments, 8 quarters)\n\n**Company-specific operational KPIs** — use the 9-sector taxonomy to know what to search for:\n- **SaaS/Cloud**: ARR, net revenue retention, RPO/cRPO, customers >$100K, cloud gross margin\n- **Consumer Tech**: DAU/MAU, ARPU, engagement metrics, installed base, paid subscribers\n- **E-commerce/Marketplace**: GMV, take rate, active buyers/sellers, order frequency\n- **Retail**: same-store sales, store count, average ticket, transactions\n- **Telecom/Media**: subscribers, churn, ARPU, content spend\n- **Hardware**: units shipped, ASP, attach rate, installed base\n- **Financial Services**: AUM, NIM, loan growth, credit quality metrics, fee income ratio\n- **Pharma/Biotech**: pipeline stage, patient starts, scripts, market share\n- **Industrials/Energy**: backlog, book-to-bill, utilization, production volumes, reserves\n\n**Stock prices & valuation multiples:**\nUse `get_stock_prices` (see `../data-access.md` Section 1.7) to pull prices for ALL companies in a single batch call. Get:\n- Current price: `dates` = 3 most recent calendar days for all company_ids\n- Quarter-end prices: `dates` = quarter-end dates matching the financial periods (for historical multiples)\n\nThen compute valuation metrics by combining stock prices with Daloopa fundamentals:\n- **Market Cap** = Close price × Diluted shares outstanding\n- **Enterprise Value** = Market Cap + Total Debt - Cash\n- **P/E (trailing)** = Market Cap / Net Income (trailing 4Q)\n- **EV/EBITDA** = EV / EBITDA (trailing 4Q)\n- **P/S** = Market Cap / Revenue (trailing 4Q)\n- **P/B** = Market Cap / Total Equity\n- **EV/FCF** = EV / Free Cash Flow (trailing 4Q)\n- **FCF Yield** = FCF (trailing 4Q) / Market Cap\n- **Dividend Yield** = Dividends Paid (trailing 4Q) / Market Cap\n\nFor beta, use web search (see `../data-access.md` Section 2). For forward multiples, use consensus estimates if available (Section 3).\n\n## 3. KPI Discovery & Mapping\n\nAfter pulling data, build the KPI mapping:\n- Which KPIs are available for which companies? Build a coverage matrix.\n- Group KPIs into categories:\n  - **Segment Revenue**: product/service line breakdowns\n  - **Growth KPIs**: subscriber growth, unit growth, same-store sales growth\n  - **Unit Economics**: ARPU, ASP, take rate, retention\n  - **Efficiency**: R&D % of revenue, SBC % of revenue, CapEx % of revenue\n  - **Engagement**: DAU/MAU, retention, churn\n- Flag KPIs that are comparable across peers vs company-specific\n\n## 4. Compute Derived Metrics\n\nFor each company, calculate:\n\n**Margins:**\n- Gross Margin, Operating Margin, Net Margin, FCF Margin (each quarter)\n\n**Growth rates:**\n- Revenue YoY, EPS YoY, segment revenue YoY (each quarter where year-ago data exists)\n\n**Capital metrics:**\n- Net Debt (Total Debt - Cash)\n- Net Debt/EBITDA\n- Shareholder Yield (Buybacks + Dividends) / Market Cap\n\n**Historical multiples (from quarter-end prices pulled in Section 2):**\n- Compute P/E, EV/EBITDA, P/S, EV/FCF at each quarter-end to show how multiples have trended\n- This lets the reader see whether the current multiple is elevated or depressed vs. the company's own history\n\n**Implied valuation:**\n- For each valuation methodology (P/E, EV/EBITDA, P/S, EV/FCF):\n  - Peer median multiple × target metric = implied value\n  - Convert to implied share price\n- Compute median implied price across methodologies\n\n## 5. Build Excel Workbook\n\nGenerate the Excel workbook directly as a local `.xlsx` file. For Codex, prefer bundled spreadsheet tooling or Python/openpyxl when available.\n\nThe workbook must contain 8 tabs with the following structure:\n\n### Tab 1: Comp Summary\nOne-page overview with all companies side-by-side:\n- Company name, ticker, price, market cap\n- All valuation multiples (P/E, EV/EBITDA, P/S, P/B, EV/FCF, div yield)\n- Latest quarter revenue, EBITDA, net income\n- Growth rates (revenue YoY, EPS YoY)\n- Key margins (gross, operating, net, FCF)\n- Implied valuation for target (median across methodologies)\n- Premium/discount vs peers\n\n### Tab 2: Revenue Drivers\nUnit economics decomposition per company (trailing 4 quarters):\n- Total revenue (4Q sum)\n- Segment revenue breakdown (% of total)\n- Key unit economics: units × ASP, or subscribers × ARPU, etc.\n- Growth trajectory by segment\n\n### Tab 3: Operating KPIs\nCross-company KPI comparison matrix:\n- Rows = KPIs (grouped by category from step 3)\n- Columns = companies\n- Show latest quarter value + YoY change where applicable\n- Highlight cells where data is unavailable (sparse matrix)\n\n### Tab 4: Financial Summary\nSide-by-side income statements (trailing 4 quarters):\n- Revenue, COGS, Gross Profit\n- R&D, SG&A, Operating Income\n- Interest, Tax, Net Income\n- Diluted EPS\n- Compute 4Q sums for each line item\n\n### Tab 5: Growth & Margins\nTrend analysis (up to 8 quarters):\n- Revenue growth YoY (%)\n- EPS growth YoY (%)\n- Gross margin (%)\n- Operating margin (%)\n- Net margin (%)\n- FCF margin (%)\n- Show trends across all periods for each company\n\n### Tab 6: Valuation Detail\nImplied prices by methodology:\n- P/E implied (peer median P/E × target EPS)\n- EV/EBITDA implied\n- P/S implied\n- EV/FCF implied\n- Median implied price\n- Current price\n- Premium/discount (%)\n\n### Tab 7: Balance Sheet & Capital\nLeverage and capital returns:\n- Total Debt, Cash, Net Debt\n- Net Debt/EBITDA\n- Trailing 4Q: OCF, CapEx, FCF\n- FCF Yield\n- Shareholder Yield (buybacks + dividends)\n\n### Tab 8: Raw Data\nFull quarterly appendix for each company:\n- All 8 quarters of financial data\n- All KPIs by quarter\n- All growth rates and margins by quarter\n- Complete data backing the summary tabs\n\n**Styling requirements:**\n- Apply the design system color palette (Navy #1B2A4A headers, Steel Blue #4A6FA5 accents)\n- Number formatting per `../design-system.md` conventions\n- Bold headers, freeze panes on all tabs\n- Conditional formatting: green for positive growth, red for negative\n- Auto-adjust column widths\n\nThe workbook generation should:\n1. Use the best available spreadsheet-generation library\n2. Construct all 8 worksheets programmatically\n3. Apply styling (bold headers, number formats, colors)\n4. Generate the `.xlsx` file\n5. Save the workbook as `reports/{TARGET_TICKER}_comp_sheet_{DATE}.xlsx`\n\n## 6. Output Summary\n\nAfter generating the Excel workbook, provide a concise summary highlighting:\n\n**Target positioning vs peers**:\n- Where does it rank on growth, margins, and valuation?\n- Quartile positioning across key metrics\n\n**Most differentiated KPIs**:\n- Which operational metrics set the target apart (positive or negative)?\n- Notable outliers in the KPI matrix\n\n**Implied valuation range**:\n- What does the peer group suggest the stock is worth?\n- Premium/discount vs current price\n- Which methodology drives the highest/lowest implied value?\n\n**Key risk**:\n- What's the biggest vulnerability the comp sheet reveals (e.g., premium valuation with decelerating KPIs, margins below peers, concentration risk)?\n\nAll financial figures in the summary must use Daloopa citation format: [$X.XX million](https://daloopa.com/src/{fundamental_id})\n"
}