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

Paolo Volpato dormilon at web.de
Wed Oct 15 20:30:00 EDT 2008

Hello Bill,

just wanted to let you know that I finally managed to make history
queries work for my setup (currently TWS v. 887.2 + shim-081003).
However, this doesn't mean I'm able to retrieve exactly those
historical data I'm searching for by now...

 > To abbreviate a long story to a not very short one:
 > ...
 > ...
 >  7.  for now you can look up the shim database contract record
 >      uid, e.g., 142 for YM above, as follows:
 >      a. in the file syms.txt, using grep or your favorite editor; or
 >      b. by running the query bin/get_ids.sql, which was used to
 >         produce the file syms.txt; or
 >      c. adapting the select statements in bin/get_ids.sql to your
 >         own purposes.

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 I could have looked into until I finally succeeded in
creating one on my own. (Your posting suggests there should have been
one, so I keep wondering why shim didn't create it on this machine.)

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

Anyway, instead of happily dumping historical data now I'm facing
another problem: The created "syms.txt" file doesn't contain the
(supposedly most common) stock/route combination for german stock.
Just some lines copied from my "syms.txt" to show waht I mean:

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

As you can see, the only route the text file shows for german stock
is "SMART". Now this doesn't make much sense to me for two basic
1. The trading fees of German stock exchanges differ a lot, so it's
   rather unusual routing stock orders to "SMART" over here and thus
   risking to pay 3 or 4 times higher fees than necessary, even less
   when talking about people who perform lots of trades every day.
   This also implies historical data mixed up from several german
   stock exchanges as done in "SMART" is not very useful when trying
   to perform backtests based upon that data. Which of the several
   trading fees should be used to calculate the performance of
   trading strategies routed to "SMART" ?
2. When talking about stocks of large capitalized german companies,
   usually a 95-98 % of the daily volume is traded on "IBIS"
   (aka. XETRA), so the need of routing "SMART" just to catch those
   remaining 2-5% doesn't really look that big to me.

In other words: Please somebody let me know how to create a
syms.txt file which includes stock/IBIS(as route) combinations for
german stocks.

 > ...
 > If the above is confusing, just focus on (7); look up the record id,
 > which is the first column in syms.txt, and make sure you regenerate
 > that file if you have changed your local symbol set in any way, say
 > by recreating the database after updates to mod/LocalSet.sql or
 > mod/LocalFut.sql...

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

('STK' , 'ALV'      , 'IBIS' , 'EUR', 'DE'),
('STK' , 'BAS'      , 'IBIS' , 'EUR', 'DE'),
('STK' , 'DBK'      , 'IBIS' , 'EUR', 'DE'),

Thanks in advance for your patience. ;)

Best regards,



More information about the ts-general mailing list