Member-only story
Call Python and R Scripts From Excel
Learn how to integrate your Python or R data processing tasks into excel
Preamble
I love wrangling, munging, and processing data in Python or R.
However, my employer does most everything in Excel. I wanted to take my Python or R code and integrate it within my Excel documents.
Furthermore, I needed to output the results directly into the Excel workbook.
I found I could implement this with minimal Visual Basic for Applications (VBA) scripts, and rearrange my Python or R scripts into apps.
If you are looking to streamline your Excel workflow with Python and R, then this tutorial is for you!
Overview
At my job, everything is split up amongst a bunch of different Excel sheets. Some are output from different software programs (which I do weekly), and some are pre-processed by the company themselves.
When working with these documents, I am either trying to automate a previously manual process, or improving on an existing Excel workbook that could benefit from some data processing with Python or R.
The order of operations that I have found work best for me are the following:
- Step 1 — Exploratory Data Analysis: Explore data in Jupyter notebook or R Markdown
- Step 2 — Create Functions: Group code into logical functions.
- Step 3 — Separate scripts: Divide functions into different scripts for each process within the code.
- Step 4 — Create App: Create a main script that imports functions from each process-specific script.
- Step 5 — Command line app: Ensure the app works by calling it from the command line.
- Step 6 — VBA Run script: Write VBA Script to call the app from Excel and ensure output is populated directly into the Excel workbook.
- Step 7 — Check Reproducibility: Ensure the process can be repeated by me or someone else weekly, to improve efficiency and streamline workflow.
