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

[R P Herrold]

On Thu, 16 Oct 2008, Paolo Volpato wrote:

> Hello Bill,

Bill, and I, left around 1800 US EDT today -- 2200 GMT

> 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).

Great -- We found a small issue with a change by IB on one 
security we test against (YM.FUT.ECBOT.Dec08) -- it seems the 
expiration date' changed, and this (and the market gyrations) 
have us on the edge our seats.

> However, this doesn't mean I'm able to retrieve exactly those
> historical data I'm searching for by now...

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

hmmm --- I think we decided to move it a couple weeks ago, as 
part of preparation for packaging under Debian, and into SRPMs 
for SuSE and CentOS.  I'll check tomorrow when O am back at my 
desk.

> 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.)

I do not think that the Makefile does this -- it perhaps 
could, but there is a problem that the database keying may not 
be set up yet. This requires thought.

> 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

great -- Bill and I are each old hands with databases, and the 
learning curve could be steep.  ;(

> 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.

Not surprising, as we have never traded in that market, and my 
German is a slight reading knowledge and not much more.  THANK 
YOU for the report -- we'll get it worked out together.

> 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
> reasons:

> 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.

wow -- totally unexpected by us.  In the US markets, there is 
no relevant price differential, and so we had no reason to 
prefer one market to another.  This will require thought as 
well.

>   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.

I assume you would like to be able to look at data retrieved 
from each Exchange's record of trades.   This may require our 
extension of the 'bind' operator, to be attainable.  Another 
approach may be needed -- we will discuss.

> Which of the several
>   trading fees should be used to calculate the performance of
>   trading strategies routed to "SMART" ?

It may be that I need to look at the fee schedule published by 
IB -- the API does not have any way for the shim to 'learn' 
this data so far as I know.

> 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.

So, the first exchange you look at is probably IBIS/XETRA I 
assume.

> 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.

We may well have some new questions, and the data may be 'most 
of the way' there presently.  Stand by until we can look into 
this.   It may be something that a slight cnaange in how the 
exchange is specified can fix.

> 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' , 'DBK'      , 'IBIS' , 'EUR', 'DE'),

I think so, but I will await Bill's post on this to be sure

> Thanks in advance for your patience. ;)

I certainly am glad for your help -- Thank you.

-- Russ herrold