Join stock prices to released fundamentals with an as-of merge
Join daily stock prices to fundamentals by turning each verified release into an effective decision timestamp, deduplicating its revisions and selecting the latest eligible version for each price row. The deliverable is an actual daily panel with the selected value and source event, not a general instruction to avoid future information. Test the dates on both sides of each release boundary before extending the join.
This guide executes a standard-library backward lookup on synthetic observations. Publication times are external inputs, not recovered automatically from report-period labels. CoTrading’s corresponding merge workflow remains planned or unverified, and a current fundamentals response does not establish that a complete historical availability database exists.
Define the daily decision cutoff and release key
The fixture uses one synthetic market with a 15:00 UTC+08:00 daily research cutoff and six supplied sessions: 19, 20, 21, 22, 23 and 26 October 2026. It studies one specified EPS field and one fixed fiscal-period definition per stock. Price and release timestamps are timezone aware. This is a declared research policy, not a claim about every market’s close or execution timing.
A release must occur strictly before a cutoff to enter that day’s panel. A release exactly at 15:00 is deferred to the next supplied session. Choose that strictness before execution and record it with the run. A different policy can be valid for a different task, but it needs different boundary tests.
The event key includes stock identity and a source event identifier. Real systems should also preserve metric, original reporting period, unit, accounting basis and revision relationship. If several fiscal periods are present, group and select them under an explicit period policy rather than allowing the latest timestamp from any period to replace the desired observation.
Prepare the release and price inputs
AAA has no eligible EPS on the first two dates. Its original EPS 1.0 is published at 16:00 on the 20th. A revision to 1.1 arrives at 14:00 on the 22nd and is duplicated identically in the source fixture. Another revision to 1.2 is published exactly at the 23rd’s cutoff. A value 9 arrives after the final 26th cutoff and cannot enter this panel.
BBB has EPS 2 published before the first cutoff. CCC has a value 3 but no publication timestamp. Those extra stocks verify security partitioning and the missing-time branch. A single global lookup could otherwise attach BBB’s available value to AAA or grant CCC an invented publication time.
The SEC API documentation describes updated filing and financial-fact data. It is useful source context but does not prove the availability timestamps required by this example. Preserve actual release or dissemination evidence from the appropriate source; do not replace it with fiscal period end or a current retrieval timestamp.
Convert publication time into an effective session
For every valid event, find the earliest supplied daily cutoff strictly after publication. The 20th’s after-close release activates at the 21st’s cutoff. The 22nd’s pre-cutoff revision activates on the 22nd. The exact-close event on Friday the 23rd activates on the next supplied session, Monday the 26th.
published = datetime.fromisoformat(row["published"])
eligible = [cutoff for cutoff in cutoffs if published < cutoff]
if not eligible:
pending.append(row["id"])
else:
effective_at = min(eligible)Python’s datetime reference supports explicit aware-time comparison. The fixture does not generate exchange holidays or US daylight-saving rules. Its supplied session sequence is part of the input contract and must cover the intended horizon. Events beyond that horizon are pending, not missing-time errors.
Unknown publication time follows a separate exclusion path. Do not infer the hour from the report title, use midnight by default or treat an old fiscal date as sufficient evidence. The CCC row remains unavailable for this historical join until its release time is established.
Deduplicate and execute the backward lookup
Deduplication uses (symbol, event_id). Identical rows within that security and identifier are reused once; conflicting content under the same pair raises conflicting_event_id. Different securities may legitimately share an event identifier and remain separate. Distinct events with ambiguous identical effective and publication times also stop rather than allowing source row order to choose a revision. Valid events are sorted by effective time and publication time inside each stock.
values = prepared.get(price["symbol"], [])
position = bisect_right([event[0] for event in values], decision)-1
selected = values[position] if position >= 0 else NoneThe bisect reference documents right-side insertion and a latest-less-than-or-equal lookup pattern. Here it searches the effective cutoffs already constructed under the strict publication rule. Inclusive matching at the effective time does not undo the earlier strict publication check.
Download the complete example. Use Python 3.12+, the standard library and synthetic data; run python R535.py. The source is docs/growth/research/round-5/examples/R535.py.
Inspect the executed daily panel
| AAA price date | Close | Joined EPS | Selected event |
|---|---|---|---|
| 19 October | 10 | unavailable | none |
| 20 October | 11 | unavailable | none |
| 21 October | 12 | 1.0 | original |
| 22 October | 13 | 1.1 | revision |
| 23 October | 14 | 1.1 | revision |
| 26 October | 15 | 1.2 | exact-close release from 23rd |
Actual output also gives BBB its own EPS 2 and leaves CCC empty. Each selected row contains event identifier, publication timestamp and effective timestamp. Assertions verify the full AAA sequence, the missing-time exclusion and the future event pending beyond the horizon. Reversing the release input order produces the same panel.
The duplicate revision does not create two price rows or change the chosen value. An injected conflicting value for the same security and identifier is rejected. A separate test gives AAA and BBB the same filing-1 identifier: each keeps its own EPS, including when source order reverses. These tests exercise data-join behavior rather than merely asserting that no future timestamp appears in a final summary.
Diagnose the different boundary failures
Joining AAA’s fiscal period directly to every price date would place EPS 1.0 on the 20th before its 16:00 publication. Joining only by publication calendar date would make the same mistake. The effective-session transformation prevents it while still allowing the 14:00 revision to enter the 22nd.
The exact-close release is a different edge. Under this conservative policy it stays out of the 23rd and enters the 26th; changing the inequality changes that result. A timestamp exactly on a boundary needs an explicit decision, not a rounding convention hidden inside a date parser.
Missing time and conflicting revision identity require different remedies. Obtain timestamp evidence for CCC; resolve the source conflict for a reused event identifier. Neither can be repaired by carrying the last value backward or selecting whichever number makes the panel look complete.
Hand off the panel with its join evidence
Keep the price rows, original release collection, deduplicated events, excluded and pending lists, effective-session policy and daily selection output. Reconcile each price row to at most one selected event in the same stock and field-period partition. The panel should preserve missing values when no eligible source exists.
For a corrected release timestamp, recompute its effective session and all daily rows in the affected interval until the next applicable revision. For a corrected value, preserve the old event version and rebuild its dependent interval. A change can affect several daily rows without changing the price table itself.
This method creates a concrete as-of merge and boundary tests. It does not establish when a trade could execute, remove all research look-ahead risks or manufacture historical forecasts from current data. A strategy requiring a next-open decision needs its own cutoff and execution design. The joined panel remains an inspectable input whose availability assumptions and unresolved rows are visible.
Verify the selected event, not only the joined number
Two revisions can contain the same EPS, so equality of final values alone does not prove correct historical selection. Compare event identifiers and effective timestamps on the boundary dates as well. The panel records those fields so a reviewer can distinguish a valid original observation from an identical later restatement that was not yet available.
Keep the exclusion list and the beyond-horizon list separate when preparing another run. Extending the calendar can legitimately activate a pending future event; it cannot resolve a missing publication timestamp. Treating both lists as generic retry items would conceal which evidence or calendar input actually needs to change.
For a real integration, retain the accepted availability clock used by the research system. If dissemination and ingestion are different, a stated policy may need the later reliable time. That policy must be applied before constructing effective sessions. A final narrative saying that only known information was used cannot repair a join that selected by period end or used an unverified publication hour. The reproducible daily event mapping is the acceptance evidence.
FAQ
Does the report’s fiscal end determine when it can join?
No. Use reliable publication or availability evidence and the declared decision cutoff.
Why is an exact-close release delayed here?
The fixture explicitly requires publication strictly before the cutoff. Other policies require their own tests.