Click to copy
<role>Engineer setting up a small, tested workspace that pulls a public target's filed numbers.</role>
<task>Run in Claude Code. Create this tree at {{PROJECT_DIR}} and write every file exactly as given. Do not summarise the plan back to me. Print `ls -R` when done.
{{PROJECT_DIR}}/scripts/pull_filed.py : pulls 10-K and 10-Q XBRL facts filed on or before a date, builds LTM
{{PROJECT_DIR}}/scripts/tie.py : ties model inputs to the filed LTM, exits 1 on a break
{{PROJECT_DIR}}/tests/test_pull.py : known-answer test on a real filing
Then run, one at a time, and show the output of each:
cd {{PROJECT_DIR}}
python3 --version (3.10 or higher; if lower, stop and install Python from python.org first)
python3 -m venv .venv
.venv/bin/python -m pip install edgartools==5.59.1 pytest==9.1.1
EDGAR_IDENTITY="{{SEC_NAME}} {{SEC_EMAIL}}" .venv/bin/python -m pytest -q
PASS CONDITION: "1 passed". The test pulls Twitter's FY2021 10-K and must read revenue 5,077,482,000 and operating income -492,739,000. Any other result: stop and use the debug table in prompt 05.
Always call .venv/bin/python, never python3, inside this workspace. {{SEC_NAME}} and {{SEC_EMAIL}} come from prompt 01: the SEC requires a real name and email on every request and allows at most 10 requests per second. If they were not captured, ask for them once and stop.
FILE scripts/pull_filed.py
import sys, csv, os, datetime as dt, warnings
warnings.filterwarnings("ignore")
from edgar import Company, set_identity
ITEMS = {
"revenue": ["RevenueFromContractWithCustomerExcludingAssessedTax", "Revenues", "SalesRevenueNet"],
"operating_income": ["OperatingIncomeLoss"],
"d_and_a": ["DepreciationDepletionAndAmortization", "DepreciationAndAmortization",
"DepreciationAmortizationAndAccretionNet", "OtherDepreciationAndAmortization"],
"stock_comp": ["ShareBasedCompensation", "AllocatedShareBasedCompensationExpense"],
"interest_expense": ["InterestExpense", "InterestExpenseDebt"],
"net_income": ["NetIncomeLoss"],
"cfo": ["NetCashProvidedByUsedInOperatingActivities"],
"capex": ["PaymentsToAcquirePropertyPlantAndEquipment", "PaymentsToAcquireProductiveAssets"],
}
BALANCE = {"cash": ["CashAndCashEquivalentsAtCarryingValue"],
"short_term_investments": ["ShortTermInvestments", "MarketableSecuritiesCurrent", "AvailableForSaleSecuritiesDebtSecuritiesCurrent"]}
def facts(filing):
df = filing.xbrl().facts.to_dataframe()
df = df[~df["is_dimensioned"].fillna(False).astype(bool)]
df = df[df["numeric_value"].notna()]
df["c"] = df["concept"].str.split(":").str[-1]
return df
def durations(df, cands):
for c in cands:
d = df[(df["c"] == c) & (df["period_type"] == "duration")]
if len(d):
d = d.assign(days=(d["period_end"].astype("datetime64[ns]") - d["period_start"].astype("datetime64[ns]")).dt.days)
return c, d
return None, None
def main():
who, ident, as_of = sys.argv[1], sys.argv[2], dt.date.fromisoformat(sys.argv[3])
set_identity(who)
co = Company(int(ident) if ident.isdigit() else ident)
fl = co.get_filings(form=["10-K", "10-Q"])
rows = [f for f in fl if f.filing_date <= as_of]
later = [f for f in fl if f.filing_date > as_of]
if not rows: sys.exit(f"STOP: no 10-K or 10-Q filed on or before {as_of}.")
tenk = next((f for f in rows if f.form == "10-K"), None)
if tenk is None: sys.exit("STOP: no 10-K on or before the as-of date inside the listing.")
q = next((f for f in rows if f.form == "10-Q" and f.filing_date > tenk.filing_date), None)
print(f"{co.name} CIK {co.cik} as of {as_of}")
print(f"USED 10-K {tenk.accession_no} filed {tenk.filing_date}" + (f"; 10-Q {q.accession_no} filed {q.filing_date}" if q else "; no later 10-Q"))
print(f"EXCLUDED as after the as-of date: {len(later)} periodic filing(s)" + (f", first {later[-1].form} filed {later[-1].filing_date}" if later else ""))
os.makedirs("out", exist_ok=True)
try:
fk = facts(tenk); fq = facts(q) if q else None
except Exception as e:
sys.exit(f"STOP: no usable XBRL in the filing ({e}). Filings before 2009 to 2011 carry none; read the statements from the filing text instead.")
src = lambda f: (f.form, f.accession_no, str(f.filing_date))
ff, lt = [], []
for item, cands in ITEMS.items():
c, d = durations(fk, cands)
if c is None:
near = sorted(set(fk[(fk["period_type"] == "duration") & fk["c"].str.contains(cands[0][:10])]["c"]))[:6]
ff.append([item, "NOT FOUND", "", "", "", *src(tenk)]); lt.append([item, f"NOT FOUND in XBRL under {cands}; nearby tags {near}. Read the statement line in the filing text, do not substitute"]); continue
years = d[(d["days"] > 350) & (d["days"] < 380)].sort_values("period_end").drop_duplicates("period_end")
fy = years.iloc[-1]
for _, y in years.iterrows(): ff.append([item, c, y["period_start"], y["period_end"], y["numeric_value"], *src(tenk)])
if q is None:
lt.append([item, f"LTM = FY {fy['numeric_value']:.0f} ({fy['period_end']})", fy["numeric_value"]]); continue
c2, d2 = durations(fq, [c] + [x for x in cands if x != c])
if c2 and c2 != c:
print(f"NOTE {item}: 10-K tags {c}, 10-Q tags {c2}. Same line, different tag; check the two labels match.")
ytd = d2[d2["period_start"] > fy["period_start"]] if c2 else None
if ytd is None or not len(ytd):
lt.append([item, f"STOP: {c} missing from the 10-Q; LTM not built"]); continue
cur = ytd[ytd["period_end"] == ytd["period_end"].max()].sort_values("days").iloc[-1]
prior = d2[(d2["period_end"] < cur["period_end"]) & (abs(d2["days"] - cur["days"]) < 8) & (d2["period_start"] <= fy["period_start"]) & (d2["period_end"] <= fy["period_end"])]
if not len(prior):
lt.append([item, f"STOP: prior-year YTD for {c} not in the 10-Q; LTM not built"]); continue
pr = prior.sort_values("period_end").iloc[-1]
val = fy["numeric_value"] + cur["numeric_value"] - pr["numeric_value"]
ff.append([item, c, cur["period_start"], cur["period_end"], cur["numeric_value"], *src(q)])
ff.append([item, c, pr["period_start"], pr["period_end"], pr["numeric_value"], *src(q)])
lt.append([item, f"LTM {val:.0f} ({c2}) = FY {fy['numeric_value']:.0f} + YTD {cur['numeric_value']:.0f} ({cur['period_start']} to {cur['period_end']}) - prior YTD {pr['numeric_value']:.0f} ({pr['period_start']} to {pr['period_end']})", val])
last = fq if q is not None else fk
lf = q if q is not None else tenk
inst = last[last["period_type"] == "instant"]
assets = inst[inst["c"] == "Assets"]
bdate = assets["period_instant"].max() if len(assets) else inst["period_instant"].max()
inst = inst[inst["period_instant"] == bdate]
for item, cands in BALANCE.items():
hit = inst[inst["c"].isin(cands)]
ff.append([item, hit.iloc[0]["c"] if len(hit) else "NOT FOUND", "", bdate, hit.iloc[0]["numeric_value"] if len(hit) else "", *src(lf)])
debt = inst[inst["c"].str.contains("Debt|Notes|Borrowing|FinanceLease", regex=True) & ~inst["c"].str.contains("Investment|Receivable|Securities|Fair|Issuance|Proceeds|Repayment|PropertyPlant|Asset", regex=True)]
with open("out/debt_lines.csv", "w", newline="") as fh:
w = csv.writer(fh); w.writerow(["concept", "as_of", "value", "form", "accession", "filed"])
for _, r in debt.drop_duplicates("c").iterrows(): w.writerow([r["c"], bdate, r["numeric_value"], *src(lf)])
with open("out/filed_facts.csv", "w", newline="") as fh:
w = csv.writer(fh); w.writerow(["item", "concept", "period_start", "period_end", "value", "form", "accession", "filed"]); w.writerows(ff)
with open("out/ltm.csv", "w", newline="") as fh:
w = csv.writer(fh); w.writerow(["item", "working", "ltm_value"]); w.writerows([r + [""] * (3 - len(r)) for r in lt])
for r in lt: print(" | ".join(map(str, r[:2])))
print(f"Balance sheet date {bdate}; debt lines written to out/debt_lines.csv ({len(debt.drop_duplicates('c'))} rows). TIER 1 source: the accessions above.")
if __name__ == "__main__":
main()
FILE scripts/tie.py
import csv, sys
ltm = {r["item"]: r["ltm_value"] for r in csv.DictReader(open("out/ltm.csv"))}
breaks = 0
for r in csv.DictReader(open(sys.argv[1])):
filed = ltm.get(r["item"], "")
if filed in ("", None):
print(f"{r['item']}: OPEN, no filed LTM figure (see out/ltm.csv)"); continue
m, f = float(r["value"]), float(filed)
gap = (m - f) / abs(f) if f else float("inf")
ok = abs(gap) <= 0.005
breaks += not ok
print(f"{r['item']}: model {m:,.0f} vs filed {f:,.0f} gap {gap:+.2%} {'TIES' if ok else 'BREAK'}")
sys.exit(1 if breaks else 0)
FILE tests/test_pull.py
import subprocess, csv, os, sys, pathlib
ROOT = pathlib.Path(__file__).resolve().parents[1]
def test_known_filing(tmp_path):
who = os.environ.get("EDGAR_IDENTITY")
assert who, "set EDGAR_IDENTITY to your real name and email, as prompt 01 captured"
r = subprocess.run([sys.executable, str(ROOT / "scripts/pull_filed.py"), who, "1418091", "2022-03-01"], cwd=tmp_path, capture_output=True, text=True)
assert r.returncode == 0, r.stdout + r.stderr
ltm = {x["item"]: x["ltm_value"] for x in csv.DictReader(open(tmp_path / "out/ltm.csv"))}
assert float(ltm["revenue"]) == 5077482000
assert float(ltm["operating_income"]) == -492739000
</task>
<trap>Naming a script after a Python standard library module (gettext.py, csv.py, email.py) inside this folder breaks `from edgar import ...` with a circular import error. Keep the names above.</trap>
<stop>If the test does not print "1 passed", do not run prompt 05 on a live target. Say which line failed and stop.</stop>