Hi al
Struggling to do something elegantly in q - Given a table such as the following:
q)t:(timestamp:10?.z.P;sym:10?IBMGOOG;bid:10?2.;ask:10?2.)
q)t
timestamp sym bid ask
2000.10.20D22:21:31.475859324 IBM 1.80615 0.824634
2003.09.24D14:46:15.547312064 IBM 1.550058 1.975569
2008.09.22D02:28:51.659587360 GOOG 0.7739636 0.7734706
2011.12.03D09:26:32.196052096 IBM 1.264823 1.453562
2001.12.09D04:18:46.408592112 GOOG 0.8657069 0.8093092
2006.05.04D22:51:25.395450560 IBM 0.4861672 1.671013
2009.04.19D08:22:33.286842304 IBM 0.7835905 1.285474
2005.01.04D13:19:53.380033792 GOOG 0.04902672 1.166052
2004.06.12D11:19:19.618774224 GOOG 0.3382087 0.2849871
2000.09.14D11:06:33.485095772 IBM 0.7882164 1.829976
If I wanted to add a column to this table containing say a log-return calc based off the mid price ((bid+ask)/2), obviously I cant just do something like:
q)select lr:deltas log (0.5*(bid+ask)) from t
lr
0.2741348
0.2927764
-0.8234601
0.5634396
-0.4842151
0.2529791
-0.0417053
-0.5322874
-0.6677038
1.435379
As the return calcs need to be grouped by symbol if symbols are interspersed in the table. In R I would treat sym as a factor and perform calculations grouped by sym, however not quite sure how I can do this in q. Ive tried:
q)select lr:deltas log (0.5*(bid+ask)) by sym from t
sym | lr
----| -------------------------------------------------------------
GOOG| -0.256549 0.07922455 -0.3210136 -0.6677038
IBM | 0.2741348 0.2927764 -0.2600205 -0.231236 -0.0417053 0.2353877
or
q)logret:{deltas log x}
q)select (logret;0.5*(bid+ask)) fby sym from t
q)select (logret;0.5*(bid+ask)) fby sym from t
sym
0.2741348
0.2927764
-0.256549
-0.2600205
0.07922455
-0.231236
-0.0417053
-0.3210136
-0.6677038
0.2353877
I guess I have two issues :
-
grouping these calcs across syms and transforming to another column;
-
as the log-diffs will have cardinality N-1 for each group of N observations per sym - either dropping the first observation or prefixing zero at the start of the log-returns series
Obviously I could split the table into multiple tables by sym and perform the calcs separately, but it would be lovely if I had a nice way in q to do this in-place across the entire table.
Cheers
– Rory