sym flipping back and forth when creating continues futures contacts based on volume

[haideralikazi8]

I am trying to create a continues futures contacts based on volume. But the problem I am facing is the symbols are flipping back and forth. How did you fixed the sym being flipping back and forth?

posted my query on stackoverflow, but no luck with solve.
https://stackoverflow.com/questions/72145825/kdb-once-the-symbol-flipped-over-then-it-should-not-fli...

[SJT]

Full answer on SO.

TL;DR: – find cumulative maxima; eliminate recurrences; upsert into an empty table; fill nulls

Eliminating recurrences uses a vector idiom inherited from APL that flags duplicate items in a list. In APL it’s (⍳⍴x)≠x⍳x, which translates easily into q as (til count x)<>x?x. 

[SJT · Accepted solution]

This answer assumes your rollover and roll_rank columns are instrumental to the Expected Output and not required in it.

---

TL;DR – find cumulative maxima; eliminate recurrences; upsert into an empty table; fill nulls

---

We’ll add two extra lines to your script: one to test the constraint described in your last comment; the other to check we find the current and not just the first maximum for the leading symbol. We’ll also sort tmp after the updates to ensure it reflects them. So the script ends:

//below steps are to generate/update sample data for testing 
// to match desired dataset which we want to create a new logic
tmp:update volume:500.4 from tmp where sdate=2010.01.04, sym=`VXG8;
// find current not first maximum
tmp:update volume:600.6 from tmp where sdate=2010.01.05, sym=`VXG8; 
// confirm VXZ4 cannot recur
tmp:update volume:700.7 from tmp where sdate=2010.01.06, sym=`VXZ4; 
tmp:`sdate xasc `volume xdesc tmp; 
/ tmp:update sums roll_rank by sdate from tmp; 

The first step enlarges Terry Lynch’s answer on StackOverflow. We select the rows where the maximum changes and also mark where the symbol changes.

q)show q:update rollover:differ sym from select sdate,sym,name,volume from tmp where differ maxs volume
sdate sym name volume rollover
-----------------------------------------
2010.01.01 VXZ4 someName4 400.4 1
2010.01.04 VXG8 someName3 500.4 1
2010.01.05 VXG8 someName3 600.6 0
2010.01.06 VXZ4 someName4 700.7 1

The second step eliminates the last row of q, because VXZ4 may not recur.

For this we use an ancient APL idiom for finding duplicates in a vector. In APL it’s (⍳⍴x)≠x⍳x, which translates easily into q as (til count x)<>x?x. And we key on sdate.

q)show r:1!delete from q where rollover and {(til count x)<>x?x}sym
sdate | sym name volume rollover
----------| ------------------------------
2010.01.01| VXZ4 someName4 400.4 1
2010.01.04| VXG8 someName3 500.4 1
2010.01.05| VXG8 someName3 600.6 0

This is all we need in the result. The rest is just filler.

q)/ make a template table
q)s:1!flip`sdate`sym`name`volume!flip tdate,\:(`;`;0n)
q)/ and fill in the gaps
q)fills s upsert delete rollover from r
sdate | sym name volume
----------| ---------------------
2010.01.01| VXZ4 someName4 400.4
2010.01.02| VXZ4 someName4 400.4
2010.01.03| VXZ4 someName4 400.4
2010.01.04| VXG8 someName3 500.4
2010.01.05| VXG8 someName3 600.6
2010.01.06| VXG8 someName3 600.6
2010.01.07| VXG8 someName3 600.6
2010.01.08| VXG8 someName3 600.6
2010.01.09| VXG8 someName3 600.6
2010.01.10| VXG8 someName3 600.6
2010.01.11| VXG8 someName3 600.6
2010.01.12| VXG8 someName3 600.6
..