looping in Q KDB

Community Support
1

Hi People- I am new to the forum and to KDB and Q, and need your help..
I am trying to solve this question:

Randomly generate a table of 300 trades with price (two decimals) and size (integer). Compare each trade price (Pn) against its previous one (Pn-1), if Pn-1 > Pn, it is a buy initiated trade, if Pn-1 < Pn it is a sell initiated trade, if Pn-1 = Pn, compare against Pn-2, or Pn-3 until you find the last price movement. Then calculate the size weighted average price of all trades (VWAP), all the buy initiated trades (VWAPB) and all the sell initiated trades (VWAPS),

I could compare each price to its previous, but dont know the code to compare the ones prior to it based on a condition.

The following is my solution for VWAPB: ( but only for previous price)

VWAPB
q)select TradeCount:count i,VWAPB:(sum(size*price)%sum(size)), BuyVol:sum(size), slippage:10000*(first price - (sum(size*price)%sum(size)) % first price) from trades where trades[i; price] \< trades[-1+i; price]

Can you please help me solve this..

2

try using prev with ? … simple below but you’ll need to handle the == condition too (whatever that should be)

update flag:?[price<prev price;buy;sell] from trade

3

Manish- thanks for your help..

tried just now, but struggling with syntax… this is my table..
See attached picture to see..

I am not able to put your code into my query..
do you mind helping please? :)

q)trades:( size:100 150; price:5.1 5.2)
q)do[300; `trades insert (floor 1?1000;  0.01*floor 100*1?72.277)]

q)select TradeCount:count i,VWAPB:(sum(size*price)%sum(size)), BuyVol:sum(size), slippage:10000*(first price - (sum(size*price)%sum(size)) % first price) from trades where trades[i; price] \< trades[-1+i; price]

4

N:300<o:p></o:p>

trades:(price:100+ 0.01*N?100;size:1+N?1000)<o:p></o:p>

select vwap:size wavg price by bs: (init,1_price{(01b!buy`sell) x > y max where x <> y}'price til@/:til count price) from trades<o:p></o:p>

<o:p> </o:p>

To understand this query I suggest you to look at:<o:p></o:p>

<o:p> </o:p>

update bs: (init,1_price{(01b!buy`sell) x > y max where x <> y}'price til@/:til count price) from trades<o:p></o:p>

<o:p> </o:p>

HTH,<o:p></o:p>

<o:p> </o:p>

Kim<o:p></o:p>

<o:p> </o:p>

Von: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] Im Auftrag von Manish Patel
Gesendet: Donnerstag, 20. Februar 2014 14:07
An: personal-kdbplus@googlegroups.com
Betreff: Re: [personal kdb+] looping in Q KDB<o:p></o:p>

<o:p> </o:p>

try using prev with ? … simple below but you’ll need to handle the == condition too (whatever that should be)<o:p></o:p>

<o:p> </o:p>

update flag:?[price<prev price;buy;sell] from trade<o:p></o:p>

<o:p> </o:p>

On Thu, Feb 20, 2014 at 12:59 PM, Siddhartha Nanda <siddnanda@gmail.com> wrote:<o:p></o:p>

Hi People- I am new to the forum and to KDB and Q, and need your help..
I am trying to solve this question:

Randomly generate a table of 300 trades with price (two decimals) and size (integer). Compare each trade price (Pn) against its previous one (Pn-1), if Pn-1 > Pn, it is a buy initiated trade, if Pn-1 < Pn it is a sell initiated trade, if Pn-1 = Pn, compare against Pn-2, or Pn-3 until you find the last price movement. Then calculate the size weighted average price of all trades (VWAP), all the buy initiated trades (VWAPB) and all the sell initiated trades (VWAPS),

I could compare each price to its previous, but dont know the code to compare the ones prior to it based on a condition.

The following is my solution for VWAPB: ( but only for previous price)

VWAPB
q)select TradeCount:count i,VWAPB:(sum(size*price)%sum(size)), BuyVol:sum(size), slippage:10000*(first price - (sum(size*price)%sum(size)) % first price) from trades where trades[i; price] \< trades[-1+i; price]

Can you please help me solve this..<o:p></o:p>

–
Submitted via Google Groups

5

Thanks..I was trying to understand the syntax of your logic  in this highlighted bit..

select vwap:size wavg price by bs: (init,1_price{(01b!buy`sell) x > y max where x <> y}'price til@/:til count price) from trades

Also I wanted to add slippage to the query which is the basis point difference between the First Price and the VWAP..

I wrote that in the query, but it throws me an error as you can see below.. Do you know why it s doing that..

q)select slippage:(10000*(first Price-vwap)%first Price), tradecount: count Size
, volume: sum(Size), vwap:Size wavg Price by bs: (init,1_Price{(01b!buy`sell)
x > y max where x <> y}'Price til@/:til count Price) from trades

'vwap

6

Hi All

Slight side topic - if you want to generate a test trade and quote database with some reasonably realistic random data you can use the buildhdb.q script detailed here: 

http://code.kx.com/wiki/Startingkdbplus/introduction

Thanks 

Jonny 

7

 Hi SN,
I guess the way you would want to implement the solution given in above posts would be :

update vwap:size wavg price by bs: (init,1_price{(01b!buysell) x \> y max where x \<\> y}'price til@/:til count price) from trades;

Required_output_table: update slippage:(10000*(first price-vwap)%first price), tradecount: count size, volume: sum(size) from trades

show Required_output_table

In case you want to see values of bs in Required_output_table write :

Required_output_table: update slippage:(10000*(first price-vwap)%first price), tradecount: count size, volume: sum(size),bs: (init,1_price{(01b!buy`sell) x > y max where x <> y}'price til@/:til count price) from trades

Hope that gives your required output.

8

Hi Sweta thanks for your reply and solution. I tried implementing it but it doesnt give me the output..
Actually there will just be 2 slippage values and not a whole table.
One would be BuySlippage: 10000*(First Price - VWAPB)/First Price
Other would be SellSlippage: 10000*(First Price - VWAPS)/First Price

I am struggling to implement it along with a table implementation that has been suggested..

can you pls help..

9

In this case you can do:

t:update bs: (init,1\_price{(<wbr>01b!buy`sell) x > y max where x <> y}'price til@/:til count price) from trades;
t:update vwap:size wavg price by bs from t;

t:select slippage:10000*first[price-vwap]%first price,tradecount:count size,volume:sum size,first vwap by bs from t;

In my opinion, cramping all queries in one line can be hard to read, that’s why I split it into several steps…

Another solution to differentiate between buy and sell side in you case:

t:update bs:{init,1_x}{$[any x=y;.z.s[x;?[x=y;prev y;y]];?[x\>y;sell;`buy]]}[price;prev price] from trades;

less memory usage

10

this might be a bit easier to understand

(and works even if the first two prices are the same)

update bs:init^fillsbuy``sell 1+signum deltas[first price;price] from`t

i.e., depending on the sign of price-change pick side and fill down (forcing the first delta to be zero)

i would leave the init^ out as is fine as it is

Cheers,

  Attila

11

Yep this is a good strategy as well..could be faster I guess.

12

Nice solution,<o:p></o:p>

<o:p> </o:p>

Kim<o:p></o:p>

<o:p> </o:p>

Side note: Crazy that deltas can take 2 arguments.<o:p></o:p>

<o:p> </o:p>

Von: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] Im Auftrag von Attila Vrabecz
Gesendet: Dienstag, 25. Februar 2014 02:03
An: [kdb+] [kdb+]
Betreff: Re: [personal kdb+] looping in Q KDB<o:p></o:p>

<o:p> </o:p>

this might be a bit easier to understand<o:p></o:p>

(and works even if the first two prices are the same)<o:p></o:p>

<o:p> </o:p>

update bs:init^fillsbuy``sell 1+signum deltas[first price;price] from`t<o:p></o:p>

<o:p> </o:p>

i.e., depending on the sign of price-change pick side and fill down (forcing the first delta to be zero)<o:p></o:p>

i would leave the init^ out as is fine as it is<o:p></o:p>

<o:p> </o:p>

Cheers,<o:p></o:p>

  Attila<o:p></o:p>

<o:p> </o:p>

On 24 Feb 2014, at 12:46, WooiKent Lee <wkent.lee@gmail.com> wrote:<o:p></o:p>

<o:p></o:p>

In this case you can do:<o:p></o:p>

<o:p> </o:p>

t:update bs: (init,1_price{(01b!buy`sell) x > y max where x <> y}'price til@/:til count price) from trades;
t:update vwap:size wavg price by bs from t;<o:p></o:p>

t:select slippage:10000*first[price-vwap]%first price,tradecount:count size,volume:sum size,first vwap by bs from t;<o:p></o:p>

<o:p> </o:p>

In my opinion, cramping all queries in one line can be hard to read, that’s why I split it into several steps…

Another solution to differentiate between buy and sell side in you case:

t:update bs:{init,1_x}{$[any x=y;.z.s[x;?[x=y;prev y;y]];?[x\>y;sell;`buy]]}[price;prev price] from trades;

less memory usage