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
Tag
analysis
92 posts
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
Excel
Checking Totals Between Two Excel Reports With SQL
Reconcile two Excel reports with SQL: compare grand totals, then group totals, then rows with a full outer join, and sort the differences into causes.
7 min read
YAML
Using DuckDB to Query Data Stored as YAML With read_yaml
Install DuckDB's yaml community extension and query YAML files with read_yaml: inferred types, nested structs, unnest, globs, mixed types and broken files.
6 min read
Excel
Building Cohort Tables From an Excel Export With SQL
Turn an Excel export of orders into a cohort retention table with SQL: first purchase month, months since, a retention triangle and honest recent cohorts.
7 min read
Excel
Building a Forecast Baseline From Excel History in SQL
Build simple forecast baselines from monthly history in SQL: last value, seasonal naive, moving average and linear trend, and test them on held out months.
7 min read
Excel
Finding Duplicates and Gaps in an Excel Sheet With SQL
Use SQL on an Excel sheet to find repeated keys, missing numbers in a sequence and missing dates with GROUP BY, window functions and gaps and islands.
7 min read