1 Monitor budget execution of a public entity
Question: How much budget has the Ministry of Health executed this year vs. what was approved?
Compares approved PIM vs. accrued spending month by month. Useful for stories on structural under-execution.
SQL — DuckDB
SELECT
anio,
ROUND(SUM(MTO_PIM) / 1e9, 1) AS pim_bn,
ROUND(SUM(DEVENGADO_) / 1e9, 1) AS dev_bn,
ROUND(SUM(DEVENGADO_) * 100
/ NULLIF(SUM(MTO_PIM), 0), 1) AS execution_pct
FROM mef_historico
WHERE PLIEGO LIKE '011%' -- 011: MINISTRY OF HEALTH
GROUP BY anio
ORDER BY anio
2 Compare execution across regions
Question: Which regional governments execute investment budgets best?
Useful for reports on territorial inequality and identifying who leaves money unspent.
SQL — DuckDB
SELECT
PLIEGO AS regional_government,
ROUND(SUM(MTO_PIM) / 1e9, 2) AS investment_pim_bn,
ROUND(SUM(DEVENGADO_) * 100
/ NULLIF(SUM(MTO_PIM), 0), 1) AS execution_pct
FROM mef_historico
WHERE NIVEL = 'R. GOBIERNOS REGIONALES'
AND CATEGORIA_GASTO = '6. CAPITAL'
AND anio = (SELECT MAX(anio) FROM mef_historico)
GROUP BY PLIEGO
ORDER BY investment_pim_bn DESC
3 Detect chronic under-execution
Question: Which entities execute less than 50% of their budget for several years in a row?
Identifies structural management issues. Under-execution for 3+ years indicates a deeper problem — not a bad year.
SQL — DuckDB
WITH annual_execution AS (
SELECT
anio,
PLIEGO,
SUM(DEVENGADO_) * 100
/ NULLIF(SUM(MTO_PIM), 0) AS execution_pct
FROM mef_historico
WHERE anio BETWEEN 2020 AND 2024
GROUP BY anio, PLIEGO
)
SELECT
PLIEGO,
ROUND(AVG(execution_pct), 1) AS avg_execution_pct,
COUNT(*) AS years_evaluated
FROM annual_execution
WHERE execution_pct < 50
GROUP BY PLIEGO
HAVING COUNT(*) >= 3
ORDER BY avg_execution_pct ASC
4 Region in numbers
Question: How does my region compare to the national average?
For regional journalism: "your region receives X while the national average is Y" with verifiable data.
SQL — DuckDB
SELECT
DEPARTAMENTO_META AS region,
ROUND(SUM(MTO_PIM) / 1e9, 2) AS pim_bn,
ROUND(SUM(DEVENGADO_) * 100
/ NULLIF(SUM(MTO_PIM), 0), 1) AS execution_pct
FROM mef_historico
WHERE anio = (SELECT MAX(anio) FROM mef_historico)
AND DEPARTAMENTO_META NOT LIKE '00%'
GROUP BY DEPARTAMENTO_META
ORDER BY pim_bn DESC
Tip
To see all entity codes, government levels, sectors and regions available, query GET /api/insights/dimensions
