Excel and Angular web app integration for interactive data-binding

One of the problems we are solving is how to make it easier for financial analysts to work with numerical data. Usually this involves working with financial and statistical reports made available by corporate entities, public organisations, and statistic compilation agencies, and tools for creating and maintaining analytical models.

Excel, of course, is the global benchmark for working with numerical and tabular data. It dates from the early 1980s, which means it is probably older than most of us, and it has been refined and upgraded over that time to provide an array of capabilities which make it the basis for financial reporting in our day. That said, the tools Microsoft provides to integrate with it are far from ideal, and in particular it can be difficult to integrate modern user interfaces into Excel.

However, the fact that it is the industry standard – more than 99% of all calculations and financial reporting is done with Excel – means there is no real alternative. Looking for other solutions is practically impossible, and likely a waste of time, and ‘Cloud’ based spreadsheets haven’t yet reached Excel for functionality. Embedding various unrelated applications will be a long way from end user expectations, given that more than 99% of them will know and be comfortable with Excel. It simply has to be Excel.

That means the challenge becomes that of meeting contemporary world user expectations – effectively one click search convenience – with Excel.

At Exerica we needed to give Excel users the ability to work with a fairly rich and functional interface. We took a slightly different path, which ultimately produced a good result. In this article I will tell you how you can organize interaction between Excel and Angular based web applications, and can expand Excel with almost any functionality which can be implemented in a modern web application.

State-of-the-art

The data here is basic numerical “financial indicators”, for example, income for the 1st quarter of 2020. For the sake of simplicity, I will refer to them simply as “numbers”. As you can see, there is little connection between the numbers in the document and the analytical model; almost everything is only in the analyst’s head.  The work involved in completing and maintaining the model takes hours of painstaking searching and incorporating numbers from the document into tables, and then looking for input errors.  At Exerica we wanted to make common user tools possible – drag-and-drop, insertion through a clipboard, and the ability to quickly locate and make available the source data in the original document.

Our starting point for the solution

By the time we started incorporating this type of capability with Excel, we already had a MongoDB database, a REST API backend in .NET Core, and a front-end single-page application (SPA) in Angular, as well as some other services. Also at this point, we had already tried different options for integrating spreadsheet applications, including Excel, and none went beyond MVP.

Excel data binding

There are some common Excel tools used to bind data in a table to an arbitrary system or data source:

• Connect the spreadsheet to an external data source
• Use RTD (RealTimeData)
• Implement UDF (User-Defined Functions)

Loading data from an external data source is not interactive and does not fit our needs. Pure RTD is less user-friendly in terms of syntax and limits the flexibility of the solution. Using UDFs, you can create a custom function that will work in a familiar way for Excel users. It can be used in other functions, it understands references like A1 or R1C1 and generally behaves as it should. We implemented UDF via an Excel Add-in. We used the C# and .NET Framework for implementation. Also we used the Excel DNA library to speed up development and to take advantage of RTD to enable instant data update.

In addition to UDF, our Add-in implements a ribbon (toolbar) with settings and shortcuts for some useful features.

Communication architecture

To transfer data to Excel and to establish interactivity, we developed a separate Notification Service that provides a Websocket connection using the SignalR library, and is a message broker for events that should be received by the front-end parts of the system in real time.

User friendly data transfer solution

In our SPA frontend, we highlight all the numbers that the system has detected. The user can select them, navigate through them, etc. For data insertion, we have implemented 3 mechanisms for various use cases:

• Dragging to Excel with a mouse (drag’n’drop)
• Automatic insertion into Excel on click in SPA
• Copy and paste via clipboard

When a user initiates drag’n’drop of a particular number from SPA, a link with the identifier of this number from our system is created to enable dragging (.../unifiedId/005F5549CDD04F8000010405FF06009EB57C0D985CD001). When pasting into Excel, our Add-in intercepts the insert event and parses the inserted text with regular expression. When a valid link is found, the Add-in replaces it with the corresponding formula =ExrcP(...).

When a user clicks on a number in the SPA, a message is sent to the Add-in via the Notification Service, containing all the necessary data to insert the formula. Then, the formula is simply inserted into the selected cell.

This works very well when a user needs to insert one number into their model, but if they need to transfer a part or complete table, then another mechanism is needed. Copying through a clipboard is the most commonly used. However, this turns out to be more complicated than the first two methods. Effectively, for convenience, the inserted data needs to be presented in the original Excel format – OpenXML Spreadsheet. This is most easily done using the Excel object model, that is, from the Add-in. Therefore, the process of creating a clipboard looks like this:

• The user selects an area with numbers in the SPA
• An array of selected numbers is sent to the Notification Service
• The Notification Service passes it to the Add-in
• The Add-in generates OpenXML and inserts it into the clipboard
• The user can paste data from the clipboard to anywhere in any Excel spreadsheet

Despite the fact that data goes a long way this happens quite quickly, thanks to SignalR and RTD, and in a way which doesn’t distract the user.

Data propagation in Excel

After a user has selected the initial data for their model, they need to “propagate” all of the periods (years, semesters and quarters) that are of interest. For these purposes, one of the parameters of our UDF is the date or period (remember: “income for the 1st quarter of 2020”). Excel has an inbuilt mechanism which enables “spreading” a formula across multiple cells, taking into account references as function arguments. That is, instead of a specific date, a link to it is inserted into the function, and the user then “extends” it to other periods, while “the same” numbers from other periods are automatically loaded into the table.

Instant data identification

The user now has a model with several hundred rows and several dozen columns. And they may have a question, what specifically is that number in cell L123? To get the answer, they just need to click on that cell, and in our SPA the same report will open, to the very location and page where the clicked number is written, and the number in the report will be highlighted. Like this:

And if this is not just one number from the report, but the result of some calculations using numbers taken from the report, or the application of a formula in Excel using those numbers, then all the numbers included in the expression calculated in Excel will be highlighted. This does not download the entire application and download all the necessary data, as in the case of following a link.

In conclusion

Exerica’s development of a non-standard interaction between Excel and a web application has turned out to be quite user friendly. Because it uses Excel, the entry threshold for target audience users is quite low (The average financial analyst ‘gets it’ pretty easily). At the same time, we also keep all Excel of Excel’s power for working with numerical data – and the data itself always remains associated with the source, as well as associated in time.

For target customers, there is no need to build, and come to terms with, completely new external systems into the business process of working with data. Our solution is embedded as an additional “tool” inside Excel, which is the de facto standard for financial data providers.

A similar architectural approach for integration of web applications with Microsoft Excel can be applied to other tasks that require interactivity and complex user interfaces when working with numerical and tabular data.