Given a table t as
ticker time close
---------------------------------------------
IBM 2025.04.07D09:15:00.000000000 22161.6
IBM 2025.04.08D09:15:00.000000000 22535.85
IBM 2025.04.11D09:15:00.000000000 22828.55
IBM 2025.04.15D09:15:00.000000000 23328.55
IBM 2025.04.16D09:15:00.000000000 23437.
How can I efficiently (using perhaps functional update) update/add, grouped by ticker, columns MA10 ... MA90 to t which are the moving average mavg[n;close] where n ranges from 10 to 90?
Once that's done, how can I efficiently transform these columns MA10 ... MA90 to sMA10 ... sMA90 where depending on whether MA10 >= close we get a BUY signal, or SELL otherwise.
Thirdly, I want to be able to calculate the 'average' column named signal over all the sMA10 ... sMA90 be it either a BUY or SELL.
In other words, I am looking for one-liners instead of writing explicit statements or do loops for accomplishing the above.
Yes, this would require a functional update. Something like:
t:([]ticker:`IBM`MSFT where 2#100;time:raze 2#enlist 0D09:15+2025.01.01+til 100;close:raze 50 300f+\:(100?10.0)-2.5)
q)show t:![t;();{x!x}1#`ticker;{(`$"ma",/:string x)!flip(mavg;x;`close)}10 20 90]
ticker time close ma10 ma20 ma90
------------------------------------------------------------------------
IBM 2025.01.01D09:15:00.000000000 51.36584 51.36584 51.36584 51.36584
IBM 2025.01.02D09:15:00.000000000 57.43149 54.39867 54.39867 54.39867
IBM 2025.01.03D09:15:00.000000000 56.86201 55.21978 55.21978 55.21978
...
For the signals
q){`sell`buy@/:(3!x)>='x`close}t
ticker time close | ma10 ma20 ma90
---------------------------------------------| --------------
IBM 2025.01.01D09:15:00.000000000 51.36584| buy buy buy
IBM 2025.01.02D09:15:00.000000000 57.43149| sell sell sell
IBM 2025.01.03D09:15:00.000000000 56.86201| sell sell sell
IBM 2025.01.04D09:15:00.000000000 52.3495 | buy buy buy
IBM 2025.01.05D09:15:00.000000000 50.09265| buy buy buy
IBM 2025.01.06D09:15:00.000000000 56.83592| sell sell sell
...
The final part of "averaging" is left to the OP (not exactly sure what you mean by average here, the most frequent signal? Either way, this could get you most of the way).