Home > Software engineering >  Get stock Low of Day (LOD) price for incomplete daily bar using minute bar data (multiple stocks, mu
Get stock Low of Day (LOD) price for incomplete daily bar using minute bar data (multiple stocks, mu

Time:09-17

I have a dataframe of minute data for multiple Stocks, each stock has multiple sessions. See sample below

         Symbol            Time     Open    High    Low  Close  Volume  LOD
2724312   AEHR 2019-09-23 09:31:00   1.42   1.42   1.42   1.42     200  NaN
2724313   AEHR 2019-09-23 09:43:00   1.35   1.35   1.34   1.34    6062  NaN
2724314   AEHR 2019-09-23 09:58:00   1.35   1.35   1.29   1.30    8665  NaN
2724315   AEHR 2019-09-23 09:59:00   1.32   1.32   1.32   1.32     100  NaN
2724316   AEHR 2019-09-23 10:00:00   1.35   1.35   1.35   1.35     400  NaN
...        ...                 ...    ...    ...    ...    ...     ...  ...
4266341     ZI 2021-09-10 15:56:00  63.08  63.16  63.08  63.15   18205  NaN
4266342     ZI 2021-09-10 15:57:00  63.14  63.14  63.07  63.07   19355  NaN
4266343     ZI 2021-09-10 15:58:00  63.07  63.12  63.07  63.10   16650  NaN
4266344     ZI 2021-09-10 15:59:00  63.09  63.12  63.06  63.11   25775  NaN
4266345     ZI 2021-09-10 16:00:00  63.11  63.17  63.11  63.17   28578  NaN

I need the Low Of Day(LOD) for the session (9:30-4pm) up to the time in each row.

The completed df should look like this

         Symbol            Time     Open    High    Low  Close  Volume  LOD
2724312   AEHR 2019-09-23 09:31:00   1.42   1.42   1.42   1.42     200  1.42   
2724313   AEHR 2019-09-23 09:43:00   1.35   1.35   1.34   1.34    6062  1.34   
2724314   AEHR 2019-09-23 09:58:00   1.35   1.35   1.29   1.30    8665  1.29   
2724315   AEHR 2019-09-23 09:59:00   1.32   1.32   1.32   1.32     100  1.29   
2724316   AEHR 2019-09-23 10:00:00   1.35   1.35   1.35   1.35     400  1.29   
...        ...                 ...    ...    ...    ...    ...     ...  ...
4266341     ZI 2021-09-10 15:56:00  63.08  63.16  63.08  63.15   18205  63.08  
4266342     ZI 2021-09-10 15:57:00  63.14  63.14  63.07  63.07   19355  63.07  
4266343     ZI 2021-09-10 15:58:00  63.07  63.12  63.07  63.10   16650  63.07  
4266344     ZI 2021-09-10 15:59:00  63.09  63.12  63.06  63.11   25775  63.06  
4266345     ZI 2021-09-10 16:00:00  63.11  63.17  63.11  63.17   28578  63.06 

My current solution

prev_symbol = "WXYZ"
prev_low = 10000000
prev_session = datetime.date(1920, 1, 1)
session_start = 1

for i, row in df.iterrows():
    current_session = (df['Time'].iloc[i]).time()
    current_symbol = df['Symbol'].iloc[i]
    if current_symbol == prev_symbol:
        if current_session == prev_session:
            sesh_low = df.iloc[session_start:i, 'Low'].min()
            df.at[i, 'LOD'] = sesh_low
        else:
            df.at[i, 'LOD'] = df.at[i, 'Low']
            prev_session = current_session
            session_start = i
    else:
        df.at[i, 'LOD'] = df.at[i, 'Low']
        prev_symbol = current_symbol
        prev_session = current_session
        session_start = i

This returns a SettingWithCopyWarning error. Please help

CodePudding user response:

You can try .groupby() .expanding():

# if you have values already converted/sorted, skip:
# df["Time"] = pd.to_datetime(df["Time"])
# df = df.sort_values(by=["Symbol", "Time"])

df["LOD"] = df.groupby("Symbol")["Low"].expanding().min().values
print(df)

Prints:

        Symbol                 Time   Open   High    Low  Close  Volume    LOD
2724312   AEHR  2019-09-23 09:31:00   1.42   1.42   1.42   1.42     200   1.42
2724313   AEHR  2019-09-23 09:43:00   1.35   1.35   1.34   1.34    6062   1.34
2724314   AEHR  2019-09-23 09:58:00   1.35   1.35   1.29   1.30    8665   1.29
2724315   AEHR  2019-09-23 09:59:00   1.32   1.32   1.32   1.32     100   1.29
2724316   AEHR  2019-09-23 10:00:00   1.35   1.35   1.35   1.35     400   1.29
4266341     ZI  2021-09-10 15:56:00  63.08  63.16  63.08  63.15   18205  63.08
4266342     ZI  2021-09-10 15:57:00  63.14  63.14  63.07  63.07   19355  63.07
4266343     ZI  2021-09-10 15:58:00  63.07  63.12  63.07  63.10   16650  63.07
4266344     ZI  2021-09-10 15:59:00  63.09  63.12  63.06  63.11   25775  63.06
4266345     ZI  2021-09-10 16:00:00  63.11  63.17  63.11  63.17   28578  63.06
  • Related