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
CLM14CLK14
CLJ14CLH14
CLG14
Thanks,
Matt.