In this post, I examined the impact of gap openings and intraday changes on the overall change in MSFT stock during the 2023 period using SQL
The data is preloaded into a table named MSFT_open_close. The stock data includes dividend adjustments.
The structure of the table MSFT_open_close is as follows:
select * from MSFT_open_close limit 5| date | open | close |
|---|---|---|
| 2023-01-03 | 239.6339092 | 236.18352792 |
| 2023-01-04 | 228.98208927 | 225.85210053 |
| 2023-01-05 | 223.97903641 | 219.15836084 |
| 2023-01-06 | 219.83857887 | 221.74121769 |
| 2023-01-09 | 223.23966899 | 223.90017055 |
Let’s do a warm-up round to calculate the columns for the daily changes, the lagged closings, and the opening gap.
select
noc.*,
(close-open) daily_diff,
lag(close, 1) over(order by date) close_lag_1,
open - lag(close, 1) over(order by date) opening_gap
from MSFT_open_close noc
limit 5| date | open | close | daily_diff | close_lag_1 | opening_gap |
|---|---|---|---|---|---|
| 2023-01-03 | 239.6339092 | 236.18352792 | -3.4503813 | NA | NA |
| 2023-01-04 | 228.98208927 | 225.85210053 | -3.1299887 | 236.18352792 | -7.201439 |
| 2023-01-05 | 223.97903641 | 219.15836084 | -4.8206756 | 225.85210053 | -1.873064 |
| 2023-01-06 | 219.83857887 | 221.74121769 | 1.9026388 | 219.15836084 | 0.680218 |
| 2023-01-09 | 223.23966899 | 223.90017055 | 0.6605016 | 221.74121769 | 1.498451 |
Now, it’s time to add the rest of the calculations step by step. Using CTEs with the with keyword can simplify the query logic. The query contains comments that detail the purpose of each subquery.
with
add_calculated_columns as --calculates the initially required columns before aggregations
(
select
moc.*,
(close-open) daily_diff,
lag(close, 1) over(order by date) close_lag_1,
open - lag(close, 1) over(order by date) opening_gap
from msft_open_close moc
),
summary as --calculates the total values of the calculated columns, as well as the opening and closing values for the entire period
(
select
sum(opening_gap) sum_gap_openings,
sum(daily_diff) sum_intraday_changes,
(select open from add_calculated_columns where date = (select min(date) from msft_open_close)) opening_period,
(select close from add_calculated_columns where date = (select max(date) from msft_open_close)) closing_period
from add_calculated_columns acg
)
select --final statement calculates the desired values, providing a summarized set of information for the conclusion
opening_period,
closing_period,
round(closing_period/opening_period-1, 2) pct_change_period,
sum_gap_openings,
s.sum_intraday_changes,
--(s.closing_period - s.opening_period) diff_period,
round(sum_gap_openings / (s.closing_period - s.opening_period), 2) pct_gap_openings,
round(s.sum_intraday_changes / (s.closing_period - s.opening_period), 2) pct_intraday_changes
from summary s| opening_period | closing_period | pct_change_period | sum_gap_openings | sum_intraday_changes | pct_gap_openings | pct_intraday_changes |
|---|---|---|---|---|---|---|
| 239.6339092 | 373.99599987 | 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.