Excel
Version Controlling the Query Behind an Excel Report
Keep the SQL behind an Excel report in Git: one file per query, parameters, reviewed changes, tests, and the commit hash written into the workbook.
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
Putting a README Sheet Into Every Generated Workbook
Add a README sheet to every generated workbook with the period, source, definitions, checks and version, written by the same code that builds the report.
7 min read
Excel
Handling Currency and Locale in Generated Workbooks
Handle currency and locale in generated workbooks: store numbers, set format codes, name the currency and know what the reader's settings change.
7 min read
Excel
Rounding and Formatting Numbers for Finance Readers
Round and format numbers for finance readers: store exact values, round once on purpose, use number formats for display and make displayed totals add up.
7 min read
Excel
Producing a Monthly Management Pack From SQL Queries
Produce a monthly management pack from SQL: fixed sheets, prior month and budget comparisons, checks that tie out, and a scheduled build with sign off.
8 min read
Excel
Writing Conditional Formatting From Code in Workbooks
Write Excel conditional formatting from Python with XlsxWriter or openpyxl: value rules, color scales, data bars and formula rules, with tests.
7 min read
Excel
Sending Charts From a Query to an Excel Sheet by Script
Send charts from a SQL query to an Excel sheet by script: write the data, add a native chart with XlsxWriter or openpyxl, size it and label it.
7 min read
Excel
Designing a Report Query Before Touching Excel at All
Design a report query before opening Excel: fix the grain, avoid join fan-out, put rules in SQL, bind parameters and test totals against a trusted number.
8 min read
ExcelGuide
Building Excel Reports From SQL Queries People Trust
Build Excel reports from SQL that readers trust: design the query, write sheets, formats and charts from code, check totals, version the query, schedule it.
18 min read
Excel
Converting Google Sheets to CSV or Excel With a Script
Export a Google Sheet to CSV or xlsx from a script using the export URL, the Drive API or gspread, choose the sheet by gid and handle sharing and auth safely.
6 min read
Excel
Converting Excel to Markdown and HTML Tables for Docs
Turn a worksheet into a Markdown or HTML table for documentation with pandas, escape pipes and line breaks, align numbers and keep wide tables readable.
6 min read
Excel
Converting Excel to SQL Insert Statements Safely and Fast
Turn a worksheet into SQL INSERT statements that quote text, handle NULLs and dates, and load in batches, or skip the script and use COPY or a driver.
6 min read
Get new articles by email
We send one email when a new article is published. No spam.