Welcome to Python Excels!
This is Python Excels, a series of blog posts that describe different techniques for automating tasks in Excel with the Python language.
-
What is Python Excels?
It’s time to give back. And get back. I’ll be sharing some ideas from the Python programs I’ve written, and talk about what problems were solved. I like to share work that is as complete as possible, so in most cases you should be able to download the script and try it right away for yourself.
-
Installing Python
Getting started with Python is easy, you just need to download the Python installation package and install onto your computer.
-
Using XLWT to Write Spreadsheets Without Excel
Long before I even started thinking about writing a blog covering the integration possibilities between Excel and Python, the folks at Simplistix were crafting some of the handiest Python modules around: xlrd, xlwt and xlutils.
-
Another XLWT Example
After completing the last example, I wanted to try something a little more interesting with XLWT this time around. This article describes a short script that uses Python and XLWT to download some raw data from the web, parse it, and write a spreadsheet with a new column derived from the data.
-
Basic Excel Driving with Python
Now it’s getting interesting. Reading and writing spreadsheets with XLRD and XLWT (as discussed in the two previous posts is sufficient for many tasks and you don’t even need a copy of Excel to do it. But to really open up your data and fully extract all the information possible, you’ll need Excel and its powerful set of functions and features like pivot tables and charting.
-
Python Excel Mini Cookbook
To get you started, I’ve created sample scripts to demonstrate some common tasks you can do with Python and Excel. Each program below is a self-contained example, just copy it, paste it, and run it.
-
Mapping Excel VB Macros to Python
A handy feature in Excel is the ability to quickly record a sequence of operations into a Visual Basic (VB) macro. It’s also fairly simple to take a captured VB macro, change it slightly, and use it in your Python scripts. I’ve used this capability dozens of times over the years to automate spreadsheet calculations and pivot table generation. I now have a good understanding of how to port the VB macro into Python; let me share the technique with you.
-
Mapping Excel VB Macros to Python Revisited
The last post introduced a technique for recording a Visual Basic macro within Excel and porting it to Python. This exercise will build on those techniques while leveraging Python for more of the work. In this example, you will create two tables from scratch — a simple multiplication table and a table of random numbers — and apply conditional formatting to the numbers.
-
Cleaning Up Corporate ERP Data
The previous posts have used Excel and Python to create and manipulate small spreadsheets. In reality, Python and Excel are especially well suited to tackling large data sets. This post will illustrate some techniques for cleaning up data downloaded from corporate Enterprise Resource Planning (ERP) systems, such as SAP and Oracle, and getting it ready for some serious data mining with Excel.
-
Introducing Pivot Tables
If you aren’t aware of pivot tables or haven’t had the time to try out this function in Excel, you need to learn about this powerful feature. Pivot tables provide a way to cross-tabulate, sort, segregate, and aggregate tabular data. Using pivot tables, you can quickly summarize data and extract totals, averages, and other information from the source data.
-
Automating Pivot Tables with Python
In the last post, I started with a raw data set and create four different pivot tables to answer various questions about the data. This post describes how to do the same thing in an automated fashion by using Python and Excel.
-
Extending Pivot Table Data
Python and Excel can help you quickly clean up a spreadsheet, organize data, and build useful reports in very few lines of code. Another helpful data preparation technique is to make new columns of information based on the available data. This post discusses how you can use Python to extend your Excel data and create more meaningful reports.
-
A User-Friendly Experience
If you’re going to offer your Windows users a new application, you should also provide a graphical user interface to help them use it. Graceful error recovery and useful error messages also go a long way toward making your application easy-to-use. This post will take the Pivot Table generation script developed in Extending Pivot Table Data and turn it into a user-friendly application with an improved user experience.
-
Ninety Six Spreadsheets
Here’s another application for Python and Excel: opening a folder of spreadsheets and pulling specific data from each spreadsheet.