I need to decide whether a row of stock-quotes meet certain criteria. For this I use the function 'Trade_signal_calc' to test if MACD (one col in df) is positive (first if). Then if 'MACD' passes the 'MACDsig' (another col from df) from below (second if) - and finally if 'MACD' passes 'MACDsig' from above (third if).
These 2 cols 'MACD' and 'MACDsig' from my df must be passed as parameter-input into my f(). The resulting col is called 'Trade' and should show either 'Buy', 'Sell', or NaN.
I am comparing only same-tickers to each-other, so I use pd.groupby for this grouping. It works.
But I have struggled to get more than one input parameter (col) into my f(), so have finally found a solution in functools.partial mentioned here: How does the functools partial work in Python?
And it seems to work/run. However see my questions below my code and output
My code
import pandas as pd
import numpy as np
from io import StringIO
import functools
text = """Ticker Date Adj_Close Volume MACD emaSlw emaFst MACDsig MACDperc
A 18-07-2016 46.8 1362900.0 0.55 45.81 45.26 0.21 -0.9954
A 19-07-2016 46.98 2579000.0 0.6 45.99 45.39 0.29 -0.9937
AA 18-07-2016 10.92 16297800.0 0.32 10.27 9.94 0.1 -0.99
AA 19-07-2016 10.63 14316200.0 0.33 10.32 9.99 0.15 -0.9856
AAL 18-07-2016 36.03 8604200.0 1.15 32.84 31.69 -0.08 -10.023
AAL 19-07-2016 36.01 7928100.0 1.32 33.33 32.01 0.19 -0.9942
AAP 18-07-2016 164.4 516800.0 2.83 162.59 159.75 2.72 -0.9832
AAP 19-07-2016 163.7 854700.0 2.71 162.76 160.05 2.76 -0.983
AAPL 18-07-2016 99.83 36439900.0 0.5 97.36 96.86 -0.16 -10.017
AAPL 19-07-2016 99.87 23703900.0 0.67 97.75 97.08 -0.01 -10.001
ABBV 18-07-2016 63.56 6384800.0 0.9 63.06 62.16 0.71 -0.9887
ABBV 19-07-2016 63.32 5716800.0 0.86 63.1 62.25 0.78 -0.9876
ABC 18-07-2016 86.03 1113600.0 2.31 82.91 80.6 1.52 -0.9817
ABC 19-07-2016 85.92 1975400.0 2.38 83.38 81.0 1.7 -0.9796
ABT 18-07-2016 42.09 7524200.0 -1.05 41.14 40.09 0.75 -0.9818
ABT 19-07-2016 41.8 8395400.0 1.02 41.24 40.22 0.84 -0.9796"""
df = pd.read_csv(StringIO(text), delim_whitespace=1, parse_dates=[0], index_col=1)
df.drop(['Volume', 'emaSlw', 'emaFst', 'MACDperc'],inplace=True,axis=1)
#print('df to check numbering and cols. \n%s' %(df.iloc[0:4,0:6]))
#print('\ndf cols. 3-4 = MACD, MACDsig \n%s' %(df.iloc[0:4,2:4])) #rows, cols
def Trade_signal_calc(group, x, y): # group
#def Trade_signal_calc(group):
if df['MACD'].irow(-1) > 0: # MACD is pos. so trade (BUY/Sell)
# BUY signals
print('\n[MACD].irow(-1) > 0 PASSED 1st if\n Group: \n%s' %(group))
# if todays MACD is higher than signal, AND yesturday's MACD was lower than signal
if (df['MACD'].irow(-1) > df['MACDsig'].irow(-1)) \
& (df['MACD'].irow(-2) < df['MACDsig'].irow(-2)) :
df['Trade'] = 'Buy'
print('\n(df[MACD].irow(-1) > df[MACDsig].irow(-1)) \
& (df[MACD].irow(-2) < df[MACDsig].irow(-2)) \n== PASSED 2nd if\n Group: \n%s' %(group))
return df
# SELL signals
# if todays MACD is lower than signal, AND yesturday's MACD was higher than signal - reverse the above
elif (df['MACD'].irow(-1) < df['MACDsig'].irow(-1)) \
& ((df['MACD'].irow(-2)) > df['MACDsig'].irow(-2)) :
df['Trade'] = 'Sell'
print('(df[MACD].irow(-1) < df[MACDsig].irow(-1)) \
& (df[MACD].irow(-2) > df[MACDsig].irow(-2)) == PASSED 3rd (el)if\n MACD: \n%s, \nMACDsig: \n%s' %(x, y))
return df
else: # No strong Buy or Sell signals
print('No strong Buy or Sell signals')
return df
else: # MACD is neg. so don't trade
print('MACD is neg. so dont trade')
return df
if __name__ == '__main__':
# Multiindex Needed for my groupby to work properly ???
df = df.set_index('Ticker', append=True)
'''Testing functools.partial to manage the number of params for groupby to input
2 cols from df.'''
fnc = functools.partial(Trade_signal_calc, x=df['MACD'], y=df['MACDsig'])
df['Trade'] = df.groupby(level='Ticker').agg({'MACD': fnc, 'MACDsig': fnc})
# df['Trade'] = df.groupby(level='Ticker').apply(lambda x: fcn())
print ('\ndf with Buy & Sell signals & with multi-index (of Date and Ticker) \ndfTrade shows trades from MACD & MACDsig comparison (summary from df) (Output)\n%s\nSignals: \nBuy: (df(MACD[-1]) > df(MACDsig[-1])) & (df(MACD[-2]) < df(MACDsig[-2])) \nSell: (df(MACD[-1]) < df(MACDsig[-1])) & (df(MACD[-2]) > df(MACDsig[-2])) \n\n%s' %(75*'-',df))#[['Trade','MACD','MACDsig','Adj_Close','Date']]))
# Resetting index from before doing groupby w. multi-index
df = df.reset_index('Ticker')
# Test the first 3 rows of each group for 'Difference' col transgress groups...
df = df.groupby('Ticker').head(3).reset_index().set_index('Date')
print ('%s\ndf (summary from df - without multi-index) (Output)\n%s\n' %(75*'-',df))
Output (Stacktrace)
[MACD].irow(-1) > 0 PASSED 1st if
Group:
Date Ticker
18-07-2016 A 0.55
19-07-2016 A 0.60
Name: MACD, dtype: float64
(df[MACD].irow(-1) > df[MACDsig].irow(-1)) & (df[MACD].irow(-2) < df[MACDsig].irow(-2))
== PASSED 2nd if
Group:
Date Ticker
18-07-2016 A 0.55
19-07-2016 A 0.60
Name: MACD, dtype: float64
[MACD].irow(-1) > 0 PASSED 1st if
Group:
Date Ticker
18-07-2016 AA 0.32
19-07-2016 AA 0.33
Name: MACD, dtype: float64
(df[MACD].irow(-1) > df[MACDsig].irow(-1)) & (df[MACD].irow(-2) < df[MACDsig].irow(-2))
== PASSED 2nd if
Group:
Date Ticker
18-07-2016 AA 0.32
19-07-2016 AA 0.33
Name: MACD, dtype: float64
[MACD].irow(-1) > 0 PASSED 1st if
Group:
Date Ticker
18-07-2016 AAL 1.15
19-07-2016 AAL 1.32
Name: MACD, dtype: float64
(df[MACD].irow(-1) > df[MACDsig].irow(-1)) & (df[MACD].irow(-2) < df[MACDsig].irow(-2))
== PASSED 2nd if
Group:
Date Ticker
18-07-2016 AAL 1.15
19-07-2016 AAL 1.32
Name: MACD, dtype: float64
[MACD].irow(-1) > 0 PASSED 1st if
Group:
Date Ticker
18-07-2016 AAP 2.83
19-07-2016 AAP 2.71
Name: MACD, dtype: float64
(df[MACD].irow(-1) > df[MACDsig].irow(-1)) & (df[MACD].irow(-2) < df[MACDsig].irow(-2))
== PASSED 2nd if
Group:
Date Ticker
18-07-2016 AAP 2.83
19-07-2016 AAP 2.71
Name: MACD, dtype: float64
[MACD].irow(-1) > 0 PASSED 1st if
Group:
Date Ticker
18-07-2016 AAPL 0.50
19-07-2016 AAPL 0.67
Name: MACD, dtype: float64
(df[MACD].irow(-1) > df[MACDsig].irow(-1)) & (df[MACD].irow(-2) < df[MACDsig].irow(-2))
== PASSED 2nd if
Group:
Date Ticker
18-07-2016 AAPL 0.50
19-07-2016 AAPL 0.67
Name: MACD, dtype: float64
[MACD].irow(-1) > 0 PASSED 1st if
Group:
Date Ticker
18-07-2016 ABBV 0.90
19-07-2016 ABBV 0.86
Name: MACD, dtype: float64
(df[MACD].irow(-1) > df[MACDsig].irow(-1)) & (df[MACD].irow(-2) < df[MACDsig].irow(-2))
== PASSED 2nd if
Group:
Date Ticker
18-07-2016 ABBV 0.90
19-07-2016 ABBV 0.86
Name: MACD, dtype: float64
[MACD].irow(-1) > 0 PASSED 1st if
Group:
Date Ticker
18-07-2016 ABC 2.31
19-07-2016 ABC 2.38
Name: MACD, dtype: float64
(df[MACD].irow(-1) > df[MACDsig].irow(-1)) & (df[MACD].irow(-2) < df[MACDsig].irow(-2))
== PASSED 2nd if
Group:
Date Ticker
18-07-2016 ABC 2.31
19-07-2016 ABC 2.38
Name: MACD, dtype: float64
[MACD].irow(-1) > 0 PASSED 1st if
Group:
Date Ticker
18-07-2016 ABT -1.05
19-07-2016 ABT 1.02
Name: MACD, dtype: float64
(df[MACD].irow(-1) > df[MACDsig].irow(-1)) & (df[MACD].irow(-2) < df[MACDsig].irow(-2))
== PASSED 2nd if
Group:
Date Ticker
18-07-2016 ABT -1.05
19-07-2016 ABT 1.02
Name: MACD, dtype: float64
[MACD].irow(-1) > 0 PASSED 1st if
Group:
Date Ticker
18-07-2016 A 0.21
19-07-2016 A 0.29
Name: MACDsig, dtype: float64
(df[MACD].irow(-1) > df[MACDsig].irow(-1)) & (df[MACD].irow(-2) < df[MACDsig].irow(-2))
== PASSED 2nd if
Group:
Date Ticker
18-07-2016 A 0.21
19-07-2016 A 0.29
Name: MACDsig, dtype: float64
[MACD].irow(-1) > 0 PASSED 1st if
Group:
Date Ticker
18-07-2016 AA 0.10
19-07-2016 AA 0.15
Name: MACDsig, dtype: float64
(df[MACD].irow(-1) > df[MACDsig].irow(-1)) & (df[MACD].irow(-2) < df[MACDsig].irow(-2))
== PASSED 2nd if
Group:
Date Ticker
18-07-2016 AA 0.10
19-07-2016 AA 0.15
Name: MACDsig, dtype: float64
[MACD].irow(-1) > 0 PASSED 1st if
Group:
Date Ticker
18-07-2016 AAL -0.08
19-07-2016 AAL 0.19
Name: MACDsig, dtype: float64
(df[MACD].irow(-1) > df[MACDsig].irow(-1)) & (df[MACD].irow(-2) < df[MACDsig].irow(-2))
== PASSED 2nd if
Group:
Date Ticker
18-07-2016 AAL -0.08
19-07-2016 AAL 0.19
Name: MACDsig, dtype: float64
[MACD].irow(-1) > 0 PASSED 1st if
Group:
Date Ticker
18-07-2016 AAP 2.72
19-07-2016 AAP 2.76
Name: MACDsig, dtype: float64
(df[MACD].irow(-1) > df[MACDsig].irow(-1)) & (df[MACD].irow(-2) < df[MACDsig].irow(-2))
== PASSED 2nd if
Group:
Date Ticker
18-07-2016 AAP 2.72
19-07-2016 AAP 2.76
Name: MACDsig, dtype: float64
[MACD].irow(-1) > 0 PASSED 1st if
Group:
Date Ticker
18-07-2016 AAPL -0.16
19-07-2016 AAPL -0.01
Name: MACDsig, dtype: float64
(df[MACD].irow(-1) > df[MACDsig].irow(-1)) & (df[MACD].irow(-2) < df[MACDsig].irow(-2))
== PASSED 2nd if
Group:
Date Ticker
18-07-2016 AAPL -0.16
19-07-2016 AAPL -0.01
Name: MACDsig, dtype: float64
[MACD].irow(-1) > 0 PASSED 1st if
Group:
Date Ticker
18-07-2016 ABBV 0.71
19-07-2016 ABBV 0.78
Name: MACDsig, dtype: float64
(df[MACD].irow(-1) > df[MACDsig].irow(-1)) & (df[MACD].irow(-2) < df[MACDsig].irow(-2))
== PASSED 2nd if
Group:
Date Ticker
18-07-2016 ABBV 0.71
19-07-2016 ABBV 0.78
Name: MACDsig, dtype: float64
[MACD].irow(-1) > 0 PASSED 1st if
Group:
Date Ticker
18-07-2016 ABC 1.52
19-07-2016 ABC 1.70
Name: MACDsig, dtype: float64
(df[MACD].irow(-1) > df[MACDsig].irow(-1)) & (df[MACD].irow(-2) < df[MACDsig].irow(-2))
== PASSED 2nd if
Group:
Date Ticker
18-07-2016 ABC 1.52
19-07-2016 ABC 1.70
Name: MACDsig, dtype: float64
[MACD].irow(-1) > 0 PASSED 1st if
Group:
Date Ticker
18-07-2016 ABT 0.75
19-07-2016 ABT 0.84
Name: MACDsig, dtype: float64
(df[MACD].irow(-1) > df[MACDsig].irow(-1)) & (df[MACD].irow(-2) < df[MACDsig].irow(-2))
== PASSED 2nd if
Group:
Date Ticker
18-07-2016 ABT 0.75
19-07-2016 ABT 0.84
Name: MACDsig, dtype: float64
df with Buy & Sell signals & with multi-index (of Date and Ticker)
dfTrade shows trades from MACD & MACDsig comparison (summary from df) (Output)
---------------------------------------------------------------------------
Signals:
Buy: (df(MACD[-1]) > df(MACDsig[-1])) & (df(MACD[-2]) < df(MACDsig[-2]))
Sell: (df(MACD[-1]) < df(MACDsig[-1])) & (df(MACD[-2]) > df(MACDsig[-2]))
Adj_Close MACD MACDsig Trade
Date Ticker
18-07-2016 A 46.80 0.55 0.21 NaN
19-07-2016 A 46.98 0.60 0.29 NaN
18-07-2016 AA 10.92 0.32 0.10 NaN
19-07-2016 AA 10.63 0.33 0.15 NaN
18-07-2016 AAL 36.03 1.15 -0.08 NaN
19-07-2016 AAL 36.01 1.32 0.19 NaN
18-07-2016 AAP 164.40 2.83 2.72 NaN At least this should be marked 'Buy'
19-07-2016 AAP 163.70 2.71 2.76 NaN
18-07-2016 AAPL 99.83 0.50 -0.16 NaN
19-07-2016 AAPL 99.87 0.67 -0.01 NaN
18-07-2016 ABBV 63.56 0.90 0.71 NaN
19-07-2016 ABBV 63.32 0.86 0.78 NaN
18-07-2016 ABC 86.03 2.31 1.52 NaN
19-07-2016 ABC 85.92 2.38 1.70 NaN
18-07-2016 ABT 42.09 -1.05 0.75 NaN
19-07-2016 ABT 41.80 1.02 0.84 NaN
---------------------------------------------------------------------------
df (summary from df - without multi-index) (Output)
Ticker Adj_Close MACD MACDsig Trade
Date
18-07-2016 A 46.80 0.55 0.21 NaN
19-07-2016 A 46.98 0.60 0.29 NaN
18-07-2016 AA 10.92 0.32 0.10 NaN
19-07-2016 AA 10.63 0.33 0.15 NaN
18-07-2016 AAL 36.03 1.15 -0.08 NaN
19-07-2016 AAL 36.01 1.32 0.19 NaN
18-07-2016 AAP 164.40 2.83 2.72 NaN At least this should be marked 'Buy'
19-07-2016 AAP 163.70 2.71 2.76 NaN
18-07-2016 AAPL 99.83 0.50 -0.16 NaN
19-07-2016 AAPL 99.87 0.67 -0.01 NaN
18-07-2016 ABBV 63.56 0.90 0.71 NaN
19-07-2016 ABBV 63.32 0.86 0.78 NaN
18-07-2016 ABC 86.03 2.31 1.52 NaN
19-07-2016 ABC 85.92 2.38 1.70 NaN
18-07-2016 ABT 42.09 -1.05 0.75 NaN
19-07-2016 ABT 41.80 1.02 0.84 NaN
- My 2 last prints of the df (one with multi-index and one without it), do not show that actually the stock rows for AAP should be marked with 'Buy', but simply says NaN... This is wrong...
- I have made my if-statements print explicit results, so I can troubleshoot the if-tests. This is also not working according to my f(), as these print-outputs omit the second parameter 'MACDsig' col.
It looks as if my groupby works properly. Also the use of my fcn = .... runs, but in the upper print-output, it should print BOTH MACD AND MACDsig cols. This does not happen, but only the MACD specified as first input-parameter in the f() shows up as it should, why does the 'MACDsig' col not get printet in these outputs?
It most probably is also the cause for my if-statements to not work correctly.
[MACD].irow(-1) > 0 PASSED 1st if
Group:
Date Ticker
18-07-2016 A 0.55
19-07-2016 A 0.60
Name: MACD, dtype: float64
(df[MACD].irow(-1) > df[MACDsig].irow(-1)) & (df[MACD].irow(-2) < df[MACDsig].irow(-2))
== PASSED 2nd if
Group:
Date Ticker
18-07-2016 A 0.55
19-07-2016 A 0.60
Name: MACD, dtype: float64
