Compute volume-weighted average prices on a stock portfolio.
The example creates an input window named PriceFeed and an output window named VWAP. VWAP outputs the results of the volume-weighted average price of the trade values processed by PriceFeed. The results are grouped by Symbol. The cast function converts share values to float.
CREATE OUTPUT WINDOW VWAP SCHEMA (Symbol STRING, LastPrice FLOAT, VWAP FLOAT, LastTime DATE) PRIMARY KEY DEDUCED AS SELECT PriceFeed.Symbol AS Symbol, PriceFeed.Price AS LastPrice, (sum((PriceFeed.Price * cast(FLOAT ,PriceFeed.Shares))) / cast(FLOAT ,sum(PriceFeed.Shares))) AS VWAP, PriceFeed.TradeTime AS LastTime FROM PriceFeed GROUP BY PriceFeed.Symbol;
The example creates an input window named Positions and an output window named IndividualPositions. IndividualPositions creates a join between Positions and VWAP using their symbol values.
CREATE OUTPUT WINDOW IndividualPositions SCHEMA (BookId STRING, Symbol STRING, CurrentPosition FLOAT, AveragePosition FLOAT) PRIMARY KEY (BookId, Symbol) AS SELECT Positions.BookId AS BookId, Positions.Symbol AS Symbol, (VWAP.LastPrice * cast(FLOAT ,Positions.SharesHeld)) AS CurrentPosition, (VWAP.VWAP * cast(FLOAT ,Positions.SharesHeld)) AS AveragePosition FROM Positions JOIN VWAP ON Positions.Symbol = VWAP.Symbol;
The example creates an output window named ValueByBook, which uses SELECT and FROM clauses to pull data from IndividualPositions using book ID values. ValueByBook groups the data by book ID.
CREATE OUTPUT WINDOW ValueByBook SCHEMA (BookId STRING, CurrentPosition FLOAT, AveragePosition FLOAT) PRIMARY KEY DEDUCED AS SELECT IndividualPositions.BookId AS BookId, sum(IndividualPositions.CurrentPosition) AS CurrentPosition, sum(IndividualPositions.AveragePosition) AS AveragePosition FROM IndividualPositions GROUP BY IndividualPositions.BookId;
The example concludes by attaching a File XML Input adapter named Adapter1 to PriceFeed, and another File XML Input adapter named Adapter2 to Positions.