In the current age of computing, the value of software development and computer programming is without question. After a periodic lull following the dot-com era, the occupation outlook for programmers continues to be extremely strong. Software developers and engineers continue to be regarded as some of the best and most in-demand jobs by Glassdoor Best Jobs in America, LinkedIn Most in demand jobs, and US News 100 Best Jobs. The US Bureau of Labor Statistics projects that the job outlook for Software Developers, Quality Assurance Analysts, and Testers will increase 22% from 2020-2030, which much higher than the overall average. With the high demand for developers, companies have found it difficult to implement the changes they need to not only maintain their existing systems, but also to keep pace with competitors as the demand for digitization increases post-COVID.
It is unrealistic to expect that business and industry experts will spend additional time to learn the tools and processes necessary to develop software to implement the calculations and logic specific to their subject domain. This is where no-code platforms have come to the forefront – to help smaller teams or “citizen developers” to implement solutions that may be too costly or complex to be done as part of a large-scale IT project. However, the most complex part of no-code is not necessarily the user interface, the orchestration of different activities, or even the storage of data. The most challenging task is to be able to define the logic that drives the application in a way that is easy for users to maintain.
With Coherent Spark, we enable users to take Microsoft Excel spreadsheet files and to convert them into customizable and easily consumable APIs. Why the focus on Microsoft Excel? The adoption of Excel has been extremely rapid given its initial introduction to Windows with Excel 2.0 in 1987. In 1996, Microsoft reported that Excel was the world’s most popular spreadsheet program with more than 30 million users. In 2016 Microsoft reported that “More than 1.2 billion people use Microsoft Office in 140 countries and 107 languages around the world.” With Excel’s capability to define calculations and manage data, Excel is an accessible way to manage logic and calculations. Given its popularity and familiarity with so many users, Excel is an ideal candidate to be the starting point behind solutions that scale from small citizen developed no-code solutions to complex transactions for the world’s largest corporations.
This paper is separated into 2 parts. In Part I we establish why Excel is a programming language from a Computer Science and Software Engineering perspective. In Part II we describe how Coherent Spark can help users overcome End User Computing risk and empowers them to apply Continuous Integration / Continuous Development processes in an Excel context.
“A programming language is a formal language comprising a set of strings that produce various kinds of machine code output. Programming languages are one kind of computer language, and are used in computer programming to implement algorithms.” (Wikipedia)
Programming languages have been around even before computers and have continued to evolve with advances in Computer Science. Although some people may only consider something a programming language if it is complex and requires coding, Excel itself is a programming language (even when excluding Visual Basic for Applications and the new Office Script). Felienne Hermans, Computer Science professor at Leiden University has conducted several presentations about Excel and why she believes it is important for traditional programmers to learn. In her talk “Spreadsheets are Code” she summarized why she believes Excel workbooks form a programming language:
From a Coherent perspective, Excel is a programming language simply because it is a way for people define calculations and logic. If Excel workbooks are a programming language, then Excel users are programmers as well! With this mindset we can take a broader view of Excel and leverage concepts from Computer Science and Software Engineering to understand how spreadsheets can be better used and developed.
It is therefore useful to apply some programming classification to Excel to understand how it fits within the spectrum of programming languages:
High-level (vs low-level)
The need to interact with how a computer operates and the use of machine code understandable by the microprocessor and memory has been abstracted from the user. Generally high-level programming languages are easier to write, debug and trace while sacrificing some potential performance. Performance concerns are alleviated with improvements in compiler developments and general advances in computing hardware.
Declarative (vs Imperative)
In older iterations of Excel, it would be considered more as a declarative programming language as it focuses on the outcome of a calculation without so much consideration of how the result is delivered and the control flow. With the recent introduction of LET and LAMBDA functions, an argument could be made that Excel contains some traces of imperative programming concepts. More will be discussed in the section Recent Excel enhancements.
Interpreted (vs compiled)
Excel workbooks are run by the Excel application which acts as an interpreter. In more general terms the interpreter then converts the program into machine code for the computer. Compare this to compiled programming languages which require conversion to machine code before execution. Compiled code technically performs faster however takes more time to debug and evaluate execution.
General purpose (vs domain specific)
We consider Excel to be a general-purpose programming language which can be used for many different applications. Examples of domain specific languages would again be HTML and SQL which focus on specific applications.
It is also an interesting comparison to understand how Python, one of the most popular programming languages used today, is classified as a programming language. Python is also a high-level interpreted general purpose programming language. Python is not popular because of its underlying speed but more for its relative ease of use, general purpose nature, and broad applications. Despite not being the fastest or the most efficient, it is used for extremely complex problems including acting as the primary interface for Big Data and Deep Learning applications.
In “Concepts of Programming Languages Eleventh Edition” by Robert W. Sedra, the author defines 3 core criteria readability, writability and reliability and 11 characteristics to evaluate programming languages (Sedra, 1.3 Language Evaluation Criteria). For defining calculations and logic, we assert that Excel is readable, writable, and a reliable programming language. The evaluation was also made with consideration of the programming experience with the Excel application as an Integrated Development Environment (IDE).
One of the advantages of Excel to more traditional code is that the “coding area” is 3-dimensional. Formulas can be arranged in 2-dimensions within a single worksheet or across multiple worksheets or even workbooks as an additional dimension. This makes it possible to layout the meaning and significance of the code in a manner that is easier for others to read but also intuitive to flip between different sections of code more easily or introducing visual elements. There are also many ways of commenting code in Excel, either directly in the worksheet or via notes and comments. This contrasts with the traditional program which is written as a top-to-bottom document and across different files. This is not to imply all Excel files are easy to read, these features can also be misused, like how regular code can be poorly written and documented. Finally Excel also includes interactive tools to make it easy to read, trace, and evaluate functions and programs.
Excel meets all the characteristics of being a readable programming language:
Simplicity
Orthogonality
Data types
Syntax design
The important characteristics noted in readability also apply to the writability of a programming language. In addition to the above characteristics, a good programming language with writable characteristics would include:
Support for abstraction
Expressivity
The important characteristics noted in readability and writability also apply to the reliability of a programming language. Excel has been around for more than 30 years and provides backwards compatibility to deprecated functions more than 20 years old.
Type checking
Exception handling
Restricted aliasing
If we consider Excel as a good programming language, it should also support good software development practices. In “Code Complete Second Edition” by Steve McConnell, he describes the “Desirable Characteristics of a Design” (McConnell, Chapter 5). A few key points are highlighted with respect to the how Excel is compatible with effective software design.
After the Office 2007 release which included the introduction of the XML file format and the ribbon user interface, the Excel application did not see too many enhancements over the next decade. The emphasis was placed more around Microsoft’s Power BI suite that worked around the capabilities of Excel. However this has changed in recent years with the introduction of some new elements that enhance the programmability of spreadsheets while also making them easier to maintain and extend.
The LET and LAMBDA functions offer a completely new way to write formulas in a more readable and reusable way by including Anonymous functions. In Computer Science, this refers to functions that do not need to be assigned an identifier. This means that they can be applied in a temporary manner.
The LET function makes it easier to define a formula by making it easy to define local variables within a formula. An example of a piecewise function for the sample spreadsheet, there are a number of terms that are repeated. Without using named ranges, formulas are not very natural to trace using cell addresses. With the LET function, new intermediate terms can be defined and reused, and cell addresses can be renamed to variable names. This also removes the need to link to dependent cells many times to facilitate tracing of formulas.
The LET formula is useful for individual formulas, but if this formula needs to be repeated frequently, having the LET function does not making it easier to reuse this function. The LAMBDA function allows users to create reusable user-defined functions without the need for Visual Basic for Applications. An example of how the LAMBDA function could be used is shown below. This will empower much cleaner spreadsheets and the ability to centrally modify commonly used functions in a workbook.
The LAMBDA function has been further extended by helper functions which can apply a LAMBDA function across a range of data. This eliminates the need to use workarounds that relied on the SEQUENCE function to perform some of these behaviours. Some other interesting applications of LAMBDA functions when combined with Dynamic Arrays include the ability to define PivotTable-like objects.
For Excel files that have been uploaded to OneDrive or SharePoint it is now possible to show the historical changes made to the file in Show Changes. It is not yet Git for Excel but this is a great step in improving auditability and collaboration in a workbook and addressing the issue of unaudited and untraceable changes.
Dynamic array functions allow users to reduce the overhead needed to manage data that may change size depending on other data or inputs. This works along with the spilled range operator to make it easier to apply formulas dependent on a dynamic array. Below the Dynamic Array function SEQUENCE is used to create a list of 3 values and their square roots, with only 2 formulas defined by the user. Changing the value of ‘n’ in B3 can extend or shorten the square root table.
Data Types allow the definition of object models which may have different properties. For example, the built-in Stocks Data Type includes properties such as price, previous close, 52 week high/low, etc.
For users who require additional programming capabilities that work online in Office 365, Microsoft has developed Office Script. It has some overlapping capabilities with Visual Basic for Applications. Script Lab is an Add-in to help users learn and write Office Script.
End User Computing (EUC) applications, such as Excel, are used in many organizations to develop new applications that are independent of traditional IT development processes. This relates to the concept of the Citizen Developer noted in the Overview. From Deloitte’s paper “End User Computing Solving The Problem”, they noted that: “User-developed and user-controlled applications, by definition, are not subject to the same development, monitoring, and reporting rigor and control as traditional applications. And often, management lacks visibility into exactly how pervasive the use of EUCs has become throughout the enterprise.”
With our cloud-based logic engine, Coherent Spark, we take Excel files and convert them into easily consumable APIs. This enables users to translate their business logic into something that can be more formally consumed in an application. To address EUC risk, we developed several features within Coherent Spark to help make Excel files more enterprise-level.
When an Excel file is being used to developing an application that leverages Coherent Spark for calculation and logic, the way the file can be setup can be greatly simplified.
When Excel files are used to create EUC applications, a lot of additional work is required to build a user interface in the file. This may involve worksheets that look like user interfaces or the use of ActiveX controls and Visual Basic for Applications. Sheets also require additional protection to prevent users from interacting with sections of the spreadsheet they are not supposed to or to prevent logic from being inadvertently distributed to users. When an application is developed in conjunction with Coherent Spark, there is no longer the need to build the user interface elements into Coherent Spark as the UI would be built separately, either through Coherent’s Form Builder technology or any other no-code applications that can be integrated with Coherent Spark.
A key capability of Coherent Spark’s Testing Center is that it allows iteration of a model against a dataset. This capability can extend to simulations or scoring of models that may be difficult to define even using code in Data Science programming languages such as Python and R. This helps to reduce the need to create large spreadsheets that are capable of ingesting large datasets. The Excel calculation and logic can be streamlined to focus on how a single calculation is performed. This improves readability and auditability of the Excel files.
Where a calculation is relatively complex and is separated into multiple Excel files for easier management across teams, Coherent Spark also enables models to be chained such that the outputs from one model flow into a subsequent model. This enables some encapsulation capabilities into Coherent Spark in a more controlled manner. Therefore, models that may have been previously connected via External Links (which are inherently brittle) can be connected in a better way with Chaining.
“Continuous integration is a DevOps software development practice where developers regularly merge their code changes into a central repository, after which automated builds and tests are run.” (AWS)
“Continuous delivery is a software development practice where code changes are automatically prepared for a release to production.” (AWS)
Continuous Integrations/Continuous Delivery (CI/CD) is a concept that may be new to many, but very familiar to those who work in modern Development Operations (DevOps) teams. CI/CD practices have been implemented in many organizations to improve the quality of software delivery.
In a team that practices Continuous Integration, the changes the development teams make are frequently included in build systems and tested. After a developer has completed their code, they run unit tests of their changes on their personal environment. If these succeed, the code is then checked into a central build server for further unit testing. In practice this may be done more in daily cycles rather than in a truly continuous manner, however the key point is to maintain a fast and frequent process of including new code changes for testing. This aids in identifying issues earlier, especially those arising from conflicting code between developers. The opposite of Continuous Integration would be a development process that waits over a long period of time to accumulate different code changes before creating a testing build. There are risks that code built out over a long period of time will have more issues, like potential conflicts between code, and will therefore take much longer to test and resolve issues.
In Continuous Delivery, finalized code from the Continuous Integration process can be deployed in an automated fashion to higher level environments e.g., Staging, User Acceptance, Production. This facilitates testing from a broader view of users and the ability to deploy changes big or small to customers much faster than if they were conducted using more manual processes.
At Coherent we believe that Coherent Spark is the CI/CD for Excel. When users are satisfied with their Excel files (similar to the personal unit testing), the model can be uploaded to Coherent Spark and immediately converted into an API. This enables further unit testing as well as bulk testing via the Testing Center. The converted API is immediately documented and ready to be consumed. Any further iterations of models are also instantly converted into API updates that can be used by downstream applications without any interruption. By facilitating a rapid change and delivery process, this enables users to share their calculations and logic in a more consistent and broader way that also promotes rapid testing and deployment of new calculations and enhancements.
Excel deserves more attention as a programming language and one that can help users to get things done. Its properties as a programming language can be classified and analyzed using standard Computer Science principles. Not only that but Excel supports good software design principles to enable users to develop robust software. Microsoft in recent years have also invested resources into advancing Excel to improve its programmability and capabilities to keep evolving it with the needs of users and enabling new use cases and functions with Excel.
With Coherent Spark and our capabilities to convert Excel calculations and logic into APIs, we open the doors to making Excel a viable platform for enterprise-grade development. Coherent Spark helps to address end-using computing risk by allowing for collaboration within a framework that versions, hashes, and logs actions on the platform. We make it easy for Excel users to realize their development aspirations much faster and even easier than other no-code platforms and extending their capabilities to conduct continuous integration and continuous development with Excel.
Request a demo of Coherent Spark today.