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

score 0 · Answer 1 · answered Jan 25 '23 at 03:23

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

score 0 · Answer 2 · answered Mar 14 '23 at 00:51

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)

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Mar 18 '23 at 14:10

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

2 Answers2