cancel
Showing results for 
Search instead for 
Did you mean: 

How to fill the gap of x - minute bars

huangyuanfei14
New Contributor
Hi All,
 
I calculate x-minute or x-second bars found some bars is lack.
 
select last price,sum size by sym,3 xbar time.second from trade where date=last date
 
example:3-second bars
 
sym     time          price       size
--------------------------------------------
AAPL   09:00:03  10.1        100
AAPL   09:00:09  10.0        300
AAPL   09:00:12  10.2        200
 
How to fill "AAPL  09:00:06  10.1   100" as a bar(use previous records to fill),Generate time continuous bars.
 
Thanks,
Sky
9 REPLIES 9

iurgasndiff
New Contributor
you can use lj.

basically create an empty table with all time bars and lj it against the result of your query.

([]sym:1000#`AAPL;time:09:00:00 + 3*til 1000) lj 2!select last price,sum size by sym,3 xbar time.second from trade where date=last date

complete solution with fills.

update fills price from ([]sym:1000#`AAPL;time:09:00:
00 + 3*til 1000) lj 2!select last price,sum size by sym,3 xbar time.second from trade where date=last date


The OP's question looks like the C column in an OHLCV query. In an OHLCV query, I would like to fill the gaps OHL values with the last C value, and then the V values with zero. How can I do that? Thx.

You can do 

update o:c^o, h:c^h, l:c^l, v:0^v from update c:fills c by sym from ohlcv 



thx

a thing to consider is to fill by sym to avoid pathologic cases (which always happen eventually)
not having prices for a sym at all at the beginning period would get filled with values from previous sym

update c^o, c^h, c^l, 0^v by sum from update fills c by sym from ohlcv 

Cheers,
   Attila

ok,
 
Im big enough to admit I am missing something and some of the less experienced people may be scratching their heads..
 (Some may quip that I have been missing something for quite a while!! )
 
However comparing these two queries given by our resident guru's:   
 
a) update c^o, c^h, c^l, 0^v by sum from update fills c by sym from ohlcv 
 
j) update o:c^o, h:c^h, l:c^l, v:0^v from update c:fills c by sym from ohlcv 
 
the only difference is the "by sym" highlighted. (I assume the sum is a typo)
 
 
The ^ works "horizontally" so as far as I can see the by sym is redundant in (a).
Im interested in the edge case that's needed for this as I agree if one exists it will occur sometime and have a nasty knock on effect.
 
To be clear, (a) is at least as correct as (j).
 
Regards,
 
James
 
 
(The short version of this email is what edge case makes the two queries return different results?)
 

From: attila.vrabecz@quantumkdb.com
Subject: Re: [personal kdb+] How to fill the gap of x - minute bars
Date: Fri, 19 Jun 2015 03:11:07 +0100
To: personal-kdbplus@googlegroups.com

a thing to consider is to fill by sym to avoid pathologic cases (which always happen eventually)
not having prices for a sym at all at the beginning period would get filled with values from previous sym

update c^o, c^h, c^l, 0^v by sum from update fills c by sym from ohlcv 

Cheers,
   Attila

yes, sum was a typo

and you are absolutely right, this very particular case is actually rectangular
so strictly speaking there is no need for "by sym"

however i find it is good practice
as one would usually have fills down for o,h,l and v too

q)ohlcv:([]o:8;h:20;l:5 6 7;c:4;v:100000;sym:`a)
q)ohlcv,:([]o:0N;h:4 5 6;l:0N;c:0N;v:30000;sym:`b)
q)ohlcv
o h  l c v      sym
-------------------
8 20 5 4 100000 a
8 20 6 4 100000 a
8 20 7 4 100000 a
  4      30000  b
  5      30000  b
  6      30000  b

in that case leaving out the "by sym" would carry data over syms
a typical query could be
q)update o:c^fills o, h:c^fills h, l:c^fills l, v:0^fills v from update c:fills c by sym from ohlcv
o h  l c v      sym
-------------------
8 20 5 4 100000 a
8 20 6 4 100000 a
8 20 7 4 100000 a
8 4  7   30000  b
8 5  7   30000  b
8 6  7   30000  b

or
q)update c^o, c^h, c^l, 0^v from fills update c:fills c by sym from ohlcv
o h  l c v      sym
-------------------
8 20 5 4 100000 a
8 20 6 4 100000 a
8 20 7 4 100000 a
8 4  7 4 30000  b
8 5  7 4 30000  b
8 6  7 4 30000  b

i've seen and probably committed these mistakes myself before, hence the warning

Cheers,
  Attila

 
Hi Akash,
 
Thank you very much.
 
Sky
在 2015年3月18日星期三 UTC+8下午3:58:03,Akash Gupta写道:
you can use lj.

basically create an empty table with all time bars and lj it against the result of your query.

([]sym:1000#`AAPL;time:09:00:00 + 3*til 1000) lj 2!select last price,sum size by sym,3 xbar time.second from trade where date=last date

On Wed, Mar 18, 2015 at 11:32 AM, <huangyu...@gmail.com> wrote:
Hi All,
 
I calculate x-minute or x-second bars found some bars is lack.
 
select last price,sum size by sym,3 xbar time.second from trade where date=last date
 
example:3-second bars
 
sym     time          price       size
--------------------------------------------
AAPL   09:00:03  10.1        100
AAPL   09:00:09  10.0        300
AAPL   09:00:12  10.2        200
 
How to fill "AAPL  09:00:06  10.1   100" as a bar(use previous records to fill),Generate time continuous bars.
 
Thanks,
Sky

--
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.