Coding, iPython

TOS Journal

@brberis
May 14, 2014

First stage of Trading journal for TOS project

Thinkorswim allows to export CSV format of history transactions, this is the easiest way to work with all transactions made during the day. 

The following code shows and idea how to import those CSV files into a Pandas data frame and modeling it to filter transactions by underlying and then populate a MySQL database.   

CSV module to import TOS report

	
	from pandas import Series, DataFrame
import pandas as pd

      
class CSVImporter:
    def __init__(self, filename, path='csv_files/'):
        self.filename = filename
        self.path = path

    def import_file(self):
        full_filename = self.path+self.filename
        f = open(full_filename)
        self.lines = f.readlines()
        return self.lines, full_filename

 
   
class TOSMethod(CSVImporter):
    def __init__(self, _report_name_, filename, path):
        super().__init__(filename, path)
        self._report_name_ = _report_name_
        self.reports={"Order History":["Account Order History\n", 1,
                                 "Account Trade History\n", -1],
                      "Trade History":["Account Trade History\n", 1,
                                 "Equities\n", -2],
                      "Cash Balance":["Cash Balance\n", 1,
                                 "Futures Statements\n", -2]
                      }

    def process_csv_report(self):
        
        self.lines, full_filename = self.import_file()
        try:
            for key, mark in self.reports.items():
                if key == self._report_name_:
                    s = self.lines.index(mark[0])+mark[1]
                    e = self.lines.index(mark[2])+mark[3]
        except ValueError:
            return "String not found"
        e = len(self.lines)-e
        report = pd.io.parsers.read_csv(full_filename, skiprows=s, skipfooter=e, header=0, engine='python')
        report = report.dropna(axis=1,how='all')
        return report

Gathering transactions

	
	class Transactions:

    def __init__(self, filename):
        self.filename = filename
        self.path = 'csv_files/'
        self.report_name = "Trade History"
        
    def process_trade_history(self):
        method = TOSMethod(self.report_name, self.filename, self.path)
        try:
            stm = method.process_csv_report()
            stm['Exec Time'] = stm['Exec Time'].fillna(method='ffill')
            stm = stm.drop(['Spread', 'Side', 'Order Type', 'Net Price'] ,1)
            toreplace={'ETF':'STOCK',
                   'ETN':'STOCK'
                   }
            for i, j in toreplace.items():
                stm['Type'] = stm['Type'].replace(i, j, regex=True)
            stm.columns = ['date', 'qty', 'pos_effect', 'symbol', 'exp', 'strike', 'type', 'price']
            stm.set_index('date', drop=True, inplace=False)
            stm = stm.fillna('')
            return stm
        except FileNotFoundError:
            print("File not found") 

Testing CVS importer

	
	        raw = Transactions("2014-09-08-AccountStatement.csv")
        stm = raw.process_trade_history()
        print(stm)

                date   qty pos_effect symbol      exp strike   type     price
0   8/11/14 08:51:09 -1000    TO OPEN    QQQ                  STOCK   95.2100
1   8/11/14 08:51:09  -100    TO OPEN    QQQ                  STOCK   95.2100
2   8/11/14 11:28:35  -905    TO OPEN    QQQ                  STOCK   95.7000
3   8/11/14 11:28:35   -95    TO OPEN    QQQ                  STOCK   95.7000
4   8/11/14 11:30:39    -4   TO CLOSE   AMZN  SEP1 14  307.5   CALL   14.8800
5   8/11/14 11:30:39     4   TO CLOSE   AMZN  SEP1 14    310   CALL   13.1400
6   8/11/14 11:33:45    -7   TO CLOSE    VXX   AUG 14     33    PUT    2.2500
7   8/11/14 11:33:45     7   TO CLOSE    VXX   AUG 14     32    PUT    1.4800
8   8/11/14 12:26:19    -6   TO CLOSE    IWM  AUG5 14    111   CALL    3.6300
9   8/11/14 12:26:19     6   TO CLOSE    IWM  AUG5 14    112   CALL    2.8900
10  8/11/14 12:46:07    20   TO CLOSE    QQQ  SEP2 14  100.5   CALL    0.1200
11  8/11/14 12:46:07    20   TO CLOSE    QQQ  SEP2 14   90.5    PUT    0.4200
12  8/11/14 13:34:16    -5   TO CLOSE    UNG  AUG5 14     21   CALL    1.0100
13  8/11/14 13:34:16     5   TO CLOSE    UNG  AUG5 14     22   CALL    0.4600
14  8/11/14 13:42:44    -4    TO OPEN    DVN   SEP 14     80   CALL    0.5300
15  8/11/14 13:42:44    -4    TO OPEN    DVN   SEP 14     70    PUT    0.7400
16  8/11/14 14:26:26   -10    TO OPEN   AAPL   SEP 14    105   CALL    0.6100
17  8/11/14 14:26:26   -10    TO OPEN   AAPL   SEP 14   87.5    PUT    0.6700
18  8/11/14 14:49:02   -10    TO OPEN   YHOO   SEP 14     40   CALL    0.7400
19  8/11/14 14:49:02   -10    TO OPEN   YHOO   SEP 14     31    PUT    0.2700
20  8/12/14 11:19:02   200   TO CLOSE    QQQ                  STOCK   95.1300
21  8/12/14 11:19:02   200   TO CLOSE    QQQ                  STOCK   95.1300
22  8/12/14 11:19:02   100   TO CLOSE    QQQ                  STOCK   95.1300
23  8/12/14 11:19:02  1400   TO CLOSE    QQQ                  STOCK   95.1300
24  8/12/14 11:19:02   200   TO CLOSE    QQQ                  STOCK   95.1300
25  8/12/14 11:21:23    10   TO CLOSE    UAL  SEP1 14     52   CALL    0.1600
26  8/12/14 11:21:23    10   TO CLOSE    UAL  SEP1 14     39    PUT    0.6100
27  8/12/14 11:21:43    10   TO CLOSE    UTX  SEP2 14    110   CALL    0.3500
28  8/12/14 11:21:43    10   TO CLOSE    UTX  SEP2 14     97    PUT    0.3800
29  8/13/14 09:52:37  -100    TO OPEN   TSLA                  STOCK  263.6700
30  8/13/14 10:01:50   -20    TO OPEN   YHOO   SEP 14     41   CALL    0.7000
31  8/13/14 10:01:50   -20    TO OPEN   YHOO   SEP 14     31    PUT    0.3500
32  8/13/14 11:46:53 -1000    TO OPEN    QQQ                  STOCK   96.3500
33  8/13/14 11:48:41 -1000    TO OPEN    QQQ                  STOCK   96.3400
34  8/13/14 13:28:56   100   TO CLOSE   TSLA                  STOCK  261.5652
35  8/14/14 11:14:35    10   TO CLOSE   AAPL   SEP 14    105   CALL    0.6000
36  8/14/14 11:14:35    10   TO CLOSE   AAPL   SEP 14   87.5    PUT    0.3600
37  8/14/14 13:23:33    10   TO CLOSE   YHOO   SEP 14     40   CALL    0.7300
38  8/14/14 13:23:33    10   TO CLOSE   YHOO   SEP 14     31    PUT    0.1900
39  8/14/14 13:24:04    20   TO CLOSE   YHOO   SEP 14     41   CALL    0.5500
40  8/14/14 13:24:04    20   TO CLOSE   YHOO   SEP 14     31    PUT    0.1900
41  8/14/14 13:34:26     1    TO OPEN    ALL   SEP 14   62.5    PUT    2.4900
42  8/14/14 13:34:26    -1    TO OPEN    ALL   SEP 14   57.5    PUT    0.2500
43  8/14/14 14:55:20  -583    TO OPEN    QQQ                  STOCK   96.8300
44  8/14/14 14:55:20  -417    TO OPEN    QQQ                  STOCK   96.8300
45  8/15/14 15:58:10  -100    TO OPEN    QQQ                  STOCK   97.4100
46  8/15/14 15:58:10  -300    TO OPEN    QQQ                  STOCK   97.4100
47  8/15/14 16:12:36  -600    TO OPEN    QQQ                  STOCK   97.4000
48  8/18/14 11:48:45     4   TO CLOSE    DVN   SEP 14     80   CALL    0.1600
49  8/18/14 11:48:45     4   TO CLOSE    DVN   SEP 14     70    PUT    0.6100
50  8/19/14 13:36:38  -500    TO OPEN    SPY                  STOCK  198.2900
51  8/19/14 16:02:53     5    TO OPEN    VIX   SEP 14     11   CALL    2.4300

Populating database

	
	import sqlite3
from pandas import Series, DataFrame
import pandas as pd
from random import randint

class DataFrameSQL:
    def __init__(self, database, table, df):
        self.database = database
        self.table = table
        self.df = df
        
    def append_df(self):
        '''Append DF without key and uniques rows'''
        cnx = sqlite3.connect(self.database)
        self.df, code = self.insert_batch_code()
        db_line=[]
        colums_name = list(self.df.columns.values)
        for i in range(len(self.df)):
            ss = ''; ls = ''
            for col in colums_name[0:(len(colums_name)-1)]: 
                ss += "("+col+" = '"+str(self.df[col][i])+"".join(["') and " if col != colums_name[-2] else "') and ("+colums_name[-1]+" != "+str(code)+") "])
                ls += " "+str(self.df[col][i])
            db_line = pd.io.sql.read_sql("select * from trade_history where "+ss , cnx)
            if db_line.empty:
                pd.io.sql.to_sql(df[i:i+1], self.table, con=cnx, if_exists='append', index=False)
            else:
                print("Already exists "+ls)
                
    def insert_batch_code(self):
        code = randint(100000, 999999)
        try:
            self.df = self.df.drop('batch_code',1)
        except Exception:
            pass 
        self.df.insert(8, column='batch_code', value = code)
        return (self.df, code)

	
	dtsql = DataFrameSQL("data/journal.db", "trade_history", stm)
dtsql.append_df()
Already exists  8/11/14 08:51:09 -1000 TO OPEN QQQ   STOCK 95.21
Already exists  8/11/14 08:51:09 -100 TO OPEN QQQ   STOCK 95.21
Already exists  8/11/14 11:28:35 -905 TO OPEN QQQ   STOCK 95.7
Already exists  8/11/14 11:28:35 -95 TO OPEN QQQ   STOCK 95.7
Already exists  8/11/14 11:30:39 -4 TO CLOSE AMZN SEP1 14 307.5 CALL 14.88
Already exists  8/11/14 11:30:39 4 TO CLOSE AMZN SEP1 14 310.0 CALL 13.14
Already exists  8/11/14 11:33:45 -7 TO CLOSE VXX AUG 14 33.0 PUT 2.25
Already exists  8/11/14 11:33:45 7 TO CLOSE VXX AUG 14 32.0 PUT 1.48
Already exists  8/11/14 12:26:19 -6 TO CLOSE IWM AUG5 14 111.0 CALL 3.63
Already exists  8/11/14 12:26:19 6 TO CLOSE IWM AUG5 14 112.0 CALL 2.89
Already exists  8/11/14 12:46:07 20 TO CLOSE QQQ SEP2 14 100.5 CALL 0.12
Already exists  8/11/14 12:46:07 20 TO CLOSE QQQ SEP2 14 90.5 PUT 0.42
Already exists  8/11/14 13:34:16 -5 TO CLOSE UNG AUG5 14 21.0 CALL 1.01
Already exists  8/11/14 13:34:16 5 TO CLOSE UNG AUG5 14 22.0 CALL 0.46
Already exists  8/11/14 13:42:44 -4 TO OPEN DVN SEP 14 80.0 CALL 0.53
Already exists  8/11/14 13:42:44 -4 TO OPEN DVN SEP 14 70.0 PUT 0.74
Already exists  8/11/14 14:26:26 -10 TO OPEN AAPL SEP 14 105.0 CALL 0.61
Already exists  8/11/14 14:26:26 -10 TO OPEN AAPL SEP 14 87.5 PUT 0.67
Already exists  8/11/14 14:49:02 -10 TO OPEN YHOO SEP 14 40.0 CALL 0.74
Already exists  8/11/14 14:49:02 -10 TO OPEN YHOO SEP 14 31.0 PUT 0.27
Already exists  8/12/14 11:19:02 200 TO CLOSE QQQ   STOCK 95.13
Already exists  8/12/14 11:19:02 200 TO CLOSE QQQ   STOCK 95.13
Already exists  8/12/14 11:19:02 100 TO CLOSE QQQ   STOCK 95.13
Already exists  8/12/14 11:19:02 1400 TO CLOSE QQQ   STOCK 95.13
Already exists  8/12/14 11:19:02 200 TO CLOSE QQQ   STOCK 95.13
Already exists  8/12/14 11:21:23 10 TO CLOSE UAL SEP1 14 52.0 CALL 0.16
Already exists  8/12/14 11:21:23 10 TO CLOSE UAL SEP1 14 39.0 PUT 0.61
Already exists  8/12/14 11:21:43 10 TO CLOSE UTX SEP2 14 110.0 CALL 0.35
Already exists  8/12/14 11:21:43 10 TO CLOSE UTX SEP2 14 97.0 PUT 0.38
Already exists  8/13/14 09:52:37 -100 TO OPEN TSLA   STOCK 263.67
Already exists  8/13/14 10:01:50 -20 TO OPEN YHOO SEP 14 41.0 CALL 0.7
Already exists  8/13/14 10:01:50 -20 TO OPEN YHOO SEP 14 31.0 PUT 0.35
Already exists  8/13/14 11:46:53 -1000 TO OPEN QQQ   STOCK 96.35
Already exists  8/13/14 11:48:41 -1000 TO OPEN QQQ   STOCK 96.34
Already exists  8/13/14 13:28:56 100 TO CLOSE TSLA   STOCK 261.5652
Already exists  8/14/14 11:14:35 10 TO CLOSE AAPL SEP 14 105.0 CALL 0.6
Already exists  8/14/14 11:14:35 10 TO CLOSE AAPL SEP 14 87.5 PUT 0.36
Already exists  8/14/14 13:23:33 10 TO CLOSE YHOO SEP 14 40.0 CALL 0.73
Already exists  8/14/14 13:23:33 10 TO CLOSE YHOO SEP 14 31.0 PUT 0.19
Already exists  8/14/14 13:24:04 20 TO CLOSE YHOO SEP 14 41.0 CALL 0.55
Already exists  8/14/14 13:24:04 20 TO CLOSE YHOO SEP 14 31.0 PUT 0.19
Already exists  8/14/14 13:34:26 1 TO OPEN ALL SEP 14 62.5 PUT 2.49
Already exists  8/14/14 13:34:26 -1 TO OPEN ALL SEP 14 57.5 PUT 0.25
Already exists  8/14/14 14:55:20 -583 TO OPEN QQQ   STOCK 96.83
Already exists  8/14/14 14:55:20 -417 TO OPEN QQQ   STOCK 96.83
Already exists  8/15/14 15:58:10 -100 TO OPEN QQQ   STOCK 97.41
Already exists  8/15/14 15:58:10 -300 TO OPEN QQQ   STOCK 97.41
Already exists  8/15/14 16:12:36 -600 TO OPEN QQQ   STOCK 97.4
Already exists  8/18/14 11:48:45 4 TO CLOSE DVN SEP 14 80.0 CALL 0.16
Already exists  8/18/14 11:48:45 4 TO CLOSE DVN SEP 14 70.0 PUT 0.61
Already exists  8/19/14 13:36:38 -500 TO OPEN SPY   STOCK 198.29
Already exists  8/19/14 16:02:53 5 TO OPEN VIX SEP 14 11.0 CALL 2.43

This output means this file was imported before.

blog comments powered by Disqus