Over 750 million Excel users across the globe use hundreds of spreadsheets each day to run critical business operations. However, most companies are not taking full advantage of Excel’s power as they spend billions globally on IT budgets to turn spreadsheets into business applications.
Adam Reynolds, our Lead Product Evangelist, explains how the Coherent Spark platform makes it possible for experts to work together to unlock the power of Excel by using the tools they are already familiar with, enabling businesses to overcome Excel’s challenges, shorten product development cycles and accelerate time to market.
Excel: Love it or hate it…
There are two types of IT professionals: those who love Excel, and Software Developers. Love it or hate it Excel is a fundamental part of many organizations. Arguably, it’s not always used in the best way though.
There’s a concept you may have heard of called “Shadow IT”. The theory is that there are a lot of applications, bespoke or bought, that run inside a business outside the remit of the IT department. As such they’re uncontrolled, unsupported, and largely unmanageable. It could be something as complex as a multi-user system or as simple as an ungoverned spreadsheet.
What does this have to do with Excel and Coherent?
The Excel Conundrum
Let’s say you’ve got a sales team that runs promotional deals for your customers. Now, there are lots of off-the-shelf products you can buy to do this, but I feel confident in saying a vast majority of teams probably do this in a spreadsheet. Which is all well and good…until it’s not.
Oftentimes this spreadsheet is sent out in an email and used from local copies which means you can’t guarantee everyone is using the right version. Talking of versions, you also have limited version control with Excel and that’s assuming you have rollbacks enabled. On top of all that you’ve also got the risk that someone straight up deletes the master file and it’s not backed up somewhere. A veritable governance nightmare.
What if there was a better way? What if you could take that file and upload it to a secure platform that’s fully version-controlled? Enabling patched, minor, or major version changes and allowing rollbacks? That’s the first thing Coherent Spark will give you.
Let’s take it a step further though. What if you want to take the logic from that carefully designed sales spreadsheet, the deal model that’s been built by the experts, and embed it into a more centrally managed application? The traditional approach to this is to go to your sales team and say, “Thanks for all your hard work, we’re going to take this model and turn it into an application.” At this point the sales team loses control of their logic, the developers have to unpick the logic and any future changes require working across two teams. This can take months of back and forth trying to decipher the magic that’s been worked in Excel and to reliably convert it into your target programming language.
This is where Coherent Spark comes to the rescue again! You know you uploaded that spreadsheet and got full version control? Well, when you did that, with a bit of cell tagging in the spreadsheet, you also got a fully functioning REST API. This API encapsulates all the logic from the spreadsheet and allows you to execute it from any application that can consume REST services. It could be a bespoke development or even an off-the-shelf product like Microsoft Dynamics or Salesforce. Hooking it up to Spark means you can simply pass in your input values, let the Excel logic do its thing, and then receive the end results back in your application.
By doing this you can improve efficiency and time to deliver by separating the concerns of bespoke business logic and end-user experience. The experts in sales deals can maintain their business logic and issue updates whilst the team in charge of the user experience can make their updates and not have to handle any small changes to the underlying logic. This removes blockers and works for both teams whilst still being secure, version-controlled, and fully managed.
It also removes the governance headache of people having their own unique copies of the spreadsheet and not knowing who has which version.
Sparking the imagination
So, what does this look like in practice? Let’s take a different example, a housing affordability calculator. It’s quite a straightforward spreadsheet. It takes a number of inputs such as gross annual income, expense, debts and costs, and then it gives you a breakdown of what the model says you can afford to pay for a house and what your monthly bills will look like.
The first thing we do is tag our spreadsheet with inputs and outputs. This is a really simple process of just adding names in a certain format to the cells (or range of cells) you want to use in your service. An input, for example, like the gross annual income cell containing the value “65,000.00” has been given the name “Xinput_GrossAnnualIncome”. Then when this spreadsheet is uploaded to Coherent Spark this field will now be made available in the API as an input field.
Note: To name a field you can either select the cell and input the name in the drop-down box just above the main spreadsheet or navigate to the Formulas tab and use the “Define Name” button.
An output field like maximum house price can be tagged “Xoutput_MaximumHousePrice” and it will be returned by the API as part of a successful request. (You can even tag charts using “Ximage_” to have them returned as an image URL!)
Once you’re happy with your tags you can head over to the Coherent Spark site, create a new folder, and upload your service. I won’t go through all the steps of this as it’s pretty easy to follow and there’s plenty of documentation should you need it.
Once it’s uploaded you can then drill into the service and access an API test tool so you can see all the inputs and outputs you’ve tagged and check it’s all working as intended by changing the input values and submitting your query.
You’ll also find JSON representations of the request and response and, on the integration tab, a way to download your API in a Swagger format. All this information can be used to then integrate your API into another system.
For example, here’s one I built in Mendix in just a few hours:
Then another example I’ve been working on in Retool:
Developing applications is easy with Coherent Spark
Coherent Spark is the perfect partner for any bespoke application development. Be it low code, no code, or traditional development. The ability to encapsulate and expose Excel logic without having to recreate it in your language of choice saves time and is easily maintainable.
The really powerful thing here is now we have these applications the underlying spreadsheet can be tweaked and changed without having to update or redeploy the frontend (unless you need a new input/output). So, if the logical model needs to change the team responsible for building that model can update it independently of the software team! No bottlenecks and easy, version-controlled updates!
That’s where the power of Coherent Spark lies. Let the experts be experts. They can use the tools they are familiar with and proficient in to work together and deliver results.
Book a demo with our team to discuss further details of how Coherent Spark can help you develop bespoke applications.
