JSONGuide
Event Analytics on JSON Properties in PostgreSQL Tested
A tested guide to analytics on JSON event data: envelope versus properties, funnels and retention, typed columns, indexes, type drift and backfills.
19 min read
Tag
analysis
92 posts
Excel
Exporting Invoices to Excel With Totals That Add Up
Export invoices and lines to Excel so totals add up: one row per line, tax by a stated rule, document totals checked, no gaps in numbering and exact decimals.
7 min read
Excel
Building a Budget Workbook From Query Output Reliably
Build a budget workbook from SQL output: actuals from the ledger, budget versions in a table, variances, planner input cells and a frozen release.
7 min read
Excel
Reconciling Bank Statements Against a Database Table
Reconcile a bank statement against a ledger table: load it, match in passes, list unmatched items on both sides and explain the closing difference.
8 min read
ExcelGuide
Excel Reports That Finance Teams Rely On Month After Month
What makes an Excel report dependable for finance: reconciliations, rounding, controls, evidence, invoices, budgets, timesheets and currencies.
16 min read
CSV
Calculating Month Over Month Growth From a CSV in SQL
Compute month over month growth with lag(): revenue of 46.50, 202.00 and 382.00 grew 334.4% and then 89.1%. Partial months, gaps and a zero base need care.
6 min read
CSV
Filling Gaps in a CSV Time Series With SQL and DuckDB
Find and fill missing days in a CSV time series: build a calendar with range(), left join the data and choose zero, carry-forward or interpolation.
7 min read
CSV
Joining Two CSV Files on a Messy Key Without Losing Rows
Join two CSVs on a messy key: a strict join matched 1 of 4 orders, a normalized key matched 3, and an anti-join listed the rest. Check counts and duplicates.
6 min read
XML
Parsing Tax Style E Invoices and Checking the Totals
Read a UBL style invoice with namespaced lines, recompute each line, sum the net amounts and compare the tax rounded per line with the tax per rate group.
6 min read
XML
Parsing a Bank Statement in XML and Reconciling the Balance
Read an ISO 20022 style account statement, apply the credit and debit sign, and check that opening balance plus entries equals the closing balance.
7 min read
YAML
Finding Duplicate Keys Across a Config Repository Reliably
Find YAML keys repeated inside a file and keys that differ across environment files, with ruamel.yaml, yamllint and a flatten pass over a repository.
6 min read
XML
Analyzing Publication Metadata From XML Archives in Python
Read thousands of journal article XML files straight from a tar.gz, count by year, journal and author count, rank keywords, and avoid unpacking the archive.
6 min read