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 data is preloaded into a table named MSFT_open_close. The stock data includes dividend adjustments.

# Load required packages
library(data.table)

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

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)

Now, it’s time to add the rest of the calculations step by step.

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

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

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.