Keeping the Dashboard Current

When the connection to the IBKR TWS API drops — and it will — the Excel dashboard has to put itself back together. A reconnection story in four acts.

The promise

The first article in this series (Beyond Market Data) ended on the rule the whole system turns on: when you look at a cell, you have to know whether the number is live or stale. The hardest place to enforce that rule is the moment the connection to TWS drops (and it will).

TWS connections don't drop just because something is wrong. They can drop because everything is normal. The network blips. TWS performs its daily restart. None of it is remarkable. What matters is what the dashboard does on the far side of a drop: show the trader, plainly, that the feed is down ... and then put itself back together — every subscription that was live before live again, without anyone touching the workbook.

That sounds like a feature. It turned out to be the hardest single piece of engineering in the system, and the story of how I learned that is the story of one bug I fixed four times before it was actually fixed.

Act one: the orphans

The first version of reconnection was the obvious one. When the connection comes back, walk the list of market-data requests and re-issue the ones that had failed. It worked in testing. In production, a handful of cells would come back from every reconnect stuck at #N/A.

The reason was buried in how I'd decided which requests to resubscribe. I only re-issued the ones carrying an error code from the drop. But the requests made during the original connection — before anything had gone wrong — had no error code. They looked healthy. So on reconnect they were skipped, and orphaned: TWS had dropped them, and my code had decided they didn't need reminding. The fix was nearly a one-liner — re-issue a reqMktData for everything, not just the requests that had errored.

That fix created the second bug.

Act two: the collection that changed underfoot

Re-issuing everything meant iterating the whole collection of requests. Reconnection runs on a background thread, while Excel — on its own thread — can call in at any moment to add a subscription. One afternoon the two collided: Excel added a request while the reconnect loop was halfway through enumerating, and .NET did what .NET does — threw InvalidOperationException, "Collection was modified," in the middle of resubscribing. The log filled with duplicate-ticker errors. The fix was to take a lock around the resubscribe, so the collection couldn't change underneath it.

That fix created the third bug.

Act three: the deadlock

The lock turned out to be reachable a second time, deeper in the same call: resubscribing constructed a new request object, and that construction reached for the lock again. In testing, survivable. In production it met Excel: the RTD thread holds the lock from one side; the reconnection thread blocks waiting for it from the other; and the Excel thread is itself waiting on the very object the reconnection thread is trying to build. Nobody moves. The server can't reach TWS at all until Excel is restarted — every cell showing #N/A except the one reporting IsConnected = FALSE, while the heartbeat ticks placidly upward, telling the trader the server is alive as it sits there unable to connect.

The fix let subscriptions be created while the connection was down — queued for the next reconnect — instead of being constructed inside the locked path.

That fix created the fourth bug.

Act four: cancelling what never existed

Now cells could subscribe while TWS was offline. Good — except that on reconnect, the resubscribe logic tidied up first, cancelling the old subscription before re-issuing it. And for a request created while offline, "the old subscription" was one TWS had never seen. A cancelMktData on a request id TWS never received doesn't fail anywhere you can see — I never found an error in any callback or log. It does something worse: it poisons that id, so the next reqMktData on it — the real one — is silently ignored. Sixteen specific tickers, stuck at #N/A forever, while every log line insisted the resubscribe had succeeded.

What the four acts were actually telling me

Four bugs, four fixes, each one nested inside the last — every fix the direct cause of the next bug. It would be easy to file that under "concurrency is hard" and move on. Two of the four were ordinary concurrency mistakes, mine and unremarkable: the unlocked iteration, then the lock that deadlocked. But the bookends — the orphaned requests, the poisoned cancel — were something else, and the same something turned up again when the account and position paths broke the same way: calling a TWS API method whose meaning depended on TWS being in a state it wasn't in. Re-issuing a request TWS still held. Cancelling a subscription TWS never got. And in every case TWS's response was identical: it didn't refuse. It accepted the nonsense quietly and failed somewhere downstream, out of sight of the call that caused it.

That is the lesson of the whole saga, and it has nothing to do with Excel: TWS will not tell you when your picture of its state is wrong. It assumes you are keeping an accurate model of what it has accepted, what it's streaming, and what it has forgotten — and when your model drifts, you don't get an exception; you get silence ... and confusion. A Python service or a Java OMS against the same API faces the same class of problem — different symptoms, the same silence underneath. Excel just makes the failure loud and immediate: the drift shows up as a wall of #N/A under the trader's eye instead of a wrong number in a log nobody reads. And it bites a one-person setup hardest — there's no second screen, no colleague on the desk, to catch the frozen number before you trade on it.

TWS has states the documentation doesn't describe

The four acts were one face of that silence. There are others, and they're worth knowing if you ever point your own code at the API.

The connection can drop without telling you. TWS stops sending, the socket still looks open, and a naive client sits there content — connected, by every signal it bothers to check, to a feed that quietly died ten minutes ago. The defense is to stop trusting the socket and start watching for the heartbeat that should be arriving; when it doesn't, tear down the connection down and rebuild it.

Then there's the one I still can't fully explain. Now and then, on reconnect, TWS rejects a brand-new client ID and keeps rejecting it — long enough that I once watched a paper-trading session burn five minutes and fifteen seconds, across nineteen silent retries, before it caught. TWS won't even write to its own API log until the handshake completes, so you debug it blind. The effective fix in this case is stupidly simple: stop retrying with the same client ID; switch to a different one and it connects at once. I don't have a tidy theory for why that works, but I know it does because I went looking (which is its own discipline). When TWS does something the docs don't cover, I write a throwaway probe — a few lines that do nothing but reproduce the behavior — and characterize exactly what's happening before I write a word of the fix.

How it's actually held together

Strip away the war stories and the reconnection engine comes down to a handful of rules, most of them scar tissue from the four acts:

• Register every subscription the moment it's made. The record of what should be live is kept by my code, not inferred from TWS — because the two drift, and mine has to be the source of truth.
• On a drop, fail loud. Connection-dependent cells go to #N/A at once — the Fail Loud rule from the first article, at its hardest moment. Nothing keeps showing a last-good price as though the feed were still there.
• Reconnect on a leash. Attempts back off and are throttled, so a flapping connection can't escalate into a retry storm that gets you rate-limited.
• Don't resubscribe into a half-open door. The subtle one. After the socket reconnects, the engine waits for TWS's own confirmation that it has fully accepted the session before re-issuing anything. Resubscribe too early — connection technically up but not yet ready — and the requests are silently dropped, and you're back to a screenful of #N/A that every log says shouldn't be there.
• Seed from cache. A newly added cell — five minutes into a session, or just after a reconnect — shows its last known value immediately instead of waiting for the next tick.

The fix under the fixes

You'll have noticed the shape of the four acts: every fix bolted one more check onto a connection lifecycle tracked by a scatter of independent boolean flags. That structure was the bug. Eventually I stopped patching it and replaced the whole thing with a single state machine, in which the only way to reach the "connected" state is through one function that confirms — under a lock — that the socket is genuinely alive at that instant. Two separate audit passes through the connection code had turned up seventeen distinct race windows between them. The rebuild closed all seventeen in a day — not by visiting seventeen sites, but by making the states that allowed them impossible to enter.

The trader sees none of it. The feed drops, a few cells flash #N/A, the status cell says so out loud, and a moment later everything is live again — and nobody has to know how much had to go right underneath.

Next — the host fighting back: the connection isn't the only thing that fights you. Excel keeps an undocumented RTD contract of its own, and at production scale it quietly stops cooperating at exactly the wrong moment. Part 3 is about noticing before the trader does.