excel vs code

I used to work at a big investment bank I'll call "P. J. Mogran". First, as a middle office analyst for a credit trading desk, where I sped up some Excel processes by simplifying the workflows and writing macros. Then, as a programmer in a team that worked on automated processing, using Python. This article is the result of research and personal experience.

Most big, medium, and also small companies use Microsoft Excel extensively. And most of them have huge spreadsheets with impossible formulas and macros that no one understands because the people who built them are no longer on the team. Plus, to understand these spreadsheets you need both advanced knowledge of macros and knowledge of the industry side, so you would need to train someone on your team in macros, or get someone who knows macros and explain some of the business logic.

Excel is an incredible tool. It's intuitive, responsive (you don't need to hit 'calculate', it updates automatically unless you change it to manual calculation mode), and it can handle huge data sets. It's flexible and everyone else uses it. So, what seems to be the problem? The problem is that, as with any tool, it has limitations. But analysts know Excel too well so, when all you have is a hammer, everything looks like a nail. And sometimes, it's better to move processes to actual code if we want smooth operations.

We will define "processes" as units of work that often involve multiple steps or tasks. They can be periodic (operations) or ad hoc. Some examples of processes are reconciliations, analysis, reports, etc.

Why Automate

Automating processes is not always easy. So why risk it? Why go through the hassle of changing things? We can think of a few reasons.

• Better control environment: with code, you get automated testing. You can run more comprehensive controls than just doing a couple of checks like you would with Excel.
• Periodic automatic running: you can set a script to run at the precise time of your choice.
• Repeatable processes: the process will run always in the same way. This means it's free from operational errors.
• Easier to audit: it's easier to go back and know exactly how the process was run since you know the specific scripts that were used.
• Easy to integrate: you can mix several inputs and create many different outputs. For example, you could take 2 emails, extract data, process it, create one PDF, send it to another email, and save it to an internal file system for control.
• Speed. It's faster and more efficient.

When implemented properly, an automated process is less risky than its manual counterpart. That's why if something is sensitive and prone to operational (manual) errors, it should probably be automated.

When we talk about automation, we don't mean just throwing a couple of Excel macros to speed things up. We mean moving the process to a dedicated computing environment and coding it using actual programming languages. This involves talking to professional programmers, who can sometimes speak with different jargon and don't always come cheap (even if they are already in your company, you have to consider the opportunity cost). This makes some companies reluctant to automate processes.

But you have to consider this: proficient programmers are used to dealing with complexity. They know where and how automated processes can go wrong, how to model different data types, which algorithms will take a decent amount of time to process data and which ones will have to run virtually forever before returning something useful. So it makes a lot of sense for them to get involved in some processes, under the right conditions.

Processes Where It's OK to Use Excel

New Processes

When teams get a new process, it takes a few weeks for them to get used to it. It's not uncommon for the previous process owner (team) not to know important pieces of information, such as who designed the process originally, or why it is important. The acclimation period when the process has just been migrated or created is not the right one to start automating. It's better to get some context and experience first.

With Excel, you have better visibility of the process, so it's easier to analyze step by step how the process works and to see if there are steps that can be simplified without needing to turn to code. The better your team understands the process, the more they will see ways of improving the process. Besides, assuming most people in your team understand Excel, it's easier to share and discuss processes in this common medium.

Changing Processes

An Excel spreadsheet is more flexible than a Python script running on a server. It's simpler to modify and you get immediate visual feedback of your changes. So, if a process is changing all the time, it is the right move to keep it running in Excel. This happens often, for example, with processes that you have just migrated from another team (new processes are often changing).

Another reason to use Excel for changing processes is that automation takes time and usually involves multiple teams. So, before going through the automation project, you should make sure that the process is stable enough.

For processes that are always changing, it is always a good idea to get more context on why this is happening. You should ask some questions such as what is the need behind the process, who are the stakeholders, and what are the consequences of getting it wrong.

Quick Processes

In automation projects, you invest time upfront to get it back in the future. If something takes 5 minutes in Excel, it's unlikely you will get the time you put into the automation back, since the time it takes to automate a task has a fixed component.

Ad Hoc Processes

This one is trivial. If you only have to run it only once, it's not worth it to automate.

Sensitive Processes

Automating sensitive processes can bring great benefits, but also the worst consequences if anything goes wrong in the automation project. So managers often don't want to automate processes with important outputs, as long as they haven't had any issues with them. This way of thinking ("If it's not broken, don't fix it") can backfire. This tends to happen with regulatory reports and audits, where the feedback cycles can be in the order of months to years, so even if the sensitive report was not done right, it would take a long time before you found out.

Manual Processes

Some processes still require a lot of human intervention, such as trying to understand the market moves of a stock or self-auditing a report that has been a little off for the last few weeks. These processes cannot just be left to an algorithm, they require our capacity to think deeply and make sense of what is going on.

Processes where the input needs data cleaning are also manual. Cleaning data is time-consuming and, even if a computer can help, it's still a big time drain. Data analysts spend a lot of their time cleaning and normalizing data. The actual percentage of the time they spent on this is disputed.

Processes To Automate With Code

Complex Processes

When processes are complex, meaning that they get analysts confused, they make a good case for automation. Code can handle arbitrarily complex requirements and always run in a reliable and controlled way. And with proper tests, the automated process becomes less risky than its manual predecessor.

Periodic or Time-Sensitive Processes

Processes that should run starting at a certain time or have a deadline are good candidates for automation. Automation can guarantee a prompt start and fast processing, allowing the stakeholders to get the results of the process ASAP.

Time-Consuming Processes

Long, multi-step, processes are a great candidate for automation, since speed of execution is the main area where code shines.

It's not only that automating time-consuming processes will save your team time. It's that when these are done manually, analysts have more time to make mistakes. Also, after a couple of hours, analysts might get tired and lose focus, leading to an increased probability of making mistakes.

Conclusion

While Microsoft Excel is an incredibly powerful and flexible tool used extensively across many companies and industries, there are certain situations where automating processes using actual programming languages like Python can provide significant advantages. Processes that are complex, time-sensitive, time-consuming, or require running periodically are prime candidates for automation through code. By leveraging the capabilities of programming languages, companies can benefit from improved control environments, automated testing, repeatable processes free from manual errors, seamless integration across systems, and increased speed and efficiency.

Ultimately, the decision to automate a process should be driven by a careful evaluation of the specific requirements, constraints, and potential benefits. While Excel may suffice for new, frequently changing, quick, ad hoc, or highly manual processes, the power of programming languages truly shines when tackling complex, mission-critical operations. By embracing automation through code, companies can reduce operational risks, enhance productivity, and gain a competitive edge in an increasingly digital landscape.