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:

 The following background points were taken into account:

 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):

  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.

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

 Limitations:

Evaluation

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

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

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
Oleksandr Topachevskyi

Oleksandr Topachevskyi

FOUNDER / HEALTH ECONOMIST

Oleksandr is a health economist with a strong background in economic evaluations of healthcare programmed and health economics modelling, epidemiology, statistics, patient reported outcomes data collection and software solutions development. From 2009 to 2014 he worked at in GlaxoSmithKline Vaccines HQ at a position of a global health economics manager.

More about author

Leave a Reply

Your email address will not be published.