build-model

分类: 数据与AI | 上传者: daloopa | 下载: 0 | 版本: v1.0（最新）
Build a multi-tab Excel financial model
更新日志: Source: GitHub https://github.com/daloopa/investing
目录结构

当前层级: .claude/skills/build-model/
📄 SKILL.md 5.0 KB
SKILL.md

---
name: build-model
description: Build a multi-tab Excel financial model
argument-hint: TICKER
---

Build a comprehensive Excel financial model (.xlsx) for the company specified by the user: $ARGUMENTS

**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.

This skill gathers all available financial data and builds a multi-tab Excel model from scratch using openpyxl.

## Phase 1 — Company Setup
Look up the company by ticker using `discover_companies`. Capture:
- `company_id`
- `latest_calendar_quarter` — anchor for all period calculations (see `../data-access.md` Section 1.5)
- `latest_fiscal_quarter`
- Firm name for report attribution (default: "Daloopa") — see `../data-access.md` Section 4.5

Get current stock price, market cap, shares outstanding, beta, and trading multiples for {TICKER} (see ../data-access.md Section 2 for how to source market data).

## Phase 2 — Comprehensive Data Pull
Calculate periods backward from `latest_calendar_quarter`. Pull as much data as Daloopa has for this company. Target 8-16 quarters.

**Income Statement — search and pull all available:**
- Revenue / Net Sales
- Cost of Revenue / COGS
- Gross Profit
- Research & Development
- Selling, General & Administrative
- Total Operating Expenses
- Operating Income
- Interest Expense / Income
- Pre-tax Income
- Tax Expense
- Net Income
- Diluted EPS
- Diluted Shares Outstanding
- EBITDA (or compute from Op Income + D&A)
- D&A

**Balance Sheet — search and pull all available:**
- Cash and Equivalents
- Short-term Investments
- Accounts Receivable
- Inventory
- Total Current Assets
- PP&E (net)
- Goodwill
- Total Assets
- Accounts Payable
- Short-term Debt
- Long-term Debt
- Total Liabilities
- Total Equity

**Cash Flow — search and pull all available:**
- Operating Cash Flow
- Capital Expenditures
- Depreciation & Amortization
- Acquisitions
- Dividends Paid
- Share Repurchases
- Free Cash Flow (compute if not direct)

**Segments:**
- Revenue by segment
- Operating income by segment (if available)

**KPIs:**
- All company-specific operating metrics

**Guidance:**
- All guidance series and corresponding actuals

## Phase 3 — Market Data & Peers
- Identify 5-8 peers and get their trading multiples (see ../data-access.md Section 2)
- Get risk-free rate (see ../data-access.md Section 2)
- If consensus forward estimates are available (../data-access.md Section 3), include NTM estimates for peers

## Phase 4 — Projections
Build forward estimates. If a projection engine is available (see ../data-access.md Section 5), use it. Otherwise, project manually:
- Revenue: guidance + decay to long-term growth
- Margins: mean-revert to trailing averages
- CapEx, D&A, tax rate, share count: trailing trends

Project 4-8 quarters forward.

## Phase 5 — DCF Inputs
Calculate:
- WACC (CAPM: Rf + Beta × ERP; cost of debt from interest/debt)
- 5-year FCF projections (annualized from quarterly)
- Terminal value (perpetuity growth at 2.5-3%)
- Sensitivity matrix: WACC (7 values) × terminal growth (6 values)

## Phase 6 — Build Excel Model
Write the complete context to `reports/.tmp/{TICKER}_model_context.json`, then run:
`python infra/excel_builder.py --context reports/.tmp/{TICKER}_model_context.json --output reports/{TICKER}_model.xlsx`

The context JSON should include ALL of these sections (each optional — the builder handles missing data):
```json
{
  "company": {name, ticker, exchange, currency},
  "market_data": {price, market_cap, shares_outstanding, beta, trailing_pe, forward_pe, ev_ebitda, ...},
  "periods": ["2023Q1", ...],
  "projected_periods": ["2026Q1", ...],
  "income_statement": {"Revenue": {"2023Q1": value, ...}, ...},
  "balance_sheet": {"Total Assets": {...}, ...},
  "cash_flow": {"Operating Cash Flow": {...}, ...},
  "segments": {"Revenue by Segment": {"iPhone": {...}, ...}},
  "kpis": {"Metric Name": {...}, ...},
  "guidance": {"series": {...}, "actuals": {...}},
  "projections": {"Revenue": {...}, ...},
  "projection_assumptions": {revenue_growth, gross_margin, op_margin, capex_pct_revenue, tax_rate, buyback_rate_qoq},
  "dcf": {wacc, terminal_growth, risk_free_rate, equity_risk_premium, projected_fcf, terminal_value, enterprise_value, implied_share_price, sensitivity},
  "comps": {"peers": [{ticker, name, trailing_pe, ev_ebitda, ...}, ...]}
}
```

If the Excel builder fails, report the error. The context JSON is still saved for debugging.

## Output
Tell the user:
- Where the .xlsx was saved: `reports/{TICKER}_model.xlsx`
- Where the context JSON was saved: `reports/.tmp/{TICKER}_model_context.json`
- Summary of what tabs were built
- Key model outputs: trailing revenue, projected revenue growth, implied DCF value, peer-implied range
- Remind user that yellow cells in the Projections tab are editable inputs

All financial figures gathered must use Daloopa citation format: [$X.XX million](https://daloopa.com/src/{fundamental_id})
登录后下载/点赞/收藏 ❤ 283 | ★ 0
build-model

目录结构

SKILL.md

举报内容

提示
