Macro in the Shell

Say your finance organization has some workbook that contains fancy accounting triggered by a VBA macro, the calculation of which you need in your own script based data pipeline (but which also may not be in perfect order⁴…). You don’t want to go through the effort of reproducing (or worse, be responsible for maintaining) the complicated (and potentially changing) logic that lives within the spreadsheets / macros⁵. You’ve resolved instead to simply “call” finances spreadsheet / macro / logic in some way.

Often the VBA from finance will live in a macro enabled workbook (.xlsm file). To trigger it programatically, you generally want to write a wrapper VBscript that can then be run through the shell (Stack Overflow thread)⁶. The VBscript will open the workbook and trigger the macro(s) of interest⁷.

Note that saying “Use a shell script” is, in a way, almost always an answer for how to incorporate another technology into a pipeline. This is more just a reminder that many tools that are designed more for interactive use often also have a batch mode⁸. Here I’m writing about triggering VBA macros, but integrating GUI based data piplining tools like Orange or Knime into your pipeline can be set-up similarly.

Passing in arguments

You can pass arguments to a VBScript through the shell (SO thread). Though given that you are already using spreadsheets, it’s also sometimes easier to write data to pre-determined locations or cells (this is often how the workbook was set-up to be used anyways).

Setting-up Gaurd Rails

There are many unavoidable limitations to any spreadsheet dependent data pipeline. But here are a few things you can do to keep things sane:

1. Think of your relationship with the workbook/macro/document in a somewhat similar way to how you might consider your relationship with an API or database you depend on. Get the owner of the document to think similarly – that is, as the owner of a standardized interface.

Identify which parts of the document need to stay consistent for your pipeline to keep working⁹, e.g. 

• Location(s) where new data is to be inputted (e.g. a database, folder of .csv files, arguments passed through the shell script)
• Location(s) where you expect to retrieve data
• Names, data types, and possible values¹⁰

These are not much different from the kinds of considerations that happen when collaborating on any data pipeline¹¹. The responsible party at each step has to adhere to certain structures about the form of the data as they expect it to come-in and the form with which it will proceed to the next step. Lines of communication should be open so that as changes occur to the tool, everyone (who needs to be) is made aware¹².

2. Set-up notifications / methods of contact.

Particularly if the pipeline is used in the wild¹³, make it easy to get notifications for when things do break¹⁴.

3. Basic optimization

If you are using spreadsheets or VBA macros in your data pipeline you probably aren’t worried too much about performance, but there may be a few things you can do to be more efficient.

For example, for the macro-shell-example, the VB script has as (headless) steps opening and closing the excel document after processing each deal. Therefore, processing five deals entails compute time spent on four unnecessary opens and closes. This wasted processing could be corrected with small changes to the VB Script.

Closing

Data Scientists should still consider their work in a context of growth & development. I am reminded of the Alan Watts quote:

“People who are responsible for technical development [must] be well-imbued with an ecological philosophy and see the direction of things so they will not keep perpetuating anachronisms.”

For further reading on how data scientists should think about integrating their knowledge into sometimes lower-tech organizational contexts, see the excellent post by Allison Horst and Jacqueline Nolis:

“Merge conflicts: helping data science students merge their advanced skills into existing teams…
What do we do about students trained in R and Python for jobs with Excel, Google Sheets and Access?”

Appendix