Analytics Functions Example 14: Value Weighted Average

Value Weighted Average

Show the volume weighted average close prices for the last week of March 2013:

 
    SELECT symbol, 
        seq_search(day, 
        seq_last_int(day)-5, 
        seq_last_int(day)) as last_week,
        seq_sum(close@last_week*volume@last_week)/seq_sum(volume@last_week) as "vwap"
    FROM Quote WHERE symbol='IBM';
 
    symbol
    last_week{}
    close{}
    volume{}
    vwap
    ------------------------------------------------------------------
    IBM
    {20130325, 20130326, 20130327, 20130328}
    {210.740005, 212.360001, 210.889999, 213.300003}
    {3242463, 2300240, 3223381, 3752999}
    211.843723
            
     

The volume weighted average is calculated by the formula

vwap = sum(close*volume) / sum(volume)

The table below sums the close*volume values (=2,652,089,151) then divides by the sum of volume (=12,519,083):

Trade Date	Close	Volume	Close*Volume
20130325	210.740005	3242463	683316668.8
20130326	212.360001	2300240	488478968.7
20130327	210.889999	3223381	679778815.9
20130328	213.300003	3752999	800514698
Sum		12519083	2652089151
vwap			211.843723

To verify: 2,652,089,151 / 12,519,083 = 211.843723.

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 14