Moving Average of Multiple Securities

Community Support
1

I’m having trouble wrapping my head around how to calculate trailing averages in a table that contains many securities.  The examples I’ve found only demonstrate how to perform the calculation on a single timeseries.

q)select from day where bbgid.ticker in ("BMO";"TD")date bbgid | open high low close volume vwap -----------------------| ---------------------------------------2014.07.02 BBG000BCRMB0| 54.93 55.32 54.93 55.06 4290490 55.16762014.07.02 BBG000BXSQ97| 78.6 79 78.45 78.79 1345347 78.83652014.07.03 BBG000BCRMB0| 55.35 55.39 55.1 55.12 5148023 55.22192014.07.03 BBG000BXSQ97| 79 79.47 78.83 78.87 1473150 79.07922014.07.04 BBG000BCRMB0| 55.22 55.35 55.22 55.28 2215873 55.30192014.07.04 BBG000BXSQ97| 78.91 79.18 78.88 78.89 330203 79.02052014.07.07 BBG000BCRMB0| 54.84 55.05 54.6 55.05 5173346 54.81712014.07.07 BBG000BXSQ97| 79.02 79.47 78.76 79.28 1601820 79.11712014.07.08 BBG000BCRMB0| 55 55.05 54.61 54.99 6530176 54.85342014.07.08 BBG000BXSQ97| 79.23 79.63 79 79.48 2017145 79.30572014.07.09 BBG000BCRMB0| 55.07 55.33 54.79 55.3 5248002 55.17492014.07.09 BBG000BXSQ97| 79.41 80 79.11 79.94 1525506 79.82172014.07.10 BBG000BCRMB0| 54.47 55.34 54.47 55.17 3822271 55.15832014.07.10 BBG000BXSQ97| 79.49 79.8 79.22 79.78 1689260 79.62652014.07.11 BBG000BCRMB0| 55.29 55.3 54.93 55.05 2013392 55.04972014.07.11 BBG000BXSQ97| 79.8 79.95 79.55 79.81 946332 79.78722014.07.14 BBG000BCRMB0| 55.23 55.3 55.06 55.23 3196446 55.19232014.07.14 BBG000BXSQ97| 80 80.4 79.95 80.09 1248129 80.112 2014.07.15 BBG000BCRMB0| 55.19 55.34 55.12 55.25 3109798 55.28442014.07.15 BBG000BXSQ97| 80.05 80.68 80.01 80.36 1920489 80.3963..

Am I organizing my tables incorrectly or is there a simple way of grouping by id and then inserting a new column?

Thanks,

Ryan

2

fmt:“DSFFFFJF”;

fns:datesymopenhighlowclosevolumevwap;

day:flip fns!(fmt; csv) 0: `$“:/tmp/day.csv”;

update m:(mavg[2];close) fby sym from day;


/ To make the results easier to check

update close:i from `day;

closem xcols

symdate xasc

update m:(mavg[2];close) fby sym from day


/ /tmp/day.csv

2014.07.02,BBG000BCRMB0,54.93,55.32,54.93,55.06,4290490,55.1676

2014.07.02,BBG000BXSQ97,78.6,79,78.45,78.79,1345347,78.8365

2014.07.03,BBG000BCRMB0,55.35,55.39,55.1,55.12,5148023,55.2219

2014.07.03,BBG000BXSQ97,79,79.47,78.83,78.87,1473150,79.0792

2014.07.04,BBG000BCRMB0,55.22,55.35,55.22,55.28,2215873,55.3019

2014.07.04,BBG000BXSQ97,78.91,79.18,78.88,78.89,330203,79.0205

2014.07.07,BBG000BCRMB0,54.84,55.05,54.6,55.05,5173346,54.8171

2014.07.07,BBG000BXSQ97,79.02,79.47,78.76,79.28,1601820,79.1171

2014.07.08,BBG000BCRMB0,55,55.05,54.61,54.99,6530176,54.8534

2014.07.08,BBG000BXSQ97,79.23,79.63,79,79.48,2017145,79.3057

2014.07.09,BBG000BCRMB0,55.07,55.33,54.79,55.3,5248002,55.1749

2014.07.09,BBG000BXSQ97,79.41,80,79.11,79.94,1525506,79.8217

2014.07.10,BBG000BCRMB0,54.47,55.34,54.47,55.17,3822271,55.1583

2014.07.10,BBG000BXSQ97,79.49,79.8,79.22,79.78,1689260,79.6265

2014.07.11,BBG000BCRMB0,55.29,55.3,54.93,55.05,2013392,55.0497

2014.07.11,BBG000BXSQ97,79.8,79.95,79.55,79.81,946332,79.7872

2014.07.14,BBG000BCRMB0,55.23,55.3,55.06,55.23,3196446,55.1923

2014.07.14,BBG000BXSQ97,80,80.4,79.95,80.09,1248129,80.112,

2014.07.15,BBG000BCRMB0,55.19,55.34,55.12,55.25,3109798,55.2844

2014.07.15,BBG000BXSQ97,80.05,80.68,80.01,80.36,1920489,80.3963

3

this could work, too

  q)update m:2 mavg close by sym from day

to check:

  q)(update m:(mavg[2];close) fby sym from day)~update m:2 mavg close by sym from day

1b