r/learnpython • u/DizzyReading9015 • Sep 07 '20
Excel đ vs Python đ Should I choose Python over Excel âto automate the boring stuffâ? Also what and how is VBA programming (Excel) different (in the automating department)? Help!
Hello, I recently bought two courses off Udemy both being complete crash courses on Excel and Python. I (only) know VBA programming in Excel can be used to automate stuff outside Excel like Microsoft Word and PowerPoint and so can Python. However, Iâm at two minds over what to choose since both claim to do the same thing. Any sort of information would be appreciated! Thanks đ
62
u/cray5252 Sep 07 '20
If you go with python, make sure and spend time learning pandas because you'll be doing a lot of stuff using pandas and numpy to a lesser extent. Python arrays, lists, dictionaries, tuples are going to also be very important.
11
u/Bayho Sep 07 '20
Not to mention setting conditional formatting on data sets that could potentially save you hours and hours of time, as opposed to doing it manually. You could even have a script ready to run for each type of data set that does what you need it to.
5
u/FrancoisTruser Sep 07 '20
Do you have a resource for beginners to sugget for Panda?
I am beginning Python with the automate boring things book.
10
u/cray5252 Sep 07 '20
https://pandas.pydata.org/pandas-docs/stable/getting_started/tutorials.html
there are a number of links within this link. You can also learn a lot by just trying to answer peoples questions here that pertain to pandas. You don't have to post but just check to see if your right or how to do it and then make sure to actually program it in your ide and play with it to make sure you understand what's going on.
2
6
u/joestuart24 Sep 07 '20
I really like Python for Data Analysis. On YouTube lookup Data School. Heâs really good at teaching pandas.... free.
4
26
Sep 07 '20
This question completely depends on what you want to automate and who you want to be able to interact with your automations.
10
Sep 07 '20
Excel sucks at web scraping, for example. :D
2
Sep 08 '20
Python is absurd. Scraped in PHP (don't ask) and JS a few time over the past year. Started building my first py scraper today. Paired with Beautiful Soup, it's "caveman commercial" worthy
25
u/inventiveEngineering Sep 07 '20
In your position I would definitely choose Python, because:
you can program outside a software suite.
it is a stable environment. VBA tends to crush or to do mysterious creepy things.
it has a huge, active community, compared to VBA.
it is well maintained and documented programming language.
it has tons of useful libraries.
it will be the top programming language for the next years
knowing Python allows you to program stand-alone executable programs with nice GUI, do data science, machine learning, web frameworks, signal- and video processing, work in a scientific environment (Jupyter), learn to hack and even create games.
Python works the same way on Windows, Mac and Linux, also on RaspberryPi and I even have a Python distro with Jupyter on my Android phone
So python gives you an enormous potential to strive for whatever you like. VBA on the other hand is in decline. Microsoft 365 especially MS Teams works already with JS and not VBA. It will sooner or later be deprecated. Of course it is good to program nice macros for Excel or Word, but you can do the same stuff even better in Jupyter or in your own GUI Python program. I tried to so some Word-macros (splitting documents, etc.) and it was hard, because there are not many sources out there. The MS documentation - you have to say this - is BS and crap, and worst it is outdated. The only thing that will help you is inside the VBA editor, but this is still nothing compared to the python environment. There are no books published in the recent years about VBA for Word... I am forced to use a book written for Word 2007 & 2010!?
So to sum up: Python is your fishing rod, VBA is the suffocating fish on the river bank that will die soon.
Forgot to add: with Python you can also do Linear Algebra, FEA and symbolic mathematical calculations.
13
u/Im_Bigger_Than_Staan Sep 07 '20
Work may not allow you to install/use python there.
4
4
u/MattR0se Sep 07 '20
Work may also not allow the use of VBA macros.
7
u/ANeonBlueDecember Sep 07 '20
This is the point I came too look for.
Given a choice I would use Python all day. Iâm stuck with VBA though because thatâs what the IT department allows.
0
Sep 08 '20
[deleted]
1
u/Im_Bigger_Than_Staan Sep 08 '20
I understand, but it should noted that anything foreign may not be allowed.
10
u/Sahmbahdeh Sep 07 '20
You should learn Python, because just about anything you need to do with Excel can be done with Python or VBA, and Python can do other things VBA can't.
7
u/KineticNate Sep 07 '20 edited Sep 07 '20
The beauty of VBA is the code is attached to the excel file so when others need to use the code they don't need coding experience or additional progams just send the file with a button on the page to run.
Python is more efficient so if its just you using it I would do python.
Tip...VBA is really easy, just open the "Object Library" and it gives you all the methods and properties.
11
u/mprz Sep 07 '20
2020 lads. Not one mention of PowerQuery that is included with Excel and offers soooo much without the need to actually learn a new programming language - you of course can, but even using only point&click gives you a fuckton of ways of automating thing. Add this to Office365 and you have automation beyond what's possible without very extensive Python training and years of experience. PowerQuery+PowerAutomate offers the best balance today when it comes to seamless automation of the most what is today done manually in the corporate space.
Edit: after a week spent on PowerAutomate and PowerQuery i was able to move inventory management for the company I am working for from requiring 3 people to be 100% automated.
3
u/ImperatorPC Sep 07 '20
Yes, is actually amazingly powerful. I've done some similar work. Also was able to utilize it to reconcile two different systems and it was a many to 1 relationship. Combing several different sources of data in all different formats. If I can do it in power query I don't touch python. For deduping I'll use python, for APIs python.
2
2
u/Crypt0Nihilist Sep 07 '20
I agree with the power, but have found PowerAutomate a real dog to use at times. I've had data types change on me for no apparent reason and after a lot of searching, found that I needed to convert something that started its life as a numeric back into a numeric. Empty cells throwing an error instead of being treated as a 0 caused me a headache too. I know you can get the same issue with Python, but Python usually provides very helpful error messages, the ones from PA and the PowerPlatform, often not. "Error. There was an error." Changing data sources etc is also much easier in Python. God help you if someone changes the name of the SharePoint list you've referenced 200 times in your PowerApp.
It is very powerful and there's a lot to like about it, but from a maintenance and debugging perspective, Python is much better. Also worth mentioning, if Microsoft decides to deprecate one of your functions, that's your automation dead in the water when it was working fine yesterday. It shouldn't be an issue if companies invested some of the savings back on keeping an eye on the evergreening, but I doubt most do so to the degree necessary to avoid apps suddenly dropping out.
I see myself using the PowerPlatform as an integration tool, with some event-based automation. I am a bit cagey about the fragility of creating an enterprise edifice and vendor lock-in.
1
u/mprz Sep 07 '20
Agree with all of your points. However. Did you know you can create PowerAutomate flows from Visio? Or Excel? I've learned recently. And once you get the gist of what you need to look after it's pretty consistent. Comparing to Python it's like type declaring the variables. It has its own reasons - if you stick to it, this means easier troubleshooting if something goes wrong. And if you find something that cannot be done, or you need paid plugin then you can still use Python or - what I prefer - Powershell. One big advantage of the trio (PowerQuery, PowerAutomate, PowerShell) is.... Huge community that already using it or are moving towards. I still use Python for some stuff I'm doing but ability to deploy a solution that doesn't need any dependencies is very tempting and useful.
1
u/Crypt0Nihilist Sep 07 '20
I'm in the data science world, so I don't spend much time in spreadsheets. The golden rule is that your script needs to be the entire story of your data analysis, so I often have to go to extreme lengths to reformat sheets which would be much easier to spend 10 minutes in Excel.
I've mostly encountered PowerAutomate as part of the PowerApp / PowerBI triangle. I have heard great things about PowerShell, but hardly touched it. PowerQuery I have a passing appreciation of through light PowerBI use.
All in all, the platform is amazing at how an average business user can create powerful apps which integrate several programs. In addition to my concerns above is what I have seen the average business user do in the past! Maybe I'm being old fashioned and as long as people can get the output they need, it's ok. I can't help the alarm bells that go off in my head thinking how people abuse Excel, especially with VBA to get it to awkwardly do things it was never supposed to do and now the PowerPlatform is going to strap rocket boosters to that.
My take is that we absolutely should be using the Power Platform to be automating the boring stuff, but I want someone like you doing anything much more complicated that copying attachments to a directory, not most of the people with whom I've worked!
2
1
u/sancarn Sep 20 '20
Biggest issue with Power Automate, I've noticed, is that it is fine as long as you are wanting to do something which is built in. If ever you want to do anything which is "new" it becomes hell.
Some things I've done in VBA which I can't do easily in PowerAutomate:
Eastings Northings to Latitude Longitude conversion
Send emails based on Outlook calendar appointments
Scan excel files in a folder based on file naming convention and report back on various statistics (this is doable, but it is hell to build in the power automate web ui in my experience)
5
u/tragluk Sep 07 '20
When I use VBA in my excel files, I can send that excel file to anyone with excel installed without worrying about it. When I use Python, I'm pretty limited to the number of people I can send it to.
Also, for the 'larger' Excel files you might consider learning Access as well. When people talk about spreadsheets that span 10s of thousands of records that's usually a spreadsheet that should have been converted to a database a long time ago.
2
u/ImperatorPC Sep 07 '20
Power query, it can handle hundreds of thousands of lines. Access is good if you want to store information and access it later. But power query is super powerful for processing system extracts and combing multiple sources. It's also easier to learn. I use both but for most use cases I would recommend power query.
2
u/ghostfacekhilla Sep 08 '20
Power query can handle millions of rows. I built a spreadsheet one that worked with 50 million rows and just emailed it the the head of accounts my to work with. Crazy how far excel has come.
1
Sep 07 '20
Can't you just compile your stuff? That's what I do at work. None of my colleagues have Python installed, but they like the stuff I come up with every now and then.
5
u/foresttrader Sep 08 '20
This question get asked a lot on r/excel. I have about a decade experience with Excel working at corporate (risk, modelling, and finance) jobs. My learning curve was like this: Excel -> VBA -> PowerQuery -> Python. Once I discovered how to integrate Python with Excel, I never looked back. I rarely use any VBA or PQ nowadays, because Python can do everything I want with Excel, and much faster and better!
I'm also a hiring manager at my team, and I can tell you that my preference is Python over VBA. VBA is just a dead language now. I encourage my team to learn Python and provide training to them.
If you are going to need to process large datasets sometime in your life/career, Python is no doubt a better choice. VBA/PQ/Excel just don't have that kind of capability. In addition, you can do a lot more automation, not just Microsoft Office related tasks, but pretty much everything can be automated with Python.
Automating the boring stuff is a good book for learning Python, but it covers tons of stuff outside of Excel. If you are interested in learning just how to use Python to automate Excel jobs, this site provides a bunch of tutorials on how to do that.
4
Sep 07 '20
I have experience in both, I know Python better. I find Python to be best, itâs quick and easy to scale up. Itâs also super useful (obviously) aside from Excel so the skills go way beyond one application.
I donât remember the package ATBS uses for excel but you can use Pandas too which I find much more efficient.
3
u/damonator4816 Sep 07 '20
Python has an entire library dedicated to excel, which is much better than VBA (in my opinion) because you can implement tasks outside of excel in the same program.
4
3
u/xphlawlessx Sep 07 '20
If you have the interest , learn both. If you're not that into it flip a coin. They're both good choices.
1
u/ojedaforpresident Sep 07 '20
VBA is great for Excel stuff, that being said, you can do anything with Python and it's not much more difficult to learn. There's a couple of packages like xlswings that let you manipulate Excel files.
1
u/DiaperDan1992 Sep 07 '20
I work for a large company in the utility industry, and have used quite a large amount of VBA (7k plus projects). I would recommend Python in every instance. VBA seems to be used by industry due to it being way behind in the software department.
1
1
u/baetylbailey Sep 07 '20
VBA for MS Office. With VBA, you can get going immediately without installing and learning Python's toolset. Also, Python's interface with Office gets clunky the deeper you go into it.
For heavy calculations, heavy data processing, and of course non-Office programming; Python.
1
u/Kevcky Sep 07 '20
Depends whether your automation will need to be rolled out across the organisation. If multiple people need to use it and arenât necessarily adept at python, choose Excel. If you donât need to roll it out at scale and do it yourself, you can do python for convenience.
1
u/ImperatorPC Sep 07 '20
What are you trying to do? I find most excel tasks can be accomplished well with power query. I do use python was well but power query is super powerful at transforming data and combing multiple sources of information. Sometimes I'll use a combination of python and excel.
1
u/Eze-Wong Sep 07 '20
Definitely don't limit yourself to one tool. They are all good in their own respects.
Sometimes you want to crack out the excel and just do some straight formulas. Sometimes you want VBA scripts to reuse on certain tasks. And python is that magical swiss army knife that does everything pretty well. They do similar tasks but cannot entirely replace the other.
I've seen people use python for everything. I've done it myself and you get it into issues where for example there's 1 change to a dataset, and you need to rerun the script. Only you have the script locally so if you go on vacation, they are sitting ducks (Obviously a high-tech company would not have this problem). If lets say I had learned VBA and attached the functions to the spreadsheet, they wouldn't need me, and I wouldn't have calls during vacation.
Despite all that, if you are limited resources in learning something. I would definitely recommend python over VBA. Simply because if you get good at it, your salary potential is much more. Excel VBA is a dime a dozen and frankly is a bit outdated. Python is the in thing and more versatile.
1
1
u/Random_182f2565 Sep 07 '20
I know I'm super late, but I personally started by learning VBA as I was working with excel files, then I learned python, and the library to work with excel files openpyxl.
I can help you if you get stuck.
1
u/guillermohs9 Sep 07 '20
Here's my two cents: I used a couple of macros on Excel that filtered and sorted data, the usual. But it was really sluggish when dealing with big worksheets. I used Python (with Pandas) and the difference in performance is huge.
I'd say you don't necessarily have to choose one over the other, but Python has a much wider scope for use cases. If you deal a lot with Excel, you certainly can take advantage of both.
1
u/takishan Sep 07 '20
Python is way easier to learn and more useful than VBA. Although if you are looking to automate typical office suite things, I'd look into Google Scripts App. It's basically VBA but with Javascript instead. (Along w/ a whole bunch of interesting things you can do)
I've automated a lot of stuff for a couple organizations using it
1
u/vb-Automation Sep 07 '20
Both are well documented if you think there is a chance to use what you learn outside of MS office I would go with python there are libraries that make it work with almost limitless of entities.
1
u/tennisanybody Sep 07 '20
Do both. Automate the boring stuff is free. Excel is for employers and for excel I strongly recommend you learn basic VB which wonât be an issue if youâve got python down.
1
u/corezon Sep 07 '20
I was under the impression that Microsoft was working to add Python support to Office so it might be good to learn the basics.
1
Sep 07 '20
If you want to learn a new new language and be able to both automate stuff in MS Office and use language outside of it I would recommend JavaScript as Microsoft now has native JavaScript API
1
u/the_battousai89 lifelong beginner Sep 07 '20
Anything you can do in excel, is easier with Python. Use openpyxl or pandas, and be ready for your mind to be blown.
1
u/saltyhasp Sep 08 '20 edited Sep 08 '20
For what it is worth, I've used both. If you want to write some small piece of code for excel that is less than say 100 lines, then probably VBA is the way to go. If you want to do something more complicated that ends up being 100's or 1000's of lines of code or more, Python all the way. The exact cross-over point is up to you of course.
Another way of thinking about it, it comes down to whether your adding some feature to a spread sheet and it's mostly a spreadsheet thing or if you writing substantial program and most of the work is about programming with some ability to read or write data to excel or another office program.
The issues with staying too long with the VB approach: (a) the applications and development environment is a mess, (b) they don't scale in terms of code size and code integration, (c) you don't have good library support, (d) you can use version control, difference, and other development tools very easily, (e) it does not play well with running multiple instances of the same system, or using parallel computing, or porting to compute clusters such as Linux clusters. (f) they tend to be fragile over time as versions of the software change. Just say no to VBA for any major programming project.
By history again for what it's worth. Use to program in C, then switched to excel and VBA for awhile because C was too time consuming, then after a few years of that and many of the frustrations above, I switched pretty much to Python. I still use spreadsheets to create the input data and to process output data. You can do that by reading and writing directly to the app, or via the file format and appropriate libraries, or using a neutral format like CSV or HTML or some other format depending on what you need.
1
u/mistanervous Sep 08 '20
Learn python and the libraries pandas and xlwings. I use that combination to extensively automate the interaction with excel workbooks.
1
Sep 08 '20
Still don't know Python yet (Automate Boring Stuff is one of my next programming books to read), but I use R to automate a lot of my tasks, especially involving combining multiple files. I'd like to get better at VBA, but I use macros to format an Excel sheet when I need to do it on a periodic report. Usually R puts the data in the format I want and the macro makes it pretty by formatting and making pivot tables for me to send out.
I know Python is similar to R and I'd say use it when you are changing the data or have more than 1 input, use VBA when you need to present it in a way you can send it out, and just use Excel by itself if you are doing a simple one-time task. If I am wrong in this, please call me out, but that is my 2 cents based on what I know.
1
u/cubinx Sep 08 '20
Based on my experience, learn only the most used formulas in excel, then move to python. Excel definitely has lots of limitations, and slow.
1
u/Clutch_Pineapple49 Sep 08 '20
Look up xlwings package. That said, I believe python is better than vba
1
u/kessma18 Sep 08 '20
if you want a job, excel.. I have done 12y of excel for a large investment bank and despise excel but I also learned that it's near impossible to convince people to start dropping it in favor of python, even if there are a million reasons and it saves money. people doing dumb stuff in excel is probably one of the largest job creation machines out there...
I was an independent contractor 3 years ago and I live in a wealth area of California and a wealth management firm was hiring and one of their main tasks they listed was to get data from bloomberg and rebalance. I was super highly over-qualified for this having worked with the bloomberg api directly but I pitched the owner that I can reduce her team of 5 excel jockeys (making tons of manual fuckups that annoyed clients) to an automated job and have someone in my team be available for maintenance, reducing her overhead by ~90%. I gave the presentation and after 15m she just stared at me blankly. she had no idea that her industry was about to be reduced to dust and the only reason she was in business because all of her clients are the type that insist on meeting with people in dark blue suits to sell them overpriced managed accounts.
needless to say I didn't win her buiness and she upped her team of excel jockeys to 7.
people reach for a spreadsheet for anything, not realizing all the cans of worms they open but they happily open more cans of worms and combat by increasing manual labor until they are disrupted by a competitor.
1
u/throwaway0891245 Sep 08 '20 edited Sep 08 '20
Learn both, you want to start seeing languages as tools and not some sort of thing you're married to.
VBA is good because it's quasi-portable, when you're working in a place with not so good infrastructure or IT sophistication then this is the way to put in some fancy logic. Since you can make SQL Server (and maybe other relational database?) connections, in a lot of cases you aren't necessarily stuck in a client-only situation. You can set up a client-server situation.
But the problem with VBA is that it's not a great language. What I mean by that is that the implementation of the language on the Excel version I was using didn't even implement boolean short-circuiting. The built-in editor is pretty terrible. It's not a powerful language in terms of the constructs you can use.
Python is better in a lot of ways - the ecosystem is a lot fuller and better supported, you get a lot more freedom in expression and the styles you want to code. However, Python has always been terrible in terms of packaging and unless you can set up some sort of web app and get dedicated server resources + operations support from the IT department, you're probably going to run into a bunch of issues distributing your thing. Seriously, in a low tech organization I wouldn't even bother. A lot of technology issues in organizations aren't even about the technology, if that makes any sense.
I've tried out a lot of languages now, maybe a handful I've done something non-trivial with but just lightly playing around I've gone through all of the mainstream ones. They're all pretty much the same in general, though it's pretty clear different languages definitely focus on some concepts more than others - like how null values are handled, typing, how memory is abstracted, tradeoffs for performance vs ease of use. You can write a complicated graph theory algorithm in any of them, in theory you could use a language to do anything really - even some complicated deep neural net framework (though whether or not that would have acceptable performance is another story).
It's not about the language, it's about the problem and choosing the language that allows you to solve that problem in a way that makes the most sense - and a lot of problems aren't even about the "technical" stuff. Don't fall into the beginner trap of thinking "X language is better than Y language", because it's more like "X language is better than Y language in this situation". Especially don't fall into the trap of "X language has a steeper language curve, and so people who are advanced or 'technical' can program in X language". The reality is that good programmers can do anything, with any language, in a reasonable amount of time - because the reality is that all languages are pretty similar in an fundamental way.
EDIT: Oh yeah, and to your point - it really doesn't matter if you do the VBA course or the Python course first. You just need to make sure to see it through because the first language is probably the hardest language to learn regardless of what it is. I'd probably go with Python though because r/learnpython is pretty active.
1
Sep 08 '20
I use Python all the time in conjunction with Excel.
Here's an example of an ongoing project at work: a coworker wrote a model in Excel that's got calculations going across 3 tabs. My job is to plug numbers into 5 input cells in one tab, extract the resulting output values in another tab, and do annual means/sums of values from the 3rd tab. Repeat 409 times.
I bet someone has a more elegant method for how to do this, but it works and I don't want to test new code lol. I use a combination of 3 packages: wincom32, openpyxl, and pandas. In a loop for each city on my list:
With openpyxl and wincom32, plug the numbers into the appropriate cells in the model file, refresh the file (just saving it won't actually change the output values! You need to close then reopen it ) and store the results in a unique output file.
I then use Pandas to read the resulting output file and do a groupby aggregation sum/mean as needed per column.
Append the groupby results to a final output data frame and repeat.
Finally, I write the results of the appended data frame out to a final excel file to send on to the rest of the group. If you want to add colors or formatting, I think you have to do those by hand. But otherwise this is a fully automated process and the only chunk of code I ever need to change is the names of the input and output files.
1
Sep 08 '20
In Short:
Excel cannot handle big data as well as python.
VBA is not as efficient as python as well.
1
u/transitionyte Sep 08 '20
Add Python to your arsenal. I am working with Google Sheets formulas, functions, and apps script. While those are great, I find myself sometimes saying, "I could probably do that faster in Python." Python is extremely useful, and since you're already familiar with VBA, I think you'll be really impressed with Python. You can use it with Excel, G-Sheets, and to automate your OS tasks. All learning is great, but I give the edge to Python
1
u/saltyhasp Sep 08 '20 edited Sep 08 '20
Other thing to consider... use both. One can use Excel as a nice front end then run python code to do the actual work. This is along the lines of what I've said before... use spreadsheets for what they are good at and and use Python for any major programming.
By the way, you can do this in a bunch of ways. Python can define COM components that VBA can call, or you can just run it as an application via a system call for example. This is in addition to Python being able to manipulate Office applications via COM, or just being able to read and write various formats that office programs can understand -- xls. ods, csv, tab text, text, html, etc. So there are a variety of models... from excel being a frontend, to excel being a data source to read, or a target to write output into. Same goes for most other office apps.
I actually don't tightly connect my spreadsheets to python. Instead I normally save the spreadsheet in a format python can read -- like csv, then write the data out into a new file in a similar format. That way I don't have to write stuff in VBA and am not tied to a specific spreadsheet or locked into windows either or need to have licenses for every system you want to run something on. I've also done something similar using ods format, and I'm sure you could do it with xls or xlsx probably too. This is really a powerful approach. It allows one to use Python to both do rapid application development, and to run parallel computing jobs on large servers if needed, but both prepare you inputs and process your outputs on the desktop with things like spreadsheets and statistical tools like R. Best of both worlds.
1
u/sancarn Sep 20 '20
Both do do the same. Both are general programming languages and you can do everything in VBA that you can in Python. There are times things are easier in python. There are also times things are easier in VBA.
Ultimately, learn both, but really don't focus on the language at all. If you get good at one language, you'll be decent in most :) Focus more on programming techniques, data structures and algorithms.
1
1
u/farmboy_du_56 Sep 07 '20
If you only need to automate really repetitive tasks, Python is probably a better option. However, Excel has the advantage of allowing you to automate things (even though I find it a bit more complicated than python), while keeping an easy to use and clear interface, and being able to seemlessly transition to working manually on your spreadsheet.
1
u/vectorpropio Sep 07 '20
You want to automate things on your actual work or to learn a new skill?
Python have a broader user case, but if you need to automate things that are generated and consumes as excel and that's the only thing your organization is using, then go with Excel.
1
u/nerdmor Sep 07 '20
I'd puta a caveat on that, as complex VBA tend to make Excel panic/crash/get really big.
When you get to the point in VBA where you want to turn screen updating off to save resources, you'd be better off with Python
-4
171
u/Tatworth Sep 07 '20
If all you are doing is Excel and already know VBA, then that is probably good enough.
Python can do lots of stuff unrelated to MS, though, which is an advantage.