Python for Excel: A Modern Environment for Automation and Data Analysis
xlwings.org
xlwings.org
Seem to recall a ~2017 article about it...
Putting Python in Excel can potentially lift more analysts to proper developers or at least make the code base more robust. A lot of analysts' first language is VBA and then they move on to more serious development and land on a developer job.
I'm one of those examples. My first language was VBA and I dig a bit deeper into it (e.g. reading about moving pointers and general coding best practice, downloading rubberduck extension for VBA, etc.) and taught myself C++ and Python. Eventually I landed a BI developer job.
So they went Excel => .NET, and were served by .NET and COM APIs in Excel.
In modern Excel you also have Lambda, JavaScript and PowerQuery as an inbox option.
PowerQuery is also very good for data cleaning. Learned a few tricks here and there.
I know they've done some R integration with PowerBI, but it's mind boggling how they ignore the two most popular programming languages that heavy Excel users would also be interested in.
Power BI and Tableau are favoured by analysts who do not necessarily want to put down a lot of efforts to learn programming (which is fine).
The new dynamic arrays in Excel with Python and xlwings - https://news.ycombinator.com/item?id=20381083 - July 2019 (9 comments)
Call Python functions from VBA in all Microsoft Office apps - https://news.ycombinator.com/item?id=18166421 - Oct 2018 (25 comments)
Xlwings: Replace Excel VBA with Python - https://news.ycombinator.com/item?id=10304402 - Sept 2015 (1 comment)
Use Python in Excel without add-ins - https://news.ycombinator.com/item?id=8372329 - Sept 2014 (70 comments)
Much of the Excel target audience - office workers - don't have much control over their work computer let alone ability to install stuff like pip allowing downloads from an unvetted repo
VBA is terrible, but it is the only programming-like option for most office workers outside of IT/Engineering
At work, we want to build a simple database and app to interface against it. Asked our boss, asked the IT guys, got the same answer:
A : "Sure, but it has to be MS Access"
Q : "Why though, PostgreSQL / SQLite/ etc. works fine"
A : "Because we already have MS Access"
Q : "OK - could we at least get [programming language + frameworks] for the interface part"
A : "Use Excel and VBA. We already have that"
I am on a mission to eliminate Access... the day i can distribute office with out Access will be a glorious day indeed
No IT Dept should be telling users to use Access.....
This is no longer technically true of Anaconda. They’ve adopted a new licensing approach that prevents employees of companies from using the free edition. Please consider adding alternative install instructions, based on PyPI and pipenv or poetry, to future versions of your book. This would help a ton of data scientists out!
I’m looking forward to reading your book via O’Reilly!
References:
Discussion on r/Python with comments from the Anaconda CEO and team, https://www.reddit.com/r/Python/comments/iqsk3y/anaconda_is_...
Anaconda license change press release, https://www.anaconda.com/blog/anaconda-commercial-edition-fa...
Anaconda Terms of Service, https://www.anaconda.com/terms-of-service
Edit: Here’s an interesting comment on the Reddit discussion from the Anaconda CEO suggesting that Miniconda can still be used with conda forge: “You can download Miniconda, and change your conda config to use conda forge, and the Terms of Service do not apply to that. The ToS only applies to commercial usage of the package repository of packages we build, at repo.anaconda.com; it does not apply to community-built and uploaded packages at anaconda.org.”, https://www.reddit.com/r/Python/comments/iqsk3y/anaconda_is_...
While Anaconda Inc.’s licensing approach is currently confusing/frustrating, they’re correct that open source sustainability is a challenge. Projects need financial support as well as volunteers.
SciPy.org maintains Pandas, NumPy, Jupyter, iPython, Matplotlib, etc. They’ve buried a donation page on their website at… https://www.scipy.org/scipylib/donations.html
There are tons of resources like this. https://quantecon.org/
It's a big issue to build tools for others to use - an excel file on a shared drive somewhere might be developed by one person with python installed and elevated permissions but used by 50 with no python installation etc.
IT arn't going to adjust their policies for a small group unless they have significant organizational sway.
I'm sharing my experience of this exact problem we have right now. Users cannot install python on their machines, in an org of 60k engineers, asking for changes to a small group of users comes with heavy resistance from IT.
On these machines (most computers in most offices in every western organization) no software can be installed from online, and running any form of executable requires IT/Admin elevation.
It's like you guys have never worked in an office in the real world that isn't dev/tech.
It also does not require admin permissions in Linux, although most distros do not offer a happy path for that.
Not really sure how this is a rebuttal, maybe what you say is true on a default windows installation, not on an IT-dept managed system.
That said, VBA is slow.
Don't mean for this to sound like a testimonial for xlwings (I guess, it kinda is) - it's been very useful for us so I had to share! It's a great library, has great documentation, and I can't recommend it enough. However, if you're after low latency* for real-time interactions across a large dataset, you'll need to look elsewhere, or roll your own.
* under 2 second response; you're dealing with Excel after all!
Shameless plug for our product: https://www.thectgrid.com/
We work in a team of only 3 people with an excel file on OneDrive and what annoys me the most is that it happens quite often that excel "says" that your changes have been saved (e.g. it says it's saved in the header, also the blue onedrive symbol shows no circle. The best indicator is the file in the explorer: If that shows the green checkmark then it's good) while in reality it's not and you only get a notification (that it cannot save because it cannot updates from someone else with yours) when you try to close the file. Annoying. Anyone with solutions is very welcome :)
We explored a few options when trying to solve the sharing problem, including OneDrive, Google Sheets, and some 3rd parties. Couldn’t find anything that fit the bill.
FWIW- What you describe could be down to OneDrive’s data quotas. If you’re dealing with pricing data or any numerical data then CT Grid solves that issue. Even if it’s not a perfect fit, I’d be happy to share the experience if it helps you.
Like MeinBlutIsBlau, I thought this was going to be Microsoft finally ditching VBA for Python as its macro language.
No, its not.
> Packages to allow Python to create/manipulate Excel files have been around for a long time.
Xlwings (1) has been around for a while, itself, and more to the point (2) isn’t just a “package to allow Python to create/manipulate Excel files”. It includes an Excel add-in to allow embedding Python in Excel, using it for purposes similar to VBA.
> Like MeinBlutIsBlau, I thought this was going to be Microsoft finally ditching VBA for Python as its macro language.
Well, it allows users to do that without Microsoft doing it, so that's closer to accurate than the description implied by your dismissal.
There is lambda and JavaScript support.
https://www.microsoft.com/en-us/research/blog/lambda-the-ult...
https://exceljet.net/excel-functions/excel-lambda-function
https://docs.microsoft.com/en-us/office/dev/add-ins/referenc...
Can't you use any language that 'integrates with' Microsoft Scripting Host?
WSH is something quite different. It runs VBScript, which is a different language from VBA. VBA is the same language as Visual Basic 6 and uses the same IDE.
pip2 install excelsior
curl -O https://prd-wret.s3.us-west-2.amazonaws.com/assets/palladium/production/atoms/files/ds140-2017-bauxi.xlsx
excelsior -f tsv ds140-2017-bauxi.xlsx
The Ninhursag project is visualisation and modeling of USGS data series 140, which include world production of almost all minerals starting from the year 1900:
https://ninhursag.herokuapp.com/mineral/statistics (Heroku free tier – might take a few seconds to load)
Yes, xlwings (one of the packagess covered by the book, and the one on whose site the page is hosted) absolutely allows this via an Excel add-in: https://docs.xlwings.org/en/stable/addin.html
Those that only use VBA only have themselves to blame.
- lambda and JavaScript, out of the box in latest versions
- any .NET or COM aware language, PowerShell fits the bill, there is even a mini-IDE installed by default
Not every company uses the latest versions of everything. We finally switched everyone to Windows 10 this year. We were paying for Windows 7 support while making the transition.
The main reason VBA is horrible is because it allows dynamic typing by default and Python would not help there at all.
VB syntax is actually nicer than Python syntax in at least 3 aspects: 1) VB is not case sensitive 2) VB allows specifying data types 3) VB uses If Then Else End If style syntax that makes it clear where things start and stop.
If Excel actually had the SQL based query interface inside the spreadsheet, it would make most of VBA unnecessary and probably improve the performance of Excel macros by several orders of magnitude.
VB is not case sensitive, but case insensitivity isn't nice; it reduces clarity and forces circumlocution in cases where having case distinctions would not.
> 2) VB allows specifying data types
Python allows specifying data types, and the major python typecheckers support a more robust type system than VBA.
> 3) VB uses If Then Else End If style syntax that makes it clear where things start and stop
Python uses indentation-based syntax that makes it more immediately visually clear where things start and stop with less visible noise.
The result is that if you declare a variable named "count" anywhere in your project, every property (and every unrelated variable) everywhere gets renamed from "Count" to "count". Makes for annoying diffs.
Also case insensitivity is a terrible idea. Everywhere it is used it causes problems.
Here's a one-minute intro from their CTO showing off some of the basic features. The video is from 2009 and I'm amazed there's been nothing comparable released, open source or otherwise, in the intervening years. If there has and I've missed it, I'd love to know!
It's always been a mystery to me why Microsoft never addressed this - they could have prevented a whole host of competitors in the "big data" space from ever existing if they'd found a way of abstracting this limit away from end users.
Too many people believe excel should do everything... no, just no
Take it for a spin if you have any interesting spreadsheets in need of some love!
Libraries like xlread and xlwrite can and would be one way to automate such processes. Xlwings can too. I think that Xlwings can do macros where the others can't though
First sheet is usually raw data in standard rows/columns, second sheet would have the cells I need, etc a cell for sum of x columns from sheet 1.
Then use panda’s to get the data, and feed it through jinja2’s template render function.