[ts-gen] Handling commissions and fees

Ken Feng kfmfe04 at gmail.com
Thu Sep 17 09:42:06 EDT 2009


Thank you, so much, Michael, for sharing!

You just saved me at least a week or two worth of work.
Much appreciated.

Regards,
Ken

On 9/17/09, Michael Gruen <gruenm at gmail.com> wrote:
> Folks,
>
> Sorry for delay, I only caught the thread now - even with a
> security-card-active account, the web service does not require login at all.
>
> It works like this:
>
> - in Account Management, you set up the Flex service, and generate a "token"
> of some ~30 characters.
> - you also set up a report(s) with desired fields, and IB associates an
> ID(s) with it.
> - With this token and query ID, you access a xml URL, which starts the
> report execution and gives a "resultset-ID".
> - After waiting a bit for execution, you then access a different URL, with
> the resultset-ID, and retrieve the results
>
> so after setting up the Flex service and report(s), no login is needed at
> all, security is handled with the "token code".  I believe there is also an
> option to limit access to specific originating IPs
>
> below is my python code which may make the process clearer (sorry I'm a
> beginner Python writer, so it may not be the most elegant - specifically, I
> write to a local file and then import to a database, I could not get Python
> to read directly to sqlite)
>
>
>
>
> updateDB.py
> -------------------------------------------------------------------------------------------------------------------------------------------------------------
> import sqlite3, csv, urllib.request, urllib.parse, urllib.error, re, time
>
> conn = sqlite3.connect("mydb9.sqlite")
> cur = conn.cursor()
>
> token = 632xxxxxxxxxxxxxxxx53730 ; query = 20xxx # this is the token issued
> by IB
>
> url = "
> https://www.interactivebrokers.com/Universal/servlet/FlexStatementService.SendRequest?t=%s&q=%s"
> % (token, query)
> ibfile = urllib.request.urlopen(url).read().decode('utf-8')
>
> pattern = re.compile(r'\d{10}')  #Search for 10-digit reference code
> (resultset code)
> m = pattern.search(ibfile)
> reference_code = (m.group()) #retrieve reference code
>
> url = "
> https://www.interactivebrokers.com/Universal/servlet/FlexStatementService.GetStatement?q=%s&t=%s&v=2"
> % (reference_code, token)
>
> pattern = re.compile(r'in progress')  #If report is not completed, IB
> returns an <in progress> message
> for i in range(5): #try 5 times, 40s apart
>     print('Try ', i,)
>     time.sleep(40)
>     ibfile = urllib.request.urlopen(url).read().decode('utf-8')
>     if pattern.search(ibfile) == None:
>         break
> print('Succesfully retrieved report')
>
> ibfile_object = open('ib.csv', 'w')
> ibfile_object.write(ibfile.replace(',',''))
> ibfile_object.close()
> print('Saved file')
>
> time.sleep(2) # Give the OS time to close the file
>
>
> # Create table
> cur.execute('''drop table IF EXISTS tbIB''')
> sql = '''create table IF NOT EXISTS tbIB (
> Symbol          TEXT,
> Description     TEXT,
> AssetClass      TEXT,
> TradeID         INTEGER,
> TradeDate       INTEGER,
> TradeTime       INTEGER,
> Quantity        INTEGER,
> Multiplier       INTEGER,
> TradePrice       REAL,
> TradeMoney       REAL,
> NetCash          REAL,
> Taxes            REAL,
> IBCommission     REAL,
> CostBasis        REAL,
> FifoPnlRealized  REAL,
> NotesCodes       TEXT
> )'''
>
> cur.execute(sql)
>
> cur.execute('''create unique index IF NOT EXISTS idx_TradeID ON
> tbIB(TradeID)''')
>
>
> ibfile_object = open('ib.csv', 'rt')
>
> csvread = csv.reader(ibfile_object, delimiter='|', quotechar='"')
> for line in csvread:
>     if line != []:
>         cur.execute('insert OR IGNORE into tbIB values
> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', line)
>
> conn.commit()
>
> cur.execute('ALTER TABLE tbIB ADD COLUMN oldSymbol           TEXT')
> cur.execute('ALTER TABLE tbIB ADD COLUMN Underlying          TEXT')
> cur.execute('ALTER TABLE tbIB ADD COLUMN OptionType          TEXT')
> cur.execute('ALTER TABLE tbIB ADD COLUMN QtyInShares         INTEGER')
> cur.execute('ALTER TABLE tbIB ADD COLUMN QtyOpening          INTEGER
> DEFAULT 0')
> cur.execute('ALTER TABLE tbIB ADD COLUMN QtyClosing          INTEGER
> DEFAULT 0')
> cur.execute('ALTER TABLE tbIB ADD COLUMN QtyCumulative       INTEGER
> DEFAULT 0')
> cur.execute('ALTER TABLE tbIB ADD COLUMN QtyStillOpenTrx     INTEGER
> DEFAULT 0')
> cur.execute('ALTER TABLE tbIB ADD COLUMN QtyWashMatched      INTEGER
> DEFAULT 0')
> cur.execute('ALTER TABLE tbIB ADD COLUMN BasisAdjWash        Real
> DEFAULT 0')
> cur.execute('ALTER TABLE tbIB ADD COLUMN BasisAdjOption      Real
> DEFAULT 0')
> cur.execute('ALTER TABLE tbIB ADD COLUMN Trace               Text')
> cur.execute('ALTER TABLE tbIB ADD COLUMN TraceWash           Text')
> cur.execute('ALTER TABLE tbIB ADD COLUMN TraceOption         Text')
>
>
> cur.close()
> conn.close()
> ----------------------------------------------------------------------------------------------------------------------------------
>
>
>
>
>
>
> On Thu, Sep 17, 2009 at 06:35, Ken Feng <kfmfe04 at gmail.com> wrote:
>
>> On 9/17/09, Paul C <paulq2o0 at yahoo.co.uk> wrote:
>> >
>> > If the TWS is already running, it seems you can get to the IB website
>> > Account Management without logging in again (i.e. without using the
>> > security device a second time) by choosing "View->Account Management"
>> > from the *TWS* menu.  It should then just appear in your browser (albeit
>> > you may not know it, until you go find the browser window).  So if
>> > you're running all week, maybe you're ok? - unless I'm misunderstanding
>> > something.
>> >
>> > ( nb, I've just managed to automate retrieval of Trade Records from
>> > the TWS 'Trades' page using the perl/ X11::GUITest approach, but it's
>> > probably not robust, and is maybe asking for trouble, so I won't
>> > elaborate here - it's not really shim related anyway.  The fact that
>> > it's wrapped in html tags I don't see as a problem, and you can get
>> > the Order_ID/Ref too, so presumably can associate a commission with an
>> > order sent via the shim [and hence in a MySql table]. But it involves
>> > sending keystrokes to the running TWS, which I don't much like. )
>>
>> That's great!  I wasn't aware of that feature - I thought I had to do
>> it through the website by hand!
>>
>> Since I am already using Jemmy to manage the perpetual log-in, I can
>> use it to bring up the browser - I need to figure out how to scrape it
>> at that point.  I will try to use Jemmy or some java-related tool
>> since I'm using it to drive TWS.
>>
>> Anyhow, thank you for pointing to a way out of this conundrum!
>>
>> - Ken
>> _______________________________________________
>> ts-general mailing list
>> ts-general at trading-shim.org
>> http://www.trading-shim.org/mailman/listinfo/ts-general
>>
>


More information about the ts-general mailing list