Hello,
I have a table t grouped by Instrument and Trade column can be 1, -1 or 0. I want to add a column LastTrade so LastTrade:Trade if Trade<>0 and LastTrade:prev Last trade if Trade=0
I tried:
t:update LastTrade:$[Trade=1;Trade=-1;-1;prev LastTrade] by Instrument from t
But it doesn’t work
q)t:(Instrument:AUAUAUAUAUAUAUAUAUAUEUEUEUEUEUEUEUEUEUEUGUGUGUGUGUGUGUGUGUGU;Trade:0 0 1 0 0 0 0 -1 0 0 0 -1 0 0 1 0 0 0 0 0 0 0 -1 0 0 1 0 -1 0 0)
I want something like this (LastTrade Column was filled by hand):
Instrument Trade LastTrade
--------------------------
AU 0 0
AU 0 0
AU 1 1
AU 0 1
AU 0 1
AU 0 1
AU 0 1
AU -1 -1
AU 0 -1
AU 0 -1
EU 0 0
EU -1 -1
EU 0 -1
EU 0 -1
EU 1 1
EU 0 1
EU 0 1
EU 0 1
EU 0 1
EU 0 1
GU 0 0
GU 0 0
GU -1 -1
GU 0 -1
GU 0 -1
GU 1 1
GU 0 1
GU -1 -1
GU 0 -1
GU 0 -1
Please can you help me?
Cheers
Francisco
Hi,
Think this will work for you.
update 0^fills LastTrade by Instrument from update LastTrade:?[trade=0;0n;trade] from t
Marcus
Sent from my iPhone
See vector conditional - http://code.kx.com/wiki/Reference/QuestionSymbol#vector_conditional
update LastTrade:?[Trade<>0;Trade;prev LastTrade] from t
Hi,
Slightly nicer option than my previous mail would be:
update lastTrade:{$[y=0;x;y]}scan trade from t
HTH
Marcus
Sent from my iPhone
That last query I sent should have a “by Instrument” added to it obviously, typo when emailing from my phone:
update lastTrade:{$[y=0;x;y]}scan trade by Instrument from t
Sent from my iPhone
Many Thanks Manis and Markus for your help.
Francisco
Good afternoon,
In the Instrument grouped table t I have two columns closeAsk and closeBid and I need to add a column Entry.
Entry column should contain:
If LastTrade=1 the maximum closeAsk since LastTrade value changed to 1
If LastTrade=-1 The minimun closeBid since LastTrade value changed to -1
Instrument closeAsk closeBid Trade LastTrade Entry
--------------------------------------------------
AU 1.35 1.34 0 0
AU 1.40 1.38 0 0
AU 1.21 1.20 1 1 1.21
AU 1.26 1.24 0 1 1.26
AU 1.22 1.20 0 1 1.26
AU 1.22 1.21 0 1 1.26
AU 1.15 1.14 0 1 1.26
AU 1.18 1.16 -1 -1 1.16
AU 1.19 1.17 0 -1 1.16
AU 1.21 1.2 0 -1 1.16
EU 1.08 1.07 0 0
EU 1.05 1.03 -1 -1 1.03
EU 1.06 1.04 0 -1 1.03
EU 1.03 1.02 0 -1 1.02
EU 1.05 1.04 1 1 1.05
EU 1.06 1.05 0 1 1.06
EU 1.08 1.06 0 1 1.08
EU 1.11 1.09 0 1 1.11
EU 1.14 1.13 0 1 1.14
EU 1.13 1.12 0 1 1.14
GU 1.45 1.43 0 0
GU 1.42 1.4 0 0
GU 1.39 1.37 -1 -1 1.37
GU 1.36 1.35 0 -1 1.35
GU 1.40 1.37 0 -1 1.35
GU 1.42 1.41 1 1 1.42
GU 1.45 1.44 0 1 1.45
GU 1.39 1.36 -1 -1 1.36
GU 1.38 1.36 0 -1 1.36
GU 1.35 1.34 0 -1 1.34
Thanks
Francisco
I tried t:update streak:{1+(x;0)y}[1;differ signum LastTrade] by Instrument from t;
Instrument closeAsk closeBid Trade LastTrade streak
--------------------------------------------------
AU 1.35 1.34 0 0 1
AU 1.40 1.38 0 0 2
AU 1.21 1.20 1 1 1
AU 1.26 1.24 0 1 2
AU 1.22 1.20 0 1 3
AU 1.22 1.21 0 1 4
AU 1.15 1.14 0 1 5
AU 1.18 1.16 -1 -1 1
AU 1.19 1.17 0 -1 2
AU 1.21 1.2 0 -1 3
EU 1.08 1.07 0 0 1
EU 1.05 1.03 -1 -1 1
EU 1.06 1.04 0 -1 2
EU 1.03 1.02 0 -1 3
EU 1.05 1.04 1 1 1
EU 1.06 1.05 0 1 2
EU 1.08 1.06 0 1 3
EU 1.11 1.09 0 1 4
EU 1.14 1.13 0 1 5
EU 1.13 1.12 0 1 6
GU 1.45 1.43 0 0 1
GU 1.42 1.4 0 0 2
GU 1.39 1.37 -1 -1 1
GU 1.36 1.35 0 -1 2
GU 1.40 1.37 0 -1 3
GU 1.42 1.41 1 1 1
GU 1.45 1.44 0 1 2
GU 1.39 1.36 -1 -1 1
GU 1.38 1.36 0 -1 2
GU 1.35 1.34 0 -1 3
and then
t:update HCloseAsk:(t.streak[i]) mmax closeAsk,LCloseBid:(t.streak[i]) mmin closeBid by Instrument from t
But i get a ´type error
Cheers
Francisco
Hi,
This might work for you:
update entry:{$[x=1; y; x=-1; z; 0n]}. 'flip (lastTrade; maxs closeAsk; mins closeBid) by Instrument from t
Having three conditions makes it more difficult as you can’t use vector conditional as it only works on a list of booleans.
But because the initial method of creating the lastTrade column ensures that only the first x entries for each sym can be 0 it makes the problem a bit simpler.
Hope that helps,
Marcus
Sent from my iPhone
Thanks a Lot for your help!
The problem is that I get the absolute max/min of the closeAsk/closeBid columns, I need the relative max/min of the current streak of rows with the same LastTrade value. This is why i was trying to use streak mmax but it’s not allowed…
Francisco
Hi,
Apologies, should be a small add-on though.
update entry:{$[x=1; y; x=-1; z; 0n]}. 'flip (lastTrade; maxs closeAsk; mins closeBid) by Instrument,lastTrade from t
Instrument Trade lastTrade closeAsk closeBid entry
AU 0 0 1.35 1.34
AU 0 0 1.4 1.38
AU 1 1 1.21 1.2 1.21
AU 0 1 1.26 1.24 1.26
AU 0 1 1.22 1.2 1.26
AU 0 1 1.22 1.21 1.26
AU 0 1 1.15 1.14 1.26
AU -1 -1 1.18 1.16 1.16
AU 0 -1 1.19 1.17 1.16
AU 0 -1 1.21 1.2 1.16
EU 0 0 1.08 1.07
EU -1 -1 1.05 1.03 1.03
EU 0 -1 1.06 1.04 1.03
EU 0 -1 1.03 1.02 1.02
EU 1 1 1.05 1.04 1.05
EU 0 1 1.06 1.05 1.06
EU 0 1 1.08 1.06 1.08
EU 0 1 1.11 1.09 1.11
EU 0 1 1.14 1.13 1.14
EU 0 1 1.13 1.12 1.14
GU 0 0 1.45 1.43
GU 0 0 1.42 1.4
GU -1 -1 1.39 1.37 1.37
GU 0 -1 1.36 1.35 1.35
GU 0 -1 1.4 1.37 1.35
GU 1 1 1.42 1.41 1.42
GU 0 1 1.45 1.44 1.45
GU -1 -1 1.3 1.36 1.35
GU 0 -1 1.38 1.36 1.35
GU 0 -1 1.35 1.34 1.34
Marcus
Is this it?
q)n:20
q)t:(instr:n?ab;closeask:n?10;closebid:n?10;lasttrade:n?0 -1 1)
q)show t
instr closeask closebid lasttrade
b 4 2 0
a 1 7 0
a 3 0 1
a 3 1 -1
b 7 9 1
a 8 2 -1
b 2 1 1
a 1 8 1
a 4 8 -1
b 2 1 0
b 8 7 -1
a 0 2 1
b 5 4 1
a 8 5 -1
b 5 4 0
a 2 2 -1
a 8 7 1
b 6 8 0
a 9 5 -1
b 0 6 -1
// create streak where the last trade value is the same for the given sym
q)update streak:sums differ lasttrade by instr from t
instr closeask closebid lasttrade streak
b 4 2 0 1
a 1 7 0 1
a 3 0 1 2
a 3 1 -1 3
b 7 9 1 2
a 8 2 -1 3
b 2 1 1 2
a 1 8 1 4
a 4 8 -1 5
b 2 1 0 3
b 8 7 -1 4
a 0 2 1 6
b 5 4 1 5
a 8 5 -1 7
b 5 4 0 6
a 2 2 -1 7
a 8 7 1 8
b 6 8 0 6
a 9 5 -1 9
b 0 6 -1 7
// add the entry value dependent on the streak
q)update entry:?[lasttrade=-1;mins closebid;maxs closeask] by instr,streak from (update streak:sums differ lasttrade by instr from t) where lasttrade<>0
instr closeask closebid lasttrade streak entry
b 4 2 0 1
a 1 7 0 1
a 3 0 1 2 3
a 3 1 -1 3 1
b 7 9 1 2 7
a 8 2 -1 3 1
b 2 1 1 2 7
a 1 8 1 4 1
a 4 8 -1 5 8
b 2 1 0 3
b 8 7 -1 4 7
a 0 2 1 6 0
b 5 4 1 5 5
a 8 5 -1 7 5
b 5 4 0 6
a 2 2 -1 7 2
a 8 7 1 8 8
b 6 8 0 6
a 9 5 -1 9 5
b 0 6 -1 7 6
// or can do in one step like this:
update entry:?[lasttrade=-1;mins closebid;?[lasttrade=1;maxs closeask;0N]] by instr,({sums differ x};lasttrade)fby instr from t
Thanks
Jonny