def placeImagesAndSumStyles(self, imageLocation, ws, tf):
'''
A helper method for export to excel. Place images and the tradesummary stylesin the
openpyxl ws object
'''
# tradeSummaries = list()
CELLS = 20 # trial and error here
srf = SumReqFields()
for loc in imageLocation:
# Place the format shapes/styles in the worksheet
tf.formatTrade(ws, srf, anchor=(1, loc[0][0][1]))
for iloc, fn in zip(loc[0], loc[1]):
if not os.path.exists(fn):
continue
img = PILImage.open(fn)
xl = XLImage()
newSize = xl.adjustSizeByHeight(img.size, CELLS)
img = img.resize(newSize, PILImage.ANTIALIAS)
img.save(fn, 'png')
img = Image(fn)
if img:
cellname = tcell(iloc)
ws.add_image(img, cellname)
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 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 __init__(self, topMargin, inputlen, spacing=3):
'''
Constructor
:params topMargin: The space at the top before the trade table. Includes the
inspire quote and space notes.
:params inputlen: Don't enter a value here. Its the length of the dframe after processing.
:params spacing: The space between trade summaries.
:params frq: The FinReqCol object
'''
srf = SumReqFields()
sumSize = srf.maxrow() + 5
self.summarySize = sumSize
self.topMargin = topMargin
self.inputlen = inputlen
self.spacing = spacing
self.DSFAnchor = None
def loadTradeSummaries(loc, trades):
'''
Load up each trade summary in the excel doc into a 1 row DataFrame, return a list
of these DataFrames. The addresses are supplied by srf plus the loc object that has the
anchors.
:params:loc: A list of the rows within the excel doc on which to find the trade summaries
:return: A list of 1-row DataFrames Each trade is on one row from each of the trade summay forms
'''
ldf = list()
ts = dict()
srf = SumReqFields()
reqCol = srf.rc
newdf = pd.DataFrame(columns=reqCol.values())
colFormat = srf.tfcolumns
for i, rowNum in enumerate(loc):
newdf = DataFrameUtil.createDf(newdf, 1)
for key in reqCol.keys():
if key in ['date', 'clean', 'id']:
continue
cell = colFormat[reqCol[key]][0]
if isinstance(cell, list):
cell = cell[0]
cell = tcell(cell, anchor=(1, rowNum))
newdf.iloc[-1][reqCol[key]] = trades[cell].value
tradekey = str(i + 1) + ' ' + newdf.iloc[0].Name
ts[tradekey] = newdf
ldf.append(newdf)
return ldf, ts
def whereDateTime(before=None, after=None):
sf = SumReqFields()
s = ''
if before is not None:
d = pd.Timestamp(before)
dbef = d.strftime('%Y%m%d')
tbef = d.strftime('%H:%M:%S')
s = f'{sf.date} >= "{dbef}" AND {sf.start} >= "{tbef}"'
if after is not None:
s += ' AND '
if after is not None:
d = pd.Timestamp(after)
daft = d.strftime('%Y%m%d')
taft = d.strftime('%H:%M:%S')
s += f'{sf.date} <= "{daft}" and {sf.start} <= "{taft}"'
return s
def test_formatTrade(self):
'''
Test the method TradeFormat.formatTrade. Specifically test that each of the elements in
srf.tfcolumns has a corresponding elementcorrectly styled in the re-opened workbook and
that each merged element in srf.tfcolumns is found in the the worsheet.
'''
wb = Workbook()
ws = wb.active
t = TradeFormat(wb)
srf = SumReqFields()
ws = wb.active
t.formatTrade(ws, srf)
dispath = "out/SCHNOrK.xlsx"
if os.path.exists(dispath):
os.remove(dispath)
wb.save(dispath)
wb2 = load_workbook(dispath)
ws2 = wb2.active
# test that each listed item in srf.tfcolumns has a corresponding style set in the
# appropriate cell of the re-opened workbook
for x in srf.tfcolumns:
address = srf.tfcolumns[x][0]
st = srf.tfcolumns[x][1]
if isinstance(address, list):
self.assertEqual(ws2[c(address[0])].style, st)
else:
self.assertEqual(ws2[c(address)].style, st)
# test that each list element has a corresponding merged cell group in the worbook
listofmerge = [
c(srf.tfcolumns[x][0][0], srf.tfcolumns[x][0][1])
for x in srf.tfcolumns if isinstance(srf.tfcolumns[x][0], list)
]
wsmerged = ws.merged_cells.ranges
self.assertEqual(len(listofmerge), len(wsmerged))
for msmerge in wsmerged:
self.assertTrue(str(msmerge) in listofmerge)
def whereDay(after=None, before=None):
'''
Return a WHERE clause for dates of a trade. If both parameters are None, return an empty string
:params after: Timestamp or str. The earliest day of a trade.
:params before: Timestamp or str. The latest day of a trade.
:return: A where clause for earliest and/or latest day of a trade.
'''
sf = SumReqFields()
s = ''
if before is not None:
d = pd.Timestamp(before)
dbef = d.strftime('%Y%m%d')
s = f'{sf.date} <= {dbef}'
if after is not None:
s += ' AND '
if after is not None:
d = pd.Timestamp(after)
daft = d.strftime('%Y%m%d')
s += f'{sf.date} >= {daft}'
return s
def whereTimeOfDay(bstart=None, estart=None):
'''
Return a WHERE clause for the start time of a trade. If both parameters are None, return an empty string
:params bstart: Timestamp or str. The earliest time of day to start
:params estart: Timestamp or str. The latest time of day to start
:return: A where clause for beginning and/or end start time
'''
sf = SumReqFields()
s = ''
if bstart is not None:
d = pd.Timestamp(bstart)
dstart = d.strftime("%H:%S:%M")
s = f'{sf.start} >= {dstart}'
if estart is not None:
s += ' AND'
if estart is not None:
d = pd.Timestamp(estart)
dstart = d.strftime("%H:%S:%M")
s = f'{sf.start} <= {dstart}'
return s
def test_populateXLDailyFormulas(self):
'''
Test populateXLDailyFormulas. Specifically test that the value written to the
ws is a formula (it begins with =).
'''
srf = SumReqFields()
self.t.populateXLDailyFormulas
self.t.populateXLDailyFormulas(self.imageLocation, self.wb.active)
# self.t.saveXL(self.wb, self.jf)
ws = self.wb.active
# Get the anchor from imageLocation, Get the form coord from tfcolumns using a key
# common to tfcolumns and tfformulas
for loc in self.imageLocation:
anchor = (1, loc[0][0][1])
for key in srf.tfformulas:
cell = srf.tfcolumns[key][0]
if isinstance(cell, list):
cell = cell[0]
valcell = tcell(cell, anchor=anchor)
# print(ws[valcell].value)
self.assertEqual(ws[valcell].value.find('='), 0)
def populateXLDailyFormulas(self, imageLocation, ws):
'''
Helper method for export to excel. Populate the excel formulas in the daily summary forms.
The formulas are in self.ts and the information to transalte cell location is in
imageLocation
'''
srf = SumReqFields()
for loc, tradekey in zip(imageLocation, self.ts):
tto = self.ts[tradekey]
# populate the trade information
for key in srf.tfcolumns:
cell = srf.tfcolumns[key][0]
if isinstance(cell, list):
cell = cell[0]
tradeval = tto[key].unique()[0]
# Put some formulas in each trade Summary
if key in srf.tfformulas:
anchor = (1, loc[0][0][1])
formula = srf.tfformulas[key][0]
args = []
for c in srf.tfformulas[key][1:]:
args.append(tcell(c, anchor=anchor))
tradeval = formula.format(*args)
if not tradeval:
continue
if isinstance(tradeval,
(pd.Timestamp, dt.datetime, np.datetime64)):
tradeval = pd.Timestamp(tradeval)
elif isinstance(tradeval, bytes):
tradeval = None
ws[tcell(cell, anchor=(1, loc[0][0][1]))] = tradeval
return
from PyQt5.QtCore import QSettings
from structjour.definetrades import DefineTrades
from structjour.thetradeobject import TheTradeObject, SumReqFields
from structjour.colz.finreqcol import FinReqCol
from structjour.statements.ibstatementdb import StatementDB
from structjour.statements.ibstatement import IbStatement
from structjour.statements.statement import getStatementType
from structjour.statements.dasstatement import DasStatement
from structjour.stock.utilities import isNumeric
# pylint: disable = C0103, W0212, C0111
# Global
grf = SumReqFields()
class TestTheTradeObject(unittest.TestCase):
'''
Test the functions and methods in thetradeobject module. TheTradeObject input requires a bunch of
prerequisites.
1. Trade transactions need to be read into the database from DAS or the Broker statement
2. A statment needs to be created which is a collection of trades from the db from a single day
3. The transactions in the statment need to be processed into trades (a collection of transactions
organized by trade with associated data starttime, average price, balance and user info)
The transition from transactions to trades is achieved by DefineTrades and TheTradeObject.
DefineTrades is responsible for:
separating which transactions go together, numbering the trades (for each day), giving
a common start time for each tx in a trade, calculating PL, calculating PL summary, balance and
naming the trade. (Note that sometimes information is incomplete)
def registerTrades(wb, theDate):
ibdb = StatementDB()
df = ibdb.getStatement(theDate)
x = ibdb.getNumTicketsForDay(theDate)
if not x[0] or not x[1]:
msg = f'''found {x[0]} tickets and {x[1]} trades. Nothing to process for the date {theDate.strftime('%A %d, %Y')}in the DB'''
return msg
tu = DefineTrades('DB')
df, ldf = tu.processDBTrades(df)
ts, entries = ibdb.getTradeSummaries(theDate)
# self.ts = setTradeSummaryHeaders(ts)
drc = DisReqCol(theDate)
tlog = wb["Trade Log"]
account = tlog['I1'].value
startSearch = False
ix = -2
cols = drc.tfcolumns
# Here is a list of the keys to use cols.keys() of the trade log DataFrame
# ['date', 'time', 'side', 'symb', 'entry1', 'acctbal', 'shares',
# 'stoploss', 'targ', 'avgexit', 'pl', 'notes'])
# Not bothering with the abstraction (drc.name) because this is entirely ours.
srf = SumReqFields()
ids = list()
for row in tlog.iter_rows():
# Unconventional loop stuff
anchor = (1, row[0].row)
keys = list(ts.keys())
if startSearch is True:
idcol = cols['id'][0][0] - 1
if row[idcol].value:
ids.append(row[idcol].value)
if not row[0].value:
startSearch = False
ix = 0
if row[0].value == 'Date':
startSearch = True
if ix >= 0 and ix < len(keys):
while True and ix < len(keys):
key = keys[ix]
tdf = ts[key]
if not gotAnyExits(tdf) or tdf['id'].unique()[0] in ids or (
tdf['Account'].unique()[0] != account):
if tdf['Account'].unique()[0] not in ['SIM', 'Live']:
raise ValueError('Programmer exception in search of weird data')
# Continue the inner loop -- keep row from the outer loop loop
ix += 1
continue
# date
cell = tcell(cols['date'][0], anchor=anchor)
tlog[cell] = theDate
# time
cell = tcell(cols['time'][0], anchor=anchor)
tim = tdf[srf.start].unique()[0]
tlog[cell] = tim
# side
name = tdf[srf.name].unique()[0]
if name:
cell = tcell(cols['side'][0], anchor=anchor)
tlog[cell] = name.split()[1]
# symb
cell = tcell(cols['symb'][0], anchor=anchor)
tlog[cell] = name.split()[0]
# entry1
cell = tcell(cols['entry1'][0], anchor=anchor)
tlog[cell] = tdf[srf.entry1].unique()[0]
# Account Balance (setting an excel formula)
cell = tcell(cols['acctbal'][0], anchor=anchor)
formula = "=$M$3+SUM($N$7:$N{})".format(row[0].row - 1)
tlog[cell] = formula
# "shares"
cell = tcell(cols['shares'][0], anchor=anchor)
shares = tdf[srf.shares].unique()[0].split()[0]
if len(shares) > 0:
try:
ishares = int(shares)
except ValueError:
ishares = 0
tlog[cell] = ishares
# stoploss
cell = tcell(cols['stoploss'][0], anchor=anchor)
sl = tdf[srf.stoploss].unique()[0]
if isinstance(sl, bytes):
sl = None
tlog[cell] = sl
# target
cell = tcell(cols['targ'][0], anchor=anchor)
target = tdf[srf.targ].unique()[0]
if isinstance(target, bytes):
target = None
tlog[cell] = target
# avgExit
cell = tcell(cols['avgexit'][0], anchor=anchor)
tlog[cell] = getAvgExit(tdf)
# P/L
cell = tcell(cols['pl'][0], anchor=anchor)
pl = tdf[srf.pl].unique()[0]
# Don't know how these are sneaking through-- Is it only on legacy files?
if isinstance(pl, bytes):
pl = 0.0
tlog[cell] = pl
# Strategy
cell = tcell(cols['strat'][0], anchor=anchor)
strat = tdf[srf.strat].unique()[0]
tlog[cell] = strat
# notes (from the mistake note field)
cell = tcell(cols['notes'][0], anchor=anchor)
note = tdf[srf.mstknote].unique()[0]
tlog[cell] = note
# id (from the database)
cell = tcell(cols['id'][0], anchor=anchor)
id = tdf['id'].unique()[0]
tlog[cell] = id
# break the inner loop
break
ix += 1
if ix >= len(keys):
# done with outer loop
break
return None
def registerTradesOLD(tsList, wb):
for fname, theDate in tsList:
wb2 = load_workbook(fname)
trades = wb2["Sheet"]
tradeLocation = getTradeSummaryFormLocations(trades)
ldf, ts = loadTradeSummaries(tradeLocation, trades)
drc = DisReqCol(theDate)
tlog = wb["Trade Log"]
startSearch = False
ix = -2
cols = drc.tfcolumns
# Here is a list of the keys to use cols.keys() of the trade log DataFrame
# ['date', 'time', 'side', 'symb', 'entry1', 'acctbal', 'shares',
# 'stoploss', 'targ', 'avgexit', 'pl', 'notes'])
# Not bothering with the abstraction (drc.name) because this is entirely ours.
srf = SumReqFields()
for row in tlog.iter_rows():
anchor = (1, row[0].row)
if startSearch is True:
if not row[0].value:
startSearch = False
ix = 0
if row[0].value == 'Date':
startSearch = True
if ix >= 0:
tdf = ldf[ix]
if not gotAnyExits(tdf):
continue
# date
cell = tcell(cols['date'][0], anchor=anchor)
tlog[cell] = theDate
# time
cell = tcell(cols['time'][0], anchor=anchor)
tim = tdf[srf.start].unique()[0]
tlog[cell] = tim
# side
name = tdf[srf.name].unique()[0]
if name:
cell = tcell(cols['side'][0], anchor=anchor)
tlog[cell] = name.split()[1]
# symb
cell = tcell(cols['symb'][0], anchor=anchor)
tlog[cell] = name.split()[0]
# entry1
cell = tcell(cols['entry1'][0], anchor=anchor)
tlog[cell] = tdf[srf.entry1].unique()[0]
# Account Balance (setting an excel formula)
cell = tcell(cols['acctbal'][0], anchor=anchor)
formula = "=$M$3+SUM($N$7:$N{})".format(row[0].row - 1)
tlog[cell] = formula
# "shares"
cell = tcell(cols['shares'][0], anchor=anchor)
shares = tdf[srf.shares].unique()[0].split()[0]
if len(shares) > 0:
try:
ishares = int(shares)
except ValueError:
ishares = 0
tlog[cell] = ishares
# stoploss
cell = tcell(cols['stoploss'][0], anchor=anchor)
sl = tdf[srf.stoploss].unique()[0]
tlog[cell] = sl
# target
cell = tcell(cols['targ'][0], anchor=anchor)
target = tdf[srf.targ].unique()[0]
tlog[cell] = target
# avgExit
cell = tcell(cols['avgexit'][0], anchor=anchor)
tlog[cell] = getAvgExit(tdf)
# P/L
cell = tcell(cols['pl'][0], anchor=anchor)
pl = tdf[srf.pl].unique()[0]
tlog[cell] = pl
# Strategy
cell = tcell(cols['strat'][0], anchor=anchor)
strat = tdf[srf.strat].unique()[0]
tlog[cell] = strat
# notes (from the mistake note field)
cell = tcell(cols['notes'][0], anchor=anchor)
note = tdf[srf.mstknote].unique()[0]
tlog[cell] = note
ix = ix + 1
if ix == len(ldf):
break
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
def __init__(self, sc, jf, df):
'''
Initialize the obect and create a Widget dictionary using the same keys as the keys to the
tto object to populate the data.
'''
self.jf = jf
self.df = df
if self.df is not None:
self.pickleitnow()
self.ts = dict()
self.entries = dict()
rc = SumReqFields()
self.timeFormat = '%H:%M:%S'
self.sc = sc
self.tradeSummaries = None
# Widget Dictionary. Keys are same keys for TheTradeObject.TheTrade object
wd = dict()
wd[rc.name] = sc.ui.title
wd[rc.acct] = sc.ui.account
wd[rc.strat] = sc.ui.strategy
# wd[rc.link1] = sc.ui.link
wd[rc.pl] = sc.ui.pl
wd[rc.start] = sc.ui.start
wd[rc.dur] = sc.ui.dur
wd[rc.shares] = sc.ui.pos
wd[rc.mktval] = sc.ui.mkt
wd[rc.targ] = sc.ui.targ
wd[rc.targdiff] = sc.ui.targDiff
wd[rc.stoploss] = sc.ui.stop
wd[rc.sldiff] = sc.ui.stopDiff
wd[rc.rr] = sc.ui.rr
wd[rc.realrr] = sc.ui.realRR
wd[rc.maxloss] = sc.ui.maxLoss
wd[rc.mstkval] = sc.ui.lost
wd[rc.mstknote] = sc.ui.sumNote
wd[rc.entry1] = sc.ui.entry1
wd[rc.entry2] = sc.ui.entry2
wd[rc.entry3] = sc.ui.entry3
wd[rc.entry4] = sc.ui.entry4
wd[rc.entry5] = sc.ui.entry5
wd[rc.entry6] = sc.ui.entry6
wd[rc.entry7] = sc.ui.entry7
wd[rc.entry8] = sc.ui.entry8
wd[rc.exit1] = sc.ui.exit1
wd[rc.exit2] = sc.ui.exit2
wd[rc.exit3] = sc.ui.exit3
wd[rc.exit4] = sc.ui.exit4
wd[rc.exit5] = sc.ui.exit5
wd[rc.exit6] = sc.ui.exit6
wd[rc.exit7] = sc.ui.exit7
wd[rc.exit8] = sc.ui.exit8
wd[rc.time1] = sc.ui.time1
wd[rc.time2] = sc.ui.time2
wd[rc.time3] = sc.ui.time3
wd[rc.time4] = sc.ui.time4
wd[rc.time5] = sc.ui.time5
wd[rc.time6] = sc.ui.time6
wd[rc.time7] = sc.ui.time7
wd[rc.time8] = sc.ui.time8
wd[rc.eshare1] = sc.ui.share1
wd[rc.eshare2] = sc.ui.share2
wd[rc.eshare3] = sc.ui.share3
wd[rc.eshare4] = sc.ui.share4
wd[rc.eshare5] = sc.ui.share5
wd[rc.eshare6] = sc.ui.share6
wd[rc.eshare7] = sc.ui.share7
wd[rc.eshare8] = sc.ui.share8
wd[rc.diff1] = sc.ui.diff1
wd[rc.diff2] = sc.ui.diff2
wd[rc.diff3] = sc.ui.diff3
wd[rc.diff4] = sc.ui.diff4
wd[rc.diff5] = sc.ui.diff5
wd[rc.diff6] = sc.ui.diff6
wd[rc.diff7] = sc.ui.diff7
wd[rc.diff8] = sc.ui.diff8
wd[rc.pl1] = sc.ui.pl1
wd[rc.pl2] = sc.ui.pl2
wd[rc.pl3] = sc.ui.pl3
wd[rc.pl4] = sc.ui.pl4
wd[rc.pl5] = sc.ui.pl5
wd[rc.pl6] = sc.ui.pl6
wd[rc.pl7] = sc.ui.pl7
wd[rc.pl8] = sc.ui.pl8
wd[rc.explain] = sc.ui.explain
wd[rc.notes] = sc.ui.notes
self.rc = rc
self.wd = wd
self.imageNames = None
self.sc.loadLayoutForms(self)
def populateDailySummaryForm(self, TheTradeList, mistake, ws, anchor):
'''
Populate the daily Summary Form. The PL values are retrieved from TheTradeList. The static
labels are set earlier. This method sets some statistics and notes for things like
regarding average winners/losers etc.
:params listOfTrade: A python list of the Summary Trade DataFrame, aka TheTrade, each one
is a single row DataFrame containg all the data for trade summaries.
:params mistke:
:params ws: The openpyxl Worksheet object
:raise Value Error: When pl is misformatted and cannot be used.
'''
srf = SumReqFields()
liveWins = list()
liveLosses = list()
simWins = list()
simLosses = list()
maxTrade = (0, "notrade")
minTrade = (0, "notrade")
# Didnot save the Trade number in TheTrade. These should be the same order...
count = 0
for TheTrade in TheTradeList:
pl = TheTrade[srf.pl].unique()[0]
live = True if TheTrade[srf.acct].unique()[0] == "Live" else False
count = count + 1
# A bug-ish inspired baby-sitter
if isinstance(pl, str):
if pl == '':
pl = 0
else:
try:
pl = float(pl)
except NameError:
raise ValueError(
'Malformed float for variable pl in createDailySummary'
)
if float(pl) > maxTrade[0]:
maxTrade = (pl, "Trade{0}, {1}, {2}".format(
count, TheTrade[srf.acct].unique()[0],
TheTrade[srf.name].unique()[0]))
if pl < minTrade[0]:
minTrade = (pl, "Trade{0}, {1}, {2}".format(
count, TheTrade[srf.acct].unique()[0],
TheTrade[srf.name].unique()[0]))
if live:
if pl > 0:
liveWins.append(pl)
else:
liveLosses.append(pl)
else:
if pl > 0:
simWins.append(pl)
else:
simLosses.append(pl)
anchor = (anchor[0], anchor[1] + mistake.numTrades + 5)
self.DSFAnchor = anchor
dailySumData = dict()
dailySumData['livetot'] = sum([sum(liveWins), sum(liveLosses)])
numt = len(liveWins) + len(liveLosses)
if numt == 0:
dailySumData['livetotnote'] = "0 Trades"
else:
note = "{0} Trade{1}, {2} Winner{3}, {4}, Loser{5}"
note = note.format(numt, "" if numt == 1 else "s", len(liveWins),
"" if len(liveWins) == 1 else "s",
len(liveLosses),
"" if len(liveLosses) == 1 else "s")
dailySumData['livetotnote'] = note
dailySumData['simtot'] = sum([sum(simWins), sum(simLosses)])
# 9 trades, 3 Winners, 6 Losers
numt = len(simWins) + len(simLosses)
if numt == 0:
dailySumData['simtotnote'] = "0 Trades"
else: # 4 trades, 1 Winner, 3 Losers
note = "{0} Trade{1}, {2} Winner{3}, {4}, Loser{5}"
note = note.format(numt, "" if numt == 1 else "s",
len(simWins), "" if len(simWins) == 1 else "s",
len(simLosses),
"" if len(simLosses) == 1 else "s")
dailySumData['simtotnote'] = note
dailySumData['highest'] = maxTrade[0]
dailySumData['highestnote'] = maxTrade[1]
dailySumData['lowest'] = minTrade[0]
dailySumData['lowestnote'] = minTrade[1]
if (len(liveWins) + len(simWins)) == 0:
dailySumData['avgwin'] = 0
else:
dailySumData['avgwin'] = sum(
[sum(liveWins), sum(simWins)]) / (len(liveWins) + len(simWins))
dailySumData['avgwinnote'] = "X {} = ${:.2f}".format(
len(liveWins) + len(simWins), sum([sum(liveWins),
sum(simWins)]))
if len(liveLosses) + len(simLosses) == 0:
dailySumData['avgloss'] = 0
else:
dailySumData['avgloss'] = sum([
sum(liveLosses), sum(simLosses)
]) / (len(liveLosses) + len(simLosses))
dailySumData['avglossnote'] = "X {} = (${:.2f})".format(
len(liveLosses) + len(simLosses),
abs(sum([sum(liveLosses), sum(simLosses)])))
for key in dailySumData.keys():
rng = mistake.dailySummaryFields[key][0]
if isinstance(rng, list):
rng = rng[0]
ws[tcell(rng, anchor=anchor)] = dailySumData[key]
def gatherDSumData(self, ts=None):
'''
The dialog does not use this. It shows the info in charts. It is called by the export to excel stuff.
Put together into a dictionary the data for populating the Daily Summary
:ts: TradeSummary dictionary. If called without running runDialog, this must be provided
If this is called from within the dialog, leave it blank.
'''
if ts:
self.ts = ts
srf = SumReqFields()
liveWins = list()
liveLosses = list()
simWins = list()
simLosses = list()
maxTrade = (0, "notrade")
minTrade = (0, "notrade")
# Didnot save the Trade number in TheTrade. These should be the same order...
count = 0
if not self.ts:
logging.info('Trade data not found')
return
# Gather all the data
for key in self.ts:
TheTrade = self.ts[key]
pl = TheTrade[srf.pl].unique()[0]
live = True if TheTrade[srf.acct].unique()[0] == "Live" else False
count = count + 1
if pl is None:
pl = 0.0
# A bug-ish inspired baby-sitter
elif isinstance(pl, str) or isinstance(pl, bytes):
pl = 0
if float(pl) > maxTrade[0]:
maxTrade = (pl, "Trade{0}, {1}, {2}".format(
count, TheTrade[srf.acct].unique()[0], TheTrade[srf.name].unique()[0]))
if pl < minTrade[0]:
minTrade = (pl, "Trade{0}, {1}, {2}".format(
count, TheTrade[srf.acct].unique()[0], TheTrade[srf.name].unique()[0]))
if live:
if pl > 0:
liveWins.append(pl)
else:
liveLosses.append(pl)
else:
if pl > 0:
simWins.append(pl)
else:
simLosses.append(pl)
dailySumData = OrderedDict()
# row1
dailySumData['livetot'] = fc(sum([sum(liveWins), sum(liveLosses)]))
numt = len(liveWins) + len(liveLosses)
if numt == 0:
dailySumData['livetotnote'] = "0 Trades"
else:
note = "{0} Trade{1}, {2} Winner{3}, {4}, Loser{5}"
note = note.format(numt, "" if numt == 1 else "s", len(liveWins),
"" if len(liveWins) == 1 else "s", len(
liveLosses),
"" if len(liveLosses) == 1 else "s")
dailySumData['livetotnote'] = note
# row2
dailySumData['simtot'] = fc(sum([sum(simWins), sum(simLosses)]))
# 9 trades, 3 Winners, 6 Losers
numt = len(simWins) + len(simLosses)
if numt == 0:
dailySumData['simtotnote'] = "0 Trades"
else: # 4 trades, 1 Winner, 3 Losers
note = "{0} Trade{1}, {2} Winner{3}, {4}, Loser{5}"
note = note.format(numt, "" if numt == 1 else "s",
len(simWins), "" if len(simWins) == 1 else "s",
len(simLosses), "" if len(simLosses) == 1 else "s")
dailySumData['simtotnote'] = note
# row3
dailySumData['highest'] = fc(maxTrade[0])
dailySumData['highestnote'] = maxTrade[1]
# row 4
dailySumData['lowest'] = fc(minTrade[0])
dailySumData['lowestnote'] = minTrade[1]
# row 5
if (len(liveWins) + len(simWins)) == 0:
dailySumData['avgwin'] = '$0.00'
else:
dailySumData['avgwin'] = fc(sum(
[sum(liveWins), sum(simWins)]) / (len(liveWins) + len(simWins)))
dailySumData['avgwinnote'] = "X {} = {}".format(
len(liveWins) + len(simWins), fc(sum([sum(liveWins), sum(simWins)])))
# row 6
if len(liveLosses) + len(simLosses) == 0:
dailySumData['avgloss'] = '$0.00'
else:
dailySumData['avgloss'] = fc(sum([sum(liveLosses), sum(
simLosses)]) / (len(liveLosses) + len(simLosses)))
dailySumData['avglossnote'] = "X {} = (${:.2f})".format(
len(liveLosses) + len(simLosses), abs(sum([sum(liveLosses), sum(simLosses)])))
return dailySumData
def __init__(self, numTrades, anchor=(1, 1)):
self.anchor = anchor
self.numTrades = numTrades
# Create the data structure to make a styled shape for the Mistake Summary Form
# 'key':[rng,style, value]
mistakeFields = {
'title': [[(1, 1), (12, 2)], 'titleStyle', 'Mistake Summary'],
'headname': [[(1, 3), (2, 3)], 'normStyle', 'Name'],
'headpl': [(3, 3), 'normStyle', "PnL"],
'headLossPL': [(4, 3), 'normStyle', "Lost P/L"],
'headmistake': [[(5, 3), (12, 3)], 'normStyle',
"Mistake or pertinent feature of trade."],
}
# Create the data structure to create a styled shape for the Daily Summary Form
# 'key':[rng, style, value]
dailySummaryFields = {
'title': [[(1, 1), (12, 2)], 'titleStyle', "Daily PnL Summary"],
'headlivetot': [[(1, 3), (2, 3)], 'normStyle', "Live Total"],
'headsimtot': [[(1, 4), (2, 4)], 'normStyle', "Sim Total"],
'headhighest': [[(1, 5), (2, 5)], 'normStyle', "Highest Profit"],
'headlowest': [[(1, 6), (2, 6)], 'normStyle', "Largest Loss"],
'headavgwin': [[(1, 7), (2, 7)], 'normStyle', "Average Win"],
'headavgloss': [[(1, 8), (2, 8)], 'normStyle', "Average Loss"],
'livetot': [(3, 3), 'normalNumber'],
'simtot': [(3, 4), 'normalNumber'],
'highest': [(3, 5), 'normalNumber'],
'lowest': [(3, 6), 'normalNumber'],
'avgwin': [(3, 7), 'normalNumber'],
'avgloss': [(3, 8), 'normalNumber'],
'livetotnote': [[(4, 3), (12, 3)], 'normStyle'],
'simtotnote': [[(4, 4), (12, 4)], 'normStyle'],
'highestnote': [[(4, 5), (12, 5)], 'normStyle'],
'lowestnote': [[(4, 6), (12, 6)], 'normStyle'],
'avgwinnote': [[(4, 7), (12, 7)], 'normStyle'],
'avglossnote': [[(4, 8), (12, 8)], 'normStyle'],
}
# Dynamically add rows to mistakeFields
# for name, trade pl, lost pl, mistake note (n, tpl, pl, m)
for i in range(numTrades):
n = "name" + str(i + 1)
tp = "tpl" + str(i + 1)
p = "pl" + str(i + 1)
m = "mistake" + str(i + 1)
ncells = [(1, 4 + i), (2, 4 + i)] # [(1,4), (2,4)]
tpcells = (3, 4 + i)
pcells = (4, 4 + i)
mcells = [(5, 4 + i), (12, 4 + i)]
mistakeFields[n] = [ncells, 'normStyle']
mistakeFields[tp] = [tpcells, 'normalNumber']
mistakeFields[p] = [pcells, 'normalNumber']
mistakeFields[m] = [mcells, 'finalNoteStyle']
mistakeFields['blank1'] = [
[(1, 4 + numTrades), (2, 4 + numTrades)], 'normStyle']
mistakeFields['total2'] = [(3, 4 + numTrades), 'normalNumber']
mistakeFields['total'] = [(4, 4 + numTrades), 'normalNumber']
mistakeFields['blank2'] = [
[(5, 4 + numTrades), (12, 4 + numTrades)], 'normStyle']
# Excel formulas belong in the mstkval and mstknote columns. The srf.tfcolumns bit
# are the target addresses for the Excel formula. The cell translation
# takes place when we create and populate the Workbook.
formulas = dict()
srf = SumReqFields()
for i in range(numTrades):
tp = "tpl" + str(i + 1)
formulas[tp] = ['={0}', srf.tfcolumns[srf.pl][0][0]]
p = "pl" + str(i + 1)
formulas[p] = ['={0}', srf.tfcolumns[srf.mstkval][0][0]]
m = "mistake" + str(i + 1)
formulas[m] = ['={0}', srf.tfcolumns[srf.mstknote][0][0]]
self.formulas = formulas
self.mistakeFields = mistakeFields
self.dailySummaryFields = dailySummaryFields
