Tutorial: Using a Java class which fetches stock quotes from finance.yahoo.com

Download

The classes (and the spread sheets) can be downloaded here: Data Access.zip

The Java Classes

In this tutorial we write a Java class YahooQuoteFetcher that fetches and stores stock data from Yahoo. YahooQuoteFetcher constitutes a cache, thus preventing that every request will open a new connection to Yahoo. The class is constructed by the constructor YahooQuoteFetcher(double updateIntervalInSeconds);, where updateIntervalInSeconds is the number of seconds after which a quote will be considered outdated and need to be refetched.

The class provides a method

				public MarketDataBean getData(String symbol);
			

which returns an instance of MarketDataBean. Our second class, MarketDataBean is a data object containing the market data for a given symbol. It provides methods like public double getPrice(); and public double getVolume();.

Both classes are in the package info.obba.datatools.web.

Setting up Obba and the Java Runtime

If you have not already done so, install Obba. Since our object needs to open a socket connection to the Internet, we need to ensure that the Java Runtime used by Obba has the appropriate rights (java.policy). The rights are granted by adding the following to your java.policy file:

				grant {

				  permission java.net.SocketPermission "*", "connect,accept";

				};

(Note: It may be advisable to add a restriction to the codebase.)

See Obba Tipps on how to edit or create the Java policy file.

Creating a new Policy File in your %USERPROFILE% Folder

If you do not have a .java.policy file in your %USERPROFILE% folder, you can create one. If Windows does not let you create the file (since its filename has to start with a dot), then copy the file from the ZIP archive to your %USERPROFILE%.

Adding an additional Policy File

If you want to add an additional policy file having a different name or being in a different folder than %USERPROFILE%/.java.policy, then you need to add the following argument to the JRE: -Djava.security.policy=someURL where someURL is the path to the policy file (using /, not backslash). See also Java Policy File Syntax.

Integration into Excel or OpenOffice

Put all necessary function calls and parameters in the spreadsheet cells A1 to A8. Once you get the hang of this, you can be more creative with the layout of your sheet.

Set up a spreadsheet. In cell A1, place a call to Obba instructing it to load our Java code. If the Java code is in the folder lib (either as a JAR or as .class files), then this function call is =obAddAllJars("lib",TRUE).
In cell A2 we set up a check that the libraries are loaded. This is done by the function call =IF(ISTEXT(FA1),"",NA()). If the libraries are loaded, then the cell A2 should be empty (contain an empty string). Later, you will link all Java calls with this cell to ensure that libraries are loaded prior to any Java call.
In cell A3, write the parameter value of the constructor of YahooQuoteFetcher, for example 30.
In cell A4, make an Obba call that constructs a YahooQuoteFetcher object. The function call is =obMake("connection",A2&"info.obba.datatools.web.YahooQuoteFetcher",obMake("","double",A3)). If all went well, then cell A4 contains the object handle for the created object. In all later calls you will reference cell A4 when you reference that YahooQuoteFetcher object.
In cell A5, write the parameter value of the symbol for which you want to fetch market data, for example AAPL.
In cell A6, make an Obba call that calls the getData method on the YahooQuoteFetcher object. The return value will be a MarketDataBean object for our symbol. The function call is =obCall("data-bean",A4,"getData",obMake("","String",A5)). If all went well, then cell A6 contains the object handle for returned MarketDataBean object. In all later calls you will reference cell A6 when you reference that MarketDataBean object.
In cell A7, make an Obba call which calls the getPrice method on the MarketDataBean object. The return value will be a double. The function call is =obCall("price",A7,"getPrice"). The double is store under the object label "price". If all went well, then cell A7 contains the object handle for that double (the object handle has yet to be converted to a literal double value for Excel).
In cell A8 we make an Obba call that converts the price into its value. The function call is =obGet(A8). Cell A8 then contains the market data.
NOTE: It is possible to compose the two function and directly use the function call =obGet(obCall("",$F7,"getPrice")) in cell A7.

Demo

The Java code and (a similar) Excel sheet can be downloaded as Data Access.zip