Retrieve Stock Data High, Low, Date, and Times of High and Low

[EricRex]

Hey there,

From my historical, intraday, DB table, I'm trying to generate some high/low time analysis. To that end, I need to get the high, low, date, and time of the high and low of each day. I've started with this:



SELECT MAX(high), MIN(low), DATEVALUE(BarDate)
FROM DIA_3min_Hist
GROUP BY DATEVALUE(BarDate)

It returns a list of daily highs and lows and the associated date. When I add TIMEVALUE(BarDate), I start having problems because almost always, the time of the MAX(high) and MIN(low) are different so I get a complete list of all of the data which isn't helpful, or I have syntax problems or aggregate function errors.

Ultimately, I want to get stats about the percentage of time the high or the low, or both the high and the low of the day are established in the first 60 or 90 minutes of the trading day.

Thanks,
Eric

[CJ_London]

So you only have one stock according to your sql?

Create another query linking this one to your table on date and the max date and get the max times from that. Add in your source table a second time and link on date and min time and get your min time from that

[EricRex]

So you only have one stock according to your sql?

Create another query linking this one to your table on date and the max date and get the max times from that. Add in your source table a second time and link on date and min time and get your min time from that

Hey CJ,

For this exercise, I have one table with historical stock data.

Unfortunately, I haven't been able to make any progress on implementing your suggestion. Any chance you could give me a specific example of what you're suggesting?

[EricRex]

To answer my own question:

Query1:

SELECT DATEVALUE(BarDate) AS IntraDayDate, MAX(high) AS IntraDayHigh, MIN(low) AS IntraDayLow
FROM DIA_3min_Hist
WHERE TIMEVALUE(BarDate) BETWEEN #7:30:00 AM# AND #2:30:00 PM#
GROUP BY DATEVALUE(BarDate)
ORDER BY DATEVALUE(BarDate);

Query2:

SELECT A.IntraDayDate, A.IntraDayHigh, TIMEVALUE(B.BarDate) as IntraDayHighTime, A.IntraDayLow, TIMEVALUE(C.BarDate) as IntraDayLowTime FROM 
Query1 AS A, DIA_3min_Hist AS B, DIA_3min_Hist AS C
WHERE (DATEVALUE(B.BarDate)=A.IntraDayDate AND A.IntraDayHigh = B.high) AND 
(DATEVALUE(C.BarDate)=A.IntraDayDate AND A.IntraDayLow = C.low);

Outputs this:

IntraDayDate	IntraDayHigh	IntraDayHighTime	IntraDayLow	IntraDayLowTime
7/25/2022	$320.39	8:09:00 AM	$318.25	1:06:00 PM
7/26/2022	$319.48	9:00:00 AM	$316.99	2:03:00 PM
7/27/2022	$323.44	1:30:00 PM	$318.04	11:24:00 AM
7/28/2022	$326.12	1:06:00 PM	$319.81	8:27:00 AM
7/29/2022	$329.27	1:48:00 PM	$324.92	7:36:00 AM