[ts-gen] adding new-expiry futures products (was: product ids)

[Bill Pippin]

Nils,

> I am having problems when using the new order route format. 

Indeed; there are actually several problems here, with enough overlap
that credit assignment becomes uncertain.  The first and most critical
is with the database symbol sources, and I'm impressed that you got as
far as you did, since as currently distributed, they don't go far
enough forward in time for the symbol you're interested in.

In the following, diagnosis paragraphs have labels of the form A.N,
and solution recipe paragraphs, labels of the form B.N, where N is
some decimal digit.

A.1: if you want the symbol: FUT:SMART:GBL:20090306
then, sorry, according to contract info, there is no such symbol.  The
following table shows what FUT:GBL symbols IB has ever assigned conids
to:

    expiry   local symbol  ib conid  exchanges
    -------- ------------  --------  ---------
    20070308  FGBL MAR 07  39548559  DTB MIBSX
    20070607  FGBL JUN 07  40743625  DTB MIBSX
    20070906  FGBL SEP 07  41958027  DTB MIBSX
    20071206  FGBL DEC 07  43246990  DTB MIBSX
    20080306  FGBL MAR 08  44613724  DTB MIBSX
    20080606  FGBL JUN 08  46089645  DTB MIBSX
    20080908  FGBL SEP 08  47574915  DTB MIBSX
    20081208  FGBL DEC 08  49450260  DTB MIBSX
    20090306  FGBL MAR 09  51728729  DTB MIBSX
    20090608  FGBL JUN 09  53716236  DTB MIBSX
    20090908  FGBL SEP 09  56441308  DTB MIBSX

A.2: once you replace SMART with DTB [MIBSX isn't yet in our
exchange list], then we see problems with the current database symbol
sources; ibc:46089645 is for jun 08.  I believe there aren't any
entries in the stock load files for an expiry of 20090306; the load
files are becoming dated.  Since you somehow got the example to work,
presumably you've added data, which is fine.

A.3: if the data was added properly, you should be able to use
ibc:51728729; there is something wrong with the way data was loaded
if ibc:46089645 expands to FUT:SMART:GBL:20090306 .  It's not clear
to me how this happens, and I believe the other problems should be
fixed first, and then presumably this one goes away.

So:

B.0: download the newest tarball, and make sure to read this message
through to the end before beginning to make changes, and consider
diff'ing sym/A00Fut01.sql and mod/LocalFut.sql between old and new
forms so you know better what's going on;

B.1: add any other load data you need to sym/A00Fut00.sql or
sym/A00Fut01.sql (almost certainly the latter), ensuring that the rows
remain in ibc order;

B.2: add expiry update entries in mod/LocalFut.sql, to mark the futures
products from the previous step as valid;

B.3: repopulate your database, using, e.g., from the command prompt for
mysql, and with that started with the sql sources directory as the
current directory, the command:

    sql$ ../bin/mysql
    mysql> source create.sql

B.4: update the symbols listing, using, again with sql as the current
directory, e.g.:

    sql$  mv syms.txt syms.old;
    sql$  mysql < bin/get_id.sql > syms.txt
    sql$  grep GBL syms.txt

checking that there are product entries for contracts ibc:51728729
and ibc:53716236; and

B.5: run the shim and verify product lookup using a tick subscription
for ibc:51728729, to see if this is the contract you want.  It has a
20090306 expiry, but that's all I know; you'll have to check the
prices, and use the IB tws gui to find the symbol to decide for
yourself.  Consider also using your paper account for orders along
with a good measure of common sense to make doubly certain that
this is indeed the contract you want.  Symbols data is very much
"no warranty included" and "if it breaks, you get to keep both pieces".

B.6: I've added two entries for GBL to A0Fut01.sql and mod/LocalFut.sql
to show you what's needed, and am about to push the resulting code
out to the web site.  Hopefully you can look at what I've done and
see how to do the same for GBM, GBS, and so on.  You'll need to run
some wildcard contract info queries against the IB tws first, and
if I may make a suggestion, when adding the data to the load files
it's helpful to have a text editor that can work out past 80 chars,
e.g., if you use vi then use "set nowrap" .

I'm currently working on ruby scripts to extract symbols data from
option contract info queries, and then translate it to load file
format.  Although I will eventually also modify those scripts to
handle futures as well, right now I'm focused on options, and I also
have some work to do on expanding the command language.

Those who have problems with the aging of the symbol load files,
please feel free to comment to the list, and feel free to make
suggestions to help out others with similar problems. Also, please
feel free to submit script source code to the list.

This is something we are already working on, although our symbol load
file collection efforts are interspersed with work on other tasks, so
again, please feel free to contribute.

By the way, the tarball I'm pushing out has some option-related
changes already in place, and these include a bump in database
version.  Note also that there will be a future change to the
exchange table, req/Exchange.sql, to add IBSX and MIBSX, the IB
size order routes.

Nils, I'd be interested in knowing whether the steps described above
are sufficient to solve your problem.

Thanks,

Bill