Imagine you need to build several financial calculators for your web app – let’s say one of them will calculate mortgage payments. Writing the code to account for all the variables takes time. Not to mention first, you need to know all the things like compound interest, PMI, HOA fees, etc.

Sounds like a lot of work, right? It is.

This makes me all the more excited to announce the release of HyperFormula, our high-performance calculation engine written in JavaScript, that’ll make it a piece of cake.

What is HyperFormula?

It’s an open-source calculation engine written in JavaScript for performing complex data calculations in web apps. HyperFormula uses a spreadsheet-like notation (A1), making it a familiar, easy-to-use tool for end-users. Plus it lets you cut down on development time and focus on other essential aspects of your app.

Its key features are:

• A high-performing calculation engine with minimum dependencies
• A built-in library of 100+ functions (and growing!)
• Easy custom function creation
• Support for Undo/Redo
• Support for Copy/Cut/Paste
• Efficient CRUD operations
• Dependencies between cells, both absolute and relative
• Multiple worksheets
• Named ranges
• Highly Customizable
• Compatible with OpenFormula (OpenDocument 1.2)
• Open source license

You can browse the source code on GitHub.

How it works

To give you a general idea of how you could use HyperFormula, take a look at the simple demo we created. The set of features it includes is just the tip of the iceberg.

Since there’s no UI in HyperFormula, we’ve added it just for the demo. 

Let’s use it to build something awesome!

HyperFormula includes a library of ready-made functions, so you can build your app quicker while still meeting the business requirements. And if you need more, you can enhance the engine with custom functions using the extensive API.

HyperFormula doesn’t impact your UI, so you’re not risking any CSS conflicts when implementing it in your app. Take a look at just a few examples:

📝  Smart documents

If you’re building a product like Quip, Notion, Airtable, or Smartsheet, combining real-time rich text editors with tools such as spreadsheets, slides, or chat, you can use HyperFormula as the formula execution runtime for the spreadsheet part.

🔢  Online calculators

With lots of built-in functions, you can use HyperFormula as a mortgage payment or stock return calculator (or really, any kind of calculator) under the hood of your app.

📵  Low connectivity apps

With all the calculations performed in-memory, HyperFormula helps design web apps for low bandwidth. 

▥  Data grid components

With HyperFormula, you can easily turn any data grid into a calculation machine where each record can be a result of complex calculations. Since it doesn’t force you to use a specific UI, you can use it with data grids like Handsontable, Kendo UI Grid, ag-Grid, Infragistics Data Grid Component, or Syncfusion Data Grid.

▦  Spreadsheet apps

If your goal is to build the next most popular spreadsheet app, HyperFormula is a perfect fit. It not only provides an extensive library of built-in functions, but also handles all complex operations like CRUD, undo/redo, and copy/cut/paste actions. The library doesn’t make any assumptions about your UI, so it’s entirely up to you what your spreadsheet will look like. 

How did we end up here?

In 2017 we partnered up with NavAlgo, a French-Polish company specializing in big data, AI, and machine learning, to enhance Handsontable with efficient, spreadsheet-like data calculations.

From left to right: Chris Spilka, Marcin Warpechowski, Sc.D. Adrian Kosowski, Warsaw 2018

We got an EU funding, and in October 2018, we started working on the “HandsOnEngine” project, which later turned into HyperFormula. 

Four stages and 15 months later, in December 2019, we had a fast calculation engine with spreadsheet syntax (A1 notation). We needed another five months to perfect the functionality and add new features like named ranges.

We wanted the engine to be compatible with the OpenFormula™ standard. That wasn’t easy, because the specification wasn’t complete and there were differences in how the standards are implemented across different apps we treated as benchmarks (e.g., Microsoft Excel, Lotus 1-2-3, LibreOffice Calc, Google Sheets, etc.)

What we did to optimize the performance

Our priority and the main theme of the research project was the engine’s performance. It went without saying that usability for the end-user depended on how fast the engine was. So at every stage of the project, we tracked the time required for processing the input and initializing data structures (building AST, calculation and recalculation of formulas, etc).

The data structure in the engine is based on a dependency graph. We also use multiple optimization methods like: 

• Graph of dependencies between cells
  • Succinct representation of dependencies between ranges.
  • Cycle detection and decomposition of the graph into strongly connected components.
• Smart recalculation after CRUDs
  • Recalculating only the cells that depend on the changed cell.
  • Lazy update of formulas (one-row insertion/deletion possibly modifies all formulas in the sheet).
• Efficient calculation of involved functions
  • Indexes for VLOOKUP.
  • Exploiting associativity property of range functions.
• Support for matrices
  • Recognition of number matrices.
  • GPU accelerated (GPU accelaration was available until HyperFormula 1.1.0) functions: MMULT / MAXPOOL / MEDIANPOOL.
• The compressed representation of data
  • Repeating / similar formulas are identified and not stored separately.
  • Different approaches for storing sparsely or densely filled sheets.
• Efficient processing of formulas
  • We check for repeating and similar formulas before parsing them.
  • Handling dates without external libraries.
• We also ran experiments like:
  • Using WebWorkers (branch) for simultaneous calculation of data in several branches of the dependency tree.
  • Using WebAssembly (branch) to increase the speed of formula evaluation.
  • Using GPU acceleration (available until HyperFormula 1.1.0) (plugins) to speed up calculations in matrix functions like MMULT.

In the end, only the GPU experiment proved useful so we implemented the results in several functions (GPU accelaration was available until HyperFormula 1.1.0). Other experiments didn’t yield the results we expected – but that’s something for another post.

Next steps

Right now we’re working on adding missing functions – and we need around 450 of them to compete with typical spreadsheet software. If you’re using HyperFormula, let us know how and what industry you’re in. It’ll help a lot!

The next step is completing the Handsontable integration – we know that a lot of people are waiting for it. 

Looking for contributors and partners

If you want to help us with creating the best calculation engine on the planet, learn more here.

Also, we’re open to working with technical and implementation partners, as well as resellers. Reach out to our sales team to talk about how we could work together.

Thank you

I want to thank everyone who participated in the project as well as those who helped and motivated us to keep going. 

Special thanks are due to the teams of NavAlgo: Bartek, Adrian (Sc.D.), Rafał, Zuzanna (PhD), Kuba, Przemek;

and Handsoncode: Wojciech, Ania, Natalia, Janek, Wojtek, Dariusz (Sc.D.), Budzio, Ola, Piotr, Piotrek and Marcin. 

Acknowledgments

We wouldn’t have made it without the co-financing from the European Regional Development Funds, a part of the Smart Growth Operational Programme. The project was carried out within the “Fast Track” support programme of the Polish National Centre for Research and Development.

• Name of the programme: Smart Growth Operational Programme 2014-2020
• Project name: “Development of the high-performance calculation engine for processing tabular data of the significant size on mobile devices and workstations using parallel computing and GPU.”
• Project number: POIR.01.01.01-00-0223/18-00
• Beneficiary: Handsoncode sp. z o.o.
• Total budget: PLN 1 121 375.00
• Amount of grant: PLN 774 742.00
• Duration: 2018-2019