apply formula to all value in a table

Community Support
1

Hi there,

I have a table (type 98) which shows dividends estimates by date and ticker. 

Column 1  shows all the tickers. column 2, all the spots, and then another 100 columns with the different dates. See below.

mnemo    spot    2020.06.30    2020.07.22   …

aapl UQ   100     0.52               …

…

I am struggling with two things. first I am trying to divide all values by the stock spot. Second I am trying to group the columns by year.

Could anyone please help me on this?

Thanks a lot in advance,

Victor

2

Seems like your table is pivoted, you will have to unpivot it and then do the grouping

q)tmnemo spot 2020.06.30 2020.07.01------------------------------------appl 2.782122 0.391543 0.5919004 hsbc 2.392341 0.08123546 0.8481567 vod 1.508133 0.9367503 0.389056 q)q)t:ungroup(mnemospot#t),' flipdateval!(count[t]#enlist"D"$string d;flip t d:2_cols t)q)tmnemo spot date val ------------------------------------appl 2.782122 2020.06.30 0.391543 appl 2.782122 2020.07.01 0.5919004 hsbc 2.392341 2020.06.30 0.08123546hsbc 2.392341 2020.07.01 0.8481567 vod 1.508133 2020.06.30 0.9367503 vod 1.508133 2020.07.01 0.389056 q)q)select val%spot by date, mnemo from tdate mnemo| val ----------------| ----------2020.06.30 appl | 0.1407354 2020.06.30 hsbc | 0.033956472020.06.30 vod | 0.6211324 2020.07.01 appl | 0.2127515 2020.07.01 hsbc | 0.35453 2020.07.01 vod | 0.2579719

3

without ungroup (~30% faster but messier code):

q)t:flip mnemospot2020.06.302020.07.222020.08.032020.08.042020.08.05!(aaplibmmsfttsla;100 200 150 600;.52 1.2 2.4 .2;.76 1.5 2.1 .3;.6 1.3 1.9 .14;.65 1.23 1.89 .5;.55 1.1 2.01 .34)q)c:2_cols tq)nt:exec flip ((mnemospot!(raze count[c]#'mnemo;raze count[c]#'spot)),datedivd!(“D”$string (count[t[c]0]*count c)#c;raze flip t c)) from tq)ntmnemo spot date divd--------------------------aapl 100 2020.06.30 0.52aapl 100 2020.07.22 0.76aapl 100 2020.08.03 0.6aapl 100 2020.08.04 0.65aapl 100 2020.08.05 0.55ibm 200 2020.06.30 1.2ibm 200 2020.07.22 1.5ibm 200 2020.08.03 1.3ibm 200 2020.08.04 1.23ibm 200 2020.08.05 1.1msft 150 2020.06.30 2.4msft 150 2020.07.22 2.1msft 150 2020.08.03 1.9msft 150 2020.08.04 1.89msft 150 2020.08.05 2.01tsla 600 2020.06.30 0.2tsla 600 2020.07.22 0.3tsla 600 2020.08.03 0.14tsla 600 2020.08.04 0.5tsla 600 2020.08.05 0.34q)select divd%spot by date,mnemo from ntdate mnemo| divd----------------| ------------2020.06.30 aapl | 0.00522020.06.30 ibm | 0.0062020.06.30 msft | 0.0162020.06.30 tsla | 0.00033333332020.07.22 aapl | 0.00762020.07.22 ibm | 0.00752020.07.22 msft | 0.0142020.07.22 tsla | 0.00052020.08.03 aapl | 0.0062020.08.03 ibm | 0.00652020.08.03 msft | 0.012666672020.08.03 tsla | 0.00023333332020.08.04 aapl | 0.00652020.08.04 ibm | 0.006152020.08.04 msft | 0.01262020.08.04 tsla | 0.00083333332020.08.05 aapl | 0.00552020.08.05 ibm | 0.00552020.08.05 msft | 0.01342020.08.05 tsla | 0.0005666667q)\t do[10000;exec flip ((mnemospot!(raze count[c]#'mnemo;raze count[c]#'spot)),datedivd!(“D”$string (count[t[c]0]*count c)#c;raze flip t c)) from t]96`