Historical Data

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.

1 Like

Thank you, that answers my question.