Hedge Fund Tear Sheet in Excel

<role>Colleague who has built tear sheets at a fund and read hundreds on the allocator side. Neutral: the sheet should be believed because it is right, not flattering. You work for the manager and write for the analyst who will re-derive every number.</role>

<objective>The allocator reads the document before the strategy. The job is a sheet an analyst can check in ten minutes and trust: every figure traced to the returns file, conventions stated, costs and fees priced, the worst window on page one, a backtest labelled as one. A sheet that fails the analyst's re-derivation loses the meeting.</objective>

<privacy>Nothing in this pack is hosted by its author. Your returns go only to your own Claude account. Before loading anything confidential, use a Team or Enterprise plan, or a personal plan with model training turned off in Settings, Privacy.</privacy>

<surface>
First, in one short paragraph, say whether the human is in the right place.
- One strategy, one workbook or CSV: Claude app, a private Project, file creation on so Claude hands back an .xlsx and a PDF. Chat is correct here.
- A folder, several strategies or share classes, a monthly rerun, or a backtest to rebuild: Claude Code pointed at the folder (prompts 12 and 13). It reads files off disk and writes the workbook and PDF.
MODEL: Claude Opus 5.5 for every step. Claude Sonnet 5 only to parse a very large trade or position export first. Never switch model inside a prompt.
WHOLE-PACK STOPS, in chat: a pasted file path or folder listing instead of the file; a backtest to run from prices and rules; more files than one conversation holds. Say what is wrong, name Claude Code, and stop.
ITEM STOPS never end the run: a missing month, fee term or benchmark month. Name it, ask once, mark dependent figures OPEN, finish everything else.
Advise, do not apologise, and do not continue past a whole-pack stop.
</surface>

<onboarding>
Ask one block at a time and wait.
1. WHAT THE SERIES IS: (A) live track record, real money (B) backtest only (C) backtest joined to live results (D) a model portfolio or paper account. Ask for the live start date if C, and for the evidence of live trading if A or C. Evidence: administrator or broker statements, audited financials, or for a listed fund its exchange prices and filed fee schedule. A vague answer ("our return history") counts as no evidence.
2. DATA: (A) uploaded workbook or CSV (B) pasted columns (C) a folder, so Claude Code (D) a mix. Name the sheet and columns that hold dates and returns, or NAV.
3. TERMS: {{STRATEGY_NAME}} {{FREQUENCY}} {{BASIS}} (gross, net of costs, net of all fees) {{FEE_TERMS}} (management, performance, hurdle, high water mark) {{BENCHMARKS}} (the one it should beat, the one the allocator will use) {{RISK_FREE}} (default 3 month T bill: FRED series TB3MS, monthly rate = TB3MS / 100 / 12, same month) {{AUDIENCE}} {{AS_OF_DATE}}.
4. THE CLAIM in two lines, your words: what should the allocator believe after thirty seconds? Everything is tested against it.
5. MODE: (A) CONVERSATION, default: run only the prompts the question needs (stats only: 04 to 05; a stress page: 06; is the backtest real: 09). (B) FULL SHEET: 02, then 03 to 11 in order, 12 and 13 in Claude Code. Rebuilding a backtest first: 12, 13, then 03 to 11 on its output.
</onboarding>

<conventions>
State these on the sheet. Change them only in this block.
Returns are decimals (0.012, not 1.2). Monthly data: 12 periods a year; daily: 252.
Annual return: geometric, (product of 1 plus r) raised to (periods per year / number of periods), minus 1. Never the mean times 12.
Volatility: sample standard deviation times square root of periods per year.
Sharpe: mean of (r minus rf) times periods, over the standard deviation of (r minus rf) times its square root, rf from {{RISK_FREE}} for the same months; rf = 0 only as a labelled extra. A self financing long/short spread earning no cash interest is already an excess return: do not subtract rf again. Unknown which: T bill Sharpe primary, and ask.
Sortino: mean r times periods, over downside deviation, where downside deviation is the square root of the mean of min(r, 0) squared over ALL periods, times the square root of periods. Target return 0 unless the human sets one.
Max drawdown: from a NAV index that starts at 1.0 BEFORE the first return, so a first month loss counts. Report peak month, trough month and recovery month or "not recovered".
Calmar: annual return over the absolute max drawdown, same window.
Up and down capture: annualised geometric return of the strategy over the benchmark's, in benchmark up and down months; benchmark months of exactly 0 left out. Skew: Excel SKEW. Recovery months count from the trough.
Correlation and beta: against each benchmark, same months only.
Under 36 months: every annualised figure carries "short record" and the sheet leads with period returns.
</conventions>

<evidence_tiers>
TIER 1: administrator NAV statements, audited financials, broker or custodian statements.
TIER 2: the manager's own returns workbook, a backtest output, a vendor price download.
TIER 3: an old tear sheet, a pitch deck, a figure typed into an email. It generates a question, never a number.
Every figure on the sheet carries its tier and source, and every derived figure shows its arithmetic.
</evidence_tiers>

<flag_discipline>
1. The claim comes first (prompt 03). A finding matters only if it changes a figure the allocator uses, or the claim.
2. Anything in NORMAL PATTERNS, or explained by the files, goes in one untagged line: "Checked, normal: ..." with the reason.
3. A concern that survives gets one tag: CHANGES THE DECISION, WORTH A QUESTION, or EXPLAINED BY CONTEXT. Only the first blocks the sheet. WORTH A QUESTION only if the answer could move an annual figure by more than 0.1 percentage point, a ratio by more than 0.05, or the claim.
4. Missing optional data (AUM, capacity, a second benchmark) is an OPEN input listed once, not a flag.
5. A flag the human or a document closes stays closed.
6. A finding that tests the claim rather than the sheet (a deep unrecovered drawdown, a return below cash) goes to the claim verdict in prompt 11, not to these tags.
7. Arithmetic outranks this list: a figure that does not tie two ways is a block, whatever the pattern says.
8. In a run where the human is not there to answer, a review gate is recorded as "awaiting sign off" and the run continues. The readiness call lists every open gate.
</flag_discipline>

<normal_patterns>
Looks wrong, is normal, unless the condition holds.
- A diversifier lags the S&P 500 in a strong equity year. Abnormal if it also lags in the equity drawdowns it claims to protect against.
- Sortino above Sharpe. Abnormal if Sortino is BELOW Sharpe: almost always a formula error, most often downside deviation divided by the count of losing months instead of all months.
- Volatility above a hedge fund index such as HFRX Global: investable indices blend many funds and are smoother. Abnormal if it breaches the strategy's own stated risk target.
- Near zero or negative correlation to equities. Abnormal if it flips to high positive in every equity sell off.
- A trend or macro strategy with a flat or losing year after a strong one. Abnormal if the loss exceeds the stated risk budget.
- An ETF's market price return differs from its NAV return by the change in premium or discount, sometimes over 1 point a year. Tie each basis to its own column; gaps up to 0.15 point a year on the same basis come from distribution reinvestment timing. Abnormal if a gap persists in one direction.
- About 2 percent a year between gross and net for a 2 and 20 fund in a good year. Abnormal if net sits above gross, or the gap is zero.
- A short record with no 2008 or 2020 row. The window is not covered, so it is absent. Abnormal if a row for it appears.
</normal_patterns>

<rules>
- Never invent a figure, month, fee or benchmark value. Missing: ask once, or mark OPEN.
- A backtest is labelled "hypothetical" on every page it appears, with the start of live trading marked on every chart.
- Net of fees is shown with at least the same prominence as gross.
- Historical stress rows come only from months the series covers. A modelled shock is labelled "hypothetical scenario" in its own table.
- Analysis, not investment advice and not legal advice. Marketing rules on performance are the manager's compliance call.
- End every prompt with "Next step:".
</rules>

<how_to_adapt>Conventions: the conventions block only. House brand: colours, font, logo and firm name live in one config the workbook and PDF read (prompt 10). Other assets: add that asset's normal patterns. Daily data: set FREQUENCY and every formula switches to 252.</how_to_adapt>

<trap>The plausible sheet uses the mean times 12, a Sharpe with no risk free rate, and a backtest with no costs. Each flatters by a margin an analyst finds in minutes, and one found error makes them doubt every other number.</trap>

<review_gate>Read every answer back before prompt 02. If FEE_TERMS is blank, the net of fee figures stay OPEN and the sheet cannot be marked READY.</review_gate>

<task>Run the desk on this synthetic sample first. Work every figure yourself from the rows, then compare line by line with EXPECTED OUTPUT and print MATCH or MISMATCH per line. Judge substance, not layout; a cleared normal pattern counts as MATCH. If any line mismatches, say which and STOP. Do not load real returns until every line matches.</task>

<sample>
Strategy K (fictional), live track record, monthly, returns GROSS of trading costs and fees. Risk free 0.35 percent every month. Benchmark: an equity index.
Month: strategy / benchmark
2025-01: 0.021 / 0.025 | 2025-02: -0.012 / -0.014 | 2025-03: 0.034 / -0.058 | 2025-04: -0.028 / -0.008
2025-05: 0.015 / 0.062 | 2025-06: 0.009 / 0.050 | 2025-07: -0.041 / 0.022 | 2025-08: 0.026 / 0.019
2025-09: 0.018 / 0.035 | 2025-10: -0.007 / 0.023 | 2025-11: 0.030 / 0.002 | 2025-12: 0.012 / 0.006
Trading: rebalances monthly, one way turnover 40 percent a month, cost 0.15 percent of value traded.
Fee terms: not provided.
Old tear sheet (Tier 3): "Max drawdown -3.1%. Rates shock 2022: -7.9%."
The claim: "Uncorrelated returns with shallow drawdowns."
</sample>

<expected_output>
E1 RETURN: product of (1 + r) minus 1 = 7.65 percent for 12 months. Benchmark 17.05 percent. Short record label on every annualised figure.
E2 VOLATILITY: sample std 0.02357 times square root 12 = 8.16 percent.
E3 SHARPE: excess mean 0.002917 times 12 = 0.0350, over 0.02357 times square root 12 = 0.0816, gives 0.43. With rf = 0 it reads 0.94; shown only as a labelled extra.
E4 SORTINO: downside deviation square root of mean of min(r,0) squared = 0.01488, times square root 12 = 5.16 percent. Mean 0.006417 times 12 = 7.70 percent. Sortino 1.49. Above Sharpe: Checked, normal.
E5 MAX DRAWDOWN: NAV from 1.0, peak 1.04305 after March, trough 0.99574 after July, -4.54 percent, recovered November. Calmar 7.65 / 4.54 = 1.69.
E6 CORRELATION -0.07, beta -0.05 (2 decimals; -0.065 and -0.049 unrounded). Lagging the benchmark by 9.4 points in an up year: Checked, normal for a diversifier.
E7 COSTS: 0.40 times 0.15 percent = 0.06 percent a month. Net of costs 6.88 percent, Sharpe 0.34, max drawdown -4.77 percent.
E8 FEES: OPEN, fee terms not provided. Printing any net of fee figure is a MISMATCH. This is the STOP path firing once, in safety.
E9 OLD SHEET: its -3.1 percent drawdown does not tie to -4.54: CHANGES THE DECISION until replaced. The 2022 row cannot come from a 2025 record: remove it, or move it to a hypothetical scenario table with its method. Keeping it as history is a MISMATCH.
E10 THE CLAIM: "uncorrelated" holds on this sample (-0.07, short record). "Shallow drawdowns" needs the net figure, -4.77 percent, not the old -3.1.
</expected_output>

<review_gate>Ten MATCH lines, with E8 reading OPEN, then go to 03.</review_gate>

<role>The allocator's analyst on first read, then the manager's colleague.</role>
<task>
Using the data source from prompt 01, write the frame the sheet must fit, before computing anything:
1. The strategy in three lines: what it trades, how it makes money, what it is meant to do in a portfolio.
2. What the series is (live, backtest, joined, paper), its first and last month, and the live start date if joined.
3. The claim from prompt 01 restated as the two or three figures that would prove or break it (a diversifier: correlation and crisis months; an absolute return fund: drawdown and hit rate; a growth equity fund: return against its benchmark after fees).
4. Who reads it ({{AUDIENCE}}) and the first three questions that reader asks, in their words.
5. Page one order: the figures that answer those questions, with the worst window and the net figure on page one.
</task>
<trap>Writing the sheet around the best statistic. The reader looks for the number that tests the claim; if page one hides it, they assume it is bad.</trap>
<stop>If nothing shows the series was traded (no administrator, broker or audited statement, no fee terms, no vehicle), do not guess and do not accuse: label the whole series hypothetical until evidence arrives, say what document would change that, and continue. The sheet cannot be READY under that label.</stop>
<output_format>The frame in five numbered parts, one page. No statistics yet.</output_format>
<review_gate>The human confirms the claim and the page one order before prompt 04.</review_gate>

<role>Analyst turning a working spreadsheet into one clean returns table.</role>
<task>
Work from the data source in prompt 01. Find the date and return (or NAV) columns and build one table: month end date, strategy return, each benchmark, risk free, a basis column (gross, net of costs, net of fees) and a live or hypothetical column. Then check, one line each:
- Returns as decimals: values between 1 and 30 are percent, divide by 100; text with % signs is converted. Log both.
- NAV to returns: r = NAV this month / NAV last month minus 1. The first month needs the prior NAV; if absent, mark it OPEN rather than dropping it.
- Every month from first to last is present. Dates are month ends. Daily data is compounded to months only if the human wants a monthly sheet.
- Benchmarks and risk free cover the same months. Rows outside the overlap are kept for the strategy and excluded from relative figures.
- Hidden rows, duplicates, subtotals and merged headers are removed and logged.
- Distributions: price series without dividends understate return. Use total return or say which it is.
- Tie out: if administrator statements or audited financials are provided, each calendar year's linked return must match the audited annual figure. List every break before any statistic. A fund's own fact sheet is a cross check, not a source: compare like with like (market price against the price column, NAV against NAV) and record gaps; it never replaces a figure.
</task>
<trap>COUNT and AVERAGE in Excel skip a blank month without warning, so a missing month shortens the record and lifts the annual return. Find gaps by date, never by counting rows.</trap>
<stop>A missing month inside the record: name it, ask for the administrator figure, and hold every statistic that spans it OPEN. Never fill it with zero or an average.</stop>
<output_format>The clean table and an intake log: each change, row and reason.</output_format>
<review_gate>The human confirms the first and last month, the count of months, and the log.</review_gate>

<role>Performance analyst whose numbers will be re-derived by the reader.</role>
<task>
From the clean table in prompt 04, compute under the conventions in prompt 01: period return, annual return, volatility, Sharpe, Sortino, max drawdown with peak, trough and recovery, Calmar, best and worst month with dates, percent positive months, skew, first order autocorrelation, calendar year returns, and against each benchmark: correlation, beta, up capture, down capture, and the return difference each year.
If daily NAV or daily prices exist, also report the daily max drawdown: month ends hide the intra month low.
Compute each figure twice: once in code or step by step, once as the Excel formula the workbook will carry (for example annual return as =EXP(SUMPRODUCT(LN(1+R)))^(12/COUNT(R))-1). Both must agree to 4 decimals.
Optional library cross-check in Claude Code, feeding RETURNS, never NAV: quantstats with periods=12 in every stats call and periods_per_year=12 in reports (its default of 252 turns a 6 percent year into hundreds); empyrical-reloaded with period='monthly' and risk_free as a MONTHLY rate. Both define Sortino as ours. An old Excel sheet that takes STDEV of losing months only will differ: say so, do not match it.
</task>
<trap>Sharpe with no risk free rate. With T bills near 4 percent, rf = 0 can double a modest Sharpe, and an analyst recomputes it first.</trap>
<stop>If the two computations disagree beyond 4 decimals, print both and the formula for each and stop. Do not pick one.</stop>
<output_format>A statistics table: figure, value, Excel formula, tier and source, short record label where due. Then calendar years side by side with each benchmark.</output_format>
<review_gate>Every figure ties two ways. Sortino below Sharpe on positive skew is checked as a likely formula error before it is printed.</review_gate>

<role>Risk analyst preparing the page the allocator's risk team reads first.</role>
<task>
From prompt 05:
1. The five deepest drawdowns: peak, trough, depth, months down, months to recover or "not recovered". If one unrecovered drawdown swallows the rest, add the worst peak to trough loss inside each calendar year.
2. Worst month, worst rolling 3 and 12 months, each with dates and the benchmark over the same months.
3. Historical stress windows, only those fully inside the LIVE record: August 2007, November 2007 to February 2009, August to September 2011, August to September 2015, October to December 2018, February to March 2020, January to October 2022, March 2023. For each: strategy and benchmark return, the change from the window's start, and the worst peak to trough inside it. Add the benchmark's three sharpest non overlapping 3 month rebounds: long/short, short biased and trend strategies often lose most when the market snaps back. Windows covered only by a backtest, or every window when the whole series is labelled hypothetical, go in a table labelled hypothetical. A partly covered window names its months or is left out.
4. If the human wants shocks the record does not cover, a separate table titled "Hypothetical scenarios" with the method in one line per row (for example beta to the benchmark times the benchmark's move in that window).
</task>
<trap>A stress row deeper than the headline max drawdown. On the same series and months that is impossible, so it means a different series, a simulated shock shown as history, or a drawdown computed without the starting 1.0.</trap>
<stop>A window the record does not cover gets no historical row. Do not estimate one and place it in the history table.</stop>
<output_format>Drawdown table, worst windows table, historical stress table, optional hypothetical table. The worst window goes on page one.</output_format>
<review_gate>Every historical row is re-derived from the monthly table. No row is deeper than the max drawdown.</review_gate>

<role>Operations analyst taking the return from paper to what an investor keeps.</role>
<task>
Build three columns side by side: gross, net of trading costs, net of all fees.
Trading costs: turnover times cost per unit traded, per month. Turnover from the human's trade log or a stated estimate. Cost from their fills, or their broker's commission plus half the spread. Long/short: turnover counts both legs. No cost data but turnover known: run 10, 25 and 50 basis points per unit traded as a labelled sensitivity band, never as the figure. No turnover either: report only the break even cost per month that takes the return to zero. The sheet stays HOLD until the manager supplies their own. If the series is already net of costs and fees (a fund's own NAV or price), say so, show it as net, and skip the cost columns and the sensitivity; show gross only if the manager supplies it.
Fees from {{FEE_TERMS}}: management fee monthly on opening NAV; performance fee only above the high water mark and the hurdle, on the stated crystallisation schedule. Show the fee in each month.
Recompute annual return, volatility, Sharpe and max drawdown for each column, and show net of costs at 0, 1 and 2 times the manager's cost so the reader sees how much the edge depends on it.
</task>
<trap>A performance fee charged on every up month. With a high water mark it is charged only on new highs, and after a drawdown nothing is due until the loss is recovered.</trap>
<stop>No fee terms: net of fee stays OPEN and the sheet cannot say net. No turnover: the cost line is a labelled assumption, never a measured figure.</stop>
<output_format>Gross, net of costs, net of fees, with the arithmetic for one month shown in full. Net goes on page one with at least equal prominence.</output_format>
<review_gate>If costs remove more than a third of the gross return, say so on page one.</review_gate>

<role>Portfolio construction analyst checking the allocation behind the returns.</role>
<task>
From positions or sleeve returns (if the human has them): gross and net exposure by sleeve over time, each sleeve's share of total risk (weight times its covariance with the portfolio, over portfolio variance), the largest single position, and correlation between sleeves. Then answer plainly: is the risk spread the way the strategy says it is, or does one sleeve carry most of it?
Efficiency: from sleeve returns, compare the current mix with an equal risk mix and a minimum variance mix at the same volatility: Sharpe, max drawdown and turnover for each. State the estimation window, rerun on one other window, and say whether the ranking holds. At most two changes, within the manager's stated limits, each with its effect on volatility and Sharpe.
If only a total return series exists, skip the sleeve work, report rolling 12 month correlation to each benchmark, and list sleeve data as OPEN.
</task>
<trap>Capital weights read as risk weights: a sleeve with 10 percent of capital and ten times leverage can carry half the risk. And an optimiser fed one window's returns picks that window's winner; a mix that only wins on one window is not more efficient.</trap>
<stop>No position or sleeve data: OPEN input, no exposure chart. Never infer exposures from the return series and present them as holdings.</stop>
<output_format>Exposure table, risk share by sleeve, the plain answer, up to two changes.</output_format>
<review_gate>Risk shares sum to 100 percent. The exposure panel shows only what the data supports.</review_gate>

<role>Model validation reviewer: the check that decides whether the track record is signal or luck.</role>
<task>
For any hypothetical or unevidenced part (a check it cannot answer is NOT KNOWN, never assumed):
1. Lookahead: does each signal use only data known before the trade? Name where the signal is lagged.
2. Survivorship: was the universe as it was at each date, including names since delisted?
3. Search: how many variants or parameter sets were tried (N)? The best Sharpe expected from luck alone is E[max] = sd x ((1 minus g) x NORM.S.INV(1 minus 1/N) + g x NORM.S.INV(1 minus 1/(N x e))), g = 0.5772, sd the sample spread of the trial Sharpes (Bailey and Lopez de Prado). Only N known: per period sd = square root of 1/(T minus 1), stated. Gate the strategy's OWN Sharpe (or the Sharpe of strategy minus benchmark returns) against E[max], never the gap between two Sharpes. In Claude Code, ffn does this: calc_expected_max_sharpe(n_trials, sr_std) takes no series; calc_deflated_sharpe_ratio(returns, trial_sharpe_ratios, nperiods=12) takes monthly returns and every trial's Sharpe.
4. Out of sample: the months after design, or after live start, reported separately.
5. Sensitivity: the result at neighbouring parameters. A lone peak is fragile.
A fully live record skips checks 1 to 5; say so in one line.
For live returns: first order autocorrelation (rho). Liquid futures strategies sit near 0; Getmansky, Lo and Makarov (2004) found about 0.22 in convertible arbitrage, where prices are stale. If rho is more than two standard errors (2 over the square root of the number of months) above zero in liquid assets, or holdings are hard to price, show the smoothing adjusted volatility (volatility times the square root of (1 plus rho) over (1 minus rho), a first order approximation) and Sharpe next to the reported ones.
Grade in the style of bank model risk guidance, voluntary, the firm is not a bank (SR 26-2 replaced SR 11-7 on 17 April 2026): conceptual soundness, data, outcomes, ongoing monitoring. Never write "SR 11-7 compliant" or "SR 26-2 compliant".
</task>
<trap>A backtest Sharpe above 2 in liquid markets with no stated trial count. Ask for the number of variants before believing it.</trap>
<stop>If the human cannot say how many variants were tried, record "search unknown" and the sheet cannot drop the hypothetical label.</stop>
<output_format>One table: each check (1 to 5, smoothing) with PASS, WATCH, FAIL, NOT KNOWN or NOT APPLICABLE and the evidence, then the four part grade in one line each, then one line on what the allocator should be told.</output_format>
<review_gate>A FAIL on lookahead or survivorship blocks the sheet until fixed and rerun.</review_gate>

<role>Analyst building the workbook the allocator's team will open and click through.</role>
<task>
Build one .xlsx (Claude app with "Code execution and file creation" on, or Claude Code with XlsxWriter; read the manager's file with pandas and openpyxl, write with XlsxWriter):
- Sheet Returns: the clean table from prompt 04, one row per month.
- Sheet Calc: NAV index starting at 1.0, running peak, drawdown, excess return, and rolling 12 month columns, all as formulas on Returns.
- Sheet Stats: every figure from prompt 05 as a live formula, with its convention in the next cell, and the prompt 05 value stored as the cached result so previews show numbers. Newer functions need their stored prefix when written from code (=_xlfn.STDEV.S(...)) or Excel shows #NAME?. Avoid =PRODUCT(1+R): it needs array entry before Excel 365.
- Sheet Monthly: year by month table with a three colour scale (red, white, green, centred on 0) and a year total column.
- Sheet Costs and Fees, Sheet Stress, Sheet Disclosures (basis, period, benchmark definitions, hypothetical label, conventions).
- Charts: cumulative growth of 1.0 against benchmarks with live start marked, and drawdown.
- Brand: one config block for firm name, colours, font and logo.
Then prove the formulas: make a copy with no cached results (load and save it with openpyxl, which drops them), recalculate it (Excel, or soffice --headless --convert-to xlsx), read it back and compare every Stats cell with prompt 05. Numbers within 0.0001; dates as dates; text exactly. Tables written as values (stress, drawdown list) carry a note that they come from the tested Python, not live formulas.
</task>
<trap>Storing the Python value as the cached result makes every cell look right even when its formula is wrong. The error surfaces the day the allocator's analyst presses F9. Only a recalculated copy proves the formula.</trap>
<stop>Any Stats cell that differs from prompt 05 by more than 0.0001 after recalculation: print the cell, both values, and stop.</stop>
<output_format>The .xlsx, then a tie out table: cell, formula result, prompt 05 value, difference.</output_format>
<review_gate>Every cell ties. The allocator can click any statistic and see its formula.</review_gate>

<role>The person who signs off the sheet before it goes to the allocator.</role>
<task>
Build a one page PDF (landscape): header with strategy, period, basis and benchmarks; six headline figures net of fees (annual return, volatility, Sharpe, max drawdown, worst 12 months, correlation to the main benchmark), each beside the benchmark; if fees are OPEN, print the supplied basis with "net of fees: OPEN" at equal size; cumulative growth and drawdown charts; monthly table net of fees; worst windows and historical stress; exposures if supported; a terms box (fees, hurdle, high water mark, liquidity, administrator, auditor) with OPEN where unknown; three key takeaways; disclosures: gross or net and fee basis, the live start date and the hypothetical label with its criteria, assumptions and limits, benchmark definitions, data source, currency, past performance line. Every figure from the workbook, not retyped.
Key takeaways: three sentences, each tied to a figure on the page and to the claim. No adjectives the figures do not support.
Readiness call, one of:
- READY: every figure ties, net shown, labels in place. Terms box items marked OPEN do not stop READY if the page shows them as OPEN.
- READY WITH LABELS: ties, but a hypothetical, short record or OPEN input must be printed on the page.
- HOLD: a figure does not tie; a series with no evidence of trading is shown as a track record; trading costs are not priced from the manager's own data on a backtest, gross or unknown basis series; net of fees cannot be shown because the fees or the basis are unknown; or the worst window is not on page one.
Separately, the claim from prompt 01: HOLDS, PARTLY HOLDS or DOES NOT HOLD, each part with the figure that decides it. A correct sheet can carry a claim that does not hold; say so on the page rather than soften the sheet.
Print what happens by default if nobody acts: nothing goes out unsigned; once signed, it goes out with the labels listed.
</task>
<trap>The takeaway that restates the best number. The reader wants the number that tests the claim, which is usually the worst window or the net figure.</trap>
<stop>A HOLD stops the PDF being called final, not the work. Finish the page, stamp it DRAFT, and list what clears the hold.</stop>
<output_format>The PDF, the three takeaways with their figure references, the readiness call with reasons.</output_format>
<review_gate>A named human signs the readiness call. Marketing rules on performance are the manager's compliance review.</review_gate>

<role>Engineer turning the steps above into a folder the manager reruns each month.</role>
<task>
Create this tree in {{PROJECT_DIR}}. Write every file. Print `ls -R` when done.
  data/                  returns.xlsx or .csv, benchmarks, risk free
  config.toml            conventions, fee terms, benchmarks, brand (read with Python's built in tomllib)
  tearsheet/load.py      prompt 04 checks, writes intake_log.csv
  tearsheet/stats.py     prompt 05 conventions, plain pandas and numpy
  tearsheet/stress.py    prompts 06 and 08: drawdown list, windows, rolling correlation
  tearsheet/costs.py     prompt 07
  tearsheet/workbook.py  prompt 10 with XlsxWriter, formulas plus cached values
  tearsheet/recalc.py    prompt 10 proof: LibreOffice recalculation of a copy, read back
  tearsheet/pdf.py       prompt 11 with matplotlib
  tearsheet/gate.py      prompt 09 noise gate, ffn optional
  tests/test_stats.py    the prompt 02 sample as a known answer fixture
  output/                tearsheet.xlsx, tearsheet.pdf, tieout.csv, intake_log.csv, proof/
  run.py                 one command: load, stats, workbook, recalc, tie out, pdf
Python 3.12 or newer (tested on 3.14). Install: python3 -m venv .venv, then .venv/bin/pip install pandas==3.0.6 numpy==2.5.3 openpyxl==3.1.5 xlsxwriter==3.2.9 matplotlib==3.11.2 pytest==9.1.1, plus .venv/bin/pip install ffn==1.2.2 for the noise gate (LibreOffice for the recalculation proof, from libreoffice.org; on a Mac its command is /Applications/LibreOffice.app/Contents/MacOS/soffice, not on the PATH)
Tests first: write tests/test_stats.py from the prompt 02 expected figures, run pytest, watch them fail, then write stats.py until they pass.
First run pass condition: pytest green, then .venv/bin/python run.py on your data prints the tie out with every difference under 0.0001 and writes tearsheet.xlsx and tearsheet.pdf.
</task>
<trap>Joining the benchmark by row number instead of by month. One missing benchmark month shifts every later row and the correlation looks plausible but is wrong. Join on the date.</trap>
<stop>If LibreOffice is not installed, the formula proof cannot run: say so, mark the tie out "formulas not proven", and do not call the workbook final.</stop>
<output_format>The tree, the test run output, the first run output.</output_format>
<review_gate>Debug by symptom: zeros in a preview means no cached value; #NAME? means a missing _xlfn prefix; an annual return a little too high means a skipped month; Sharpe near double means rf missing; drawdown too shallow means no starting 1.0. Done when a new month added to data/ and .venv/bin/python run.py reproduces the sheet. Next: a second share class, a daily risk page, an allocator data room export.</review_gate>

<role>Quant rebuilding the strategy's history so the return series on the sheet comes from code anyone can rerun.</role>
<task>
In the folder from prompt 12, add tearsheet/backtest.py. Inputs: daily or monthly prices for the universe in data/prices/, the rules in config.toml (signal, lookback, rebalance day, position limits, cost per unit traded). Output: data/returns_backtest.csv with month, gross return, turnover, cost, net of cost return, and a live or hypothetical column.
Order: write tests/test_backtest.py first with two known answers, a buy and hold of one asset that must equal its own price return, and a rule on a ten row toy price series worked by hand. Run pytest, watch them fail, then write the code until they pass.
Signals use prices up to the close BEFORE the trade: compute the signal, then shift it one bar, in one place in the code.
The universe is as it was on each date. A price file of today's constituents is survivorship: say so on the sheet if that is all there is.
Then feed returns_backtest.csv through run.py, so the sheet labels it hypothetical and prompt 09 grades it. Record every parameter set tried in trials.csv; that count is N in prompt 09.
</task>
<trap>A backtest that trades on the bar that made the signal. Sharpe above 3 in liquid markets usually means exactly this: shift the signal one bar and rerun before anything else.</trap>
<stop>No point in time universe and no delisted names: run it, but the sheet carries "survivorship not controlled" and cannot be READY.</stop>
<output_format>The two test results, the backtest summary gross and net of cost, trials.csv row count.</output_format>
<review_gate>Done when pytest passes, the shifted signal is in one function, and the sheet built from returns_backtest.csv reads hypothetical on every page. Model: Claude Opus 5.5.</review_gate>