Monitor the Stock Price"

From Documentation
m (correct highlight (via JWB))
 
(16 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 
{{ZKSpreadsheetEssentialsPageHeader}}
 
{{ZKSpreadsheetEssentialsPageHeader}}
 +
 +
 +
{{Deprecated|url=http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials}}
 +
 +
  
 
__TOC__
 
__TOC__
  
Here is a ''Stock Price'' monitoring system that uses the ZK Spreadsheet's onCellChange event and data push mechanism.
+
Here is a ''Stock Price'' monitoring system that uses the ZK Spreadsheet's onCellChange event and Book sharing mechanism<ref>{{ZSS EE}}</ref>.
 +
<references/>
  
 
===Purpose===
 
===Purpose===
Line 11: Line 17:
 
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.
 
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.
  
<tt>''monitorSheet''</tt>
+
<code>''monitorSheet''</code>
  
 
[[Image:stock-monitor.png]]
 
[[Image:stock-monitor.png]]
  
<tt>''dataSheet''</tt>
+
<code>''dataSheet''</code>
  
 
[[Image:stock-data.png]]
 
[[Image:stock-data.png]]
  
 
===How ZK Spreadsheet Do the Job===
 
===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.
+
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 user preset selling and buying price then trigger buying or selling via another Web service.
  
 
[[Image:stock-event.png]]
 
[[Image:stock-event.png]]
Line 26: Line 32:
 
===ZUML===
 
===ZUML===
 
<source lang="xml">
 
<source lang="xml">
<window apply="org.zkoss.zss.demo.test.StockComposer" width="100%" vflex="1">
+
<window apply="org.zkoss.zssessentials.cellaction.StockComposer" width="100%" vflex="1">
 
<spreadsheet id="stock"
 
<spreadsheet id="stock"
    src="/stock.xls"
 
 
    maxrows="200"
 
    maxrows="200"
 
    maxcolumns="40"
 
    maxcolumns="40"
Line 34: Line 39:
 
    width="100%">
 
    width="100%">
 
</spreadsheet>
 
</spreadsheet>
<vlayout id="message" height="200px" width="100%">
+
<vlayout id="message" height="200px" width="100%" style="overflow:auto">
 
</vlayout>
 
</vlayout>
 
</window>
 
</window>
Line 41: Line 46:
 
===Composer===
 
===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.
 
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.
<source lang="java" high="22,29,39,41">
+
<source lang="java" highlight="18,20,32,46,56,58">
package org.zkoss.zssessentials.cellaction.stock;
+
package org.zkoss.zssessentials.cellaction;
 
public class StockComposer extends GenericForwardComposer {
 
public class StockComposer extends GenericForwardComposer {
private StockUpdateService service;
+
private static Book book = null;
 +
private static StockUpdateService service;
 
private Vlayout message;
 
private Vlayout message;
 
private Spreadsheet stock;
 
private Spreadsheet stock;
private Sheet monitorSheet;
+
private Worksheet monitorSheet;
 
private int left;
 
private int left;
 
private int top;
 
private int top;
Line 55: Line 61:
 
public void doAfterCompose(Component comp) throws Exception {
 
public void doAfterCompose(Component comp) throws Exception {
 
super.doAfterCompose(comp);
 
super.doAfterCompose(comp);
service = new StockUpdateService(stock);
+
synchronized (StockComposer.class) {
 +
if (book == null) { //initialize the shared Book and Stock update service
 +
final Importer importer = Importers.getImporter("excel");
 +
final InputStream is = Sessions.getCurrent().getWebApp().getResourceAsStream("/WEB-INF/excel/cellaction/stock.xls");
 +
book = importer.imports(is, "stock.xls");
 +
book.setShareScope(EventQueues.APPLICATION); //share the work book in Application Scope
 +
service = new StockUpdateService(book);
 +
}
 +
}
 +
stock.setBook(book);
 
monitorSheet = stock.getSelectedSheet();
 
monitorSheet = stock.getSelectedSheet();
 
final Range priceRange = Ranges.range(monitorSheet, "price");
 
final Range priceRange = Ranges.range(monitorSheet, "price");
Line 63: Line 78:
 
bottom = priceRange.getLastRow();
 
bottom = priceRange.getLastRow();
 
}
 
}
public void onCellChange$stock(CellEvent event) {
+
public void onCellChange$stock(CellSelectionEvent event) {
final Sheet sheet = event.getSheet();
+
final Worksheet sheet = event.getSheet();
 
if (!monitorSheet.equals(sheet)) {
 
if (!monitorSheet.equals(sheet)) {
 
return; //not the monitorSheet, return
 
return; //not the monitorSheet, return
 
}
 
}
final int row = event.getRow();
+
final int eleft = event.getLeft();
final int col = event.getColumn();
+
final int etop = event.getTop();
if (left <= col && col <= right && top <= row && row <= bottom) { //in range
+
final int eright = event.getRight();
final Range priceRng = Ranges.range(monitorSheet, row, col);
+
final int ebottom = event.getBottom();
final Range sellRng = priceRng.getOffset(0, 3);
+
if (left > eright || eleft > right || top > ebottom || etop > bottom) {
final Range buyRng = priceRng.getOffset(0, 2);
+
return; //no intersection, return
final Range codeRng = priceRng.getOffset(0, -1);
+
}
final double newPrice = ((Number)priceRng.getValue()).doubleValue();
+
for (int row = etop; row <= ebottom; ++row) {
final double sellPrice = ((Number)sellRng.getValue()).doubleValue();
+
for (int col = eleft; col <= eright; ++col) {
final double buyPrice = ((Number)buyRng.getValue()).doubleValue();
+
if (left <= col && col <= right && top <= row && row <= bottom) { //in range
final String stockCode = (String) codeRng.getValue();
+
final Range priceRng = Ranges.range(monitorSheet, row, col);
if (newPrice <= buyPrice) {
+
final Range sellRng = priceRng.getOffset(0, 3);
buy(stockCode, priceRng);
+
final Range buyRng = priceRng.getOffset(0, 2);
} else if (newPrice >= sellPrice) {
+
final Range codeRng = priceRng.getOffset(0, -1);
sell(stockCode, priceRng);
+
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);
 +
}
 +
}
 
}
 
}
 
}
 
}
Line 99: Line 123:
  
 
[[Image:stock-result.png]]
 
[[Image:stock-result.png]]
 +
 +
View complete source of ZUML [http://code.google.com/p/zkbooks/source/browse/trunk/zssessentials/examples/WebContent/cellaction/stock.zul stock.zul]
 +
 +
View complete source of composer [http://code.google.com/p/zkbooks/source/browse/trunk/zssessentials/examples/src/org/zkoss/zssessentials/cellaction/StockComposer.java StockComposer.java]
  
 
=Version History=
 
=Version History=

Latest revision as of 12:57, 19 January 2022



Stop.png This article is out of date, please refer to http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials for more up to date information.


Here is a Stock Price monitoring system that uses the ZK Spreadsheet's onCellChange event and Book sharing mechanism[1].

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

Stock-monitor.png

dataSheet

Stock-data.png

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 user preset selling and buying price then trigger buying or selling via another Web service.

Stock-event.png

ZUML

<window apply="org.zkoss.zssessentials.cellaction.StockComposer" width="100%" vflex="1">
	<spreadsheet id="stock"
	    maxrows="200"
	    maxcolumns="40"
	    vflex="1"
	    width="100%">
	</spreadsheet>
	<vlayout id="message" height="200px" width="100%" style="overflow:auto">
	</vlayout>
</window>

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 org.zkoss.zssessentials.cellaction;
public class StockComposer extends GenericForwardComposer {
	private static Book book = null;
	private static StockUpdateService service;
	private Vlayout message;
	private Spreadsheet stock;
	private Worksheet monitorSheet;
	private int left;
	private int top;
	private int right;
	private int bottom;
	
	public void doAfterCompose(Component comp) throws Exception {
		super.doAfterCompose(comp);
		synchronized (StockComposer.class) {
			if (book == null) { //initialize the shared Book and Stock update service
				final Importer importer = Importers.getImporter("excel");
				final InputStream is = Sessions.getCurrent().getWebApp().getResourceAsStream("/WEB-INF/excel/cellaction/stock.xls");
				book = importer.imports(is, "stock.xls");
				book.setShareScope(EventQueues.APPLICATION); //share the work book in Application Scope
				service = new StockUpdateService(book);
			}
		}
		stock.setBook(book);
		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(CellSelectionEvent event) {
		final Worksheet sheet = event.getSheet();
		if (!monitorSheet.equals(sheet)) {
			return; //not the monitorSheet, return
		}
		final int eleft = event.getLeft();
		final int etop = event.getTop();
		final int eright = event.getRight();
		final int ebottom = event.getBottom();
		if (left > eright || eleft > right || top > ebottom || etop > bottom) {
			return; //no intersection, return
		}
		for (int row = etop; row <= ebottom; ++row) {
			for (int col = eleft; col <= eright; ++col) {
				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

Stock-result.png

View complete source of ZUML stock.zul

View complete source of composer StockComposer.java

Version History

Last Update : 2022/01/19


Version Date Content
     


All source code listed in this book is at Github.


Last Update : 2022/01/19

Copyright © Potix Corporation. This article is licensed under GNU Free Documentation License.