Synthesising a continuous futures contract

[Matt Craig]

Hi all,

I'm very green when it comes to KDB+/q, but thanks to reading some helpful posts here and perseverance, now have an hdb running and automatically updating from a broker feed.

Next I'd like to write a q function to build a continuous futures series on the fly. For anyone not familiar, the idea is to splice together data from multiple contracts in a given series to form a single synthetic data series.

There are many ways to choose how & when to switch (rollover) from one contract in the series to another; one common approach is to switch to a newer contract once it trades more volume than the older one, and to then offset all prices on the prior contract by the price difference between the older and newer contracts at the point of switching (akin to back-adjusting historical stock prices prior to a dividend payment).

However I'm stumped on how to implement this in q, and would appreciate any help. So far I've managed a few of the steps:

///// Construct continuous futures for CL (light sweet crude) series from Jan-Mar 2014

/ Get 1-minute OHLCv bars for symbols of interest in t

t:select from tradeBar where date >= 2014.01.01, date <= 2014.03.01, sym like "CL*"

/ Gets the symbol with the greatest volume on each day
frontSymByDate:select sym:first sym where size=max size by date from (select sum size by date, sym from t)

/ Get rollover date for each symbol

rolloverDates:`date xasc select first date by sym from frontSymByDate

q)rolloverDates
sym  | date     
-----| ----------
CLG14| 2014.01.01
CLH14| 2014.01.17
CLJ14| 2014.02.19

So based on the above, the combined series should use CLG14 prices until Jan 17th (after back-adjusting them for the price difference between CLG14 and CLH14 immediately prior to Jan 17th), then CLH14 until Feb 19th (with a similar back adjustment, also applied to earlier CLG14 data), and CLJ14 prices verbatim for the rest.

I'm lost on how to go about computing the price difference between two contracts at the rollover points above (ideally it would take the last e.g. five 1-minute-bars immediately prior to rollover date *where both contracts traded* and use the average close price difference over those bars).

Also not clear on how to apply the offsets once calculated, however could probably figure that out from the examples for applying corporate actions at:

http://code.kx.com/wiki/Cookbook/CorporateActions

http://www.firstderivatives.com/Products_pdf.asp?downloadflyer=q_for_Gods_April_2014_Temporal_Data

I'd be very grateful for any assistance, and apologies for the clumsy q code. No doubt there's a way to achieve all of this in a couple of lines of q when you know what you're doing!

P.S.: Came up with a query for fetching the symbols in reverse-chronological order from their contract code. Not sure if this is useful for picking consecutive contract pairs to calculate rollovers between:

/ Get symbols in reverse-expiry order

q)(desc {s:string x;l:count s;(s[(til l-3),l-2,1,3];x)} each (select distinct sym from t)[`sym])[;1]
`sym$`CLH15`CLM14`CLK14`CLJ14`CLH14`CLG14

Thanks,

Matt.

[Felix LUNGU]

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbpl...@googlegroups.com.
To post to this group, send email to personal...@googlegroups.com.
Visit this group at http://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.

[Felix LUNGU]

sorry about that. sent from my pocket.

[Matt Craig]

Below is the solution I've come up with. It's no doubt neither efficient nor idiomatic (and the last line doesn't work), so I'd welcome any suggested improvements.

Thanks,

Matt.

/ Returns OHLC bars for front-month future contract beginning with prefix in range (start;end).
/ Data is back-adjusted for contract rolls, which occur at the end of the day before the far-month
/ contract becomes more liquid than the near-month.
/ Roll level is calculated as the median price difference across the last n bars
contFuture:{[prefix;start;end;n;tbl]

    / Returns the median difference in open/close prices for the last n bars before d where both s1 & s2 traded
    medDiff:{[s1;s2;d;n;t]

        / Table of last n bars that match between the two symbols
        lt:(neg n)#ej[`date`time;select date,time,o1:open,c1:close from t where date<d,sym=s1,size>0;select date,time,o2:open,c2:close from t where date<d,sym=s2,size>0];

        :med (lt[`o1]-lt[`o2]),(lt[`c1]-lt[`c2]);
    };

    / Data for candidate symbols in global var (so we can pass `t to medDiff)
    t::select from tbl where date>=start,date<=end,sym like (string[prefix],"*");
   
    / Gets symbol with the greatest volume on each day
    symbolByDate:select sym:first sym where size=max size by date from (select sum size by date, sym from t);

    / Get table of new sym, old sym, and rollover date
    rollTable:select symAfter:sym, symBefore:prev sym, date from (`date xasc select first date by sym from symbolByDate);
   
    / Add columns for medDiff function (yuck!)
    rollTable[`n`t]:(n;`t);
              
    / Calc cumulative diff column; replace null with 0
    rollTable[`cumDiff]:0^next reverse (0+\(reverse 0^medDiff .' flip value flip rollTable));
   
    / Remove unneeded columns and rename symAfter
    rollTable:`sym xcol `symBefore`n`t _ rollTable;
   
    / Add null cumDiff entries on the rollover dates for each symbol we've rolled off
    rollTable,:1_select prev sym, date, cumDiff:0n from rollTable;
    
    / Select bars from each contract based on the rollTable dates; join with cumDiff
    rawBars:select from aj[`sym`date;t;$[not count rollTable;:t;rollTable]] where cumDiff <> 0n;
   
    / Apply offset
    :select date, sym, time, open:open+cumDiff, high:high+cumDiff, low:low+cumDiff, close:close+cumDiff, size from rawBars;

    / Delete global t var. Doesn't work?
    delete t from `..;
}

[Matt Craig]

Just realized the last line didn't work as it was after the return value, and should have been:

    delete t from `.;