Reconcile stock prices from two data providers
When two stock data providers disagree, first determine whether their rows describe the same observation. Align security, trading session, timezone, price basis and measurement unit before comparing values. Then apply field-specific tolerances, retain links to both original rows and either choose a supported source or quarantine the unresolved date. Averaging incompatible or unexplained prices does not reconcile them.
This guide executes that decision process on two synthetic daily-close and volume tables. It produces a selected sequence and a separate discrepancy map. It does not declare either provider universally correct or demonstrate a CoTrading reconciliation interface. The example covers its declared fields and policies; full OHLC datasets require the same treatment for every additional field and relevant bar invariant.
Declare the target observation before comparing files
The synthetic target is fictional stock AAA, CNY prices, raw price basis, regular-session observations and volume measured in shares. Its daily keys use a fixed UTC+08:00 convention. Those choices belong in the reconciliation configuration; they must not be inferred from whichever source happens to look more complete.
Investor.gov explains that closing prices can differ when regular-session and after-hours conventions are mixed. That is a semantic mismatch before it is a numerical discrepancy. The Alpha Vantage documentation similarly distinguishes adjustment and extended-hours settings in its data parameters. Check the actual source export rather than assuming a column called close has one universal meaning.
For real files, map both schemas to the target fields while preserving original filenames, source row numbers and raw strings. Parse identifiers as text and prices with an explicit numeric representation. Reject duplicate source keys before building dictionaries; otherwise later rows can overwrite earlier conflicts invisibly. The fixture starts from unique synthetic maps and does not claim to implement arbitrary file import.
Normalize timestamps to the intended date convention
The Python datetime reference distinguishes timezone-aware and naive observations. Require an explicit timezone for timestamps and convert to the declared market convention before constructing the daily key. A UTC date and a local session date can differ, so joining their unconverted strings can manufacture missing matches.
def session_date(value):
stamp = datetime.fromisoformat(value)
if stamp.tzinfo is None:
raise ValueError("timezone_required")
return stamp.astimezone(timezone(timedelta(hours=8))).date().isoformat()The script checks that A’s local timestamp and B’s equivalent UTC timestamp on 19 October map to the same date. It also verifies a UTC timestamp crossing into the following local date. This fixed-offset fixture does not implement US daylight-saving calendars or decide whether an event belongs to a valid exchange session. Supply the appropriate real timezone and calendar rules separately.
Separate compatible rows from semantic mismatches
Only rows matching the intended stock, currency, regular session and raw basis enter numeric comparison. A row excluded for after-hours coverage or adjusted prices should be retained with that reason, not labeled an unexplained bad price. If one compatible source remains, our declared policy allows using it with a single-source label.
| Date | A close / volume | B observation | Decision |
|---|---|---|---|
| 19 October | 10 / 1000 | 10.005 / 1000, compatible | choose A within tolerance |
| 20 October | 11 / 1000 | 11.3, after hours | choose compatible A |
| 21 October | 12 / 1000 | 6, adjusted basis | choose raw A |
| 22 October | 13 / 1000 | 13.5 / 1000, compatible | quarantine close conflict |
| 23 October | 14 / 1000 | 14 / 900, compatible | quarantine volume conflict |
| 26 October | absent | 15 / 1000, compatible | choose B, single source |
The dates are supplied synthetic observations, not a downloaded market calendar. A real package should also verify listing intervals and expected sessions. A missing A observation on the 26th does not prove A failed; record the actual request and response status if available.
Compare each accepted field with its own tolerance
Our price tolerance is an absolute 0.01 CNY, while volume tolerance is zero shares. The 19th’s difference 0.005 is within the price tolerance. The 22nd’s difference 0.5 is not. On the 23rd, prices agree but volume differs by 100, so the entire row is withheld rather than mixing A’s price and B’s volume into an undocumented hybrid.
differences = [field for field, tolerance in
[("close", D("0.01")), ("volume", D("0"))]
if abs(D(first[field])-D(second[field])) > tolerance]
if differences:
audit[day] = "unresolved:" + "+".join(differences)
continuePython’s decimal reference documents the explicit decimal arithmetic used for prices and tolerance. Tolerance is a publication-precision policy, not an estimate of economic truth. Do not raise it after seeing a troublesome date simply to eliminate an anomaly. Save the original difference and any later approved tolerance revision.
For full bars, compare open, high, low, close, volume and any other required field, then validate high-low enclosure and units on the selected row. Passing close alone does not certify an entire OHLCV file. Keep optional fields outside the accepted contract until their definitions are confirmed.
Inspect the actual selected sequence
Download the complete example. Use Python 3.12+, the standard library and synthetic tables; run python R532.py. The source is docs/growth/research/round-5/examples/R532.py.
Actual output selects A’s closes 10, 11 and 12 on the 19th, 20th and 21st, and B’s close 15 on the 26th. All selected volumes are 1000. The audit marks the 22nd unresolved:close and the 23rd unresolved:volume; neither date enters the canonical sequence. Assertions verify the exact dates, selected source on the fallback date and both quarantined conflicts.
Source preference A before B applies only after compatibility and conflict checks. It does not automatically make A’s 13 authoritative on the 22nd. Within tolerance, choosing A preserves an actual source observation instead of inventing an average of 10 and 10.005. A single-source fallback remains explicitly less corroborated than agreement from two compatible rows.
Preserve unresolved disagreements instead of smoothing them
The price conflict is the primary counterexample: both rows describe the requested observation, yet the difference exceeds the declared tolerance. Without evidence resolving it, leave the date out of the usable sequence and retain both original values. Do not average 13 and 13.5 or choose the prettier return path.
The volume conflict is a different failure despite agreeing closes. A downstream liquidity calculation could be wrong even when price returns match. Quarantining the full row avoids silently constructing mixed-source bars. If a later source correction resolves the field, record the new evidence and rerun that date’s selection.
After-hours and adjustment mismatches need different repairs: obtain a matching export or keep the compatible source under the single-source policy. They should not be put into the same numerical outlier bucket. This distinction makes the next action operational rather than merely calling one provider unreliable.
Deliver a usable sequence with a coverage boundary
The handoff contains both original tables, normalization rules, target contract, field tolerances, daily comparison results, selected source reasons and quarantined dates. Preserve the source-and-date key needed to return to each original row. Reconcile the union of input dates to selected or withheld outcomes, and report resulting gaps to downstream consumers.
A four-date selected sequence with two quarantined dates is not a complete six-date history. Do not interpolate those gaps simply to make a chart continuous. If downstream use requires full coverage, the reconciliation remains incomplete until the source conflict is resolved or a documented alternative is accepted.
Reopen the process after corrected files, changed session settings, corporate-action basis changes or tolerance revisions. Keep the earlier selected version for existing research runs. The result is an inspectable row-selection operation with supported values and visible unresolved boundaries, not a permanent judgment that one data vendor is always correct.
Make every selected value traceable
The source name and date should resolve to the exact retained input row, including its original timestamp and basis. When a source corrects a value, keep the old row and create a new source revision rather than updating the evidence behind an already published selection in place. This allows an earlier research run to reproduce the value it actually consumed.
Review a within-tolerance date, a semantic mismatch and each unresolved field conflict. These paths test different decisions: corroborated preference, single-compatible-source use and withholding. A test containing only obvious price disagreements would not verify that volume or session definitions influence the output.
When exporting the canonical sequence, export its coverage and provenance as well. A downstream consumer that reads only selected prices can miss the reason for gaps or the weaker support of a fallback date. Require that consumer to accept the declared partial coverage before calculation starts; the reconciliation table should not be interpreted as permission to invent missing bars or ignore its unresolved dates.
FAQ
Can conflicting prices be averaged to reduce noise?
That creates a new unsupported observation. First resolve their meaning and the reason for the difference.
Does agreement on close certify the whole bar?
No. The volume conflict demonstrates why every required field and its unit must be checked.