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 __init__(self, db=None, source=None):
'''Initialize and set the db location'''
settings = QSettings('zero_substance', 'structjour')
# jdir = settings.value('journal')
self.settings = settings
self.db = None
if not db:
db = self.settings.value('tradeDb')
else:
db = db
if db:
self.db = db
if not self.db:
title = 'Warning'
msg = (
'<h3>Database files have not been set</h3>'
'<p>Please set a valid location when calling setDB or you may select or '
'create a new location by selecting file->filesettings</p>')
msgbx = QMessageBox(QMessageBox.Warning, title, msg,
QMessageBox.Ok)
msgbx.setWindowIcon(QIcon("structjour/images/ZSLogo.png"))
msgbx.exec()
return
self.rc = FinReqCol()
self.sf = SumReqFields()
self.createTable()
ModelBase.session.close()
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 __init__(self, source='DAS'):
'''
Constructor
Note: source is not used by this class. Argument could be probably be removed.
'''
self.source = source
assert self.source in ['DAS', 'IB_HTML', 'IB_CSV', 'DB']
self._frc = FinReqCol(source)
def __init__(self, db=None):
self.account = None
self.statementname = None
self.beginDate = None
self.endDate = None
self.inputType = None
self.broker = None
self.db = db
self.rc = FinReqCol()
def test_addFinReqCol(self):
'''
Test the method journal.definetrades.TestDefineTrades.addFinReqCol
'''
rc = self.rc
frc = FinReqCol()
df = pd.DataFrame(np.random.randint(0,
1000,
size=(10, len(rc.columns))),
columns=rc.columns)
dtrades = DefineTrades()
df = dtrades.addFinReqCol(df)
for x in frc.columns:
self.assertIn(x, df.columns)
self.assertGreaterEqual(len(df.columns), len(frc.columns))
def imageData(self, df, ldf, ft="png"):
'''
Gather the image names and determine the locations in the Excel doc to place them. Excel
has a few things at top followed by trade summaries, charts and tables for each trade.
Return with the image name/location data structure. The structure can be used for the Excel
DataFrame-- to navigate summary form locations and just for the names
:params df: The DataFrame representing the input file plus some stuff added in
processOutputFile
:params ldf: A list of dataFrames. Each encapsulates a trade.
:parmas ft: Image filetype extension. (NOT USED)
:return (Imagelocation, df): ImageLocation contains information about the excel document
locations of trade summaries and image locations. The dataFrame df is the
outline used to create the workbook, ImageLocation will be used to stye it
and fill in the stuff.
'''
# Add rows and append each trade, leaving space for an image. Create a list of
# names and row numbers to place images within the excel file (imageLocation
# data structure).
# Number of rows between trade summaries
frq = FinReqCol()
newdf = DataFrameUtil.createDf(df, self.topMargin)
df = newdf.append(df, ignore_index=True)
imageLocation = list()
count = 0
for tdf in ldf:
imageName = '{0}_{1}_{2}_{3}.{4}'.format(
tdf[frq.tix].unique()[-1].replace(' ', ''),
tdf[frq.name].unique()[-1].replace(' ', '-'),
tdf[frq.start].unique()[-1], tdf[frq.dur].unique()[-1], ft)
# Holds location, deprected name, image name, trade start time, trade duration as delta
imageLocation.append([
len(tdf) + len(df) + self.spacing,
tdf.Tindex.unique()[0].replace(' ', '') + '.' + ft, imageName,
tdf.Start.unique()[-1],
tdf.Duration.unique()[-1]
])
count = count + 1
# Append the mini trade table then add rows to fit the tradeSummary form
df = df.append(tdf, ignore_index=True)
df = DataFrameUtil.addRows(df, self.summarySize)
return imageLocation, df
def testCheckReqColumnsWithReqColSuccess(self):
'''Test return values of DataFrameUtil.checkRequiredInputFields'''
reqCol = ReqCol()
finReqCol = FinReqCol()
frc = pd.DataFrame(columns=finReqCol.columns)
t1 = False
t2 = False
try:
t1 = DataFrameUtil.checkRequiredInputFields(frc, finReqCol.columns)
t2 = DataFrameUtil.checkRequiredInputFields(frc, reqCol.columns)
except ValueError as ex:
print(ex)
self.assertTrue(t1)
self.assertTrue(t2)
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 test_TheTradeObjectSetEntries(self):
rc = FinReqCol()
for tto in self.ttos:
tto._TheTradeObject__setEntries()
# if len(tto.df) < 4:
# self.fail('This test requires a longer sample of transactions to run.')
count = 0
x0 = tto.df.index[0]
long = False
r = tto.df.loc[x0]
if len(r[rc.oc]) < 1 and (r[rc.side].startswith('B') or
r[rc.side].lower().startswith('hold+')):
long = True
elif r[rc.oc] == 'O' and r[rc.shares] > 0 or r[
rc.oc] == 'C' and r[rc.shares] < 0:
long = True
# side = r[rc.side]
for i, row in tto.df.iterrows():
count += 1
if (long and row[rc.shares] > 0) or (not long
and row[rc.shares] < 0):
if row[rc.price] != 0:
entry = 'Entry' + str(count)
ttoprice = tto.TheTrade[entry].unique()[0]
self.assertEqual(ttoprice, row.Price,
"Failed to set entry correctly")
else:
if row[rc.price] != 0:
entry = 'Exit' + str(count)
ttoprice = tto.TheTrade[entry].unique()[0]
self.assertEqual(ttoprice, row[rc.price],
"Failed to set exit correctly")
if row[rc.PL] != 0:
PLname = 'PL' + str(count)
ttopl = tto.TheTrade[PLname].unique()[0]
self.assertEqual(
ttopl, row[rc.PL] if isNumeric(row[rc.PL]) else 0,
"Failed to set pl correctly")
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 imageData(ldf):
'''
Create generic image names, one for each trade. The start and dur are the first and last
entries. This is called in the inital loading of a file. More specific names will be used
for created charts.
:ldf: A list of DataFrames slices from the input dataframe, each includes the transactions
for a single trade.
:return: A list of the generic images, one per trade.
'''
frq = FinReqCol()
imageNames = list()
for tdf in ldf:
tindex = tdf[frq.tix].unique()[-1].replace(' ', '')
name = tdf[frq.name].unique()[-1].replace(' ', '_')
begin = pd.Timestamp(tdf[frq.start].unique()[-1]).strftime('%H%M%S')
dur = pd.Timedelta(tdf[frq.dur].unique()[-1]).__str__().replace(
':', '').replace(' ', '_')
imageName = '{0}_{1}_{2}_{3}.{4}'.format(tindex, name, begin, dur,
'png')
imageName = imageName.replace(':', '')
imageNames.append(imageName)
return imageNames
def testCheckrequiredColumnsWithReqColFail(self):
'''Test method DataFrameUtil.checkRequiredInputFields'''
reqCol = ReqCol()
finReqCol = FinReqCol()
fail = pd.DataFrame(
columns=['Time', 'Symb', 'Side', 'Price', 'Qty', 'Account'])
rc = pd.DataFrame(columns=reqCol.columns)
gotve = False
try:
DataFrameUtil.checkRequiredInputFields(fail, reqCol.columns)
except ValueError:
gotve = True
finally:
self.assertTrue(gotve, "Failed to throw value error")
gotve = False
try:
DataFrameUtil.checkRequiredInputFields(rc, finReqCol.columns)
except ValueError:
gotve = True
finally:
self.assertTrue(gotve, "Failed to throw a ValueError")
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 layoutExcelData(self, df, ldf, imageNames):
'''
1) Determine the locations in the Excel doc to place trade summaries, trade tables and
images.
2) Create the empty rows and place the trade tables in the df according to the locations.
:params df: We requre the df as a whole because we are adding rows to it.
:params ldf: A list of dataframes, each a trade, each one is placed into our new skeletal
layout for excel
:return (Imagelocation, df): ImageLocation contains
[ [list of image location], # up to 3 per trade
[list of image names], # up to 3 per trade
Start time,
trade dur,
]
'''
imageLocation = list()
srf = SumReqFields()
sumSize = srf.maxrow() + 5
summarySize = sumSize
spacing = 3
# Image column location
c1col = 13
c2col = 1
c3col = 9
frq = FinReqCol()
newdf = DataFrameUtil.createDf(df, self.topMargin)
df = newdf.append(df, ignore_index=True)
deleteme = []
for i, tdf in enumerate(ldf):
theKey = tdf[frq.tix].unique()[-1]
if len(theKey) == 0:
deleteme.append(i)
continue
imageName = imageNames[theKey]
xtraimage = 0 # Add space for second/third image
if len(imageName) > 1:
xtraimage = 21
ilocs = []
# Need 1 entry even if there are no images
ilocs.append((c1col, len(tdf) + len(df) + spacing))
for i in range(0, len(imageName)):
if i == 1:
ilocs.append((c2col, len(tdf) + len(df) + spacing + 20))
elif i == 2:
ilocs.append((c3col, len(tdf) + len(df) + spacing + 20))
# Holds image locations, image name, trade start time, trade duration as delta
imageLocation.append([
ilocs, imageName,
tdf.Start.unique()[-1],
tdf.Duration.unique()[-1]
])
# Append the mini trade table then add rows to fit the tradeSummary form
df = df.append(tdf, ignore_index=True)
df = DataFrameUtil.addRows(df, summarySize + xtraimage)
for d in deleteme:
ldf.pop(d)
return imageLocation, df
import pandas as pd
from PyQt5.QtCore import QSettings
from structjour.colz.finreqcol import FinReqCol
from structjour.dfutil import DataFrameUtil
from structjour.stock.graphstuff import FinPlot
from structjour.utilities.util import isNumeric
# pylint: disable=C0103
# Use to access columns in the (altered) input dataframe, known on this page as df.
# Use sf (SumReqFields instance) to access
# columns/data for the summary trade dataframe, known on this page as TheTrade.
frc = FinReqCol()
class SumReqFields:
'''
Manage the required columns, cell location and namedStyle for the summary aka TheTradeObject
and TheTradeStyle. There are three types of data represented here.
:attribute self.rc (The data): The data is held in the locations represented by self.rc and the
associatied dict keys. The keys can be of course accessed as strings or as attributes such
as self.name. The latter should be used to maintain portable input files. This data will be
placed in the workbooks in the trade summaries. The rc columns are used in a DataFrame (aka
TheTrade) that summarizes each single trade with a single row. This summary information
includes information from the user, target, stop, strategy, notes, saved images etc.
:attribute self.tfcolumns (The style): This the the tradeformat excel form. The form can be
re-shaped by changing this data structure. These will style workbook in the trade summaries
:attribute self.tfformulas (hybrid data): These entries will override the rc data. The entries