The timestamps that make financial data usable

A value describing August may not be published until September. A correction in October may still carry an August date. Put those records into a table with one column called date, and the researcher has to discover which meaning survived.

I spent years in investment research and portfolio management before software became my main work. A backtest has to use each number as it was known on the date of the decision, not as it is known today. Using a later revision is look-ahead bias: the backtest acts on information no one had at the time.

Take a fictional activity index. The publisher releases August 2026’s value as 102.4 on September 3 at 12:30 UTC, then revises it to 102.1 on September 10. The values and times are invented.

A backtest of a decision made on September 4 needs 102.4. A query made today for the August value returns 102.1, and filtering for August doesn’t prevent the mistake, because both versions describe August. The query has to ask a different question: of the versions published on or before September 4, which is the latest revision? Every answer depends on an as-of date, and loading a later revision must not change the answer for an earlier one.

This is how vintage data works. By default, the St. Louis Fed’s FRED API returns what is known about the past today. Its documentation explains that ALFRED users can change the real-time period to retrieve what was known as of a past period.

Company filings work the same way. In my investigation of Apple’s, Microsoft’s, and Walmart’s filings, the SEC’s company facts keep each filing’s value for a period beside every other filing’s, each with the date it was filed. Apple’s 10-K for fiscal 2009, filed October 27, 2009, reported net income of $5,704 million; its amended 10-K, filed January 25, 2010, reported $8,235 million for the same year. Across the three companies, 1,487 of the 24,825 figures reported by more than one filing changed, and no field marks a change. The frame field, which tags one value per calendar period, sits on the latest filing’s value, so a backtest that selects values by frame can use figures no one had at the time. I’m building edgar-asof, a public library that joins each value to the acceptance time of the filing that reported it, so a query can ask what was known at a past time. It isn’t released yet.

In a research team, the problem arrives under other names: a backtest that gives a different answer when rerun, or daily copies of a vendor’s API kept because the API can’t say what it returned last month. Each is a question about which version was known when, and the answer depends on which timestamps the dataset kept.

A backtest needs the record as it stood

In my backtests I worked this out by intuition: every query asked what was known as of a date, keyed on when each number was published. I first read Martin Fowler’s Bitemporal History without connecting it to that work; rereading it while researching this article gave me names for the two time axes.

Fowler calls them actual time, when something was true in the world, and record time, when the system learned of it; database literature also calls them valid time and transaction time. A model that keeps both is bitemporal. Here the observation period, August, is the actual time. The publication date is the record time a researcher cares about.

The revised 102.1 may be the better estimate of what happened in August. That makes it the right figure for some work, such as measuring how far first releases miss the final value. A backtest needs the record as it stood: 102.4 until September 10, then 102.1.

A publication date stands in for when you could act

My own backtests used fundamentals and worked from nightly figures, because that is how I trade, so publication dates were enough and delivery time never came up. Approaching the same data as a provider brought delivery lag into view. For intraday strategies, the lag matters for a first release as much as for a revision.

Close to a release, a backtest keyed on publication dates can get a number before anyone could have used it. A date with no time doesn’t say whether a release came before the market opened or after it closed. A researcher who receives the data through a vendor could act only once the vendor delivered it.

A provider that wants to answer the as-of question for its customers therefore records more than the publication time. Record time splits three ways, because the source publishes a version, the provider receives it, and the API makes it retrievable at three different moments:

At 12:30:30 on September 3, the publisher has released the number and the provider has received it, but the API cannot yet return it. A customer reconstructing what they could have retrieved at that instant should get no August value. At 12:32, the answer is 102.4. On September 10 at 12:30:20, it is still 102.4: the revision exists at the source but will take another twenty seconds to reach the API.

In the provider’s records, period_start and period_end describe August, from August 1 up to September 1, with the end excluded. published_at records the source release, received_at the provider’s acquisition, and available_at the time an entitled customer could first retrieve the version through the designated API. Each revision carries its own time fields.

available_at requires evidence from the serving system. Writing a row to the ingestion database may leave an index refresh or cache invalidation unfinished. A customer using a bulk export may receive the data on another schedule altogether, so the delivery channel is part of the field’s meaning. It still won’t say when a particular customer downloaded the value. They may have polled later or had an outage, and reconstructing the inputs to their trading system could require their own acquisition log.

Historical records may offer less precision than this example. A source might supply only a publication date, or an archive might preserve revisions without recording delivery times. Turning a date into midnight invents precision. Keep the date and its precision explicit; leave availability unknown where there is no record of it.

Loading old observations creates a similar trap. A provider that imports ten years of data today has evidence of acquiring it today. Source vintages can still support a backtest keyed on publication dates, but old observation dates do not establish that the provider’s customers could retrieve those records ten years ago.

A provider’s mistake creates a second history

Take an earlier month from the same series. The publisher releases February’s value as 101.3 on March 3. The provider divides it by one hundred in error, serves 1.013, and fixes the mistake on March 5. A backtest built on the source’s data wants 101.3 as of March 4, because that is what the publisher released. An audit of what a customer’s live system received on March 4 needs 1.013. The API has to keep both answers.

Silently replacing the old row loses the second. The original record and the correction need separate versions. The correction keeps the source’s publication and receipt times, because the publisher released 101.3 only once; only its availability time is new. The publisher’s September 10 revision to August is a different kind of event: it changes the estimate itself. Labelling each version as a source revision or a provider correction lets the customer keep both histories. A current-value view can make the latest version easy to query while the underlying history preserves those differences.

Arrival order cannot safely decide which version is latest. A queued initial release could arrive after its revision. Source revision order, plus a rule for ties or missing ordering information, has to determine which version supersedes which. In this example, each version of an observation carries a revision number in source order, and a provider correction takes the next number. The highest number as of the query date wins. Otherwise replaying a queue can change the answer.

Every input to a join has to be as of the decision

The mistake can reappear in a join even when the database preserves every version. Suppose a research table holds one row per trading decision, and the analysis joins each decision to the activity index by month:

-- Look-ahead: a decision on August 15 receives the August value,
-- which wasn't published until September 3.
SELECT d.decided_at, d.position, i.value
FROM decisions AS d
JOIN index_current AS i
  ON i.period_start = date_trunc('month', d.decided_at);

The join is wrong twice. A decision on August 15 receives August’s value, which wasn’t published until September 3. And because index_current holds only the latest version, rerunning the analysis after September 10 swaps in 102.1 without any sign of the change.

Each decision should instead receive the most recent observation published by the moment it was made:

SELECT d.decided_at, d.position, v.period_start, v.value, v.revision_number
FROM decisions AS d
CROSS JOIN LATERAL (
  SELECT period_start, value, revision_number
  FROM index_versions
  WHERE series_id = 'activity-index'
    AND published_at <= d.decided_at
  ORDER BY period_start DESC, revision_number DESC
  LIMIT 1
) AS v;

In this invented series, each month is published around the third of the following month, so a decision on August 15 receives July’s value. A decision on September 4 receives 102.4, and one on September 11 receives 102.1. Later revisions have later publication times, so rerunning the query leaves all three answers unchanged.

The provider’s correction of February takes the next revision number, 2, and keeps the source’s March 3 publication time, so a decision on March 4 receives 101.3.

For an intraday strategy, key the query on the provider’s available_at instead. A decision at 12:30:30 on September 3 then receives July’s value, not a first release its system couldn’t yet have retrieved. A decision on March 4 receives 1.013, the value the customer’s system received that day, and decisions after the correction became available on March 5 receive 101.3.

Ordering by period before revision matters. An as-of join, such as pandas merge_asof, matches each row to the last row whose key is at or before it. Keyed on publication time, that is the most recently published version, which can be a revision of an older month: a June revision published on October 5 would displace September’s value, published two days earlier. Keyed on the observation date, it repeats the look-ahead join above. An as-of join gives the right answer once its right-hand side has been reduced to the latest known value at each publication time.

Write the expected answers down before implementing the query. For the example: no August value before publication; 102.4 from publication; still 102.4 immediately before the revision; then 102.1. After loading the revision, earlier as-of dates must keep their earlier answers, and replaying the same events must preserve them too. A provider correction needs a case for each history the query can return.

Those checks catch a service that successfully returns August data while giving the researcher information from September 10. The HTTP response alone offers no sign of the mistake.

Backtests that key every figure on when it became available are part of the research tools I build for investment teams. If yours give different answers when rerun, tell me about them.