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