2. Prices: consolidated, delayed and official close

Level 1: Where they come from and what they carry inside

When you write datos["Close"] you think you are asking for "the price". There is no such thing. There are dozens of defensible numbers for the same stock at the same instant, and the column that reaches you is a decision somebody made on your behalf. This module is about knowing which one you got.

A stock trades in many places at once

In the United States there are more than a dozen registered exchanges and dozens of alternative venues, on top of the enormous volume executed off-exchange through wholesalers and internalisers. No single exchange holds the majority of the volume in a large stock, and the share executed away from the public markets has run, in recent years, at something like half of the total.

Out of that come two different products:

• Consolidated data. It collects trades and quotes from every venue and publishes a single tape, with the best bid and the best offer across the market as a whole. It is normally what you want for analysis.
• Single-venue data. The direct feed of one particular exchange. It is faster and cheaper, but it is a fraction of the market. If your cheap source gives you the "close" from a single venue, that number may look nothing like the price at which the stock actually closed.

Last trade versus midpoint

The last trade is a fact: somebody bought and somebody sold at that price. But it is a contaminated fact, because a trade executes against the bid or against the offer, not in the middle. With a wide spread, the last trade bounces between the two extremes without the stock having moved at all: this is bid-ask bounce, and in illiquid names it manufactures measured volatility that does not exist.

The midpoint of the best bid and the best offer is not a price anyone traded at, but it is usually a better estimate of the consensus value at that instant.

import pandas as pd

libro = pd.DataFrame({
    "hora": pd.to_datetime(["2026-03-16 21:59:58", "2026-03-16 21:59:59"]),
    "bid": [101.10, 101.12],
    "ask": [101.30, 101.34],
    "ultimo": [101.11, 101.30],   # an aggressive sell and then an aggressive buy
})

libro["medio"] = (libro["bid"] + libro["ask"]) / 2
libro["horquilla_pb"] = (libro["ask"] - libro["bid"]) / libro["medio"] * 10000
libro["sesgo_ultimo_pb"] = (libro["ultimo"] - libro["medio"]) / libro["medio"] * 10000
print(libro[["hora", "medio", "horquilla_pb", "sesgo_ultimo_pb"]])

In one second, the midpoint has moved barely three cents and the last trade has jumped nineteen. If your price series is a sequence of last trades in a wide-spread name, you are measuring the sign of the orders, not the price.

The official close is not the last trade of the day

Here is the most expensive misunderstanding in the module. On modern exchanges, the close is formed in an auction: for a few minutes orders pile up without being crossed and, at the end, an algorithm computes the single price that maximizes the volume traded. That auction price is the official close, and it is the one used by indices, index funds and portfolio valuations.

Consequences worth internalizing:

• The official close may not match any earlier trade of the day, and it can sit above or below the last price seen in continuous trading.
• The closing auction concentrates a disproportionate share of the daily volume, on the order of a tenth of the total in large caps, all in a single instant.
• Many cheap sources fill their close column with the last trade recorded on their tape, which may come from an exchange that does not take part in the primary auction, or may even be later still, in after-hours trading.

The same happens at the open: there is an opening auction, and the "Open" you see may be that auction price or the first trade your vendor happened to see. They are not the same thing.

Why two serious vendors disagree

Before accusing anyone of having bad data, here is the list of legitimate reasons why two closes differ for the same stock on the same day:

• One uses the official auction price and the other the last trade on the consolidated tape.
• One includes after-hours trades and the other excludes them.
• One filters out trades flagged as ineligible for last-price purposes and the other does not.
• One takes a primary market and the other consolidates.
• One has already applied corporate action adjustments and the other has not yet.
• One rounds to two decimals and the other stores four.

The grown-up way to handle this is not to pick a vendor and believe them: it is to cross-check them and monitor the difference.

a = pd.read_csv("cierres_proveedor_a.csv", parse_dates=["fecha"])
b = pd.read_csv("cierres_proveedor_b.csv", parse_dates=["fecha"])

comp = a.merge(b, on=["fecha", "ticker"], suffixes=("_a", "_b"))
comp["dif_pb"] = (comp["cierre_a"] / comp["cierre_b"] - 1) * 10000

print(comp["dif_pb"].abs().describe())

# Pragmatic threshold: under 25 bp it is usually definition; above that, go and investigate.
sospechosos = comp.loc[comp["dif_pb"].abs() > 25, ["fecha", "ticker", "cierre_a", "cierre_b", "dif_pb"]]
print(sospechosos.sort_values("dif_pb", key=abs, ascending=False).head(20))

If the discrepancies cluster on a handful of dates, suspect a market event or an adjustment applied by only one of the two. If they are spread evenly across every day and every stock, what you have is a difference of definition, and you have to choose which one you want.

A real case: August 24, 2015

That morning US futures opened sharply lower and the cash open descended into disorder. More than a thousand volatility trading halts were recorded that day, spread across several hundred stocks and exchange-traded funds. Many ETFs traded far below the value of the assets they held, with intraday falls that in some cases exceeded 30% while their underlying portfolios fell a fraction of that.

The lesson for anyone building data: during those minutes, "the price" of a single fund was radically different depending on whether you looked at the last trade, the midpoint of an enormous spread, the consolidated tape or a single venue. A backtest that assumes it could have executed at those prices is fantasizing. Education, not financial advice.