Using Pandas, generate weekly open, high, low, close and simple moving average of Close for each day in historical data

Question

From daily stock OHLC data as under,

Date                      Daily Open          Daily High            Daily Low             Daily Close

31-Oct-22            17910.2                 18022.8                 17899.9                 18012.2

1-Nov-22             18130.7                 18175.8                 18060.15              18145.4

2-Nov-22             18177.9                 18178.75              18048.65              18082.85

3-Nov-22             17968.35              18106.3                 17959.2                 18052.7

4-Nov-22             18053.4                 18135.1                 18017.15              18117.15

I am trying to get weekly OHLC data (as below) for each day of the week using pandas but not managed to get it despite spending over a month looking for a solution. The closest I could get is to use pandas resample at Weekly frequency but it gives no output for Monday to Thursday. It gives output only for Friday being last day of the week. Can this be accomplished using pandas or does it require iterative processing using python loop.

Date                      Weekly Open     Weekly High       Weekly Low        Weekly Close

31-Oct-22            17910.2                 18022.8                 17899.9                 18012.2

1-Nov-22             17910.2                 18175.8                 17899.9                 18145.4

2-Nov-22             17910.2                 18178.75              17899.9                 18082.85

3-Nov-22             17910.2                 18178.75              17899.9                 18052.7

4-Nov-22             17910.2                 18178.75              17899.9                 18117.15

On this weekly data, I am also trying to get a Simple moving average(SMA) of Weekly close for each day.

So for the next week data, on 7 Nov 22(Monday), the Simple moving average will be (18202.8 ( closed on Monday) + 18117.15(close on previous Friday) )/2=18159.97 and on 9 Nov 22,  it will (18137.07+18117.15)/2=18137.07 and so on. Can this also be accomplished using pandas_ta library once the weekly OHLC dataframe is available as above or does it require iterative processing using python loops

Date                      Weekly Close     2weekSMA

7-Nov-22             18202.8                 18159.975

9-Nov-22             18157                    18137.075

10-Nov-22           18028.2                 18072.675

11-Nov-22           18349.7                 18233.425

Answer 1

Sounds like you want to use the .rolling function. That is, for each day of the week you want to see stats for the OHLC data that covers the last n trading days, as opposed to the calendar week where you get only Friday's data for example.

not sure of your dataframe structure fully, but you could do something like:

ma2 = df.close.rolling(2).mean() 
ma5 = df.open.rolling(5).mean()

and this would give you columns to use as the rolling averages for 2 and 5 days respectively for the variables 'close' and 'open'.

hth

Answer 2

I have the same needs as you, here is my try

Assuming 'price' is daily stock OHLC data

def week_gen(dataframe):
    if not dataframe.empty:
        dataframe['Weekly Open'] = dataframe['Open'].values[0]
        dataframe['Weekly High'] = dataframe['High'].cummax()
        dataframe['Weekly Low'] = dataframe['Low'].cummin()
        dataframe['Weekly Close'] = dataframe['Close']
        dataframe['Weekly Volume'] = dataframe['Volume'].cumsum()
        return dataframe
    else:
        return None

tmp = []
for _, group in price.resample('W'):
    tmp.append(week_gen(group))
price = pd.concat(tmp)