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 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
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
–
Submitted via Google Groups
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