Excel Spreadsheets are user-familiar interfaces for working with many sorts of data. However,
writing macros/plugins in VBA involves stress-inducing and arcane syntax.
Python is an intuitive and powerful language for all sorts of programming tasks.
This repository collects basic examples of how these two powerful tools can be made to work together,
xlwings library as a bridge.
Once you are inside a virtual environment, install all dependencies with the following:
Manually install the
xlwings add-in, and restart Excel. You can get the path to the addin file by running
xlwings addin install.
xlwings quickstart <name>, replacing
<name> with your project name. No special characters!
After opening the generated workbook, add the path to your virtualenv to Interpreter field
in the Excel xlwings Addins ribbon under “interpreter”
# Example: /Users/cameron/Environments/xlwings/bin/python
This is the basic quickstart demo that
xlwings provides by default.
Invoking the macro writes a basic string to the first worksheet.
This demo lets users download data from the world’s largest public data repository, Enigma Public directly into Excel. If you want to transform the data before it moves between the API and the sheet (i.e. perform groupby or filtering aggregations), you should edit the base
Add the following to an
.env file in the repository folder.
- Write hello-world example
- Use Pandas to pipe a dataframe into Excel
- Use Pandas to pipe an Enigma table into Excel
- Create examples of doing rollup aggregation in Python / link to pandas demos which can pipe the results into Excel readily.
- Add example SQL queries, possibly create a blog post, using Enigma data
- Extend pandas-datareader to support the new Enigma API
- Only on Windows can you define callable functions (UDF).
- On Mac, invoke functions by pressing
F5in the VBA Editor, or by adding a button to your ribbon.
- It is easier to update Excel from Python than it is to invoke Python from Excel.