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 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_addTradeSummariesSA(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.clearTables()
ibs, x = self.openStuff()
# ibdb.getUncoveredDays
tcrud = TradeCrud()
covered = tcrud.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.addTradeSummariesSA(ts, ldf)
summaries = ibdb.getTradeSumByDateSA(day)
for summary in summaries:
entryTrades = ibdb.getEntryTradesSA(summary.id)
self.assertGreater(len(entryTrades), 0)
break # Use this to just test addTradeSummaries once
bu = Backup()
bu.restore()
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 backup(self):
if not self.settings.value('journal') or not os.path.exists(
self.settings.value('journal')):
EJControl()
self.rootdir = os.path.normpath(
os.path.join(self.settings.value('journal'), 'backup'))
bu = Backup()
bu.backup()
self.fillAvailBackups()
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 restore(self):
if not self.settings.value('journal') or not os.path.exists(
self.settings.value('journal')):
EJControl()
jdir = self.settings.value('journal')
self.rootdir = os.path.normpath(os.path.join(jdir, 'backup'))
if not jdir or not os.path.exists(jdir):
self.setStatusTip(f'Invalid journal directory: {jdir}')
logging.error(f'Invalid journal directory {jdir}')
bu = Backup()
theDir = os.path.join(self.rootdir,
self.ui.avail_backups.currentText())
bu.restore(theDir=theDir)
def test_openIbStatement(self):
ibs = IbStatement()
ibdb = StatementDB()
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)
bu = Backup()
bu.restore()
def setUpClass(cls):
bu = Backup()
bu.backup()
print(
f'Files have been backed up. Most recent back is {bu.mostRecent()}'
)
settings = QSettings('zero_substance', 'structjour')
tdb = settings.value('tradeDb')
# if tdb and os.path.exists(tdb):
# os.remove(tdb)
# sdb = settings.value('structjourDb')
# if sdb and os.path.exists(sdb):
# os.remove(sdb)
ModelBase.connect(new_session=True)
ModelBase.createAll()
def test_getUncoveredDays(self):
'''Tests several methods in the covered process from'''
self.clearTables()
ibdb = StatementDB()
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.getUncoveredDaysSA(ibs.account, begin, end)
self.assertTrue(len(covered) > 0)
for c in covered:
self.assertTrue(c > ibs.endDate)
self.assertTrue(c <= end)
bu = Backup()
bu.restore()
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 setUpClass(cls):
bu = Backup()
bu.backup()
if ModelBase.session:
ModelBase.session.rollback()
print(
f'Files have been backed up. Most recent back is {bu.mostRecent()}'
)
settings = QSettings('zero_substance', 'structjour')
# tdb = settings.value('tradeDb')
# if tdb and os.path.exists(tdb):
# os.remove(tdb)
# sdb = settings.value('structjourDb')
# if sdb and os.path.exists(sdb):
# os.remove(sdb)
dcrud = DailyNotesCrud(daDate="20301231")
dcrud.createTable()
dcrud.setNote('In the year 2030 on new years eve ...')
def setUpClass(cls):
bu = Backup()
bu.backup()
if ModelBase.session:
ModelBase.session.rollback()
print(
f'Files have been backed up. Most recent back is {bu.mostRecent()}'
)
settings = QSettings('zero_substance', 'structjour')
tdb = settings.value('tradeDb')
# if tdb and os.path.exists(tdb):
# os.remove(tdb)
# sdb = settings.value('structjourDb')
# if sdb and os.path.exists(sdb):
# os.remove(sdb)
# create the schema
scrud = StrategyCrud()
scrud.createTables()
scrud.addStrategy('VWAP Support')
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 test_findTradeSummarySA(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()
ibdb = StatementDB()
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.addTradeSummariesSA(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.findTradeSummarySA(theDate, trade['Start'].unique()[0])
self.assertEqual(trade['Name'].unique()[0], x.name)
bu = Backup()
bu.restore()
def test_updateKey(self):
settings = QSettings('zero_substance', 'structjour')
bu = Backup()
bu.backup()
statement = text('delete from api_keys')
ModelBase.connect(new_session=True)
statement = text('delete from api_keys')
ModelBase.engine.execute(statement)
mk = util.ManageKeys(create=True)
mk.updateKey('bc', 'Its the end of the world')
mk.updateKey('av', 'as we know it')
bck = mk.getKey('bc')
avk = mk.getKey('av')
self.assertTrue(bck == 'Its the end of the world')
self.assertTrue(avk == 'as we know it')
bu.restore()
def tearDownClass(cls):
bu = Backup()
bu.restore()
def setUpClass(cls):
bu = Backup()
bu.backup()
def initSettings(self):
bu = Backup(backuploc=self.rootdir)
bu.initializeSettings()
def setUpClass(cls):
bu = Backup()
bu.backup()
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