This post shows the step by step guide on how to use Obba. Obba is a Java object handler for excel spreadsheet applications. Obba documentations are very good for the intermediate or advanced level. For the beginner or novice, we provide some useful examples for more clear understanding.
Introduction
We try to create objects for Java classes and call its member functions in Excel using Obba.
For this purpose, 1) we make a sample Java code and export it as a jar file and 2) use this jar file in Excel through Obba.
Installing Obba
If Obba was not installed previously, download and install it with default setting.
Sample Java Class
At first, we make a Java file which has a class definition with its members and member functions. The object generated from this class is used in Excel through Obba.
For this end, open Eclipse, create new Java Project, and add new Class. Names for our project and class are aObba and CObba respectively. Of course, you can change these names to your favorite names.
For the clear understanding, it doesn’t suffice to treat with only simple calculations. So we consider not only simple scalar examples but also vector or array related examples.
We write the following sample code to CObba.java file.
- CObba.java
//=========================================================================# // Financial Econometrics & Derivatives, ML/DL using R, Python, Tensorflow // by Sang-Heon Lee // // https://shleeai.blogspot.com //-------------------------------------------------------------------------# // How to use Obba (connection between Java and Excel) //=========================================================================# package aObba; public class CObba { public static double func1(double a) {return (a*a);} public static double func2(double a, double b) {return (a*b);} public static double[] func3(double[] a, double[] b) { double[] c = new double[a.length]; for(int i=0;i<a.length;i++) {c[i] = a[i]*b[i];} return (c); } public static void main(String[] args) { // scalar input & scalar output double a = func1(7); // scalar input & scalar output double b = func2(7,8); // array input & array output double[] c = {1,2,3,4,5,6,7,8,9,10}; double[] d = {2,3,4,5,6,7,8,9,10,11}; double[] e = func3(c,d); // print results System.out.println("func1 = " + a); System.out.println("func2 = " + b); System.out.println("func3 = "); for(int i=0;i<e.length;i++) { System.out.println(e[i]); } } }
Running CObba.java file results in the following correct outputs.
Eventually, our purpose is to call Java functions such as func1(), func2(), func3() and to see the above same results in Excel.
Export as a JAR file
Obba extracts information regarding classes from JAR file. Therefore, we should make a JAR file for our class.
Using Export menu in Eclipse, we can make the JAR file (aObba.jar) as the following figure.
Obba Commands in Excel
Now it is time to use Excel. In Excel file, write the following Obba commands to each cell as formula and save it to the same folder in which JAR file is located.
C2 = OBADDJAR("aObba.jar",TRUE) C5 = OBMAKE("Obba1","aObba.CObba") F10 = OBGET(OBCALL("",$C$5,"func1", OBMAKE("","double",C10))) F15 = OBGET(OBCALL("",$C$5,"func2", OBMAKE("","double",C15), OBMAKE("","double",D15))) F20 ={TRANSPOSE( OBGET(OBCALL("",$C$5,"func3", OBMAKE("","double[]",C20:C29), OBMAKE("","double[]",D20:D29))))}
Using the above Obba commands, we can see the following Excel file with same results.
Seeing the above figure and its formula in each cell, we can easily figure out the meaning of Obba commands.
- In C2, find and ready to use aObba.jar
- In C5, make an Obba1 object as an instance of class aObba.CObba.
- In F10, call a func1 (OBCALL) with single scalar input (OBMAKE) and get a result (OBGET).
- In F15, call a func2 (OBCALL) with two scalar inputs (OBMAKE) and get a result (OBGET).
- In F20, call a func3 (OBCALL) with two array inputs (OBMAKE) and get a result (OBGET) and the transpose for the vertical representation.
In particular, formula in cell F20 should be executed by using Ctrl+Shift+Enter since func3 function returns a vector or array,
Final Thought
The bottom line is that after finding a JAR file and making an instance of its class, use OBCALL for calling a function, use OBMAKE for passing input arguments, and use OBGET for getting a result.
From this post, we can learn how to use Obba with some examples of variety ,which are intended for some general usage.
Originally posted on SHLee AI Financial Model blog.
Disclosure: Interactive Brokers
Information posted on IBKR Campus that is provided by third-parties does NOT constitute a recommendation that you should contract for the services of that third party. Third-party participants who contribute to IBKR Campus are independent of Interactive Brokers and Interactive Brokers does not make any representations or warranties concerning the services offered, their past or future performance, or the accuracy of the information provided by the third party. Past performance is no guarantee of future results.
This material is from SHLee AI Financial Model and is being posted with its permission. The views expressed in this material are solely those of the author and/or SHLee AI Financial Model and Interactive Brokers is not endorsing or recommending any investment or trading discussed in the material. This material is not and should not be construed as an offer to buy or sell any security. It should not be construed as research or investment advice or a recommendation to buy, sell or hold any security or commodity. This material does not and is not intended to take into account the particular financial conditions, investment objectives or requirements of individual customers. Before acting on this material, you should consider whether it is suitable for your particular circumstances and, as necessary, seek professional advice.
Join The Conversation
If you have a general question, it may already be covered in our FAQs. If you have an account-specific question or concern, please reach out to Client Services.