Portfolio Valuation

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.