Excel
Rounding Rules That Survive Audit in Spreadsheets and SQL
Write a rounding policy an auditor can follow: where to round, which tie rule, per line or per total, and how to test it in Excel, Python and SQL.
7 min read
Topic
Excel
Where the business keeps its data. Reading workbooks, taming dates and formulas, and moving sheets to and from a database.
Excel
Protecting a Generated Workbook From Accidental Edits
Lock the cells, sheets and structure of a workbook your script generates so readers cannot break formulas, with openpyxl and XlsxWriter, and know the limits.
7 min read
Excel
Watching a Folder for New Excel Files and Importing Them
Watch a folder for new Excel files and import each once: polling or watchdog, waiting for the copy to finish, moving files, idempotent loads and failures.
7 min read
Excel
Batch Converting XLS Files to XLSX With LibreOffice
Convert a folder of old XLS files to XLSX with LibreOffice headless, Python or Excel automation, and check what formatting and formulas survive.
6 min read
Excel
Splitting One Excel Sheet Into a Workbook per Group
Split one Excel sheet into a workbook per region or customer with pandas groupby, safe file names, formatting, a manifest and checks that the parts add up.
7 min read
Excel
Merging Hundreds of Workbooks Into One Table With Python
Combine hundreds of Excel files into one table with Python: glob, read each sheet, check headers, add the source name, quarantine bad files and verify counts.
7 min read
Excel
Automating Weekly Excel Reports With Python and cron
Build a weekly Excel report with Python: query the database, write the workbook, email it and schedule it with cron, with paths, logs, locks and failures.
7 min read
Excel
Office Scripts for Excel on the Web and Power Automate
Office Scripts automate Excel on the web with TypeScript. Record an action, edit the code, run it from Power Automate on a schedule, and know its limits.
7 min read
Excel
How Running a Python Script From Excel With xlwings Works
Run Python from an Excel button with xlwings: install, quickstart, RunPython and RunPython, read and write ranges, user defined functions and the limits.
7 min read
Excel
VBA Versus Python for Excel Automation in Real Projects
Compare VBA and Python for automating Excel by where the code runs, what it can touch, how it is tested and who maintains it, with a rule for choosing.
8 min read
YAML
Converting YAML to a Markdown Table for Docs and READMEs
Generate a Markdown table from a YAML list in Python, escaping pipes, joining lists, handling blanks and keeping the table in sync with the YAML in CI.
6 min read
YAML
Converting an Excel Sheet of Settings Into YAML Using Python
Read a key, value and type sheet with openpyxl, build nested YAML from dotted keys, and keep versions, dates and blanks from changing on the way.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.