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
Data Analysis
Answers from raw rows: aggregates, cohorts, funnels, time series, plus the string and text wrangling that precedes every report.
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
YAMLGuide
Analyzing Configuration and Metadata Held in YAML With SQL
Turn a repository of YAML into tables and ask SQL: Compose fleets, version drift, Kubernetes audits, dbt coverage and duplicate keys, with tested results.
17 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
JSON
Loading JSON Into a pandas DataFrame the Right Way
pandas read_json turned the zip "01234" into 1234. How orient, lines, dtype and the pyarrow backend change the result, and what changed in pandas 3.
6 min read
Excel
Replacing a Fragile Excel Model With a Query and a View
Move a fragile Excel model into a SQL view: map inputs, calculations and outputs, translate formulas, run both in parallel and keep Excel as the front end.
8 min read
Get new articles by email
We send one email when a new article is published. No spam.