VWAP calculator for ASX securities: why the spreadsheet number differs

Two trading days show the size of it. Real windows are longer, 15 qualifying trading days under rule 7.1A.3 (ASX Listing Rule 7.1A.3), but two days show the mechanism without fifteen rows of arithmetic.

The two-day VWAP is the total value divided by the total volume: $1,240,000 / 1,200,000 = $1.0333. Average the two daily VWAPs instead and $1.00 and $1.20 average to $1.10. The gap is six and two thirds of a cent, 6.7 cents rounded, and it is not a rounding artefact. Day 2 carried one sixth of the volume and the shortcut gave it half the say.

Put that gap where it lands. A placement under the additional 10% capacity must be priced at not less than 75% of the volume weighted average market price (ASX Listing Rule 7.1A.3). Seventy-five per cent of $1.0333 is 77.5 cents. Seventy-five per cent of $1.10 is 82.5 cents. The two methods produce minimum issue prices five cents apart on the same two days of trading.

Here the shortcut reads high, which is the safer of the two directions. Swap the two volumes over, so the million shares trade at $1.20 and the 200,000 at $1.00, and the value traded becomes $1,400,000 on the same 1,200,000 shares: $1,400,000 / 1,200,000 = $1.1667. The average of the daily VWAPs is still $1.10. The floor is now 87.5 cents and the shortcut says 82.5 cents, so an issue priced at what the spreadsheet called the floor comes in five cents under the price the rule requires, and nothing in the paperwork shows it.

If each row of the table genuinely carries the day's VWAP and the day's volume, the trade-level answer is recoverable exactly. Multiply each day's VWAP by that day's volume to recover the value traded, add the values down the column, add the volumes, and divide once. On the two days above: $1.00 × 1,000,000 is $1,000,000, $1.20 × 200,000 is $240,000, and $1,240,000 / 1,200,000 = $1.0333. One division at the end, never an average of the daily column.

Two conditions have to hold for that recovery to be the rule's figure. The VWAP column and the volume column must describe the same trade set, and that trade set must already be the one the definition names: both markets in, the six excluded trade types out (ASX Listing Rule 19.12). Nothing on the face of the table says whether either condition is met, and a daily summary built from all reported trading fails the second one before the arithmetic starts.

The example above is generous, because it assumes the daily table carries a true daily VWAP. Most do not. They carry open, high, low, close and volume, and the usual substitute is a typical price of (high + low + close) / 3 weighted by daily volume. That method compresses a whole session into one blended number built from three prices, then assumes the day's volume all traded there.

The exclusions are the harder problem. The trades the definition takes out are large by construction: block trades start at $200,000 of consideration for a Tier 3 equity market product and $1,000,000 at Tier 1, and a large portfolio trade runs to at least $5,000,000 across at least 10 classes (ASIC Market Integrity Rules (Securities Markets) 2017, rules 6.2.1 and 6.2.2), so a single one of them left in a thin stock's five-day window can move the answer on its own. A daily summary cannot be repaired, because the excluded types are already blended into the day's volume and the day's range.

The fix is time and sales data covering both markets, with price, volume, timestamp and condition codes on every row, and a documented policy for turning those codes into exclusions. Expect the exclusion set applied in practice to be wider than the six named categories, because real data carries trade types the rule never contemplated, and record that as a judgement rather than as the rule speaking.

Read the full guide to common VWAP calculation mistakes.