When NumeRe is the Better Spreadsheet

Spreadsheets (Microsoft Excel & Co.) are used for all kinds of tasks. They often stand out due to their simplicity and flexibility. However, if you leave the terrain of "everyday calculations", you soon reach the point where such applications are no longer practical. In this article, we want to look at the aspects in which NumeRe suddenly has the edge and why it is worth taking a closer look at NumeRe's "slightly different" approach.

Is this shameless self-promotion? Only in part, because the arguments can be applied to any solution that moves in the gap between "simple" spreadsheets and "real" programming languages.

Fundamentals

Before we get into the subject matter, we would like to make it clear, especially for those who are not familiar with NumeRe, that NumeRe is not a direct replacement for spreadsheets and also has a very different operating concept. Whereas in spreadsheets most of the logic is part of the table itself (in the form of formulas that are assigned to cells), logic and data are separate in NumeRe: the logic is in a script or one or more procedures, the data is managed in tables, which are themselves pure data structures. Working in NumeRe therefore has a much clearer focus on structured programming.

For an initial comparison of how to solve a simple problem in Excel and NumeRe, we have already published an article in the past that we would like to recommend to all newcomers.

Better tool support during development

The need for good support from the tools used is usually only noticed quite late in spreadsheets. The right programming tools are worth their weight in gold, especially when it comes to troubleshooting or when you want to modify the created solution again at a later date. Without them, such situations can turn into real nightmares.

Debugger for troubleshooting

Of course, you can also execute functions step-by-step in Excel to isolate errors, but the support for proper status monitoring and jumping within the call stack is more than limited.

A proper debugger makes this task much easier. In NumeRe you can set breakpoints at which the debugger stops and lists the current values of the variables. You can also assign conditions to these breakpoints so that they are only triggered if their conditions are fulfilled.

Version control and diff viewer

Have you ever had the problem that after several changes to the logic, suddenly nothing works anymore? And then older versions are no longer available due to the lack of a cloud connection?

That can't happen with NumeRe. Each save creates a new version of the file in the local version management. You can compare the changes between any two versions directly (using a "unified diff") or restore older versions. But that's not all: you can also annotate versions (using "tags") so that you can quickly find them again in the revision overview. For example, after a completed part of the development, such a version could be provided with a corresponding comment.

Documenting logic through comments

Of course, we don't mean that you write comments in any cells of the spreadsheet, which may then be confused with data, but real code comments that can add thoughts and ideas to the associated code block so that someone other than the author understands what you were thinking. Those comments may partly be converted into a PDF documentation.

Refactoring

Refactoring? This refers to the revision of existing solutions if the code quality no longer meets the standards after development. An example of this would be very long and confusing code segments. NumeRe offers the option of extracting marked segments into your own procedures and thus making the code clearer again. In addition, variables can be safely renamed with a simple function to emphasize their purpose.

Static code analyzer (Linter)

A static code analyzer can detect problems and optimizations without the code having to be executed. Of course, there is no guarantee that the code will run without errors if no problems are found. However, it can help to avoid typical problems.

Architectural advantages

The "different nature" of NumeRe's mode of operation results in advantages that are particularly evident with more complex problems, but which can become almost tedious with simple problems. The aforementioned separation of data and logic, which requires significantly more programming than spreadsheets, should be emphasized here. At the same time, however, this also allows significantly more freedom and improved control over the current status of the software.

Separation of data and logic

One of the biggest problems in advanced spreadsheet solutions is that both data and program logic are managed in the same entity (the spreadsheet). This may not seem like a big problem at first, but it prevents the logic from being easily portable to other problems.

In NumeRe, data is managed in so-called tables, which are pure data storage. It is not possible to assign formulas to columns, rows or individual cells as in Excel, which might be automatically executed when values have changed. Each operation must be carried out by separate code in a script or procedure.

No accidental modification of data

Because data and logic are so inextricably linked in spreadsheets, it can easily happen that when the logic is modified, the data is also changed without you realizing it. All too quickly you select the wrong cell and have typed something.

This cannot happen in NumeRe.  Code is managed in different files than data and even if you modify loaded data, this is not automatically mirrored in their source files. You have to do this explicitly.

Higher level of abstraction

In NumeRe, solutions are developed separately from the data. While you will look at the data in parallel to select possible approaches, you will never develop directly on the data. In fact, most solutions will involve the following three steps:

The individual steps can vary in complexity and scope, but it is important that the data only comes into play during code execution and not during development. This automatically results in solutions with a higher level of abstraction, which tend to be applicable to more different use cases.

Re-use

With the higher level of abstraction, the aspect of reusability of already developed solutions also comes into play. You will find that your solutions become more abstract and reusable with increasing experience, so that you will soon have a whole library of solutions tailored to your problems.

Advanced features

What sets NumeRe apart from conventional spreadsheets are the advanced algorithms for Fourier transforms, differential equations and fitting, for example. Particularly in the case of fitting, the fact that NumeRe is not limited to a few selected models, but allows (almost) any function to be used as a model for fitting, is particularly important.

FFT and FWT

NumeRe offers out-of-the-box algorithms for Fourier (fft, fft2d) and wavelet transforms (fwt), which can be used to transform data quickly and easily into frequency or k-space. These algorithms are among the standards in modern data science and are used for many different applications to gain deeper insights into the data to be analyzed.

The Fourier transform in particular also has applications in the convolution of data, as a convolution in frequency space corresponds to a multiplication that is many times faster.

Fitting with arbitrary expressions

Tired of the very limited trend line options in Excel? In NumeRe you can fit almost any one- or two-dimensional function with (fitw) or without further weighting factors (fit) to your data. The functions can be composed of whole expressions: A*sin(B*x)*exp(-C*x)+sqrt(cos(D*x)) There are only very few exceptions to functions (e.g. random numbers) that cannot be part of such an expression. The defined parameters (in this example A, ..., D) of the function are adjusted.

Integrate differential equations

NumeRe can numerically integrate ordinary differential equations (ODE). In addition to the Runge-Kutta method, other variants are available as possible integrators. The differential equations must be of the first order, but it is possible to convert nth-order differential equations into n first-order differential equations and specify these as a common system.

Creating GUIs

In addition to simple dialog boxes, NumeRe supports the creation of entire applications that run in a separate window. NumeRe must continue to run in the background, but it is possible to hide the NumeRe window completely while your own application is running.

The layout algorithm can take some getting used to, as it does not allow pixel-precise positioning, but organizes elements in automatically scaling groups. The advantage is that it then plays a subordinate role how large the user wants to make the window.

Conclusion

In the previous sections, we have emphasized that as soon as an implementation requires a somewhat more complex logic beyond simple number and string manipulation (the "magic limit"), NumeRe can be used to create solutions that are significantly more maintainable and easier to control. In particular, the associated implementations can be made more abstract, which significantly increases reusability and, over time, leads to extensive libraries.

At the same time, however, it has also turned out that an implementation in NumeRe below the "magic limit" is rather more complex than the same with spreadsheets. Where exactly this "magic limit" is will certainly vary from person to person and everyone will therefore have to find out for themselves. The following are reliable indicators: