Measuring Gap Opening Contributions in Stock Returns Using R

Programming

Programming/R

Published

September 15, 2024

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 R programming language. The stock data includes dividend adjustments.

# Load required packages
library(data.table)
# output line width (chars)
options(width = 200)

# read data file
MSFT <- fread("MSFT, 1D adjusted with dividents.csv", sep = ",")

# select necessary columns and rename column "time" as "date"
MSFT_open_close <- MSFT[, .(date = time, open, close)]

# filter year 2023
MSFT_open_close <- MSFT_open_close[date >= "2023-01-01" & date < "2024-01-01"]

The structure of the table MSFT_open_close is as follows:

head(MSFT_open_close)

         date     open    close
       <IDat>    <num>    <num>
1: 2023-01-03 239.6339 236.1835
2: 2023-01-04 228.9821 225.8521
3: 2023-01-05 223.9790 219.1584
4: 2023-01-06 219.8386 221.7412
5: 2023-01-09 223.2397 223.9002
6: 2023-01-10 224.5262 225.6056

Let’s do a warm-up round to calculate the columns for the daily changes, the lagged closings, and the opening gap.

MSFT_open_close[, daily_diff := close - open]
MSFT_open_close[, date := as.Date(date)]
MSFT_open_close[, close_lag_1 := shift(close, 1, type = "lag")]
MSFT_open_close[, opening_gap := open - shift(close, 1, type = "lag")]
head(MSFT_open_close)

         date     open    close daily_diff close_lag_1 opening_gap
       <Date>    <num>    <num>      <num>       <num>       <num>
1: 2023-01-03 239.6339 236.1835 -3.4503813          NA          NA
2: 2023-01-04 228.9821 225.8521 -3.1299887    236.1835  -7.2014387
3: 2023-01-05 223.9790 219.1584 -4.8206756    225.8521  -1.8730641
4: 2023-01-06 219.8386 221.7412  1.9026388    219.1584   0.6802180
5: 2023-01-09 223.2397 223.9002  0.6605016    221.7412   1.4984513
6: 2023-01-10 224.5262 225.6056  1.0794764    223.9002   0.6259977

Now, it’s time to add the rest of the calculations one by one:

aggregations <- MSFT_open_close[, .(
  beginning_of_period = min(date),
  end_of_period = max(date),
  sum_gap_openings = sum(opening_gap, na.rm = TRUE),
  sum_intraday_changes = sum(daily_diff),
  opening_period = MSFT_open_close[date == min(date), open],
  closing_period = MSFT_open_close[date == max(date), close]
)]

aggregations

   beginning_of_period end_of_period sum_gap_openings sum_intraday_changes opening_period closing_period
                <Date>        <Date>            <num>                <num>          <num>          <num>
1:          2023-01-03    2023-12-29          72.6508             61.71129       239.6339        373.996

aggregations[, .(
  opening_period,
  closing_period,
  pct_change_period = round(closing_period / opening_period - 1, 2),
  sum_gap_openings,
  sum_intraday_changes,
  pct_gap_openings = round(sum_gap_openings / (closing_period - opening_period), 2),
  pct_intraday_changes = round(sum_intraday_changes / (closing_period - opening_period), 2)
)]

   opening_period closing_period pct_change_period sum_gap_openings sum_intraday_changes pct_gap_openings pct_intraday_changes
            <num>          <num>             <num>            <num>                <num>            <num>                <num>
1:       239.6339        373.996              0.56          72.6508             61.71129             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.