Compute a Lagging VWAP with Window Join

Question

Given the table:

trades:([] sym:`A;
           time:09:30:00.000 09:30:15.500 09:30:32.100 09:30:58.750 09:31:12.300 09:31:45.600 09:32:03.200 09:32:28.900 09:32:55.400 09:33:18.700;
           price:100.5 100.7 100.3 100.9 101.2 101.0 100.8 101.5 101.3 101.1;
           size:10.0 12.0 11.0 15.0 18.0 2.0 9.0 3.0 11.0 6.0)

Create a new column called vwapPrice, which is a 1-minute, lagging, volume-weighted average price computed over the last minute.

Solution

w:-00:01 00:00 +\: trades`time
out:wj1[w;`sym`time;update orig_price:price from trades;(trades;(wavg;`size;`price))]
`sym`time`vwapPrice`size`price xcol out

sym time         vwapPrice size price
-------------------------------------
A   09:30:00.000 100.5     10   100.5
A   09:30:15.500 100.6091  12   100.7
A   09:30:32.100 100.5061  11   100.3
A   09:30:58.750 100.6292  15   100.9
A   09:31:12.300 100.8357  18   101.2
A   09:31:45.600 101.06    2    101
A   09:32:03.200 101.0621  9    100.8
A   09:32:28.900 100.9786  3    101.5
A   09:32:55.400 101.1304  11   101.3
A   09:33:18.700 101.27    6    101.1

Alternative Solution

out:wj1[w;`sym`time;update orig_price:price from trades;(trades;({[x;y] (x$y)%sum x};`size;`price))]

Explanation

wavg is the volume-weighted average — wavg[size;price] weights each price by its corresponding size rather than treating every price equally, which is exactly what VWAP requires. (wavg;size;price) tells wj1 to compute that weighted average over the size and price columns of every window.

There's a naming collision to avoid here: the wavg aggregate produces a result column named price (matching the column it's based on), but trades already has a price column. Rather than let one silently overwrite the other and lose track of which is which, update orig_price:price from trades keeps a separate copy of the original price under a different name (orig_price) before the join — so after wj1 merges the new, aggregate price column into the result, the original untouched price is still available, just under the orig_price label.

`sym`time`vwapPrice`size`price xcol out then renames everything into its final, readable form: the merged (now-aggregate) price column becomes vwapPrice, and orig_price is renamed back to price — giving a table with the row's own raw price under price, and the computed VWAP under vwapPrice, alongside the size that was already correctly labeled throughout.

The alternative computes the same weighted average from first principles: x$y is q's matrix-multiply operator (mmu), which for two equal-length vectors collapses to a dot product — the sum of each size multiplied by its corresponding price. Dividing that by sum x (the total size) gives the textbook VWAP formula: sum(size*price) / sum(size), spelled out rather than relying on the built-in wavg.