Analytics Functions Example 2: Weighted Average

Volume Weighted Average Buy and Sell Prices

Given a table of trades with price and volume, the following SQL select statement will compare each trade price Pn against its previous one Pn-1. If Pn-1 > Pn, it is a buy initiated trade; if Pn-1 < Pn, it is a sell initiated trade; if Pn-1 = Pn, it will compare against Pn-2, or Pn-3 and so on until it finds the last price movement. Then it calculates the volume weighted average price of all the buy initiated trades VWAPB and all the sell initiated trades VWAPS:

 
    SELECT seq_wavg( seq_filter( seq_trend(price) < 0, volume),
        seq_filter( seq_trend(price) < 0, price) ) as VWAPB,
        seq_wavg( seq_filter( seq_trend(price) > 0, volume),
        seq_filter( seq_trend(price) > 0, price)) as VWAPS
    FROM Security
     

This example relies on the function seq_trend() to determine which way the compared prices are “trending”; if Pn-1 is less than Pn seq_trend() returns -1 which indicates a “sell” trade; if Pn-1 is greater than Pn seq_trend() returns 1 which indicates a “buy” trade. For example, consider the sequence of price values:

 
    { 1, 2, 3, 3, 2, 2, 4, 5, 6, 5, 5 }
     

For this sequence mco_seq_trend() would produce the following resulting sequence:

 
    { 0, 1, 1, 1,-1,-1, 1, 1, 1,-1,-1 }
     

The two calls to seq_filter() return sequences with only those elements for which the “trend” for price is less than 0 for VWAPB, greater than 0 for VWAPS. Finally the call to seq_wavg() returns the weighted average of these two sequences. For example, given the sequence of (price, volume) values:

 
    { (10.0, 100),  (10.1, 150),  (10.2,200),  (10.2,300),  (9.9, 90),  (9.8,110)}
     

The “buy” trades (Pn-1 > Pn) would result from the pairs:

     
    (10.2,300) <> (9.9, 90)
    (9.9, 90)    <> (9.8,110)
     

The “sell” trades (Pn-1 < Pn) would result from the pairs:

     
    (10.0, 100) <> (10.1, 150)
    (10.1, 150) <> (10.2,200)
    (10.1,150)  <> (10.2,300)
     

And the resulting values would be:

 
    VWAPB = (9.9 * 90 + 9.8 * 110) / (90 + 110) = 9.845
     
    VWAPS = (10.1 * 150 + 10.2 * 200 + 10.2 * 300) / (150 + 200 + 300) = 10.177
     

To run this example

A sample script to demonstrate this select statement using xSQL can be run from the samples/xsql/scripts/financial directory with the following command:

     
    x 2