ZK Spreadsheet Essentials/Working with ZK Spreadsheet/Listen to Cell Changes and Action/Monitor the Stock Price

Here is a Stock Price monitoring system that uses the ZK Spreadsheet's onCellChange event and data push mechanism.

Purpose

Automatically place buy or sell command whenever the price of a selected stock reach some preset point.

Template Excel File with Proper Name Expressions

Here is an Excel template file with monitorSheet and dataSheet. monitorSheet shows the selected stocks that refer to the dataSheet with formulas. dataSheet is a sheet that list all available stocks.

monitorSheet

dataSheet

How ZK Spreadsheet Do the Job

Assume a stock price service will keep on pumping in updated stock price into the dataSheet on a separate thread. Whenever a new price is updated into dataSheet, ZK Spreadsheet will trigger the cell change event on monitorSheet that refer to the dataSheet. The onCellChange event listener registered on the ZK Spreadsheet will be called and check the prices . It compairs new price with preset selling price and buying price then notify buying or selling via another Web service.

ZUML Example

<window apply="org.zkoss.zss.demo.test.StockComposer" width="100%" vflex="1">
	<spreadsheet id="stock"
	    src="/stock.xls"
	    maxrows="200"
	    maxcolumns="40"
	    vflex="1"
	    width="100%">
	</spreadsheet>
	<vlayout id="message" height="200px" width="100%">
	</vlayout>
</window>

Stock Composer

This is the controller that handle the onCellChange event. The key method is the onCellChange$stocck() event listener. Whenever an onCellChange event is triggered, this method will be called and pass through with a CellEvent event. The event listener then compare if the cell changed is in the price column range and check if the price achieve buy or sell price setting by end user.

package demo.stock;
public class StockComposer extends GenericForwardComposer {
	private StockUpdateService service;
	private Vlayout message;
	private Spreadsheet stock;
	private Sheet monitorSheet;
	private int left;
	private int top;
	private int right;
	private int bottom;
	
	public void doAfterCompose(Component comp) throws Exception {
		super.doAfterCompose(comp);
		service = new StockUpdateService(stock);
		monitorSheet = stock.getSelectedSheet();
		final Range priceRange = Ranges.range(monitorSheet, "price");
		left = priceRange.getColumn();
		top = priceRange.getRow();
		right = priceRange.getLastColumn();
		bottom = priceRange.getLastRow();
	}
	public void onCellChange$stock(CellEvent event) {
		final Sheet sheet = event.getSheet();
		if (!monitorSheet.equals(sheet)) {
			return; //not the monitorSheet, return
		}
		final int row = event.getRow();
		final int col = event.getColumn();
		if (left <= col && col <= right && top <= row && row <= bottom) { //in range
			final Range priceRng = Ranges.range(monitorSheet, row, col);
			final Range sellRng = priceRng.getOffset(0, 3);
			final Range buyRng = priceRng.getOffset(0, 2);
			final Range codeRng = priceRng.getOffset(0, -1);
			final double newPrice = ((Number)priceRng.getValue()).doubleValue();
			final double sellPrice = ((Number)sellRng.getValue()).doubleValue();
			final double buyPrice = ((Number)buyRng.getValue()).doubleValue();
			final String stockCode = (String) codeRng.getValue();
			if (newPrice <= buyPrice) {
				buy(stockCode, priceRng);
			} else if (newPrice >= sellPrice) {
				sell(stockCode, priceRng);
			}
		}
	}
	private void buy(String stockCode, Range priceRng) {
		//stockService.buy(stockCode, price);
		new Label("Buy "+stockCode+" at price: "+priceRng.getText()).setParent(message);
	}
	private void sell(String stockCode, Range priceRng) {
		new Label("Sell "+stockCode+" at price: "+priceRng.getText()).setParent(message);
	}
}

Result

Version History

Version	Date	Content

All source code listed in this book is at Github.