The Messy Business of Building a Futures Security Master

Signal data

The cur­rent strate­gies gen­er­ate sig­nals from dai­ly prices for futures (Con­tract­Type col­umn). Han­dling stocks and ETFs is already sup­port­ed but will be the top­ic of anoth­er arti­cle.

The sys­tem stitch­es his­to­ries for indi­vid­ual futures con­tracts into back-adjust­ed series for sig­nal cal­cu­la­tions and back­tests.

CSI and Nor­gate togeth­er cov­er more mar­kets than either pro­vides on its own. Both have pro­vid­ed excel­lent sup­port and cor­rect­ed data after I report­ed incon­sis­ten­cies. That is a good rea­son to build your own qual­i­ty checks, even when you are pay­ing some­one else to do theirs.

The Provider col­umn selects the extrac­tor used to load an instrument’s dai­ly data. One provider may have sev­er­al extrac­tors, such as Nor­gate and Nor­gate-Delist­ed. The lat­ter runs only when rebuild­ing the his­to­ry from scratch.

Delist­ed mar­kets have no new dai­ly prices, so their his­to­ries can sit out the dai­ly down­load.

The Provider­Sym­bol col­umn records the instrument’s sym­bol at the select­ed provider. When his­tor­i­cal data is unavail­able, it con­tains NA and the miss­ing cov­er­age is doc­u­ment­ed in Com­ment. That makes the gap explic­it rather than leav­ing an emp­ty cell for the read­er, or the code, to inter­pret. Start­Date records the date the sys­tem has his­toric data for an instru­ment.

Bid/ask data

Bid and ask quotes allow the sys­tem to esti­mate spread costs and mea­sure slip­page by com­par­ing exe­cu­tion prices with the quotes pre­vail­ing imme­di­ate­ly before order place­ment.

While delayed bid/ask data at rel­a­tive­ly coarse inter­vals is usu­al­ly avail­able free of charge from the bro­ker, real-time feeds gen­er­al­ly require a paid sub­scrip­tion.

To keep costs down, I esti­mate spread costs using free bro­ker data wher­ev­er pos­si­ble. To mea­sure slip­page in live trad­ing, I use real-time feeds from exchanges with afford­able sub­scrip­tions. For exchanges with more expen­sive real-time feeds, such as CME and ICE, I use Data­ben­to instead.

Data­ben­to pro­vides his­tor­i­cal trade and quote data. I use the record­ed order-place­ment time­stamp to retrieve the bid and ask quotes imme­di­ate­ly before sub­mis­sion, then com­pare them with the exe­cu­tion price.

The Intra­dayProvider col­umn spec­i­fies the provider used to mea­sure slip­page, while Intra­dayI­den­ti­fi­er iden­ti­fies the instru­ment at that provider.

For exchanges that do not have a con­fig­ured provider, spread costs can only be esti­mat­ed using delayed data.

If you are start­ing out, you do not need a ded­i­cat­ed quote-data feed. An ini­tial spread esti­mate can also be obtained from observ­ing quotes in your broker’s trad­ing plat­form. With a hand­ful of mar­kets, that is man­age­able. Across a large uni­verse, you have qui­et­ly acquired a sec­ond job.

Piecing together the past

The “back­fill” step uses rows from the spread­sheet to sup­ply the price his­to­ry for anoth­er instru­ment. They remain use­ful for back­test­ing, but do not rep­re­sent addi­tion­al instru­ments to trade.

Take 🍫️COCOA: the CSI his­to­ry used here starts in June 1975, while the stored Nor­gate his­to­ry begins in Decem­ber 1979. Com­bin­ing them adds about four and a half years with­out requir­ing ongo­ing down­loads from both providers.

My CSI sub­scrip­tion allows 150 mar­kets to be updat­ed at a time, so each slot is valu­able. The old­er CSI prices are down­loaded once, archived under 🍫️COCOA_backfill, and read by the CSI-Delist­ed extrac­tor. That frees the CSI slot while Nor­gate sup­plies new data for COCOA.

The same approach works across con­tract sizes. The his­to­ry of 🇺🇸SP500_micro, for exam­ple, is extend­ed with data from the larg­er E‑mini con­tract, 🇺🇸SP500. That his­to­ry is extend­ed in turn with the orig­i­nal full-size con­tract, 🇺🇸SP500ORG. The chain reach­es back to 1982.

Some­times the larg­er con­tract rep­re­sents the entire his­to­ry: 🇨🇦TSE60_mini uses the stan­dard contract’s data because the providers do not cov­er the small­er ver­sion. These cas­es are marked “uses full size data” in the Com­ment col­umn and are only pos­si­ble if small-size vari­ants of the con­tract are always liq­uid enough.

Where the small­er con­tract is suf­fi­cient­ly liq­uid, using the larg­er contract’s data can also save a provider slot, as with 🌽️CORN-W_mi­cro. Fig­ure 3 shows how much his­to­ry this recov­ers in three exam­ples.

“Wait a minute,” you might say. “The S&P 500 Micro did not exist in 1982. How can it appear in a back­test that goes back that far?”

For the years before the Micro exist­ed, the back­test uses the old­er S&P 500 contract’s price his­to­ry and cal­cu­lates posi­tions using the Micro’s small­er con­tract size. This recon­structs how a small­er posi­tion would have behaved over that peri­od.

The full-size, E‑mini and Micro con­tracts there­fore have very sim­i­lar his­to­ries. My sys­tem groups them when allo­cat­ing risk, so adding anoth­er con­tract size does not auto­mat­i­cal­ly give the S&P 500 more weight in the port­fo­lio. Small­er con­tracts still serve a pur­pose: they allow posi­tions to be adjust­ed in small­er steps.

I will return to the details of risk allo­ca­tion in a sep­a­rate arti­cle on port­fo­lio con­struc­tion.

Recov­er­ing the his­to­ry of instru­ments is only half the job. The con­tracts need a clear han­dover, and their prices need to speak the same lan­guage.

Fig­ure 4 shows three ways of com­bin­ing price his­to­ries.

For COCOA, the back­fill Inac­tive­Con­tract is the Decem­ber 1981 con­tract tak­en from CSI. Lat­er con­tracts come from Nor­gate. The han­dover fol­lows con­tract expiry rather than a sin­gle cal­en­dar-date cut­off across all price series.

Price con­ven­tions also need to agree. CSI quotes 🌾WHEAT_micro in dol­lars, while the bro­ker quotes it in cents. There­fore, its prices are mul­ti­plied by 100 before they are com­bined with the old­er wheat con­tracts.

Oth­er migra­tions need a fixed off­set. After the mar­ket closed on April 26, 2024, Mon­tréal Exchange con­vert­ed the out­stand­ing BAX con­tracts expir­ing after June 2024 into three-month CORRA futures¹. In the work­book, these are 🇨🇦CAD3M and 🇨🇦CORRA3. The con­ver­sion spread was 32.138 basis points. Because these futures are quot­ed as 100 minus the inter­est rate, putting the old BAX prices on the CORRA basis requires adding 0.32138 price points. The back­fill applies that adjust­ment to the pre­de­ces­sor con­tracts before join­ing the his­to­ries.

If you are now tempt­ed to aban­don futures trad­ing alto­geth­er, I sym­pa­thize. You can lose months untan­gling this mess before you have test­ed a sin­gle trad­ing idea. With equi­ties, at least a good data provider takes the bur­den of splits and oth­er cor­po­rate actions off your shoul­ders. Nor­gate does this well.

And just when the his­to­ries seem com­plete, cur­ren­cies add anoth­er com­pli­ca­tion. The back­test needs exchange-rate his­to­ries to express non-dol­lar posi­tions and prof­its in US dol­lars. Some price his­to­ries, like 🇫🇷️CAC, use euros even for years before the euro exist­ed.

To cov­er those years, I had to recon­struct a euro proxy from the his­tor­i­cal cur­ren­cy bas­ket behind the Euro­pean Cur­ren­cy Unit (ECU).

Gone, but still in the backtest

An instru­ment can leave today’s trad­ing uni­verse and still be part of the back­test. Build­ing a back­test strict­ly from today’s sur­vivors is prob­lem­at­ic, as is easy to see in equi­ties: omit the com­pa­nies that went bank­rupt, and some expen­sive mis­takes dis­ap­pear from the back­test. That is called sur­vivor­ship bias, and keep­ing delist­ed instru­ments helps address it. The secu­ri­ty mas­ter must there­fore record both what can be trad­ed today and what was avail­able to the strat­e­gy in each ear­li­er peri­od.

Retired mar­kets also add data where it is most wel­come: in the ear­ly years, when far few­er instru­ments exist­ed. Keep­ing them broad­ens the set of mar­kets avail­able in those years and lets a strat­e­gy face more mar­ket con­di­tions. Relat­ed mar­kets still share risks, so every extra instru­ment is not an inde­pen­dent time series, but the broad­er sam­ple makes the back­test more rep­re­sen­ta­tive.

Futures mar­kets can dis­ap­pear for dif­fer­ent rea­sons. Hedg­ing needs change, liq­uid­i­ty dries up, or a replace­ment con­tract attracts more busi­ness. The com­mod­i­ty itself need not have become worth­less. Take 🐖PORKBELLIES, which had their moment of Hol­ly­wood fame in Trad­ing Places (1983)². CME delist­ed frozen pork bel­ly futures in July 2011³ after a pro­longed lack of trad­ing vol­ume. Bacon had not gone out of fash­ion, but trad­ing that par­tic­u­lar futures con­tract had.

Uncle Fred there­fore stamps the file “Retired” and puts it back in the cab­i­net. The trades it gen­er­at­ed, includ­ing the bad ones, still belong in the back­test. Fred may enjoy a tidy desk, but that is no excuse to put the losers through the shred­der.

A more recent exam­ple comes from my bro­ker. Inter­ac­tive Bro­kers will stop trad­ing its Lon­don Met­al Exchange looka­likes in Novem­ber 2026. These are over-the-counter con­tracts that ref­er­ence LME futures⁴, giv­ing access to met­als such as alu­minum, cop­per and tin. The under­ly­ing LME mar­kets con­tin­ue to exist, but this route to trad­ing them is clos­ing. Their his­to­ries remain use­ful even after they leave the live uni­verse. Here, “retired” describes their sta­tus in the sys­tem. It does not mean the exchange has delist­ed the under­ly­ing mar­ket.

The Inac­tive­Con­tract col­umn records where the retained con­tract his­to­ry ends. It con­tains a con­tract iden­ti­fi­er, rather than a cal­en­dar date. For 🟠️COPPER-LME, for exam­ple, 1763CAM-2026M iden­ti­fies the June 2026 con­tract. Lat­er con­tracts are exclud­ed when build­ing the his­to­ry, and an instru­ment with this field filled in is omit­ted from the live con­fig­u­ra­tion. That June cut­off is sep­a­rate from IB’s Novem­ber with­draw­al. The final sim­u­lat­ed trade also depends on the roll sched­ule and the data it needs.

The same col­umn serves two pur­pos­es. A back­fill entry sup­plies old­er con­tracts to anoth­er instru­ment, as in the cocoa exam­ple above. A retired instru­ment keeps its own his­tor­i­cal iden­ti­ty and can remain in the back­test.

Fig­ure 5 shows the 55 retired instru­ments with stored price his­to­ries. The retire­ment cab­i­net holds rather more than a few curiosi­ties.

Fig­ure 6 counts the his­to­ries avail­able in each year. Togeth­er, these retired instru­ments con­tribute ten his­to­ries in 1980 and 27 in 1990, reach­ing a peak of 39. The retire­ment cab­i­net is doing use­ful work long before many of today’s con­tracts enter the pic­ture.

Keep­ing a delist­ed instru­ment does not auto­mat­i­cal­ly make it eli­gi­ble every day in the his­toric back­test: The instru­ment still needs usable data and enough liq­uid­i­ty dur­ing the peri­od being test­ed. The aim is to include mar­kets that could real­is­ti­cal­ly have been trad­ed then, with the infor­ma­tion avail­able then.

One market, several contracts

Uncle Fred has bare­ly put his stamp down when the Aus­tralian dol­lar returns to his desk wear­ing a dif­fer­ent exchange badge.

“New tick­er” it announces. “More diver­si­fi­ca­tion”. Fred looks over his glass­es. “I have three stamps that say ‘Reject­ed’. That doesn’t make them three inde­pen­dent instru­ments.”

The same expo­sure can come in sev­er­al con­tract sizes or trade on com­pet­ing exchanges. The work­book excludes 🇦🇺AUD-ICE in favor of AUD, for exam­ple, and 🇪🇺ESTR-CME in favor of ESTR. In both cas­es, the Com­ment col­umn doc­u­ments a cheap­er, more liq­uid alter­na­tive. Fred points to it with the expres­sion of a man who sus­pects you have come all this way with­out read­ing the form.

Each can­di­date still deserves a prop­er exam­i­na­tion. Whether two con­tracts count as dupli­cates is a case-by-case deci­sion. I treat 🍫COCOA-LDN and COCOA as dis­tinct mar­kets: Lon­don cocoa is priced in ster­ling for deliv­ery in Europe, while New York cocoa is priced in US dol­lars for deliv­ery in the US⁵. Cur­ren­cy move­ments and local sup­ply-and-demand con­di­tions can there­fore dri­ve their prices apart. Even Fred accepts that “both involve choco­late” is an inad­e­quate basis for a rejec­tion.

For the back­test, anoth­er high­ly cor­re­lat­ed instru­ment may add no diver­si­fi­ca­tion at all. Pho­to­copy­ing the same wit­ness state­ment does not give you ten wit­ness­es. Count­ing each ver­sion as a sep­a­rate mar­ket can also give the under­ly­ing expo­sure more weight sim­ply because an exchange offers anoth­er way to pack­age it. Ten wrap­pers around the same mar­ket do not make a diver­si­fied port­fo­lio.

The addi­tion­al con­tract also needs a use­ful job. If the pre­ferred con­tract pro­vides the desired expo­sure more cheap­ly and with bet­ter liq­uid­i­ty, its rival may nev­er reach the port­fo­lio. Fred com­pares costs for equiv­a­lent expo­sure. A low­er com­mis­sion per con­tract is less impres­sive when you need ten of them. The Black­list flag records the exclu­sion, and Com­ment explains the deci­sion, sav­ing the reject­ed appli­cant from return­ing next year with a fresh hair­cut and the same spread.

Then 🇦🇺AUD_micro arrives. Fred checks the con­tract size: A$10,000, against AUD’s A$100,000. “You appear to have lost a zero.” In this case, that is the point. A small­er con­tract allows fin­er posi­tion siz­ing when posi­tions must be round­ed to whole con­tracts, espe­cial­ly in a small­er account. The S&P 500 vari­ants dis­cussed ear­li­er serve the same pur­pose. Fred puts down the rejec­tion stamp. “Approved. But your larg­er rel­a­tive has already claimed the diver­si­fi­ca­tion allowance.”

Cor­re­la­tion weight­ing pre­vents retained vari­ants from each receiv­ing the allo­ca­tion of an inde­pen­dent mar­ket. Port­fo­lio con­struc­tion then decides how to express the expo­sure. High cor­re­la­tion alone there­fore does not set­tle the appli­ca­tion: the “high­ly cor­re­lat­ed” step in Fig­ure 2 removes alter­na­tives that offer no com­pelling exe­cu­tion advan­tage, while the final “unique mar­kets” bar groups the retained size vari­ants.

Fig­ure 7 shows the ver­dict for one exam­ple: keep the stan­dard and micro con­tracts at CME, and exclude the ICE alter­na­tive. Three appli­ca­tions, two approvals, one AUD/USD mar­ket. Fred has earned a bis­cuit. He breaks it in half and briefly con­sid­ers record­ing two.

Broker access

Fred has approved the paper­work. Unfor­tu­nate­ly, the bro­ker has its own rejec­tion stamp, and unlike Fred, it does not stop work­ing at five.

An instru­ment can have a use­ful price his­to­ry and still be unavail­able to trade. The DoNot­Trade col­umn lets me keep that dis­tinc­tion in the sys­tem. With the flag set to 1, an instru­ment will still gen­er­ate a trad­ing sig­nal and receive a the­o­ret­i­cal posi­tion in the opti­mal port­fo­lio, where frac­tion­al con­tracts are allowed. Dur­ing port­fo­lio con­struc­tion, how­ev­er, its posi­tion lim­it is set to zero. It may con­tribute infor­ma­tion, but the result­ing port­fo­lio holds no con­tracts in it.

This assumes the instru­ment is not black­list­ed. With Black­list set to 1, its price his­to­ry is not loaded and no trad­ing sig­nal is gen­er­at­ed. The sys­tem still checks its bid/ask spreads if DoNot­Trade is 0. Fig­ure 8 com­pares these paths through the sys­tem.

I typ­i­cal­ly refrain from hav­ing too many instru­ments in the uni­verse that gen­er­ate sig­nals but are not trad­able because sev­er­al exper­i­ments have shown that when an instru­ment gen­er­ates a sig­nal but is not trad­able, the final port­fo­lio does not improve.

Some trad­abil­i­ty restric­tions arise from reg­u­la­tion. The work­book flags some US sec­tor futures such as 💻US-TECH because of restric­tions asso­ci­at­ed with sec­tion 871(m) of the US tax code with regard to non-US investors⁶.

Oth­er obsta­cles are more mun­dane. The bro­ker may not sup­port the prod­uct for the account, or may not make it avail­able for mar­gin trad­ing. Restric­tions can also be tem­po­rary. The bro­ker may tem­porar­i­ly block trades that open or increase a posi­tion. The mar­ket can remain open, with prices arriv­ing nor­mal­ly, while my route into it is blocked. DoNot­Trade is a blunt con­trol: it impos­es a zero posi­tion lim­it rather than rep­re­sent­ing every vari­a­tion of those rules. The Com­ment col­umn records the rea­son, and a tem­po­rary restric­tion needs revis­it­ing when the bro­ker changes its pol­i­cy.

There­fore, bro­ker access is an ongo­ing main­te­nance job. Fig­ure 9 shows the cur­rent process: the sys­tem auto­mat­i­cal­ly records whether the bro­ker allows each instru­ment to be trad­ed, but I update DoNot­Trade man­u­al­ly in the secu­ri­ty mas­ter. Once enough trad­abil­i­ty his­to­ry has accu­mu­lat­ed, I plan to feed it auto­mat­i­cal­ly into the back­test, with the option of man­u­al over­rides.

Anyone on the other side?

Fred has the broker’s approval. He calls out, “Next, please.” Noth­ing hap­pens. He tries again. For the first time in his career, the emp­ty wait­ing room is a prob­lem.

Mar­kets with vir­tu­al­ly no activ­i­ty usu­al­ly go on my Black­list. Thin­ly trad­ed mar­kets can stay in the dataset because liq­uid­i­ty changes. The eli­gi­bil­i­ty is reassessed each day with­in the sys­tem with its own flag IsTrad­able.

This dai­ly liq­uid­i­ty check has two parts. Aver­age dai­ly vol­ume over the pre­vi­ous 20 trad­ing days must exceed 50 con­tracts. Risk vol­ume must also exceed USD 100,000. This mea­sure mul­ti­plies aver­age con­tract vol­ume by the contract’s val­ue in US dol­lars and its esti­mat­ed annu­alised volatil­i­ty. The result puts dif­fer­ent­ly sized con­tracts on a com­mon risk scale.

Fig­ure 10 illus­trates these checks using 🇪🇺MSCIEU, an MSCI Europe equi­ty-index future. It remains eli­gi­ble through­out 2021, becomes inter­mit­tent as activ­i­ty weak­ens, spends much of the sec­ond half of 2024 out­side the trad­able uni­verse, and qual­i­fies for most of 2025. In 2026, it moves in and out again as the two liq­uid­i­ty mea­sures cross their thresh­olds.

IsTrad­able also checks min­i­mum volatil­i­ty and esti­mat­ed hold­ing costs. Those tests pass through­out this exam­ple. To avoid con­stant­ly switch­ing eli­gi­bil­i­ty on and off, a pass­ing result is car­ried for­ward for up to 30 trad­ing days. The flag can there­fore remain 1 after liq­uid­i­ty falls below a thresh­old. An eli­gi­ble instru­ment can thus be includ­ed in the live port­fo­lio.

The mar­ket can qual­i­fy again when activ­i­ty returns. At the end of the chart, both liq­uid­i­ty mea­sures have slipped below their thresh­olds, but the flag remains 1 because the last pass­ing obser­va­tion is still inside the 30-day grace peri­od. Fred approves of the dai­ly review. An appli­ca­tion that can be reopened every morn­ing is prac­ti­cal­ly a pen­sion plan.

Cost configuration

Trad­ing involves two main vari­able costs: bro­ker com­mis­sions and bid–ask spreads.

These costs arise when­ev­er a posi­tion is opened or closed, includ­ing when it is rolled from one con­tract expiry to the next. Depend­ing on the rolling method, this may involve clos­ing the exist­ing posi­tion and open­ing a new one, or trad­ing the cal­en­dar spread direct­ly.

The tim­ing and method of each roll can mate­ri­al­ly affect the trad­ing system’s total costs. Automat­ing this process prop­er­ly is a top­ic in its own right.

In brief, PriceRoll­Cy­cle defines the con­tracts used for each instru­ment, while Hol­dRoll­Cy­cle spec­i­fies the expiries that the sys­tem may hold. RollOff­set­Days sets how many trad­ing days before expiry a roll may occur, while Car­ry­Off­set deter­mines whether the sys­tem cal­cu­lates an instrument’s car­ry using the next or the pre­vi­ous con­tract.

The work­book also spec­i­fies a Spread­Cost for each instru­ment. Expressed in the con­tract cur­ren­cy, this val­ue rep­re­sents half of the instrument’s typ­i­cal bid–ask spread. The sys­tem uses it as an ini­tial esti­mate when reli­able his­tor­i­cal spread data are not yet avail­able. Once enough obser­va­tions and actu­al fills have been col­lect­ed to esti­mate the spread cost, the field is no longer used.

You may have noticed that the work­book does not spec­i­fy bro­ker fees for each instru­ment. My sys­tem retrieves the cur­rent com­mis­sion from the bro­ker auto­mat­i­cal­ly and main­tains a his­to­ry of each instrument’s fees.

Fig­ure 11 applies the dai­ly cost fil­ter to five live instru­ments and the black­list­ed AEX Mini. For each instru­ment, it adds the one-way com­mis­sion to the esti­mat­ed half-spread, divides that cost by the cur­rent annu­alised risk of one con­tract, and then mul­ti­plies the result by the num­ber of sched­uled rolls per year. This com­mon scale reveals what dol­lar fees can hide: the Nas­daq Nano has a low­er dol­lar fee than its larg­er rel­a­tives, but a much high­er cost rel­a­tive to the risk it car­ries. Sim­i­lar­ly, month­ly rolls can push an appar­ent­ly mod­est trad­ing cost above the screen­ing thresh­old.

As Fig­ure 11 shows, the AEX Mini exceeds the 10% ceil­ing and is there­fore marked “too expen­sive” in the work­book. Pass­ing this cost screen does not by itself make an instru­ment trad­able: the liq­uid­i­ty and volatil­i­ty checks apply inde­pen­dent­ly, and each strat­e­gy also decides which instru­ments it can trade based part­ly on its turnover.

Com­mis­sions change over time, and real­is­tic esti­mates are essen­tial for a mean­ing­ful back­test. Because the price his­to­ries of some instru­ments extend back to the 1970s, the cost his­to­ry must include at least rea­son­able prox­ies for the bro­ker fees charged at the time. In the 1970s, com­mis­sions could be as high as USD 30 per trans­ac­tion!

I will cov­er how these set­tings work togeth­er, how to auto­mate rolls effi­cient­ly and how to esti­mate costs for the ear­li­er part of the back­test in a sep­a­rate arti­cle.

Contract quirks

Fred likes the phrase “stan­dard­ised con­tracts”. It sounds as though some­one has already done the dif­fi­cult work. He opens the secu­ri­ty mas­ter expect­ing a qui­et after­noon and finds a pet­ting zoo.

The first ani­mal is 🔥GAS-US-WEEKLY, which expires every Fri­day⁷. Fred’s fil­ing sys­tem iden­ti­fies a futures con­tract only by year and month. This works beau­ti­ful­ly until a month con­tains five Fri­days. “Which Fri­day?” asks Fred. The spread­sheet main­tains a dig­ni­fied silence (see Fig­ure 12). This instru­ment is black­list­ed because the required his­to­ry is unavail­able, but the prob­lem of week­ly expiries would remain even if the data became avail­able.

Then comes Euro­pean nat­ur­al gas along: It changes its size with­out ask­ing per­mis­sion. The con­tract sizes of Dutch TTF 🔥GAS-W-NL⁸ and UK NBP 🔥GAS-W-UK⁹ depend on the num­ber of days in the deliv­ery month, so a March con­tract is larg­er than a Feb­ru­ary con­tract. TTF also counts the deliv­ery hours, where day­light-sav­ing time occa­sion­al­ly adds or removes an hour.

My solu­tion is to hold only Decem­ber expiries (hence the suf­fix ‑W for Win­ter). Decem­ber reli­ably sup­plies 31 days, giv­ing fixed con­fig­ured mul­ti­pli­ers of 744 MWh for TTF and 31,000 therms for NBP. It is less a tri­umph of finan­cial engi­neer­ing than a strate­gic alliance with the cal­en­dar.

Fred is just begin­ning to admire this solu­tion when ICE announces a min­i­mum trad­ing size of five lots for both prod­ucts. Order­ing one gas con­tract is not on the menu: The small­est serv­ing feeds a fam­i­ly of five. Fred marks both rows DoNot­Trade, stamps the appli­ca­tion closed and con­grat­u­lates him­self on anoth­er job well done.

Trad­ing on exchanges around the world brings plen­ty of local pecu­liar­i­ties. Aus­tralia is a par­tic­u­lar­ly gen­er­ous con­trib­u­tor. Until June 2025, ASX cash equi­ties opened in five groups ordered by secu­ri­ty code¹⁰. Trad­ing start­ed in alpha­bet­i­cal batch­es: A–B first, C–F next, and every­one from S–Z wait­ing at the back. ASX now uses a sin­gle open­ing auc­tion, but its bond futures still keep Fred employed.

The Aus­tralian bond futures have a fixed face val­ue of AUD 100,000, yet their prices are quot­ed as 100 minus the annu­al yield. Con­se­quent­ly, the dol­lar val­ue of the same yield move varies with the lev­el of inter­est rates¹¹. The exact con­ver­sion is non­lin­ear.

My sys­tem uses a fixed aver­age mul­ti­pli­er instead. This will not win a prize in numer­i­cal analy­sis, but the vari­a­tion is small enough for the job and Com­ment records the approx­i­ma­tion for the next per­son who won­ders why the mul­ti­pli­er looks sus­pi­cious­ly hand-craft­ed.

Hong Kong sup­plied anoth­er les­son when I first trad­ed equi­ties there. At noon, the reg­u­lar morn­ing ses­sion end­ed. I checked the logs, the inter­net con­nec­tion, and, briefly, my san­i­ty. Noth­ing was bro­ken: the after­noon ses­sion begins at 1 p.m. Even an elec­tron­ic mar­ket can put up a sign say­ing, “Back after lunch.”

Final­ly, expiry sched­ules can change dur­ing the life­time of an instru­ment. For 🌾MILLWHEAT, Com­ment records a cycle change and says that the usable sequence begins with the March 2015 con­tract, 2015H. The cur­rent cycle com­pris­es March, May, Sep­tem­ber and Decem­ber¹². Unfor­tu­nate­ly, a com­ment is doc­u­men­ta­tion, not an exe­cutable instruc­tion. Writ­ing “start here” in a spread­sheet does not make the loader start there, just as Fred’s note say­ing “please work” does not repair the office print­er.

These odd­i­ties point to the same engi­neer­ing rule: a futures iden­ti­fi­er is part of the data mod­el, not just a label. If an excep­tion changes prices, posi­tion size, trad­ing access or con­tract selec­tion, the rule belongs in code and tests. Com­ment should explain the excep­tion, not be the only place where it exists.

Fred adds “stan­dard­ised” to his list of words that should require sup­port­ing evi­dence.

Collect, compare, reconcile

The process starts with three auto­mat­ed steps: retriev­ing the futures uni­verse from Inter­ac­tive Bro­kers, col­lect­ing CSI’s prod­uct cat­a­log, and obtain­ing Norgate’s futures sym­bols. Each source pro­vides a dif­fer­ent part of the pic­ture. The bro­ker lists prod­ucts that may be avail­able to trade. The providers list the his­to­ries they cov­er.

Next comes match­ing. Auto­mat­ed helpers com­pare the bro­ker cat­a­log with the sym­bols already record­ed in the secu­ri­ty mas­ter. They flag new prod­ucts to con­fig­ure and exist­ing sym­bols that no longer appear in the broker’s cat­a­log. Resolv­ing these gaps is still a man­u­al process. The descrip­tion, exchange, cur­ren­cy, con­tract size, and set­tle­ment terms help estab­lish whether a bro­ker prod­uct match­es a provider’s his­to­ry. Sim­i­lar names are use­ful clues, but they are not proof.

From candidates to decisions

Once the sources are rec­on­ciled, the review pro­ceeds through sev­er­al fil­ters and pro­pos­al lists. The first pass removes prod­ucts out­side the intend­ed scope: sin­gle-stock and spot-quot­ed futures, along with mar­kets the sys­tem can­not access. The remain­ing can­di­dates need usable his­to­ries and a way to esti­mate trad­ing costs.

The next pass revis­its ear­li­er exclu­sions. Has a pre­vi­ous­ly illiq­uid con­tract attract­ed vol­ume? Has a short his­to­ry become long enough? Is bid/ask data now avail­able? Have mea­sured spreads fall­en below the cost esti­mate that kept the instru­ment out? The note­book iden­ti­fies these can­di­dates using provider sta­tis­tics, stored his­to­ries, and col­lect­ed spreads. Where evi­dence is miss­ing, the work list may point back to the exchange for a man­u­al liq­uid­i­ty check.

Exist­ing instru­ments need atten­tion too. Miss­ing bro­ker sym­bols and sus­tained zero vol­ume can sig­nal dete­ri­o­rat­ing access or liq­uid­i­ty. Oth­er reports com­pare high­ly cor­re­lat­ed con­tracts and sug­gest shar­ing his­to­ries across sizes. These pro­pos­als need review: units and set­tle­ment terms can dif­fer. Using a larg­er contract’s his­to­ry also omits the small­er contract’s own vol­ume and open-inter­est data, which can affect roll deci­sions.

The review ends with a deci­sion for each can­di­date: add it, revise its con­fig­u­ra­tion, retain it only for his­tor­i­cal research, or leave it exclud­ed. The work­book records the deci­sion and its rea­son, so next year’s review can ques­tion an assump­tion rather than recon­struct it.

The reports iden­ti­fy can­di­dates — I make the changes. Fred’s REVIEWED stamp is only use­ful if next year’s review­er can tell what he checked.

Rebuild, check, repeat

After the work­book changes, the data pipeline reloads the def­i­n­i­tions, updates the con­tract his­to­ries, applies migra­tions and back­fills, cre­ates roll cal­en­dars, and assem­bles the back-adjust­ed series. This is where a plau­si­ble spread­sheet row has to pro­duce usable data.

Auto­mat­ed data qual­i­ty checks sup­port each step, as out­lined below. A fail­ure sends the rel­e­vant def­i­n­i­tion or source data back for inves­ti­ga­tion.

The updat­ed mas­ter then feeds into strat­e­gy recal­i­bra­tion. New mar­kets and longer his­to­ries can change sig­nal mag­ni­tudes and cor­re­la­tions. The sys­tem re-esti­mates fore­cast scal­ing and diver­si­fi­ca­tion adjust­ments, then reruns the port­fo­lio back­test after costs and pro­duces a recal­i­bra­tion report.

The annu­al review revis­its the whole uni­verse. Dai­ly auto­mat­ed checks and mon­i­tor­ing may prompt an ad hoc review. A new prod­uct or a bro­ker restric­tion (as in the LME exam­ple above) can there­fore restart the rel­e­vant part of the process. The aim is to make each update small­er and eas­i­er to explain, rather than save a year’s sur­pris­es for one hero­ic spread­sheet ses­sion.