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
- Install the community extension
Load the community extension for your DuckDB version and platform.
- Inspect a public USD table
Choose a real indicator and retain its reference and release fields.
- Materialise one result
Store the returned rows as a local evidence table for subsequent joins.
- Review timing and coverage
Check sources, access window and timestamp basis before drawing conclusions.
From a native table function to a local evidence table: the analytical joins happen in DuckDB.
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 |
The integration surface determines what is immediately visible; the provider response determines access and data semantics.
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
| Time meaning | Interpretation |
|---|---|
| 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. |
Keep these meanings distinct when interpreting a returned record.
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
NULLvalues unavailableRetain 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.
A research packet is useful when another reader can reconstruct its scope and evidence.
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.
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.
AI was used for research, creating images and editing.