4

I am attempting to use an oscillator (relative strength index) to know when to buy and sell a stock. I created a dataframe for RSI and closing price. I am able to plot both but I want to also add to my plot when the RSI hits a buy and sell signal. So in order to do this I need to create a comparison of my RSI column when the RSI drops below 25, which will trigger my buy signal and a sell signal for my RSI if it goes over 85. My issue is that I cannot figure out pull my closing price column on the date when my RSI column drops below 25 until the date when my RSI column rises above 85. All I get is Nan in my new dataframe column.

#rsi
import pandas
import warnings
import pandas_datareader.data as web
import datetime
import matplotlib.pyplot as plt
warnings.filterwarnings('ignore')
# Window length for moving average
window_length = 14

# Dates
start = datetime.datetime(2016, 1, 5)
end = datetime.datetime(2016, 12, 31)

# Get data
data = web.DataReader('FB', 'morningstar', start, end)
df= pd.DataFrame(data)

# Get just the close
close = data['Close']
# Get the difference in price from previous step
delta = close.diff()
# Get rid of the first row, which is NaN since it did not have a previous 
# row to calculate the differences
delta = delta[1:] 

# Make the positive gains (up) and negative gains (down) Series
up, down = delta.copy(), delta.copy()
up[up < 0] = 0
down[down > 0] = 0

# Calculate the EWMA
roll_up1 = pandas.stats.moments.ewma(up, window_length)
roll_down1 = pandas.stats.moments.ewma(down.abs(), window_length)

# Calculate the RSI based on EWMA
RS1 = roll_up1 / roll_down1
RSI1 = 100.0 - (100.0 / (1.0 + RS1))

# Calculate the SMA
roll_up2 = pandas.rolling_mean(up, window_length)
roll_down2 = pandas.rolling_mean(down.abs(), window_length)

# Calculate the RSI based on SMA
RS2 = roll_up2 / roll_down2
RSI2 = 100.0 - (100.0 / (1.0 + RS2))
df['RSI2']=RSI2

df=df.dropna(axis=0)

df['RSI2']=df['RSI2'].astype(float)




df['BUY']=df['Close'][df['RSI2'] < 25]
print (df['BUY'])




# Compare graphically
plt.figure()
df['BUY'].plot(title='FB',figsize = (20, 5))
plt.show()
RSI1.plot(title='Relative Strength Index',figsize = (20, 5))
RSI2.plot(figsize = (20, 5))
plt.legend(['RSI via EWMA', 'RSI via SMA'])
plt.show()
Jimbo
  • 143
  • 1
  • 8

1 Answers1

1

If i got your question correctly, then What you are looking for is there in pandas (pd.query() just like in SQL, e.g

df['rsi_query'] = np.zeros(df.shape[0])
myquery = df.query('RSI>.25 & RSI<.85').index
df.iloc[myquery, -1] = 1(replace it with what you want)

Further reference

Aditya
  • 2,380
  • 2
  • 14
  • 39
  • Yes exactly I want to replace with the closing price at that date when it hits 25 in order to buy and plot the stock until my sell signal at 85. – Jimbo Apr 15 '18 at 02:56
  • Do the same for the other columns via this myquery if this is the sole deciding condition and then just get the values at such, such indices – Aditya Apr 15 '18 at 02:59
  • Why the -1? and the 1? is that just before and after the .query? – Jimbo Apr 15 '18 at 03:01
  • df['rsi_query'] = np.zeros(df.shape[0]) myquery = df.query('RSI2>.25 & RSI2<.85').index df.iloc[myquery, -1] = 1(df['Close']) Error: 'int' object is not callable – Jimbo Apr 15 '18 at 03:05
  • -1 is to refer the last column, as by default the column gets added at the last, you need to extract those rows from your dataset or a particular column.. what the myquery does it to get all the indices of the DF where the given condition holds..(try printing it out?) – Aditya Apr 15 '18 at 03:18
  • so I printed out the myquery you created and all I get is time series data for the whole time series. The dates include all the dates but the labels are as follows:(maybe you can explain the date from the labels) I cant add all the labels because it is too long here are a few:[14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, – Jimbo Apr 15 '18 at 03:30
  • Those are the row nos where the condition is true – Aditya Apr 15 '18 at 06:20