r/learnpython • • 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 😊

362 Upvotes

117 comments sorted by

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.

35

u/DizzyReading9015 Sep 07 '20

For context I haven’t started either of the courses but judging by what you said I think it’s safer to go with Python first since it can automate Excel, MS Word, PowerPoint? Thanks!

132

u/The_Mann_In_Black Sep 07 '20

Go with Python. You can learn VBA if you need to for specific things. I did a corporate finance internship where we had some moderately large data sets (60,000 rows). The laptops we had couldn’t really run a vlookup between two days sets that large. It would take hours to run because it would crash.

8 lines of code in python was all it took to create a program that ran in 10 minutes and never crashed.

30

u/stilterfish Sep 07 '20

You mention vlookups, but have you heard about Excel's Get Data / Power Query funtionality. I use it to semi-automate data work. There is also a lot of power in Excel's Power Pivot, but I don't recommend using it unless you are on a 64-bit version of Excel.

13

u/zurtex Sep 07 '20

One of my colleagues did a presentation on Power Query recently and I was rather underwhelmed so I guess my expectations were too high.

It seems useful for running small procedural tasks in Excel on data whose quality is already fairly high. And running those tasks way more efficiently than typical Excel functions.

When I think about the typical Extract-Tansform-Load data pipeline it seems to just fulfill Transform okay and Extract and Load only if your data source and destination is something well supported by Excel (which is a lot but dwarfed by Python 3rd party libraries).

But even then I'd be worried about maintaining a Power Query script if it got longer than 30 lines or so. The language doesn't seem to have higher level abstractions (branches, loops, user functions, etc..?) to keep things from getting out of hand?

Maybe I'm missing something? Maybe there are better use cases for it?

11

u/stilterfish Sep 07 '20

I would generally agree with your assessment. I come from an accounting background, and regularly see a criminal use of Excel. Most of those CPAs will never learn to program, but I can usually get them comfortable using Power Query within an hour of working on a project together.

Use cases are often one-off analysis or periodic reporting. We convert client report exports into useable data tables, combine them with data from other sources, and then model it into something that can then be used for custom analysis and reporting, or formed into period-end journal entries that can be loaded back into the client's financials.

Nowhere near as neat as Python, but also nowhere near the learning curve. I just started learning Python, finished a four week online college course, and still have absolutely no practical use for it. Starting "Automate The Boring Stuff" now, hoping its more practical.

4

u/ImperatorPC Sep 07 '20

Use cases are far from one off. I've been helping out controlling and tax departments semi automate a lot of work utilizing power query. Inventory analysis and hold accruals, state tax apportionment are some.

2

u/stilterfish Sep 07 '20

I definitely have recurring use cases, but I've been trying to get away from them, especially anything more frequent than monthly, in favor of Power BI or hopefully some Python and/or Power Automate.

6

u/[deleted] Sep 07 '20

Power query is effectively the same and Power BI's data load. I wouldn't worry about connecting... it supports ODBC and such so any way you'd connect w/ Python it'd be basically the same interface.

2

u/zurtex Sep 07 '20

So here are just a few of ways I connect and extract data with Python in production that didn't seem obvious Excel/PowerQuery could handle:

  • Custom Rest API

  • FTP / SFTP / FTPS

  • FIX Protocol

  • Receiving Email from SMTP gateway

  • Binary Stream over a Socket

  • Custom Log, XML, JSON, HTML messages

  • Custom msgpack and binary messages

2

u/randiesel Sep 07 '20

PQ is great for ad-hoc transformations.

I regularly use it to clean and transform data to fit etl pipelines that already exist. It’s weakness is that there’s no real way to save the process and easily apply it to another file.

2

u/ianitic Sep 08 '20

PQ has function declarations. It’s a functional language based off of f#. As a user of both, if I need to one off clean and transform data or if the final form is an xlsx anyways, I’d prefer PQ over python.

Looping is done with recursive functions though...

2

u/LogChief Sep 08 '20

PQ can do way more than ETL. It provides user interface solutions to some of the most complicated excel formulas. It can also pull millions of rows of data from many different sources. Once the query is loaded, I can duplicate and ETL it multiple times to produce smaller tables with aggregated data. The thing I love the most with PQ is that I can take data with multiple headers and turn them easy to sort tables. Add this to power pivot, and I can analyze metadata for anything.

3

u/The_Mann_In_Black Sep 07 '20

I believed I used it in college, but didn't have any obvious applications because I wasn't working with any big data at that point. Mostly modeling, which meant lots of cell references, but simple math and hard coded numbers

They had us using 2-3 year old laptops at this job. My boss was the one doing this vlookup and she would stay until 6-7pm because it would fail so often and have to restart. She also couldn't do anything while it was attempting to complete it.

Unfortunately, I have left data heavy roles and now most of my automation is mostly cleaning data and attempting to download the automation and filtering. I could probably do it in excel easily (through vba), but my excel skills are so rarely utilized I am beginning to lose them.

2

u/cheyen0609 Sep 08 '20

If using VBA, move the data into arrays, try not to work with native excel objects for calculations there's too much overhead. Disable screen updates. These make a huge difference.

1

u/optionexpert Sep 08 '20

Totqly agree

1

u/GT_YEAHHWAY Sep 08 '20

Isn't a license needed for power pivot?

3

u/stilterfish Sep 08 '20

Not at all. Power Pivot is a COM add-in that is already part of Excel. You just have to enable it and it will show up in the ribbon.

Note of caution though, Power pivot works great on 64-bit Excel, but is prone to breakage on 32-bit. The only thing it seems to break is itself, but that gets really inconvenient when all of your data model files stop working.

On 32-bit Excel, I'll use Power Query. I build tables that load up looking like pivot reports. Then build custom tables that support my visuals and hide those tabs.

16

u/Cleover453 Sep 07 '20

Man 8lines only. I am complaining because I can’t do a good twenty lines of coding.

18

u/ThalanirIII Sep 07 '20

Most of programming, especially in python, is googling the right words to see how other people have solved your problem. And if you can't find an answer, broaden your terms: 'python merge 2 spreadsheets and vlookup' could be simplified to 'python vlookup' or 'python spreadsheets'

7

u/Crypt0Nihilist Sep 07 '20

Import pandas, two lines for the inputs, a line for the output, a line to do the business and a gratuitous use of three lines for comments :)

1

u/[deleted] Sep 07 '20 edited Sep 07 '20

Most of programming, especially in python

Most toy problems maybe.

edit: it's a programmer meme/joke but let's be honest, advanced programming is not just copying and pasting other people's code and hoping it works.

10

u/The_Mann_In_Black Sep 07 '20

It was very simple. I just copied documentation from Pandas. I only had to change the column names of my data sets and import them as dataframes. Pandas Merge is the function I used. Very simple, but can save a lot of time.

2

u/BaconFlavoredSanity Sep 07 '20

Xlookup is much more versatile and less resource intensive than v or h lookup. It’s my new favorite toy.

2

u/my_password_is______ Sep 07 '20

The laptops we had couldn’t really run a vlookup between two days sets that large. It would take hours to run because it would crash.

then you don't know what you're doing
I regularly run vlookups with 200k rows and it takes no more than one minute

2

u/i_suckatjavascript Sep 07 '20

Holy fuck, 8 lines? Share your code please! 😁

1

u/The_Mann_In_Black Sep 10 '20

import pandas as pd

file1 = "filepath1"

file2 = "filepath2"

df1 = pd.read_csv(file1)

df2 = pd.read_csv(file2)

inner_join = pd.merge(df1,df2, on ='Company Name', how ='left')

inner_join.to_csv("csvname.csv")

1

u/TheThingsiLearned Sep 07 '20

Index and Match

1

u/P-dawgs Sep 07 '20

This. I don't know about VBA but I have to read and filter out details from a 300+ mb (9L rows and 100+ columns) csv. It takes me about 5 mins just to open the file in excel. And filtering out details is impossible.

It takes 10 mins in total to write the code and filter things out in python

3

u/The_Mann_In_Black Sep 07 '20

Exactly, I got started with combining multiple excel sheets that were generated each day and then some poor sob had to copy paste that onto a master file. It took all of 5 minutes to do it each day, but no one had done it for 6 months.

I realized I could use glob to combine and then filter that master sheet extremely easily. My biggest mistake was telling them I could do it as fast as I could.

3

u/P-dawgs Sep 07 '20

My biggest mistake was telling them I could do it as fast as I could.

Story of our non-tech lives. because we can "automate" and simplify our lives by using Python (which I learnt in my own personal time), we get dumped with more work

2

u/[deleted] Sep 07 '20

That's why you have to be strategic about when to let them know you're done with your work...

1

u/space_wiener Sep 07 '20

This is why I started my python journey. Laptop couldn’t handle big spreadsheets.

I still use excel for most things as it’s faster, but if it’s a big sheet or even a semi complex chart I’ll use excel.

For the OP...if you current job has you using excel I’d s start with that. Excel is a lot easier to learn (unless it’s the vba part because that sucks). And unless you are going for a specific job path that uses python, excel is better to learn. But again depends on your job you have/want.

0

u/[deleted] Sep 07 '20

[deleted]

4

u/The_Mann_In_Black Sep 07 '20

Interesting, I did not know that. I picked up Python just because I thought it was a good skill to differentiate me in finance. I've been looking at learning some other languages for creating applications and websites, but was worried it would be a waste of my somewhat limited free time. Programs are great, but if you can't create a GUI or way for non programmers to use it then it won't get the traction it should.

4

u/eloydrummerboy Sep 07 '20

Python is likely to be more difficult, and this would also be harder to teach others what you're doing, explain how it's done, and set up their computers so they could do the same things (even if it's just running the code you write). So there's a lot of "organizational", company specific, and task specific details that should be taken into consideration.

With VBA and excel, you could build a spreadsheet template and share that and anybody should be able to run it, no problem (unless your company's IT department locks it down, which you might be able to put a ticket in to get this ability enabled). If you choose python, you will need to install python on the machines of everyone needing to run the code, worry about package management, how to push updates, etc.

So python is much more powerful and a great skill to learn. But Excel and VBA "might" be a better fit from a manageability and change management perspective.

Just some things to consider.

2

u/Natural-Intelligence Sep 07 '20

That might not be that big of an issue always (though sometimes it is). Just have an env file somewhere that can be easily installed. Basically you just need to install probably Conda and the env file and you are good to go. And inform IT to install those to every new computer in your team/unit so that won't be a problem (they should have an installation script if it was still a problem).

The problem with Excel and VBA is that they are pain to maintain and cannot be really automated. On the other hand, I can create a Python package, put it on a network drive, say to my colleagues to pip install it (one-liner that can be put in a script if they fear terminals) and maybe share a Notebook template that demonstrates the use. I can maintain it on GIT, have automated testing and every juicy feature while it is easy for them to install and update. They just need to know how to use my high-level easy to use API and how to open a Jupyter Notebook.

I think Python is much more manageable than Excel if you have a little technical expertise to do things properly. Too often I have lost my faith while maintaining Excels that suddenly corrupts while the VBA is baked into the files and the previous "version" (the "v16_final_copy_without_password.xlsx") has unknown amount of changes. And don't get me started with VBA's error codes.

3

u/eloydrummerboy Sep 07 '20

I agree completely, except for the "little technical expertise". I'm only guessing, but the fact that OP is considering Excel hints that his coworkers are likely far from "a little technically experienced". I with with many engineers of an older generation who use VBA and Excel to speed up a lot of number crunching. Yet I know for a fact, as a coworker of mine has been trying for years to get the group to move towards more automaton using python, that is an up hill battle. Completely depends on the team, management, and environment.

I think people like us often overestimate what's "easy".

To illustrate my point, the team in on has an app for people to search. These are all engineers, so smart people. I suggested we use the * as the wild card, but require a * at the beginning or end of the user wishes to match things before or after, to give better flexibility.

Example: Given the terms "apple", "apple pie", "apple sauce", with my way of you search "apple" you only return "apple". But if you want the rest, just use "apple*".

With the other way, searching "apple" would return all of them because the wild card would be appended behind the scenes. There would be no way to filter out the rest of the results.

I thought this was simple and easy and users would appreciate it.

Narrator: "It wasn't. They didn't."

2

u/Natural-Intelligence Sep 07 '20

Well, you have good points. But moving to more automated ways of doing things requires OK infrastructure. If you try to get your (non Python programmer) people to program Python without providing them the infrastructure to get stated, you run to a wall. If you make it easy to get started with (ie. provide easy way to install the needed stuff, have a convenient package for your domain etc) you should have no problems to get people excited. Maybe I underestimated the knowledge needed to put this together but you need one knowledgeable person to able to set up this stuff.

I have done bunch of these things and it's nice to see how the snowball effect takes place. Suddenly everyone is interested in Python and people all around want to try my Python toolbox as it's just so handy and easy to use. Many of these people have never programmed before.

Just make Python easy to approach.

3

u/eloydrummerboy Sep 07 '20

Agreed and you're preaching to the choir. I'm curious what size company you work for. Mine is a large fortune 500 type. It's typical that something that takes me literally minutes on my personal computer takes days or weeks to get done at work. "Put in a ticket"

So if OP can do the things you're talking about, and there's a younger workforce, or people willing to learn new things, and they have a culture of change, then 100% go with Python. But OP should consider their work environment and politics a bit before deciding. It's not an entirely technical decision, and I would say in many places the technical aspects are less a deciding factor. The best technical solution that IT will block is beat by a crappy solution that gets implemented and used.

2

u/Natural-Intelligence Sep 07 '20

I work in a mid sized bank. Our team is highly technical but most of us are not from CS background including me. Our IT is fortunately fairly flexible and we use what we want. I'm not saying Python is solution everywhere but business people tend often have unnecessary obsession on Excel. In fact I come from a business background thus I understand why. It's just that Excel make things seem easy but in reality it's inferior in almost any complex task and it is especially poor in automation. Business people often lack the exposure for better tools and often don't recognize the problems in Excel. It needs someone with enough will and knowledge to show better ways to do things before people will switch on those.

But I agree: depends somewhat on the culture. Maybe it's just that I would not work in such an environment that prevents me using tools I want to use.

1

u/selah-uddin Sep 08 '20

you are kind of on a biased ground here since you are in r/learnpython

but generally speaking learning python imo would open more doors for you than learning Excel

1

u/warhammer1989 Sep 08 '20

Vba is used in all microsoft office programs including outlook, use the refererece library in the vba window to see all the different libraries vba can intergrate with. For example i got a vba script that takes a spreadsheet converts the selected sheets to pdf, creates an email attaches it and sends to a list of contacts all through vba. So if your planning on just using ms office application it may be easier to use that.

4

u/thecave Sep 08 '20

As someone who came to Python through Excel it’s not good enough.

Even if we’re talking about google sheets which removes the heavy limitations of the MS framework the difference is this:

Excel holds your hand getting up and running and provides a simple infrastructure that makes it quick and easy to set up simple things.

But the more complicated it gets, the harder Excel makes it.

Python requires more understanding to start to use. And it requires more setup to do routine things. But beyond that it’s like a pickup truck compared to a bicycle with training wheels.

Both built in functions and modules provide vastly more flexible tools that are ironically much more consistent to use. As things get more complex, you just keep going - it doesn’t get profoundly more complex.

Tl;dr: Python takes a bit longer to get up and running but makes it much easier to do much more complex things.

Also learning excel is trivially simple once you know a general programming language.

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.

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

u/e-rekt-ion Sep 08 '20

Corey Schafer’s pandas series on YouTube is excellent too

26

u/[deleted] 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

u/[deleted] Sep 07 '20

Excel sucks at web scraping, for example. :D

2

u/[deleted] 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:

  1. you can program outside a software suite.

  2. it is a stable environment. VBA tends to crush or to do mysterious creepy things.

  3. it has a huge, active community, compared to VBA.

  4. it is well maintained and documented programming language.

  5. it has tons of useful libraries.

  6. it will be the top programming language for the next years

  7. 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.

  8. 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

u/DustinKli Sep 07 '20

Definitely something to inquire about for many companies or organizations.

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

u/[deleted] 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

u/zorclon Sep 07 '20

I'll have to check this out.

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

u/[deleted] Sep 08 '20

Actually good advice that's based on corporate experience.

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

u/[deleted] 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

u/[deleted] 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

u/ImperatorPC Sep 07 '20

Yes but a lot of companies don't give you that freedom to install it.

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

u/Hateblade Sep 07 '20

Will your code ever need to run outside of Windows?

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

u/martynrbell Sep 07 '20

I second everyone out there in the world who mentioned pandas

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

u/[deleted] 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

u/[deleted] 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

u/[deleted] 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

u/[deleted] 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

u/yellowmonkeyzx93 Sep 07 '20

Good question. Was wondering the same thing too!

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

u/[deleted] Sep 07 '20

Lol VBA what year is this