95% Confidence Interval for a weighted column in KDB+/Q

Question

I have a table like the following where each row corresponds to an execution:

table:([]name:`account1`account1`account1`account2`account2`account1`account1`account1`account1`account2;
Pnl:13.7,13.2,74.1,57.8,29.9,15.9,7.8,-50.4,2.3,-16.2;
markouts:.01,.002,-.003,-.02,.004,.001,-.008,.04,.011,.09;
notional:1370,6600,-24700,-2890,7475,15900,-975,-1260,210,-180)

I'd like to create a 95% confidence interval of Pnl for `account1. The problem is, Pnl is the product of markouts and notional values, so it's weighted and the mean wouldn't be a simple mean. I'm pretty sure the standard deviation calculation would also be a bit different than normal.

Is there a way to still do this in KDB? I'm not really sure how to go about this. Any advice is greatly appreciated!

Answer 1

statistics isn't my strong point but most of this can be done with some keywords for the standard calculation:

q)select { avg[x] + -1 1*  1.960*sdev[x]%sqrt count x } Pnl by name from table
name    | Pnl
--------| ------------------
account1| -15.90856 37.76571
account2| -18.45611 66.12278

https://code.kx.com/q/ref/avg/#avg

https://code.kx.com/q/ref/sqrt/

https://code.kx.com/q/ref/dev/#sdev

As shown on the kx ref, the sdev calculation is as follows which you could use as a base to create your own to suit what you want/expect.

{sqrt var[x]*count[x]%-1+count x}

There is also wavg if you want to do weighted average:

https://code.kx.com/q/ref/avg/#wavg

Edit: Assuming this can work by substituting in weighted calculations, here's a weighted sdev I've thrown together wsdev:

table:update weight:2 6 3 5 2 4 5 6 7 3 from table;

wsdev:{[w;v] sqrt (sum ( (v-wavg[w;v]) xexp 2) *w)%-1+sum w }

// substituting avg and sdev above
w95CI:{[w;v] wavg[w;v] + -1 1*  1.960*wsdev[w;v]%sqrt count v };

select w95CI[weight;Pnl] by name from table

name    | Pnl
--------| ------------------
account1| -19.70731 28.47701
account2| -8.201463 68.24146