--- title: "4.5.5 Fault tolerance: idempotency and reconciliation" description: "Idempotent orders with client keys, state in SQLite, crash and restart, and reconciliation with the broker statement that finds 13 failures." url: "https://pythonparatrading.com/en/python-trading-course/level-4/fault-tolerance-idempotency-and-reconciliation/" lang: "en" image: "https://pythonparatrading.com/og/en/python-trading-course/level-4/fault-tolerance-idempotency-and-reconciliation.png" --- # 4.5.5 · Fault tolerance: idempotency, persistent state and reconciliation at startup > A live system goes down: the process dies, the connection drops, the broker resends messages or skips them. This lesson builds an order manager that survives that with idempotency keys, state in SQLite and transactions, kills it in the middle of an event and brings it back up, and reconciles four months of the live account against the broker statement until it finds, one by one, the thirteen failures planted in the data. - Course: [Level 4: Portfolios, risk, execution and live trading](https://pythonparatrading.com/en/python-trading-course/level-4/) - Module: 4.5 · Live trading system - Duration: 75 min - Prerequisites: [3.7.4 · ccxt for trading on multiple exchanges](https://pythonparatrading.com/en/python-trading-course/level-3/ccxt-multi-exchange-trading/), [4.5.2 · Order management: states, partial fills, cancellations and reconnections](https://pythonparatrading.com/en/python-trading-course/level-4/order-management-states-fills-and-reconnections/), [4.5.4 · Task scheduling and alerts: cron, APScheduler, Prefect, Telegram, Slack and email](https://pythonparatrading.com/en/python-trading-course/level-4/task-scheduling-and-trading-alerts/) ## By the end of this lesson - Identify the failure windows of an order system and what is lost or duplicated in each one. - Generate deterministic idempotency keys from the signal and explain why a random key does not protect you after a restart. - Store the order manager's state in SQLite with transactions, so that an event is applied in full or not at all, even if the process dies halfway. - Reconcile executions, positions, cash and orders against the broker statement and classify each difference. - Design the startup of a system: load state, reconcile and decide whether it trades or blocks. ## Where an order system breaks Lesson 4.5.2 followed the life of an order with the system running: states, partial fills, reconnections. This one is about what happens when something breaks in the middle. An order management system (OMS) receives a signal, saves what it is going to do, sends the order, receives the acknowledgment and the executions, and updates position and cash. Between every two steps there is a window in which the process can die or the network can drop: ```mermaid flowchart TB S[Signal] --> G[Save intent] G --> E[Send order] E --> A[Broker ack] A --> X[Execution] X --> P[Save execution] P --> C[Reconcile statement] ``` | Fails between… | What the system sees on return | Risk | Remedy | |---|---|---|---| | signal and saving the intent | nothing | losing the order (it is recomputed) | deterministic, reproducible signal | | sending and ack | a “sent” order with no response | **sending it twice** | idempotency key and lookup by key | | execution and saving it | a stale position | losing the execution or **counting it twice** | one transaction per event, unique execution id | | broker and system (network) | gaps, duplicates, out-of-order messages | a state that is not the account's | sequence numbers, deduplication, reconciliation | | system and accounting reality | what the broker does not send as an event | interest, fees, corrections, corporate actions | reconciliation with the statement | The data of the fictional level 4 system (synthetic) come with all those failures planted in the live account `U-0418272`, from October 13 to December 31, 2025, with their known answer in `vivo4_anomalias_verdad.csv`. Lesson 4.5.2 already covered the ones visible in the message stream while the system is trading (duplicates, out-of-order messages, gaps and the resent order); this one adds what happens when the one that goes down is the process itself and what only shows up when comparing with the broker statement. At the end, it compares the list of findings with that truth. The header figure gives away the result: the cash the system thinks it has, as counted by the events and after reconciling, against the cash in the statement. ## Idempotency: the same order, only once Lesson 3.7.4 taught the basic case: a timeout does not tell you whether the order arrived, and the solution is to send it with your own identifier (`clientOrderId`) so that the broker rejects the retry as a duplicate. Lesson 4.5.2 analyzed the mistake of the system in the data: on November 13, during a disconnection, the acknowledgment of `R00113` never arrived and the OMS resent it as `R00114`, with another key, without asking the broker first. The summary, for what follows: ```python import numpy as np import pandas as pd ev = pd.read_csv("vivo4_eventos_broker.csv") real = ev[ev["modo"] == "real"].reset_index(drop=True) ords = pd.read_csv("vivo4_ordenes.csv").set_index("id_orden_cliente") sen = pd.read_csv("vivo4_senales.csv").set_index("id_senal") par = ords.loc[["R00113", "R00114"]] print(par[["id_senal", "ts_envio", "lado", "cantidad", "motivo", "orden_origen"]].to_string()) fills = real[real["id_orden_cliente"].isin(par.index) & real["id_ejecucion"].notna()] print(fills[["seq", "id_orden_cliente", "cantidad_ultima", "precio_ultimo"]].to_string(index=False)) s = sen.loc["S00215"] print("signal S00215:", s["posicion_actual"], "->", s["posicion_objetivo"], s["instrumento"]) ``` ```output id_senal ts_envio lado cantidad motivo orden_origen id_orden_cliente R00113 S00215 2025-11-13T20:50:00.246596Z venta 7 rebalanceo NaN R00114 S00215 2025-11-13T20:51:14.296596Z venta 7 reenvio_sin_respuesta R00113 seq id_orden_cliente cantidad_ultima precio_ultimo 309 R00114 7 717.07 298 R00113 7 716.82 signal S00215: 147 -> 140 ORUS ``` The signal asked to sell 7 ORUS (from 147 to 140). Both orders were executed: 14 sold. The broker did nothing wrong; it received two orders with different keys. Asking before resending (the solution in 4.5.2) requires knowing what to ask, and that is where the way the key is generated comes in. Two approaches, with a simulated broker that loses the response to the first order and an OMS that, not receiving it, dies and restarts before resending the pending signal: ```python import uuid class BrokerSim: """Accepts orders; rejects a repeated key; can lose the response to the next order (the order does go through).""" def __init__(self): self.ordenes, self.perder = {}, 0 def enviar(self, clave, instrumento, cantidad): if clave in self.ordenes: return {"estado": "duplicate", "clave": clave} self.ordenes[clave] = (instrumento, cantidad) if self.perder: self.perder -= 1 raise TimeoutError("no response") return {"estado": "accepted", "clave": clave} def consultar(self, clave): return self.ordenes.get(clave) def clave_aleatoria(senal, cuenta): return uuid.uuid4().hex # new on every call def clave_de_senal(senal, cuenta): return f"{cuenta}-{senal}" # comes from the intent def ejecutar(generar, reinicios=1): """Sends S00215; the response is lost and the process restarts `reinicios` times before retrying.""" b = BrokerSim() b.perder = 1 for _ in range(reinicios + 1): # each pass: one process clave = generar("S00215", "U-0418272") try: r = b.enviar(clave, "ORUS", -7) except TimeoutError: continue # the process “dies” if r["estado"] == "duplicate": r = {"estado": "already existed", **r} break return len(b.ordenes), r["estado"] for nombre, g in (("random uuid", clave_aleatoria), ("signal key", clave_de_senal)): print(f"{nombre:18} orders at the broker: %d · %s" % ejecutar(g)) ``` ```output random uuid orders at the broker: 2 · accepted signal key orders at the broker: 1 · duplicate ``` With a random UUID, the new process generates another key and the broker accepts a second order: it is exactly what happened with `R00113` and `R00114`. With a key that comes from the intent (account and signal; if a signal can produce several orders, also an order number within the signal), the retry carries the same key even if another process makes it, the broker recognizes it and the system only has to query the state of the order that already exists. The equivalent alternative is to generate the random key **and save it to disk before sending**: the new process reads it instead of making up another one. Two precautions with real brokers. Not all of them reject a repeated key the same way, and not all of them accept one (Alpaca, for example, describes it as a unique identifier for the order of up to 128 characters[^1]; in ccxt it depends on the exchange): you have to check in the test environment what the broker responds to a repeated key before relying on it. And if the broker does not support it, the only defense is to query the day's open orders and executions before resending (lesson 3.7.4). ## Persistent state: one event, one transaction The OMS stores its state in SQLite, which ships with Python: a single file, ACID transactions and nothing to install. Four tables are enough for what matters here: the events received (key: `seq`, the broker's sequence number), the executions (key: `id_ejecucion`), the positions and the cash. The design rule is that each broker event is applied in **a single transaction**: either the event, the execution, the position and the cash are all saved, or nothing is. ```python import sqlite3 ESQUEMA = """ CREATE TABLE IF NOT EXISTS eventos ( seq INTEGER PRIMARY KEY, id_orden TEXT, evento TEXT, id_ejecucion TEXT); CREATE TABLE IF NOT EXISTS ejecuciones ( id_ejecucion TEXT PRIMARY KEY, id_orden TEXT, instrumento TEXT, cantidad INTEGER, precio REAL, comision REAL, seq INTEGER); CREATE TABLE IF NOT EXISTS posiciones ( instrumento TEXT PRIMARY KEY, cantidad INTEGER NOT NULL); CREATE TABLE IF NOT EXISTS efectivo ( id INTEGER PRIMARY KEY CHECK (id = 1), saldo REAL NOT NULL); """ SIGNO = ords["lado"].map({"compra": 1, "venta": -1}) INSTR = ords["instrumento"] def abrir(ruta): con = sqlite3.connect(ruta, isolation_level=None) # manual BEGIN con.execute("PRAGMA journal_mode=WAL") con.execute("PRAGMA synchronous=FULL") con.executescript(ESQUEMA) con.execute("INSERT OR IGNORE INTO efectivo VALUES (1, 1e6)") return con class Caida(BaseException): """The process dies: nobody does a ROLLBACK or closes anything.""" def aplicar(con, e, morir=False): con.execute("BEGIN IMMEDIATE") nuevo = con.execute( "INSERT OR IGNORE INTO eventos VALUES (?, ?, ?, ?)", (int(e.seq), e.id_orden_cliente, e.evento, e.id_ejecucion if isinstance(e.id_ejecucion, str) else None) ).rowcount if nuevo and isinstance(e.id_ejecucion, str): q = int(SIGNO[e.id_orden_cliente] * e.cantidad_ultima) ins = INSTR[e.id_orden_cliente] con.execute("INSERT INTO ejecuciones VALUES (?,?,?,?,?,?,?)", (e.id_ejecucion, e.id_orden_cliente, ins, q, e.precio_ultimo, e.comision, int(e.seq))) con.execute("""INSERT INTO posiciones VALUES (?, ?) ON CONFLICT(instrumento) DO UPDATE SET cantidad = cantidad + excluded.cantidad""", (ins, q)) if morir: raise Caida(f"dies at seq {e.seq}, before the COMMIT") con.execute("UPDATE efectivo SET saldo = saldo - ? - ?", (q * e.precio_ultimo, e.comision)) con.execute("COMMIT") return nuevo def estado(con): pos = dict(con.execute("SELECT instrumento, cantidad FROM " "posiciones WHERE cantidad != 0")) saldo = con.execute("SELECT saldo FROM efectivo").fetchone()[0] n = con.execute("SELECT COUNT(*) FROM ejecuciones").fetchone()[0] return pos, round(saldo, 2), n ``` Three details. `isolation_level=None` disables the implicit transactions of the `sqlite3` module so that we open and close them by hand (`BEGIN IMMEDIATE` takes the write lock at the start). `INSERT OR IGNORE` on the primary key makes a repeated event do nothing: `rowcount` is 0 and the position is not touched. And WAL mode with `synchronous=FULL` ensures that a completed COMMIT is on disk even if the power goes out afterward; SQLite guarantees that a half-done transaction is rolled back when the file is opened again[^2]. The `Caida` exception inherits from `BaseException` so that no `except Exception` catches it: it represents the death of the process, not an error the code handles. First, the reference: all the events of the live account, in the order they arrived, with no crashes. ```python from pathlib import Path def limpiar(ruta): for sufijo in ("", "-wal", "-shm"): Path(ruta + sufijo).unlink(missing_ok=True) limpiar("oms.db") con = abrir("oms.db") aplicados = sum(aplicar(con, e) for e in real.itertuples()) ref_pos, ref_saldo, ref_n = estado(con) con.close() print(len(real), "messages;", aplicados, "applied;", ref_n, "executions") print("cash:", ref_saldo) print(dict(sorted(ref_pos.items()))) ``` ```output 649 messages; 645 applied; 348 executions cash: 981849.26 {'ORAU': 488, 'ORIG': -557, 'ORTN': 112} ``` Six hundred forty-nine messages, 645 applied and 348 executions: the remaining four are exact repeats of messages already received, with the same `seq` and the same execution, which the primary key discards. They are the executions the broker resent when reconnecting on October 21 and December 9. Counting them twice would have added shares and costs that do not exist. ## The process dies in the middle of an event Now the crash. A naive OMS updates the position and the cash in one transaction and records the event as processed in another; the transactional one does it all in one. In both cases the process dies at event number 292 in the list (the sale of 923 ORIG of `R00109`, on November 6), the file is reopened as a new process would, and the broker resends the messages from the last one the system had not confirmed: ```python def aplicar_ingenuo(con, e, morir=False): """Position and cash in one transaction; the event mark, in another. No unique keys on the executions.""" hecho = con.execute("SELECT 1 FROM eventos WHERE seq = ?", (int(e.seq),)).fetchone() if hecho: return 0 if isinstance(e.id_ejecucion, str): q = int(SIGNO[e.id_orden_cliente] * e.cantidad_ultima) con.execute("BEGIN") con.execute("""INSERT INTO posiciones VALUES (?, ?) ON CONFLICT(instrumento) DO UPDATE SET cantidad = cantidad + excluded.cantidad""", (INSTR[e.id_orden_cliente], q)) con.execute("UPDATE efectivo SET saldo = saldo - ? - ?", (q * e.precio_ultimo, e.comision)) con.execute("COMMIT") if morir: raise Caida(f"dies at seq {e.seq}, between the COMMITs") con.execute("INSERT INTO eventos VALUES (?, ?, ?, ?)", (int(e.seq), e.id_orden_cliente, e.evento, None)) return 1 K = 292 # the event where it dies print(real.loc[K, ["seq", "id_orden_cliente", "evento", "cantidad_ultima"]].to_dict(), INSTR[real.loc[K, "id_orden_cliente"]]) for nombre, fn in (("naive", aplicar_ingenuo), ("transactional", aplicar)): limpiar("oms.db") con = abrir("oms.db") try: for i, e in enumerate(real.itertuples()): fn(con, e, morir=(i == K)) except Caida as c: print(f"{nombre}: {c}") con.close() # the OS closes the file con = abrir("oms.db") # a new process for e in real.iloc[K:].itertuples(): # the broker resends fn(con, e) pos, saldo, _ = estado(con) con.close() dif = {k: pos.get(k, 0) - ref_pos.get(k, 0) for k in set(pos) | set(ref_pos) if pos.get(k, 0) != ref_pos.get(k, 0)} print(f" difference from the reference: positions {dif}, " f"cash {saldo - ref_saldo:+,.2f}") ``` ```output {'seq': 292, 'id_orden_cliente': 'R00109', 'evento': 'ejecutada', 'cantidad_ultima': 923} ORIG naive: dies at seq 292, between the COMMITs difference from the reference: positions {'ORIG': -923}, cash +105,197.23 transactional: dies at seq 292, before the COMMIT difference from the reference: positions {}, cash +0.00 ``` The naive one had committed the execution but not the processed-event mark; on return, the resent event looks new and the execution is counted again: the system believes it sold 923 more ORIG than it did and holds 105,197.23 USD of cash that do not exist. The transactional one dies with the execution and the position written but not committed: SQLite discards them, the resent event is applied in full once, and the state matches the reference to the cent. A variant of the same problem is dying **after** the COMMIT but before telling the broker that the message was processed: the broker resends it, and `INSERT OR IGNORE` discards it. That is the general idea: the network delivers “at least once”, and idempotent processing turns that into an “exactly once” effect. ## What comes from the broker: duplicates, out-of-order messages and gaps Lesson 4.5.2 built a sequencer that sorts messages by `seq`, discards repeats and detects gaps while the system is trading. For the reconciliation, it is enough to have the list of what it found, in one cell: the duplicates (which the primary key has already discarded), the executions that arrive after a later one of the same order (the cumulative quantity goes backwards, which is what breaks a state machine) and the `seq` gaps. ```python dup = real[real.duplicated(["seq", "evento", "id_ejecucion"])] unicos = real.drop(dup.index) fills = unicos[unicos["evento"].isin(["parcial", "ejecutada"])] previo = (fills.groupby("id_orden_cliente")["cantidad_acumulada"] .transform(lambda s: s.cummax().shift())) desorden = fills[fills["cantidad_acumulada"] < previo] huecos = sorted(set(range(1, real["seq"].max() + 1)) - set(real["seq"])) tarde = (unicos["seq"] < unicos["seq"].cummax().shift()).sum() print("duplicates:", dup[["seq", "id_ejecucion"]] .to_records(index=False).tolist()) print("arrive after a higher seq:", int(tarde)) print("execution that goes backwards:", desorden[["seq", "id_orden_cliente", "id_ejecucion", "cantidad_acumulada"]].to_dict("records")) print("gaps in seq:", huecos) ``` ```output duplicates: [(61, 'E710000210.01'), (62, 'E710000168.01'), (471, 'E710001365.01'), (475, 'E710001386.01')] arrive after a higher seq: 47 execution that goes backwards: [{'seq': 393, 'id_orden_cliente': 'R00162', 'id_ejecucion': 'E710001134.01', 'cantidad_acumulada': 857}] gaps in seq: [202, 519] ``` Four duplicates, 47 messages that arrive after another one with a higher number (almost all of them acknowledgments overtaken by their execution, or the batch the broker resends after a reconnection) and only one that matters: execution `E710001134.01` of `R00162`, with 857 cumulative shares, arrives when another one of the same order with 1,509 had already arrived. An OMS that processes in order of arrival would put the order at 857 after having marked it as filled; one that sorts by `seq` before applying the state machine would not. And two gaps, 202 and 519: two messages that never arrived and that the broker did not resend. Lesson 4.5.2 rebuilt their quantity and price from the cumulative figure of the next message; the statement confirms or corrects it. ## Reconciliation with the statement: executions The broker statement (`vivo4_extracto_*`) is the accounting truth of the account: what the broker settles, charges and holds in custody. The reconciliation compares four things, from the most specific to the most aggregate: executions, positions, cash and, finally, orders against signals. ```python ex = pd.read_csv("vivo4_extracto_operaciones.csv") ej = (unicos[unicos["id_ejecucion"].notna()] [["id_ejecucion", "id_orden_cliente", "cantidad_ultima", "precio_ultimo", "comision", "seq"]]) m = ex.merge(ej, on="id_ejecucion", how="outer", indicator=True, suffixes=("_ext", "_ev")) print(m["_merge"].value_counts().to_dict()) solo_ext = m[m["_merge"] == "left_only"] cliente_de = (real.dropna(subset=["id_orden_broker"]) .drop_duplicates("id_orden_broker") .set_index("id_orden_broker")["id_orden_cliente"]) print(solo_ext[["fecha_operacion", "id_ejecucion", "instrumento", "lado", "cantidad", "precio"]].to_string(index=False)) ambos = m[m["_merge"] == "both"] otro_px = ambos[(ambos["precio"] - ambos["precio_ultimo"]).abs() > 1e-9] otro_q = ambos[ambos["cantidad"] != ambos["cantidad_ultima"]] otra_com = ambos[(ambos["comision_ext"] - ambos["comision_ev"]).abs() > 1e-9] print("different price:", otro_px[["fecha_operacion", "id_ejecucion", "cantidad", "precio", "precio_ultimo"]].to_dict("records")) print("different quantity:", len(otro_q), "| different commission:", len(otra_com)) ``` ```output {'both': 348, 'left_only': 2, 'right_only': 0} fecha_operacion id_ejecucion instrumento lado cantidad precio 2025-11-03 E710000553.01 ORTN venta 112 99.06 2025-12-15 E710001498.01 ORIG compra 557 114.03 different price: [{'fecha_operacion': '2025-12-03', 'id_ejecucion': 'E710001162.01', 'cantidad': 170, 'precio': 115.34, 'precio_ultimo': 115.29}] different quantity: 0 | different commission: 0 ``` Three hundred forty-eight executions match by identifier. Two are in the statement and not in the events: they are exactly the two gaps, a partial sale of 112 ORTN on November 3 and a partial purchase of 557 ORIG on December 15, which the broker did not resend when reconnecting. None is in the events and missing from the statement. And one matches by identifier but not by price: the broker settled `E710001162.01` at 115.34 instead of the 115.29 in the execution message, a later correction of 8.50 USD on 170 shares. Quantities and commissions match in all of them. ## Positions: what the broker did without telling you With the statement's executions incorporated (the missing ones and the corrected price), the system's positions should be the statement's every day. Positions are rebuilt by summing executions by date and compared with `vivo4_extracto_posiciones.csv`, which gives each day's closing position: ```python ej_f = ej.assign( fecha=ords.loc[ej["id_orden_cliente"], "fecha"].to_numpy(), instrumento=INSTR[ej["id_orden_cliente"]].to_numpy(), q=(SIGNO[ej["id_orden_cliente"]].to_numpy() * ej["cantidad_ultima"].to_numpy())) falta = solo_ext.assign( fecha=solo_ext["fecha_operacion"], q=np.where(solo_ext["lado"] == "compra", 1, -1) * solo_ext["cantidad"]) todas = pd.concat([ej_f[["fecha", "instrumento", "q"]], falta[["fecha", "instrumento", "q"]]]) pos_ext = (pd.read_csv("vivo4_extracto_posiciones.csv") .pivot(index="fecha", columns="instrumento", values="cantidad").fillna(0)) pos_sis = (todas.pivot_table(index="fecha", columns="instrumento", values="q", aggfunc="sum") .reindex(pos_ext.index).fillna(0).cumsum()) dif = (pos_ext - pos_sis.reindex(columns=pos_ext.columns, fill_value=0)) dif = dif.loc[:, (dif != 0).any()] print(dif[(dif != 0).any(axis=1)].head(3)) d0 = dif.index[(dif != 0).any(axis=1)][0] sis = pos_ext.loc[d0, "ORAU"] - dif.loc[d0, "ORAU"] x = pos_ext.loc[d0, "ORAU"] / sis px = pd.read_csv("vivo4_extracto_posiciones.csv").query( "instrumento == 'ORAU'").set_index("fecha")["precio_cierre"] print(f"{d0}: ratio {x:.4f}; close {px.shift().loc[d0]} -> " f"{px.loc[d0]} (ratio {px.shift().loc[d0] / px.loc[d0]:.3f})") efe = pd.read_csv("vivo4_extracto_efectivo.csv") print(efe[efe["concepto"] == "desdoblamiento"].to_string(index=False)) ``` ```output instrumento ORAU fecha 2025-11-17 374.0 2025-11-18 374.0 2025-11-19 374.0 2025-11-17: ratio 3.0000; close 369.01 -> 124.15 (ratio 2.972) fecha concepto instrumento importe saldo 2025-11-17 desdoblamiento ORAU 0.0 6297.88 ``` All positions match every day except one, ORAU, from November 17: the statement has 374 more shares, exactly twice the 187 that were there. The ratio is 3.0000, the closing price goes from 369.01 to 124.15 (a ratio of 2.97 that combines the split and the day's move) and the cash statement has a `desdoblamiento` (split) line with no amount. It is a 3-for-1 stock split the system did not know about. The difference persists over the following days with the same size: the later ORAU orders were computed with the wrong position. The broker does not send a split as an execution, and a system that does not incorporate it miscomputes everything that depends on the position: the real weight of ORAU was three times what it believed, and the next order to bring it to target bought more than it should have. Data providers and brokers publish corporate actions in advance (lesson 1.4.3); the system must read them before the open and adjust position and reference prices. ## Cash: interest, fees and corrections The system's cash is the initial balance plus the amounts of the executions. Compared with the statement balance at the end of each day, with the executions corrected, the remaining differences are movements that are not trades: ```python imp = ex.groupby("fecha_operacion")["importe_neto"].sum() saldo_ext = efe.groupby("fecha")["saldo"].last() saldo_ops = 1e6 + imp.reindex(saldo_ext.index, fill_value=0).cumsum() resto = (saldo_ext - saldo_ops).round(2) saltos = resto.diff().fillna(resto).round(2) print(saltos[saltos != 0].to_dict()) otros = efe[~efe["concepto"].isin(["ingreso", "operaciones", "comisiones"])] print(otros.groupby("fecha")["importe"].sum().round(2).to_dict()) # the same with the executions from the EVENTS, uncorrected ev_imp = (ej_f.assign(imp=-ej_f["q"] * ej_f["precio_ultimo"] - ej_f["comision"]) .groupby("fecha")["imp"].sum()) ingenuo = 1e6 + ev_imp.reindex(saldo_ext.index, fill_value=0).cumsum() d_ing = (saldo_ext - ingenuo).round(2) print("uncorrected difference, at the end:", d_ing.iloc[-1], "| corrected:", resto.iloc[-1]) ``` ```output {'2025-10-31': 164.77, '2025-11-28': 42.07, '2025-12-31': 276.76} {'2025-10-31': 164.77, '2025-11-17': 0.0, '2025-11-28': 42.07, '2025-12-31': 276.76} uncorrected difference, at the end: -51947.56 | corrected: 483.6 ``` With the statement's executions, the cash only departs from the system on three days, the last three business days of each month: +164.77, +42.07 and +276.76 USD. They match the sum of the statement's `intereses` (interest) and `tasa_datos_mercado` (market data fee) lines (174.77 − 10, 52.07 − 10, 286.76 − 10): interest on cash and a market data fee that the broker credits and charges without sending any event. With the events' executions uncorrected, the final difference is −51,947.56 USD: the ORIG purchase the system did not see (−63,516.66 with its commission), the ORTN sale it did not see either (+11,094.00 net), the price correction on 170 ORRE (−8.50) and the interest and fees (+483.60). The header figure follows the two curves day by day: the one from the raw events jumps at each failure; the reconciled one only moves on the last day of each month, by amounts the statement explains. ## Orders versus signals What is left is not at the broker: it is the duplicate order from the beginning. Events and statement agree on it because both orders existed. It is only visible when comparing, for each signal, what was executed with what it asked for: ```python o = ords[ords["modo"] == "real"].copy() lleno = ej_f.groupby("id_orden_cliente")["q"].sum() # the two executions from the statement, to their order (by broker id) for _, f in solo_ext.iterrows(): cli = cliente_de[f["id_orden_broker"]] lleno[cli] = lleno.get(cli, 0) + (1 if f["lado"] == "compra" else -1) * f["cantidad"] o["ejecutado"] = lleno.reindex(o.index).fillna(0) por_senal = o.groupby("id_senal")["ejecutado"].sum() pide = (sen["posicion_objetivo"] - sen["posicion_actual"]) desv = (por_senal - pide.reindex(por_senal.index)).astype(int) for s, d in desv[desv != 0].items(): os_ = o[o["id_senal"] == s] print(s, sen.loc[s, "instrumento"], f"{d:+d}", os_.index.tolist(), os_["motivo"].tolist()) rech = real[real["evento"] == "rechazada"] print(rech[["id_orden_cliente", "motivo"]].to_string(index=False)) ``` ```output S00125 ORIG -1260 ['R00007'] ['rebalanceo'] S00215 ORUS -7 ['R00113', 'R00114'] ['rebalanceo', 'reenvio_sin_respuesta'] id_orden_cliente motivo R00007 poder de compra insuficiente ``` Two signals did not end up as requested. `S00125` executed nothing because its order was **rejected** for insufficient buying power: a real mismatch, but already explained and reported by the broker (it is one of the alerts in lesson 4.5.4). `S00215` has 7 ORUS sold too many, with two orders, the second one flagged as `reenvio_sin_respuesta` (resent without response): the duplicate order. Nothing at the broker was wrong; the error was in the OMS. ## All thirteen, one by one Putting all the checks together, the reconciliation produces a list of findings with their type, date and identifiers. The known answer in the data lets us check whether the list is complete and whether anything is extra: ```python fecha_seq = dict(zip(real["seq"], real["ts_broker"].str[:10])) h = [] for _, r in dup.iterrows(): h.append(("ejecucion_duplicada", fecha_seq[r["seq"]], r["id_orden_cliente"], r["id_ejecucion"])) for _, r in desorden.iterrows(): h.append(("fuera_de_orden", fecha_seq[r["seq"]], r["id_orden_cliente"], r["id_ejecucion"])) for _, r in solo_ext.iterrows(): cli = cliente_de[r["id_orden_broker"]] h.append(("falta_ejecucion", r["fecha_operacion"], cli, r["id_ejecucion"])) for _, r in otro_px.iterrows(): h.append(("correccion_precio", r["fecha_operacion"], r["id_orden_cliente"], r["id_ejecucion"])) h.append(("desdoblamiento", d0, None, None)) split_f = efe.loc[efe["concepto"] == "desdoblamiento", "fecha"].values for f in otros["fecha"].unique(): if f not in split_f: h.append(("tasas_e_intereses", f, None, None)) for s, d in desv[desv != 0].items(): os_ = o[o["id_senal"] == s] if (os_["motivo"] == "reenvio_sin_respuesta").any(): h.append(("orden_duplicada", os_["fecha"].iloc[0], os_.index[-1], None)) hall = pd.DataFrame(h, columns=["tipo", "fecha", "id_orden_cliente", "id_ejecucion"]) verdad = pd.read_csv("vivo4_anomalias_verdad.csv") claves = ["tipo", "fecha", "id_orden_cliente", "id_ejecucion"] cruce = verdad.merge(hall, on=claves, how="outer", indicator=True) print(cruce["_merge"].value_counts().to_dict()) print(cruce.sort_values("fecha")[["id_anomalia", "tipo", "fecha", "id_orden_cliente"]].to_string(index=False)) ``` ```output {'both': 13, 'left_only': 0, 'right_only': 0} id_anomalia tipo fecha id_orden_cliente A02 ejecucion_duplicada 2025-10-21 R00024 A01 ejecucion_duplicada 2025-10-21 R00030 A03 tasas_e_intereses 2025-10-31 NaN A04 falta_ejecucion 2025-11-03 R00079 A05 orden_duplicada 2025-11-13 R00114 A06 desdoblamiento 2025-11-17 NaN A07 tasas_e_intereses 2025-11-28 NaN A08 correccion_precio 2025-12-03 R00166 A09 fuera_de_orden 2025-12-03 R00162 A10 ejecucion_duplicada 2025-12-09 R00195 A11 ejecucion_duplicada 2025-12-09 R00198 A12 falta_ejecucion 2025-12-15 R00214 A13 tasas_e_intereses 2025-12-31 NaN ``` *Fig. — Where each of the 13 anomalies in the live account shows up: in the broker message stream, when reconciling executions, positions and cash against the statement, or when comparing what was executed with the signals.* Matrix of seven anomaly types by five checks. Duplicates and out-of-order messages only show in the stream; missing executions in the stream (a gap) and in fills, positions and cash; the duplicate order only when comparing with the signals. Highlighted, the price correction, the stock split and the interest and fees, 5 of the 13, which only show against the statement. Thirteen findings and thirteen planted anomalies, and all thirteen match by type, date, order and execution: none is missing and none is extra. The figure sorts them by the check that uncovers them: five (the price correction, the split and the three month-ends with interest and fees) leave no trace in the message stream and only show up against the statement, and the duplicate order only shows up against the signals. The rejection of `S00125` does not appear because the duplicate-order rule requires a resend; a rejection is reported by the broker and goes another way (the alert). That the list matches the truth depends on the data being built for it; in a real account, the reconciliation gives a list and someone has to decide what each item is. What can be automated is the classification of known signatures, which is what the cell does. ## What to do with each difference Finding the difference is half the job. The other half is deciding, for each type, whether the system fixes it on its own, fixes it and alerts, or stops: | Finding | Correction | Can it keep trading? | |---|---|---| | duplicate execution | ignore it (primary key) | yes, without alerting | | out-of-order by a few ms | sort by `seq` before the state machine | yes | | execution that goes backwards | sort by `seq`; log it | yes, with an alert | | gap in `seq` | request the messages from the broker or take the execution from the statement | only after incorporating it | | price correction | adopt the statement price | yes, with an alert | | interest and fees | record them as cash movements | yes | | corporate action | adjust position and reference prices | **no** until adjusted | | duplicate order | fix the OMS; decide whether to unwind the position | **no** until reviewed | | unexplained difference | none automatic | **no** | The rule in the last row is the most important: if on startup there is a difference left that fits no known signature, the system does not trade. Trading on a false state propagates the error (what happened with ORAU for two weeks). The startup of a live system is, therefore, a small state machine: ```mermaid stateDiagram-v2 [*] --> LOADING LOADING --> RECONCILING RECONCILING --> TRADING RECONCILING --> CORRECTING CORRECTING --> RECONCILING RECONCILING --> BLOCKED BLOCKED --> RECONCILING TRADING --> RECONCILING ``` `LOADING` reads the state from disk; `RECONCILING` asks the broker for open orders, executions and positions (and, if there is a new statement, compares it); with everything explained it moves to `TRADING`; with differences of a known signature, to `CORRECTING`, which applies the correction and reconciles again; with something unexplained, to `BLOCKED`, which sends a critical alert (lesson 4.5.4) and waits for a person. From `TRADING` it reconciles again after every reconnection and every morning. It is the same principle as the kill switch in lesson 4.2.5, applied to state instead of to results. NautilusTrader performs this step when starting its live node, as lesson 4.5.6 will show. ## Exercises 1. In `aplicar`, replace the execution key with an `INSERT` without `OR IGNORE` and process all the events of the live account again with no crashes. What happens to the duplicates? Why is it preferable for it to fail rather than count them twice? **Solution.** ```python def aplicar_estricto(con, e): con.execute("BEGIN IMMEDIATE") try: con.execute("INSERT INTO eventos VALUES (?, ?, ?, ?)", (int(e.seq), e.id_orden_cliente, e.evento, None)) con.execute("COMMIT") return "ok" except sqlite3.IntegrityError as err: con.execute("ROLLBACK") return f"seq {e.seq}: {err}" limpiar("estricto.db") con = abrir("estricto.db") r = [aplicar_estricto(con, e) for e in real.itertuples()] con.close() print([x for x in r if x != "ok"]) ``` ```output ['seq 61: UNIQUE constraint failed: eventos.seq', 'seq 62: UNIQUE constraint failed: eventos.seq', 'seq 471: UNIQUE constraint failed: eventos.seq', 'seq 475: UNIQUE constraint failed: eventos.seq'] ``` The four duplicates collide with the primary key and the database rejects them with `IntegrityError`; the transaction is rolled back and the state is left intact. With `OR IGNORE` the result is the same but silent. Both options are safe, because the database guarantees the duplicate is not applied; the dangerous thing would be having no key, like the naive OMS. Counting the rejections has an advantage: an increase in duplicates is a symptom (a broker that resends too much, a client that reconnects in a loop) worth seeing in the daily digest. 2. Write a function that, given the table of daily positions from the statement and the system's, detects stock splits without looking at the cash statement: a difference that appears on a day with no executions in the instrument and whose ratio to the system's position is an integer or the inverse of an integer. **Solution.** ```python def desdoblamientos(pos_ext, pos_sis, ejecs): out = [] for ins in pos_ext.columns: d = pos_ext[ins] - pos_sis.get(ins, 0) nuevos = d[(d != 0) & (d.shift(fill_value=0) == 0)] for fecha in nuevos.index: hubo = ((ejecs["instrumento"] == ins) & (ejecs["fecha"] == fecha)).any() antes = pos_sis.loc[fecha, ins] if hubo or antes == 0: continue r = pos_ext.loc[fecha, ins] / antes for k in (r, 1 / r): if abs(k - round(k)) < 1e-9 and round(k) > 1: out.append((ins, fecha, f"{r:.4g}")) return out desdoblamientos(pos_ext, pos_sis, todas) ``` ```output [('ORAU', '2025-11-17', '3')] ``` It detects the ORAU split on November 17 with a ratio of 3. The condition that there are no executions that day matters: if the system had traded ORAU on the same day, the ratio would no longer be exact and you would have to compare with the position before trading. A reverse split would give a ratio of 1/k, which the function also recognizes. 3. A colleague proposes not reconciling with the statement and relying on the events, because “the broker always sends everything”. With the data in this lesson, what would you tell them? **Solution.** That in four months of a 1,000,000 USD account the broker did not send as events two executions (112 ORTN and 557 ORIG, some 74,600 USD of trades), a price correction, a 3-for-1 stock split and six interest and fee movements. Without reconciling, the system would have traded for two weeks with a third of its real ORAU position and, from December 15, with 557 ORIG it did not know it had. Events are for reacting quickly; the statement, for knowing the truth. A live system needs both: the events during the session and the reconciliation every morning and at every startup. [^1]: Alpaca documentation, `POST /v2/orders`: `client_order_id`, “A unique identifier for the order. Automatically generated if not sent. (<= 128 characters)”. The page does not document the response to a repeated key. Accessed October 22, 2026. [^2]: SQLite, “Atomic Commit In SQLite” (https://www.sqlite.org/atomiccommit.html) and “Write-Ahead Logging” (https://www.sqlite.org/wal.html): a committed transaction survives a crash of the process or the system, and an uncommitted one is rolled back when the database is opened again. ## Self-assessment 1. The order manager assigns each order a random UUID when sending it and keeps it in memory. It sends an order, the process dies before receiving the acknowledgment, and on startup it resends the pending signal. What does the UUID protect? - A. Nothing: the new process generates another UUID, and for the broker it is a different order - B. Everything: the broker rejects the duplicate - C. Only if the broker is Interactive Brokers - D. Only limit orders *Answer A: Nothing: the new process generates another UUID, and for the broker it is a different order.* The key only helps if the retry carries the same one. A UUID created when sending and kept in memory dies with the process. The key must come from something that survives (the signal: account, date and identifier) or be saved to disk before sending. 2. The manager updates the position, does a COMMIT and then, in another transaction, marks the event as processed. The process dies between the two. What happens on startup? - A. Nothing: the position is already correct - B. SQLite rolls back the first transaction - C. The manager refuses to start - D. The broker resends the event, the manager does not see it marked and applies it again: the position counts the execution twice *Answer D: The broker resends the event, the manager does not see it marked and applies it again: the position counts the execution twice.* What was committed survives the failure, and what was not is lost. If the position and the processed-event mark go in separate transactions, there is a window between them in which the state is inconsistent. In the lesson, that crash counts a sale of 923 ORIG twice and leaves 105,197.23 USD in the cash balance that do not exist. 3. When rebuilding the live account from the events and comparing it with the statement, ORAU has 561 shares in the statement and 187 in the system on November 17, with no execution in between. What is most likely? - A. A missed execution of 374 shares - B. An error in the statement - C. A 3-for-1 stock split: the statement reflects it (and its closing price is divided by 3) and the system does not know about it - D. A duplicate order *Answer C: A 3-for-1 stock split: the statement reflects it (and its closing price is divided by 3) and the system does not know about it.* The exact ratio 561/187 = 3, the price divided by 3 and a `desdoblamiento` (split) line with no amount in the cash activity are the signature of a corporate action. A missed execution would show up as a trade line in the statement with no event. 4. There is a gap in the broker's sequence (202 is missing). What does the lesson's manager do? - A. Makes up the missing event - B. Records it as pending and looks for it in the reconciliation: the statement has execution E710000553.01, which never arrived; it is taken from there and an alert is raised - C. Ignores the gap: if it mattered, the broker would resend it - D. Stops the system for good *Answer B: Records it as pending and looks for it in the reconciliation: the statement has execution E710000553.01, which never arrived; it is taken from there and an alert is raised.* A gap says that something is missing, not what. The broker statement is the accounting truth: the partial fill that was not resent is there. The system's position was wrong from that day until it was reconciled. 5. The reconciliation finds 7 ORUS too few compared with the target position of signal S00215, and the statement matches the events. What happened? - A. The order was sent twice (R00113 and R00114) without a common key and the broker executed both - B. A rounding error - C. The broker did not execute the order - D. A stock split *Answer A: The order was sent twice (R00113 and R00114) without a common key and the broker executed both.* Events and statement agree because both orders really exist. The mismatch only shows up when comparing what was executed with what the signal wanted: R00114 is a resend after a disconnection without an idempotency key. That is why reconciliation has to reach all the way to the intent, not stop at the broker. ## Key points - A network and a process fail at the worst moment: between sending and receiving the acknowledgment, between executing and saving. Each window is closed with a concrete rule: idempotency keys, transactions and reconciliation. - The idempotency key must come from the intent (account, date, signal) or be saved before sending: that way the retry, including one from a new process, carries the same key. - State on disk and one event per transaction, with the execution identifier as the primary key: duplicates are ignored automatically and a crash halfway leaves nothing half done. - The broker statement is the accounting truth. Reconciling executions, positions, cash and orders against it finds what the event stream does not tell you: missed executions, price corrections, stock splits, interest and fees. - In the level 4 data, the reconciliation recovers the 13 planted anomalies with no false positives; the only additional mismatch, a rejection, had already been explained by the broker itself. - On startup, the system reconciles before trading and blocks if anything remains unexplained: trading on a false state is worse than not trading. ## Resources ### Research - [Finite-State Machines for Quantitative Trading Execution in Python](https://pythonparatrading.com/en/finite-state-machines-trading-python/) · Research-0029 · 47 min ### Documentation - [SQLite: Atomic Commit (how a transaction survives a crash)](https://www.sqlite.org/atomiccommit.html) · sqlite.org - [SQLite: Write-Ahead Logging](https://www.sqlite.org/wal.html) · sqlite.org - [Python: sqlite3, transaction control](https://docs.python.org/3/library/sqlite3.html) · docs.python.org - [Alpaca: create an order (client_order_id)](https://docs.alpaca.markets/reference/postorder) · docs.alpaca.markets ### Data - [Level 4 data: broker events of the live system (CSV)](https://pythonparatrading.com/curso/datos/nivel-4/vivo4_eventos_broker.csv) · Download · vivo4_eventos_broker.csv - [Level 4 data: trade statement of the live account (CSV)](https://pythonparatrading.com/curso/datos/nivel-4/vivo4_extracto_operaciones.csv) · Download · vivo4_extracto_operaciones.csv Previous lesson: [4.5.4 · Task scheduling and alerts: cron, APScheduler, Prefect, Telegram, Slack and email](https://pythonparatrading.com/en/python-trading-course/level-4/task-scheduling-and-trading-alerts/) Next lesson: [4.5.6 · NautilusTrader or LEAN live: from backtest to production with the same code](https://pythonparatrading.com/en/python-trading-course/level-4/nautilustrader-and-lean-live-trading/)