Monitor the Stock Price"

From Documentation
(Created page with '{{ZKSpreadsheetEssentialsPageHeader}} __TOC__ Here is a ''Stock Price'' monitoring system that uses the ZK Spreadsheet's onCellChange event and data push mechanism. ==Purpose=…')
 
m (correct highlight (via JWB))
 
(36 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 
{{ZKSpreadsheetEssentialsPageHeader}}
 
{{ZKSpreadsheetEssentialsPageHeader}}
  
−
__TOC__
 
  
−
Here is a ''Stock Price'' monitoring system that uses the ZK Spreadsheet's onCellChange event and data push mechanism.
+
{{Deprecated|url=http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials}}
  
−
==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.
 
  
−
[[Image:stock-template.png]]
+
__TOC__
  
−
==How ZK Spreadsheet Do the Job==
+
Here is a ''Stock Price'' monitoring system that uses the ZK Spreadsheet's onCellChange event and Book sharing mechanism<ref>{{ZSS EE}}</ref>.
−
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.
+
<references/>
  
−
[[Image:stock-event.png]]
+
===Purpose===
 +
Automatically place ''buy'' or ''sell'' command whenever the price of a selected stock reach some preset point.
  
−
===ZUML Example===
+
===Template Excel File with Proper Name Expressions===
−
<source lang="xml" high="5">
+
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.
−
<spreadsheet apply="demo.stock.StockComposer"
 
−
src="/stock.xls"
 
−
maxrows="200"
 
−
maxcolumns="40"
 
−
vflex="1"
 
−
width="100%">
 
−
</spreadsheet>
 
−
</source>
 
  
−
===Stock Composer===
+
<code>''monitorSheet''</code>
−
This is the controller that handle the onCellChange event.
 
−
<source lang="java" high="17">
 
−
package demo.stock;
 
−
public class StockComposer extends GenericForwardComposer {
 
  
−
}
+
[[Image:stock-monitor.png]]
−
</source>
 
  
−
==Result==
+
<code>''dataSheet''</code>
  
−
[[Image:bsheet-result.png]]
+
[[Image:stock-data.png]]
  
−
==When Value of Java Bean Changes==
+
===How ZK Spreadsheet Do the Job===
−
Will ZK Spreadsheet update the cells if the value of the Java bean changes? Well, if you notify it.
+
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.
−
<source lang="java">
 
−
bsheet.getBook().notifyChange(new String[] {"dataBean"});
 
−
</source>
 
−
When you notify the ZK Spreadsheet work book model  that beans with the specified names have changed, it will collect which cells are affected (i.e. those dependent cells with the specified bean names), and publish data change event accordingly. The ZK Spreadsheet UI or any other components that have subscribed to the work book's event queue and are interested in the event will then retrieve the new cells' data from the beans and update themselves.
 
  
−
=Integrate ZK Spreadsheet with Spring=
+
[[Image:stock-event.png]]
−
As we have mentioned in [[#How ZK Spreadsheet Resolve my Java Beans]], ZK Spreadsheet follows ZK's EL expression variable resolving mechanism. When trying to resolve a variable, it will finally ask variable resolvers declared in the ZUML page to retrieve the bean of the named variable. So simply declare the <tt>variable-resolver</tt> with <tt>org.zkoss.zkplus.spring.DelegatingVariableResolver</tt> on top of your ZUML page, you are  now data-binding your ZK Spreadsheet with your Spring-Managed beans directly. Following we demonstrate the example that use the same bsheet.xls template file as in [[#Template Excel File with Proper Name Expressions]] but data-binding to Spring-Managed bean.
 
−
 
−
===ZUML Example===
 
−
In ZUML page, we declare the variable resolver for retrieving Spring-Managed beans so ZK Spreadsheet can find them and show the evaluated value in the spreadsheet's cells.
 
−
 
−
<source lang="xml" high="1">
 
−
<?variable-resolver class="org.zkoss.zkplus.spring.DelegatingVariableResolver"?>
 
−
<spreadsheet id="bsheet"
 
−
    src="/bsheet.xls"
 
−
    maxrows="200"
 
−
    maxcolumns="40"
 
−
    vflex="1"
 
−
    width="100%">
 
−
</spreadsheet>
 
−
</source>
 
  
−
===Spring Configuration File===
+
===ZUML===
−
Define bean definitions in <tt>applicationContext.xml</tt> file, and put it into your <tt>WEB-INF</tt> directory.
+
<source lang="xml">
−
 
+
<window apply="org.zkoss.zssessentials.cellaction.StockComposer" width="100%" vflex="1">
−
<source lang="xml" high="5">
+
<spreadsheet id="stock"
−
<?xml version="1.0" encoding="UTF-8"?>
+
    maxrows="200"
−
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
+
    maxcolumns="40"
−
+
    vflex="1"
−
<beans>
+
    width="100%">
−
  <bean id="dataBean" class="demo.springbean.DataBeanImpl"/>
+
</spreadsheet>
−
</beans>
+
<vlayout id="message" height="200px" width="100%" style="overflow:auto">
 +
</vlayout>
 +
</window>
 
</source>
 
</source>
  
−
===Spring Bean Class===
+
===Composer===
−
Define DataBean interface and its implementation. Mainly getters and setters for various attributes for a balance sheet.
+
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" highlight="18,20,32,46,56,58">
−
<tt>''DataBean.java''</tt>
+
package org.zkoss.zssessentials.cellaction;
−
 
+
public class StockComposer extends GenericForwardComposer {
−
<source lang="java" high="3">
+
private static Book book = null;
−
package demo.springbean;
+
private static StockUpdateService service;
−
public interface DataBean {
+
private Vlayout message;
−
public double getLiquidAssets();
+
private Spreadsheet stock;
−
 
+
private Worksheet monitorSheet;
−
public void setLiquidAssets(double liquidAssets);
+
private int left;
−
 
+
private int top;
−
//...other getters and setters
+
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);
 +
}
 
}
 
}
 
</source>
 
</source>
  
−
<tt>''DataBeanImpl.java''</tt>
+
===Result===
  
−
<source lang="java" high="17">
+
[[Image:stock-result.png]]
−
package demo.springbean;
 
−
public class DataBeanImpl implements DataBean {
 
−
private double liquidAssets;
 
−
private double fundInvestment;
 
−
private double fixedAssets;
 
−
private double intangibleAsset;
 
−
private double otherAssets;
 
−
private double currentLiabilities;
 
−
private double longTermLiabilities;
 
−
private double otherLiabilities;
 
−
private double capitalStock;
 
−
private double capitalSurplus;
 
−
private double retainedEarnings;
 
−
private double otherEquity;
 
−
private double treasuryStock;
 
−
 
−
public double getLiquidAssets() {
 
−
return liquidAssets;
 
−
}
 
  
−
public void setLiquidAssets(double liquidAssets) {
+
View complete source of ZUML [http://code.google.com/p/zkbooks/source/browse/trunk/zssessentials/examples/WebContent/cellaction/stock.zul stock.zul]
−
this.liquidAssets = liquidAssets;
 
−
}
 
  
−
//...other getters and setters implementation
+
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]
−
}
 
−
</source>
 
  
 
=Version History=
 
=Version History=

Latest revision as of 12:57, 19 January 2022


DocumentationZK Spreadsheet EssentialsWorking with ZK SpreadsheetListen to Cell Changes and ActionMonitor the Stock Price
Monitor the Stock Price




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].

  1. ↑ Available in ZK Spreadsheet EE only

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.


Retrieved from "https://www.zkoss.org/_w/index.php?title=ZK_Spreadsheet_Essentials/Working_with_ZK_Spreadsheet/Listen_to_Cell_Changes_and_Action/Monitor_the_Stock_Price&oldid=53650"