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

sam sam_backtester at yahoo.fr
Fri Aug 29 11:36:56 EDT 2008

Thanks for the answer.

The db structure is more clear for me now, and I know now what was wrong for me, when I was trying to get historical data for some of my european stocks.

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). So sym/A0Stk05.sql might be changed or re-generated for this stock.
Second, I still have a strange issue with a french stock, TFI, ibid 29612096. 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...
Third point, I tried to add some E-Mini contracts, which don't seems 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 ?

Unfortunatly, I was forced to stop my tests, I have a strange issue, with the lastest tarball, a fresh  database, when I try to get historical data (here example with AIG):
select past 6 5 Ymd_T(20080801 23:00:00)

Problem: 550 ice: req wire-level format typecheck error
I've restarted few times my TWS, but still got this error for.

About Warrants, I'm not so big fan of these products, but I can help if needed.

It's a good idea to include th .ps file in the tarball, I had some issues for generate the file with Graphviz on my Debian based distribution.



--- En date de : Mer 27.8.08, Bill Pippin <pippin at owlriver.net> a écrit :
De: Bill Pippin <pippin at owlriver.net>
Objet: Re: How symbols and contracts are defined during database creation
À: sam_backtester at yahoo.fr, "trading-shim general mailing list" <ts-general at trading-shim.org>
Date: Mercredi 27 Août 2008, 19h59


You ask about adding symbols to the database:

> I'll like to add new european symbols/contracts to the database,

Hopefully they are, with the exception of options, already there,
although you will find that updates for cash and futures are needed,
abut which more in the following.  To check symbol availability,
run bin/get_id.sql, and feel free to adapt the statements therein
to your uses.  You may also grep through sql/sym/*.sql to see what
symbols are loaded into the database via load.sql.

> I think the easiest way to do that is to follow the 2007/08 post,
> with the right table names.

> I was wondering if it might be possible to update table names in
> this post: http://www.trading-shim.org/pipermail/ts-general/
> 2007-August/000076.html.

A comprehensive update following the form of post would take awhile,
and might be somewhat confusing, since it expects users to need to
add symbols, and the table redesign was made in part to reduce that
need.  The database structure is much improved, and quite different.

Before we were working with symbol data from a wide variety of
sources of differing quality, and so the design approach was very
general, in order to deal with fundamental problems with the product

Now we provide what we believe, outside of options, forwards, and
warrants, to be a comprehensive set of the abstract products
accessible via the IB tws api.  We're thinking about options, not
sure if forwards are of interest --- we want to fix cash and futures
first --- and are handicapped with warrants because they don't trade
here.  (We're also not so knowledgeable about fixed incomes, so
although bonds are included in the load files, caveat emptor.)

Actually, the best guides to the database table structure, are,
(1), the create table statements; (2), the foreign key dependency
graphs; and (3), the select statements in more.sql and slow.sql.
The creates and graphs are found in:

    (1) {enum, base, syms, secs, subs, xact} x {.sql}
    (2) {foreign,    syms,             xact} x {.ps}

The postscript files above were produced using the graphviz dot
program via the script bin/fkey.rb .  If you lack the tools to
produce the graphs, and one or more is missing from your tarball,
I've just pushed another release that has all three of the above
postscript files in the sql directory.

Of the fkey diagrams, foreign.ps and syms.ps are the most useful
for your purposes, with the first showing how load file data is
shifted around, and the second showing how products are defined
by a tagged variant union of Static, Future, and Option.

As for (3), recall that the load files of interest are in sql/sym.
Their contents are src'd into Source and FutSrc, with OptSrc planned
for future use; note also that there are not yet any load files for
options, although we are considering how to support them later.

>From the source tables, insert-selects in more.sql populate Descript,
Symbol, Expire, Future, Static, and Product.  Stocks, indices, cash,
and bonds, those securities and derivatives that do not have an
expiry, are placed in Static, and data for futures is split between
Expire and Future.  The insert-selects in slow.sql go on to define
default contracts in the table Contract.

If you want to add symbols, however, feel free to add information
to the appropriate load files in sql/sym, as long as you know it's
correct, and are sure it's not already there.  Or, you can create
new load files, and modify load.sql accordingly.  For options, you'll
need also to uncomment out the insert at the bottom of more.sql:

> insert into Product(tid, sfo, ibc) select ... from  Static  ...
> insert into Product(tid, sfo, ibc) select ... from  Future  ...
> -- ert into Product(tid, sfo, ibc) select ... from `Option` ...

There is a very real possibility that there will be errors in
your data that lead to later headaches as the database population
scripts are executed; in particular, ib contract ids map 1-to-1
with products, so that if the data you add is duplicative, tables
won't be populated as insert statements fail due to unique key

Table updates are another issue entirely, being useful and in some
cases essential.  Recall that I mentioned errors in the cash and
future records as given in the load files; and there are two
problems that are impractical to repair prior to distribution of
the load files.

The data we have collected for IB currency pairs sometimes lacks
proper information about the quote currency, for a pair (A.B) that
second currency B; and the expiries for about three quarters of the
futures are off, most often by a few days, but in any case likely
to be different from the values given by the api via contract

Since according to IB's account license we can't redistribute
api data, our intended solution for the longterm is to provide
a script fill.rb to update the cash currency and future expiry
attributes by querying the tws for the correct value.  For now,
you are free to do this yourself by hand for symbols of
interest.  You may find the scripts exs/cash and exs/futures.rb
useful for this purpose; you would run those, and edit various
.sql files to reflect the answers that were captured to the shim
log file.

For a complete solution, you could update the load files
{sql/sym/} x {A0Csh00, A0Fut00, A0Fut01} x {.sql}, and
modify the related insert-selects, but you probably want
to wait for our fill.rb script.  For the short run, you
may find updates via sql/mod/LocalFut.sql to be useful.

The roles of {sql/mod} x {LocalSet, LocalFut} x {.sql} have
changed.  You no longer use these to add symbols --- they should
already be there --- rather you use them to control contract
generation, in two ways.  First, for any symbols included, in
LocalSet for symbols in Static, and LocalFut for futures, they
provide what you may find to be a more convenient contract
numbering, by pulling your favorite symbols to the front of the
number sequence by their inclusion therein.  Second, and not so
incidentally, for futures your entries in LocalFut.sql allow you
to fix expiry values, with the result that the future's expiry is
marked as corrected.  Without this correction the future symbol
would not have a tradeable contract, since the raw data from the
net for this attribute is not trustworthy, and the insert
statements to populate Product from (Future, Expire) have been
written to ignore the raw, uncorrected entries.

If you want to work with warrants, please speak up on the list;
you may be able to help us with testing.  We already have data
for warrants, but since we can not check it, fix it, or use it,
we don't want to distribute it either.  Please understand that
this kind of work would take awhile, and we are not ready to do
it right away; but still, please let us know if you are

I hope the above material is sufficient; if not, please feel free
to ask more questions.



Envoyez avec Yahoo! Mail. Une boite mail plus intelligente http://mail.yahoo.fr
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.trading-shim.org/pipermail/ts-general/attachments/20080829/55eaec7e/attachment-0001.html 

More information about the ts-general mailing list