How to Pull Macro Data into Excel / Google Sheets

Spreadsheets remain the most common tool for macro analysis. Whether you prefer Excel or Google Sheets, pulling live central-bank and economic indicator data directly into a cell — rather than copying values by hand — turns a static table into a self-updating decision aid. This guide shows you how to connect FXMacroData to both platforms: Power Query and VBA for Excel, and Apps Script for Google Sheets. By the end, every macro number you care about will refresh automatically without leaving your spreadsheet.

Part 1 — Excel

Excel offers two routes for live API data: Power Query (no code, great for analysts) and VBA (code-driven, best when you need to write values into specific cells or trigger logic from a button or event).

Step 1 — Understand the API endpoint shape

Every FXMacroData indicator follows the same REST pattern. A request for the US policy rate looks like this:

GET https://fxmacrodata.com/api/v1/announcements/usd/policy_rate?api_key=YOUR_API_KEY&start=2020-01-01

The JSON response is a flat object with a data array:

{
  "data": [
    { "date": "2025-03-19", "val": 4.25, "announcement_datetime": "2025-03-19T18:00:00Z" },
    { "date": "2025-01-29", "val": 4.25, "announcement_datetime": "2025-01-29T19:00:00Z" },
    { "date": "2024-12-18", "val": 4.25, "announcement_datetime": "2024-12-18T19:00:00Z" }
  ]
}

Each record carries a date (YYYY-MM-DD), a numeric val, and — where available — a second-level UTC announcement_datetime. Because every indicator endpoint shares this shape, one Power Query function or one VBA helper can serve all of them. Explore the full indicator catalogue in the API docs.

Step 2 — Excel Power Query: import via Web connector

Power Query's built-in From Web connector can consume any JSON REST endpoint with no code at all.

1. In Excel, click Data → Get Data → From Other Sources → From Web.
2. Paste your URL into the dialog — replace YOUR_API_KEY with your actual key:
   https://fxmacrodata.com/api/v1/announcements/usd/policy_rate?api_key=YOUR_API_KEY&start=2020-01-01
3. Click OK. Power Query Navigator opens showing the JSON structure.
4. Click the data record in the navigator to expand it, then click Convert to Table → OK.
5. Use the Expand column button (double-arrow icon) on the Column1 header to unpack the nested records into flat columns: date, val, announcement_datetime.
6. Click Close & Load to write the table into a new Sheet.

Step 3 — Excel Power Query: parameterise with a reusable function

Once you have the single-indicator query working, promote it to a reusable M function so you can pull any currency/indicator combination without repeating the Web connector steps.

1. In the Query Editor, right-click your query in the left panel → Create Function.
2. Name it FetchMacroData.
3. Replace its body with the M code below:

// FetchMacroData — reusable Power Query function
// Parameters: currency (e.g. "usd"), indicator (e.g. "policy_rate"), apiKey, startDate
(currency as text, indicator as text, apiKey as text, startDate as text) =>
let
    url     = "https://fxmacrodata.com/api/v1/announcements/"
              & currency & "/" & indicator
              & "?api_key=" & apiKey
              & "&start=" & startDate,
    raw     = Json.Document(Web.Contents(url)),
    records = raw[data],
    tbl     = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expand  = Table.ExpandRecordColumn(tbl, "Column1",
                {"date", "val", "announcement_datetime"},
                {"Date", "Value", "AnnouncementDatetime"}),
    typed   = Table.TransformColumnTypes(expand, {
                {"Date",  type date},
                {"Value", type number}
              })
in
    typed

4. Close the function editor. Now create a new blank query for each indicator you need:

= FetchMacroData("usd", "inflation", "YOUR_API_KEY", "2022-01-01")

= FetchMacroData("eur", "policy_rate", "YOUR_API_KEY", "2022-01-01")

Each query loads into its own table. The function handles the HTTP call, JSON parse, column rename, and type conversion. To add a new indicator, add one blank query — no connector wizard needed. Find indicator slugs on the API docs for any currency pair.

Step 4 — Excel VBA: write values directly into cells

VBA is a better fit when you need to place values into specific named cells, link them to existing formulas, or trigger the refresh from a button or a workbook-open event.

Open the VBA editor (Alt+F11), insert a new module (Insert → Module), and paste the following:

Option Explicit

' ─────────────────────────────────────────────────────────────────
' FetchLatestIndicator
' Returns the most recent val for a given currency/indicator.
' Requires a reference to "Microsoft XML, v6.0" (VBA Editor →
' Tools → References → tick "Microsoft XML, v6.0").
' ─────────────────────────────────────────────────────────────────
Function FetchLatestIndicator(currency As String, indicator As String, _
                               apiKey As String) As Variant
    Dim http     As New MSXML2.XMLHTTP60
    Dim url      As String
    Dim json     As String
    Dim dataArr  As Variant
    Dim parsed   As Object

    url = "https://fxmacrodata.com/api/v1/announcements/" & _
          LCase(currency) & "/" & LCase(indicator) & _
          "?api_key=" & apiKey & "&limit=1"

    http.Open "GET", url, False
    http.setRequestHeader "Accept", "application/json"
    http.Send

    If http.Status <> 200 Then
        FetchLatestIndicator = CVErr(xlErrValue)
        Exit Function
    End If

    ' ── Minimal JSON parser (no external library needed) ──────────
    ' Locate the first "val": number pattern after "data":[{
    Dim pos     As Long
    Dim valStr  As String
    json = http.responseText
    pos  = InStr(json, """val"":")
    If pos = 0 Then
        FetchLatestIndicator = CVErr(xlErrNA)
        Exit Function
    End If
    pos    = pos + Len("""val"":")
    valStr = Mid(json, pos, 20)
    ' Trim to the actual number (stop at comma, space, or brace)
    valStr = Split(Trim(valStr), ",")(0)
    valStr = Split(valStr, "}")(0)
    valStr = Trim(valStr)
    FetchLatestIndicator = CDbl(valStr)
End Function


' ─────────────────────────────────────────────────────────────────
' RefreshMacroDashboard
' Writes the latest value of several indicators into named cells.
' Define Named Ranges (Formulas → Name Manager) matching the keys
' used in the pairs array below.
' ─────────────────────────────────────────────────────────────────
Sub RefreshMacroDashboard()
    Dim apiKey As String
    apiKey = "YOUR_API_KEY"   ' ← replace with your key

    Dim pairs(5, 1) As String
    pairs(0, 0) = "usd" : pairs(0, 1) = "policy_rate"
    pairs(1, 0) = "eur" : pairs(1, 1) = "policy_rate"
    pairs(2, 0) = "gbp" : pairs(2, 1) = "policy_rate"
    pairs(3, 0) = "usd" : pairs(3, 1) = "inflation"
    pairs(4, 0) = "usd" : pairs(4, 1) = "unemployment"
    pairs(5, 0) = "usd" : pairs(5, 1) = "gdp"

    Dim i   As Integer
    Dim val As Variant
    Dim nm  As String
    For i = 0 To 5
        val = FetchLatestIndicator(pairs(i, 0), pairs(i, 1), apiKey)
        nm  = UCase(pairs(i, 0)) & "_" & pairs(i, 1)
        On Error Resume Next
        ThisWorkbook.Names(nm).RefersToRange.Value = val
        On Error GoTo 0
    Next i

    MsgBox "Dashboard updated — " & Now(), vbInformation, "FXMacroData"
End Sub

Part 2 — Google Sheets

Google Apps Script is a JavaScript runtime embedded directly into Google Workspace. It runs server-side on Google's infrastructure, so there is nothing to install. You call FXMacroData via UrlFetchApp and write results to the spreadsheet with SpreadsheetApp. Time-driven triggers let you schedule automatic refreshes.

Step 5 — Open the Apps Script editor

1. Open sheets.google.com and create a new blank spreadsheet.
2. Click Extensions → Apps Script.
3. Rename the project to FXMacroData Loader (top-left field).
4. Delete the default myFunction() placeholder.

Step 6 — Write the fetch helper and sheet writer

Paste the following code into Code.gs. The three functions handle fetching, normalising, and writing:

// ── Config ──────────────────────────────────────────────────────
const BASE_URL = 'https://fxmacrodata.com/api/v1';

// List of {currency, indicator} pairs to load.
// Add or remove rows to customise your dashboard.
const INDICATORS = [
  { currency: 'usd', indicator: 'policy_rate'  },
  { currency: 'usd', indicator: 'inflation'    },
  { currency: 'usd', indicator: 'unemployment' },
  { currency: 'eur', indicator: 'policy_rate'  },
  { currency: 'gbp', indicator: 'policy_rate'  },
  { currency: 'aud', indicator: 'policy_rate'  },
];


// ── Fetch helper with retry ─────────────────────────────────────
/**
 * Fetches the latest N records for a currency/indicator pair.
 * Retries up to maxRetries times with exponential back-off.
 *
 * @param {string} currency   - e.g. 'usd'
 * @param {string} indicator  - e.g. 'policy_rate'
 * @param {string} apiKey
 * @param {number} limit      - number of records to return (default 12)
 * @param {number} maxRetries
 * @returns {Array} array of {date, val, announcement_datetime} objects
 */
function fetchIndicator(currency, indicator, apiKey, limit = 12, maxRetries = 3) {
  const url = `${BASE_URL}/announcements/${currency}/${indicator}`
            + `?api_key=${apiKey}&limit=${limit}`;

  for (let attempt = 0; attempt < maxRetries; attempt++) {
    try {
      const resp = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
      if (resp.getResponseCode() === 200) {
        return JSON.parse(resp.getContentText()).data || [];
      }
    } catch (e) {
      if (attempt < maxRetries - 1) {
        Utilities.sleep(Math.pow(2, attempt) * 1000); // 1 s, 2 s, 4 s
      }
    }
  }
  return [];
}


// ── Sheet writer ────────────────────────────────────────────────
/**
 * Writes all indicator rows to a sheet named 'MacroData'.
 * Creates the sheet if it does not exist; clears it on each run
 * so stale rows are removed.
 */
function refreshMacroData() {
  const apiKey = PropertiesService.getScriptProperties()
                                  .getProperty('FXMACRODATA_API_KEY');
  if (!apiKey) {
    throw new Error('FXMACRODATA_API_KEY script property is not set.');
  }

  const ss        = SpreadsheetApp.getActiveSpreadsheet();
  let   sheet     = ss.getSheetByName('MacroData');
  if (!sheet) {
    sheet = ss.insertSheet('MacroData');
  }
  sheet.clearContents();

  // Header row
  const headers = ['Currency', 'Indicator', 'Date', 'Value', 'AnnouncementDatetime'];
  sheet.appendRow(headers);

  // Style header
  const headerRange = sheet.getRange(1, 1, 1, headers.length);
  headerRange.setBackground('#1e3a5f');
  headerRange.setFontColor('#ffffff');
  headerRange.setFontWeight('bold');

  // Data rows
  INDICATORS.forEach(({ currency, indicator }) => {
    const records = fetchIndicator(currency, indicator, apiKey);
    records.forEach(r => {
      sheet.appendRow([
        currency.toUpperCase(),
        indicator,
        r.date,
        r.val,
        r.announcement_datetime || ''
      ]);
    });
  });

  // Auto-resize columns for readability
  sheet.autoResizeColumns(1, headers.length);
}

Step 7 — Run and verify

1. Click the Save icon (floppy disk) in the editor toolbar.
2. Select refreshMacroData in the function dropdown next to the Run button (▶).
3. Click Run. The first run asks for permission to access the spreadsheet and make external HTTP requests — click Review permissions → Allow.
4. Switch back to the spreadsheet. A new tab called MacroData should now contain rows like:

Step 8 — Schedule automatic refresh with a trigger

A time-driven trigger calls refreshMacroData on a schedule without any manual action.

1. In the Apps Script editor, click the Triggers icon (clock) in the left sidebar.
2. Click + Add Trigger (bottom-right).
3. Set Choose which function to run → refreshMacroData.
4. Set Select event source → Time-driven.
5. Set Select type of time-based trigger → Day timer.
6. Set Select time of day → e.g. 7am – 8am (before the European open).
7. Click Save. The trigger appears in the trigger list.

Step 9 — Pull the latest value into any cell with a custom function

Add the function below to Code.gs if you want to reference macro values directly in cell formulas — just like GOOGLEFINANCE() but for central-bank data:

/**
 * Custom Sheets function: returns the latest value for a currency/indicator.
 *
 * Usage in a cell:  =FXMD("usd","policy_rate")
 *
 * @param {string} currency   e.g. "usd"
 * @param {string} indicator  e.g. "policy_rate"
 * @customfunction
 */
function FXMD(currency, indicator) {
  const apiKey  = PropertiesService.getScriptProperties()
                                   .getProperty('FXMACRODATA_API_KEY');
  const records = fetchIndicator(currency, indicator, apiKey, 1);
  if (!records || records.length === 0) return null;
  return records[0].val;
}

After saving, type =FXMD("usd","policy_rate") in any cell. Sheets calls the function, fetches from the API, and returns the current value. Combine it with currency labels in adjacent cells to build a compact summary panel.

Summary

You have connected FXMacroData to both Excel and Google Sheets:

• Excel Power Query — a parameterisable M function that loads any indicator into a structured table and refreshes on file open.
• Excel VBA — a macro that writes the latest value of each indicator into named cells, assignable to a button.
• Google Apps Script — a refreshMacroData() function that builds a full MacroData tab, plus a =FXMD() custom formula for per-cell lookups, both refreshed automatically on a time-driven trigger.

From here, consider layering in more indicators — for example, adding core inflation alongside headline inflation, or pulling PMI for multiple currencies to compare growth momentum across regions. The consistent endpoint shape means every addition is a one-line change to the INDICATORS array or the M function call.