Time Weighted Average

Source: adopted from here

Introduction

The time weighted average is a commonly used measure in electronic trading. This measure assigns a smaller weight to a sudden and short-lived changes in the analytics you are interested in so that your analytics is not dramatically influenced by outliers.

Question

The function simulateSpreadBps simulates the spread from 09:30 to 16:00, which returns a table with two columns: time and spreadBps. The time is the effective time of the corresponding spread, which is in the unit of basis point.

simulateSpreadBps:{[n]
    maxSpreadBps:10;
    cdf:0,sums {[n;p]n{(0,y*1-x)+x*y,0}[p]/1#1f}[maxSpreadBps-1;0.5];
    seed:-314159;
    mktOpenTime:"t"$09:30;
    mktCloseTime:"t"$16:00;
    system "S ",string seed;
    spreads:1+bin[cdf;n?1f]+n?1f;
    duration:ceiling raze 1?/:500&1%0.01*abs spreads-0.5*maxSpreadBps;
    dur:mktDur*durP:duration%mktDur:`long$mktCloseTime-mktOpenTime;
    times:mktOpenTime+`long$sums dur%sum durP;
    data:([]time:mktOpenTime,times;spreadBps:`float$maxSpreadBps,spreads);
    data:update dur:next[time]-time from data;
    data:update spreadBps:5*spreadBps from data where dur<00:00:00.050;
    select time,spreadBps from data
  };
spreads:simulateSpreadBps[100000];

Write a function calcTwaSpread to calculate the time weighted average spread for any given time period. The expected time weighted average spread between 10:31 and 14:35 is about 6.52 bps, instead, the simple average spread is around 11 bps.

calcTwaSpread[spreads;10:31;14:35] / 6.52

Answer

Below is the suggested answer:

calcTwaSpread:{[tbl;st;et]
    endPoints:([] time:"t"$(st;et));
    endPoints:endPoints,'tbl asof endPoints;
    targetTbl:`time xasc endPoints,select from tbl where time within (st;et);
    targetTbl:update dur:next[time]-time from targetTbl;
    exec dur wavg spreadBps from targetTbl
  };

The spreads at the end points of a given time range might not readily available. In this case, the prevailing spread should be interpolated at the end times of the time range. It is worth mentioning that the effective duration of a spread is from the current time to the time of next row in the table. Once the duration of a spread is determined, wavg should be used to calculate the weighted average.