def test_placeImagesAndSumStyles(self):
'''
At a loss for how to test image placement---the image is in a zip, the links are not in
the worksheet. Openpyxl does not get the image info from load_workbook. While these asserts
are not satisfactory, they will probably fail if something has gone wrong with the insert
locations for images and the SumStyle forms.
'''
tf = TradeFormat(self.wb)
ws = self.wb.active
self.mistake.mstkSumStyle(ws, tf, self.a)
self.mistake.dailySumStyle(ws, tf, self.a)
self.t.placeImagesAndSumStyles(self.imageLocation, ws, tf)
# self.t.saveXL(self.wb, self.jf)
for loc in self.imageLocation:
sumcell = tcell((1, loc[0][0][1]))
aboveSumCell = tcell((1, loc[0][0][1] - 1))
self.assertEqual(ws[sumcell].style, 'titleStyle', sumcell)
self.assertEqual(ws[aboveSumCell].style, 'Normal', aboveSumCell)
for iloc, fn in zip(loc[0], loc[1]):
imgcell = tcell(iloc)
sumcell = tcell((1, iloc[1]))
self.assertIsNotNone(ws[imgcell].value)
def dailySumStyle(self, ws, tf, anchor=(1, 1)):
'''
Create the shape and populate the daily Summary Form
:params ws: The openpyxl Worksheet object
:parmas tf: The TradeFormat object. It holds the styles used.
:params listOfTrade: A python list of DataFrames, each one a trade with multiple tickets
:params anchor: The location of the top left corner of the form
'''
# Alter the anchor to place this form below the (dynamically sized) Mistake form
anchor = (anchor[0], anchor[1] + self.numTrades + 5)
for key in self.dailySummaryFields:
rng = self.dailySummaryFields[key][0]
style = self.dailySummaryFields[key][1]
if isinstance(rng, list):
tf.mergeStuff(ws, rng[0], rng[1], anchor=anchor)
ws[tcell(rng[0], anchor=anchor)].style = tf.styles[style]
mrng = tcell(rng[0], rng[1], anchor=anchor)
style_range(ws, mrng, border=tf.styles[style].border)
# if key in headers:
if len(self.dailySummaryFields[key]) == 3:
ws[tcell(rng[0], anchor=anchor)] = self.dailySummaryFields[key][2]
else:
ws[tcell(rng, anchor=anchor)].style = tf.styles[style]
# if key in headers:
if len(self.dailySummaryFields[key]) == 3:
ws[tcell(rng, anchor=anchor)] = self.dailySummaryFields[key][2]
def populateXLMistakeForm(self, mistake, ws, imageLocation):
'''
For the export to excel the mistake summary form. Populate the dynamic parts of mistake
summaries. That includes fomulas and hyperlinks with references to tradeSummaries and
hyperlinks to back. The cell location for the links to tradeSummaries requires then
anchor information in imageLocation and the specific cell within tradeSummaries found in
mistakeFields. The return hyperlinks in the tradeSummaries forms are also translated here.
:parmas mistake: A dataframe containing the info to populate the mistake summary form.
:params ws: The openpyxl worksheet object.
:parmas imageLocation: A list containing the locations in the worksheet for each of the
trades in tradeSummaries.
'''
# Populate the name fields as hyperlinks to tradeSummaries title cell and back.
for i, (iloc, tradekey) in enumerate(zip(imageLocation, self.ts)):
tsum = self.ts[tradekey]
key = "name" + str(i + 1)
cell = mistake.mistakeFields[key][0][0]
cell = tcell(cell, anchor=mistake.anchor)
targetcell = (1, iloc[0][0][1])
targetcell = tcell(targetcell)
cellval = "{0} {1} {2}".format(i + 1,
tsum.Name.unique()[0],
tsum.Account.unique()[0])
link = "#{}!{}".format(ws.title, targetcell)
ws[cell].hyperlink = (link)
ws[cell] = cellval
ws[cell].font = Font(color=colors.WHITE, underline="double")
link = "#{}!{}".format(ws.title, cell)
ws[targetcell].hyperlink = (link)
ws[targetcell].font = Font(color=colors.WHITE,
size=16,
underline="double")
# Populate the pl (loss) fields and the mistake fields. These are all formulas like =B31
tokens = ["tpl", "pl", "mistake"]
for token in tokens:
for i in range(len(self.ts)):
key = token + str(i + 1)
if isinstance(mistake.mistakeFields[key][0], list):
cell = mistake.mistakeFields[key][0][0]
else:
cell = cell = mistake.mistakeFields[key][0]
cell = tcell(cell, anchor=mistake.anchor)
formula = mistake.formulas[key][0]
targetcell = mistake.formulas[key][1]
targetcell = tcell(targetcell,
anchor=(1, imageLocation[i][0][0][1]))
formula = formula.format(targetcell)
# logging.info("ws[{0}]='{1}'".format(cell, formula))
ws[cell] = formula
def test_populateXLMistakeForm_correctTradePL(self):
'''This method knows way too much, but how else to deal with excel and test formula results...'''
ws = self.wb.active
iloc = self.imageLocation
self.t.populateXLDailyFormulas(iloc, ws)
self.t.populateXLMistakeForm(self.mistake, ws, iloc)
# self.t.saveXL(self.wb, self.jf)
# Here we will get the values that the simple formulas point to and test that the trade pl matches
for i in range(len(self.ldf)):
tpl = 'tpl' + str(i + 1)
cell = tcell(self.mistake.mistakeFields[tpl][0], anchor=self.a)
vallink = ws[cell].value.replace('=', '')
val = ws[vallink].value
try:
val = float(val)
except ValueError:
print(val)
if val == '':
val = 0.0
else:
raise ValueError('Unexpected value for a currency amount.',
type(val), val)
self.assertIsInstance(val, float, val)
origval = float(self.ldf[i].iloc[-1].Sum)
self.assertAlmostEqual(val, origval)
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 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 test_styleTopwithnothin(self):
'''
Test the method layoutsheet.LayoutSheet.styleTop. Test that it still works without
table data. We still know too much about the method,. Note that we are using a protected
member of Worksheet ws._tables
'''
quoteRange = [(1, 1), (13, 5)]
noteRange = [(1, 6), (13, 24)]
quoteStyle = 'normStyle'
noteStyle = 'explain'
margin = 25
inputlen = 50 # len(df)
wb = Workbook()
ws = wb.active
# Make sure the out dir exists
if not os.path.exists("out/"):
os.mkdir("out/")
# Make sure the file we are about to create does not exist
dispath = "out/SCHNOrK.xlsx"
if os.path.exists(dispath):
os.remove(dispath)
ls = LayoutSheet(margin, inputlen)
ls.styleTop(ws, 13, TradeFormat(wb))
wb.save(dispath)
wb2 = load_workbook(dispath)
ws2 = wb2.active
listOfMerged = list()
listOfMerged.append(
tcell((quoteRange[0])) + ':' + tcell((quoteRange[1])))
listOfMerged.append(
tcell((noteRange[0])) + ':' + tcell((noteRange[1])))
for xx in ws2.merged_cells.ranges:
# print (str(xx) in listOfMerged)
self.assertIn(str(xx), listOfMerged)
self.assertEqual(ws[tcell(quoteRange[0])].style, quoteStyle)
self.assertEqual(ws[tcell(noteRange[0])].style, noteStyle)
self.assertEqual(len(ws._tables), 1)
begin = tcell((1, ls.topMargin))
end = tcell((13, ls.topMargin + ls.inputlen))
tabRange = f'{begin}:{end}'
key = list(ws._tables.keys())
if key:
self.assertEqual(tabRange, ws._tables[key[0]].ref)
os.remove(dispath)
def mstkSumStyle(self, ws, tf, anchor=(1, 1)):
'''
Create the shape and stye for the Mistake summary form, populate the static values.
The rest is done elsewhere including formulas (with cell translation) and the
names, each with a hyperinks to the Trade Summary form.
:params ws: The openpyx worksheet object
:params tf: The TradeFormat object which has the styles
:params anchor: The cell value at the Top left of the form in tuple form.
'''
a = anchor
# Merge the cells, apply the styles, and populate the fields we can--the
# fields that don't know any details todays trades (other than how many trades)
# That includes the non-formula fields and the sum formula below
for key in self.mistakeFields:
rng = self.mistakeFields[key][0]
style = self.mistakeFields[key][1]
if isinstance(self.mistakeFields[key][0], list):
tf.mergeStuff(ws, rng[0], rng[1], anchor=a)
ws[tcell(rng[0], anchor=a)].style = tf.styles[style]
mrng = tcell(rng[0], rng[1], anchor=a)
style_range(ws, mrng, border=tf.styles[style].border)
if len(self.mistakeFields[key]) == 3:
ws[tcell(rng[0], anchor=a)] = self.mistakeFields[key][2]
else:
ws[tcell(rng, anchor=a)].style = tf.styles[style]
if len(self.mistakeFields[key]) == 3:
# ws[tcell(rng, anchor=a)] = headers[key]
ws[tcell(rng, anchor=a)] = self.mistakeFields[key][2]
# The total sum formulas are done here. It is self contained to references to the Mistake
# Summary form
totcell = self.mistakeFields['total'][0]
begincell = (totcell[0], totcell[1] - self.numTrades)
endcell = (totcell[0], totcell[1] - 1)
rng = tcell(begincell, endcell, anchor=a)
totcell = tcell(totcell, anchor=a)
f = '=SUM({0})'.format(rng)
ws[totcell] = f
totcell2 = self.mistakeFields['total2'][0]
begincell = (totcell2[0], totcell2[1] - self.numTrades)
endcell = (totcell2[0], totcell2[1] - 1)
rng = tcell(begincell, endcell, anchor=a)
totcell2 = tcell(totcell2, anchor=a)
f = '=SUM({0})'.format(rng)
ws[totcell2] = f
def populateXLDailyNote(self, ws):
'''
Export the daily note as found in the DailyControl dialog.
'''
cell = (1, 6)
cell = tcell(cell)
dc = DailyControl(self.jf.theDate)
note = dc.getNote()
if note:
ws[cell] = note
def styleTop(self, ws, widthDF, tf):
'''
Style the table, and the top bit. Here we style the table and the things above it. The
table data is already there. Above the table are two merged groups. At the top is the
inspire quote. Next is an introductory notes section. There is currently no external
control to set the sizes of these two things. Its hard coded here here.
:params ws: The openpyxl Worksheet to use.
:params widthDF: The width of the dataFrame holding the trades.
:params tf: The TradeFormat object
:prerequisites: Depends that ws has the table data beginning A25 (1,25), beginning with
column headers on row 25. The length of the table is defined in
self.inputlen. If topMargin changes, this code will need change.
'''
# Hard coded sizes here
quoteRange = [(1, 1), (13, 5)]
noteRange = [(1, 6), (13, 24)]
tblRng = "{0}:{1}".format(
tcell((1, self.topMargin)),
tcell((widthDF, self.topMargin + self.inputlen)))
tab = Table(displayName="Table1", ref=tblRng)
style = TableStyleInfo(name="TableStyleMedium1",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=False)
tab.tableStyleInfo = style
ws.add_table(tab)
# A1:M5 inspire quote. Style the quote and insert the quote
tf.mergeStuff(ws, quoteRange[0], quoteRange[1])
ws["A1"].style = tf.styles["normStyle"]
style_range(ws, "A1:M5", border=tf.styles["normStyle"].border)
inspire = Inspire()
ws["A1"] = inspire.getrandom().replace("\t", " ")
# A6:M24 introductory notes. Style the cells and enter minimal text.
tf.mergeStuff(ws, noteRange[0], noteRange[1])
ws["A6"].style = tf.styles["explain"]
style_range(ws, "A6:M24", border=tf.styles["explain"].border)
def test_dailySumStyle(self):
'''
Test the method MistakeSummary.mstkSumStyle.
'''
wb = Workbook()
ws = wb.active
tf = TradeFormat(wb)
numTrades = 5
ms = MistakeSummary(numTrades)
ms.dailySumStyle(ws, tf)
dispath = "out/SCHNOrK.xlsx"
if os.path.exists(dispath):
os.remove(dispath)
wb.save(dispath)
anchor = (1, ms.anchor[1] + ms.numTrades + 5)
wb2 = load_workbook(dispath)
ws2 = wb2.active
# test that each listed item in ms.mistakeFields has a corresponding style set in the
# appropriate cell in the re-opened workbook.
for x in ms.dailySummaryFields:
cell = ''
entry = ms.dailySummaryFields[x]
if isinstance(entry[0], list):
cell = tcell(entry[0][0], anchor=anchor)
else:
cell = tcell(entry[0], anchor=anchor)
self.assertEqual(entry[1], ws2[cell].style)
# Get the entries with the static values-- the headers
if len(entry) == 3:
self.assertEqual(ws2[cell].value, ms.dailySummaryFields[x][2])
os.remove(dispath)
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
def test_mstkSumStyles(self):
'''
Test the method MistakeSummary.mstkSumStyle.
'''
wb = Workbook()
ws = wb.active
tf = TradeFormat(wb)
ms = MistakeSummary(5)
ms.mstkSumStyle(ws, tf)
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 ms.mistakeFields has a corresponding style set in the
# appropriate cell in the re-opened workbook.
for x in ms.mistakeFields:
cell = ''
entry = ms.mistakeFields[x]
if isinstance(entry[0], list):
cell = tcell(entry[0][0])
else:
cell = tcell(entry[0])
# print(cell, entry[1], ws[cell].style)
self.assertEqual(entry[1], ws[cell].style)
if len(entry) == 3:
self.assertEqual(ws2[cell].value, ms.mistakeFields[x][2])
os.remove(dispath)
def test_populateXLDailySummaryForm(self):
'''
Test the method populateXLDailySummaryForm. Specifically test that given a specific anchor,
12 specific spots are written to by default.
'''
for jf, lf, df, ldf in zip(self.jfs, self.lfs, self.dframes,
self.ldfs):
NUMROWS = 6
mstkAnchor = (3, 3) # arbitrary
mistake = MistakeSummary(numTrades=len(ldf), anchor=mstkAnchor)
t = ExportToExcel(lf.ts, jf, df)
wb = Workbook()
t.populateXLDailySummaryForm(mistake, wb.active, mstkAnchor)
# t.saveXL(wb, self.jf)
anchor = (mstkAnchor[0], mstkAnchor[1] + len(ldf) + 5)
cell = tcell(mstkAnchor, anchor=anchor)
for i in range(0, NUMROWS):
cell = tcell((mstkAnchor[0], mstkAnchor[1] + i), anchor=anchor)
cell2 = tcell((mstkAnchor[0] + 1, mstkAnchor[1] + i),
anchor=anchor)
self.assertIsInstance(wb.active[cell].value, str)
self.assertIsInstance(wb.active[cell2].value, str)
def test_populateXLMistakeForm_hyperlinks(self):
'''
Test the method populateXLMistakeForm has set up correct hyperlinks to the daily summary
forms.
'''
ws = self.wb.active
iloc = self.imageLocation
self.t.populateXLDailyFormulas(iloc, ws)
self.t.populateXLMistakeForm(self.mistake, ws, iloc)
# self.t.saveXL(self.wb, self.jf)
# cell = tcell(self.mistake.mistakeFields['tpl1'][0])
for i in range(len(self.ldf)):
n = 'name' + str(i + 1)
cell = tcell(self.mistake.mistakeFields[n][0][0], anchor=self.a)
self.assertIsNotNone(ws[cell].hyperlink)
link = ws[cell].hyperlink.target.split('!')[1]
self.assertIsNotNone(ws[link].hyperlink)
link2 = ws[link].hyperlink.target.split('!')[1]
self.assertEqual(cell, link2)
def test_populateXLMistakeForm_noteLinks(self):
'''
This method knows way too much, but how else to deal with excel and test formula results...
Test that when data is included for the mistake note in ts, that data is linked with a
simple formula (e.g. '=X98') in the mistake summary form
'''
ws = self.wb.active
iloc = self.imageLocation
self.t.populateXLDailyFormulas(iloc, ws)
self.t.populateXLMistakeForm(self.mistake, ws, iloc)
# self.t.saveXL(self.wb, self.jf)
# Here we will get the values that the simple formulas point to and test that the note is what we wrote
for i in range(len(self.ldf)):
m = 'mistake' + str(i + 1)
note = self.note + str(i + 1)
cell = tcell(self.mistake.mistakeFields[m][0][0], anchor=self.a)
vallink = ws[cell].value.replace('=', '')
val = ws[vallink].value
self.assertEqual(val, note)
def populateXLDailySummaryForm(self, mistake, ws, anchor):
'''
For the export to excel daily summary forms.
Populate the daily Summary Form. The values are retrieved from self.ts via the
DailyControl dialog object-without calling runDialog. The static labels are set earlier
at the creation of the form shape/style. This method sets some statistics and notes for
things like average winners/losers etc.
:params mistke:
:params ws: The openpyxl Worksheet object
:raise Value Error: When pl is misformatted and cannot be used.
'''
dc = DailyControl()
dailySumData = dc.gatherDSumData(self.ts)
anchor = (anchor[0], anchor[1] + mistake.numTrades + 5)
for key in dailySumData.keys():
cell = mistake.dailySummaryFields[key][0]
if isinstance(cell, list):
cell = cell[0]
ws[tcell(cell, anchor=anchor)] = dailySumData[key]
def test_populateXLDailyNote(self):
'''
Test the method populateXLDailyNote. Specifically test after setting a note, calling the
method the workbook contains the note as expected
'''
for jf, lf, df in zip(self.jfs, self.lfs, self.dframes):
dc = DailyControl(jf.theDate)
note = 'Ipsum solarium magus coffeum brewum'
dc.setNote(note)
t = ExportToExcel(lf.ts, jf, df)
wb = Workbook()
ws = wb.active
t.populateXLDailyNote(ws)
t.saveXL(wb, jf)
wb2 = load_workbook(jf.outpathfile)
cell = (1, 6)
cell = tcell(cell)
val = wb2.active[cell].value
self.assertEqual(note, val)
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 test_styleTop(self):
'''
Test the method layoutsheet.LayoutSheet.styleTop. This will probably produce warnings from
openpyxl as there is empty data when it makes the headers. No worries.
Note that we are using a protected member of Worksheet ws._tables, so if it this fails, look
at that. openpyxl does not provide a public attribute for tables.
Note that knowing the quoteRange and noteRange is bad design. Eventually these two bits of
design data should be abstracted to somewhere accessible by the user. (and testing too)
'''
quoteRange = [(1, 1), (13, 5)]
noteRange = [(1, 6), (13, 24)]
quoteStyle = 'normStyle'
noteStyle = 'explain'
margin = 25
inputlen = 50 # len(df)
wb = Workbook()
ws = wb.active
# Make sure the out dir exists
if not os.path.exists("out/"):
os.mkdir("out/")
# Make sure the file we are about to create does not exist
dispath = "out/SCHNOrK.xlsx"
if os.path.exists(dispath):
os.remove(dispath)
# Create table header and data in the ws
headers = [
'Its', 'the', 'end', 'of', 'the', 'world', 'as', 'we', 'know',
'it.', 'Bout', 'Fn', 'Time!'
]
for i in range(1, 14):
ws[tcell((i, 25))] = headers[i - 1]
ls = LayoutSheet(margin, inputlen)
for x in range(ls.topMargin + 1, ls.inputlen + ls.topMargin + 1):
for xx in range(1, 14):
ws[tcell((xx, x))] = randint(-1000, 10000)
ls.styleTop(ws, 13, TradeFormat(wb))
wb.save(dispath)
wb2 = load_workbook(dispath)
ws2 = wb2.active
listOfMerged = list()
listOfMerged.append(
tcell((quoteRange[0])) + ':' + tcell((quoteRange[1])))
listOfMerged.append(
tcell((noteRange[0])) + ':' + tcell((noteRange[1])))
for xx in ws2.merged_cells.ranges:
# print (str(xx) in listOfMerged)
self.assertTrue(str(xx) in listOfMerged)
self.assertEqual(ws[tcell(quoteRange[0])].style, quoteStyle)
self.assertEqual(ws[tcell(noteRange[0])].style, noteStyle)
self.assertEqual(len(ws._tables), 1)
begin = tcell((1, ls.topMargin))
end = tcell((13, ls.topMargin + ls.inputlen))
tabRange = f'{begin}:{end}'
self.assertEqual(tabRange, ws._tables[0].ref)
os.remove(dispath)
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 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 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 populateMistakeForm(self, tradeSummaries, mistake, ws, imageLocation):
'''
Populate the dynamic parts of mistake summaries. That includes fomulas with references to
tradeSummaries and hyperlinks to the same. The anchor info for the tradeSummaries cell translation is in
imageLocation and the specific location of the transaltions is in the mistakeFields. Th
return hyperlinks in the tradeSummaries forms are also translated here.
The form and the static content are already created presumably in the ws in the arguments.
Either way we create the info into the given ws.
:Programming note: MistakeSummaries form is not a one-place creation because of the hard-
coded stuff in this method. With some careful work, it could be. It
would involve placing all the data we need for the hyperlinks (other
than cell transation), in the mistakeFields or in the formula dict.
blwntffczmlz
:params tradeSummaries: A dataframe containing the the trade summaries info, one line per
trade.
:parmas mistake: A dataframe containing the info to populate the mistake summary form.
:params ws: The openpyxl worksheet object.
:parmas imageLocation: A list containing the locations in the worksheet for each of the
trades in tradeSummaries.
'''
# Populate the name fields as hyperlinks to tradeSummaries title cell and back.
for i, (iloc, tsum) in enumerate(zip(imageLocation, tradeSummaries)):
key = "name" + str(i + 1)
cell = mistake.mistakeFields[key][0][0]
cell = tcell(cell, anchor=mistake.anchor)
targetcell = (1, iloc[0])
targetcell = tcell(targetcell)
cellval = "{0} {1} {2}".format(i + 1,
tsum.Name.unique()[0],
tsum.Account.unique()[0])
link = "#{}!{}".format(ws.title, targetcell)
ws[cell].hyperlink = (link)
ws[cell] = cellval
ws[cell].font = Font(color=colors.WHITE, underline="double")
link = "#{}!{}".format(ws.title, cell)
ws[targetcell].hyperlink = (link)
ws[targetcell].font = Font(color=colors.WHITE,
size=16,
underline="double")
# Populate the pl (loss) fields and the mistake fields. These are all simple formulas
# like =B31
tokens = ["tpl", "pl", "mistake"]
for token in tokens:
for i in range(len(tradeSummaries)):
key = token + str(i + 1)
if isinstance(mistake.mistakeFields[key][0], list):
cell = mistake.mistakeFields[key][0][0]
else:
cell = cell = mistake.mistakeFields[key][0]
cell = tcell(cell, anchor=mistake.anchor)
formula = mistake.formulas[key][0]
targetcell = mistake.formulas[key][1]
targetcell = tcell(targetcell, anchor=(1, imageLocation[i][0]))
formula = formula.format(targetcell)
# logging.debug("ws[{0}]='{1}'".format(cell, formula))
ws[cell] = formula
