Computing hourly bars and hourly high from tick data

[Francisco]

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

[Attila Vrabecz]

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

> --
> 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...@googlegroups.com.
> To unsubscribe from this group, send email to personal-kdbpl...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/personal-kdbplus?hl=en.
>

[Francisco]

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

[Francisco]

Hello,

Thanks for your help.

The command that you suggest returns the highest value in each hour
period, but it has unwanted prophetic capabilities as since the first
tick in each hour is returned the highest value even if it appears in
the minute 59.

The columns as I need them should contain the highest value as found
in real time using as inputs only the current row and older (former
rows in the table...)

Best regards


Francisco

[wp]

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

> --
> You received this message because you are subscribed to the Google Groups
> "Kdb+ Personal Developers" group.

> To view this discussion on the web visit
> https://groups.google.com/d/msg/personal-kdbplus/-/UEVg3Y2P5nYJ.

[Francisco]

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]

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

Regards,

Junan

--

You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.

To view this discussion on the web visit https://groups.google.com/d/msg/personal-kdbplus/-/F6Yg_uchz1AJ.

[Francisco]

Thanks a lot!!

Francisco