Repeated public trade IDs: audit two captures without hiding conflicts

A repeated ID is a question about identity, not a command to delete

After reconnecting a public feed, a research file may contain a saved earlier block and a later block. Some records can appear in both. Counting every row may count a repeated representation twice; keeping the last row for each number may erase a real disagreement. This worksheet shows the groups and preserves their evidence so you can decide what your next investigation needs.

It takes two small captures from the same declared venue and feed namespace. It does not connect, replay a stream or repair a database. Every accepted source row appears in the exported report, including conflicts and records without IDs. A useful report is available before the optional link to this site’s separate offer.

Start with the documented scope of the identifier

Kraken’s public spot WebSocket v 2 trade documentation describes its trade ID as unique per book. It also distinguishes snapshot and update messages, and says several trades can be batched in one message without representing one taker order. These details illustrate why a bare number, a message count and an own-order ID are not interchangeable identities. They do not establish the rules of another feed.

For this worksheet, the grouping key is the exact symbol together with the exact trade ID, inside the single declared venue and namespace. BTC/USD 100 and ETH/USD 100 remain separate groups. The source reference records why that rule is appropriate for your supplied records. If the rule or reference is unknown, the worksheet reports possible groups but withholds its identity-based counts.

Sources: Kraken Developers — Spot WebSocket v 2 Trades.

A matching key can still contain conflicting evidence

Inside each group, compare the represented event instant, price, quantity and side. Decimal strings 100 and 100.0 represent the same numerical price here. A UTC timestamp and a +08:00 timestamp can represent the same instant. The report marks these spelling changes separately instead of mislabelling them as different trades. Original strings remain available for inspection.

A changed price, quantity, instant or side is a conflicting payload. The worksheet does not automatically choose the later capture, average the prices or assume that a correction occurred. A parser mistake, source revision, namespace error or some other explanation may be involved. The report identifies the disagreement; it does not select its cause. An unknown side prevents a fully equivalent-payload result even if all other fields agree.

Read all four groups in the fictional example

The first BTC/USD 101 record uses UTC and the second uses Singapore’s +08:00 offset. Their represented instant is identical, as are 101 versus 101.0 and 0.2 versus 0.200. That group contains two equivalent payloads. In contrast, the two BTC/USD 100 rows disagree on price 100 versus 100.5, so their group is retained as conflicting.

The ETH/USD 100 row belongs to another exact symbol and is therefore a single separate group. BTC/USD 102 is another single group. Six input rows have not become six confirmed distinct trades, nor has the conflict been silently reduced to one accepted record. The three uncontested groups and one conflicted group describe only the submitted excerpt.

Do not reset trade identity at a diary date boundary

Singapore Standard Time is UTC+8. The government-hosted BCA data standard explicitly describes that offset; this page uses only the time representation, not its construction rules. An event just before 16:00 UTC belongs to the previous Singapore calendar date, while one just after belongs to the next. A local diary label should not silently change an identifier’s documented scope.

In the example, both BTC/USD 100 rows describe 23:59:59.900001 on 20 October in Singapore. The chosen journal date is 21 October, so both are flagged outside that date, but their conflict is still examined across the captures. Splitting files by arrival date or throwing away the earlier local day would conceal that relationship. The supplied event time is not a measured message-arrival time.

Sources: BCA — Site Management Data Standards v 1.1, page 65.

Keep enough timestamp precision to see a conflict

The source documentation’s example timestamp includes microseconds. This worksheet accepts up to nine fractional digits and compares their integer representation without discarding submillisecond differences. A change from .100001 to .100002 therefore remains visible. Adding trailing zeros to an equivalent instant does not create a new trade.

This is representation precision, not clock accuracy. A date-only value, an unqualified local time or unsupported offset is rejected instead of assigned an invented time zone. The report does not measure the delay between captures, establish chronological delivery or prove that all events between the two files were received. It only compares the rows that were supplied.

Unknown IDs and incomplete files stay unknown

A blank ID is retained as an unidentified record. Two such rows are not merged because their price and quantity happen to match. Conversely, two numerically similar ID strings with different leading zeros remain different exact identifiers under this worksheet’s text contract. If your source defines a different canonical spelling, document and prepare that conversion separately rather than guessing it here.

The absence of an ID from the second capture does not prove a missing market trade or a failed reconnect. A capture can be bounded, a snapshot can cover a different interval, and this worksheet has no connection log. Feed completeness, the cause of repetition and your own execution status remain unknown. Public trade records cannot authenticate a private account fill.

Use the audit to preserve the question you need to resolve

Review the declared ID rule first, then conflicted and incomplete groups, then equivalent repeats. Use the source-row references to retrieve both original records. If you later obtain authoritative correction evidence, make a new report and keep the earlier uncertainty. The tool intentionally offers an audit CSV rather than a cleaned replacement that would discard competing evidence.

The export includes every original event time, price and quantity spelling, normalised values, source capture and Singapore date flag. Formula-like text is protected in CSV; import identifiers as text in your spreadsheet. Editing any field invalidates the old result until recalculation. With JavaScript off, the full method and fictional group table remain readable.

Worked examples

{
  "venue": "DEMO venue",
  "namespace": "DEMO public spot trade feed v1",
  "id_rule": "per_book",
  "id_reference": "DEMO saved documentation: trade ID unique within each exact symbol book",
  "journal_date": "2026-10-21",
  "before_rows": "symbol|trade_id|event_at|price|quantity|side\nDEMO-BTC/USD|100|2026-10-20T15:59:59.900001Z|100|0.1|buy\nDEMO-BTC/USD|101|2026-10-20T16:00:00.100001Z|101|0.2|buy\nDEMO-ETH/USD|100|2026-10-20T16:00:01Z|200|0.3|sell",
  "after_rows": "symbol|trade_id|event_at|price|quantity|side\nDEMO-BTC/USD|101|2026-10-21T00:00:00.100001+08:00|101.0|0.200|buy\nDEMO-BTC/USD|102|2026-10-20T16:00:02Z|102|0.4|sell\nDEMO-BTC/USD|100|2026-10-20T15:59:59.900001Z|100.5|0.1|buy"
}

Questions about the method

Primary sources and scope

1. Kraken Developers — Spot WebSocket v 2 TradesChecked 2026-10-20. Trade ID scope per book, snapshot/update distinction, batched trade events and fractional timestamp example. No subscription, eligibility or current feed behaviour verified.
2. BCA — Site Management Data Standards v 1.1, page 65Checked 2026-10-20. Page 65 identifies +08:00 as Singapore Standard Time. No construction rule or financial obligation imported.

Examples explain a method. They do not predict prices or establish where a trading service is available. Check the named source before relying on future schedules.