how to handle type F in a table in Q/KDB

Question

I have started to learn q/KDB since a while, therefore forgive me in advance for trivial question but I am facing the following problem I don't know how to solve.

I have a table named "res" showing, side, summation of orders and average_price of some simbols

sym  side | sum_order avg_price
----------| -------------------
ALPHA B   | 95109     9849.73  
ALPHA S   | 91662     9849.964 
BETA  B   | 47        9851.638 
BETA  S   | 60        9853.383 

with these types

c        | t f a
---------| -----
sym      | s   p
side     | s    
sum_order| f    
avg_price| f    

I would like to calculate close and open positions, average point, made by close position, and average price of the open position.

I have used this query which I believe it is pretty bizarre (I am sure there will be a more professional way to do it) but it works as expected

position_summary:select 
   close_position:?[prev[sum_order]>sum_order;sum_order;prev[sum_order]],
   average_price:avg_price-prev[avg_price],
   open_pos:prev[sum_order]-sum_order,
   open_wavgprice:?[sum_order>next[sum_order];avg_price;next[avg_price]][0]
 by sym from res

giving me the following table

sym       | close_position average_price open_pos open_wavgprice
----------| ----------------------------------------------------
ALPHA     |  91662          0.2342456     3447    9849.73      
BETA      |  47             1.745035      -13     9853.38  

and types are

c             | t f a
--------------| -----
sym           | s   s
close_position| F    
average_price | F    
open_pos      | F    
open_wavgprice| f 

Now my problem starts here, imagine I join position_summary table with another table appending another column "current_price" of type f

What I want to do is to determinate the points of the open positions.

I have tried this way:

select 
    ?[open_pos>0;open_price-open_wavgprice;open_wavgprice-open]
  from position_summary

but I got 'type error,

surely because sum_order is type F and open_wavgprice and current_price are f. I have search on internet by I did not find much about F type.

First: how can I handle this ? I have tried "cast" or use "raze" but no effects and moreover I am not sure if they are right on this particular occasion.

Second: is there a better way to use "if-then" during query tables (for example, in plain English :if this row of this column then take the previous / next of another column or the second or third of previous /next column)

Thank you for you help

Answer 1

Let me rephrase your question using a slightly simpler table:

q)show res:([sym:`A`A`B`B;side:`B`S`B`S]size:95 91 47 60;price:49.7 49.9 51.6 53.3)
sym side| size price
--------| ----------
A   B   | 95   49.7
A   S   | 91   49.9
B   B   | 47   51.6
B   S   | 60   53.3 

You are trying to find the closing position for each symbol using a query like this:

q)show summary:select close:?[prev[size]>size;size;prev[size]] by sym from res
sym| close
---| -----
A  |  91
B  |  47

The result seems to have one number in each row of the "close" column, but in fact it has two. You may notice an extra space before each number in the display above or you can display the first row

q)first 0!summary
sym  | `A
close| 0N 91

and see that the first row in the "close" column is 0N 91. Since the missing values such as 0N are displayed as a space, it was hard to see them in the earlier display.

It is not hard to understand how you've got these two values. Since you select by sym, each column gets grouped by symbol and for the symbol A, you have

q)show size:95 91
95 91

and

q)prev size
0N 95

that leads to

q)?[prev[size]>size;size;prev[size]]
0N 91

(Recall that 0N is smaller than any other integer.)

As a side note, ?[a>b;b;a] is element-wise minimum and can be written as a & b in q, so your conditional expression could be written as

q)size & prev size
0N 91

Now we can see why ? gave you the type error

q)close:exec close from summary
q)close
 91
 47

While the display is deceiving, "close" above is a list of two vectors:

q)first close
0N 91

and

q)last close
0N 47 

The vector conditional does not support that:

q)?[close>0;10;20]
'type
  [0]  ?[close>0;10;20]
       ^

One can probably cure that by using each:

q)?[;10;20]each close>0
20 10
20 10

But I don't think this is what you want. Your problem started when you computed the summary table. I would expect the closing position to be the sum of "B" orders minus the sum of "S" orders that can be computed as

q)select close:sum ?[side=`B;size;neg size] by sym from res
sym| close
---| -----
A  | 4
B  | -13

Now you should be able to fix the rest of the columns in your summary query. Just make sure that you use an aggregation function such as sum in the expression for every column.

Answer 2

Type F means the "cell" in the column contains a vector of floats rather than an atom. So your column is actually a vector of vectors rather than a flat vector.

In your case you have a vector of size 1 in each cell, so in your case you could just do:

select first each close_position, first each average_price.....

which will give you a type f.

I'm not 100% on what you were trying to do in the first query, and I don't have a q terminal to hand to check but you could put this into your query:

select close_position:?[prev[sum_order]>sum_order;last sum_order; last prev[sum_order].....

i.e. get the last sum_order in the list.