Process Tick data to form One minute OHCLV Using SQL

|| श्री ||

Ticks to usable Time Frame

Tick data is raw data that we need to process into the usable time frame. As I am using Zerodha ticker service it gives me data every tick. If we need to find an opportunity for trading, required to process the data to make it usable.

Here I am saving data into mssql data base to store tick data. Below is my table structure. 

      You can use below script to use table. I am storing only current day data for Nifty50 stock and Nifty50Index. In my table End of the day will move this data into my historical table which store 1 min data. and truncate table.

CREATE TABLE [dbo].[TickerData](
[TimeStamp] [datetime] NULL,
[Open] [decimal](9, 2) NULL,
[High] [decimal](9, 2) NULL,
[Low] [decimal](9, 2) NULL,
[Close] [decimal](9, 2) NULL,
[Volume] [bigint] NULL,
[InstrumentID] [bigint] NULL,
[recordid] [bigint] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_TickerData] PRIMARY KEY CLUSTERED
(
[recordid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Tick data received from ticker service is having only Latest Price, TimeStamp, And volume.
I have set my Open, close, high, low to LTP.  Now we need to understand how to get 1 min data usnig raw data.

Open
First Tick for min is Open price .i.e when LTP for any stock for 1st second (9:15:00) of the min is Open price

Close 
Last Tick for min is Close price .i.e when LTP for any stock for Last second of the min is Close price

High
Highest price for current session i.e one min. is High price

Low
Lowest Price for current session i.e one min is Low Price

Volume
Volume is cumulative so we need to subtract First tick Volume from Last tick volume.

Below is SQL script which will process raw data and convert it to 1 min data.

**********************************************************************

Declare @instrumentid BIGINT, 
 @starttime    DATETIME, 
 @endtime      DATETIME 
  BEGIN 
      DECLARE @DateTime DATETIME; 


  SET  @instrumentid=  897537 
  SET @starttime='2020-05-29 09:15:09.000'
  SET @endtime='2020-05-29 14:19:55.000' 


      SET @DateTime=Getdate(); --Dateadd(Day,0, Getdate()) 
      SELECT *, 
             Row_number() 
               OVER( 
                 partition BY s1, m1, h1 
                 ORDER BY [timestamp])      rowNum, 
             Row_number() 
               OVER( 
                 partition BY s1, m1, h1 
                 ORDER BY [timestamp] DESC) rowNum2 
      INTO   #temp2 
      FROM   (SELECT *, 
                     Datepart(hour, [timestamp])  H1, 
                     Datepart(minute, [timestamp])M1, 
                     Datepart(minute, [timestamp])S1 
              FROM   [RENDB].[dbo].[tickerdata] 
              WHERE  instrumentid = @instrumentid 
                     AND ( [timestamp] >= @starttime 
                           AND [timestamp] <= @endtime )) res 
    

      SELECT [open], 
             [close], 
             [high], 
             [low], 
             [timestamp] 'datetime', 
             [volume] 
      FROM   (SELECT Sum([open])      [Open], 
                     Sum([close])     [Close], 
                     Max([high])      [High], 
                     Max([low])       [Low], 
                     Max([timestamp]) [TimeStamp], 
                     Max([volume])    [Volume]             
              FROM   (SELECT [open], 
                             00.00  [close], 
                             0.0    [high], 
                             [open] [low], 
                             [timestamp], 
                             0      [volume] 
                      FROM   #temp2 
                      WHERE  rownum = 1 
                      UNION 
                      SELECT 00.00  [Open], 
                             [open] [close], 
                             0.0    [high], 
                             [open] [low], 
                             [timestamp], 
                             0      [volume] 
                      FROM   #temp2 
                      WHERE  rownum2 = 1 
                      /****/ 
                      UNION 
                      SELECT 00.00                         [Open], 
                             00.00                         [close], 
                             0.0                           [high], 
                             v1.[Open]                     [low], 
                             v1. [timestamp], 
                             ( v2.[volume] - v1.[volume] ) [Volume] 
                      FROM   #temp2 v1 
                             JOIN #temp2 v2 
                               ON v1.rownum = v2.rownum2 
                                  AND v1.h1 = v2.h1 
                                  AND v1.m1 = v2.m1 
                      WHERE  v1.rownum = 1 
                      /****/ 
                      UNION 
                      SELECT 00.00            [Open], 
                             00.00            [close], 
                             Max([open])      [High], 
                             Min([open])      [low], 
                             Max([timestamp]) [TimeStamp], 
                             0                [volume] 
                      FROM   [RENDB].[dbo].[tickerdata] 
                      WHERE  instrumentid = @instrumentid 
                             AND ( [timestamp] >= @starttime 
                                   AND [timestamp] <= @endtime ) 
                      GROUP  BY Datepart(year, [timestamp]), 
                                Datepart(month, [timestamp]), 
                                Datepart(day, [timestamp]), 
                                Datepart(hour, [timestamp]), 
                                Datepart(minute, [timestamp]), 
                                ( Datepart(second, [timestamp]) / 1 ) 
                      UNION 
                      SELECT 00.00            [Open], 
                             00.00            [close], 
                             0.0              [High], 
                             Min([open])      [low], 
                             Max([timestamp]) [TimeStamp], 
                             0                [volume] 
                      FROM   [RENDB].[dbo].[tickerdata] 
                      WHERE  instrumentid = @instrumentid 
                             AND ( [timestamp] >= @starttime 
                                   AND [timestamp] <= @endtime ) 
                      GROUP  BY Datepart(year, [timestamp]), 
                                Datepart(month, [timestamp]), 
                                Datepart(day, [timestamp]), 
                                Datepart(hour, [timestamp]), 
                                Datepart(minute, [timestamp]), 
                                ( Datepart(second, [timestamp]) / 1 )) res4 
              GROUP  BY Datepart(year, [timestamp]), 
                        Datepart(month, [timestamp]), 
                        Datepart(day, [timestamp]), 
                        Datepart(hour, [timestamp]), 
                        ( Datepart(minute, [timestamp]) / 1 ))res5 
      ORDER  BY [datetime] 

      DROP TABLE #temp2 
  END 


**********************************************************************


Using about script you Generate the 1 min candle(OHLC(V)) data.  As I said earlier We will keep only Data for the same day. as it will have only limited record so above scrip will not take more than 1 sec to execute.

I have observed that some time OHLCV is not exect matching to the graph. this may be because of capturing tick data i have loosed some data and hence it show some different, but we can use this data to provide it program which will scan stock trading opportunity. I have highlighted Data on TradingView and My Table in mssql. 

Thanks for visiting my blog, Please suggest your thought on above approach. if you have any doubts  please post in comment.