[ts-gen] How symbols and contracts are defined during database creation

[Bill Pippin]

Sam,

I'm now working my way through your questions about symbols; some of
these answers may since have been overtaken by events.

This 1st point below has already been dealt with by newer releases,
which include an updated entry for STK:35052259:

> First, one stock name change its symbol/name these last months, but
> keep the same ibc (it's the stock named now GSZ instead of GAZ, ibid
> 35052259). 

Please let us know if the newer releases don't fix this problem.
For the following, I confirm your results with respect to the api:

> Second, I still have a strange issue with a french stock, TFI,
> ibid 29612096.

Using grep, I find the following entries are loaded into the database:

    sym$ grep "'TFI'" *Stk* 
    sym$ grep "'FSE'" *Stk* 

A0Stk00.sql:( 2950131, 'STK', 'TFI', 'EUR', 'ES' , 'TABLEROS DE FIBR ...
A0Stk03.sql:(26319564, 'STK', 'TFI', 'EUR', 'IT' , 'TREVI FINANZIARI ...
A0Stk03.sql:(29612096, 'STK', 'TFI', 'EUR', 'FR' , 'TF1'             ...
A0Stk08.sql:(46134271, 'STK', 'TFI', 'USD', 'US' , 'SPDR LEHMAN MUNI ...
A0Stk03.sql:(17481350, 'STK', 'FSE', 'USD', 'US' , 'FINANCIAL SEC AS ...
A0Stk09.sql:(47674201, 'STK', 'FSE', 'AUD', 'AU' , 'CENTRALIAN MINER ...

Given the commands:

1| 9| 0|select wild STK TFI  all;	|
1| 9| 0|select wild STK FSE  all;	|

with resulting requests:

2| 9| 3|9|3|TFI|STK||1.00||1||||1|
2| 9| 3|9|3|FSE|STK||1.00||1||||1|

I receive messages indicating that, for my paper account,
there are six contracts for stocks with symbol name TFI,
and two with FSE, where all but STK:TFI:51282502 are in
the database:

  * STK:TFI: 2950131
  * STK:TFI:26319564
  * STK:TFI:29612096
  * STK:TFI:46134271
    STK:TFI:51282502

  * STK:FSE:17481350
  * STK:FSE:47674201

Of course your account may in some ways have a different viewpoint,
but in particular, I confirm that there is an ibc 29612096 with
symbol name TFI.  Your problem seems to be that you can't get
market data, and I confirm this as well.

> ...  I'm unable to get any data for this stock, it's maybe
> an IB issue, because I've found a strange thing, when I get a chart
> in TWS for this stock, the symbol name is FSE instead of TFI ...

Searching the output of the bin/get_id.sql script, I find that
the database uid (our contract id) is 16088, so I use this
number for commands to make market data and market depth
subscriptions:

1|10| 0|select tick  16088 1;	|
1|11| 0|select book  16088 1;	|

with requests as follows (note that contract descriptions for
the market data and market depth request types vary slightly):

2| 1| 5| 1|5|1|TFI|STK||0.00||1|SMART||EUR|| |STK:SMART:TFI:
2|10| 3|10|3|2|TFI|STK||0.00||1|SMART| EUR||1|STK:SMART:TFI:

which results in the messages: 

3| 4| 2| 1| 200|No security definition has been found for the request|
3| 4| 2| 2| 200|No security definition has been found for the request|

Later when I try to cancel, those requests naturally fail as well:

3| 4| 2| 1| 300|Can't find EId with tickerId:1|
3| 4| 2| 2| 310|Can't find the subscribed market depth with tickerId:2|

So, my account also does not give access to STK:TFI:29612096,
which does not surprise me very much; it is, after all, a
foreign stock, and presumably my paper account does not include
subscriptions for it.

It's also conceivable that some special incantation for the market
data request would fix this problem, and in any case I would expect
you to have access to the symbol, so I suggest that you use the IB
sample client to attempt to subscribe to market data.

If you can get that working, we can try to identify the problem.
Otherwise, I'm stuck for now.  If you choose to investigate
this symbol via the IB sample client for your account, please
let me know what you find.

> Third point, I tried to add some E-Mini contracts, which don't seem
> to be here in sym/*.sql.  I thought I was just need to add the correct
> entries in, for example, sym/A0Fut00.sql, and in Expiry.sql. But I
> don't get the newly added contracts with a get_id.sql, maybe i'll need
> to change the where clauses in this script ?

Currently, there are several steps needed to add future contracts to
the database, and the process I'm about to describe is temporary, that
is not yet finalized.

For your example, given that the product is completely missing from
the input data, you do indeed need to add an entry, in either of
sym/A0Fut0[01].sql.

Next, you also, as you correctly suspect, need to deal with contract
expiration information, and here you mention changes to Expiry.sql.
This, however, is a wrong turn; a reasonable one, but you are being
led astray by our incomplete approach to future contract expiries.

(A), the files in sym/A0Fut0?.sql already include expiry values
that we have obtained from the net.

(B), if you check for occurrences of the table Expiry throughout
the sql scripts, you'll see that although it's loaded, it's never
used in any other way.  Using grep, and ignoring comments, we see that
the table is dropped/created, and populated via insert, and that's
it:

    sql$ grep Expiry *.sql */*.sql

    drop.sql      :drop table if exists Expiry;
    base.sql      :create table Expiry
    more.sql      :source        sym/Expiry.sql; -- local only
    sym/Expiry.sql:  into Expiry(type, ibc, expiry)

(C), at the point where loaded in more.sql, as the comment below
indicates, it's currently for local use only.  So, ignore the table
Expiry for now.  At that point where it's loaded in more.sql,
however, we find another src'd table, LocalFut.sql, which is meant
to patch inaccurate expiry values.

    source mod/LocalSet.sql;   -- fix init contract numbering
    source mod/LocalFut.sql;   -- fix uncertain expiry values
    source   sym/Expiry.sql;   -- local only

Adding entries to mod/LocalFut.sql is probably a step you've
missed.  Since the data we collect from the net has imprecise
expiry values, or at least they often disagree with contract
details, then those values need to be validated, and the entries
in LocalFut.sql do just that.  As currently implemented, entries
from sym/A0Fut0?.sql are ignored unless a matching update from
mod/LocalFut.sql updates the expiry and sets a validation flag.

Looking at the table definition for Future in syms.i, there are two
expiry-related attributes:

    netexp         char( 8)  not null,
    expiry      varchar( 8),

While the first is set from the symbol load files sym/A0Fut0?.sql, the
second comes from mod/LocalFut.sql, and it's that second value that
is decisive when contracts are generated, via slow.sql; in the
insert statement below from that file, note the where condition
that Future.expiry is not null:

    insert
      into Contract(tag, route)
    select
  distinct Product.uid as tag, Exchange.uid
      from SecType  ,
           LocalSet ,
           Exchange ,
           Symbol   ,
             Expire straight_join
             Future straight_join
            Product
     where   SecType.type  = 'FUT'
        and  SecType.type  = LocalSet.type
        and LocalSet.route = Exchange.name
        and LocalSet.route =   Expire.route
        and LocalSet.name  =   Symbol.name and LocalSet.region = Symbol.region
        and LocalSet.curr  =   Symbol.curr and SecType.uid =  Symbol.tid
        and    Symbol.uid  =   Expire.ncr  and SecType.uid =  Expire.tid
        and    Expire.uid  =   Future.eid  and SecType.uid =  Future.tid
        and    Future.uid  =  Product.sfo  and SecType.uid = Product.tid
        and    Future.expiry is not null
        and (find_in_set(LocalSet.route, Expire.stks)
          or find_in_set(LocalSet.route, Expire.misc))
   order by tag;

So, unless you added the appropriate entries to mod/LocalFut.sql,
no contract was generated for the products you've added.
By the way, in the future we hope to provide a ruby script, say
fill.fut.rb, to automatically update the expiry attribute in the
Future table.

Each site would have to run the script after setting up the database,
since we can't redistribute api data, so database setup would be
somewhat more involved, but on balance it's probably worth the
trouble.  It will, however, require some time-consuming internal
changes to the shim, so it's being put off for now.

For now, and your contracts in particular, please feel free to submit
a reasonable number of entries to the list if you so desire.  We can
add those to LocalFut.sql to save you the trouble; we've already done
this for other contracts of interest to various users.

Thanks,

Bill