1

QuickSMA – A Stock Market Analyzer for Open Office Calc
Pavalanathan Priyanthan priyanthanuom@gmail.com
Department of Computer Science &Engineering, University of Moratuwa, Sri Lanka.  Abstract— OpenOffice application suite is gaining popularity after the introduction of a more improved version. The supremacy of open source software relies on its support for vast amount of extensibility and customization. OpenOffice.org provides such support through Extensions - which include Components, Add-ins and Add-Ons. This paper is a study of the process of developing an Add-On for OpenOffice Calc which is aimed towards analyzing stock market data. Index Terms— Optimization, Stock markets, Share prices

Further currently there are no such extensions or features exist in OpenOffice Calc regarding stock market analysis or option pricing. But there are sophisticated software available for this kind of purposes and also few companies have developed some Add-Ins for Microsoft Excel. This extension is costless and is easy to use; hence will be useful for those who are related to stock exchange especially to the investors, investment advisors and option writers. The initial requirement was further enhanced to create an Add-On which allows the users to do the following: use the downloaded stock price data to perform Technical Analysis using seven important Technical Analysis indicators such as MACD, RSI, Aroon Oscillator, Stochastic, OBV, ADX and Simple moving average, generate charts for each indicators for more accurate analysis and price Options using Binomial, Black, Black Scholes and Merton model.

I. INTRODUCTION OpenOffice, is an open-source application suite whose main components are for word processing, spreadsheets, presentations, graphics, and databases[1]. It is available for a number of different computer operating systems and distributed as free software and is written using its own GUI toolkit. As an Open source Software, nowadays OpenOffice is gaining popularity in many countries especially in developing countries where people find financially difficult to buy commercial software. An OpenOffice extension is anything that extends its functionality beyond the core. As an open source application suite OpenOffice.org allows and promotes people to develop independent 3rd party extensions. The extensions can range from Basic libraries, packages with Java/JavaScript or Python macros to more sophisticated extensions in the form of UNO components implemented in Java, C++ or Python [2]. Open Office extensions allow us to extend the basic functionality and construct it more suited to our needs, better looking and more prolific. This extension for Open Office Spread sheet –OpenOffice Calc, is aimed towards analyzing stock price movements and pricing options using different theoretical models. The main concern of this project is to have a basic stock price data analyzing functionality with standard user friendly interface which is similar to the existing OpenOffice GUI. This idea emerged because, today the trend is, more and more people are investing in stock market and they require a simple, less costly and user-friendly software that could be used to analyze the past price movements in order to predict the future price movements.

II. LITERATURE REVIEW As in March, 2011, OpenOffice.org repository for extensions list 627 extensions [3]. This shows the power of extensions in providing useful support to OpenOffice users. Wikipedia states that “Currently Calc does not have any addins for real-time stock quotes. However, live streaming financial data can be provided with two commercial packages for Mathematica, CalcLink and DDFLink.”[4] So this application gives a whole new feature to Open Office calc. However this section will review the existing software which substitute this extension
TABLE I EXISTING WELL KNOWN SOFTWARE

Name AnalyzerXL OptionsXL TraderStar AmiBroker

Type Add-in for Microsoft Excel Add-in for Microsoft Excel Software Software

2  Framework: The framework provides many high level functions to the OpenOffice.org application modules. It's the glue between the hardware abstraction layers and the application modules [7]. Utilities, Common GUI, BASIC and Drawing Layer The bottom tier consists of:  Analyzing the above software [5], reveals that the first two items in Table I are costly and it could only be used with Microsoft Excel. TradeStar is a commercial software which has most of the features other than broker interface and alerts. AmiBroker is a sophisticated costly software which is more powerful than the others mentioned here. Statmetrics is an open source software but it lacks some major functionalities. MetaStock and CarryQuote has the same functionalities. III. DESIGN AND IMPLEMENTATION A. Open Office Overall Architecture  UNO: UNO is the central concept in Open Office, because it manages all the OpenOffice.org internal objects. That is UNO provides a way to call other objects without taking care of their implementation language or their execution environment. UCB: The Universal Content Broker (UCB) allows the application layer components to transparently access content with different structures, including the associated Meta information to the content. The UCB contains a core and several Universal Content Providers (UCP) [8]. GSL: The Graphic System Layer (GSL) project is an umbrella project to provide graphic output on different devices. Graphic output refers to a range of operations starting from simple line drawing to complex raster operations Config Manager: The Configuration Manager has a similar ability as the UNO Service Manager; it can scan a set of folders for data and puts them all together.

Statmetrics MetaStock CarryQuote

Free software Software Software







B. Open Office Components or Extension Architecture Extensions developed for Open Office should be integrated to Open Office via UNO (Universal Network Objects) bridge. An externally developed component should satisfy UNO specifications in order to be able to plug it to OpenOffice via UNO Bridge. Specifications in UNO are a set of methods and properties defining an API. Thus QuickSMA extension too follows UNO specifications. Fig.2, below depicts how the UNO runtime is linked to the QuickSMA extension.

Fig. 1. Open Office overall architecture

As shown in Fig.1, OpenOffice has been separated into a three tiered stack in order to understand the structure clearly and allows developers to modify the different layers as necessary [6]. The three tiers are namely,  Top layer   Middle layer Bottom layer

The top tier consists of the applications of OpenOffice, which include: Calc, Impress, Base, Writer, IDE, Math and Wizards. The middle tier consists of:  i18n: This enables OpenOffice applications to work with different localizations.
Fig. 2. QuickSMA linkage to UNO runtime environment

3

The conceptual architectural framework of the QuickSMA extension is comprised of two layers namely,  User Interface layer  Application Logic layer C. Architectural Goals: Extensibility The major objective of the OpenOffice architectural framework is to support for extensions. Since this product is developed as an extension for OpenOffice, the development should conform to the component part of OpenOffice architecture. Further having a two layer conceptual architectural framework for QuickSMA extension, facilitate further extensibility. D. Development Process method and Architectural Representation Rational Unified Process (RUP) has been used to develop and implement the project. The 4+1 view model of architecture was used in the implementation process. Since the RUP process method has been used and RUP uses 4+1 view model, the same model was used for the architectural representation. E. Implementation At first the UNO component and UNO bridge have been built to have the communication with the Open Office core. The UNO Packages can be written in any programming language .But this particular extension is developed in Java using NetBeans IDE. The OpenOffice.org API plugin for NetBeans simplifies accessing and using of the API in new projects and the task of creating complete OpenOffice.org extension packages. Thus the use of this plugin reduces the complexity of building this extension. For this extension development OpenOffice.org Add-On Project Type has been used as this project type creates an adapted J2SE class library project with special support for a UNO component library and an OpenOffice.org extension package [9]. The wizard collects the necessary information for an OpenOffice.org Add-On component and generates an AddOn specific code skeleton. During the next stage, the business logic has been implemented. For the implementation of Option pricing, the algorithms and equations are translated in to a code. However Binomial Option pricing model was somewhat complex thus further research have been carried out until a simplified solution was found. However initial research pointed out that implementing Technical Analysis was difficult due to the complex nature of some the indicators implemented. So the solution was to reuse the existing components, code base or libraries. The choice was the use of TA-Lib, which is widely used by trading software developers requiring to perform technical analysis of financial market data [10]. TA-Lib was chosen because it includes 200 indicators such as ADX,

MACD, RSI, Stochastic, and Bollinger Bands and has a proper documentation so that both facilitate the architectural goal of high extensibility. The difficult part of this extension development was getting the data values from the cell. The first step is getting the selected range (range is selected by click and dragging the cursor). Then the values should be retrieved from the selected range in order to perform the required calculations. For the purpose of getting the selected range XRangeSelection and ExampleRangeListener has been used. XCellRange, XCellRangeAddressable and XCell interfaces and getCellRangeByName, getRangeAddress, getValue and getCellByPosition methods has been used to implement the function of getting the data from the cell [11]. Further the calculated Technical Analysis indicator value has also been plotted in a graph. This is done using XInterface, XSpreadsheets, XSpreadsheetDocument , XIndexAccess, XSpreadsheet, XTableChartsSupplier, XTableCharts, XChartDocument, XShape and XPropertySet interfaces. After all these have been implemented the intended outcome was achieved.

IV. RESULTS The final result of the project was an .OXT file. The extension can be installed in OpenOffice.org as an .OXT file, where the user only has to add the .OXT file of the extension to the extension manager. After the addition it displays two icons in the tool bar as well as a new menu in the menu bar. This research effort of OpenOffice extension development has resulted in success. Fig.3 shows how to install the extension .Fig.4 and Fig.5 shows the outcome QuickSMA extension installed and configured in OpenOffice.

Fig. 3. Installation of QuickSMA.oxt

4   Artificial intelligence optimization of trading systems, list of rules, ranking systems and neural network prediction items Build trading systems using rules, ranking systems, composites, neural network models, money management techniques, and optimize the whole. VI. CONCLUSION The effort of creating a basic stock market analyzer was successful. If the proposed improvements mentioned in the earlier section have been carried out, we could have a proper sophisticated extension that would cater diverse range of users. The extension was built following a standard development approaches and has been installed in several computers and rigorously tested. The results were promising as users with basic knowledge about stock market could use it effortlessly without any training. ACKNOWLEDGMENT P.Priyanthan would like to thank Dr.Shahani Markus Weerawarana, Dr.Shehan Perera, Dr.Mahendrarajah Piraveenan, Mr.Chulaka Gunasekara and Ms. Sachini Weerawardhane, lecturers in-charge of this project, for their support, guidance and instructions. Sincere appreciation to OpenOffice.org community for their guidance and support during this development work. REFERENCES
[1] OpenOffice.org [Online]. Available: http://en.wikipedia.org/wiki/OpenOffice.org[Accessed: Mar.08, 2010] [2] OpenOffice.org Extensions [Online]. Available: http://wiki.services.openoffice.org/wiki/Extensions [Accessed: Mar.08, 2010] [3] Extensions [Online]. Available: http://extensions.services.openoffice.org/ [Accessed: Mar.08, 2010] [4] OpenOffice.org Calc [Online]. Available: http://en.wikipedia.org/wiki/ OpenOffice.org_Calc [Accessed: Mar.09, 2010] [5] Feature comparison of products [[Online]. Available: http://en.wikipedia.org/wiki/Technical_analysis_software [Accessed: Mar.09, 2010] [6] Architecture [Online] .Available: http://wiki.services. [Accessed: Mar.10, 2010] openoffice.org/wiki/Architecture [Accessed: Mar.10, 2010] [7] Framework [Online] .Available: http://wiki.services.openoffice. org/wiki/Framework [Accessed: Mar.10, 2010] [8] UCB [Online] .Available: http://ucb.openoffice.org/ [Accessed: Mar.10, 2010] [9] OpenOffice NetBeans Integration [Online] .Available: http://wiki. services.openoffice.org/wiki/OpenOffice_NetBeans_Integration [Accessed: Mar.11, 2010] [10] TA-Lib : Technical Analysis Library [Online] .Available: http://talib.org/ [Accessed: Mar.12, 2010] [11] OpenOffice.org 2.3 Develper’s Guide ,Sun Microsystems, Inc,2007. pp 34-50,831-730,793-810

Fig. 4. Option Pricing Wizard

Fig. 5. Technical Analysis Wizard

V. FUTURE WORK This project development work can be extended to have the following additional functionalities and improvements.          Adding more Technical Analysis indicators Portfolio Optimization feature Analysis of the total portfolio and its returns against its buys and as against its sells, which are later compared to all the transactions made Maintenance of accounts of different derivative instruments or currencies Pricing other derivatives Comparison reports of important averages on the available shares per price transactions Comparison of your gains and losses in all the accounts of the portfolio Download data for hundreds of tickers simultaneously Automatically apply or refresh Technical Analysis indicator graphs