def setUpClass(cls):
'''
Open up a bunch of statments and add them to a test database for testing stuff
TODO: Develop the randomtradgenerator write trades to the db for more generic testing
'''
settings = QSettings('zero_substance', 'structjour')
for i, name in enumerate(cls.infiles):
name = os.path.join(cls.datadir, name)
x, cls.inputType = getStatementType(name)
print(cls.inputType)
if cls.inputType == 'DAS':
ds = DasStatement(name, settings, cls.thedates[i])
ds.getTrades(testFileLoc=cls.datadir, testdb=cls.testdb)
elif cls.inputType == "IB_CSV":
ibs = IbStatement(db=cls.testdb)
ibs.openIBStatement(name)
else:
continue
# self.assertTrue(4 == 5, "Unsupported file type in test_TheTradeObject")
statement = StatementDB(db=cls.testdb)
df = statement.getStatement(cls.thedates[i])
# self.assertFalse(df.empty, f"Found no trades in db on {daDate}")
dtrade = DefineTrades(cls.inputType)
dframe, ldf = dtrade.processDBTrades(df)
tto = TheTradeObject(ldf[0], False, SumReqFields())
cls.ttos.append(tto)
def test_findTrade(self):
'''
Tests find a unique trade using findTrade with date, ticker, shares and account.
The method is meant to be more exclusive than inclusive.
'''
rc = FinReqCol()
ibdb = StatementDB()
row = {
rc.ticker: 'SNRK',
"DateTime": '20191212;093145',
rc.shares: 3000,
rc.price: 150.23,
rc.comm: None,
rc.oc: 'O',
rc.acct: "U2229999",
rc.bal: 3000,
rc.avg: 150.23,
rc.PL: None,
"DAS": 'DAS',
"IB": None}
data = list(row.values())
columns = list(row.keys())
x = pd.DataFrame(data=[data], columns=columns)
ibdb.insertTradeSA(x.iloc[0])
ModelBase.session.commit()
# conn.commit()
foundit = ibdb.findTradesSA(x.iloc[0]['DateTime'], x.iloc[0][rc.ticker], x.iloc[0][rc.shares], x.iloc[0][rc.acct])
self.assertTrue(foundit)
bu = Backup()
bu.restore()
def test_figureBAPL(self):
'''
figureBAPL is called by openIBStatement and is give the trade tables and
position tables from an IB statement. figureBAPL fills in Balance, Average
and pnl. Every Balance entry should be filled. There could be blanks for
Average. Here we can test all balance entries made it to the db
'''
ibs = IbStatement()
ibdb = StatementDB()
ibdb.reinitializeTradeTables()
# These two files are a multiday flex and an activity.
# Find a way to find recent files to test
# trades1, meta1 = ibs.openIBStatement(self.testfile1)
trades2, meta2 = ibs.openIBStatement(self.testfile2)
len2 = len(trades2['TRNT']) if 'TRNT' in trades2.keys() else len(
trades2['Trades'])
ModelBase.connect(new_session=True)
session = ModelBase.session
q = session.query(Trade).all()
q2 = session.query(Trade).filter(Trade.balance is None).all()
self.assertEqual(len2, len(q))
self.assertEqual(len(q2), 0)
bu = Backup()
bu.restore()
def saveTheTradeObject(self, name):
'''pickle tto list'''
# I believe this renders this toothless. Next step is delete it after awaiting
# errors as fallout
if not self.ts or not self.entries:
return
ibdb = StatementDB()
self.ts = ibdb.updateTradeSummaries(self.ts)
self.sc.ui.useDatabase.setChecked(True)
def openIBStatementHtml(self, infile):
'''
Open an IB Statement in html form
'''
if not os.path.exists(infile):
return
soup = BeautifulSoup(readit(infile), 'html.parser')
tbldivs = soup.find_all("div", id=lambda x: x and x.startswith('tbl'))
title = soup.find('title').text
self.parseTitle(title)
tables = dict()
tablenames = dict()
for tableTag in tbldivs:
continueit = True
tabKey = ''
for key in [
'tblAccountInformation', 'tblOpenPositions',
'tblLongOpenPositions', 'tblShortOpenPositions',
'tblTransactions', 'tblTrades'
]:
if tableTag['id'].startswith(key):
continueit = False
tabKey = key[3:]
break
if continueit:
continue
tab = tableTag.find("table")
if not tab:
continue
df = pd.read_html(str(tab))
assert len(df) == 1
df = df[0] # .replace(np.nan, '')
tables[tabKey] = df
if 'Transactions' not in tables.keys() and 'Trades' not in tables.keys(
):
msg = 'The statment lacks a trades table.'
return dict(), msg
self.doctorHtmlTables(tables)
posTab = None
if 'OpenPositions' in tables.keys():
posTab = tables['OpenPositions']
tables['Trades'] = self.figureBAPL(tables['Trades'], posTab)
ibdb = StatementDB(source='IB', db=self.db)
ibdb.processStatement(tables['Trades'],
self.account,
self.beginDate,
self.endDate,
openPos=posTab)
for key in tables:
tablenames[key] = key
tablenames[tabKey] = tabKey
return tables, tablenames
return dict(), 'This statement lacks any overnight information.'
def setUpClass(cls):
bu = Backup()
bu.backup()
if ModelBase.session:
ModelBase.session.rollback()
ddiirr = os.path.dirname(__file__)
os.chdir(os.path.realpath(ddiirr + '/../'))
outdir = 'test/out'
cls.outdir = os.path.realpath(outdir)
cls.db = 'data/testdb.sqlite'
cls.db = os.path.realpath(cls.db)
if os.path.exists(cls.db):
clearTables(cls.db)
cls.rtg = RTG(db=cls.db)
# cls.dates = ['20200203 09:30', '20200204 07:30', '20200205 09:35', '20200206 11:40', '20200207 10:39']
cls.dates = ['20200207 10:39']
cls.infiles = cls.rtg.saveSomeTestFiles(cls.dates,
cls.outdir,
strict=True,
overwrite=True)
settings = QSettings('zero_substance', 'structjour')
for i, name in enumerate(cls.infiles):
name = os.path.join(cls.outdir, name)
x, cls.inputType = getStatementType(name)
# print(cls.inputType)
if cls.inputType == 'DAS':
ds = DasStatement(name, settings, cls.dates[i])
ds.getTrades(testFileLoc=cls.outdir, testdb=cls.db)
elif cls.inputType == "IB_CSV":
ibs = IbStatement(db=cls.db)
ibs.openIBStatement(name)
else:
continue
# self.assertTrue(4 == 5, "Unsupported file type in test_TheTradeObject")
statement = StatementDB(db=cls.db)
df = statement.getStatement(cls.dates[i])
# self.assertFalse(df.empty, f"Found no trades in db on {daDate}")
dtrade = DefineTrades(cls.inputType)
dframe, ldf = dtrade.processDBTrades(df)
# tto = TheTradeObject(ldf[0], False, SumReqFields())
jf = JournalFiles(indir=cls.outdir,
outdir=outdir,
theDate=cls.dates[i],
infile=name)
cls.sc = SumControl()
lf = LayoutForms(cls.sc, jf, dframe)
lf.runTtoSummaries(ldf)
cls.jfs.append(jf)
cls.dframes.append(dframe)
# cls.ttos.append(tto)
cls.ldfs.append(ldf)
cls.lfs.append(lf)
def saveTheTradeObject(self, name):
from structjour.statements.statementcrud import TradeCrud
if not self.ts or not self.entries:
return
ibdb = StatementDB()
tcrud = TradeCrud()
self.ts = ibdb.updateTradeSummariesSA(self.ts)
# self.ts = ibdb.updateTradeSummaries(self.ts)
# tcrud.updateTradeSummaries(self.ts)
self.sc.ui.useDatabase.setChecked(True)
def test_addTradeSummaries(self):
'''
Tests addTradeSummaries. The method requires trades are already in the database.
We achieve that with openStuff.
For this test, I load everything (openStuff) and run
addTradeSummaries on all covered days. Its slow. Could be partially sqlite but
all the APL BAPL stuff is probably the main crawler. In practice, this will add
daily or monthly statements. And in running the program there is no way to run
the trade summaries in mass. Its desinged to load up a single day. ITs day-trader
centric. Let it stay slow for now.
'''
ibdb = StatementDB(self.db)
self.clearTables()
ibs, x = self.openStuff()
# ibdb.getUncoveredDays
covered = ibdb.getCoveredDays()
for count, day in enumerate(covered):
df = ibdb.getStatement(day)
if not df.empty:
tu = DefineTrades("DB")
dframe, ldf = tu.processDBTrades(df)
tradeSummaries, ts, entries, initialImageNames = runSummaries(ldf)
ibdb.addTradeSummaries(ts, ldf)
summaries = ibdb.getTradeSumByDate(day)
for summary in summaries:
summary = ibdb.makeTradeSumDict(summary)
entryTrades = ibdb.getEntryTrades(summary['id'])
self.assertGreater(len(entryTrades), 0)
break # Use this to just test addTradeSummaries once
def test_openIbStatement(self):
ibs = IbStatement(db=self.db)
ibdb = StatementDB(db=self.db)
ibdb.reinitializeTradeTables()
x = ibs.openIBStatement(self.testfile1)
self.assertIsNotNone(x)
self.assertIsInstance(x[0], dict)
self.assertTrue('TRNT' in x[0].keys() or 'Trades' in x[0].keys())
st = ibdb.getStatement(self.theDate)
self.assertIsInstance(st, pd.DataFrame)
self.assertGreater(len(st), 0)
def test_findTradeSummary(self):
'''
Test findTradeSummary, a helper method for addTradeSummaries and updateTradeSummaries.
Note that one of those needs to have run and succeeded inorder to test this method.
'''
infile = "data/flex.369463.ActivityFlexMonth.20191008.20191106.csv"
theDate = pd.Timestamp('2019-10-16')
# Create these three objects
ibs = IbStatement(db=self.db)
ibdb = StatementDB(self.db)
ibdb.reinitializeTradeTables()
trades = DefineTrades("DB")
# This call loads the statement into the db
ibs.openIBStatementCSV(infile)
# Here is an example of processing a single day of trades (3 calls)
# This gets a collection of trades from a single day that can become a trade_sum entry
df = ibdb.getStatement(theDate)
# The following method and function process the statement transactions into a collection
# of trades where each trade is a single row representing multiple transactions
dframe, ldf = trades.processDBTrades(df)
tradeSummaries, ts, entries, initialImageNames = runSummaries(ldf)
ibdb.addTradeSummaries(ts, ldf)
# The test database trades_sum should now only the trades from theDate, one
# entry per trade
for i, trade in enumerate(tradeSummaries):
x = ibdb.findTradeSummary(theDate, trade['Start'].unique()[0])
self.assertEqual(trade['Name'].unique()[0], x[1])
def test_getUncoveredDays(self):
'''Tests several methods in the covered process from'''
self.clearTables()
ibdb = StatementDB(self.db)
ibs, x = self.openStuff()
delt = ibs.endDate - ibs.beginDate
assert delt.days > 20
begin = ibs.endDate - pd.Timedelta(days=15)
end = ibs.endDate + pd.Timedelta(days=15)
covered = ibdb.getUncoveredDays(ibs.account, begin, end)
self.assertTrue(len(covered) > 0)
for c in covered:
self.assertTrue(c > ibs.endDate)
self.assertTrue(c <= end)
def loadTradesFromDB(self, theDate=None):
'''
User tweak-- if DAS or IB import files are checked when the load button is clicked,
Change the selected radio to useDatabase and return. Update will show if there are
any DB trades to load.
'''
if self.sc.ui.dasImport.isChecked() or self.sc.ui.ibImport.isChecked():
self.sc.ui.useDatabase.setChecked(True)
self.sc.dbDefault(True)
return 1
ibdb = StatementDB()
df = ibdb.getStatement(theDate)
tu = DefineTrades('DB')
self.df, ldf = tu.processDBTrades(df)
ts, self.entries = ibdb.getTradeSummariesSA(theDate)
if not ts:
logging.info(
f"No user data has been saved for {theDate.strftime('%A %B %d')}."
)
return None
self.ts = setTradeSummaryHeaders(ts)
logging.info('load up the trade names now')
tradeSummaries = []
self.sc.ui.tradeList.clear()
for key in self.ts:
self.sc.ui.tradeList.addItem(key)
tradeSummaries.append(self.ts[key])
# Load dailyote
dailyNoteModel = DailyNotesCrud(theDate)
note = dailyNoteModel.getNote()
self.dailyNoteModel = dailyNoteModel
if not note:
note = ""
self.sc.ui.dailyNote.setText(note)
inf = self.sc.ui.infileEdit.text()
windowTitle = f"{self.sc.baseWindowTitle}: {inf}: User Data Loaded"
self.sc.setWindowTitle(windowTitle)
# In prep to do the mistake summary and excel export, return the list it uses now
# It might be good to use the dict self.ts instead
return tradeSummaries
def setUp(self):
ddiirr = os.path.dirname(__file__)
os.chdir(os.path.realpath(ddiirr))
os.chdir(os.path.realpath('../'))
self.inputtype = 'IB_CSV'
indir = 'data/'
# f2 = 'ActivityStatement.20190313_PL.html'
jf = JournalFiles(indir=indir, infile=self.f1, theDate=self.theDate, inputType='IB_CSV', mydevel=False)
jf.inputType = 'IB_CSV'
# statement = Statement_IBActivity(jf)
# df = statement.getTrades_IBActivity(jf.inpathfile)
ibs = IbStatement(db=self.testdb)
ibdb = StatementDB(self.testdb)
ibdb.reinitializeTradeTables()
ibs.openIBStatementCSV(jf.inpathfile)
df2 = ibdb.getStatement(self.theDate)
if df2.empty:
sdate = self.theDate.strftime('%Y-%m-%d')
msg = f'In test_dailycontrol.setup: Error: found no trades in db for {sdate}'
self.assertTrue(not df2.empty, msg)
tu = DefineTrades(jf.inputType)
self.df, ldf = tu.processDBTrades(df2)
sc = SumControl()
lf = LayoutForms(sc, jf, self.df)
lf.runTtoSummaries(ldf)
self.ts = lf.ts
ibdb.addTradeSummariesSA(self.ts, ldf)
def test_StatementDB(self):
'''Test table creation called from __init__'''
StatementDB(self.db)
conn = sqlite3.connect(self.db)
cur = conn.cursor()
x = cur.execute('''SELECT name FROM sqlite_master WHERE type='table'; ''')
x = x.fetchall()
tabnames = ['chart', 'holidays', 'ib_covered', 'ib_trades', 'ib_positions', 'trade_sum']
self.assertTrue(set(tabnames).issubset(set([y[0] for y in x])))
def test_popHol(self):
ModelBase.connect(new_session=True)
statement = text('delete from holidays')
ModelBase.engine.execute(statement)
ibdb = StatementDB()
for holiday in ibdb.holidays:
for day in holiday[1:]:
if day:
self.assertTrue(ibdb.tcrud.isHoliday(day))
def setUpClass(cls):
bu = Backup()
bu.backup()
ddiirr = os.path.dirname(__file__)
os.chdir(os.path.realpath(ddiirr))
os.chdir(os.path.realpath('../'))
cls.outdir = os.path.realpath(cls.outdir)
cls.db = os.path.realpath(cls.db)
if os.path.exists(cls.db):
clearTables(cls.db)
cls.rtg = RTG(db=cls.db, overnight=100)
cls.theDate = '20200207 10:39'
cls.infile = cls.rtg.saveSomeTestFiles([cls.theDate],
cls.outdir,
strict=True,
overwrite=False)[0]
settings = QSettings('zero_substance', 'structjour')
# for i, name in enumerate(cls.infiles):
name = os.path.join(cls.outdir, cls.infile)
x, cls.inputType = getStatementType(name)
if cls.inputType == 'DAS':
ds = DasStatement(name, settings, cls.theDate)
ds.getTrades(testFileLoc=cls.outdir, testdb=cls.db)
elif cls.inputType == "IB_CSV":
ibs = IbStatement(db=cls.db)
ibs.openIBStatement(name)
else:
raise ValueError(f'Unsupported File type: {cls.inputType}')
statement = StatementDB(db=cls.db)
cls.df = statement.getStatement(cls.theDate)
cls.dtrades = DefineTrades(cls.inputType)
cls.rc = FinReqCol(cls.inputType)
cls.trades = cls.dtrades.addFinReqCol(cls.df)
rccolumns = cls.rc.columns.copy()
rccolumns = cls.dtrades.appendCols(rccolumns)
cls.trades = cls.trades[rccolumns]
cls.trades.copy()
cls.trades = cls.trades.sort_values(
[cls.rc.ticker, cls.rc.acct, cls.rc.date])
def runDBInput(self, daDate, jf):
'''
Get the trades from daDate in the DB and process the trades
'''
self.statement = StatementDB()
daDate = qtime2pd(daDate)
df = self.statement.getStatement(daDate)
if df.empty:
return False
tu = DefineTrades(self.inputtype)
dframe, ldf = tu.processDBTrades(df)
lf = LayoutForms(self.sc, jf, dframe)
lf.pickleitnow()
lf.runTtoSummaries(ldf)
self.statement.addTradeSummariesSA(lf.ts, ldf)
return True
def test_StatementDB(self):
'''Test table creation called from __init__'''
ibdb = StatementDB()
tns_expected = ['api_keys', 'chart', 'daily_notes', 'description', 'holidays', 'ib_covered',
'ib_positions', 'ib_trades', 'images', 'inspire', 'links', 'migrate_model',
'source', 'sqlite_sequence', 'strategy', 'tags', 'trade_sum', 'trade_sum_tags']
tns = ibdb.tcrud.getTableNames()
print(set(tns))
self.assertEqual(set(tns_expected), set(tns))
def test_ibstatement(self):
'''Test basic usage loading an ib statement and getting a statement'''
infile = "data/flex.369463.ActivityFlexMonth.20191008.20191106.csv"
theDate = pd.Timestamp('2019-10-16')
# Create these two objects
ibs = IbStatement()
ibdb = StatementDB()
ibdb.reinitializeTradeTables()
# This call loads the statement into the db
ibs.openIBStatementCSV(infile)
# This call will then retrieve one day of trades as a dataframe. theDate is string or timestamp
df2 = ibdb.getStatement(theDate)
self.assertIsInstance(df2, pd.DataFrame)
self.assertFalse(df2.empty)
bu = Backup()
bu.restore()
def fixTsid(self, tdf):
if len(tdf['tsid'].unique()) > 1:
therightone = list()
for id in tdf['tsid'].unique():
if isNumeric(id):
therightone.append(id)
if len(therightone) > 1:
# This could be a place for user input...There are confused ids here
# The question is what trade do these transactions belong to.
raise ValueError(
'Programmers exception: Something needs to be done here')
elif len(therightone) == 1:
ibdb = StatementDB()
for i, row in tdf.iterrows():
tdf.at[i, 'tsid'] = therightone[0]
ibdb.updateTSID(row['id'], therightone[0])
# If one of the vals was nan, the column became float
tdf = tdf.astype({'tsid': int})
# Update the db
return tdf
def test_insertTrade(self):
'''
Test the method insertTrade. Verifys that it inserts a trade and then, with an
identical trade, it does not insert the trade. The col DateTime requires fxn.
'''
rc = FinReqCol()
row = dict()
row[rc.ticker] = 'AAPL'
row['DateTime'] = '20190101;123045'
row[rc.shares] = 450
row[rc.price] = 205.43
row[rc.comm] = .75
row[rc.oc] = 'O'
row[rc.acct] = 'U1234567'
row[rc.bal] = 450
row[rc.avg] = 205.43
row[rc.PL] = 0
ibdb = StatementDB()
self.clearTables()
ibdb.insertTradeSA(row)
ModelBase.session.commit()
c = ibdb.tcrud.getTradeCount()
self.assertEqual(c, 1)
ibdb.insertTradeSA(row)
ModelBase.session.commit()
c = ibdb.tcrud.getTradeCount()
self.assertEqual(c, 1)
bu = Backup()
bu.restore()
def setStopVals(self, key, stop, diff, rr, maxloss):
'''
When the user enters a value in stoploss several things happen in the callback then
here we store all the related values in tto. If the widgets have been marked clean,
determine if a loss in PL exceeds maxloss. If so, save the data to tto
'''
rc = self.rc
tto = self.ts[key]
tto[rc.stoploss] = stop
tto[rc.sldiff] = diff
if rr:
tto[rc.rr] = rr
maxloss = 0.0 if not maxloss else maxloss
tto[rc.maxloss] = maxloss
lost = 0.0
note = ''
clean = tto['clean'].unique()[0]
name = tto[rc.name].unique()[0]
pl = tto[rc.pl].unique()[0]
if isinstance(pl, bytes):
pl = 0
if maxloss:
if 'long' in name.lower() and diff >= 0:
return lost, note, clean
if 'short' in name.lower() and diff <= maxloss:
return lost, note, clean
# assert maxloss < 0
if maxloss > pl:
lost = maxloss - pl
tto[rc.mstkval] = lost
note = 'Loss exceeds max loss!'
tto[rc.mstknote] = note
ibdb = StatementDB()
if 'id' in tto:
ibdb.updateMstkValsSA(tto['id'].unique()[0], lost, note)
return (lost, note, clean)
def test_getStatementDays(self):
'''
The tested method is not currently used by structjour.
Test the method StatementDB.getStatementDays. Exercises getUncovered. Specifically test that
when it returns data, it has the correct fields required in FinReqCol. And that the trades
all occur within the specified dates (this tests on a single day). Noticd that openStuff
exercises a bunch of stuff.
'''
frc = FinReqCol()
ibs, x = self.openStuff()
current = ibs.endDate
ibdb = StatementDB(db=self.db)
days = list(pd.date_range(start=current - pd.Timedelta(days=21), end=current))
days.sort(reverse=True)
for day in days:
if day.weekday() > 4 or ibdb.isHoliday(current):
continue
s = ibdb.getStatementDays(ibs.account, beg=day)
if not s.empty:
cols = [frc.ticker, frc.date, frc.shares, frc.bal, frc.price,
frc.avg, frc.comm, frc.acct, frc.oc, frc.PL, 'id']
self.assertTrue(set(cols) == set(list(s.columns)))
for daDate in s[frc.date].unique():
self.assertEqual(day.date(), pd.Timestamp(daDate).date())
def test_insertTrade(self):
'''
Test the method insertTrade. Verifys that it inserts a trade and then, with an
identical trade, it does not insert the trade. The col DateTime requires fxn.
'''
rc = FinReqCol()
row = dict()
row[rc.ticker] = 'AAPL'
row['DateTime'] = '20190101;123045'
row[rc.shares] = 450
row[rc.price] = 205.43
row[rc.comm] = .75
row[rc.oc] = 'O'
row[rc.acct] = 'U1234567'
row[rc.bal] = 450
row[rc.avg] = 205.43
row[rc.PL] = 0
ibdb = StatementDB(self.db)
self.clearTables()
conn = sqlite3.connect(self.db)
cur = conn.cursor()
ibdb.insertTrade(row, cur)
conn.commit()
x = cur.execute('''SELECT count() from ib_trades ''')
x = x.fetchone()
self.assertEqual(x[0], 1)
ibdb.insertTrade(row, cur)
conn.commit()
x = cur.execute('''SELECT count() from ib_trades ''')
x = x.fetchone()
self.assertEqual(x[0], 1)
self.clearTables()
def connect(cls):
if cls.settings.value('tradeDb') is None:
return
cls.ibdb = StatementDB(db=cls.settings.value('tradeDb'))
cls.conn = sqlite3.connect(cls.ibdb.db)
cls.cur = cls.conn.cursor()
def test_openIbStatement_notcsv(self):
ibs = IbStatement(db=self.db)
ibdb = StatementDB(db=self.db)
ibdb.reinitializeTradeTables()
x = ibs.openIBStatement('data\alittleOrgTODO.txt')
self.assertEqual(len(x[0]), 0)
class runController:
'''
Programming notes-- minimize the use of the ui (self.ui). Instead create high level
interface in sc as needed.
:Settings-keys: ['theDate', 'setToday', scheme', 'journal', 'dasInfile', 'dasInfile2',
'ibInfile', outdir, 'interval', inputType]
'''
def __init__(self, sc):
self.sc = sc
self.ui = self.sc.ui
self.initialize()
self.inputtype = None
self.ui.goBtn.pressed.connect(self.runnit)
self.sc.ui.dateEdit.dateChanged.connect(self.theDateChanged)
# self.ui.loadBtn.pressed.connect(self.loadit)
self.loadedDate = None
self.statement = None
def theDateChanged(self, val):
self.sc.dateInSync = False
self.sc.theDateChanged(val)
if self.sc.ui.useDatabase.isChecked() and self.gotTrades():
self.runnit()
outdir = os.path.join(self.sc.getDirectory(), 'out')
self.settings.setValue('outdir', outdir)
def initialize(self):
'''
Initialize the inputs and outs
'''
# Might blitz thes lines if JournalFiles gets an overhaul. For ease of transaiton
# We keep JournalFiles till its allworks into the Qt run
self.settings = self.sc.settings
self.inputtype = self.settings.value('inputType')
self.indir = self.sc.getDirectory()
inkey = ''
if self.inputtype == 'DAS':
inkey = 'dasInfile'
elif self.inputtype == 'IB_HTML':
inkey = 'ibInfileName'
if self.settings.value('outdirPolicy') == 'default':
self.outdir = None
else:
self.outdir = self.settings.value('outdir')
theDate = self.settings.value('theDate', pd.Timestamp.today())
if theDate and isinstance(theDate, (QDate, QDateTime)):
theDate = qtime2pd(theDate)
self.theDate = theDate
self.positions = self.settings.value('dasInfile2')
# end blitz
self.infile = self.settings.value(inkey, "")
self.inpathfile = self.ui.infileEdit.text()
if os.path.splitext(self.inpathfile)[1].lower() == ".csv":
self.infile = os.path.split(self.inpathfile)[1]
self.sc.setWindowTitle(self.sc.baseWindowTitle)
def loadit(self):
'''
Load saved objects
'''
self.sc.doWeSave()
daDate = self.ui.dateEdit.date()
daDate = qtime2pd(daDate)
self.loadedDate = daDate
self.settings.setValue('theDate', daDate)
self.initialize()
if not self.indir:
logging.info('No file to load?')
return
jf = JournalFiles(indir=self.indir,
outdir=self.outdir,
theDate=self.theDate,
infile=self.infile,
inputType=self.inputtype,
infile2=self.positions,
mydevel=True)
lf = LayoutForms(self.sc, jf, None)
if not lf.loadTradesFromDB(daDate):
msg = f'No user data has been saved for {daDate.strftime("%A %B %d")}. Loading trade data.'
logging.info(msg)
# msgbx = QMessageBox()
# msgbx.setIconPixmap(QPixmap("structjour/images/ZSLogo.png"))
# msgbx.setText(msg)
# msgbx.exec()
self.runnit(True)
def runDBInput(self, daDate, jf):
'''
Get the trades from daDate in the DB and process the trades
'''
self.statement = StatementDB()
daDate = qtime2pd(daDate)
df = self.statement.getStatement(daDate)
if df.empty:
return False
tu = DefineTrades(self.inputtype)
dframe, ldf = tu.processDBTrades(df)
lf = LayoutForms(self.sc, jf, dframe)
lf.pickleitnow()
lf.runTtoSummaries(ldf)
self.statement.addTradeSummariesSA(lf.ts, ldf)
return True
def gotTrades(self):
'''
From the text in the infileEdit box, determine if we have
trades in the db. We can tell because the first token is an
int showing how many trades are held.
'''
if self.sc.ui.useDatabase.isChecked():
text = self.sc.ui.infileEdit.text()
if len(text):
try:
num = int(text.split(' ')[0])
if num > 0:
return True
except Exception:
pass
if self.statement is not None:
count, countt = self.statement.getNumTicketsForDaySA(
qtime2pd(self.sc.ui.dateEdit.date()))
if count > 0 or countt > 0:
return True
return False
def runnit(self, loaditrun=False):
'''
Load an initial input file and process it.
'''
self.sc.dateInSync = True
if self.sc.ui.useDatabase.isChecked() and loaditrun is False:
if not self.gotTrades():
return
return self.loadit()
self.sc.doWeSave()
self.initialize()
if not self.indir:
logging.info('No file to load?')
return
jf = JournalFiles(indir=self.indir,
outdir=self.outdir,
theDate=self.theDate,
infile=self.infile,
inputType=self.inputtype,
infile2=self.positions,
mydevel=True)
if self.inputtype == 'DB':
self.runDBInput(self.theDate, jf)
windowTitle = f'{self.sc.baseWindowTitle}: {self.sc.ui.infileEdit.text()}: no user data'
self.sc.setWindowTitle(windowTitle)
if self.gotTrades():
self.sc.ui.useDatabase.setChecked(True)
self.sc.dbDefault(True)
return
local = os.path.normpath(self.ui.infileEdit.text())
if os.path.normpath(jf.inpathfile) != local:
if os.path.exists(local):
d, jf.infile = os.path.split(local)
jf.inpathfile = local
x, inputType = getStatementType(jf.inpathfile)
if not inputType:
msg = f'<h3>No trades found. File does not appear to be a statement</h3><ul> '
msg += f'<div><strong>{jf.inpathfile}</strong></div>'
msgbx = QMessageBox()
msgbx.setIconPixmap(QPixmap("structjour/images/ZSLogo.png"))
msgbx.setText(msg)
msgbx.exec()
return
self.inputtype = inputType
windowTitle = self.sc.baseWindowTitle + ': ' + self.sc.ui.infileEdit.text(
) + ': no user data'
self.sc.setWindowTitle(windowTitle)
if self.inputtype == 'IB_HTML' or self.inputtype == 'IB_CSV':
jf.inputType = self.inputtype
ibs = IbStatement()
x = ibs.openIBStatement(jf.inpathfile)
msg = ''
if x[0]:
tkey = 'Trades' if 'Trades' in x[0].keys(
) else 'TRNT' if 'TRNT' in x[0].keys() else None
if not tkey:
raise ValueError(
f'Error in processing statemnt {jf.inpathfile}')
numtickets = len(x[0][tkey])
gotToday = self.runDBInput(self.theDate, jf)
if gotToday:
if self.gotTrades():
self.sc.ui.useDatabase.setChecked(True)
self.sc.dbDefault(True)
else:
msg = f'<h3>No trades found on date {self.theDate.date()}</h3><ul> '
msg += f'<div><strong>{jf.inpathfile}</strong></div>'
msg += f'<div>Found {numtickets} tickets. They are now in DB</div>'
msg += f'<div>{list(x[1].keys())}</div>'
else:
msg = f'<h3>No trades recorded from the file:</h3><ul> '
msg = msg + f'<div><strong>{jf.inpathfile}</strong></div>'
msg = msg + f'<div>{x[1]}</div>'
msgbx = QMessageBox()
msgbx.setIconPixmap(QPixmap("structjour/images/ZSLogo.png"))
msgbx.setText(msg)
msgbx.exec()
return
elif self.inputtype == 'DAS':
x = checkDateDir(jf.inpathfile)
if not x:
msg = "<h3>The date for this DAS statement is not clear</h3>"
msg += "<div>Please enter the date for this statement</div>"
msg += f'<div><strong>{jf.inpathfile}</strong></div>'
msg += '<div>(YYYYMMDD) ex: 20190113</div>'
theDate = getDate(msg)
if theDate:
self.settings.setValue('theDate', theDate)
self.sc.ui.dateEdit.setDate(pd2qtime(theDate, qdate=True))
else:
return
ds = DasStatement(jf.infile, self.settings, self.theDate)
ds.getTrades()
self.runDBInput(self.theDate, jf)
if self.gotTrades():
self.sc.ui.useDatabase.setChecked(True)
self.sc.dbDefault(True)
return
else:
msg = '<h3>Unrecognized input:</h3><ul> '
msgbx = QMessageBox()
msgbx.setIconPixmap(QPixmap("structjour/images/ZSLogo.png"))
msgbx.setText(msg)
msgbx.exec()
return
def cheatForBAPL(self, t):
'''
Check the db to find at least one trade for each ticker that matches a trade in t and get
the balance. Then set the balance for that ticker
:return: Either a df that includes balance for all trades or an empty
'''
# TODO This is the third of three methods that do similar things. Its a bit complex and
# is bound to produce errors. Eventually, this method, figureBAPL and figureAPL should be
# combined or at least share code.
rc = self.rc
ibdb = StatementDB(db=self.db, source='IB')
t[rc.bal] = np.nan
t[rc.avg] = np.nan
t[rc.PL] = np.nan
newdf = pd.DataFrame()
for ticker in t[rc.ticker].unique():
LONG = True
SHORT = False
tdf = t[t[rc.ticker] == ticker]
tdf = tdf.sort_values(['DateTime'])
tdf.reset_index(drop=True, inplace=True)
for i, row in tdf.iterrows():
x = ibdb.findTrades(row['DateTime'], row[rc.ticker],
row[rc.price], row[rc.shares],
row[rc.acct])
# Its possible this can return more than one trade, if it does, note that they share
# everything but balance
if x:
tdf.at[i, rc.bal] = x[0][3]
break
started = False
balance = 0
for i, row in tdf.iterrows():
if not math.isnan(tdf.at[i, rc.bal]):
started = True
balance = row[rc.bal]
elif started:
balance = row[rc.shares] + balance
offset = balance
if started:
pastPrimo = False
side = LONG
balance = offset
for i, row in tdf.iterrows():
quantity = row[rc.shares]
if pastPrimo and side == LONG and balance < 0:
side = SHORT
elif pastPrimo and side == SHORT and balance > 0:
side = LONG
prevBalance = balance
tdf.at[i, rc.bal] = row[rc.shares] + balance
balance = tdf.at[i, rc.bal]
# This the first trade Open; average == price and set the side-
if not pastPrimo and balance == row[rc.shares]:
pastPrimo = True
average = row[rc.price]
tdf.at[i, rc.avg] = average
side = LONG if row[rc.shares] >= 0 else SHORT
# Here are openers -- adding to the trade; average changes
# newAverage = ((prevAverage * prevBalance) + (quantity * price)) / balance
elif (pastPrimo and side is LONG
and quantity >= 0) or (pastPrimo and side is SHORT
and quantity < 0):
newAverage = ((average * prevBalance) +
(quantity * row[rc.price])) / balance
average = newAverage
tdf.at[i, rc.avg] = average
# Here are closers; PL is figured and check for trade ending
elif pastPrimo:
# Close Tx, P/L is figured on CLOSING transactions only
tdf.at[i, rc.avg] = average
tdf.at[i, rc.PL] = (average - row[rc.price]) * quantity
if balance == 0:
pastPrimo = False
else:
# This should be a first trade for this statmenet/Symbol. Could be Open or
# Close. We are lacking the previous balance so cannot reliably figure the
# average.
# logging.debug(f'''There is a trade for {row[rc.ticker]} that lacks a transaction in this statement''')
pass
newdf = newdf.append(tdf)
else:
# If the balance for any trade is not found, return empty.
return pd.DataFrame()
return newdf
def openTradeFlexCSV(self, infile):
'''
Open a Trade flex statement csv file. This is a single table file. The headers are in the
top row so just reading it with read_csv will collect them. This table is missing the
Open/Close data.
'''
df = pd.read_csv(infile)
self.inputType = 'T_FLEX'
rc = self.rc
# This one table file has no tableid
currentcols = list(df.columns)
ourcols = self.getColsByTabid('FlexTrades')
ourcols, missingcols = self.verifyAvailableCols(
currentcols, ourcols, 'DailyTrades')
df = df[ourcols].copy()
df = df.rename(columns={
'Date/Time': 'DateTime',
'Code': 'Codes',
'ClientAccountID': 'Account'
})
lod = df['LevelOfDetail'].str.lower().unique()
if 'order' in lod:
pass
elif 'execution' in lod:
if 'OrderID' in missingcols:
msg = 'This table contains transaction level data but lacks OrderID.'
return dict(), msg
else:
# df = df.rename(columns={'OrderID': 'IBOrderID'})
df = self.combinePartialsFlexTrade(df)
else:
# TODO 2019-07-03 if this never trips, blitz the statmement for just in case
raise ValueError(
"If this trips, detemine if the data is savlagable")
# if len(df) < 1:
if df.empty:
msg = 'This statement has no trades.'
return dict(), msg
# The Codes col acks the OpenClose codes so were done with it.
df = df.drop(['LevelOfDetail', 'Codes'], axis=1)
df = self.unifyDateFormat(df)
self.account = df['Account'].unique()[0]
beg = df['DateTime'].min()
end = df['DateTime'].max()
assert beg
assert end
try:
self.beginDate = pd.Timestamp(beg).date()
self.endDate = pd.Timestamp(end).date()
except ValueError:
msg = f'Unknown date format error: {beg}, {end}'
return dict(), dict()
df = df.rename(columns={'Symbol': rc.ticker, 'Quantity': rc.shares})
x = self.cheatForBAPL(df)
if not x.empty:
ibdb = StatementDB(db=self.db, source='IB')
ibdb.processStatement(x, self.account, self.beginDate,
self.endDate)
df = x.copy()
return {'Trades': df}, {'Trades': 'Trades'}
return {'Trades': df}, {'Trades': 'Trades'}
def setUpClass(cls):
ddiirr = os.path.dirname(__file__)
os.chdir(os.path.realpath(ddiirr + '/../'))
outdir = 'test/out'
cls.outdir = os.path.realpath(outdir)
cls.db = 'data/testdb.sqlite'
cls.db = os.path.realpath(cls.db)
if os.path.exists(cls.db):
clearTables(cls.db)
cls.rtg = RTG(db=cls.db)
# cls.dates = ['20200203 09:30', '20200204 07:30', '20200205 09:35', '20200206 11:40', '20200207 10:39']
cls.theDate = '20200207 10:39'
cls.infile = cls.rtg.saveSomeTestFiles([cls.theDate], cls.outdir)[0]
settings = QSettings('zero_substance', 'structjour')
# for i, name in enumerate(cls.infiles):
name = os.path.join(cls.outdir, cls.infile)
x, inputType = getStatementType(name)
if inputType == 'DAS':
ds = DasStatement(name, settings, cls.theDate)
ds.getTrades(testFileLoc=cls.outdir, testdb=cls.db)
elif inputType == "IB_CSV":
ibs = IbStatement(db=cls.db)
ibs.openIBStatement(name)
else:
raise ValueError(f'Unsupported File type: {inputType}')
statement = StatementDB(db=cls.db)
df = statement.getStatement(cls.theDate)
dtrade = DefineTrades(inputType)
dframe, cls.ldf = dtrade.processDBTrades(df)
cls.jf = JournalFiles(indir=cls.outdir,
outdir=outdir,
theDate=cls.theDate,
infile=name)
cls.sc = SumControl()
lf = LayoutForms(cls.sc, cls.jf, dframe)
lf.runTtoSummaries(cls.ldf)
# Setup mistake note fields to test
cls.note = 'Ground control to Major Tom'
for i, key in enumerate(lf.ts):
tto = lf.ts[key]
notex = cls.note + str(i + 1)
tto['MstkNote'] = notex
# Setup a couple images to add
imdir = 'images/'
img1 = os.path.join(imdir, 'fractal-art-fractals.jpg')
img2 = os.path.join(imdir, 'psych.jpg')
assert os.path.exists(img1)
assert os.path.exists(img2)
for key in lf.ts:
tto = lf.ts[key]['chart1'] = img1
tto = lf.ts[key]['chart2'] = img2
t = ExportToExcel(lf.ts, cls.jf, df)
imageNames = t.getImageNamesFromTS()
cls.imageLocation, dframe = t.layoutExcelData(t.df, cls.ldf,
imageNames)
assert len(cls.ldf) == len(cls.imageLocation)
# Create an openpyxl wb from the dataframe
ls = LayoutSheet(t.topMargin, len(t.df))
cls.wb, ws, nt = ls.createWorkbook(dframe)
# Place both forms 2 cells to the right of the main table
mstkAnchor = (len(dframe.columns) + 2, 1)
cls.mistake = MistakeSummary(numTrades=len(cls.ldf), anchor=mstkAnchor)
cls.t = t
cls.a = mstkAnchor
