def test_mergeStuff(self): ''' Test the method tradestyle.TradeFormat.mergeStuff. Specificaly test that a merged cell group exists where it is should be after saving and opening an xlsx file. ''' wb = Workbook() ws = wb.active begin = (1, 1) end = (5, 5) anchor = (3, 3) t = TradeFormat(wb) b, e = t.mergeStuff(ws, begin=begin, end=end, anchor=anchor) dispath = "out/SCHNOrK.xlsx" if os.path.exists(dispath): os.remove(dispath) wb.save(dispath) wb2 = load_workbook(dispath) ws2 = wb2.active x = ws2.merged_cells.ranges # print(b, (x[0].min_col, x[0].min_row)) # print(e, (x[0].max_col, x[0].max_row)) self.assertEqual(b, (x[0].min_col, x[0].min_row)) self.assertEqual(e, (x[0].max_col, x[0].max_row)) os.remove(dispath)
def test_TradeFormatAddNamedStyle(self): ''' Test the object formation and the creation of the styles in __init__ and addNamedStyle for the Workbook. Specifically test that the named style is created in the workbook and is still there when you close it and open it. ''' wb = Workbook() ws = wb.active tf = TradeFormat(wb) # Use a list to verify the order styleList = list(tf.styles.keys()) if not os.path.exists("out/"): os.mkdir("out/") dispath = "out/SCHNOrK.xlsx" if os.path.exists(dispath): os.remove(dispath) # Write one cell for each named style in TradeFormat for i, key in enumerate(styleList): # print(key, c((1, i+1))) ws[c((1, i + 1))] = key ws[c((1, i + 1))].style = tf.styles[key] wb.save(dispath) wb2 = load_workbook(dispath) ws2 = wb2.active # Open it back up and check the the named styles are where we think they are for i, key in enumerate(styleList): # print(key,ws2[c((1, i+1))].style ) self.assertEqual(key, ws2[c((1, i + 1))].style)
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 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}' self.assertEqual(tabRange, ws._tables[0].ref) os.remove(dispath)
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 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_runSummaries(self, unusedstub1, unusedstub2, unusedstub3): ''' Test the method prunSummaries. The setup here is alost the entire module trade.py We run the standard set of infiles ''' # global D # infiles = ['trades.1116_messedUpTradeSummary10.csv', 'trades.8.WithHolds.csv', # 'trades.8.csv', 'trades.907.WithChangingHolds.csv', # 'trades_190117_HoldError.csv', 'trades.8.ExcelEdited.csv', # 'trades.910.tickets.csv', 'trades_tuesday_1121_DivBy0_bug.csv', # 'trades.8.WithBothHolds.csv', 'trades1105HoldShortEnd.csv', # 'trades190221.BHoldPreExit.csv'] global D for tdata, infile in zip(self.dadata, self.infiles): # ::::::::: Setup :::::::: D = deque(tdata) # :::::::::::::: SETUP :::::::::::::: # theDate = '2018-11-05' outdir = 'out/' indir = 'C:/python/E/structjour/src/data/' mydevel = False jf = JournalFiles(infile=infile, outdir=outdir, indir=indir, mydevel=mydevel) trades, jf = Statement_DAS(jf).getTrades() trades, success = InputDataFrame().processInputFile(trades) inputlen, dframe, ldf = DefineTrades().processOutputDframe(trades) # Process the openpyxl excel object using the output file DataFrame. Insert # images and Trade Summaries. margin = 25 # Create the space in dframe to add the summary information for each trade. # Then create the Workbook. ls = LayoutSheet(margin, inputlen) imageLocation, dframe = ls.imageData(dframe, ldf) wb, ws, dummy = ls.createWorkbook(dframe) tf = TradeFormat(wb) mstkAnchor = (len(dframe.columns) + 2, 1) mistake = MistakeSummary(numTrades=len(ldf), anchor=mstkAnchor) mistake.mstkSumStyle(ws, tf, mstkAnchor) # :::::::::::::: END SETUP :::::::::::::: tradeSummaries = ls.runSummaries(imageLocation, ldf, jf, ws, tf) # 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) wb.save(dispath) wb2 = load_workbook(dispath) ws2 = wb2.active srf = SumReqFields() for trade, loc in zip(tradeSummaries, imageLocation): anchor = (1, loc[0]) # print(anchor) for col in trade: if col in['clean']: continue # Get the cell if isinstance(srf.tfcolumns[col][0], list): cell = tcell(srf.tfcolumns[col][0][0], anchor=anchor) else: cell = tcell(srf.tfcolumns[col][0], anchor=anchor) # Nicer to read wsval = ws2[cell].value tval = trade[col].unique()[0] # Test Formulas (mostly skipping for now because its gnarly) # Formulas in srf.tfformulas including the translation stuff if col in srf.tfformulas.keys(): self.assertTrue(wsval.startswith('=')) # Test empty cells elif not tval: # print(wsval, '<------->', tval) self.assertIs(wsval, None) # Test floats elif isinstance(tval, float): # print(wsval, '<------->', tval) self.assertAlmostEqual(wsval, tval) # Time vals elif isinstance(tval, (pd.Timestamp, dt.datetime, np.datetime64)): wsval = pd.Timestamp(wsval) tval = pd.Timestamp(tval) self.assertGreaterEqual((wsval-tval).total_seconds(), -.01) self.assertLessEqual((wsval-tval).total_seconds(), .01) # Test everything else else: # print(wsval, '<------->', tval) self.assertEqual(wsval, tval)
def test_populateDailySummaryForm(self, unusedstub1, unusedstub2, unusedstub3): ''' Test the method populateMistakeForm. The setup here is alost the entire module trade.py The tested method puts in the trade PL and notes ''' global D for tdata, infile in zip(self.dadata, self.infiles): # ::::::::: Setup :::::::: D = deque(tdata) # :::::::::::::: SETUP :::::::::::::: # theDate = '2018-11-05' outdir = 'out/' indir = 'data/' mydevel = False jf = JournalFiles(infile=infile, outdir=outdir, indir=indir, mydevel=mydevel) print(jf.inpathfile) trades, jf = Statement_DAS(jf).getTrades() trades, success = InputDataFrame().processInputFile(trades) inputlen, dframe, ldf = DefineTrades().processOutputDframe(trades) # Process the openpyxl excel object using the output file DataFrame. Insert # images and Trade Summaries. margin = 25 # Create the space in dframe to add the summary information for each trade. # Then create the Workbook. ls = LayoutSheet(margin, inputlen) imageLocation, dframe = ls.imageData(dframe, ldf) wb, ws, dummy = ls.createWorkbook(dframe) tf = TradeFormat(wb) mstkAnchor = (len(dframe.columns) + 2, 1) mistake = MistakeSummary(numTrades=len(ldf), anchor=mstkAnchor) # mistake.mstkSumStyle(ws, tf, mstkAnchor) mistake.dailySumStyle(ws, tf, mstkAnchor) tradeSummaries = ls.runSummaries(imageLocation, ldf, jf, ws, tf) # :::::::::::::: END SETUP :::::::::::::: # ls.populateMistakeForm(tradeSummaries, mistake, ws, imageLocation) ls.populateDailySummaryForm(tradeSummaries, mistake, ws, mstkAnchor) # 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) wb.save(dispath) # print(infile, 'saved as', dispath) wb2 = load_workbook(dispath) ws2 = wb2.active # Live Total frc = FinReqCol() livetot = 0 simtot = 0 highest = 0 lowest = 0 numwins = 0 numlosses = 0 totwins = 0 totloss = 0 for trade in tradeSummaries: acct = trade[frc.acct].unique()[0] pl = trade[frc.PL].unique()[0] highest = pl if pl > highest else highest lowest = pl if pl < lowest else lowest if pl > 0: numwins += 1 totwins += pl # Trades == 0 are figured in the loss column-- comissions and all else: numlosses += 1 totloss += pl if acct == 'Live': livetot += pl elif acct == 'SIM': simtot += pl # print(livetot) # livetotcell = tcell(mistake.dailySummaryFields['livetot'][0], anchor=ls.DSFAnchor) # print(simtot) avgwin = 0 if numwins == 0 else totwins/numwins avgloss = 0 if numlosses == 0 else totloss/numlosses data = [['livetot', livetot], ['simtot', simtot], ['highest', highest], ['lowest', lowest], ['avgwin', avgwin], ['avgloss', avgloss]] for s, d in data: cell = tcell(mistake.dailySummaryFields[s][0], anchor=ls.DSFAnchor) # msg = '{} {} {}'.format(s, d, ws2[cell].value) # print(msg) self.assertAlmostEqual(d, ws2[cell].value) #, abs_tol=1e-7) data = ['livetotnote', 'simtotnote', 'highestnote', 'lowestnote', 'avgwinnote', 'avglossnote'] for s in data: cell = tcell(mistake.dailySummaryFields[s][0][0], anchor=ls.DSFAnchor) val = ws2[cell].value self.assertIsInstance(val, str) self.assertGreater(len(val), 1)
def test_populateMistakeForm(self, unusedstub1, unusedstub2, unusedstub3): ''' Test the method populateMistakeForm. The setup here is alost the entire module trade.py ''' global D for tdata, infile in zip(self.dadata, self.infiles): # ::::::::: Setup :::::::: D = deque(tdata) # :::::::::::::: SETUP :::::::::::::: # theDate = '2018-11-05' outdir = 'out/' indir = 'data/' mydevel = False jf = JournalFiles(infile=infile, outdir=outdir, indir=indir, mydevel=mydevel) trades, jf = Statement_DAS(jf).getTrades() trades, success = InputDataFrame().processInputFile(trades) inputlen, dframe, ldf = DefineTrades().processOutputDframe(trades) # Process the openpyxl excel object using the output file DataFrame. Insert # images and Trade Summaries. margin = 25 # Create the space in dframe to add the summary information for each trade. # Then create the Workbook. ls = LayoutSheet(margin, inputlen) imageLocation, dframe = ls.imageData(dframe, ldf) wb, ws, dummy = ls.createWorkbook(dframe) tf = TradeFormat(wb) mstkAnchor = (len(dframe.columns) + 2, 1) mistake = MistakeSummary(numTrades=len(ldf), anchor=mstkAnchor) mistake.mstkSumStyle(ws, tf, mstkAnchor) tradeSummaries = ls.runSummaries(imageLocation, ldf, jf, ws, tf) # :::::::::::::: END SETUP :::::::::::::: ls.populateMistakeForm(tradeSummaries, mistake, ws, imageLocation) # 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) wb.save(dispath) wb2 = load_workbook(dispath) ws2 = wb2.active frc = FinReqCol() # ragged iteration over mistakeFields and tradeSummaries. count = 0 # ragged iterator for tradeSummaries for key in mistake.mistakeFields: entry = mistake.mistakeFields[key] cell = entry[0][0] if isinstance(entry[0], list) else entry[0] cell = tcell(cell, anchor=mistake.anchor) if key.startswith('name'): # Get the hyperlink target in mistakeform , parse the target and verify the # hyperlinks point to each other tsName = tradeSummaries[count][frc.name].unique()[0] tsAcct = tradeSummaries[count][frc.acct].unique()[0] targetcell = ws2[cell].hyperlink.target.split('!')[1] originalcell = ws2[targetcell].hyperlink.target.split('!')[1] # print(ws2[cell].value, '<--------', tsumName) # print(ws2[cell].value, '<-------', tsumAccount) # print(cell, '<------->', originalcell) self.assertGreater(ws2[cell].value.find(tsName), -1) self.assertGreater(ws2[cell].value.find(tsAcct), -1) self.assertEqual(cell, originalcell) count = count + 1 # ::::::: tpl fields ::::::: count = 0 for key in mistake.mistakeFields: entry = mistake.mistakeFields[key] cell = entry[0][0] if isinstance(entry[0], list) else entry[0] cell = tcell(cell, anchor=mistake.anchor) if key.startswith('tpl'): targetcell = ws2[cell].value[1:] origval = tradeSummaries[count][frc.PL].unique()[0] # print(ws2[targetcell].value, '<------->', origval ) if origval == 0: self.assertIs(ws2[targetcell].value, None) else: self.assertAlmostEqual(ws2[targetcell].value, origval) count = count + 1 # These next two tests (for plx and mistakex) have no unique entries (without user # input or mock) Test for the static values and that plx entry is next to its header if key.startswith('pl'): headval = 'Proceeds Lost' targetcell = ws2[cell].value[1:] headercell = 'A' + targetcell[1:] # print(ws2[targetcell].value, '<------->', None) # print(headercell, '------->', ws2[headercell].value) self.assertTrue(ws2[targetcell].value is None) self.assertEqual(ws2[headercell].value, headval) if key.startswith('mistake'): noteval = 'Final note' targetcell = ws2[cell].value[1:] # print(ws2[targetcell].value, '<------->', noteval) self.assertEqual(ws2[targetcell].value, noteval)
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 run(infile='trades.csv', outdir=None, theDate=None, indir=None, infile2=None, mydevel=True): ''' Run structjour. Temporary picker for input type based on filename. If infile has 'activity' in it and ends in .html, then its IB Activity Statement web page (as a file on this system) :params infile: Name of the input file. Default trades.csv--a DAS export from the trades window. If infile contains the string 'trades', input type is set to DAS. If infile contains the string 'activity', input type is set to IB Activity. Default will try DAS :params outdir: Location to write the output file. :params theDate: The date of this input file. If trades lack a Date, this date be the trade date. :params indir: Location of the input file. :parmas infile2: Name of the DAS positions file. Will default to indir/positions.csv :params mydevel: If True, use a specific file structure and let structjour create it. All can be overriden by using the specific parameters above. ''' settings = QSettings('zero_substance', 'structjour') settings.setValue('runType', 'CONSOLE') # indir=None, outdir=None, theDate=None, infile='trades.csv', mydevel=False jf = JournalFiles(indir=indir, outdir=outdir, theDate=theDate, infile=infile, infile2=infile2, mydevel=mydevel) name, ext = os.path.splitext(jf.infile.lower()) if name.find('activity') > -1 and ext == '.html': jf.inputType = 'IB_HTML' statement = Statement_IBActivity(jf) df = statement.getTrades_IBActivity(jf.inpathfile) elif name.find('trades') > -1 and ext == '.csv': # This could be an IB CSV--so this is temporary-- when I enable some sort of IB CSV, will # probably do some kind of class heirarchy here for statements. tkt = Ticket(jf) df, jf = tkt.getTrades() # trades = pd.read_csv(jf.inpathfile) else: #Temporary print('Opening a non standard file name in DAS') tkt = Ticket(jf) df, jf = tkt.getTrades() idf = InputDataFrame() trades, success = idf.processInputFile(df, jf.theDate, jf) if not success: print('Failed. Between you and me, I think its a programming error') return jf tu = DefineTrades() inputlen, dframe, ldf = tu.processOutputDframe(trades) # Process the openpyxl excel object using the output file DataFrame. Insert # images and Trade Summaries. margin = 25 # Create the space in dframe to add the summary information for each trade. # Then create the Workbook. ls = LayoutSheet(margin, inputlen) imageLocation, dframe = ls.imageData(dframe, ldf) wb, ws, nt = ls.createWorkbook(dframe) tf = TradeFormat(wb) ls.styleTop(ws, len(nt.columns), tf) assert len(ldf) == len(imageLocation) mstkAnchor = (len(dframe.columns) + 2, 1) mistake = MistakeSummary(numTrades=len(ldf), anchor=mstkAnchor) mistake.mstkSumStyle(ws, tf, mstkAnchor) mistake.dailySumStyle(ws, tf, mstkAnchor) tradeSummaries = ls.runSummaries(imageLocation, ldf, jf, ws, tf) # app = QApplication(sys.argv) # qtf = QtForm() # qtf.fillForm(tradeSummaries[1]) # app.exec_() ls.populateMistakeForm(tradeSummaries, mistake, ws, imageLocation) ls.populateDailySummaryForm(tradeSummaries, mistake, ws, mstkAnchor) ls.save(wb, jf) print("Processing complete. Saved {}".format(jf.outpathfile)) return jf