How to boost Excel data management with APIs via Python, R, Rust…

When API newbies can augment Excel while DevOps can open up their coding power.

9 min readApr 26, 2021

About this article:
During the last years I spent in the banking and fintech worlds, I observed some important changes in technology-driven behaviors around data analysis. This article shares of data analysis tools evolution and how Excel users and DevOps are adapting to it.

Starting up a new business in this field, I thought it would be useful to share my approach, views, in addition to some information about what we have built at XLbroadcaster.io.

Table of Contents:

1. APIs or the new normality to consume data

2. Excel-based data analysis methodologies face a paradox

3. Low code & No code: the new trend

4. The king is dead, long live the Queen: Excel and its suitors

5. Short focus on XLbroadcaster.io and overview of current solutions

1] APIs or the new normality to consume data

API is becoming the base trend for data consumption. The usage of Internet has evolved; it is not only a way for human users to access data, but it is also becoming a common tool for computers to share data with each other by calling APIs.

Additionally, the specificities associated with each programming language give birth to a complex landscape to which systems and developers need to adapt.

Want to read this story later? Save it in Journal.

Let’s quickly dive into two programming language examples that showcase my point:

- R, that was nowhere in 2010, is now a commonly used programming language. R is actually very well fitted to answer a growing need around building statistical software as well as data analysis.
Have a look at the Tiobe Index (The TIOBE Programming Community Index is an indicator of the popularity of programming languages) and check the yearly evolution of R (in red in the below graph) in their ranking, up to the 9th position in 2021.

- Julia, is nowadays a trendy programming language thanks to its capacity to deliver faster calculation outputs compared to Python or R. Is Julia going to break through like R ?

It worth mentioning that the existence and emergence of (new) libraries is a fundamental catalyst for a developer to jump/switch from one language to another!

Being agnostic to programming languages will become a standard for interfaces. You just want to tap into the best external or internal data sources without caring about any limitations from one or another programming language.
APIs are the answer to this need by essence!

2] Excel-based data analysis methodologies face a paradox

https://sciencetonnante.files.wordpress.com/2013/04/homer-simpson.jpg — https://en.wikipedia.org/wiki/Simpson%27s_paradox

a) On one side, our expectations related to data management tools are becoming increasingly challenging for Excel. It is having a hard time to face the following “classic” pitfalls:

- rather slow calculations
- difficult management of massive files
- poor business collaboration capacities
- macros limitations for deployment and versioning controls
- VBA macros not always well suited for specific requirements

Related to VBA, have a look at the 2020 survey from StackOverflow on developers about their interest in using programming languages.
This is the outcome: VBA is today the least loved language / in red below

Nowadays, businesses that demand strong data management aptitudes require different abilities and skills like:

Dealing with increasing complexity over pricing / behavioral / predictive / statistical models
Pumping up models with faster calculation, easy deployment and robust codebases
Easy Integration with 3rd party data to enhance those models
Dealing with increasingly larger datasets

b) On the other side, Excel is still massively used as the most common user-facing (& friendly) front-end for structured data all over the world.
The reason is very simple: it remains the most flexible and instinctive solution to manipulate structured data. It’s a super-efficient tool to quickly build up dashboards or interfaces with no need to code.

Robert Half Benchmarking Report (Figure 21 -p38) shows the Leading Types of Budgeting and Planning Tools in North America; you can see that Excel is still massively used, much more than any other solution in the market.
The Report also shows that, while Excel remains the most popular tool, it continues to lose ground as the preferred tool over the past years. The report detailed that almost 60% of firms with less than $25 million in revenue reported Excel as their tool of choice for budgeting and planning — which is a remarkable performance. However, this is down 10% from their 2018 report and around 20% from 2017.

In a nutshell:

> data scientists and analysts legitimately disparage Excel

> most businesses still work with Excel data

> alternative solutions are fighting to get a piece of the cake

On the field, the reality is that the average programming knowledge of analysts is improving. They begin to use scripts to reduce time-consuming tasks usually performed by hand or via Excel.

3] Low code & No code: the new trend

Source: University of Alabama at Birmingham Online

If we agree that there is a correlation between our still poor capacity to benefit from a global digital approach and the lack of educated resources to support its technology, we should then also agree that we need a “shortcut”.

It is worth mentioning here, as Gartner reported, that demand for business applications is five times higher than internal organizations’ capacity. The shortage of software developers and IT professionals is the main contributing factor to this limited capacity.

Therefore the rise of “citizen developers” sounds like a natural evolution. “Citizen developers” are software assemblers: with minimum to no coding education, a smart guy can create new software capabilities, which in turn can help accelerate the digital transformation of a business. This is what we call low-code and no-code technology (LCNC).

LCNC software trend represents the rise of this new class of data scientists and analysts who are not traditional software developers. Suddenly, innovation becomes accessible and faster even with software developers and IT professionals shortage.

In the Banking industry this trend is already reaching another level. Quoting Jezri Mohideen, Chief Digital Officer at Nomura: “Nomura will increasingly be targeting physicists rather than economists and will look to hire employees with a deeper understanding of statistics, mathematics and computer science straight from university.”

That being said, LCNC has some limitations as described by Alex Schmelkin, Chief Marketing Officer at Unqork Inc: “Low-code offerings provide efficiencies that increase developer productivity but they almost all require coding to do the hard stuff. (…) This new code becomes legacy the moment it’s written and recreates the same problems low-code/no-code claims to fix.”

So, LCNC platforms still require some IT capacities, at least to deliver full flexible solutions.

4] The king is dead, long live the Queen: Excel and its suitors

Soucre: thechronicleherald.ca — 6 Feb 1952

Let’s be honest, the above-mentioned Excel paradox is also fueled by a lack of understanding about what technology really means in our economy. We miss a broader buy-in at management level across industries to effectively spread the benefits of a digital approach and training resources to support technology.

I believe we are already on the right path, but we must admit that our “legacy system” is slowing down this evolution. Whether we can consider Excel as a legacy system is a fair question. Nevertheless today’s reality is clear: organizations are turning to customized or Excel-hybrid solutions.

Depending on the size of the business, the burden of IT resources or IT investments have a wide impact. In my experience, most small and medium businesses are taking the following stand: let’s just augment Excel!
This makes a lot of sense, considering the entry ticket cost for a full new solution for those businesses. The price and/or the time needed for training and getting used to a new system is still perceived as too high.

This is my understanding of the situation:

* More and more big corporates and brand-new businesses are moving outside Excel mainly because they usually have a strong management buy-in for a technological mindset and the budget to impulse it.

* For incumbents small and medium businesses and some specific industries like Finance, I see Excel as the dead King but also as the new Queen, surrounded by solutions allowing to incorporate APIs and LCNC trend inside Excel.

5] Short focus on XLbroadcaster.io and overview of current solutions

Focus on XLbroadcaster.io

XLbroadcaster.io turns Excel into an API front-end requiring zero lines of code. It augments Excel (versions from 2003 to nowadays) via an agnostic connector to/from your favorite APIs and preferred programming languages.

XLbroadcaster.io targets the following goals:

* No Code efficient connection to REST APIs directly to/from Excel

* Boosting Excel’s computational capacities and refresh latency by calling functions in Python, R, C++, Java, Julia, Rust …etc.

* Building a robust agnostic system while eliminating deployment and versioning headaches

* Opening up DevOps’ coding knowledge and making it accessible to every “basic Excel user”

Thanks to XLbroadcaster.io enjoy Excel user-facing front-end while empowering your calculations, business logic and data access with the most appropriate API or programming language.

Classic use case examples:

* Efficient connection to APIs directly to/from Excel
Our native-like Excel functions allow you to import data from REST APIs without writing any script (VBA or any other alternative). Just use our functions xlbc_get() or xlbc_post() and the data will be send to your spreadsheets.
For the other way around, upload data from Excel to any REST API and its associated data warehouse.
Our functions get (or post): =xlbc.get(“URL-of-the-API“,, “Cell”)
broadcast the data instantly, “Cell” being the pointing cell to place the data.
The real time data via Websocket is often a must have for some industries like finance. XLbroadcaster.io also provides a Real Time/Websocket function.

* Augment Excel-based models and improve DevOps business logic
Excel is great for its flexibility and the power it gives to desktop users but it lacks computing power. Fill this gap moving computationally intensive functions to a more efficient language (Python, R, Rust, C++…).
XLbroadcaster will send parameters and format results to directly in Excel spreadsheets. Any change in your functions will be instantly accessible to all.
Open your code to every “Excel basic user” and forget about deployment headaches.

* Secured Sharing of sensitive data from one Excel user to another ( how to share data directly from one remote Excel spreadsheet to another )
Share confidential data in the most secured and efficient way from your Excel spreadsheet to any API or directly to another Excel spreadsheet.
Leverage on our API via Databroadcaster to create an Excel Data transfert capacity with End-to-End Encrytion.
Take back the control over your data while sticking to your classic Excel front-end to store and manage sensitive data.

Overview of current solutions

Below, you can find a quick overview of current solutions available out there. It is by no means an exhaustive list.

The tools I find worth mentioning first are:

· ToolPak, Power Pivot, Power Query or even “Lambda functions”: These are proprietary Microsoft solutions already included in modern Excel versions.

· Google Sheet: a spreadsheet program included as part of the free, web-based Google Docs Editors suite offered by Google.