Excel
Year Over Year Comparison From a Monthly Excel Sheet
Compare each month with the same month last year from an Excel sheet in SQL: LAG, self joins, partial years, missing months and percent change.
7 min read
Tag
analysis
92 posts
Excel
Summarizing a Sales Workbook With GROUP BY in SQL Queries
Summarize a sales workbook with GROUP BY: totals by region and month, averages, rollups and ranking, with checks that make the numbers trustworthy.
7 min read
Excel
Joining Two Excel Sheets With SQL in DuckDB or SQLite
Join two Excel sheets with SQL in DuckDB or SQLite: choose the join type, find rows that match nothing, avoid duplicated keys and check row counts.
7 min read
Excel
Querying an Excel File With SQL in DuckDB Step by Step
Run SQL on an Excel workbook with DuckDB: the excel extension, a pandas DataFrame route, sheet and range options, types and checks that keep numbers right.
7 min read
ExcelGuide
Doing Proper Analysis on Excel Data With SQL in Practice
A guide to analyzing Excel data with SQL: query workbooks in DuckDB, join, summarize, compare years, find gaps, build cohorts, reconcile and replace models.
16 min read
XML
Reading Government Open Data in XML With a USGS Feed
Read a real public QuakeML feed from the USGS: two namespaces, nested value elements, depth in meters, magnitude types, and checks that catch a wrong unit.
6 min read
Excel
Pivot Table Refresh Errors and Missing Fields in Excel
Fix field name is not valid, reference is not valid, fields that vanish after refresh, old items that stay, lost formatting and new rows that never appear.
6 min read
Excel
Calculating Percent of Total in a Pivot and in SQL
Show values as percent of total in an Excel pivot, then write the same share in SQL with SUM OVER and avoid integer division and rounding traps.
6 min read
Excel
Unpivoting Columns Back Into Rows in SQL for Excel Data
Turn a wide Excel sheet with a column per month into a long table: LATERAL VALUES in PostgreSQL, UNPIVOT or CROSS APPLY in SQL Server, UNION ALL in MySQL.
6 min read
Excel
Pivoting Rows Into Columns in MySQL and SQL Server
Pivot in MySQL with SUM and CASE, and in SQL Server with the PIVOT operator. Includes dynamic column lists built with prepared statements and QUOTENAME.
7 min read
XML
Analyzing Podcast and Blog Feeds From XML With Python
Turn an RSS podcast feed into cadence, duration and size numbers, handle itunes durations in two formats and RFC 822 dates, and compare with feedparser.
6 min read
Excel
Pivoting Rows Into Columns in PostgreSQL With FILTER
Turn rows into columns in PostgreSQL with conditional aggregates and FILTER, or with crosstab from tablefunc, and generate the column list when it changes.
6 min read