3

I'm trying to retrieve data for a Open-high-low-close (OHLC) chart directly from the database, it's the kind of chart you see of 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 per month / year, not per day as used for stocks.

I would like to query something like this:

SELECT PriceType, MAX(Price) as High, MIN(Price) as Low, [Price of first item of month] as Open, [Price of last item of month] as Close GROUP BY PriceType, Year(Date), Month(Date)

To access the SQL Server I use LLBLGen, so an anwser based on that technology would be great, a generic SQL server will do too!

It's SQL 2005, but 2008 is also an option.

Thanks.

monksy
  • 14,156
  • 17
  • 75
  • 124
Gabriël
  • 1,323
  • 2
  • 22
  • 34

2 Answers2

2

This appears to work. There may well be a less verbose way to do it.

--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
Ed Harper
  • 21,127
  • 4
  • 54
  • 80
  • Sorry, I don't use SQL a lot (my DAL takes care of that) would this also work if I have missing first or last days of the month? There's no such thing as FIRST / LAST in T-SQL for SQL server? – Gabriël Sep 29 '09 at 13:22
  • @Gabriël - this code report based on the earliest/latest day for each month regardless of whether it is the first/last, but whether the answers are correct or not will depend on your business rules. If the first day of a month is missing, do you report the opening value as the earliest day in the month, or should the last value from the previous month be carried forward? This answer will meet the first case, but not the second. It would not be impossible to extend the code to cover the second case. – Ed Harper Sep 29 '09 at 14:49
  • @Gabriël - There is no direct equivalent to FIRST/LAST in T-SQL. Your DAL probably returns first/last by executing `SELECT TOP 1 ~columns~ FROM ~table~ WHERE id = ~id~ ORDER BY ~order columns~ [ASC (for first) | DESC (for last)]`. – Ed Harper Sep 29 '09 at 14:55
1

This is a little query I wrote that seems to work nicely for one time span at a time. All you need to do is comment the select DATEPARTS in order to get to the timespan you are looking for. Or you could just make multiple views for different timespans. Also the underlying data table uses Bid Ask tick style data. If you are using mids or last prices you could eliminate the case statements from the selects.

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) )
MarcZ2121
  • 31
  • 4