-
sym flipping back and forth when creating continues futures contacts based on volume
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-flipped-back-and-forth -
2 Replies
-
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 its
(??x)?x?x, which translates easily into q as(til count x)<>x?x. -
This answer assumes your
rolloverandroll_rankcolumns 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
Well 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. Well also sort
tmpafter 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 Lynchs 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 its
(??x)?x?x, which translates easily into q as(til count x)<>x?x. And we key onsdate.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 0This 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 ..
Log in to reply.