1

I am trying to test different sell Simple Moving Average criteria based on the given buy date.

I have a database of buy data as follows (df_buy). I want to fill out the NaN values. (FCU = First Close Under)

  Symbol                Time  buy_price     LOD  date_FCU_10dMA  price_FCU_10dMA
0    AMD 2019-12-12 09:36:00      39.52   27.43             NaN              NaN
1    AMD 2020-01-16 09:33:00      49.21   27.43             NaN              NaN
2   BITF 2021-08-03 09:47:00       4.26    2.81             NaN              NaN
3   DOCN 2021-06-14 09:32:00      41.76   35.35             NaN              NaN
4   NVDA 2020-07-29 09:38:00     416.81  169.32             NaN              NaN
5   NVDA 2020-09-25 10:34:00     499.72  169.32             NaN              NaN
6   UPST 2021-02-09 09:32:00      76.83   22.61             NaN              NaN
7   UPST 2021-03-18 09:32:00      88.56   22.61             NaN              NaN

I have another database with stock daily data as follows (df_day)

        Symbol                Time  Close   LOD   10MA    20MA
2722244   AEHR 2019-11-25 16:00:00   1.90  1.29  2.005  1.8870
2722289   AEHR 2019-11-26 16:00:00   1.92  1.29  2.032  1.8925
2722383   AEHR 2019-12-02 16:00:00   1.88  1.29  2.056  1.8985
2722435   AEHR 2019-12-03 16:00:00   1.88  1.29  2.046  1.8995
2722471   AEHR 2019-12-04 16:00:00   1.89  1.29  2.020  1.9055
2722569   AEHR 2019-12-06 16:00:00   1.93  1.29  1.993  1.9140

Based on the strategy, the first close must be at least 2 days after the buy date.

df_filt2['price_FCU_10dMA'] = (df_buy['buy_price'] > df_day['20MA'])
Error: ValueError: Can only compare identically-labeled Series objects


for i, row in df_buy.iterrows():
    # drop unlisted symbols
    filt1 = (df_day['Symbol'] != df_buy['Symbol'].loc[i])
    df_filt1 = df_day.drop(index=df_day[filt1].index)

    # drop trades from before buy date + 2
    filt2 = (pd.to_datetime(df_day['Time']) < (pd.to_datetime(df_buy['Time'].loc[i] + pd.to_timedelta(2, unit='d'))))
    df_filt2 = df_filt1.drop(index=df_filt1[filt2].index)
    # sort values
    df_filt2.sort_values(by=['Symbol', 'Time'], inplace=True)

    # drop rows where Close is above the 10MA
    df_filt2['price_FCU_10dMA'] = (df_buy['buy_price'] > df_day['20MA'])
    filt3 = (df_filt2['price_FCU_10dMA'] == False)

    # trail_sell = df_filt2[filt3].loc[10]
    df_buy['price_FCU_10dMA'].loc[i] = df_filt2.loc[filt3, 'Close'].iloc[0]  # returns the single value of first close under 10ma
    df_buy['date_FCU_10dMA'].loc[i] = df_filt2.loc[filt3, 'Time'].iloc[0]  # returns the single value of first close under 10ma
Trippy Dippy
  • 99
  • 6

1 Answers1

0

You can combine all 3 conditions in a logical statement:

two_days = pd.to_timedelta('2d')
no_match = pd.Series({'Time': np.nan, 'Close': np.nan})

for i, row in df_buy.iterrows():
    cond = (
        (df_day['Symbol'] == row['Symbol']) &
        (df_day['Time'] > row['Time'] + two_days) &
        (df_day['Close'] < df_day['10MA'])
    )
    match = no_match if not cond.any() else df_day[cond].head(1)
    df_buy.loc[i, ['price_FCU_10dMA', 'date_FCU_10dMA']] = match[['Close', 'Time']]
Code Different
  • 90,614
  • 16
  • 144
  • 163
  • Thanks @Code Different. That is much more elegan, however, there's still a problem: `df_day[cond].head(1)` (2nd last line) returns the correct row, however, the values aren't being filled in to the desired columns – Trippy Dippy Sep 12 '21 at 10:15
  • Try replacing head(1) with iloc[0] – Code Different Sep 12 '21 at 13:33
  • I split up `df_buy.loc[i, ['price_FCU_10dMA', 'date_FCU_10dMA']] = match[['Close', 'Time']]` into `df_buy.loc[i, 'price_FCU_10dMA'] = match['Close']` and `df_buy.loc[i, 'date_FCU_10dMA'] = match['Time']` and it now works. Cheers – Trippy Dippy Sep 12 '21 at 14:05