def getTrades(self, listDf=None): ''' Create an alternate dataFrame by ticket. For large share sizes this may have dramatically fewer transactions. :params listDf: Normally leave blank. If used, listDf should be the be a list of DFs. :params jf: A JournalFiles object as this new CSV file needs to be written into the outdir. :return: The DataFrame created version of the data. :side effects: Saves a csv file of all transactions as single ticket transactions to jf.inpathfile ''' # TODO: Add the date to the saved file name after we get the date sorted out. rc = ReqCol() if not listDf: listDf = self.getListOfTicketDF() DataFrameUtil.checkRequiredInputFields(listDf[0], rc.columns) newDF = DataFrameUtil.createDf(listDf[0], 0) for tick in listDf: t = self.createSingleTicket(tick) newDF = newDF.append(t) outfile = "tradesByTicket.csv" opf = os.path.join(self.jf.indir, outfile) newDF.to_csv(opf) self.jf.resetInfile(outfile) return newDF, self.jf
def processOutputDframe(self, trades): ''' Run the methods to create the new DataFrame and fill in the data for the new trade- centric DataFrame. ''' c = self._frc # Process the output file DataFrame trades = self.addFinReqCol(trades) newTrades = trades[c.columns] newTrades.copy() nt = newTrades.sort_values([c.ticker, c.acct, c.time]) nt = self.writeShareBalance(nt) nt = self.addStartTime(nt) nt.Date = pd.to_datetime(nt.Date) nt = nt.sort_values([c.ticker, c.acct, c.start, c.date, c.time], ascending=True) nt = self.addTradeIndex(nt) nt = self.addTradePL(nt) nt = self.addTradeDuration(nt) nt = self.addTradeName(nt) # ldf is a list of DataFrames, one per trade ldf = self.getTradeList(nt) ldf, nt = self.postProcessing(ldf) nt = DataFrameUtil.addRows(nt, 2) nt = self.addSummaryPL(nt) # Get the length of the original input file before adding rows for processing Workbook # later (?move this out a level) inputlen = len(nt) dframe = DataFrameUtil.addRows(nt, 2) return inputlen, dframe, ldf
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 as ex: print(ex) gotve = True finally: self.assertTrue(gotve, "Failed to throw value error") gotve = False try: DataFrameUtil.checkRequiredInputFields(rc, finReqCol.columns) except ValueError as ex: gotve = True finally: self.assertTrue(gotve, "Failed to throw a ValueError")
def test_dfUtil_createDf(self): '''Test method DataFrameUtil.createDf''' cols = pd.DataFrame(columns=[ 'Its', 'the', 'end', 'of', 'the', 'world', 'as', 'we', 'know', 'it' ]) cols2 = [ 'Its', 'the', 'end', 'of', 'the', 'world', 'as', 'we', 'know', 'it' ] numRow = 9 fill = '' x = DataFrameUtil.createDf(cols, numRow, fill) y = DataFrameUtil.createDf(cols2, numRow, fill) self.assertEqual(list(x.columns), list(y.columns)) self.assertEqual(len(x), len(y)) for xc, yc in zip(x.iloc[1], y.iloc[1]): self.assertEqual(xc, yc) self.assertEqual(xc, fill) fill = None y = DataFrameUtil.createDf(cols2, numRow, fill) for xc, yc in zip(x.iloc[1], y.iloc[1]): self.assertTrue(xc != yc) self.assertEqual(yc, fill)
def __init__(self, jf, df=None): self.jf = jf if not isinstance(df, pd.DataFrame): self.df = pd.read_csv(self.jf.inpathfile) else: self.df = df if 'Date' not in self.df.columns: self.df['Date'] = jf.theDate rc = ReqCol() DataFrameUtil.checkRequiredInputFields(self.df, rc.columns)
def __init__(self, df, interview, srf): ''' Create a dataframe that includes all the summary material for review. Some of this data comes from the program and some of it comes from the user. The user will determine which parts to fill out from a couple of options. :params:df: A DataFrame that includes the transactions, or tickets, from a singel trade. ''' self.interview = interview col = srf.tfcolumns.keys() TheTrade = pd.DataFrame(columns=col) TheTrade = DataFrameUtil.addRows(TheTrade, 1) self.srf = srf ix = df.index[-1] ix0 = df.index[0] # TODO This list should be retrieved from TheStrategyObject strats = [ 'ORB', 'ABCD', 'VWAP Reversal', 'Bull Flag', 'Fallen Angel', 'VWAP False Breakout', 'VWAP Reversal', '15 Minute Reversal', 'VWAP MA trend', 'Other', 'Skip' ] side = df.loc[ix0][frc.side] self.df = df self.TheTrade = TheTrade self.ix = ix self.ix0 = ix0 self.strats = strats self.side = side self.shares = 0 self.chartSlot1 = None self.chartSlot2 = None self.chartSlot3 = None
def createSingleTicket(self, tickTx): ''' Create a single row ticket from a Dataframe with a list (1 or more) of Transactions. :prerequisites: tickTx needs to have 'len(unique()) == 1' for side, symb, account, and cloid. That uniqueness is created in getListOfTicketDF() so use that to create a list of ticktTX. :params tickTx: A DataFrame with transactions from a single ticket :return: A single row data frame with total shares and average price. ''' rc = ReqCol() total = 0 totalPL = 0 for dummy, row in tickTx.iterrows(): total = total + (row[rc.price] * row[rc.shares]) totalPL = totalPL + row[rc.PL] totalShares = tickTx[rc.shares].sum() avgPrice = total / totalShares newDf = DataFrameUtil.createDf(tickTx, 0) oneRow = tickTx.sort_values([rc.time, rc.price]).head(1) newDf = newDf.append(oneRow) newDf.copy() newDf[rc.price] = avgPrice newDf[rc.shares] = totalShares newDf[rc.PL] = totalPL return newDf
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 processInputFile(self, trades, theDate=None, jf=None): ''' Run the methods for this object ''' reqCol = ReqCol() DataFrameUtil.checkRequiredInputFields(trades, reqCol.columns) trades = self.zeroPadTimeStr(trades) trades = trades.sort_values( [reqCol.acct, reqCol.ticker, reqCol.date, reqCol.time]) trades = self.mkShortsNegative(trades) swingTrade = self.getOvernightTrades(trades) swingTrade, success = self.figureOvernightTransactions(trades, jf) if not success: return None, success trades = self.insertOvernightRow(trades, swingTrade) trades = self.addDateField(trades, theDate) return trades, True
def testCheckRequiredColumnsThrow(self): '''Test DataFrameUtil.checkRequredInputFields for raising exceptions''' vals = [[1, 2, 3, 4, 5, 6, 7, 8, 9, 10], ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j']] apd = pd.DataFrame(vals) apd.columns = [[ 'Its', 'the', 'end', 'of', 'the', 'world', 'as', 'we', 'know', 'it' ]] columns = [ 'Its', 'the', 'end', 'of', 'the', 'world', 'as', 'we', 'know', 'it', 'sofuckit' ] # DataFrameUtil.checkRequiredInputFields(apd, columns) try: DataFrameUtil.checkRequiredInputFields(apd, columns) except ValueError: pass except Exception as ex: msg = "{0}{1}".format("Unexpected exception. ", ex) self.fail(msg) else: self.fail("Failed to throw expected exception") vals = [[1, 2, 3, 4, 5, 6, 7, 8, 9], ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i']] apd = pd.DataFrame(vals, columns=[ 'Its', 'the', 'end', 'of', 'world', 'as', 'we', 'know', 'it' ]) gotve = False try: DataFrameUtil.checkRequiredInputFields(apd, columns) except ValueError: gotve = True except Exception as ex: msg = "Wrong exception was thrown" + ex self.fail(msg) finally: self.assertTrue(gotve, "Failed to throw a Value Error Exception")
def test_dfUtil_addRow(self): '''Test method DataFrameUtil.addRows ''' cols2 = [ 'Its', 'the', 'end', 'of', 'the', 'world', 'as', 'we', 'know', 'it' ] numRow = 9 fill = 'something silly' fill2 = 'sillier' y = DataFrameUtil.createDf(cols2, numRow, fill=fill) y = DataFrameUtil.addRows(y, numRow, fill=fill2) self.assertEqual(len(y), numRow * 2) for i in range(numRow): for ii in y.iloc[i]: self.assertEqual(ii, fill) for i in range(numRow, numRow * 2): for ii in y.iloc[i]: self.assertEqual(ii, fill2)
def loadTradeSummaries(loc, trades): ''' Load up each trade summary in the excel doc into a 1 row DataFrame, return a list of these DataFrames :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() srf = SumReqFields() reqCol = srf.rc newdf = pd.DataFrame(columns=reqCol.values()) colFormat = srf.tfcolumns for rowNum in loc: newdf = DataFrameUtil.createDf(newdf, 1) for key in reqCol.keys(): 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 ldf.append(newdf) return ldf
def insertOvernightRow(self, dframe, swTrade): ''' Insert non-transaction rows that show overnight transactions. Set Side to one of: HOLD+, HOLD-, HOLD+B, HOLD_B :params dframe: The trades dataframe. :params swTrade: A data structure holding information about tickers with unbalanced shares. ''' rc = ReqCol() newdf = DataFrameUtil.createDf(dframe, 0) for ldf in self.getListTickerDF(dframe): # print(ldf[rc.ticker].unique()[0], ldf[rc.acct].unique()[0]) for trade in swTrade: if (trade['ticker'] == ldf[rc.ticker].unique()[0] and (trade['acct'] == ldf[rc.acct].unique()[0])): # msg = "Got {0} with the balance {1}, before {2} and after {3} in {4}" # print(msg.format(trade['ticker'], trade['shares'], trade['before'], # trade['after'], trade['acct'])) # insert a non transaction HOLD row before transactions of the same ticker if trade['before'] != 0: newldf = DataFrameUtil.createDf(dframe, 1) for j, dummy in newldf.iterrows(): if j == len(newldf) - 1: newldf.at[j, rc.time] = '00:00:01' newldf.at[j, rc.ticker] = trade['ticker'] if trade['before'] > 0: newldf.at[j, rc.side] = "HOLD-B" else: newldf.at[j, rc.side] = "HOLD+B" newldf.at[j, rc.price] = float(0.0) newldf.at[j, rc.shares] = -trade['before'] # ZeroSubstance' newldf.at[j, rc.acct] = trade['acct'] newldf.at[j, rc.PL] = 0 ldf = newldf.append(ldf, ignore_index=True) break # Insert a non-transaction HOLD row after transactions from the same ticker # Reusing ldf for something different here...bad form ... maybe ... # adding columns then appending and starting over if trade['after'] != 0: # print("Are we good?") ldf = DataFrameUtil.addRows(ldf, 1) for j, dummy in ldf.iterrows(): if j == len(ldf) - 1: ldf.at[j, rc.time] = '23:59:59' ldf.at[j, rc.ticker] = trade['ticker'] if trade['after'] > 0: ldf.at[j, rc.side] = "HOLD+" else: ldf.at[j, rc.side] = "HOLD-" ldf.at[j, rc.price] = float(0.0) # -trade makes the share balance work in excel # for shares held after close ldf.at[j, rc.shares] = 0 # -trade['after'] # 'ZeroSubstance' ldf.at[j, rc.acct] = trade['acct'] ldf.at[j, rc.PL] = 0 newdf = newdf.append(ldf, ignore_index=True, sort=False) return newdf