This post is the continuation of a previous post: Calculating the Weight of Gap Openings in a Stock Using SQL; the subject is the same but the application is via python programming language.

The stock data includes dividend adjustments.

The pandas package is used throughout the application.

In [1]:
import pandas as pd
In [2]:
MSFT = pd.read_csv("MSFT, 1D adjusted with dividents.csv")
In [3]:
# select necessary columns and rename column "time" as "date"
MSFT_open_close = MSFT[['time', 'open', 'close']].rename(columns={'time': 'date'})

# filter year 2023
MSFT_open_close = MSFT_open_close[(MSFT_open_close['date'] >= '2023-01-01') & (MSFT_open_close['date'] < '2024-01-01')]

The structure of the table *MSFT_open_close* is as follows:
In [4]:
MSFT_open_close.head()
Out[4]:
date open close
0 2023-01-03 239.633909 236.183528
1 2023-01-04 228.982089 225.852101
2 2023-01-05 223.979036 219.158361
3 2023-01-06 219.838579 221.741218
4 2023-01-09 223.239669 223.900171

Let’s do a warm-up round to calculate the columns for the daily changes, the lagged closings, and the opening gap.
In [5]:
# calculate daily difference
MSFT_open_close['intraday_diff'] = MSFT_open_close['close'] - MSFT_open_close['open']

# convert date column to datetime
MSFT_open_close['date'] = pd.to_datetime(MSFT_open_close['date'])

# create lagged close column
MSFT_open_close['close_lag_1'] = MSFT_open_close['close'].shift(1)

# calculate opening gap
MSFT_open_close['opening_gap'] = MSFT_open_close['open'] - MSFT_open_close['close_lag_1']

MSFT_open_close.head()
Out[5]:
date open close intraday_diff close_lag_1 opening_gap
0 2023-01-03 239.633909 236.183528 -3.450381 NaN NaN
1 2023-01-04 228.982089 225.852101 -3.129989 236.183528 -7.201439
2 2023-01-05 223.979036 219.158361 -4.820676 225.852101 -1.873064
3 2023-01-06 219.838579 221.741218 1.902639 219.158361 0.680218
4 2023-01-09 223.239669 223.900171 0.660502 221.741218 1.498451

Now, it’s time to add the rest of the calculations step by step.
In [6]:
# perform aggregations
aggregations = pd.DataFrame({
    'beginning_of_period': [MSFT_open_close['date'].min()],
    'end_of_period': [MSFT_open_close['date'].max()],
    'sum_gap_openings': [MSFT_open_close['opening_gap'].sum(skipna=True)],
    'sum_intraday_diff': [MSFT_open_close['intraday_diff'].sum()],
    'opening_period': [MSFT_open_close.loc[MSFT_open_close['date'] == MSFT_open_close['date'].min(), 'open'].values[0]],
    'closing_period': [MSFT_open_close.loc[MSFT_open_close['date'] == MSFT_open_close['date'].max(), 'close'].values[0]]
})

aggregations.head()
Out[6]:
beginning_of_period end_of_period sum_gap_openings sum_intraday_diff opening_period closing_period
0 2023-01-03 2023-12-29 72.650797 61.711294 239.633909 373.996

And the final calculations...
In [7]:
aggregations['pct_change_period'] = (aggregations['closing_period'] / aggregations['opening_period'] - 1).round(2)
aggregations['pct_gap_openings'] = (aggregations['sum_gap_openings'] / (aggregations['closing_period'] - aggregations['opening_period'])).round(2)
aggregations['pct_intraday_changes'] = (aggregations['sum_intraday_diff'] / (aggregations['closing_period'] - aggregations['opening_period'])).round(2)

aggregations[['opening_period', 'closing_period', 'pct_change_period', 'sum_gap_openings', 'sum_intraday_diff', 'pct_gap_openings', 'pct_intraday_changes']]
Out[7]:
opening_period closing_period pct_change_period sum_gap_openings sum_intraday_diff pct_gap_openings pct_intraday_changes
0 239.633909 373.996 0.56 72.650797 61.711294 0.54 0.46

Conclusion

In 2023, MSFT increased by 56%. Within the total increase in 2023, the weight of gap openings was 54%, and the weight of intraday changes was 46%.

As an extreme example, if one buys at the opening price at the beginning of each trading day and sells at the closing price, 54% of the total rise during the year would be missed compared to buying at the beginning of 2023 and selling at the end. In other words, 54% of the annual increase would be missed; this means missing out on a (56% x 54% = ) 30% profit relative to the index value at the beginning of the year. This shows that gap openings can have a significant impact in the long term.