How can I populate missing rows in sql with static data?

Question

I have collected one-minute 'tick' share price data for a number securities over a period of weeks, and stored these in a table called 'intraday'. This table includes one-minute tick data between 08:01:00 and 16:30:00 each trading day.

Some securities do not trade every minute of every day, and when they do not trade, no row is created (missing ticks). I was hoping to insert rows for all missing ticks, which would carry across the share price from the previous share price, with a trade volume of '0' for these ticks.

For example, currently for one security I have the following stored:

ticker     date           tick                     cls        volume

ASC        20151231       1899-12-30 12:30:00      3453       2743
ASC        20151231       1899-12-30 12:29:00      3449       3490
ASC        20151231       1899-12-30 12:28:00      3436       930
ASC        20151231       1899-12-30 12:27:00      3435       255
ASC        20151231       1899-12-30 12:26:00      3434       4
ASC        20151231       1899-12-30 12:23:00      3444.59    54

(apologies for the annoying 1899-12-30 dates for each tick - these make it look a bit messy but do no harm, hence why they remain there currently)

What I would ideally like to be stored, is this:

ticker     date           tick                     cls        volume

ASC        20151231       1899-12-30 12:30:00      3453       2743
ASC        20151231       1899-12-30 12:29:00      3449       3490
ASC        20151231       1899-12-30 12:28:00      3436       930
ASC        20151231       1899-12-30 12:27:00      3435       255
ASC        20151231       1899-12-30 12:26:00      3434       4
ASC        20151231       1899-12-30 12:25:00      3444.59    0          < new row
ASC        20151231       1899-12-30 12:24:00      3444.59    0          < new row
ASC        20151231       1899-12-30 12:23:00      3444.59    54

So, for each distinct ticker and date value, there would be a range of values for each minute between 08:01:00 and 16:30:00. Some would be as they are currently stored, and the others would have a volume figure of 0, and a close value that copies the previous close value.

I'm absolutely stumped, and would appreciate any help you could potentially offer on this!

Kind regards.

Frustratingly, I forgot to mention that in the first table, the there was no trade for ticker 'ASC' at 12:25:00 and 12:24:00 and therefore there are no records for these values...it is these values that I would like to input. Hopefully this is apparent in my second table, but just for further clarification. Many thanks. — Jambo
– Jambo, Commented May 24, 2016 at 16:14
Do you need to do this at once (fill the db gaps now) or it has to work in the future too? — Dexion
– Dexion, Commented May 24, 2016 at 16:29
Hi Dexion - All at once. I've finished collecting the data, and now I need to go through it all historically and add the values. — Jambo
– Jambo, Commented May 24, 2016 at 16:35
For one shot, you can create simply a cursor to check the data and insert when missing. Or create a dummy table filled with 0 values for every minute and insert the missing data from the dummy table into the real table. Or union/join the dummy and the real tables. — Dexion
– Dexion, Commented May 24, 2016 at 16:37
Thanks for taking the time to respond to this, Dexion. If I create a dummy table, would that allow me to insert data into every missing row, for every security (I have a few hundred 'ticker' codes that I would need to do this for)? Also, would that allow me to copy over the previous 'cls' price if there was not one. I can see the benefit of doing this if inserting 0 values into all fields, but the 'cls' price will depend on the previous 'cls' in this case. In which case, is this method valid? — Jambo
– Jambo, Commented May 24, 2016 at 17:41

msheikh25 · Accepted Answer · 2016-05-25 03:17:40Z

2

Use a recursive CTE to create a time table for the range you need. I just used 12 minutes using the data from your sample but you can extend it out. Getting the last non-null value is a little tricky without using a cursor but it is possible. Here is a more thorough explanation on that if you are interested.

Demo of the code below: http://rextester.com/QDQR73738

setup:

create table test_data(ticker varchar(5), date integer, tick datetime, cls decimal(10,2), volume integer);
create table test_data2(ticker varchar(5), date integer, tick datetime, cls decimal(10,2), volume integer);

insert into test_data
    select 'ASC', 20151231, '1899-12-30 12:30:00', 3453,    2743 union all
    select 'ASC', 20151231, '1899-12-30 12:29:00', 3449,    3490 union all
    select 'ASC', 20151231, '1899-12-30 12:28:00', 3436,    930  union all
    select 'ASC', 20151231, '1899-12-30 12:27:00', 3435,    255  union all
    select 'ASC', 20151231, '1899-12-30 12:26:00', 3434,    4    union all
    select 'ASC', 20151231, '1899-12-30 12:23:00', 3444.59, 54   union all

    select 'BSC', 20151231, '1899-12-30 12:23:00', 3444.59, 54   union all
    select 'BSC', 20151231, '1899-12-30 12:28:00', 3436,    930 
;

query:

Declare @tickers Table (ticker varchar(5));
Insert into @tickers select distinct ticker from test_data;

Declare @ticker varchar(5);
While exists (Select * From @tickers)
  BEGIN
    select @ticker = min(ticker) from @tickers;

    with cte(tm) 
    as( Select cast('1899-12-30 12:23:00' as datetime) as tm
        union all
    Select dateadd(minute, 1, tm)
        from cte
        where tm < cast('1899-12-30 12:31:00' as datetime)
    )    

    insert into test_data2 
    select 
      max(ticker) over (partition by grp order by tick rows unbounded preceding) ticker,
      max(date) over (partition by grp order by tick rows unbounded preceding) date,
      tick,
      max(cls) over (partition by grp order by tick rows unbounded preceding) cls,
      volume
    from (
          select
              ticker,
              date,
              tick,
              cls,
              volume,
              id,
              max(id1) OVER(ORDER BY tick ROWS UNBOUNDED PRECEDING) AS grp
          from (
                select 
                  td.ticker,
                  td.date,
                  coalesce(td.tick, cte.tm) tick,
                  td.cls,
                  coalesce(td.volume, 0) volume,
                  row_number() over (order by tick) id
                from test_data td
                right outer join cte
                on td.tick = cte.tm
                and td.ticker = @ticker
         ) cte2
         CROSS APPLY ( VALUES( CASE WHEN ticker IS NOT NULL THEN id END) )
            AS A(id1)
     ) cte3;

      Delete from @tickers where ticker = @ticker;
    End

select * from test_data2
order by ticker, tick;

edited May 25, 2016 at 3:17

answered May 24, 2016 at 19:47

msheikh25

5783 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jambo Over a year ago

Thanks for this. Your demo works brilliantly. When I attempt to run this with my full dataset in SQL Server I receive the following message: 'Msg 402, Level 16, State 1, Line 21 The data types datetime and time are incompatible in the equal to operator'. Changing 'time' to 'datetime' causes this error message to disappear, but the results are odd (510 rows of data which are blank, other than the minute ticks from 08:01:00 to 16:30:00). It seems that this could be an error with SQL Server 2008/2012 conversion, so I'll look into it,but it you are aware of any quick fix that would be appreciated

msheikh25 Over a year ago

Blank rows maybe because the TICK values from your table are not matching the TM value from cte. Since you updated it to a datetime, verify that the date portion in each is matching correctly. You can also just cast(TICK as time) since date doesn't seem to be needed.

msheikh25 Over a year ago

Also think this will only work for one TICKER value at a time. Not sure if you are trying with more than one?

msheikh25 Over a year ago

Updated the answer above. Have to loop through the data if you want to be able to handle all the tickers in one shot and insert all data, including the missing ticks, into a new table (test_data2). Also updated the format for TM to datetime to be consistent with your table. Using 1899-12-30 for the date part as in your sample data.

BJones · Accepted Answer · 2016-05-24 21:09:12Z

1

Tell me if this works. I used the sample data you provided and it worked with the rows that are missing. It may take a little bit of time depending on how many records you have. I created the temp table to test with. You may need to do this once for each ticker symbol.

CREATE TABLE #Stocks (  ticker VARCHAR(5),
                    date DATE,
                    tick datetime,
                    cls money,
                    volume money)

INSERT INTO #Stocks (ticker, date, tick, cls, volume)
VALUES
('ASC','2015-12-31','1899-12-30 12:30:00',3453,2743),
('ASC','2015-12-31','1899-12-30 12:29:00',3449,3490),
('ASC','2015-12-31','1899-12-30 12:28:00',3436,930),
('ASC','2015-12-31','1899-12-30 12:27:00',3435,255),
('ASC','2015-12-31','1899-12-30 12:26:00',3434,4),
('ASC','2015-12-31','1899-12-30 12:23:00',3444.59,54)



DECLARE @TotalRows1 INT
SELECT @TotalRows1 = COUNT(*) FROM #Stocks
DECLARE @Row INT = 1
WHILE @Row < @TotalRows1
BEGIN
DECLARE @TotalRows INT
SELECT @TotalRows = COUNT(*) FROM #Stocks
 IF (SELECT DATEADD(MI,1,tick) FROM (SELECT ROW_NUMBER() OVER (ORDER BY tick) RowNumber, * FROM #Stocks) AS T1 WHERE T1.RowNumber = @Row)
<>
(SELECT T2.tick FROM (SELECT ROW_NUMBER() OVER (ORDER BY tick) RowNumber, * FROM #Stocks) AS T2 WHERE T2.RowNumber = @Row + 1)
BEGIN
INSERT INTO #Stocks
SELECT T.ticker, T.Date, DATEADD(MI,1,T.tick) tick, T.cls, 0.00
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY tick) RowNumber, * FROM #Stocks) AS T
    WHERE T.RowNumber = @Row
SET @Row = @Row +1
END
ELSE
SET @Row = @Row +1
END

SELECT *
FROM #Stocks
ORDER BY tick DESC

answered May 24, 2016 at 21:09

BJones

2,4702 gold badges20 silver badges28 bronze badges

1 Comment

Jambo Over a year ago

I appreciate your help with this. So in this case, I could bring in data for each ticker into the temp table one-by-one (select ticker, date, tick, cls, volume into test.dbo.test_stocks from advfn.dbo.intraday where ticker = 'ASC'), run your query above and insert the rows into a combined database, delete the temp table and follow the same process with the next ticker code? This would suit...although there are approx 1000 ticker codes, so it take a few hours - but much, much quicker than doing it manually! Thanks for your help with this.

Collectives™ on Stack Overflow

How can I populate missing rows in sql with static data?

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related