4. Reconciliation: squaring your books with the broker's

Level 10: Knowing what is going on

You already know the broker is the source of truth. That settles who wins an argument, but it does not solve the real problem: finding out that there is an argument. Reconciliation is the daily process that compares your books with theirs, and it is the control that saves the most money in the whole level, because it is the only one that detects the failures that produce neither errors nor alerts.

Reconciling means comparing three things, not one

Every day, after the close and before the system can trade again, three blocks are compared against what the broker declares:

Positions: shares by symbol, with sign. Here the tolerance is zero.
Cash: the available balance. Here there is a minimal tolerance for rounding.
The day's trades: the list of fills, matched by identifier. The same ones must appear on both sides.

Comparing only one of the three gives a false sense of control. Cash can square while shares do not, for example after a split the broker adjusted and your database did not. And positions can square while cash does not, because you received a dividend your accounting does not model.

from decimal import Decimal

TOLERANCIA_ACCIONES = Decimal("0")
TOLERANCIA_EFECTIVO = Decimal("0.05")

def dec(x):
    return Decimal(str(x))

def reconciliar_posiciones(locales, del_broker):
    simbolos = set(locales) | set(del_broker)
    diferencias = []
    for s in sorted(simbolos):
        mia = dec(locales.get(s, 0))
        suya = dec(del_broker.get(s, 0))
        if abs(mia - suya) > TOLERANCIA_ACCIONES:
            diferencias.append({"simbolo": s, "local": mia,
                                "broker": suya, "delta": mia - suya})
    return diferencias

Use Decimal, not floating point: a discrepancy of pennies that appears and disappears depending on the day is indistinguishable from a real one and ends up teaching you to ignore the process.

Where the differences come from

Almost every difference you will see falls into six families, and it is worth recognizing them at a glance:

Partial fills your code counted as complete.
Commissions, fees and taxes the broker deducts and your accounting does not model.
Dividends and coupons paid in cash, which move the balance with no trade of yours.
Splits and corporate actions, which change the number of shares without you doing anything.
Manual trades by the user from the broker's app: the most frequent cause and the hardest to imagine from the code.
Orders you thought were canceled that were filled between your cancellation request and its processing.

Note that only the last two are failures of yours; the first four are legitimate real-world facts your accounting did not know about. That is why reconciliation is not only for catching software bugs: it is for discovering everything that happens to your money that your code does not yet model. Every explained difference ends up turned into a new rule, and over the months the process goes quiet.

Tolerances: one single figure will not do for everything

The share tolerance is zero and is not negotiable: half a share of difference does not exist in nature except in fractional accounts, and if your broker allows those, the tolerance is still the minimum the broker accepts. Cash is different, because commission rounding does produce legitimate pennies, but it has to be set low enough that a real error does not fit inside it. A generous tolerance is the most elegant way of never finding out about anything, and it has the unpleasant property of hiding errors better the smaller the account you start trading with.

The complete check

def reconciliar_dia(estado_local, cliente, fecha):
    dif_pos = reconciliar_posiciones(estado_local.posiciones, cliente.posiciones())
    dif_efectivo = dec(estado_local.efectivo) - dec(cliente.efectivo())

    mias = {o["id_cliente"] for o in estado_local.operaciones_de(fecha)}
    suyas = {o["id_cliente"] for o in cliente.operaciones(fecha)}
    solo_mias = mias - suyas
    solo_suyas = suyas - mias

    cuadra = (not dif_pos
              and abs(dif_efectivo) <= TOLERANCIA_EFECTIVO
              and not solo_mias and not solo_suyas)

    registrar(logging.INFO if cuadra else logging.ERROR, "reconciliacion",
              fecha=fecha, cuadra=cuadra, posiciones_dif=len(dif_pos),
              delta_efectivo=str(dif_efectivo),
              solo_mias=sorted(solo_mias), solo_suyas=sorted(solo_suyas))
    return cuadra, dif_pos, dif_efectivo, solo_mias, solo_suyas

def cerrar_dia(estado_local, cliente, fecha):
    cuadra, dif_pos, dif_efectivo, solo_mias, solo_suyas = reconciliar_dia(
        estado_local, cliente, fecha)
    if cuadra:
        M.contar("reconciliaciones_ok")
        return True
    estado_local.bloquear("descuadre:" + fecha)
    alertar("critico", "descuadre",
            "reconciliacion de %s no cuadra: %d posiciones, efectivo %s"
            % (fecha, len(dif_pos), dif_efectivo))
    return False

On finding a difference: stop and raise the alarm, never fix in silence

The temptation is enormous: the broker says 40 and you have 26, the broker rules, I write 40 and tomorrow we start square. Never do that. Copying the number covers up the fact that produced the difference, and that fact is almost always still active: if an order was duplicated by a badly built retry, tomorrow it will duplicate again, with more money and with nobody noticing, because the process that was supposed to warn has learned to adjust on its own.

The correct behavior has three steps, in this order: block trading for the following day, issue a critical alert, and use module one's log to reconstruct the complete chain of the order involved until you find the cause. The adjustment comes afterwards, by hand, with the explanation written beside it.

And watch out for a frequent design trap: writing only the happy branch. A process that marks the reconciliation as correct when it squares and does nothing when it does not looks like it works for months. And if on top of that nobody watches for the absence of that mark, with what you learned in module three, the discrepancy is invisible.

Worked case: two fills where there should have been one

A scenario invented for teaching purposes. One Thursday reconciliation returns: positions with a difference of 26 shares in one name, local cash 4,938 euros above the real figure, and a fill identifier that appears at the broker and not in your books. All three clues point to the same place: it was bought twice. The log confirms that the second send carried a new client identifier, so the broker treated it as a different order. Cost of the failure: the unwanted position plus the slippage of unwinding it. Cost of not reconciling: the same error repeating every week with nobody seeing it.