cancel
Showing results for 
Search instead for 
Did you mean: 

Count/Sum orders between trades

mwmsigma
New Contributor
I am very new to kdb+/q and I am truly amazed with it. Got so many questions.. But among many reasons I want to use it is speed, and particularly I want to escape for loops.

I understand that in functional language I need to twist my mind, and for some basic examples I do because I use apply family function in R, but there is a routine that I do over and over in many situations and still cannot wrap my head how to do it in R or kdb+/q..

So I've got following simplified feed

~/q? $ q

KDB+ 3.5 2017.11.30 Copyright (C) 1993-2017 Kx Systems

m32/ 4()core 16384MB Dovla dovla.homenet.telecomitalia.it 192.168.1.121  

q)\l ob.q

q)msgs

time| BidAsk Rate Size Type 

----| ----------------------

t1  | 1      10   1    order

t2  | 1      11   2    trade

t3  | 1      12   3    order

t4  | 1      13   4    order

t5  | 1      14   5    trade


And I'd like to get sum of Rate and Size between trades in column Type. Notice that there can be any number of orders between trades.

q)ob

time| Rate Size

----| ---------

t2  | 10   1   

t5  | 25   7   

usually I slice the table between current and last trade in a for loop, like following in R

Rate <- character(); Size <- character() 
index <- msg$Type == 'trade'
indexP <- which(index %in% TRUE)
knt <- sum(index)
beg <- 1
for(i in 1:knt) {
  cnt <- indexP[i] - 1
  print(beg)
  print(cnt)
  row1 <- msg[beg:cnt,]
  Rate[i] <- sum(row1$Rate)
  Size[i] <- sum(row1$Size)
  beg <- beg + 2 + (cnt-beg)
}
ob <- data.frame(Rate,Size)
ob

  Rate Size
1   10    1
2   25    7

---------------------------------

So the whole idea is just to sum number of orders coming from a feed between trades. In real example, I build orderbook after each trade, and do couple of calculations between current and last trade, and store the trade with calculations as a row for later use. I apply additional filters for Ask/Bid and update OrderBook to real one from time to time.. but this is really essential part.

Hope I clearly communicated idea..
2 REPLIES 2

quintanar401
New Contributor
In Q SQL you can use the fact that rows are ordered. Also you should always try to use vector operations. For example:

select last time, sum[rate]-last rate, sum[size]-last size by sums prev Type=`trade from msgs

Here the "sums prev Type=`trade" assigns to each "until the next trade" block a unique number. Sums, prev, =, last are all vector ops eliminating the need for explicit cycles.



понедельник, 25 декабря 2017 г., 21:18:27 UTC+3 пользователь Victor Velletti написал:
I am very new to kdb+/q and I am truly amazed with it. Got so many questions.. But among many reasons I want to use it is speed, and particularly I want to escape for loops.

I understand that in functional language I need to twist my mind, and for some basic examples I do because I use apply family function in R, but there is a routine that I do over and over in many situations and still cannot wrap my head how to do it in R or kdb+/q..

So I've got following simplified feed

~/q? $ q

KDB+ 3.5 2017.11.30 Copyright (C) 1993-2017 Kx Systems

m32/ 4()core 16384MB Dovla dovla.homenet.telecomitalia.it 192.168.1.121  

q)\l ob.q

q)msgs

time| BidAsk Rate Size Type 

----| ----------------------

t1  | 1      10   1    order

t2  | 1      11   2    trade

t3  | 1      12   3    order

t4  | 1      13   4    order

t5  | 1      14   5    trade


And I'd like to get sum of Rate and Size between trades in column Type. Notice that there can be any number of orders between trades.

q)ob

time| Rate Size

----| ---------

t2  | 10   1   

t5  | 25   7   

usually I slice the table between current and last trade in a for loop, like following in R

Rate <- character(); Size <- character() 
index <- msg$Type == 'trade'
indexP <- which(index %in% TRUE)
knt <- sum(index)
beg <- 1
for(i in 1:knt) {
  cnt <- indexP[i] - 1
  print(beg)
  print(cnt)
  row1 <- msg[beg:cnt,]
  Rate[i] <- sum(row1$Rate)
  Size[i] <- sum(row1$Size)
  beg <- beg + 2 + (cnt-beg)
}
ob <- data.frame(Rate,Size)
ob

  Rate Size
1   10    1
2   25    7

---------------------------------

So the whole idea is just to sum number of orders coming from a feed between trades. In real example, I build orderbook after each trade, and do couple of calculations between current and last trade, and store the trade with calculations as a row for later use. I apply additional filters for Ask/Bid and update OrderBook to real one from time to time.. but this is really essential part.

Hope I clearly communicated idea..

Spasibo Andrei.

That sounds exactly perfect. Ill try to implement now.

Thank You so much.