XML
Reading Financial and Government XML Datasets With Python
Read the ECB daily reference rates XML: two namespaces, rates as attributes, 29 currencies, a 64-day history, and Decimal for the arithmetic.
8 min read
Tag
analysis
92 posts
Excel
Writing the Same Excel Pivot Table as a GROUP BY Query
Map each part of an Excel pivot table to SQL: rows to GROUP BY, values to aggregates, columns to CASE, filters to WHERE and totals to ROLLUP.
6 min read
XML
Summarizing a Large XML Export With a Streaming Parser
Total amounts by currency and top SKUs over 20,000 invoices in one streaming pass with Decimal, 21 MB of memory, and the same USD total as a tree-based check.
6 min read
Excel
Grouping Dates by Month or Quarter in a Pivot Table
Group dates in a pivot table by month, quarter or year, fix the Cannot group that selection error, handle fiscal quarters, and do the same grouping in SQL.
6 min read
Excel
Making a Pivot Table From Data Larger Than Excel Allows
A sheet holds 1,048,576 rows, but a pivot can read more. Use the Data Model, pre-aggregate in SQL or DuckDB, or sample, and know where each approach stops.
6 min read
XML
Counting Elements and Attributes in an Unknown XML File
Profile an XML file you have never seen in 0.2 seconds and 21 MB of memory, by counting every element and attribute path with iterparse, and read the profile.
7 min read
Excel
Building a Pivot Table From a Database Query in Excel
Connect Excel to a database, load a SQL query as a connection and build a pivot table on it that refreshes. Covers the Data Model, parameters and traps.
7 min read
XMLGuide
Analysis on XML Data When Nobody Has Converted It Yet
A method for questions about an XML file nobody has converted: look, profile, sample, pick a tool, answer, then prove the numbers with a second method.
17 min read
ExcelGuide
Pivot Tables and Their SQL Equivalents for Excel Users
For people who know Excel pivot tables and need the SQL behind them: GROUP BY, conditional aggregates, ROLLUP, window functions, unpivoting and checks.
19 min read
Excel
Why SQL Beats Formulas Once the Data Gets Large and Slow
Why spreadsheet formulas slow down as data grows and where SQL pulls ahead: lookup cost, recalculation, memory, repeatability and auditability.
7 min read
Excel
Date Functions in Excel Versus SQL Date Functions Compared
Translate Excel date functions to SQL: YEAR, EOMONTH, EDATE, DATEDIF, NETWORKDAYS, WEEKDAY, WEEKNUM and TEXT, with worked results and week numbering traps.
7 min read
Excel
Text Functions in Excel Versus SQL String Functions
Map Excel text functions to SQL string functions: LEFT, MID, LEN, TRIM, SUBSTITUTE, FIND, TEXTJOIN, PROPER and TEXT, with the edge cases for spaces and nulls.
7 min read