Computing hourly bars and hourly high from tick data

Francisco1 · February 12, 2012, 9:27am

Hello,I’m a newcomer to Q and I need some help with my initial doubts:My tick database have these colums:DateTime BidPrice AskPrice-----------------------------------------2010.05.01T18:17:37.000 1.32875 1.329752010.05.02T05:05:36.000 1.3289 1.32992010.05.02T18:19:49.000 1.32865 1.330152010.05.02T18:19:58.000 1.32895 1.330452010.05.02T18:30:50.000 1.3287 1.33072010.05.02T18:30:59.000 1.3296 1.33162010.05.02T18:32:05.000 1.3299 1.33192010.05.02T18:44:00.000 1.3304 1.33242010.05.02T18:54:05.000 1.3324 1.33442010.05.02T18:54:15.000 1.33125 1.333252010.05.02T18:54:41.000 1.33146 1.333462010.05.02T18:54:41.000 1.33151 1.333512010.05.02T18:54:41.000 1.33177 1.33377Here is where I need helpI want to add three columns: HourlyBar HourlyHighBid andHourlyHighAsk:With these results (computed by hand):DateTime BidPrice AskPrice HourlyBar HourlyHighBidHourlyHighAsk------------------------------------------------------------------------------------------2010.05.01T18:17:37.000 1.32875 1.32975 2010.05.01T18:00:00.0001.32875 1.329752010.05.02T05:05:36.000 1.3289 1.3299 2010.05.02T05:00:00.0001.3289 1.32992010.05.02T18:19:49.000 1.32865 1.33015 2010.05.02T18:00:00.0001.32865 1.330152010.05.02T18:19:58.000 1.32895 1.33045 2010.05.02T18:00:00.0001.32895 1.330452010.05.02T18:30:50.000 1.3287 1.3307 2010.05.02T18:00:00.0001.32895 1.33072010.05.02T18:30:59.000 1.3296 1.3316 2010.05.02T18:00:00.0001.3296 1.33162010.05.02T18:32:05.000 1.3299 1.3319 2010.05.02T18:00:00.0001.3299 1.33192010.05.02T18:44:00.000 1.3304 1.3324 2010.05.02T18:00:00.0001.3304 1.33242010.05.02T18:54:05.000 1.3324 1.3344 2010.05.02T18:00:00.0001.3324 1.33442010.05.02T18:54:15.000 1.33125 1.33325 2010.05.02T18:00:00.0001.3324 1.33442010.05.02T18:54:41.000 1.33146 1.33346 2010.05.02T18:00:00.0001.3324 1.33442010.05.02T18:54:41.000 1.33151 1.33351 2010.05.02T18:00:00.0001.3324 1.33442010.05.02T18:54:41.000 1.33177 1.33377 2010.05.02T18:00:00.0001.3324 1.3344How can I get these result using Q? Shoud I use xbar function? How?Best regards

Attila · February 12, 2012, 10:57am

To: personal-kdbplus@googlegroups.com
X-Mailer: Apple Mail (2.1251.1)

update HourlyHighBid:max BidPrice,HourlyHighAsk:max AskPrice by =
HourlyBar:(01:00%24:00)xbar DateTime from table

As DateTime is floating point days, so 1%24 is one hour.

Note that I would suggest using timestamp instead of datetime, in that =
case it would be
(`long$0D1)xbar timestamp

Cheers,
Attila
On 12 Feb 2012, at 09:27, Francisco wrote:

> Hello,
>
> I’m a newcomer to Q and I need some help with my initial doubts:
>
> My tick database have these colums:
>
>
> DateTime BidPrice AskPrice
> -----------------------------------------
> 2010.05.01T18:17:37.000 1.32875 1.32975
> 2010.05.02T05:05:36.000 1.3289 1.3299
> 2010.05.02T18:19:49.000 1.32865 1.33015
> 2010.05.02T18:19:58.000 1.32895 1.33045
> 2010.05.02T18:30:50.000 1.3287 1.3307
> 2010.05.02T18:30:59.000 1.3296 1.3316
> 2010.05.02T18:32:05.000 1.3299 1.3319
> 2010.05.02T18:44:00.000 1.3304 1.3324
> 2010.05.02T18:54:05.000 1.3324 1.3344
> 2010.05.02T18:54:15.000 1.33125 1.33325
> 2010.05.02T18:54:41.000 1.33146 1.33346
> 2010.05.02T18:54:41.000 1.33151 1.33351
> 2010.05.02T18:54:41.000 1.33177 1.33377
>
> Here is where I need help
>
> I want to add three columns: HourlyBar HourlyHighBid and
> HourlyHighAsk:
>
> With these results (computed by hand):
>
> DateTime BidPrice AskPrice HourlyBar HourlyHighBid
> HourlyHighAsk
> =
--------------------------------------------------------------------------=

> 2010.05.01T18:17:37.000 1.32875 1.32975 2010.05.01T18:00:00.000
> 1.32875 1.32975
> 2010.05.02T05:05:36.000 1.3289 1.3299 2010.05.02T05:00:00.000
> 1.3289 1.3299
> 2010.05.02T18:19:49.000 1.32865 1.33015 2010.05.02T18:00:00.000
> 1.32865 1.33015
> 2010.05.02T18:19:58.000 1.32895 1.33045 2010.05.02T18:00:00.000
> 1.32895 1.33045
> 2010.05.02T18:30:50.000 1.3287 1.3307 2010.05.02T18:00:00.000
> 1.32895 1.3307
> 2010.05.02T18:30:59.000 1.3296 1.3316 2010.05.02T18:00:00.000
> 1.3296 1.3316
> 2010.05.02T18:32:05.000 1.3299 1.3319 2010.05.02T18:00:00.000
> 1.3299 1.3319
> 2010.05.02T18:44:00.000 1.3304 1.3324 2010.05.02T18:00:00.000
> 1.3304 1.3324
> 2010.05.02T18:54:05.000 1.3324 1.3344 2010.05.02T18:00:00.000
> 1.3324 1.3344
> 2010.05.02T18:54:15.000 1.33125 1.33325 2010.05.02T18:00:00.000
> 1.3324 1.3344
> 2010.05.02T18:54:41.000 1.33146 1.33346 2010.05.02T18:00:00.000
> 1.3324 1.3344
> 2010.05.02T18:54:41.000 1.33151 1.33351 2010.05.02T18:00:00.000
> 1.3324 1.3344
> 2010.05.02T18:54:41.000 1.33177 1.33377 2010.05.02T18:00:00.000
> 1.3324 1.3344
>
> How can I get these result using Q? Shoud I use xbar function? How?
>
> Best regards
>
> –
> You received this message because you are subscribed to the Google =
Groups “Kdb+ Personal Developers” group.
> To post to this group, send email to =
personal-kdbplus@googlegroups.com.
> To unsubscribe from this group, send email to =
personal-kdbplus+unsubscribe@googlegroups.com.
> For more options, visit this group at =
http://groups.google.com/group/personal-kdbplus?hl=en.
>

Francisco1 · February 12, 2012, 6:48pm

Hello,Thanks for your help.The command that you suggest returns the highest value in each hourperiod, but it has unwanted prophetic capabilities as since the firsttick in each hour is returned the highest value even if it appears inthe minute 59.The columns as I need them should contain the highest value as foundin real time using as inputs only the current row and older (formerrows in the table…)Best regardsFranciscoOn Feb 12, 11:57?am, Attila Vrabecz <attila.vrab…> wrote:> update HourlyHighBid:max BidPrice,HourlyHighAsk:max AskPrice by HourlyBar:(01:00%24:00)xbar DateTime from table>> As DateTime is floating point days, so 1%24 is one hour.>> Note that I would suggest using timestamp instead of datetime, in that case it would be> (`long$0D1)xbar timestamp>> Cheers,> ? Attila> On 12 Feb 2012, at 09:27, Francisco wrote:>>>>>>>> > Hello,>> > I’m a newcomer to Q and I need some help with my initial doubts:>> > My ?tick database have these colums:>> > DateTime ? ? ? ? ? ? ? ?BidPrice AskPrice> > -----------------------------------------> > 2010.05.01T18:17:37.000 1.32875 ?1.32975> > 2010.05.02T05:05:36.000 1.3289 ? 1.3299> > 2010.05.02T18:19:49.000 1.32865 ?1.33015> > 2010.05.02T18:19:58.000 1.32895 ?1.33045> > 2010.05.02T18:30:50.000 1.3287 ? 1.3307> > 2010.05.02T18:30:59.000 1.3296 ? 1.3316> > 2010.05.02T18:32:05.000 1.3299 ? 1.3319> > 2010.05.02T18:44:00.000 1.3304 ? 1.3324> > 2010.05.02T18:54:05.000 1.3324 ? 1.3344> > 2010.05.02T18:54:15.000 1.33125 ?1.33325> > 2010.05.02T18:54:41.000 1.33146 ?1.33346> > 2010.05.02T18:54:41.000 1.33151 ?1.33351> > 2010.05.02T18:54:41.000 1.33177 ?1.33377>> > Here is where I need help>> > I want to add three columns: HourlyBar HourlyHighBid and> > HourlyHighAsk:>> > With these results (computed by hand):>> > DateTime ? ? ? ? ? ? ? ?BidPrice AskPrice HourlyBar HourlyHighBid> > HourlyHighAsk> > --------------------------------------------------------------------------- ---------------> > 2010.05.01T18:17:37.000 1.32875 ?1.32975 2010.05.01T18:00:00.000> > 1.32875 ?1.32975> > 2010.05.02T05:05:36.000 1.3289 ? 1.3299 ?2010.05.02T05:00:00.000> > 1.3289 ? 1.3299> > 2010.05.02T18:19:49.000 1.32865 ?1.33015 2010.05.02T18:00:00.000> > 1.32865 ?1.33015> > 2010.05.02T18:19:58.000 1.32895 ?1.33045 2010.05.02T18:00:00.000> > 1.32895 ?1.33045> > 2010.05.02T18:30:50.000 1.3287 ? 1.3307 ?2010.05.02T18:00:00.000> > 1.32895 ?1.3307> > 2010.05.02T18:30:59.000 1.3296 ? 1.3316 ?2010.05.02T18:00:00.000> > 1.3296 ? 1.3316> > 2010.05.02T18:32:05.000 1.3299 ? 1.3319 ?2010.05.02T18:00:00.000> > 1.3299 ? 1.3319> > 2010.05.02T18:44:00.000 1.3304 ? 1.3324 ?2010.05.02T18:00:00.000> > 1.3304 ? 1.3324> > 2010.05.02T18:54:05.000 1.3324 ? 1.3344 ?2010.05.02T18:00:00.000> > 1.3324 ? 1.3344> > 2010.05.02T18:54:15.000 1.33125 ?1.33325 2010.05.02T18:00:00.000> > 1.3324 ? 1.3344> > 2010.05.02T18:54:41.000 1.33146 ?1.33346 2010.05.02T18:00:00.000> > 1.3324 ? 1.3344> > 2010.05.02T18:54:41.000 1.33151 ?1.33351 2010.05.02T18:00:00.000> > 1.3324 ? 1.3344> > 2010.05.02T18:54:41.000 1.33177 ?1.33377 2010.05.02T18:00:00.000> > 1.3324 ? 1.3344>> > How can I get these result using Q? Shoud I use xbar function? How?>> > Best regards>> > –> >

Submitted via Google Groups</attila.vrab…>

Francisco1 · February 12, 2012, 6:58pm

Hello,

Thanks for your help Attila.

I need that the columns return the highest value in each hour period as found in real time, using as input data current row and older (excluding future data).

Best regards

wp1 · February 12, 2012, 9:48pm

maybe you could just offset, as in

update HourlyHighBid:max BidPrice,HourlyHighAsk:max AskPrice by
HourlyBar:(01:00%24:00)+(01:00%24:00)xbar DateTime from table

Francisco1 · February 13, 2012, 9:52pm

Perhaps I’m totally wrong but I had this idea:

Getting the temporal position (tp) of each row in its hourly bar and using that position to compute mmax of the tp rows should return the instant hourly high.

Does that sound more or less right?

I have no idea about how to implement this idea. Please, can anyone comment on this?

Cheers

Francisco

junan_duan · February 17, 2012, 6:15am

update HourlyHighBid:(maxs;BidPrice) fby HourlyBar,HourlyHighAsk:(maxs;AskPrice) fby HourlyBar from update HourlyBar:(1%24) xbar DateTime from t

Regards,

Junan

Francisco1 · February 17, 2012, 11:56am

Thanks a lot!!FranciscoOn Feb 17, 7:15?am, junan duan <junan.d…> wrote:> update HourlyHighBid:(maxs;BidPrice) fby> HourlyBar,HourlyHighAsk:(maxs;AskPrice) fby HourlyBar from update> HourlyBar:(1%24) xbar DateTime from t>> Regards,>> Junan>>>>>>>> On Mon, Feb 13, 2012 at 3:52 PM, Francisco <frango…> wrote:> > Perhaps I’m totally wrong but I had this idea:>> > Getting the temporal position (tp) of each row in its hourly bar and using> > that position to compute mmax of the tp rows should return the instant> > hourly high.>> > Does that sound more or less right?>> > I have no idea about how to implement this idea. Please, can anyone> > comment on this?>> > Cheers>> > Francisco>> > –> >

Submitted via Google Groups</frango…></junan.d…>

Topic		Views
Calculation of bars according to the time range Community Support kdb-and-q	2	March 25, 2015
how to sync time series trade price tick data in Q/Kdb? Community Support kdb-and-q	1	July 26, 2010
Adding Columns Calculated on Grouping Value Community Support kdb-and-q	2	January 19, 2013
Updating Tickdata Table Community Support kdb-and-q	4	June 10, 2012
KDB Converting Subselect to Q Query Community Support kdb-and-q	9	July 20, 2017

Computing hourly bars and hourly high from tick data

Related topics