The FXMacroData DuckDB community extension exposes latest macro observations, indicator history and release calendars as SQL tables. Install it through DuckDB's community repository and begin with a public USD query. Materialise the result once, then analyse that bounded snapshot while retaining the distinction between an observation period and its release time.
Who this guide is for: SQL analysts and quant researchers who want macro evidence beside their existing local datasets.
Outcome: A reusable SQL table that joins official macro observations to your own research data.
A research desk often starts with a market hypothesis and a pile of separately downloaded files. Putting macro observations into the same SQL workspace as the rest of the study makes the question reproducible: which series was queried, which rows were available and which transformation produced the result? The extension supplies named, typed tables so those choices are visible in a query instead of hidden in spreadsheet edits.
Use USD policy-rate evidence and the release calendar to orient the first session. The goal is to preserve the data's meaning as it moves into the research workflow.
1. Connect the accepted integration
The official community entry supplies the normal installation route. Community binaries must match the DuckDB version and platform; the descriptor excludes DuckDB-WASM, Windows RTools and Linux musl. Start with the supported native client rather than assuming a browser build can load the same extension.
Use the official listing for discovery and the FXMacroData integration README for its version-specific instructions.
INSTALL fxmacrodata FROM community;
LOAD fxmacrodata;
SET TimeZone = 'UTC';
SELECT indicator, val, unit, date, announced_at, source
FROM fxmacrodata_latest('USD')
WHERE val IS NOT NULL
ORDER BY announced_at DESC;Research workflow
- 01Install the community extension
Load the community extension for your DuckDB version and platform.
- 02Inspect a public USD table
Choose a real indicator and retain its reference and release fields.
- 03Materialise one result
Store the returned rows as a local evidence table for subsequent joins.
- 04Review timing and coverage
Check sources, access window and timestamp basis before drawing conclusions.
2. Run the native research workflow
The useful first output is a table you can inspect, filter and join. For a USD policy-rate study, keep the source and reference date in the materialised result. Repeated analysis of that temporary table uses the same retrieved rows, which prevents two notebook cells from quietly querying different moments. A SQL WHERE clause filters the rows returned by the function; it does not push an earlier history window or a new page into the service.
CREATE TEMP TABLE usd_policy_evidence AS
SELECT *
FROM fxmacrodata_announcements('USD', 'policy_rate');
SELECT date, val, previous_value, announced_at, source_url
FROM usd_policy_evidence
ORDER BY date DESC;Choose the right result surface
| Native surface | Useful research output | Scope to check |
|---|---|---|
| fxmacrodata_latest(currency) | Latest observations, names, units and frequency | One latest-observations request |
| fxmacrodata_announcements(currency, indicator) | One indicator's history with source links | Version 0.1.0 requests one page of up to 100 rows |
| fxmacrodata_calendar(currency) | Scheduled release rows and source fields | No SQL date-range parameters in version 0.1.0 |
3. Interpret the result before using it
An observation's date identifies its reference period. The announced_at column converts API epoch seconds to a DuckDB TIMESTAMP representing UTC; that SQL type does not keep a timezone label. The local-time string, when returned, is separate. Missing values remain SQL NULL. A null observation should never become a zero merely because a downstream aggregate or chart wants a numeric cell.
Four meanings that should survive extraction
- Reference period
- The period described by an observation.
- Observed publication
- A publisher instant only when the response's quality fields establish it.
- Official schedule
- A future time or date-only announcement, retained with its status.
- Client receipt
- When a client received the response, separate from all three above.
4. Review scope, access and quality
The currently listed 0.1.0 history function reads a single page. Credentials widen API access but do not change that implementation limit. Its projections also omit publication-status, revision and dataset-version fields. For a study requiring complete history or verified historical information cutoffs, use the documented complete response and an appropriate research loader, retaining its page and vintage metadata. A timestamp column alone cannot certify a revision-aware backtest.
Public USD history follows the documented recent-window and delay policy. Additional currencies and history depend on authorised access. The extension reads FXMACRODATA_API_KEY, with FXMD_API_KEY as an alias, from the DuckDB process environment and sends it as an X-API-Key header. Keep credentials out of SQL text and shared query plans.
A repeatable review checklist
- Count the rows actually materialised
A first page is a bounded research slice. Record the requested window before reusing it.
- Preserve source links and units
Use publisher links to identify the original series and keep the supplied units.
- Keep SQL NULL values unavailable
Retain the null mask in SQL aggregates; a missing figure cannot become a zero.
- Separate scheduled and observed times
A numeric clock needs publication-quality evidence before it can define a backtest cutoff.
Keep the original response or a reproducible local snapshot alongside the analysis. Measure row coverage, the treatment of missing values and the ability to reproduce the same window. If those checks fail, diagnose the extraction or interpretation before attributing a difference to the market.
Troubleshooting a first session
If installation fails, check binary/platform compatibility first. For 401 or 403, check the process environment and dataset access. A 404 can indicate an unpublished indicator slug; discover slugs through the FXMacroData catalogue. An empty anonymous USD history can reflect the access window, and fewer rows than expected can reflect the extension's one-page scope. Do not repeatedly retry a valid empty window as if it were a transport failure.
Frequently asked questions
Does a SQL filter fetch more history?
No. In the listed 0.1.0 release, SQL filters act after the extension has retrieved its page.
Can I use announced_at alone for a historical backtest?
It helps distinguish reference and release dates, but publication quality, original vintages and revisions also matter. The current typed projection does not expose all of those fields.
Is an API key required for the first query?
The public USD macro workflow works without a key; its window and delay rules still apply.
Sources and next steps
Version and acceptance evidence: official integration listing and public adapter source. Data behaviour and optional access: FXMacroData API reference, hosted MCP guide and subscription access.
