Re: Loading data for combinations of instruments and date ranges (rolled series)

rocuinneagain

Member

February 1, 2023 at 12:00 am

If you wanted to touch the files as little as possible you could work out ranges and overlaps

//Create a spec ensuring to include some overlaps and gaps to test code better 
q)spec:flip `inst`startDate`endDate!(`A`B`C;2022.01.01 2022.02.01 2022.06.01;2022.03.31 2022.04.30 2022.07.31) 
q)spec 
inst startDate endDate 
-------------------------- 
A 2022.01.01 2022.03.31 
B 2022.02.01 2022.04.30 
C 2022.06.01 2022.07.31 

//Explode the ranges into their individual dates 
q)ranges:ungroup select inst,date:startDate+til each 1+endDate-startDate from spec 

//Now regroup to gather instruments by date 
q)ranges:0!select inst by date from ranges 

//Find cases where there are gaps or the instruments change 
q)ranges:update dDate:deltas[date],dInst:differ inst from ranges 

//Grab the indexes from your ranges table for the beginning and ending of our needed queries 
q)rInds:{-1_x,'-1+next x}(exec i from ranges where (dDate>1) or dInst),count ranges 
q)rInds 
0 30 31 89 90 119 120 180 

//Pulling out the indexes you can see each pair of rows forms a query 
q)select from ranges where i in raze rInds 
date inst dDate dInst 
--------------------------- 
2022.01.01 ,`A 8036 1 
2022.01.31 ,`A 1 0 
2022.02.01 `A`B 1 1 
2022.03.31 `A`B 1 0 
2022.04.01 ,`B 1 1 
2022.04.30 ,`B 1 0 
2022.06.01 ,`C 32 1 
2022.07.31 ,`C 1 0 

//Sample table of data 
q)trades:([] date:2022.01.01+til 365;sym:365?`A`B`C)

//Build and execute functional selects to query from disk 
q)result:raze {?[`trades;((within;`date;x`date);(in;`sym;enlist x[`inst]0));0b;()]} each ranges each rInds 

//Visually inspect result to see months and instruments pulled 
q)select count i by sym,date.month from result 
sym month | x 
----------| -- 
A 2022.01 | 8 
A 2022.02 | 11 
A 2022.03 | 12 
B 2022.02 | 8 .
B 2022.03 | 9 
B 2022.04 | 10 
C 2022.06 | 15 
C 2022.07 | 11

KX Community

rocuinneagain