An engine for automatic transformation of Excel models to JavaScript

A few years ago Digital Health Outcomes faced a need to develop an engine core which would be able to automatically transform Excel models with all their logic, formulas and dependencies to JavaScript. The reasoning was simple: instead of doing the whole transformation process every time for each model, it was enough to write the core once and then use it for all the subsequent case, thus significantly reducing development time.

The goal was clear and the challenge was accepted.

The following objectives were set:

  • Automate migration of complex health economics Excel models to the Web and Mobile Devices, such as iPad
  • Avoid intruding into a stable model core
  • Preserve 100% outcomes correlation
  • Achieve calculation performance which would be comparable to that of Microsoft Excel or exceed it
  • Be able to create state-of-the art graphical user interfaces based on the model core.

 The following background points were taken into account:

  • Existing web solutions, such as online spreadsheets [4, 5], are limited in their application for heavy Excel models. The model size is usually restricted by providers [4, 5] and the calculation time is rather high (Figure 5) that hinders users’ interaction with the model “live”
  • Reusing Microsoft Excel for the web applications violates the end-user licence agreement with Microsoft [6].
  • We propose a solution for the automated transformation of Excel models into self-contained JavaScript modules, which replicates the original model  and can be run in a web browser or on a server. JavaScript models can be used as a basis for the development of state-of-the art web and mobile applications. Our solution is generally applicable for Excel models; however, we additionally optimize it for the challenging case of heavy health economic Markov [1-3] models.

 Creating the proper methodology was the next step. Methodology consisted of the following points:

 Solution Architecture

The main components of an ordinary Excel model are: data (numbers, texts etc.), mark-up information, formulas and VBA code. Furthermore, an Excel model requires a runtime where the calculations can be actually executed. Microsoft Excel is such a runtime. Our solution transforms the components of an Excel model into the platform independent JavaScript code, which can be interpreted by a web browser of any desktop, laptop or mobile device. Instead of Microsoft Excel runtime, we use our custom-developed lightweight runtime, which is small enough to be included in the model itself, therefore making the model self-executable. The architecture of the solution is illustrated in Figure 1.

Figure 1: Solution Architecture
Figure 1: Solution Architecture

Model Migration Process

Figure 2 illustrates the migration process of an Excel model into a Web-based model
Figure 2 illustrates the migration process of an Excel model into a Web-based model.

Model deployment modes

 A web based model can be deployed in one of three modes (illustrated in Figure 3):

  • Client mode. Model is run completely on the client side and doesn’t require any server communication. This mode is used when:
  1. The model must function without internet connection
  2. Calculations must be carried out live (network latency is unacceptable)
  3. The model has to be delivered as a mobile app or standalone application.
  • Server mode. Model is run completely on the server side. This mode is used for extremely heavy models which are too big to be loaded and run on the client side.
  • Mixed mode. This mode is used for heterogeneous models which have both very heavy and very light calculations. Therefore, for sake of performance, heavy calculations are carried out on the server side, while the light ones are executed on the client.

Figure 3: JavaScript Model Deployment Modes
Figure 3: JavaScript Model Deployment Modes

 RESULTS

 Key Solution Characteristics

JavaScript model size. The final size of a JavaScript model is highly dependent on the optimization potential. Figure 4 below illustrates the ratio of the size of the original Excel model to the size of resulting JavaScript model. We take six models with different amount of cells and compare their sizes for each stage of model transformation:

  1. Excel model (both xls and xlsm) – the size of the original Excel file;
  2. JavaScript model API- non-optimized development-ready JavaScript model directly generated from the Excel file;
  3. Optimized JavaScript model – JavaScript model after optimization and obfuscation;
  4. Compressed JavaScript model – JavaScript model after compression;

Performance of the JavaScript model

Every Excel model consists of a set of cells and formulas which connect them. The performance of such model is determined by the following factors: cell access time and actual formula execution time. Markov models usually contain simple arithmetic formulas executed on a large amount (several hundreds of thousands) of interconnected cells. The formula execution time in such models is rather short; therefore the performance impact is determined by the amount of formulas executed and the number of cells accessed. In our benchmark we perform various arithmetic operations on sets of interconnected cells (chains) in a range of 1,000 to 200,000 cells. Speaking in terms of Markov models, 200,000 cells are equivalent to a model with 200 health states that evolve over 1,200 months. The results illustrated in Figure 5 were measured for the following devices: iPad 2 (iOS)[11], Asus TF700 tablet (Android)[12] and MacBook Air 2011 (MacOS) [10], and various web browsers.

 

Figure 4: The Comparison of Excel and JavaScript Model Sizes by Stages of Transformation and Number of Linked Cells
Figure 4: The Comparison of Excel and JavaScript Model Sizes by Stages of Transformation and Number of Linked Cells

Figure 5: The performance of JavaScript Model by Number of Interconnected Cells for Various Devices
Figure 5: The performance of JavaScript Model by Number of Interconnected Cells for Various Devices

 Other Solution Characteristics

  • Transformation speed. The model transformation is done automatically; therefore, the mathematical component (model core) of the web application is created within a few minutes.
  • No human intrusion into the model core. Upon the automated model transformation, the human factor, which can lead to introduction of new errors in the stable model core, is excluded.
  • Multiple end user delivery modes. Multiple model deployment modes allow for better tailoring the model performance to the user needs.
  • Platform independence. The resulting model is represented in JavaScript language. Programs written in this language can run practically on any software platform (Windows, Mac, Linux, iPad, Android etc.) and exhibit the performance similar to the fast low level programming languages such as C [7].
  • Model Specific Optimizations. We apply specific optimizations which improve the model performance and reduce its size. The key is to concentrate on visible parameters only and remove all unrelated data.
  • Security. JavaScript model run on the client side is obfuscated and substantially reduced in size. All texts and labels are removed. Variables are renamed which makes it almost impossible for humans to understand the semantics of the model. These code transformations make reverse engineering of the model core impossible with a reasonable effort. In extreme sensitive cases the code can be run on the server side. This approach hides the model core completely.
  • Customizability and extendibility. JavaScript models are supplied with the application programming interface (API) and can be deliberately customized. They can be enriched with the catchy user interface or integrated with online services and databases.
  • Scalability. In the “client” deployment mode all the calculations are moved to the client, thus unloading the server and allowing the support for the high number of concurrent users.
  • Offline capability. The JavaScript model can function without internet connection.

 Limitations:

  •  In the “client” deployment mode, some initial loading time is required. The loading time varies depending on an internet connection speed and the
  • A level of development and design is required to obtain a final web-based model

Evaluation

 We have evaluated our solution for a typical large Markov-chain based health economic Excel model:

  • The model was used to compare different treatment strategies and based on the Markov chains with age specific transition probabilities among 200 health states, that evolve over 1,200 monthly cycles. The model results were expressed as a number of accumulated clinical and economic outcomes as well as QALYs and Lys gained over a lifetime of a hypothetical cohort.
  • The model size – 14 MB (xlsx), 33.5 (xls); Number of sheets used – 35; Number of non-empty cells – 765, 433; Maximum length of an interconnected cell chain – 220025
  • We have transformed the model into JavaScript and run it in the Firefox[9] web browser of MacBook Air 2011[10]. In parallel, we have made the same Excel model accessible on the web with the help of native C++ Excel framework. We have compared the sizes and the performance of both models.
  • The size of the original Excel model was reduced from 13.3 to 1.72 MB;
  • The speed of the most heavy calculation was reduced from 0,185 to 0,15 seconds;

Next steps:

  1. Further optimize the engine, improve the performance and reduce the data size;
  2. Be able to easily integrate, accommodate or weave existing or new third-party solutions, such as spreadsheets or Excel;

Figure 6: Integrating Third Party Solutions
Figure 6: Integrating Third Party Solutions

  1. Discover additional model value through integrating the model core with additional services such as Location based services, Social media. Online Databases [13, 14] and other services.

CONCLUSIONS

  • With the proposed solution complex, Health Economics Markov models can be automatically transformed into platform-independent JavaScript models that can be further used to develop state-of-the-art iPad or Web based applications
  • Our benchmarks have demonstrated that JavaScript models are smaller in size and can demonstrate performance comparable to that of Microsoft Excel
  • The developed prototype evidences that it is possible to develop much more accessible and transparent user interfaces with means of HTML 5 and JavaScript

References

  1. Barnhart HX, Caldwell MB, Thomas P et al. Natural history of human immunodeficiency virus disease in perinatally infected children: an analysis from the Pediatric Spectrum of Disease Project. Pediatrics 1996; 97:710-716.
  2. Briggs A, Sculpher M. An introduction to Markov modelling for economic evaluation. Pharmacoeconomics 1998; 13:397-409.
  3. Michaels JA, Galland RB. Management of asymptomatic popliteal aneurysms: the use of a Markov decision tree to determine the criteria for a conservative approach. Eur J Vasc Surg 1993; 7:136-143.
  4. Zoho Webspreadsheet. https://sheet.zoho.com/features
  5. Google Docs support. http://support.google.com/drive/bin/answer.py?hl=de&answer=37603
  6. Microsoft support. http://support.microsoft.com/kb/257757/ru
  7. Programming language performance comparison. http://raid6.com.au/~onlyjob/posts/arena/.
  8. JSON home. http://www.json.org/
  9. Mozilla home. http://www.mozilla.org/de/firefox/fx/
  10. Apple Specifications. http://www.apple.com/macbookair/specs.html
  11. Apple Store. http://store.apple.com/de/buy/home/shop_ipad/family/ipad2
  12. Asus. http://www.asus.com/Tablet/Transformer_Pad/ASUS_Transformer_Pad_Infinity_TF700T/
  13. World Health Organisation database. http://www.who.int/library/databases/en/
  14. Unicef Statistics and Monitoring. http://www.unicef.org/statistics/index.html

Leave a Reply

Your email address will not be published. Required fields are marked *

ERROR: si-captcha.php plugin: GD image support not detected in PHP!

Contact your web host and ask them to enable GD image support for PHP.

ERROR: si-captcha.php plugin: imagepng function not detected in PHP!

Contact your web host and ask them to enable imagepng for PHP.