My Answer To An Excel VBA vs Python Question

image: xlwings.org

Two weeks ago, someone in our MVP community asked a question about Excel VBA vs Python. He is an expert Excel VBA programmer and uses it a lot at his job but now he is considering going fully into the data science field and was considering picking up Python. He wanted to know if anyone has gone a similar route, from Excel VBA to Python, and what the person's experience was.

It was my turn to shine. I am very silent in the community mostly because some of what they discuss are above my knowledge or outside my area of use of Excel. But this was a great opportunity to raise my head and show they that I also know some things. And below is the answer I provided.

"

First, I am probably in same category with you. Already great at VBA for all my automated data analysis work. And like you I have been exploring the world of Python and R. I have even managed to be competent enough in R to teach a 4-day class in it to BI folks from banks and telecoms companies here. And as for Python I am using it for the stock analysis application backend that my company is building. But all these experiences are recent – about a year and half with the usual slowness at the beginning.

Similarities

At the start, majorly due to the huge buzz around Python, I thought there were things I couldn’t do with VBA that were possible with Python. Now, I only think so very faintly. Except building a web app with Python (which, arguably, isn’t data analysis), there is nothing I have seen someone build with Python that isn’t replicable with Excel VBA. Just factor in that maybe I haven’t seen thousands; I have seen just a low hundreds. Is it from data scraping to stochastic search algorithms to time series? We may not enjoy the ready made advantage of Pandas but a good VBA programmer will achieve the same.

Differences

I find three levels of differences:

1. Market opportunities and buzz differences. I am used to clients being surprised by how much I use Excel to achieve for them – from automating the customer site survey and commissioning tasks for a telecoms company that used to pay a third party $50,000/monthly for same results using many manual labour to solving a travelling salesman algorithm question. People constantly underestimate Excel. In contrast, they don’t underestimate Python. For the consulting end, it might not be a big deal since you can always show a proof of concept or be more creative in convincing the client to go with you over the Python guy. But in the job market, they don’t even give you the chance. Every data science role wants Python and/or R. Excel? What is that?
2. Embedded applications. Or should I call them backend tasks. Service workers. Web workers. The names are many. Because you always have to lug around the full Excel application, you can’t use VBA effectively to handle backend tasks for lean applications use. I have been trying to not fall for the temptation of using Excel for our stock analysis applications backend data scrapping and storage into the cloud SQL server. It can do it but not very efficient use of resources. And when it comes to service workers that are supposed to reduce the strain on your web deployed application built on top of your data analysis/science, VBA is out the picture.
3. Pandas, Numpy and all those life easing libraries for advanced analytics. Unfortunately, we don’t have such luxuries in Excel VBA.

 Again, I am more like describing an elephant from touching one part of it. So take this as my biased perspective.

"