dl-databook-comps

name: dl-databook-comps description: > Adds the comparable-companies analysis layer to a populated Overland deal Databook: a PublicComps tab (curated peer-set table + 5-year historical TEV/EBITDA chart), a TnxComps tab (precedent transactions), and a JSON valuation summary consumed by the Pre-Screen memo. Use at P6 when the user asks to "build the comps tabs," "pull public comps," "add precedent transactions," or "run comps" for a deal with the populated Databook available. Sources peers and precedents through a market-data connector cascade (FMP, Pitchbook, S&P Capital IQ, web) under strict filter, curation, and never-fabricate rules, and writes the two new tabs into the same Databook behind a machine-verified preservation gate. Not for the legacy Capital-IQ-formula Comps workbook; requires the populated Databook from dl-databook-financials.

Databook comps — PublicComps + TnxComps + 5-yr historical chart

When to use

Use this skill when the user has a populated Databook (the dl-databook-financials vS output) and needs the comparable-companies + precedent-transactions layer added: a PublicComps tab and a TnxComps tab written into the same Databook, not a separate workbook.

Do not use it to: refresh a legacy Capital-IQ-formula Comps workbook (retired — the connector cascade replaces it); modify pre-existing Databook tabs (the skill is write-additive only); build comps without a populated Databook (the LTM anchors + GICS read from FinInputs/SUCAP are mandatory inputs); or fabricate peer / precedent names when the cascade exhausts (the skill degrades with [INSUFFICIENT DATA — <field>] markers, never guesses).

Install required package: pip install openpyxl

Inputs (Turn 1)

• Populated Databook (vS) — the dl-databook-financials output; carries the LTM Revenue + LTM EBITDA + GICS reads.
• Borrower identity — company name + optional public-ticker proxy.
• GICS sub-industry — read from the Databook's SUCAP sector cell, supplied by the user, or resolved via the four-tier lookup in reference/connector-cascade.md §2.
• Connector availability — FMP / Pitchbook / S&P CIQ / web search / Yahoo Finance / SEC EDGAR per the user's runtime config. Confirmed at Step 1; tiers fall through per the cascade.
• Optional filter caps — TEV/Revenue / TEV/EBITDA / P/E (default 10× / 35× / 50× per the PortCo Coverage Template; user can override).

Outputs

• Modified Databook — saved as a deal-named vS copy (.xlsm macro layer preserved when the input was macro-enabled); two new tabs (PublicComps + TnxComps); every pre-existing tab identical to input.
• JSON valuation_summary — printed to stdout (emittable as a sidecar <Company> - valuation-summary.json). Consumed by dl-memo-prescreen's Valuation section — its field shape is a stable interface; do not rename or reshape fields.

Workflow

Copy this checklist and check items off as you progress:

Databook comps progress:
- [ ] Step 0: Orient (deliverable, classification, runtime detection)
- [ ] Step 1: Mandatory user gate (target, Databook, GICS)
- [ ] Step 2: Public-comps cascade + filter + curation
- [ ] Step 2.5: Historical-chart data pull + per-period math
- [ ] Step 3: Precedent-trx cascade + web-enrichment + AND-filter
- [ ] Step 4: Build plan + validate (scripts/validate_comps.py)
- [ ] Step 5: Populate (scripts/populate_comps.py)
- [ ] Step 6/7: Re-read verify + hand back

Step 0 — Orientation

• Deliverable: the modified Databook .xlsx / .xlsm with the two new tabs added — not a regenerated workbook, not a summary.
• Runtime detection: identify the operating environment (Excel add-in / Claude Desktop / claude.ai) and bind the write-channel adapter per reference/runtime-surfaces.md.
• [INSUFFICIENT DATA — <what is missing>] is the only uncertainty marker. Never fabricate a ticker, deal target, or multiple; never write 0 as a substitute for unavailable data.

Step 1 — Mandatory user gate

Confirm in one message and wait for the response:

1. Target identity — company name + public-ticker proxy if any.
2. Databook attached + LTM anchors resolvable. If not attached, decline: "populate the Databook first via dl-databook-financials, then re-invoke."
3. GICS sub-industry — read from the Databook's SUCAP sector cell if populated; else elicit from the user; canonicalize against the MSCI GICS code list; show back for confirmation before the cascade runs.

Use fully-qualified Server:tool MCP names; confirm the server name at first invocation. Never fabricate a connector name — an unresolved name is confirmed with the user once at this step.

Step 2 — Public-comps cascade + curation

Walk the tiers FMP → Pitchbook → S&P CIQ → web per reference/connector-cascade.md §1, then run the script on the resolved tier outputs:

python scripts/fetch_public_comps.py \
    --tier-output tier_output.json \
    --target-gics <gics-code> \
    --max-peers 15 \
    --out peer_set.json

The script applies the filter ladder (US-listed / market cap >$100M / LTM EBITDA >0 / TEV/Revenue ≤10× / TEV/EBITDA ≤35×), relaxes incrementally only as needed to hold the 10-name floor (flagging the relaxation), and curates to 10–20 names (target ~15), SMID-cap weighted.

Step 2.5 — Historical-chart data pull

Pull 5-yr historical TEV + LTM EBITDA per peer via the historical-market-data cascade (FMP → CIQ → Yahoo → SEC/Edgar). Sampling: monthly default (60 observations); quarterly fallback if monthly is unavailable for ≥3 peers.

python scripts/build_historical_chart.py \
    --market-data market_data.json \
    --out-series chart_series.json

The four-series spec (equal-weighted time series + flat average + flat ±1σ bands), the equal-weighting algorithm, the population-stddev sigma, and the visualization discipline are in reference/chart-construction.md.

Step 3 — Precedent-transactions cascade

Walk Pitchbook → web; web-enrich any Pitchbook field that is null, incomplete, or dubious (target name, acquiror, TEV, multiples, leases flag); then run:

python scripts/fetch_precedent_trx.py \
    --tier-output tier_output.json \
    --target-gics <gics-code> \
    --max-precedents 15 \
    --out precedent_set.json

The exclusion filter is an AND (exclude only when TEV/Revenue>10x AND TEV/EBITDA>35x); curate to 10–20 names (target ~15). No relaxation clause for precedents — a surviving set under 10 records a data gap and the build continues. See reference/connector-cascade.md §4.

Step 4 — Build the plan + validate

Construct an in-memory plan mapping every write cell to its source field, then run the pre-write validation:

python scripts/validate_comps.py \
    --peer-set peer_set.json \
    --precedent-set precedent_set.json \
    --chart-series chart_series.json \
    --comps-input comps_input.json

It machine-verifies, before any write: the workbook outside the two new tabs is identical before and after; every populated cell traces to a source field; the set sizes and filters comply; the layout matches the PortCo Coverage Template; the formatting matches the live Databook; the chart meets the four-series spec. Per-check detail: reference/cell-map.md Part B. A violation refuses with no file written — never partial output.

Step 5 — Populate · Step 6 — Verify · Step 7 — Hand back

python scripts/populate_comps.py \
    --databook <input-databook>.xlsx \
    --peer-set peer_set.json \
    --precedent-set precedent_set.json \
    --chart-series chart_series.json \
    --comps-input comps_input.json \
    --out <output-databook>.xlsx

The populator opens the Databook with openpyxl (data_only=False; keep_vba=True when .xlsm — data_only=True discards formulas permanently and is never used), derives formatting donors from the live FinInputs / SUCAP tabs, writes the two new tabs, anchors the historical chart at C32 on PublicComps, and saves the deal-named vS copy. Re-read to confirm the preservation gate held (everything outside the two new tabs identical to input). Then hand back: print the modified Databook path + the JSON valuation_summary + this instruction:

The Databook now carries a PublicComps tab (peer-set table + 5-year historical TEV/EBITDA chart) and a TnxComps tab (precedent-transactions table). Paste the JSON valuation_summary above into the Pre-Screen memo project's chat to feed the Valuation section.

When the filter floor was relaxed, add:

NOTE: the public-comps filter floor was relaxed to maintain the 10-name minimum. See filter_relaxation_flag in the valuation_summary and the relaxed-name rationale in the PublicComps tab.

Constraints

• Never fabricate. Every populated cell traces to a source field; no peer ticker, deal target, or multiple is invented. A cascade-exhausted field degrades to [INSUFFICIENT DATA — <field>] written verbatim into the cell, with a data gap recorded.
• The preservation gate is inviolable. A validation violation refuses with no file written. Pre-existing Databook tabs are identical post-write; the skill is write-additive only on the two new tabs. The .xlsm macro layer is preserved when present.
• Connector failures are named, never papered over. A NOT_FOUND / ACCESS_DENIED / not-connected result is recorded in data_gaps and the cascade falls through to the next tier — never silently treated as received.
• Idempotent. When the two tabs already exist, refuse with comps_already_built; pass --rebuild to force re-population.
• Read-only toward everything upstream. Other skills' scripts, outputs, and the bundled assets/portco-coverage-layout.xlsx (a layout reference, not a write target) are never modified. Never write the legacy Capital-IQ =_xll.ciqfunctions.udf.CIQ(...) formula layer into the new tabs.

Escalation

Halt with a named reason and no output (recorded in the structured output) when:

• The populated Databook is not attached (decline + recommend dl-databook-financials first).
• The borrower's GICS sub-industry cannot be resolved through the four-tier lookup.
• Every public-comps cascade tier exhausts with no candidates.
• The pre-write validation reports a violation that cannot be remediated in the plan (e.g., a peer-set count outside 10–20 with no data gap recorded).

Never auto-decide a bespoke shape; never fabricate a peer or precedent to fill out the count; never write a workbook that fails validation.

References

• reference/cell-map.md — the write surface: PortCo Coverage Template column specs, the PublicComps/TnxComps create-if-absent specs, formatting-consistency rules, the pre-write checks.
• reference/connector-cascade.md — the four cascades (public comps / GICS / historical market data / precedents); per-tier query shape; MCP tool-name discipline; tier-exhaustion and degradation rules; the valuation-summary handoff shape.
• reference/chart-construction.md — the four-series spec, sampling cadence, sigma computation, visualization checklist, openpyxl LineChart construction.
• reference/runtime-surfaces.md — the three runtime surfaces, per-surface write-channel adapter, connector access, persistence and handoff.

Classification & review state

The comps tabs are internal (CONFIDENTIAL, MNPI) — the Databook never leaves the firm, and no RESTRICTED input is read. The output is a draft pending human review: the draft signal is the vS filename — no [DRAFT — HUMAN REVIEW REQUIRED] banner in the workbook body, because the file is an in-place-edited production template and a banner would corrupt it. A reviewer approves and finalizes; never approve, finalize, or issue. [INSUFFICIENT DATA — <what is missing>] for any gap — never fabricate.

Runtime

The scripts run where Python is available: Claude Desktop or claude.ai with code execution enabled. In the Claude Excel add-in the skill still works through its documented add-in write-channel adapter — cell-API writes against the open workbook, with the historical chart anchored as a rendered PNG instead of a native chart object (see reference/runtime-surfaces.md). The cascade, filters, and validation gates are identical on every surface — never improvise an ungated build. Connectors are invoked with fully-qualified Server:tool names; confirm the server name at first invocation.