How to append previous 6 trade records as columns for each transaction?

Question

Columns:

date of transaction
uid is the trader id
sym is the trade id

Subset of table:

q) select date, uid, sym from tb

date       uid    sym   
------------------------
2011.08.12 171196 537876
2012.09.08 171196 562161
2012.12.28 171196 570391
2014.04.29 171196 599420
2014.04.29 171196 601520
2014.05.11 171196 602286
2014.06.24 171196 605785
2014.07.19 171196 605686
2011.03.15 160872 524982
2011.07.11 168153 536311
2011.07.25 168153 535616
2011.08.25 30746  537340
2011.01.27 122083 523350
2011.03.05 122083 525676
2011.05.06 122083 531523
2011.01.07 181372 521088
2011.02.07 181372 522780
2011.03.02 181372 523984
2011.03.15 181372 524980
2011.03.21 181372 525448
2011.04.09 181372 529164
2011.04.19 181372 527627
2011.04.28 181372 528302
2011.05.16 181372 530337
2011.06.14 181372 532987

Aim

I'm trying to get the previous 6 trades for each uid for each sym.

There are multiple uids for each sym; for each of these I need their previous 6 syms for each uid. Need to return all the columns, not just the subset you can see above.

For example, for this row:

2011.04.19 181372 527627

it would return + additional columns row by row - so it would return each prev info for each previous date where sym and uid match in same row in new table - so it is the original table by with additional columns containing previous data for each row.

date       uid    sym     prevdate1    prevuid1   prevsym1  prevdate2   prevuid2  etc.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2011.04.19 181372 527627 2011.01.07 181372 521088 2011.03.02 181372 523984 2011.03.15 181372 524980 2011.03.21 181372 525448

If there are no previous dates or only 3 instead of 6 etc. then it will still return the columns but will just be null.

My attempt:

getHHData:{[tb;syms;colnames;numtrades]
dict:exec i by uid from select uid from tb where sym=syms;
tradedate:first exec date from tb where sym=syms;
histdates:?[?[tb;enlist (in;`uid;(raze;(inv;`dict)));(enlist`uid)!enlist`uid;colnames!colnames];();`uid;`date];
histdatesro:raze each value (asc dict),histdates;
ind:til count histdatesro;
preind:{[prevtrades;ind;tradedate;histdatesro]
{[prevtrades;ind;tradedate;histdatesro]
prevtrades+{where y[z] in x}[tradedate;histdatesro;]each ind
}[;ind;tradedate;histdatesro]
}[;ind;tradedate;histdatesro]each neg 1_til numtrades;
uids:exec uid from tb where sym=syms;
histdata:reverse each key asc(value ?[tb;enlist (in;`uid;`uids);`uid;last colnames])!(value dict);
histCols:{[x;data]
{$[x<count y;y[x];0N]}[x] each data}[;histdata] each til numtrades-1;
flip(`sym`uid,`$string[last colnames],/:string 1+til numtrades-1)!(enlist[count[histdata]#syms],enlist[uids],histCols)
}

Wrapper function:

getHHDataMerged:{[tb;syms;colnames;numtrades]
raze{[tb;sym;colnames;numtrades](lj/){`sym`uid xkey x
}each {[tb;sym;c;numtrades]getHHData[tb;sym;c;numtrades]
}[tb;sym;;numtrades]each colnames
}[tb;;colnames;numtrades]each syms
}

I'm looking for a more concise approach to solving this problem.

EDIT:

q)updCols:{`$string[x],\:string[y]}
q)prevCols:{if[x=0;:`date`uid`sym]; updCols[;x] `prevdate`prevuid`prevsym}
q)7#f/[tb1;1+til 6]
'type
  [0]  7#f/[tb1;1+til 6]
          ^
q)tb1
date       uid    sym   
------------------------
2011.08.12 171196 537876
2012.09.08 171196 562161
2012.12.28 171196 570391
2014.04.29 171196 599420
2014.04.29 171196 601520
2014.05.11 171196 602286
2014.06.24 171196 605785
2014.07.19 171196 605686
2011.03.15 160872 524982

terrylynch · Accepted Answer · 2025-03-31 13:55:23Z

An alternative approach to appending many extra columns is to just maintain a column of indices back into the table:

q)t:([]date:20?2011.03.01+til 10;uid:20?17000+til 10;sym:20?50000+til 15);
q)show t:update idx:{neg[6]sublist x,y}\[();i] by uid from t
date       uid   sym   idx
--------------------------------
2011.03.07 17009 50012 ,0
2011.03.05 17002 50008 ,1
2011.03.02 17007 50010 ,2
2011.03.04 17000 50001 ,3
2011.03.04 17001 50010 ,4
2011.03.08 17009 50009 0 5
2011.03.09 17002 50010 1 6
2011.03.03 17001 50011 4 7
2011.03.02 17008 50010 ,8
2011.03.05 17008 50005 8 9
2011.03.03 17001 50004 4 7 10
2011.03.09 17007 50013 2 11
2011.03.01 17002 50006 1 6 12
...

Then you can index into the table for each one you want to inspect

q)t raze exec idx from t where date=2011.03.03,uid=17002
date       uid   sym   idx
--------------------------------
2011.03.05 17002 50008 ,1
2011.03.09 17002 50010 1 6
2011.03.01 17002 50006 1 6 12
2011.03.03 17002 50005 1 6 12 16

qfb · Accepted Answer · 2025-03-30 09:05:13Z

1

edit: didn't have it by uid originally - updated

does this solution work for your case?

q)t:([]date:20?2011.03.01+til 10;uid:20?17000+til 10;sym:20?50000+til 15)
q)7#t
date       uid   sym
----------------------
2011.03.09 17008 50001
2011.03.02 17003 50000
2011.03.01 17004 50000
2011.03.08 17001 50010
2011.03.04 17006 50001
2011.03.04 17004 50010
2011.03.07 17004 50007

q)updCols:{`$string[x],\:string[y]}
q)prevCols:{if[x=0;:`date`uid`sym]; updCols[;x] `prevdate`prevuid`prevsym}
q)f:{![x;();{x!x}1#`uid;prevCols[y]!(prev;) each prevCols y-1]}
q)12#f/[t;1+til 6]
date       uid   sym   prevdate1  prevuid1 prevsym1 prevdate2  prevuid2 prevsym2 prevdate3 prevuid3 prevsym3 prevdate4 prevuid4 prevsym4 prevdate5 pre..
------------------------------------------------------------------------------------------------------------------------------------------------------..
2011.03.07 17009 50012                                                                                                                                ..
2011.03.05 17002 50008                                                                                                                                ..
2011.03.02 17007 50010                                                                                                                                ..
2011.03.04 17000 50001                                                                                                                                ..
2011.03.04 17001 50010                                                                                                                                ..
2011.03.08 17009 50009 2011.03.07 17009    50012                                                                                                      ..
2011.03.09 17002 50010 2011.03.05 17002    50008                                                                                                      ..
2011.03.03 17001 50011 2011.03.04 17001    50010                                                                                                      ..
2011.03.02 17008 50010                                                                                                                                ..
2011.03.05 17008 50005 2011.03.02 17008    50010                                                                                                      ..
2011.03.03 17001 50004 2011.03.03 17001    50011    2011.03.04 17001    50010                                                                         ..
2011.03.09 17007 50013 2011.03.02 17007    50010                                                                                                      ..

edited Mar 30 at 9:05

answered Mar 30 at 0:39

qfb

1194 bronze badges

7 Comments

threedom Mar 30 at 9:01

No that didn't work

threedom Mar 30 at 9:02

I have updated my question to show u the output of ur solution.

qfb Mar 30 at 9:06

apologies was in the middle of an edit can you try again

threedom Mar 30 at 9:16

It seems to be working - if i wanted to add more columns like bid, ask, etc. would i just add them to the prevCol function in the symbol list?

qfb Mar 30 at 10:15

Yeah that should work. Note the 0 case needs to be updated too

|

Collectives™ on Stack Overflow

How to append previous 6 trade records as columns for each transaction?

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related