Measuring Gap Opening Contributions in Stock Returns Using Python

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.

import pandas as pd
MSFT = pd.read_csv("MSFT, 1D adjusted with dividents.csv")
# 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:

MSFT_open_close.head()

	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.

# 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()

	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.

# 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()

	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…

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']]

	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.