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 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 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