Macroeconomic & interest rates

lhbox duckdb --catalog commons/pubdata

SELECT series_id, country_code, series_name, frequency, date, value, unit
FROM commons_pubdata.population.macro_rates
QUALIFY row_number() OVER (PARTITION BY series_id ORDER BY date DESC) = 1
ORDER BY country_code NULLS LAST, series_id;

INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs;
CREATE SECRET pubdata (TYPE S3, ENDPOINT 'lakehousebox-public-data.b-cdn.net', URL_STYLE 'path', REGION 'us-east-1');
ATTACH ':memory:' AS pubdata;
CREATE SCHEMA pubdata.population;
CREATE VIEW pubdata.population.macro_rates AS SELECT * FROM iceberg_scan('s3://commons--pubdata/population/macro_rates');

SELECT series_id, country_code, series_name, frequency, date, value, unit
FROM pubdata.population.macro_rates
QUALIFY row_number() OVER (PARTITION BY series_id ORDER BY date DESC) = 1
ORDER BY country_code NULLS LAST, series_id;

# Macroeconomic & interest rates

*Policy rates, bond yields, inflation, unemployment, GDP and Brent oil from FRED.*

Eleven benchmark macroeconomic and financial time series for the United States, the Euro Area and global energy markets, served from the Federal Reserve Bank of St. Louis (FRED) public data feeds: Federal Funds Rate, ECB Deposit Facility Rate, US and Euro Area 10-year government bond yields, US 2-year Treasury yield, US 30-year fixed mortgage rate, US and Euro Area consumer price indices (CPI/HICP), US unemployment rate, US real GDP and Europe Brent crude oil spot price. One row per `(series_id, date)`.

## At a glance

| | |
|---|---|
| Table | `pubdata.population.macro_rates` |
| Category | Population · Time series, Economy, Country |
| Rows | n/a |
| Size | n/a data file(s) |
| Iceberg format | v? |
| Last refreshed | not loaded yet |
| Source version | n/a |
| Refresh cadence | weekly (replace) |
| Coverage | United States (US), Euro Area (EU) and global Brent crude benchmark; 1947 to present (daily, weekly, monthly and quarterly series) |

## Origin

- **Source:** [FRED (Federal Reserve Bank of St. Louis) — Federal Reserve, BLS, BEA, US Treasury, ECB, Eurostat, EIA](https://fred.stlouisfed.org/)
- **Licence:** Public domain / open data (US Federal Reserve, BLS, BEA, US Treasury, EIA, ECB and Eurostat via FRED)
- **Attribution:** Source: Federal Reserve Economic Data (FRED), Federal Reserve Bank of St. Louis; underlying series from the Board of Governors of the Federal Reserve System, U.S. Bureau of Labor Statistics, U.S. Bureau of Economic Analysis, U.S. Energy Information Administration, European Central Bank and Eurostat.
- **Recipe:** `pubdata/recipes/macro_rates.py` in the LakehouseBox repository; registry entry `pubdata/registry/macro_rates.yaml`.

## Schema

| column | type | description |
|---|---|---|
| `series_id` | string | FRED series code (e.g. FEDFUNDS, DGS10, CPIAUCSL, UNRATE, ECBDFR, DCOILBRENTEU) |
| `date` | date | observation date (period start for monthly/quarterly series) |
| `country_code` | string | US, EU (Euro Area), or null for global commodity benchmarks |
| `series_name` | string | human-readable indicator title |
| `frequency` | string | daily, weekly, monthly or quarterly |
| `unit` | string | unit of measure (e.g. percent, index 1982-84=100, USD per barrel) |
| `value` | double | numeric observation value |

**Join keys:** `date`, `country_code`, `series_id`.

## For agents

Series have different frequencies (`daily`, `weekly`, `monthly`, `quarterly`); filter on `series_id` before
aggregating. Monthly series (`CPIAUCSL`, `UNRATE`, `FEDFUNDS`, `IRLTLT01EZM156N`, `CP0000EZ19M086NEST`) use the first
day of the month (`YYYY-MM-01`) as `date`. `CPIAUCSL` and `CP0000EZ19M086NEST` are price level indices, not year-on-year
growth rates: compute 12-month percentage change with `lag(value, 12) OVER (ORDER BY date)`.

## Example SQL (DuckDB)

```sql
-- latest reading of every macroeconomic benchmark series
SELECT series_id, country_code, series_name, frequency, date, value, unit
FROM pubdata.population.macro_rates
QUALIFY row_number() OVER (PARTITION BY series_id ORDER BY date DESC) = 1
ORDER BY country_code NULLS LAST, series_id;
-- US year-over-year CPI inflation (%) by month since 2020
WITH cpi AS (
  SELECT date, value, lag(value, 12) OVER (ORDER BY date) AS value_12m_ago
  FROM pubdata.population.macro_rates
  WHERE series_id = 'CPIAUCSL'
)
SELECT date, round((value / value_12m_ago - 1) * 100, 2) AS yoy_cpi_inflation_pct
FROM cpi WHERE date >= DATE '2020-01-01' ORDER BY date DESC;
```

## How to read it

With a LakehouseBox account:

```bash
# with a LakehouseBox account (any organisation): the public catalog by name, read-only
lhbox duckdb --catalog commons/pubdata     # opens DuckDB attached as `commons_pubdata`
SELECT * FROM commons_pubdata.population.macro_rates LIMIT 10;
```

Public URL of the table's storage: `https://s3.lakehousebox.com/commons--pubdata/population/macro_rates/`. This card: `https://lakehousebox.com/pubdata/cards/macro_rates.md`; the page: https://lakehousebox.com/public-data/macro_rates. Every dataset as a table: `commons_pubdata.meta.datasets`; as Markdown: https://lakehousebox.com/public-data.md.