Excel is the King

Anyone with some experience in any office around the world has some kind of contact with an Excel expert that made that gorgeous excel sheet that calculates that complex report that we must deliver every morning. He or she is our hero!

Back in the old days, the same report was made by an entire team using valuable hours of our precious time just to report whatever management wants to know on a daily basis.

But to make this report work, we have to manually input data from some paper reports, some information from the main system, and on many occasions, this is very error-prone.

Don't get me wrong, Excel is an excellent tool, but is clearly not intended for solving day-to-day routine reports as the manual effort to accomplish the job is by far not adequate.

Information usually has to be copied and pasted, modified at will, and verified manually. With some time you will see one of two scenarios: one single sheet that is modified on a daily basis or an infinite set of reproductions of the same sheet.

Formulas can be protected but usually, they are not. Excel Macros can be created but usually is not the case, and sensitive information can easily be left without any protection.

Information managed this way is usually to consolidate data at the end of the month, or even to consolidate data in a complex way. It really takes an Excel expert to do that.

But users are no tech guys. Users can be lawyers, human resources specialists, diplomats, soldiers, or even priests. Information technology is here to stay.

Databases as the unknown solution

Maybe, those solutions are better solved if we had a database at hand. But let's face it. The chances to have a database expert in a standard office, even very familiar ones like MySQL/MariaDB or MongoDB are far away.

Access is not a bad solution, but it is not so popular and needs some knowledge related to relational databases that are not intuitive as Excel can be.

Filemaker is an excellent choice. Can be used to create small solutions database-centric and with many options to choose from. Even can transform some Excel solutions with a little work with extraordinary results.

Honestly, I am not aware of similar quality solutions that can compare to these two, but in this vast universe of software, who could be?

A web-based solution as a mini-app

Web technologies have a very nice approach to solve these use cases, as they can manage input validation, crud operations, single or multiple databases, ETL, reporting, and information distribution even on the same office or in a corporate intranet.

Best of all is all can be acomplished and tailored with a complete stack of open-source technologies, and even hosting the solution on external servers, or in local machines.

There are a ton of possible solutions to choose from, but I will drive you on a Stack (the one I use for this purpose)

Database

I use MariaDB (https://mariadb.org/) that can use standard relational technology, but it can also handle JSON fields. Can be installed in a docker container and move at will to other machines easily.

Backend

After testing many options, the one I prefer is NestJs (https://nestjs.com/). You will need to develop three use cases: CRUD, ETL, and API.

Frontend

Angular (https://angular.io/) is what I use, but, I am seriously considering Vue (https://vuejs.org/) or React (https://reactjs.org/), as they are better suited for small projects.

This is where you will have to spend most of the time (60% of the development).

Using these frameworks will also require a lot of formal knowledge and many external libraries. But it is indeed a clear solution for repetitive office work.

Other tools

You will need some other tools to accomplish the job as code editors and REST testing tools, but, the idea behind is to capture the complexity of boring reports and turning them into an application that can be used by anyone in the office and even export full report to Excel, without giving away the calculation formulas, or sensitive information.

For the web developer

Web development is usually associated with marketing sites and not with Business Intelligence, issues, Report Making, and other office areas.

Is a growing market as pandemic has arrived and information needs to be handled in a web style way instead of the old worksheet method.

So, next time if you need a report to be done, think that Excel is not the only way to get it done, and probably you will find new methods to save money and gain extra performance in your organization.