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

[Bill Pippin]

The following is a moderately long background piece for a question that has
come up a number of times now, of how people in Europe can add new symbols
and contracts to the database.  In what follows, mixedcase names are database
tables, and paths are relative to the directory sql of an unpacked tarball.
_______________________________________________________________________________

A brief look at symbols and contracts in the database, by asking: Where is
the data, and how does it get there?

In [very] brief, there are three primary source tables that are loaded, and
these are then used to populate the table Underlying. Then the table Symbol
is loaded, first from Underlying, and second from ProductMap.  Finally Symbol
is filtered by LocalMap into Contract to give the set of contracts.

The contracts are the entities that can be subscribed to for current market
data, queried for history data, and in some cases bought and sold.

A.  Data for the table Underlying 

In reading the load.sql script, focusing on the primary symbol data, and
working backwards from goal to means, note, 1st, the insert statements in
load.sql that populate the Underlying table; 2nd, the table names those insert
statements select from, and 3rd, the data sources that are src'd by load.sql
in order to populate the three primary source tables.

In brief, Underlying is populated from Currency, Miscellany, and Stock,
and those src'd from req/Currency.sql, mod/Miscellany.sql, and mod/Stock.sql.
At this point Underlying is filled with type, home, name, and description
quadruples (see syms.sql for the create table statement for Underlying).
Looking at the values for the src'd tables, Underlying can be seen to be
a union of security, more precisely, product, tuples, labelled by type of
security.

B.  Data for the table Symbol

Once given the contents of Underlying, inserts in the load.sql script
fill Symbol from Underlying and ProductMap, respectively.

B.1  Underlying fills Symbol

Script load.sql first copies from Underlying to Symbol, giving us an initial
set of symbols.  Keep in mind that a symbol is an abstraction, not just a
string like SUNW, or JAVA.  Those are names, and a symbol has in addition a
type, e.g., stock or future; and a home, since different exchanges may use the
same name for different symbols, that is underlying products.

Although Symbol now has an initial population originally derived from: the
primary currencies supported by IB, USD, AUD, CAD, CHF, EUR, GBP, HKD, JPY,
MXN, and SEK; miscellaneous index, commodity, and paper names from Miscellany;
and the stocks from, you guessed it, Stock, currently mostly NYSE and NASDAQ,
and some AMEX --- still, there are any number of possible derived products not
yet in symbol.  In particular, for the entries in Miscellany, we may want to
use the same name for related indices, futures, and options.

B.2  ProductMap adds to Symbol

The values in the three primary source tables are treated as base case
values, and the ProductMap table allows us to multiply them into new Symbol
tuples as desired.  Note from the create table statements in syms.sql that
Underlying and Symbol have nearly the same structure, being in essence
type-exchange-name triples.  Each product mapping gives directions for
creating a new, derived symbol triple from an underlying, and incidently
stands for the derivative relationship between the two, since that is not
directly visible except by matching on the long name comment attributes,
a tedious and fragile business at best. 

C.  Data for the table Contract

Recall that the table LocalSet controls how contracts are created from
symbols.  Note that as delivered, the load script for the database results
in, as this is written, more than 5000 symbols, but less than 200 LocalSet
tuples and contracts.  This indicates one of the reasons for LocalSet, to
eliminate uninteresting symbols from contract consideration;  another is
to provide the additional information needed to fully define contracts.

C.1  The role of LocalSet

The entries in LocalSet consist of key values related to key indices in
Symbol, and three additional attributes, the key values for route, unit of
currency, and variant part tag index.

The first two are straight-forward, and have already been encountered in
a different context, as symbol home exchanges, and currencies to be traded.
Here the route refers to the floor or ECN where the trade is to take place
or cross, while the currency is used as a unit of denomination.

The tag index varies in interpretation with the security type, and as it
implies additional variant part data such as expiry and right.  For stocks
and indices, where there is no variant part, the tag is always zero, while
for futures and options it stands for a tuple index into either of the tables
FutDetail and OptDetail.

C.2  Adding new contracts

The proper way to add contracts to the Contract table is by adding new data
to the source and intermediate load files from which it was originally defined,
and reloading the database by running the create.sql script.  Note that for
production use, you will want to first unload history data records and order
journal entries in order to save them, since in reloading the database tables
are first dropped, then recreated, and finally reloaded.  In what follows,
when I speak of adding an entry to a table, I mean that an entry is added to
the load file, so that the changes you make will persist the next time you
recreate the database.

In brief, the preferred way to add a new contract to the Contract table
is to add an entry to the LocalSet load file mod/LocalSet.sql, add supporting
entries as needed to primary and intermediate load files used to populate
Symbol, and then recreate the database.

In the worst case, for a new Underlying not yet appearing in the database,
you will have to add to one of Currency, Miscellany, or Stock, then, if
deriving from that, to ProductMap, and in any case, add to LocalSet.  If the
underlying already exists, but some derivative of interest does not, then you
need add to only ProductMap and LocalSet; and if the security of interest
occurs in Symbol, you only need select it by adding to LocalSet.

To summarize, in order to add a new contract, add a new LocalSet load file
entry, and add as well supporting entries as needed to meet foreign key
constraints, to the source load files req/Currency.sql, mod/Miscellany.sql,
mod/Stock.sql, and mod/ProductMap.sql; and then, from the directory sql,
and in the mysql interpreter, source the create.sql script.

The table LocalSet, although used by the load script, is never referred to by
the shim, and you are free to add or delete entries up to the point the
database is used for production, after which existing entries must not be
deleted, and adds should be at the end.

Thanks,

Bill