I am extracting 1 day and Hourly data for a ticker (‘AAPL’) from yesterday (3/6/2023). The day data reflects the closing price but the hourly data does not seem to match the closing price. Please see below:
Day data (3/6/2023):
open
high
low
close
volume
trade_count
vwap
timestamp
2023-03-06 05:00:00+00:00
153.785
156.3
153.46
153.83
87558028
691990
154.689477
Hourly data (3/6/2023):
open
high
low
close
volume
trade_count
vwap
timestamp
2023-03-06 09:00:00+00:00
152.050
152.8600
152.050
152.2700
79078
1640
152.422615
2023-03-06 10:00:00+00:00
152.320
152.8700
152.300
152.8100
43201
1022
152.520803
2023-03-06 11:00:00+00:00
152.760
152.8800
152.280
152.7500
71164
1278
152.625241
2023-03-06 12:00:00+00:00
152.680
153.4900
152.500
153.4500
331018
5787
153.014973
2023-03-06 13:00:00+00:00
152.700
154.0100
150.653
153.7200
824683
11030
153.561648
2023-03-06 14:00:00+00:00
153.740
155.0300
153.460
154.4299
15153089
132202
154.234401
2023-03-06 15:00:00+00:00
154.410
155.5800
154.140
155.1467
15570019
123209
154.988438
2023-03-06 16:00:00+00:00
155.150
156.0700
155.150
155.7350
10535718
91855
155.652748
2023-03-06 17:00:00+00:00
155.735
156.3000
154.960
155.0600
9899322
82962
155.686550
2023-03-06 18:00:00+00:00
155.060
155.4450
154.500
154.5500
7941408
67407
154.982455
2023-03-06 19:00:00+00:00
154.550
154.6300
153.960
154.1176
8513935
70462
154.275281
2023-03-06 20:00:00+00:00
154.120
154.2958
153.700
153.8700
10506307
96611
153.958175
2023-03-06 21:00:00+00:00
153.890
153.8900
153.600
153.6200
7812535
1961
153.829197
2023-03-06 22:00:00+00:00
153.620
153.8300
153.560
153.7000
81179
1033
153.715076
2023-03-06 23:00:00+00:00
153.710
153.9000
153.650
153.8900
93909
1077
153.743968
2023-03-07 00:00:00+00:00
153.850
153.9800
153.720
153.7800
69818
1121
153.864596
The hourly closing price at 4pm EST should match in the 1 day closing price however it is not. Am I missing anything here?
@suraj There are two reasons why the daily close price will not generally match bar data (especially hourly bar data).
First, let’s figure out which hourly bar we should be looking at. The markets closed at 16:00 ET on 2023-03-06 which is 21:00 UCT. Bars are labelled with the beginning time so the 2023-03-06 20:00:00+00:00 bar would include all trades between 15:00-16:00 ET. The close of that bar is 153.8700 which indeed does not match the daily close of 153.83.
So, the primary reason the closes won’t match is because the last trade of the day (ie the daily close) for most stocks occurs in the closing auction. The closing auction ‘starts’ at 16:00 and takes a finite amount of time to execute. The last trade of the day actually occurs after 16:00 (typically a few seconds after) and is not included in the 2023-03-06 20:00:00+00:00 bar.
Below are all the trades just before and after 16:00. Notice the blue highlighted trade is the closing trade of the 15:00 hourly bar with a price of 153.87. It was the last trade before 16:00. Also notice the red highlighted trade. It has a price of 153.83 and a trade condition of “6” which specifies a Market Center Closing Trade. It occurred at 16:00:00.294. That is the close price which is reported as the daily close price but won’t get included in the 15:00 hourly bar.
The second confounding reason that bar data won’t typically match the daily bar is after hours trading. The daily bar data only includes trades made during regular trading hours. One could be tempted to include the 16:01 minute bar in an effort to include the closing auction trades. However, the 16:01 minute bar also includes after hours trades. Those have a trade condition of “T”. Notice all the “T” trades after the market close trade in the trades above. Those will get included in the 16:01 bar but, again are not included in the daily bar.
So, for these two reasons there isn’t any way to get daily bars from minute or hourly bars.
