Become an Excel scripting wizard with this VBA Bundle


#1

Originally published at: https://boingboing.net/2017/11/11/become-an-excel-scripting-wiza.html


#2

Please, please don’t do this. There is almost nothing you can do with excel/VBA that you wouldn’t be enormously better off doing with Python/Pandas, and it’s not any harder to learn.


#3

Anything online? Can you use those to manipulate Excel?! Lemme know!


#4

Friends don’t let friends use Excel. Scripting it is double-stupid.


#5

So, there exists a GUI for Python/pandas that allows the average office admin staffer to generate reports based on complex queries, and make minor modifications to the input values on those reports?

It’s not that Excel is such an amazing tool for doing even somewhat complex data analysis; it spectacularly isn’t. It’s that Excel is ubiquitous, and has just enough functionality to handle higher-end tasks, without terrifying people who think opening a CLI window is l33t h4xx0ring. Or people who don’t want to learn the quirks and metaphors of yet another UI, and happen to already be familiar with Excel.


#6

For Python, this is a great place to start:
http://openbookproject.net/thinkcs/python/english3e/

Download the Anaconda Python distro here:

And work within the Jupyter notebook.
For Data processing/analysis use Pandas, which is documented in a few online tutorials and on stackoverflow.

You can’t (or at least shouldn’t) use Python to manipulate excel. However, the people @waterloonie is describing aren’t going to be messing with your VBA scripts anyway, so you can let them keep using excel for what they use it for, and then just save the spreadsheets as csv files where you can easily operate on them in Python, and save your outputs as csv files, which Excel can read. These processes are all easy to script and automate in whatever environment you’re in.


#7

Perhaps you don’t work in a corporate environment?


#8

Buddy of mine is a financial Engineer, he develops the models and algorithms for HFT (or did, I think he is portfolio manager now).

Excel/VBA was used at one of his early gigs because everyone knew it. It was what a 2 Billion dollar portfolio was running on. As they get bigger and more sophisticated so do the tools, and eventually they mostly don’t use Excel at all, try start developing custom tools in whatever flavor is most popular for their segment in the market (in his case a mix of C++ for performance and C# for utility and ubiquity).


#9

There’s a whole category of risk management dealing with end used computing apps and even a category for “spreadsheet risk” that applies to just what you described.


#10

Yeah… when the coding is left to the traders, you get a lot of usernames and passwords in that code…


#11

Not just that but business stability risk around proliferation of “critical” spreadsheets which no one even understands the accumulated craft of scripts within.


#12

This topic was automatically closed after 5 days. New replies are no longer allowed.