[ts-gen] Handling commissions and fees

Thu Sep 17 09:30:55 EDT 2009

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.trading-shim.org/pipermail/ts-general/attachments/20090917/4eaf2419/attachment.html 