[ts-gen] Unable to retrieve historical data for german stocks

pippin at owlriver.net pippin at owlriver.net
Fri Oct 17 15:33:32 EDT 2008


Paolo,

I'll take your most critical point first:

> ... let me know how [to add] ... stock/IBIS (as route)
> combinations for german stocks.

First, let me define the following terms related to symbols:

    1.  abstract product: a security or derivative w/o  route
    2.  concrete product: a security or derivative with route
    3.  contract: a currency denominated product, whether abstract
        or concrete.

So, is an IB contract id abstract or concrete?  Unfortunately
it depends.  For any ibc there is a related route set, typically
non-singleton, and about which more later, and often, especially
for popular symbols, there are several IB contract ids for a
given abstract product.  Again, more to come.

For now, for the common case, note simply that IB contract ids
typically label partially specified products, so that there is
more than one possible route for any given ibc, and in addition
abstract products may also have several possible ibc numbers.

Given that although the products listed below from syms.txt may
have undesirable routing choices, the abstract product symbols
themselves are of interest:

> cid    pid    ibc    type  route  name   curr   region

> 14157  14148  14079  STK   SMART  ALV    EUR    DE
> 14195  14186  14081  STK   SMART  BAS    EUR    DE
> 14197  14188  14084  STK   SMART  BAY    EUR    DE
> 14315  14306  14121  STK   SMART  DBK    EUR    DE

then your question can be restated in two parts:

    1.  How does one find out if the shim's database has the
        particular routes desired; and

    2.  If those are lacking, what can be done to add them;
        a.  Does the IB tws api support such a choice of routes; and
        b.  If so, how should it be added to the shim's database?
            
Taking the questions in order:

________________________________________________________________

1.  Checking the shim's database sources for default routes
________________________________________________________________

The shim's database is populated via a number of load files found in
the subdirectories of the directory sql:

    req
    mod
    sym

We've collected a fair amount of product, routing, and contract 
information for IB-supported symbols from the net, and that data
is found in the directory sym.  Although it is not precisely the
same as the contract info you could get via the contract info
request, it has the virtue of being redistributable, whereas the
account license prevents such redistribution of data collected
via the api.  So, we provide this symbol data collected from
the net as part of the shim distribution, and it provides the
initial population of symbols for the shim database.

>From the directory sym, ls gives:

    sym$ ls
    A0Bnd00.sql  A0Csh00.sql  A0Stk01.sql  A0Stk06.sql
    A0Bnd01.sql  A0Fut00.sql  A0Stk02.sql  A0Stk07.sql
    A0Bnd02.sql  A0Fut01.sql  A0Stk03.sql  A0Stk08.sql
    A0Bnd03.sql  A0Ind00.sql  A0Stk04.sql  A0Stk09.sql
    A0Bnd04.sql  A0Stk00.sql  A0Stk05.sql

Yes, the file names are boring and unimaginative. ;<)  The key point
is that they're in sql/sym; once there, you should be able to figure
out from context what you're looking at.  Ok, moving on, for equities,
we want the files with prefix A0Stk.  They begin with insert statement
headers that include a table attribute list:

    insert into Source(
        ibc, type, name, curr, code, `desc`, stks, misc, route)
    values

Using grep we can see what symbols from these files are
supposed to be available in the database:

   (grep "'ALV'" ??Stk*.sql >  ../file; \
    grep "'BAS'" ??Stk*.sql >> ../file; \
    grep "'BAY'" ??Stk*.sql >> ../file; \
    grep "'DBK'" ??Stk*.sql >> ../file)

Note the nested quotes; the single quotes are part of the sql insert
syntax, and the double quotes protect those symbols from the shell;
we are looking not for, say, BAY, which would match on, among others,
EBAY, but rather for 'BAY' in particular.  These quotes are one
reason I'm working from the original load files in sql/sym, rather
than the symbol report sql/syms.txt.  Another is that the latter
file is purely informative, and from the standpoint of dataflow an
effect, not a cause.

The queries above provide rows of information which, truncated,
reordered, grouped, and labelled [by the attribute list above],
are as follows:

   ibc     type   name   curr  code    desc   ( ...  stks misc route)
--------  -----  -----  -----  ----   -------------------------
   14079, 'STK', 'ALV', 'EUR', 'DE' , 'ALLIANZ SE'                ...
   14081, 'STK', 'BAS', 'EUR', 'DE' , 'BASF AG'                   ...
   14084, 'STK', 'BAY', 'EUR', 'DE' , 'BAYER AG ON'               ...
   14121, 'STK', 'DBK', 'EUR', 'DE' , 'DEUTSCHE BANK AG NA O.N'   ...
 5148183, 'STK', 'ALV', 'CHF', 'DE' , 'ALLIANZ SE'                ...
10400282, 'STK', 'DBK', 'CHF', 'DE' , 'DEUTSCHE BANK AG NA O.N'   ...
11976156, 'STK', 'BAS', 'CHF', 'DE' , 'BASF AG'                   ...
11976160, 'STK', 'BAY', 'CHF', 'DE' , 'BAYER AG ON'               ...
--------  -----  -----  -----  ----   -------------------------
38708139, 'STK', 'BAY', 'MXN', 'DE' , 'BAYER AG-SPONSORED ADR'    ...
38709082, 'STK', 'DBK', 'MXN', 'DE' , 'DEUTSCHE BANK AG NA O.N'   ...
--------  -----  -----  -----  ----   -------------------------
  908971, 'STK', 'BAY', 'GBP', 'GB' , 'BRITISH AIRWAYS'           ...
 2746144, 'STK', 'ALV', 'USD', 'SE' , 'AUTOLIV INC'               ...
37091856, 'STK', 'ALV', 'CAD', 'CA' , 'ALMA RESOURCES LTD'                                          , 'VENTURE' , ''        , 'VENTURE' ),
37051919, 'STK', 'BAS', 'USD', 'US' , 'BASIC ENERGY SERVICES INC' ...
44187872, 'STK', 'BAS', 'AUD', 'AU' , 'BASS STRAIT OIL CO LTD'    ...
45459414, 'STK', 'BAY', 'CAD', 'CA' , 'BAYSWATER URANIUM CORP'    ...
--------  -----  -----  -----  ----   -------------------------

The reordering is suggested by currency and description, and guided
by common sense.  If you're going to use this information to update
your site's mod/LocalSet.sql file, this step should include human
judgement.  E.g., presumably the contracts denominated in Mexican
pesos (38708139 and 38709082) are to be ignored by the German
investor, even though the abstract products STK:DE:BAY and STK:DE:DBK
may well be of interest.  And, obviously, the investor looking for
STK:DE:BAY will not be happy with British Airways, STK:GB:BAY, or
Bayswater Uranium, STK:CA:BAY.

I'm going to guess that the euro-denominated contracts are where
we should focus for now, ignoring the ones in Swiss franks.  They
have low-numbered IB contract ids, which is always a good sign:

   14079, 'STK', 'ALV', 'EUR', 'DE' , 'ALLIANZ SE'                ...
   14081, 'STK', 'BAS', 'EUR', 'DE' , 'BASF AG'                   ...
   14084, 'STK', 'BAY', 'EUR', 'DE' , 'BAYER AG ON'               ...
   14121, 'STK', 'DBK', 'EUR', 'DE' , 'DEUTSCHE BANK AG NA O.N'   ...

Now we come to the truncated info from the right hand side, which
includes the route sets:

   ibc   st:name:cur:reg    stks                           misc route
-------- -------------- ---------------------------------
   14079 STK:ALV:EUR:DE 'AEB,FWB,IBIS,SBF,SMART,SWB,VIRTX', '','SMART'
   14084 STK:BAY:EUR:DE 'BM,BVME,FWB,IBIS,SMART,SWB,VIRTX', '','SMART'

   14081 STK:BAS:EUR:DE 'FWB,IBIS,SMART,SWB,VIRTX'        , '','SMART'
   14121 STK:DBK:EUR:DE 'FWB,IBIS,SMART,SWB,VIRTX'        , '','SMART'

In the rows above as modified from the results of the original grep
queries, the descriptions, commas, and most single quotes are elided
to fit.  Note that the stks route set is useful, while the misc set is
empty; this is often the case for equities, though almost never for
futures.  The division of the ib route set into two parts is arbitrary
on our part, driven by the support in mysql for sets of cardinality at
most 64; see the load file sql/req/Exchange.sql for more information.
The last entry in each row is the default route, in each case here
SMART.  

The scripts that populate the shim's database, such as
sql/ x {load.sql, more.sql, slow.sql}, use the information from
the sql/sym load files to fill in the tables Descript, Symbol,
Expire, Future, Static, Product, and Contract.  In particular,
the default route determines which specific contract is predefined
for each product route set.

So, we can now answer the first question above:

Q:  [Given some symbol] how does one find out if the shim's database
has the particular routes desired?

A:  One way to check, and the approach I recommend, is to query the
sql/sym load files as described above.  From them, we see here
that abstract products and contracts are likely to be predefined,
while specific contracts may well not be.  In particular, the route
you would like, IBIS, does not have a predefined contract for the
symbols {ALV, BAS, BAY, DBK}.

This latter shortfall occurs in part because it is impractical to
provide contract entries in the shim's database for all possible
routes; IB supports far too many, and many of these are undesirable
for most users.  That's one reason why we are moving to add contract
descriptions to the command language.
________________________________________________________________

2.a Checking the feasibility of additional routes
________________________________________________________________

Your second question broke into two parts, and the answer to the
first is suggested by the route sets above; if an exchange is not
included in the product route sets provided by our load files,
then it's probably not a feasible route.  

Granted, our data about IB's product routes may be incomplete, and
you can check this via wildcard contract info queries, as described
in earier posts in the archives for this list.

If contract info doesn't show a route, then either your account
subscriptions are incomplete, or else IB simply doesn't offer that
product/route combination, presumably because the ecn doesn't
trade that product, or the volume is too low, or the symbol name
refers to another product.  In any case solving the problem is
outside the control of the shim, and so about which no more.

Given, however, the enormous number of possible routes described
by our data and supported by IB, this is not the common case problem.
________________________________________________________________

2.b Adding contracts for new routes
________________________________________________________________

In particular, how would a user trading in euros route any of ib
contracts 14079, 14084, 14081, or 14121 to IBIS, that is subscribe
to market data, or submit orders, for:

    14079 STK:ALV:EUR:IBIS
    14081 STK:BAS:EUR:IBIS
    14084 STK:BAY:EUR:IBIS
    14121 STK:DBK:EUR:IBIS

Ideally this issue should not arise.  If you read back far enough
in the mailing list, you'll know that once we support contract
descriptions in the command language, and given a default currency
set to EUR, market data subscriptions would as simple as:

    select tick STK:IBIS:ALV;
    select tick STK:IBIS:BAS;
    select tick STK:IBIS:BAY;
    select tick STK:IBIS:DBK;

Since the shim does not yet support contract expressions in its
command language, you still need to use explicit contract numbers.

Again, reading back in the mailing list archives, you might want
to use ibc contract ids, disambiguated by a default route from
the .shimrc file.  Since the command language currently only
accepts shim database contract uids, for now that doesn't work
either.

So, the best you can do at this point is to add entries to the
load file sql/mod/LocalSet.sql .  As it's name suggests, LocalSet
provides site-specific contracts.  And it's clear from your
email that you've already figured this out:

> Does this mean the only thing I would have to do to achieve my above
> mentioned goal would be to add some lines like the following to
> mod/LocalSet.sql...?
 
>   ('STK' , 'ALV'      , 'IBIS' , 'EUR', 'DE'),
>   ('STK' , 'BAS'      , 'IBIS' , 'EUR', 'DE'),
    ('STK' , 'BAY'      , 'IBIS' , 'EUR', 'DE'),
>   ('STK' , 'DBK'      , 'IBIS' , 'EUR', 'DE'),
 
Exactly!  This is just what we intended for you to do, and
what we intended would work.

Unfortunately, when I added these to sql/mod/LocalSet.sql as
a test, I found a problem for your example; the commands that
populate the Contract table, in sql/slow.sql, are too
restrictive, since there is a constraint in the where clause
limiting entries to the default route, so that the entries above
would be ignored.  A patch for slow.sql is provided below:

*** slow.sql	Fri Oct 17 14:39:54 2008
--- slow.new	Fri Oct 17 14:39:09 2008
***************
*** 21,27 ****
           or  SecType.type  = 'IND' or  SecType.type  = 'BOND')
          and  SecType.type  = LocalSet.type
          and LocalSet.route = Exchange.name
!         and LocalSet.route =   Static.route
          and LocalSet.name  =   Symbol.name and LocalSet.region = Symbol.region
          and LocalSet.curr  =   Symbol.curr and SecType.uid =  Symbol.tid
          and    Symbol.uid  =   Static.ncr  and SecType.uid =  Static.tid
--- 21,27 ----
           or  SecType.type  = 'IND' or  SecType.type  = 'BOND')
          and  SecType.type  = LocalSet.type
          and LocalSet.route = Exchange.name
! --      and LocalSet.route =   Static.route
          and LocalSet.name  =   Symbol.name and LocalSet.region = Symbol.region
          and LocalSet.curr  =   Symbol.curr and SecType.uid =  Symbol.tid
          and    Symbol.uid  =   Static.ncr  and SecType.uid =  Static.tid

I'll push a new release soon that includes this fix, but it will have
a new database version due to some unrelated changes I'm in the middle
of, so I'm not quite ready to put out the release.

If you are willing to apply the above patch, then, the fastest fix
for you will be to add entries to LocalSet.sql, modify slow.sql, and
repopulate your database via create.sql, or even setup.sql if so
desire.  You may modify slow.sql by deleting or commenting out the
Static.route constraint as indicated by the double dash comment
prefix in the patch above, or if you save the text of this email as,
say the file local.patch in the directory sql, via the patch program
as follows:

    patch < local.patch

Now, for other points from your post that don't fit neatly with the
two basic questions of checking for and adding routes.

> My problems of performing any queries simply derived from the fact
> that there was no such file called "syms.txt" whithin the shim sql
> directory ...

True, I did expect you to find that file as sql/syms.txt in the release
tar ball, because it was supposed to be there.  My mistake, and I've
added the name to our release script, so it will be included from now
on.  Thanks for letting me know about this.

> Given my complete lack of knowledge about MySQL usage it took me
> quite a long time to browse through tutorials, man pages and "batch
> mode" command lists until finally one of my trials did the job.
> The following command line worked for me and created the desired
> syms.txt file (fist part is simply copied from shim "create.sql"
> doc page):
 
> mysql -u code testing < bin/get_id.sql > syms.txt

Congratulations!  Its clear that our tutorial, once complete, should
include brief explanations of using the sql interpreter as a tool
with which to query the database.  I'm holding off work on the
tutorial for now since it falls out of date too quickly, as we add
features to the shim.  Thanks also for mentioning this documentation
{bug/feature request}.
 
> ... the only route the text file shows for german stock
> is "SMART".

True, and for more on this see above.

> ... [SMART as default] doesn't make much sense to me for two
> basic reasons:

Although you might reasonably request that we change the
default routes for the symbols you mention, I'd prefer not to
take that approach.  Compact code to choose default routes is
an instance of the set-cover problem, known to be NP-complete,
and using SMART where possible is the sword that cuts through
an otherwise thorny problem [the code for this is in
sql/bin/MergeEsets.sql, if you are interested].  Instead, as
noted above, we intend to eliminate the need to compute default
routes for the shim database entries by enhancing the command
language to accept contract expressions, so that routes may be
chosen dynamically within shim commands.

If *you* wish to make changes so that IBIS is default for German
equities, feel free.  For this case, you'l want to stay away
from the general-purpose code in the MergeEsets ruby script,
for the reasons mentioned above.  Instead, you'll want to update
the table Source after it's loaded via the files in sql/sym,
and *before* it's used to populate later tables, say by adding
the following statement at the top of sql/more.sql :

update Source
   set route = 'IBIS'
 where  type = 'STK'
   and  code = 'DE';

Then you would need to run create.sql again to make your changes
take effect.  By the way, if you want to understand better what
you are doing, sql/syms.i and sql/syms.sql include definitions
for the table Source.

> ... how to create a syms.txt file which includes
> stock/IBIS(as route) combinations for german stocks.

As noted above, the contents of syms.txt are an effect, not a
cause, with respect to the database contents.  Once you update
your database, either by adding to mod/LocalSet.sql, or else by
modifying the table population scripts to change the STK:DE route
default, via the update statement above, then you can extract
another syms.txt report, and look up the contract ids you need
in that.

Thanks,

Bill


More information about the ts-general mailing list