How to Read the US Equity Trade Tape

The US equity trade tape is the record of every print, every reported trade in every exchange-listed stock and ETF, with a timestamp, a price, a size, the venue that reported it and a set of condition codes that say what kind of trade it was. Read raw, it is the closest thing to ground truth a backtest can stand on. Read naively, it double-counts closing auctions, treats cancelled prints as fills and mistakes a 1970 placeholder for a timestamp. This post walks the columns of our stocks_trades table in the order a simulator meets them; every figure in it is measured on the tape itself. It pairs with our guides to how block trades print on the tape and fractional shares on the tape.

What does the trade tape contain?

The table holds the consolidated tape from both US securities information processors, CTA for NYSE-group listings and UTP for Nasdaq listings, for exchange-listed stocks and ETFs. Coverage begins on 2003-09-10 and runs to the prior session, one to two days behind the calendar at the front edge. The count of sessions per year is the first thing to check, and it lines up with the exchange calendar every year from 2004 onward.

SELECT
    toYear(date)                 AS year,
    toString(min(date))          AS first_session,
    uniqExact(date)              AS sessions,
    uniqExact(ticker)            AS symbols
FROM global_markets.stocks_daily_aggs
WHERE date >= '2003-09-10'
GROUP BY year
ORDER BY year

Two things the tape does not contain, and both produce an honest zero-row result. OTC and pink-sheet prints are absent, so a symbol that keeps trading over the counter after a delisting ends on its last exchange session. A symbol that never traded on an exchange on a given day has no rows, which is the correct answer. The daily bar table separates the two cases: a ticker with a stocks_daily_aggs bar carrying volume and no prints on the tape is a gap worth reporting; a ticker with no bar either did not trade on an exchange that day. The symbol count in the table above rises from 8657 in the first partial year to 14195 in 2026 as ETFs and share classes multiplied; that count is also the universe a survivorship-free study starts from, a topic we cover in survivorship bias in stock data.

Which timestamp is the execution time?

Each print carries three timestamps. sip_timestamp is when the processor handled the print, participant_timestamp is the reporting exchange's own clock at execution, and trf_timestamp is when a FINRA trade-reporting facility received an off-exchange print. Only the first one exists for the whole history. The other two are populated from a single day onward and hold the epoch placeholder, 1970-01-01, on every row before it.

SELECT
    toYear(sip_timestamp)                                                                   AS year,
    count()                                                                                 AS prints,
    round(100 * countIf(toUnixTimestamp64Nano(participant_timestamp) = 0) / count(), 1)     AS placeholder_pct,
    round(100 * countIf(toUnixTimestamp64Nano(sip_timestamp) % 1000000 = 0) / count(), 1)   AS sip_ms_or_coarser_pct
FROM global_markets.stocks_trades
WHERE ticker IN ('IBM', 'MSFT')
  AND toYYYYMMDD(sip_timestamp) IN [20030910,20040315,20050315,20060315,20070315,20080314,20090316,20100315,20110315,20120315,20130315,20140314,20150316,20160315,20170315,20180315,20190315,20200316,20210315,20220315,20230315,20240315,20250314,20260316]
GROUP BY year
ORDER BY year

The chart is a step, not a slope. In 2003 the placeholder share is 100%; in 2026 it is 0%. The second column shows the SIP clock itself getting finer over the same span: the share of prints stamped to the millisecond or coarser falls from 100% in the first year to 0% in the last, the move from millisecond to nanosecond feeds. Narrowing to the weeks around the change pins the day.

SELECT
    toString(toDate(sip_timestamp))                                                       AS day,
    count()                                                                               AS prints,
    round(100 * countIf(toUnixTimestamp64Nano(participant_timestamp) = 0) / count(), 1)   AS placeholder_pct
FROM global_markets.stocks_trades
WHERE ticker IN ('IBM', 'MSFT', 'AAPL', 'GE', 'XOM')
  AND sip_timestamp >= '2015-07-20' AND sip_timestamp < '2015-08-08'
GROUP BY day
ORDER BY day

SELECT
    toString(min(toDate(sip_timestamp)))    AS first_populated_session,
    count()                                 AS populated_prints
FROM global_markets.stocks_trades
WHERE ticker IN ('IBM', 'MSFT', 'AAPL', 'GE', 'XOM')
  AND sip_timestamp >= '2015-07-20' AND sip_timestamp < '2015-08-08'
  AND toUnixTimestamp64Nano(participant_timestamp) != 0

The exchange stamp appears on 2015-07-27, a Monday, and from that session the placeholder share is 0%. The rule for an execution clock is one expression: the participant stamp where it is populated, the SIP stamp where it is not. The SIP stamp is dissemination time; on exchange prints it trails the exchange's own clock by a fraction of a millisecond, and the gap widens only on late off-exchange reports and on the cancel and correction records below.

How do you find the opening and closing auction prints?

The auction prints are the largest single executions of the day and the anchor for any close-to-close study. The condition codes that mark them are one numeric set across the whole history, and the stocks_condition_codes reference table carries the CTA and UTP letter for each. What changes by year is whether the processor sent the flag at all. On NYSE-listed names the closing print carries code 8 from mid-2009 and the opening print code 17 from later that year; before that the NYSE auction prints are unflagged, identifiable only as the first print at or after 9:30 and the largest print after 4:00 p.m. on exchange 10. Nasdaq-listed names carry 8 and 17 alongside 9, the cross-trade code, from the start of the flagged era.

SELECT
    toYear(sip_timestamp)                                                                  AS year,
    toString(toDateTime64(min(sip_timestamp), 3, 'America/New_York'))                      AS closing_print_et,
    round((toUnixTimestamp64Milli(min(sip_timestamp))
           - toUnixTimestamp64Milli(toDateTime64(concat(toString(toDate(min(sip_timestamp))), ' 16:00:00'), 3, 'America/New_York'))) / 1000, 1) AS seconds_after_1600,
    any(size)                                                                              AS shares
FROM global_markets.stocks_trades
WHERE ticker = 'IBM' AND exchange = 10
  AND has(conditions, 8) AND correction = 0
  AND toYYYYMMDD(sip_timestamp) IN [20100315,20110315,20120315,20130315,20140314,20150316,20160315,20170315,20180315,20190315,20200316,20210315,20220315,20230315,20240315,20250314,20260316]
GROUP BY year
ORDER BY year

Two habits follow from the chart. First, test the code with a membership function, not equality: the closing print's condition array reads as 8 alone in 2010 and gains a trade-through-exempt flag, 41, in later years, so an equality test silently drops the modern prints. Second, expect the print after the bell. In 2010 IBM's closing auction printed 17.2 seconds after 4:00 p.m.; in 2012 it printed 150.4 seconds after; by 2026 the gap is 2.6 seconds. A closing print stamped at 4:02 on an older session is the auction itself, not a reporting delay, and from the day the exchange stamp appears it agrees with the SIP stamp to within a few milliseconds on these prints.

Codes 15 and 16 deserve their own warning. They are the market center's official close and official open messages: they repeat the auction print's price and size, they are not executions, and since 2020 the NYSE re-sends the official close several times a session. Exclude both from executions and from volume.

What do the correction codes mean?

The correction column is the processor's correction indicator, and its values arrive in pairs. A print that is later corrected carries 1, and its correction record, the row with the corrected fields, carries 12. A print that is later cancelled carries 8, and its cancel record carries 10. Codes 7 and 11 do the same for prints marked erroneous and stopped appearing after 2014. A session's counts pair up exactly.

SELECT
    correction,
    multiIf(correction = 0,  'regular',
            correction = 1,  'original, later corrected',
            correction = 12, 'correction record',
            correction = 8,  'original, later cancelled',
            correction = 10, 'cancel record',
            correction = 7,  'original, later errored',
            correction = 11, 'error record', 'other')                       AS meaning,
    count()                                                                  AS prints
FROM global_markets.stocks_trades
WHERE ticker IN ('AAPL','MSFT','AMZN','NVDA','TSLA','META','GOOGL','BAC','C','AMD','ADBE','BIIB','ARCC','AA','AAL')
  AND toYYYYMMDD(sip_timestamp) = 20230315
GROUP BY correction
ORDER BY correction

The link between a record and the print it amends is the sequence number. A cancel or correction record carries the original's sequence_number, its exchange and, from the cutover date above, its participant stamp, with a later SIP stamp marking when the amendment was disseminated. Sequence numbers restart every session, so the key is the ticker, the session and the sequence number together. Reconstructing the final tape is one rule: keep rows with correction 0 or 12 and drop the rest. For a point-in-time replay, keep the originals as well and apply each 10 or 12 at its own SIP time, which is when the market learned of it. On the session above, 6495850 prints were regular against 50 cancellations and 13 corrections across fifteen names.

Does the tape field tell you where a stock is listed?

Not in the way its name suggests. The tape column records which processor's feed carried the print: 1 for CTA, 3 for UTP. CTA covers both Tape A, NYSE listings, and Tape B, the Arca and NYSE American listings that include most ETFs, so the column cannot separate the two.

SELECT
    ticker,
    any(tape)     AS tape,
    count()       AS prints
FROM global_markets.stocks_trades
WHERE ticker IN ('SPY', 'IWM', 'GLD', 'IBM', 'GE', 'MSFT', 'QQQ', 'AAPL')
  AND toYYYYMMDD(sip_timestamp) = 20230315
GROUP BY ticker
ORDER BY tape, ticker

SPY, IWM and GLD, all Arca-listed Tape B securities, carry the same value as IBM and GE. The listing venue lives in the exchange column instead, which is the reporting venue for each print and joins to stocks_exchanges: 10 is NYSE, 12 is Nasdaq, 11 is NYSE Arca, 1 is NYSE American, and every off-exchange print reported through a FINRA facility carries 4, in every year of the history.

Are ticker symbols reused?

They are, and the tape stores each symbol exactly as it traded on the day, never renamed after the fact. FB prints through 2022-06-08 and META from 2022-06-09; YHOO becomes AABA on the day of its rename. The consequence for a long study is that one symbol can name two unrelated companies, with nothing on the tape to tell them apart.

SELECT
    if(date < '2010-01-01', 'Tweeter Home Entertainment', 'Twitter')   AS issuer,
    toString(min(date))                                                 AS first_session,
    toString(max(toDate(date)))                                         AS last_session,
    count()                                                             AS sessions,
    round(avg(toFloat64(close)), 2)                                     AS avg_close
FROM global_markets.stocks_daily_aggs
WHERE ticker = 'TWTR' AND date >= '2003-09-10' AND date <= '2022-10-31'
GROUP BY issuer
ORDER BY first_session

TWTR was a home-electronics retailer for 951 sessions ending 2007-06-20, then a social network for 2259 sessions from 2013-11-07. GM before 2009 and GM after 2010 are different companies; DELL disappears in 2013 and returns in 2018. There is no permanent security identifier on the tape, so a multi-year study needs its own map of symbol, first date, last date and issuer, built from an external master and joined on symbol and session. The field notes on every one of these columns now ride on the schema itself, in the note field of the API's schema endpoint and in the API reference's trade tape guide.

FAQ

Which timestamp should a backtest use on the trade tape?

The exchange's own stamp, participant_timestamp, where it is populated, which on this tape is from 2015-07-27 onward. Before that date the field holds a 1970 placeholder on every row and sip_timestamp, the processor's dissemination time, is the only clock on the record.

How do you reconstruct the final tape after cancellations and corrections?

Keep rows with a correction value of 0 or 12 and drop 1, 7, 8, 10 and 11. The amendment record carries the original print's sequence number, so within one ticker and session the pair is identifiable; the cancel or correction record's SIP time is when the amendment reached the market.

Why does the NYSE closing print show up after 4:00 p.m.?

On older sessions the NYSE closing auction completed and printed one to two and a half minutes after the bell, and from mid-2015 the exchange's own timestamp on those prints agrees with the SIP stamp. From 2021 the print lands about two seconds after 4:00 p.m. The official close message, code 15, is a separate row that repeats the print and is re-sent later in the session.

Does the tape include delisted stocks?

Yes, through each company's last exchange-listed session. What it does not include is over-the-counter trading, so a symbol that moves to the pink sheets after a delisting has no rows past its final listed day.