The PortfolioValuation project that you build in this tutorial applies
current prices to a portfolio of investments to compute the value of each investment and of
the portfolio. It uses simple queries to aggregate and join data from two input
windows.
The example:
- Receives a stream of prices in an input window called PriceFeed.
The schema for this window has five columns: Id, Symbol, Price, Shares, and
TradeTime. The window uses the Id field as a primary key, and is set to keep the
last 10 minutes of price events.
- Applies an Aggregate simple query to create a 10-minute moving
averageāa volume weighted average price (VWAP). With the VWAP, you can see the
value of positions based on the average price, rather than see the value of your
positions change with every small price movement. The VWAP formula is calculated
as:
sum(PriceFeed.Price *
PriceFeed.Shares) /
sum(PriceFeed.Shares)
- Reads data from another input window, Positions, with three
columns: BookId, Symbol, and SharesHeld.
- Applies a Join simple query, joining the market price (from the
VWAP aggregate) to your holdings (Positions), so that you can see the value of
your position in each stock:
FROM VWAP
RIGHT JOIN Positions
ON
VWAP.Symbol = Positions.Symbol
- Applies one more aggregation to show the total value of each
"book." This aggregate, ValueByBook, groups current and average values for
individual positions into different "books." Each book may comprise a set of
investment portfolios or funds. In the CCL, a GROUP BY clause performs the
aggregation:
CREATE OUTPUT WINDOW ValueByBook
PRIMARY KEY DEDUCED
AS
SELECT IndividualPositions.BookId BookId,
sum(IndividualPositions.CurrentPosition) CurrentPosition,
sum(IndividualPositions.AveragePosition) AveragePosition
FROM IndividualPositions
GROUP BY IndividualPositions.BookId ;
Portfolio Valuation Sample Diagram (Iconic Mode)