Adding multiple, parametrized columns using functional update

Question

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.

terrylynch · Accepted Answer · 2025-04-17 15:24:03Z

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).

Collectives™ on Stack Overflow

Adding multiple, parametrized columns using functional update

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related