How to query Open-high-low-close (OHLC) data from SQL Server
I am trying to get data for an Open-high-low-close (OHLC) chart directly from the database, this is the kind of chart you see in stocks. Is this possible, and if, how?
I have a table like this (simplified):
Date | Price | PriceType
A record is created for each day, I will report on a month / year, not a day, as used for stocks.
I would like to request something like this:
SELECT PriceType, MAX (Price) as high, MIN (Price) as low, [Price of the first item of the month] as open, [Price of the last item of the month] as Close GROUP BY PriceType, Year (Date), month (date)
To access SQL Server I am using LLBLGen, so anwser based on this technology will be great, a generic SQL server will be created as well!
This is SQL 2005, but also a 2008 variant.
Thank.
source to share
It works. There may be a less surefire way to do this.
--create test data
CREATE TABLE #t
(priceDate DATETIME
,price MONEY
,priceType CHAR(1)
)
INSERT #t
SELECT '20090101',100,'A'
UNION SELECT '20090102',500,'A'
UNION SELECT '20090103',20 ,'A'
UNION SELECT '20090104',25 ,'A'
UNION SELECT '20090105',28 ,'A'
UNION SELECT '20090131',150,'A'
UNION SELECT '20090201',501,'A'
UNION SELECT '20090203',21 ,'A'
UNION SELECT '20090204',26 ,'A'
UNION SELECT '20090205',29 ,'A'
UNION SELECT '20090228',151,'A'
UNION SELECT '20090101',100,'B'
UNION SELECT '20090102',500,'B'
UNION SELECT '20090103',20 ,'B'
UNION SELECT '20090104',25 ,'B'
UNION SELECT '20090105',28 ,'B'
UNION SELECT '20090131',150,'B'
UNION SELECT '20090201',501,'B'
UNION SELECT '20090203',21 ,'B'
UNION SELECT '20090204',26 ,'B'
UNION SELECT '20090205',29 ,'B'
UNION SELECT '20090228',151,'B'
--query
;WITH rangeCTE
AS
(
SELECT MIN(priceDate) minDate
,MAX(priceDate) maxDate
FROM #t
)
,datelistCTE
AS
(
SELECT CAST(CONVERT(CHAR(6),minDate,112) + '01' AS DATETIME) AS monthStart
,DATEADD(mm,1,CAST(CONVERT(CHAR(6),minDate,112) + '01' AS DATETIME)) -1 AS monthEnd
,1 AS monthID
FROM rangeCTE
UNION ALL
SELECT DATEADD(mm,1,monthStart)
,DATEADD(mm,2,monthStart) - 1
,monthID + 1
FROM datelistCTE
WHERE monthStart <= (SELECT maxDate FROM rangeCTE)
)
,priceOrderCTE
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY monthID, priceType
ORDER BY priceDate
) AS rn1
,ROW_NUMBER() OVER (PARTITION BY monthID, priceType
ORDER BY priceDate DESC
) AS rn2
,ROW_NUMBER() OVER (PARTITION BY monthID, priceType
ORDER BY price DESC
) AS rn3
,ROW_NUMBER() OVER (PARTITION BY monthID, priceType
ORDER BY price
) AS rn4
FROM datelistCTE AS d
JOIN #t AS t
ON t.priceDate BETWEEN d.monthStart AND d.monthEnd
WHERE monthStart <= (SELECT maxDate FROM rangeCTE)
)
SELECT o.MonthStart
,o.priceType
,o.Price AS opening
,c.price AS closing
,h.price AS high
,l.price AS low
FROM priceOrderCTE AS o
JOIN priceOrderCTE AS c
ON c.priceType = o.PriceType
AND c.monthID = o.MonthID
JOIN priceOrderCTE AS h
ON h.priceType = o.PriceType
AND h.monthID = o.MonthID
JOIN priceOrderCTE AS l
ON l.priceType = o.PriceType
AND l.monthID = o.MonthID
WHERE o.rn1 = 1
AND c.rn2 = 1
AND h.rn3 = 1
AND l.rn4 = 1
source to share
This is a small query I wrote that seems to work nicely for one amount of time at a time. All you have to do is comment on the selected DATEPARTS to get to the time you are looking for. Or you can just do multiple views for different time intervals. The Bid Ask style data is also used in the base data table. If you are using average or latest prices, you can exclude case statements from the selections.
Select
tmp.num,
rf.CurveName,
rf.Period as Period,
CASE WHEN (tmp2.Bid is null or tmp2.Ask is null) then isnull(tmp2.Bid,0)+isnull(tmp2.Ask,0) else (tmp2.Bid+tmp2.Ask)/2 end as [Open],
tmp.Hi,
tmp.Lo,
CASE WHEN (rf.Bid is null or Rf.Ask is null) then isnull(rf.Bid,0)+isnull(rf.Ask,0) else (rf.Bid+rf.Ask)/2 end as [Close],
tmp.OpenDate,
tmp.CloseDate,
tmp.yr,
tmp.mth,
tmp.wk,
tmp.dy,
tmp.hr
from BidAsk rf inner join
(SELECT count(CurveName)as num,CurveName,
Period,
max(CASE WHEN (Bid is null or Ask is null) then isnull(Bid,0)+isnull(Ask,0) else (Bid+Ask)/2 end) as Hi,
min(CASE WHEN (Bid is null or Ask is null) then isnull(Bid,0)+isnull(Ask,0) else (Bid+Ask)/2 end) as Lo,
max(CurveDateTime) as CloseDate, min(CurveDateTime) as OpenDate,
DATEPART(year, CurveDateTime) As yr,
DATEPART(month, CurveDateTime) As mth,
DATEPART(week, CurveDateTime) As wk,
DATEPART(Day, CurveDateTime) as dy,
DATEPART(Hour, CurveDateTime) as hr
--DATEPART(minute, CurveDateTime) as mnt
FROM
BidAsk
GROUP BY
CurveName,Period,
DATEPART(year, CurveDateTime),
DATEPART(month, CurveDateTime),
DATEPART(week, CurveDateTime),
DATEPART(Day, CurveDateTime) ,
DATEPART(Hour, CurveDateTime)
--DATEPART(minute, CurveDateTime)
) tmp on
tmp.CurveName=rf.CurveName and
tmp.CloseDate=rf.CurveDateTime and
tmp.Period=rf.Period
inner join BidAsk tmp2 on
tmp2.CurveName=rf.CurveName and
tmp2.CurveDateTime=tmp.Opendate and
tmp2.Period=rf.Period
ORDER BY
CurveName,Period,tmp.yr,tmp.mth
--DATEPART(year, CurveDateTime),
--DATEPART(month, CurveDateTime)
--DATEPART(day, CurveDateTime),
--DATEPART(Hour, CurveDateTime),
--DATEPART(minute, CurveDateTime) )
source to share