--- title: "1.5.1 Financial statements, XBRL and EDGAR, no look-ahead" description: "Financial statements, TTM ratios and SEC EDGAR XBRL with filing dates: Q4 from the 10-K, restatements and a join without look-ahead bias." url: "https://pythonparatrading.com/en/python-trading-course/level-1/financial-statements-xbrl-and-sec-edgar/" lang: "en" image: "https://pythonparatrading.com/og/en/python-trading-course/level-1/financial-statements-xbrl-and-sec-edgar.png" --- # 1.5.1 · Financial statements, ratios, XBRL and SEC EDGAR > A quarter ends on March 31, but its figures do not exist for the market until the company files them, weeks later, and they may change months after that. This lesson builds quarterly and TTM ratios as they were known each day and measures how far ahead the naive join by period date runs. - Course: [Level 1: Financial data, markets and analysis](https://pythonparatrading.com/en/python-trading-course/level-1/) - Module: 1.5 · Fundamental and macro data - Duration: 65 min - Prerequisites: [0.5.3 · Selection, filtering, groupby, merge, pivot, resample and moving windows](https://pythonparatrading.com/en/python-trading-course/level-0/pandas-groupby-merge-resample-rolling/), [1.1.1 · Market participants and trading venues](https://pythonparatrading.com/en/python-trading-course/level-1/market-participants-and-trading-venues/), [1.4.4 · Universes without survivorship bias: delistings, mergers and symbol changes](https://pythonparatrading.com/en/python-trading-course/level-1/survivorship-bias-and-point-in-time-data/) ## By the end of this lesson - Tell flow items (income statement) from stock items (balance sheet) and know what a 10-Q contains and what a 10-K contains. - Get the fourth quarter by subtracting the three 10-Qs from the 10-K, using the versions known on the 10-K's filing date. - Compute point-in-time TTM EPS, ROE, margin and P/E and compare them with the naive join by `periodo_fin`. - Treat restatements as new versions with their own filing date. - Read the structure of EDGAR's `companyfacts` JSON and extract from it a series with a knowledge date. ## Three statements and two dates Companies listed in the US file their accounts with the SEC on two forms: the **10-Q**, quarterly, for the first three quarters of the fiscal year, and the **10-K**, annual and audited, in place of the fourth 10-Q. Both carry the same three statements: | Statement | What it measures | Item type | Examples | |---|---|---|---| | Income statement | What was earned or lost **during** the period | Flow | Revenue, net income, EPS | | Balance sheet | What is owned and owed **on a date** | Stock | Total assets, shareholders' equity, debt | | Cash flow statement | The money that came in and went out during the period | Flow | Operating cash flow, investments, dividends | The difference between flow and stock decides how they aggregate. A year's revenue is the sum of its four quarters; a year's equity is not the sum of anything: it is the equity on the last day. And in the 10-K, flow items are reported for the **full fiscal year**: there is no published “fourth quarter”, it has to be obtained by subtraction. Each figure also has two dates. The **period date** (`periodo_fin`) is the last day it covers: March 31 for the first quarter of a calendar fiscal year. The **filing date** (`fecha_publicacion`) is the day the company files the form and the figure starts to exist for the market. Weeks pass between the two: the SEC gives large accelerated and accelerated filers 40 days for the 10-Q (45 for everyone else) and 60, 75 or 90 days for the 10-K depending on the filer's size[^1]. After that, the figure can still change. ```mermaid flowchart TB C[Quarter end] -->|25-47 days| P[10-Q filing] P -->|after the close| D[Next session] D -->|months later| R[Restatement] ``` A backtest that makes decisions with income statement data has to use the second date. With the first, each quarter reaches the model before anyone knew it. This is the look-ahead bias of lesson 0.1.1, in its most frequent form with fundamental data, and the accounting version of the point-in-time data of lesson 1.4.4. ## The data: statements as they were known `fundamentales_pit.csv` holds the quarterly statements of ten fictional stocks from the universe of lesson 1.4.4, from 2018 to 2025, with one row per filing. Two of the stocks stop filing: one goes bankrupt in 2023 and another is acquired in 2024. Figures are in millions of dollars (shares in millions). ```python import pandas as pd fu = pd.read_csv("fundamentales_pit.csv", parse_dates=["periodo_fin", "fecha_publicacion"]) print(fu.shape, fu["ticker"].nunique(), "stocks") cols = ["periodo_fin", "fecha_publicacion", "formulario", "ingresos", "beneficio_neto", "version"] fu.loc[(fu["ticker"] == "ZXNW") & (fu["periodo_fin"].dt.year == 2021), cols] ``` ```output (295, 10) 10 stocks periodo_fin fecha_publicacion formulario ingresos beneficio_neto version 101 2021-03-31 2021-05-07 10-Q 3319.3 383.3 1 102 2021-06-30 2021-08-09 10-Q 3662.3 519.0 1 103 2021-09-30 2021-10-26 10-Q 3556.9 554.3 1 104 2021-09-30 2022-02-22 10-Q 3361.8 392.4 2 105 2021-12-31 2022-02-25 10-K 14055.8 1804.0 1 ``` ZXNW's 2021 fiscal year shows the three points of this lesson. The three 10-Qs carry their quarters. The 10-K carries 14,055.8 million in revenue: the whole year, not the fourth quarter. And the third quarter appears twice: version 1, filed on October 26, 2021, and version 2, a downward **restatement** filed on February 22, 2022, almost five months after the quarter ended. How long does each form take? ```python fecha_pub, fecha_fin = fu["fecha_publicacion"], fu["periodo_fin"] fu["retraso"] = (fecha_pub - fecha_fin).dt.days fu.groupby(["formulario", "version"])["retraso"].agg( ["count", "min", "median", "max"]) ``` ```output count min median max formulario version 10-K 1 66 45 54.0 62 10-Q 1 225 25 36.0 47 2 4 145 234.0 326 ``` The 10-Qs are filed between 25 and 47 days after the quarter ends (median, 36); the 10-Ks, between 45 and 62 (median, 54), because they are audited; the figure at the top draws both distributions. The four restatements arrive between 145 and 326 days after the period they correct. That delay is exactly what a join by period date brings forward. ## From the 10-K to the fourth quarter To get a quarterly series, each 10-K has to be turned into a quarter. For flow items: $$ \text{T4} = \text{10-K} - (\text{T1} + \text{T2} + \text{T3}) $$ The point-in-time detail lies in which T1, T2 and T3 are subtracted: the ones known **on the 10-K's filing date**. If a quarter was restated earlier, the new version is subtracted (the 10-K already adds up the restated figures); if it had been restated later, the old one. Stock items (assets, equity) are left alone: the 10-K gives the balance as of December 31, which is the fourth quarter's. ```python FLUJOS = ["ingresos", "beneficio_neto"] def trimestrales(fu): """One row per filing with the quarter's flows. In the 10-K, T4 = fiscal year - (T1 + T2 + T3), with the T1-T3 versions known on the 10-K's filing date.""" filas = [] for _, g in fu.groupby("ticker"): for _, f in g.iterrows(): f = f.copy() if f["formulario"] == "10-K": ano = f["periodo_fin"].year previos = g[(g["periodo_fin"].dt.year == ano) & (g["formulario"] == "10-Q") & (g["fecha_publicacion"] <= f["fecha_publicacion"])] ultimos = (previos.sort_values("version") .groupby("periodo_fin").last()) assert len(ultimos) == 3, "missing quarters" for c in FLUJOS: f[c] = f[c] - ultimos[c].sum() filas.append(f) return pd.DataFrame(filas).reset_index(drop=True) tr = trimestrales(fu) tr.loc[(tr["ticker"] == "ZXNW") & (tr["periodo_fin"].dt.year == 2021), cols] ``` ```output periodo_fin fecha_publicacion formulario ingresos beneficio_neto version 101 2021-03-31 2021-05-07 10-Q 3319.3 383.3 1 102 2021-06-30 2021-08-09 10-Q 3662.3 519.0 1 103 2021-09-30 2021-10-26 10-Q 3556.9 554.3 1 104 2021-09-30 2022-02-22 10-Q 3361.8 392.4 2 105 2021-12-31 2022-02-25 10-K 3712.4 509.3 1 ``` ZXNW's fourth quarter of 2021 comes to 3,712.4 million in revenue: 14,055.8 minus the three quarters, with the third in its restated version, which was filed three days before the 10-K. The `assert` is not decorative: a stock with a missing 10-Q would give an inflated T4 without any warning. ## Point-in-time TTM A single quarter is noisy and seasonal. Valuation and profitability ratios are computed over the **trailing twelve months** (TTM): the sum of the last four quarters for flow items and the latest balance for balance sheet items. | Ratio | Formula | What it measures | |---|---|---| | TTM EPS | TTM earnings / diluted shares | Earnings per share over the last year | | P/E | price / TTM EPS | How many years of earnings the price pays for | | ROE | TTM earnings / shareholders' equity | Return on the shareholders' capital | | Net margin | TTM earnings / TTM revenue | How much of each dollar sold remains as profit | Point-in-time means recomputing the TTM **at each filing** with what was known that day: one row per knowledge date, not per period. The function walks through each stock's filings in order and keeps the latest known version of each quarter: ```python def ttm_pit(tr): out = [] orden = tr.sort_values(["fecha_publicacion", "version"]) for tk, g in orden.groupby("ticker"): conocido = {} # periodo_fin -> latest known version for _, f in g.iterrows(): conocido[f["periodo_fin"]] = f ult = pd.DataFrame(list(conocido.values())) ult4 = ult.sort_values("periodo_fin").tail(4) dias = (ult4["periodo_fin"].iloc[-1] - ult4["periodo_fin"].iloc[0]).days if len(ult4) < 4 or dias > 280: # 4 consecutive continue out.append({ "ticker": tk, "disponible": f["fecha_publicacion"], "periodo_fin": ult4["periodo_fin"].iloc[-1], "ing_ttm": ult4["ingresos"].sum(), "ben_ttm": ult4["beneficio_neto"].sum(), "patrimonio": ult4["patrimonio_neto"].iloc[-1], "acciones": ult4["acciones_diluidas"].iloc[-1]}) return pd.DataFrame(out) pit = ttm_pit(tr) pit["bpa"] = pit["ben_ttm"] / pit["acciones"] print(len(pit), "point-in-time rows") ver = ["disponible", "periodo_fin", "ben_ttm", "bpa"] pit.loc[pit["ticker"] == "ZXNW", ver].iloc[10:15].round( {"ben_ttm": 1, "bpa": 2}) ``` ```output 265 point-in-time rows disponible periodo_fin ben_ttm bpa 90 2021-08-09 2021-06-30 1938.4 15.63 91 2021-10-26 2021-09-30 2006.7 16.19 92 2022-02-22 2021-09-30 1844.8 14.88 93 2022-02-25 2021-12-31 1804.0 14.55 94 2022-04-27 2022-03-31 1936.5 15.72 ``` The rows for February 22 and 25, 2022 show the difference from a table by period. No new quarter ends on February 22: the restatement of the third quarter arrives and TTM EPS drops from 16.19 to 14.88 that day. Three days later the 10-K arrives and the TTM takes in the fourth quarter. A table indexed by `periodo_fin` cannot represent either of the two. ## The naive join and how many days it runs ahead The naive way to build the same series is the one a database of “fundamentals by period” gives you: the final version of each quarter, indexed by its end date. When it is joined to prices by `periodo_fin`, each quarter is used from the day it ends. ```python final = (tr.sort_values("version") .groupby(["ticker", "periodo_fin"]).last() .reset_index().sort_values(["ticker", "periodo_fin"])) g = final.groupby("ticker") final["ben_ttm"] = g["beneficio_neto"].transform( lambda s: s.rolling(4).sum()) final["ing_ttm"] = g["ingresos"].transform( lambda s: s.rolling(4).sum()) final["bpa"] = final["ben_ttm"] / final["acciones_diluidas"] ingenuo = final.dropna(subset=["ben_ttm"]) print(len(ingenuo), "rows by period") ``` ```output 261 rows by period ``` To compare the two versions, they are joined to each stock's sessions (`universo_precios.parquet`). The point-in-time one uses `allow_exact_matches=False`: a figure filed on any given day is used from the next session, because we do not know whether it was filed before or after the close (lesson 1.1.1). ```python pr = pd.read_parquet("universo_precios.parquet") pr = (pr[pr["ticker"].isin(fu["ticker"].unique())] .sort_values("fecha")) d_pit = pd.merge_asof( pr, pit.sort_values("disponible"), left_on="fecha", right_on="disponible", by="ticker", allow_exact_matches=False) d_ing = pd.merge_asof( pr, ingenuo[["ticker", "periodo_fin", "bpa", "ben_ttm"]] .sort_values("periodo_fin"), left_on="fecha", right_on="periodo_fin", by="ticker") both = d_pit.merge(d_ing, on=["fecha", "ticker"], suffixes=("_pit", "_ing")) both = both.dropna(subset=["bpa_pit", "bpa_ing"]) adelanta = both["periodo_fin_ing"] > both["periodo_fin_pit"] print(f"Sessions: {len(both)}") print(f"With a quarter not yet published: {adelanta.mean():.1%}") ``` ```output Sessions: 16116 With a quarter not yet published: 43.9% ``` Almost half of the sessions. With quarters of about 91 days and delays of 25 to 62, the naive join runs ahead of reality for 40-60% of each quarter. In an earnings surprise study, or in any factor built on fundamentals (*value*, *quality*), that lead is exactly the period in which the price reacts to the filing: the model would see the news before the market. ```python sesiones = pd.to_datetime(["2022-01-03", "2022-02-22", "2022-02-23", "2022-02-25", "2022-02-28"]) z = both[(both["ticker"] == "ZXNW") & both["fecha"].isin(sesiones)] z.set_index("fecha")[["periodo_fin_pit", "bpa_pit", "periodo_fin_ing", "bpa_ing"]].round( {"bpa_pit": 2, "bpa_ing": 2}) ``` ```output periodo_fin_pit bpa_pit periodo_fin_ing bpa_ing fecha 2022-01-03 2021-09-30 16.19 2021-12-31 14.55 2022-02-22 2021-09-30 16.19 2021-12-31 14.55 2022-02-23 2021-09-30 14.88 2021-12-31 14.55 2022-02-25 2021-09-30 14.88 2021-12-31 14.55 2022-02-28 2021-12-31 14.55 2021-12-31 14.55 ``` *Fig. — ZXNW TTM EPS, 2021–2023: the point-in-time series changes the session after each filing; the naive one on the day the quarter ends, with the final version.* Two step lines of ZXNW's TTM EPS from January 2021 to March 2023. The naive one, dashed, changes at each quarter end and runs one or two months ahead. For Q3 2021 the naive line goes straight to 14.88, the restated figure; the point-in-time line rises to 16.19 in October 2021 and only falls to 14.88 on 23 February 2022, highlighted, with the restatement, and to 14.55 on 28 February with the 10-K. Throughout January and most of February 2022, the naive version already “knows” the full 2021 fiscal year, with the restated third quarter: EPS of 14.55. The point-in-time version keeps the TTM to September in its original version (16.19) until February 23, the session after the restatement, when it drops to 14.88. On February 28, the session after the 10-K (filed on Friday the 25th), the two match. The naive version was almost two months ahead, and in the direction of a negative surprise. ## Universe ratios on a date With the point-in-time table, a snapshot of the ratios of all stocks in one session is a filter. On June 28, 2024, ZXBY has already been acquired and ZXSW has gone bankrupt: they do not appear, just as in that day's market. ```python foto = d_pit[d_pit["fecha"] == "2024-06-28"].set_index("ticker") foto = foto.assign( roe=100 * foto["ben_ttm"] / foto["patrimonio"], margen=100 * foto["ben_ttm"] / foto["ing_ttm"], per=foto["cierre"] / foto["bpa"]) foto[["periodo_fin", "bpa", "roe", "margen", "per"]].round( {"bpa": 2, "roe": 1, "margen": 1, "per": 1}) ``` ```output periodo_fin bpa roe margen per ticker ZXLJ 2024-03-31 16.22 20.3 19.1 1.4 ZXHN 2024-03-31 0.40 13.2 11.6 926.8 ZXZP 2024-03-31 4.79 19.9 19.1 67.5 ZXZE 2024-03-31 3.60 18.7 19.5 155.3 ZXYC 2024-03-31 6.68 8.0 7.7 37.7 ZXZN 2024-03-31 10.72 15.0 14.7 5.2 ZXSI 2024-03-31 12.82 17.1 17.5 10.1 ZXNW 2024-03-31 18.88 15.1 14.4 1.3 ``` ROE and margin are consistent across stocks: margins from 7.7% to 19.5% and ROE from 8.0% to 20.3%. The P/E is not: it ranges from 1.3 to more than 900. This is synthetic data, and the universe's prices and the statements were generated separately, so the level of the P/E means nothing. What is real is its mechanics: it changes every day with the price and jumps at each filing, and that jump can only happen on the date the filing was published. How wrong is the naive ratio in the sessions where it runs ahead? ```python err = 100 * (both["bpa_ing"] / both["bpa_pit"] - 1) print(err[adelanta].abs().describe( percentiles=[0.5, 0.9]).round(2)) ``` ```output count 7071.00 mean 7.63 std 32.83 min 0.04 50% 3.52 90% 9.21 max 543.64 dtype: float64 ``` In the 7,071 sessions where it runs ahead, the median difference is 3.5% and one in ten exceeds 9.2%; the maximums, of hundreds of points, belong to the stock that goes bankrupt, whose TTM earnings pass close to zero. It looks small, but what matters is not the size but the nature: the naive error is not noise, it is information about the next quarter, which is exactly what a model learns to exploit. ## Restatements: versions, not corrections A restatement changes a figure that was already published. In a point-in-time table it is a new row with its own date, and the old figure remains the one that was known until then. The four in this data: ```python v1 = fu[fu["version"] == 1] v2 = fu[fu["version"] == 2] rx = v2.merge(v1, on=["ticker", "periodo_fin"], suffixes=("_v2", "_v1")) rx["dias"] = (rx["fecha_publicacion_v2"] - rx["fecha_publicacion_v1"]).dt.days for c in ["ingresos", "beneficio_neto"]: rx[f"{c}_%"] = 100 * (rx[f"{c}_v2"] / rx[f"{c}_v1"] - 1) rx[["ticker", "periodo_fin", "dias", "ingresos_%", "beneficio_neto_%"]].round({"ingresos_%": 1, "beneficio_neto_%": 1}) ``` ```output ticker periodo_fin dias ingresos_% beneficio_neto_% 0 ZXBY 2019-06-30 197 -4.9 -24.0 1 ZXHN 2019-06-30 191 -4.4 -17.6 2 ZXLJ 2023-03-31 281 -2.2 -18.4 3 ZXNW 2021-09-30 119 -5.5 -29.2 ``` *Fig. — The four restatements in fundamentales_pit.csv: how much net income (upper bar) and revenue (lower bar) of the restated quarter change, and how many days after the original figure.* Paired horizontal bars, one pair per restatement. ZXBY, Q2 2019, 197 days later: net income −24.0%, revenue −4.9%. ZXHN, Q2 2019, 191 days: −17.6% and −4.4%. ZXLJ, Q1 2023, 281 days: −18.4% and −2.2%. ZXNW, Q3 2021, 119 days: −29.2% (highlighted) and −5.5%. All of them are downward and hit earnings (from −17.6% to −29.2%) harder than revenue (from −2.2% to −5.5%): a small cut in revenue with fixed costs takes a large share of the earnings. Between the original and the corrected figure, 119 to 281 days pass. A backtest using the latest version would have used, during those months, earnings the market did not know and, worse, it would have “avoided” the negative surprise of the restatement. ```mermaid stateDiagram-v2 [*] --> Original: 10-Q Original --> Restated: 10-Q/A or 10-K Original --> Original: no changes Restated --> [*] ``` In EDGAR a restatement can arrive in an amendment (forms ending in `/A`, such as `10-Q/A`) or in the comparatives of the next 10-K. In both cases, what makes it point-in-time is the filing date of the document that carries it. ## XBRL and the EDGAR API For years now, the financial statements filed with the SEC have carried their figures tagged in **XBRL**: each figure is a “fact” with a concept from a taxonomy (`us-gaap:NetIncomeLoss`, `us-gaap:Revenues`…), a period, a unit and the document that carries it. EDGAR publishes those facts as JSON at `data.sec.gov`, with no authentication or API key[^2]: | Endpoint | What it returns | |---|---| | `/submissions/CIK##########.json` | Filing history of an entity | | `/api/xbrl/companyconcept/CIK##########/us-gaap/.json` | All facts of one concept for an entity | | `/api/xbrl/companyfacts/CIK##########.json` | All concepts of an entity in a single call | | `/api/xbrl/frames/us-gaap//USD/CY2024Q1.json` | One fact per entity for a calendar period | The CIK is the entity's identifier in EDGAR, ten digits with leading zeros. The structure of `companyfacts` is a tree: ```mermaid flowchart TB A[companyfacts] --> B[cik, entityName] A --> C[facts] C --> D[us-gaap] C --> E[dei] D --> F[NetIncomeLoss] F --> G[label, units] G --> H[USD: facts] ``` Each fact in `units → USD` carries `start` and `end` (the period), `val`, `accn` (the document's accession number), `fy` and `fp` (fiscal year and period **of the document**), `form` and `filed`. The same period appears in several documents: the 10-Q for a quarter and, as a comparative, the one for the same quarter of the following year, or the 10-K three years later. That is why `fy` is not the year of the period. And the `frame` field (`CY2024Q1`) marks, according to the SEC documentation, the fact **filed last** that best fits that calendar period: it is the final version, the one in the naive table. The following example is a **made-up** response with that structure: a fictional company, three quarters of 2024, its 10-K and the 2025 10-Q that repeats the first quarter of 2024 as a comparative, restated. ```python import json respuesta = json.loads("""{ "cik": 1, "entityName": "EXAMPLE CORP (fictional)", "facts": {"us-gaap": {"NetIncomeLoss": { "label": "Net Income (Loss)", "units": {"USD": [ {"start": "2024-01-01", "end": "2024-03-31", "val": 412000000, "accn": "A-24-1", "fy": 2024, "fp": "Q1", "form": "10-Q", "filed": "2024-05-06"}, {"start": "2024-04-01", "end": "2024-06-30", "val": 455000000, "accn": "A-24-2", "fy": 2024, "fp": "Q2", "form": "10-Q", "filed": "2024-08-05"}, {"start": "2024-07-01", "end": "2024-09-30", "val": 430000000, "accn": "A-24-3", "fy": 2024, "fp": "Q3", "form": "10-Q", "filed": "2024-11-04", "frame": "CY2024Q3"}, {"start": "2024-01-01", "end": "2024-12-31", "val": 1650000000, "accn": "A-25-1", "fy": 2024, "fp": "FY", "form": "10-K", "filed": "2025-02-24", "frame": "CY2024"}, {"start": "2024-01-01", "end": "2024-03-31", "val": 380000000, "accn": "A-25-2", "fy": 2025, "fp": "Q1", "form": "10-Q", "filed": "2025-05-05", "frame": "CY2024Q1"} ]}}}}}""") hechos = respuesta["facts"]["us-gaap"]["NetIncomeLoss"] df = pd.DataFrame(hechos["units"]["USD"]) for c in ["start", "end", "filed"]: df[c] = pd.to_datetime(df[c]) df["dias"] = (df["end"] - df["start"]).dt.days + 1 df["val_M"] = df["val"] / 1e6 df[["start", "end", "dias", "form", "fy", "fp", "filed", "frame", "val_M"]] ``` ```output start end dias form fy fp filed frame val_M 0 2024-01-01 2024-03-31 91 10-Q 2024 Q1 2024-05-06 NaN 412.0 1 2024-04-01 2024-06-30 91 10-Q 2024 Q2 2024-08-05 NaN 455.0 2 2024-07-01 2024-09-30 92 10-Q 2024 Q3 2024-11-04 CY2024Q3 430.0 3 2024-01-01 2024-12-31 366 10-K 2024 FY 2025-02-24 CY2024 1650.0 4 2024-01-01 2024-03-31 91 10-Q 2025 Q1 2025-05-05 CY2024Q1 380.0 ``` Five facts for four periods. The first quarter of 2024 appears twice: 412 million in the May 2024 10-Q and 380 million as a comparative in the May 2025 10-Q, with `fy` 2025 even though the period is in 2024. The `frame` `CY2024Q1` is on the second one: the last one filed. The duration (`dias`) separates quarters from full fiscal years. Turning it into a point-in-time series is the same as with the CSV: each fact is a version of the period (`start`, `end`), known from `filed`. ```python trim = df[df["dias"].between(80, 100)] trim = trim.sort_values(["end", "filed"]) trim["version"] = trim.groupby("end").cumcount() + 1 def conocido_en(fecha): vis = trim[trim["filed"] <= fecha] return vis.groupby("end")["val_M"].last() print("What was known on 2024-12-31:") print(conocido_en("2024-12-31").to_string()) print("What frame says (the latest version):") print(trim.dropna(subset=["frame"])[["end", "val_M"]].to_string()) ``` ```output What was known on 2024-12-31: end 2024-03-31 412.0 2024-06-30 455.0 2024-09-30 430.0 What frame says (the latest version): end val_M 4 2024-03-31 380.0 2 2024-09-30 430.0 ``` On December 31, 2024, first-quarter earnings were 412 million for the whole market; the 380 version did not exist until May 2025. A series built with `frame` would have used 380 from the start. The fourth quarter would be obtained as with the CSV: the annual fact from the 10-K minus the three quarters known on February 24, 2025. The real request is one line with `urllib` or `requests`. The SEC asks you to declare in the `User-Agent` header who is making the request (a name and a contact email) and limits access to 10 requests per second[^3]: ```python skip import json import time import urllib.request CABECERAS = {"User-Agent": "Your Name your.email@example.com"} def companyfacts(cik: int) -> dict: url = ("https://data.sec.gov/api/xbrl/companyfacts/" f"CIK{cik:010d}.json") req = urllib.request.Request(url, headers=CABECERAS) with urllib.request.urlopen(req, timeout=30) as r: datos = json.load(r) time.sleep(0.15) # well below 10 requests/s return datos datos = companyfacts(320193) # ten-digit CIK: 0000320193 usd = datos["facts"]["us-gaap"]["NetIncomeLoss"]["units"]["USD"] ``` The response is a JSON with the structure above, often several megabytes, with every concept the company has tagged. The practical problems are not about downloading but about content: companies that switch concepts between years (`Revenues` versus `RevenueFromContractWithCustomerExcludingAssessedTax`), fiscal years that do not match the calendar year, different units (`USD`, `USD/shares`, `shares`) and facts duplicated across documents. Vendor solutions (lesson 1.3.4) sell precisely that cleanup, with or without its knowledge date; before you buy, ask which one. ## What this changes in a backtest - **Two dates per figure, always.** A fundamentals table without `fecha_publicacion` is no good for simulating decisions. If a vendor only gives the period date, the prudent approximation is to delay each figure by the maximum legal deadline of its form, knowing that it is an approximation. - **One row per filing, not per period.** The point-in-time table grows with each version: the join with prices is a `merge_asof` by knowledge date, with `allow_exact_matches=False` if you do not know the time. - **Survivorship here too.** Stocks that stop filing (bankruptcy, acquisition) have to stay in the table until their last filing; lesson 1.4.4 explains why. - **The level of a ratio depends on how it is built.** TTM, annualized last quarter or fiscal year give different P/E ratios. Pick one, document it and do not change it between research and execution. ## Exercises 1. Separate the two errors of the naive join. Build a version that uses the correct filing date but the **latest version** of each quarter (no point-in-time restatements) and count in how many sessions its EPS differs from the point-in-time one. What part of the total error is due to the date and what part to the versions? **Solution.** ```python fecha_ok = ingenuo.merge( tr.groupby(["ticker", "periodo_fin"])["fecha_publicacion"] .min().rename("primera_pub").reset_index(), on=["ticker", "periodo_fin"]) # Q4 becomes known with the 10-K: its first filing d_ver = pd.merge_asof( pr, fecha_ok[["ticker", "primera_pub", "bpa"]] .sort_values("primera_pub"), left_on="fecha", right_on="primera_pub", by="ticker", allow_exact_matches=False) cmp = d_pit.merge(d_ver, on=["fecha", "ticker"], suffixes=("_pit", "_ver")) cmp = cmp.dropna(subset=["bpa_pit", "bpa_ver"]) distinto = (cmp["bpa_ver"] - cmp["bpa_pit"]).abs() > 1e-9 print(f"Sessions with a different EPS: {distinto.mean():.1%}") print(cmp.loc[distinto, "ticker"].value_counts().to_string()) ``` ```output Sessions with a different EPS: 3.3% ticker ZXLJ 192 ZXBY 135 ZXHN 131 ZXNW 81 ``` With the correct date, the lead from dates disappears and only the one from versions remains: 3.3% of the sessions, all of them from the four stocks with a restatement (from 81 to 192 sessions each), in the months between the original figure and the corrected one. Measured in sessions, almost all of the naive join's error comes from dates; the error from versions is small, but it falls right in the periods of the negative surprises. 2. ZXSW goes bankrupt in 2023. Using `d_pit`, what is the last session with fundamental data, and what TTM ROE and margin did the market know that day? Compare it with its first point-in-time row. **Solution.** ```python s = d_pit[d_pit["ticker"] == "ZXSW"].dropna(subset=["bpa"]) s = s.assign(roe=100 * s["ben_ttm"] / s["patrimonio"], margen=100 * s["ben_ttm"] / s["ing_ttm"]) s.iloc[[0, -1]][["fecha", "periodo_fin", "ing_ttm", "roe", "margen"]].round( {"ing_ttm": 1, "roe": 1, "margen": 1}) ``` ```output fecha periodo_fin ing_ttm roe margen 10458 2019-02-28 2018-12-31 3562.6 11.4 10.2 20944 2023-04-28 2022-09-30 2893.7 -94.0 -8.4 ``` ZXSW's last session with data is April 28, 2023, and the latest thing known was the TTM to September 2022: revenue of 2,893.7 million (3,562.6 in its first row, in 2019), a margin of −8.4% and an ROE of −94.0%, with equity almost wiped out. The deterioration was in the data. A backtest without the stock (because it “no longer exists” at the vendor) would never see that trajectory or the final loss; one with the stock but with the latest version of its statements by period date would see the deterioration weeks before the market. 3. A vendor offers you “point-in-time” fundamentals with a `fecha_disponible` column equal to the period date plus 45 days for every form. What problems does it have with 10-Ks and with restatements? **Solution.** With 10-Ks it falls short: here they are filed between 45 and 62 days after the year end (and the SEC gives 60, 75 or 90 depending on the filer), so the rule brings many fourth quarters forward by a few days, right in the annual earnings season. With 10-Qs it is too conservative in some cases and correct in others, which adds noise at the moment of the reaction. And a fixed rule cannot represent restatements: the corrected version arrives months later and needs its own date. Real point-in-time data keeps the filing date of each document and one row per version. [^1]: Deadlines from the general instructions of the SEC forms: 10-Q, 40 days after the quarter for *large accelerated* and *accelerated filers* and 45 for the rest, with no fourth-quarter 10-Q (); 10-K, 60, 75 and 90 days after the fiscal year depending on the category (). [^2]: SEC, “EDGAR Application Programming Interfaces”: the `data.sec.gov` APIs require no authentication or keys; the CIK has ten digits with leading zeros; the JSON files are updated throughout the day, as filings are disseminated. [^3]: SEC, “Accessing EDGAR Data”: current limit of 10 requests per second and a `User-Agent` header with the name of whoever is accessing and a contact email. ## Self-assessment 1. A 10-Q for the quarter ending June 30 is filed on August 6, after the close. From when can a daily backtest use its figures? - A. From June 30, the period date - B. From the open on August 6 - C. From July 1 - D. From the August 7 session, the one after the filing *Answer D: From the August 7 session, the one after the filing.* The figure does not exist for the market until it is filed. If you do not know the time of the filing, the prudent choice is to use it from the next session: that is what `merge_asof` does with `allow_exact_matches=False`. Using it from June 30 brings information forward by 38 days. 2. A fiscal year's 10-K reports revenue of 14,055.8 and the year's three 10-Qs add up to 10,343.4. What was the fourth quarter's revenue? - A. 3,712.4: the full year minus the first three quarters - B. 14,055.8: the 10-K is the fourth quarter - C. 3,513.9: a quarter of the year - D. It cannot be known without a fourth-quarter 10-Q *Answer A: 3,712.4: the full year minus the first three quarters.* There is no fourth-quarter 10-Q: the 10-K reports the full fiscal year for flow items. Quarter 4 comes from subtracting the three quarters, using the versions known on the 10-K's filing date. 3. A company restates a quarter's earnings downward seven months later. Your database keeps only the latest version, indexed by period date. What error does it introduce? - A. None: the latest version is the correct one - B. Only a rounding error - C. Double look-ahead: it uses the figure before it was published and, on top of that, a version nobody knew until months later - D. It makes earnings look higher than they were *Answer C: Double look-ahead: it uses the figure before it was published and, on top of that, a version nobody knew until months later.* For seven months the market traded on the original figure. The latest version is the right one for an accounting study, not for simulating decisions: the backtest would “know” that the earnings were going to be restated. 4. In EDGAR's `companyfacts` response, which field says when each figure became known? - A. `end`, the end of the period - B. `filed`, the filing date of the document that contains it - C. `fy`, the fiscal year - D. `frame`, the calendar period *Answer B: `filed`, the filing date of the document that contains it.* `start` and `end` delimit the period; `fy` and `fp` belong to the document (a 10-K also carries prior years as comparatives); `frame` marks the fact filed last for a calendar period. The knowledge date is `filed`. 5. In this lesson's table, in what share of sessions does the naive join use a quarter that had not been published yet? - A. None - B. About 44% - C. About 5% - D. All of them *Answer B: About 44%.* With delays of 25 to 62 days per quarter of about 91, the join by period date runs ahead of reality almost half the time: in 43.9% of the sessions of the ten stocks. ## Key points - The income statement measures flows over a period; the balance sheet, stocks on a date. The 10-K carries the full fiscal year: the fourth quarter is obtained by subtraction. - Every figure has two dates: the period date and the filing date. A backtest can only use the second, and preferably from the next session. - The naive join by period date brings each quarter forward by 25 to 62 days in this data; in 43.9% of sessions it uses one that did not exist yet. - A restatement is a new version with its own date. Keeping only the latest version brings in information from months later. - EDGAR publishes financial statements in XBRL with no API key: `companyfacts` returns each fact with `start`, `end`, `val`, `form` and `filed`; the knowledge date is `filed`. ## Resources ### Research - [Alpha Factors and Alpha Trading in Python](https://pythonparatrading.com/en/alpha-factors-and-alpha-trading-in-python/) · Research-0007 · 5 min ### Documentation - [SEC: EDGAR Application Programming Interfaces (submissions, companyfacts, frames)](https://www.sec.gov/search-filings/edgar-application-programming-interfaces) · sec.gov - [SEC: Accessing EDGAR Data (declared User-Agent, 10 requests per second)](https://www.sec.gov/os/accessing-edgar-data) · sec.gov - [SEC: Form 10-Q, general instructions (40- and 45-day deadlines)](https://www.sec.gov/files/form10-q.pdf) · sec.gov - [SEC: Form 10-K, general instructions (60-, 75- and 90-day deadlines)](https://www.sec.gov/files/form10-k.pdf) · sec.gov - [pandas: merge_asof](https://pandas.pydata.org/docs/reference/api/pandas.merge_asof.html) · pandas.pydata.org ### Data - [Level 1 data: point-in-time financial statements of 10 stocks (CSV)](https://pythonparatrading.com/curso/datos/nivel-1/fundamentales_pit.csv) · Download · fundamentales_pit.csv Previous lesson: [1.4.8 · Reproducible pipeline: download, validation, incremental updates and quality tests](https://pythonparatrading.com/en/python-trading-course/level-1/reproducible-market-data-pipeline/) Next lesson: [1.5.2 · Macro data, economic calendars, estimates and transcripts](https://pythonparatrading.com/en/python-trading-course/level-1/macro-data-economic-calendars-and-estimates/)