[ts-gen] Contract id numbering

[Bill Pippin]

Sam,

This message tries to deal with three distinct topics: bitrot to
the exs/past.30.rb script; future design plans for symbolic
contract expressions, which are not yet implemented; and contract
id numbering for the present.

If this is confusing, please make sure that you find the example
query near the end of this post, and run it.  This will give you
a flat file you can refer to in order to determine contract ids,
and that's the key take home message for now.

About using the download history example script, please note that,
due to significant changes to the security symbol relations in the
trading-shim database, the script exs/past.30.rb does not work
correctly; there have been changes to the dow since it was written,
so not all of the 30 symbols it queries for are correct.  We intend
to fix this specific problem by adding stock index tables to the
database, to decouple index sets from the contract number sequencing. 

> So, I've tried to use Shim, read that:
> http://www.trading-shim.org/pipermail/ts-general/2008-March/000168.html,
> and tried to do the same thing, to get some historical data. ...
> When I try to get the first Dow30 stock historical data ...
 
This is also a symptom of a larger problem, which is that using 
contract numbers to identify symbols is error prone, due in part
to changes over time to the products offered by exchanges.  Features
are being added to the shim to allow you to specify contracts
symbolically, but that work is not finished yet.

I'm not sure what's happening with your setup; I'm not sure whether
you're getting the contract ids you should be expecting or not.
Since you still need to use contract numbers for now, I believe the
key issue for you is, how to determine what the contract id is for a
particular symbol? 

In order to check the contract numbering for the database as you
have set it up, use the bin/get_id.sql query to lookup contract
numbers.

E.g., if your dbms server is on the host named mydb, and you've
retained the default database names of testing and trading, and
account names of code, shim, and data, then from the directory
sql, run:

    mysql -u code -h mydb testing < bin/get_id.sql > cids

This runs the contract query in bin/get_id.sql through the mysql
interpreter, and saves the resulting join to a file.

With the current release, the file cids will have around 30k records.
For us, when I run the query for our db host, the first 40-odd lines
I get are as follows:

cid	ibid	type	route	name	curr	region
1	4005	STK	SMART	AA	USD	US
2	4301	STK	SMART	AIG	USD	US
3	4350	STK	SMART	HON	USD	US
4	4721	STK	SMART	AXP	USD	US
5	4762	STK	SMART	BA	USD	US
6	4901	STK	SMART	VZ	USD	US
7	4957	STK	SMART	BGC	USD	US
8	5111	STK	SMART	BMY	USD	US
9	5437	STK	SMART	CAT	USD	US
10	5684	STK	SMART	CVX	USD	US
11	6100	STK	SMART	CSC	USD	US
12	6366	STK	SMART	DD	USD	US
13	6459	STK	SMART	DIS	USD	US
14	6522	STK	SMART	DOW	USD	US
15	6585	STK	SMART	DTE	USD	US
16	6765	STK	SMART	EK	USD	US
17	6813	STK	SMART	EMC	USD	US
18	7516	STK	SMART	GE	USD	US
19	7661	STK	SMART	GM	USD	US
20	7930	STK	SMART	HD	USD	US
21	8082	STK	SMART	HNZ	USD	US
22	8314	STK	SMART	IBM	USD	US
23	8719	STK	SMART	JNJ	USD	US
24	8894	STK	SMART	KO	USD	US
25	9245	STK	SMART	LTD	USD	US
26	9408	STK	SMART	MCD	USD	US
27	9720	STK	SMART	MMM	USD	US
28	9769	STK	SMART	MO	USD	US
29	9822	STK	SMART	MRK	USD	US
30	10098	STK	SMART	BAC	USD	US
31	10512	STK	SMART	NSM	USD	US
32	11031	STK	SMART	PFE	USD	US
33	11054	STK	SMART	PG	USD	US
34	11697	STK	SMART	ROK	USD	US
35	11758	STK	SMART	RTN	USD	US
36	11905	STK	SMART	SCHW	USD	US
37	12205	STK	SMART	SLE	USD	US
38	12991	STK	SMART	C	USD	US
39	13096	STK	SMART	TXN	USD	US
40	13364	STK	SMART	UTX	USD	US
41	13805	STK	SMART	WMB	USD	US
42	13824	STK	SMART	WMT	USD	US
43	13977	STK	SMART	XOM	USD	US
...

I've pushed another release, so that you are able to synchronize
your database setup with our current developmental system.  Once
you have downloaded the tarball for today, and recreated
the database by running setup.sql, running the bin/get_id.sql
script should produce results beginning as above.

Please let us know on the list whether this is the case or
not, and please feel free to ask if you have more questions.

Thanks,

Bill