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