Is there a way to find out if the last value is in the lower 50% range of the previous six days values? I want to add another column that shows yes or no. I tried sorting the previous six to get the middle value, but could not compare it to last and/or make it iterate to populate the new column. My data looks like below:
Date Open High Low Close Adj Close Volume
2020-12-14 3675.270020 3697.610107 3645.840088 3647.489990 3647.489990 4594920000
2020-12-15 3666.409912 3695.290039 3659.620117 3694.620117 3694.620117 4360280000
2020-12-16 3696.250000 3711.270020 3688.570068 3701.169922 3701.169922 4056950000
2020-12-17 3713.649902 3725.120117 3710.870117 3722.479980 3722.479980 4184930000
2020-12-18 3722.389893 3726.699951 3685.840088 3709.409912 3709.409912 7068340000
I spent 5-6 hours googling and trying to no avail, any sort of guidance is greatly appreciated
Understanding your question as asking for the ratio of the previous day's closing price to the average of the previous six days, I created the following code. Sort the closing prices of the retrieved stocks in descending order. In a new column, use the rolling function to calculate the six-day average and add it. Then shift the data to align the new column with the closing price to be compared. Then we added the ratio calculation.
import yfinance as yf
data = yf.download("AAPL", start="2020-11-17", end="2020-12-18")['Adj Close'].to_frame()
data.sort_index(ascending=False, inplace=True)
data['pre_6'] = data.rolling(6).mean()
data['pre_6'] = data['pre_6'].shift(-5)
data['check'] = data['Adj Close'] /data['pre_6']
data
Adj Close pre_6 check
Date
2020-12-17 128.699997 125.303332 1.027108
2020-12-16 127.809998 124.149999 1.029480
2020-12-15 127.879997 123.578332 1.034809
2020-12-14 121.779999 122.889999 0.990968
2020-12-11 122.410004 122.968333 0.995460
2020-12-10 123.239998 123.056666 1.001490
2020-12-09 121.779999 123.030000 0.989840
2020-12-08 124.379997 123.186667 1.009687
2020-12-07 123.750000 122.298335 1.011870
2020-12-04 122.250000 121.105001 1.009455
2020-12-03 122.940002 120.068334 1.023917
2020-12-02 123.080002 118.773333 1.036260
2020-12-01 122.720001 117.234999 1.046786
2020-11-30 119.050003 116.338332 1.023308
2020-11-27 116.589996 116.269998 1.002752
2020-11-25 116.029999 116.509998 0.995880
2020-11-24 115.169998 117.069998 0.983770
2020-11-23 113.849998 117.924999 0.965444
2020-11-20 117.339996 NaN NaN
2020-11-19 118.639999 NaN NaN
2020-11-18 118.029999 NaN NaN
2020-11-17 119.389999 NaN NaN
2020-11-16 120.300003 NaN NaN