Excel as a Frontend

Ler em português

Corporate and personal finances. Governmental and management reports. Account statements. Inventory. Projections. Simulations. Lists of people. 90% of the global GDP. What do all those things have in common?

They go through Excel.

On the projects I have worked on, one of the most common features requested by clients is exporting data to Excel, especially for back-office operations, so analysts can more easily find information, make reports and calculate sums. Not just back-office — field agents, support and sales people also use it.

Some real-life examples:

• Internal reports of transactions per individual;
• Reports of sales grouped by business partners;
• A salesman that needs to see which offers are available for a customer;
• An economist that needs the inflation rates of previous months;
• An HR analyst checking employees' worked hours;
• An investment manager analysing the historical valuation of a product.

Excel is a great tool for visualizing data and is very intuitive, easily used by anyone.

Considering that many times it is the final data format, here is the question: instead of having a web app, backend and frontend, hosting, servers, etc., can't we just do everything on Excel? Wouldn't it be easier? In this article, let's explore this idea.

Connecting to external data sources

Excel can connect to external sources and download data from them. Many integrations are available, such as:

• XMLs from the internet
• XML, CSV, TXT and XLSX files
• Flat files
• Databases, like SQL Server, PostgreSQL, MySQL
• Azure Blob Storage
• HTTP endpoints
• And others.

For example, let's create a local XML file with the content below:

On Excel, on the Data tab, let's click on New Query > From File > From XML, and then select our file.

After importing, the result will be:

Whenever you wish to pull data, you can go on Data > Refresh All, and the table will be updated with the latest remote data.

Advantages

Having Excel pull remote data has many advantages.

One of them is that we don't need to spend effort, time or infrastructure building a website to make that information available; all we need is the data source and a spreadsheet.

Even information security is solved for us:

• If the data source is a remote file, we just need to control access permission on it.
• If the data source is a database or HTTP endpoint, we can provide a personalized credential with permissions only for specific queries.
• Windows Authentication is an option for the cases above.

Another advantage is that once downloaded, the data is available for usage even if there is no internet connection.

Last, having individual spreadsheets gives users a great degree of freedom, because they can customize them however they want.

Write operations

Can we use Excel to insert, delete and modify remote data? The answer is yes. UserForms and macros are the way to achieve that.

UserForms are custom dialogs for user data input. Macros on the other hand are the code behind, responsible for data validations and connecting to the database and server.

Userform example on Excel.

Keep in mind that whenever a change is required on those write integrations, users will need to download updated versions of the spreadsheet.

I recommend the following userform tutorials: one from Excel Easy and another from Wise Owl Training.

Final thoughts

For internal services within a business, or those that heavily rely on mathematics and reports, Excel can definitely be a good frontend solution.

When I discovered that Excel can connect to external data sources, I thought "how come I didn't know about this earlier?". It makes a lot of sense. Since it's a software not widely used by IT professionals, interesting features like this may go unnoticed by us, even though they could help us a lot.