In a great development for data analysts and coding enthusiasts, John Lam’s new guide that expertly explains Python programming with Excel from the start to a practical example.
Book of Python in Excel goes through the basics like using the XL() function in Python to grab data from an Excel workbook, importing external data and editing Python within Excel.
There’s practical example where data in an Excel table is analyzed by Python. First ‘cleaning’ the data, something that’s clumsy in Excel but done in a snap by a Python library then filtering and anaylsing the results. In this case, taking NBA player data separating Rookies from Veterans and Undrafted (using a custom function in Python). Finally comparing each salary against the average to show the top paid players (the 80th quantile and above).
After the example there’s a lot on Python with Excel basics and an appendix. This is a ‘work in progress’ with many ‘TO DO’ items marked in the text.
Excel differences with Python
John’s ‘Book of Python in Excel’ also covers important differences between the two.
Error Handling
Python, like most programming languages (all?) raises exceptions and can stop when there’s a problem in the code. Excel doesn’t do that, instead generating #ERROR in cells. That means different error trapping methods apply in each.
It also means you have to think about what is causing the error – Python or Excel.
” is it an error that originates in Python and propagates to the grid?
Or is it an error that originates in the grid and propagates to Python?”
There’s also a passing mention of Python’s ASSERT command which is very useful for debugging by adding simple tests for situations that should not arise like an empty array, zero or non-zero number variable, empty string, type of variable or function output etc.
Also ERROR.TYPE() which lets the Python code get the error details from an Excel cell.
There’s this handy little code block which grabs the value of a cell (A1) with a way to show the error in the Excel Diagnostics pane if there’s a problem.
try:
xl("A1")
except Exception as e:
print(f"Exception type {type(e)}: {e}")
Order of Calculation
While it doesn’t matter for smaller tasks, it’s worth keeping in mind that Python and Excel have a different order for calculating cells.
Modern Excel uses complex algorithms to work out the best and most efficent order of calculation (unless you choose Manual calculation).
Python work through cells from left-to-right and top-to-bottom. It starts with the left workbook sheet and works to the right – something that will surprise Excel users since worksheet (tab ) order doesn’t matter in Excel.
Python Editor for Excel is out and essential
A new way to get UTC time into Excel
Get Python in Google Sheets
