Architecture

All computations in the Excel applications are performed on the java server xlloop-finance.jar file and then the results are transferred and displayed in Excel. The calls to the server are performed using functions that are registered in Excel and can be used in exactly the same way as any standard Excel function. The xlloop addin registeres all server functions in Excel including the function (i) category; (ii) name; (iii) description (help); and arguments description (arg help). The information can be accessed in Excel using Formulas> Insert Function menu option. An illustration of Excel function help window is illustrated below (the example illustrates how to create a java list object from an Excel data range).



In terms of Model-View-Controller (MVC) architecture, a typical design of an application is described as follows.

• Model. Statistical or financial modelling is performed in java. The project uses standard open-source packages, such as Jama used for matrix calculations and developed by the MathWorks and NIST (Jama), apache.commons.math3 for special statistical functions calculations (the package also implements matrix calculations, optimization and other algorithms, http://commons.apache.org/proper/commons-math/), etc. The details of the third-party java projects and frameworks, used in this project, and the frameworks developped within this project can be found here.


• View. The modelling results are presented in Excel, which is used as application interface. The model is estimated by running Excel user-defined functions (UDF), which are executed on the java side and the result is returned to Excel. The result is generally returned either as an object or an array. Therefore by connecting Excel (as an interface) to java (as a model), the architecture effectively allows to use object-oriented programming in Excel. The array output is presented using Excel array functions (which are input using Ctrl+Shift+Enter key combination). The list of available UDF functions is provided in server-functions.pdf and is generated in server-functions.xlsm


• Controller. The role of controller is to connect together the model and the interface. The role is performed by XLLoop Excel addin (developped in XLLoop open source project, http://xlloop.sourceforge.net). The addin executes each Excel UDF function by sending function-related request, processing request in java, and returning output to Excel. The request is send either (i) to the localhost (in which case the java application must be running on the local machine) or (ii) to alexacomputing.com (in which case user is only required to add the XLLoop to the list of Excel addins).

Shematically the MVC architecture is illustrated in the diagram below.

To summarize, under the architecture Excel is integrated directly with java. From my experience, the approach produces very user-friendly applications and for that reason I am using as a default option for any new developped application. However the design is not necessarily the best option for each case. Other options are also available.

Originally java applications in this project had java interface. To make java inteface coding a more efficient process, a direct coding was replaced with a framework, which is described in more detail in [link]. The framework (i) uses Spring factory classes to assemble application interface; (ii) uses xml configuaration files to describe interface elements (since xml syntaxis is still sufficiently complex and redundant, a simplified syntaxis was also developped for interface configuration files); (iii) uses a custom application-building framework to link different application components and application actions together efficiently. Java server application, which is part of XLLOOP model, was implemented using the above framework.

Java interface however is generally requires too complex programming to construct efficiently a user-friendly application. The next step in the project evolution was to run the project in java and then save all selected tables into an Excel file. The integration with Excel is performed using docx4j package ( https://www.docx4java.org). The output can be written in macros-enabled Excel files so that Excel is effectively used as an interface. The disadvantage of this approach compared to the current approach is that Excel has to be restarted each time when the parameters of the application are updated. Current architecture provides a more seamless integration between the model and the interface.

The applications are build on certain java frameworks. Therefore the project can be beneficial not only for those interested in using the specific applications from this website but also for thoise who want to leverage from the source code and integrate it in their own projects.