Keep NowMetrix metrics in Microsoft Excel

Excel requests the selected data directly from the NowMetrix REST API:

NowMetrix REST API → Power Query → Excel table → charts, formulas, or PivotTables

The example in this guide imports the last 30 available daily rows from /v1/overview. Each row contains a date, tracker, tracker timezone, pageviews, and visits. Refreshing the query replaces the imported table with the latest API response.

You need:

• a NowMetrix account with REST API access,
• access to the tracker you want to export,
• Microsoft Excel with Power Query, and
• permission to create and refresh external data connections.

On Windows, Power Query is available under Data → Get Data. On current Microsoft 365 versions for macOS, use Data → Get Data (Power Query). Menu names can differ slightly by Excel version and language.

If you cannot find the Power Query Editor on a Mac, update Microsoft 365 first. Microsoft documents the current Mac workflow in its Power Query for Excel for Mac guide.

1. Sign in to NowMetrix.
2. Open Settings → API.
3. Create a new key with a clear label, for example Microsoft Excel.
4. Copy the complete key immediately. It is shown only once.
5. Note the tracker ID for the website you want to export.

Excel for Windows

1. Create a new workbook or open the workbook that should contain the export.
2. Select Data → Get Data → From Other Sources → Blank Query.
3. In the Power Query Editor, select Home → Advanced Editor.

Excel for macOS

1. Create a new workbook or open the target workbook.
2. Select Data → Get Data (Power Query) → Launch Power Query Editor.
3. Create a Blank Query, then open Home → Advanced Editor.

1. Remove the existing content from the Advanced Editor.
2. Paste the complete query below.
3. Replace nm_YOUR_KEY and TRACKER_ID in the first two lines.
4. Leave the quotation marks around both values.
5. Select Done.

let
    ApiKey = "nm_YOUR_KEY",
    TrackerId = "TRACKER_ID",

    Response = Json.Document(
        Web.Contents(
            "https://api.nowmetrix.com",
            [
                RelativePath = "v1/overview",
                Query = [
                    site = TrackerId,
                    days = "30"
                ],
                Headers = [
                    Authorization = "Bearer " & ApiKey,
                    Accept = "application/json"
                ],
                Timeout = #duration(0, 0, 0, 30)
            ]
        )
    ),

    DailyRows = if List.IsEmpty(Response[daily]) then
        #table(type table [date = text, pageviews = Int64.Type, visits = Int64.Type], {})
    else
        Table.FromRecords(Response[daily]),

    AddTracker = Table.AddColumn(
        DailyRows,
        "tracker",
        each Response[site],
        type text
    ),
    AddTimezone = Table.AddColumn(
        AddTracker,
        "timezone",
        each Response[timezone],
        type text
    ),
    ReorderedColumns = Table.ReorderColumns(
        AddTimezone,
        {"date", "tracker", "timezone", "pageviews", "visits"}
    ),
    TypedColumns = Table.TransformColumnTypes(
        ReorderedColumns,
        {
            {"date", type date},
            {"tracker", type text},
            {"timezone", type text},
            {"pageviews", Int64.Type},
            {"visits", Int64.Type}
        }
    )
in
    TypedColumns

The fixed base URL plus RelativePath and Query keep the address readable and prevent the API key from appearing in the URL. The key is sent only in the HTTPS Authorization header.

Excel may ask how it should connect to https://api.nowmetrix.com.

1. Choose Anonymous.
2. Apply the setting to https://api.nowmetrix.com.
3. Select Connect.
4. If asked for a privacy level, select the level required by your organization. Private provides the strictest separation from other data sources.

1. Wait until the Power Query preview displays five columns.
2. Optionally rename the query to NowMetrix Daily.
3. Select Home → Close & Load.
4. Excel creates a worksheet table with the imported rows.

You can now create formulas, charts, PivotTables, or additional worksheets that reference this table. Keep manual notes and calculations outside the imported table because a refresh can resize or replace its rows.

Refresh manually

Select Data → Refresh All. Excel runs the query again and replaces the imported rows.

Refresh when the workbook opens

1. Open Data → Queries & Connections.
2. Right-click NowMetrix Daily and select Properties.
3. Enable Refresh data when opening the file.

Refresh at an interval

1. Open the same query properties.
2. Enable Refresh every and enter an interval such as 60 minutes.
3. Save the workbook.

Avoid unnecessarily frequent refreshes. The NowMetrix API limit is shared per tracker across API keys and users. Microsoft explains Excel connection settings in its external data refresh guide.

The REST API provides several datasets for different reporting needs:

• Use a separate NowMetrix key labeled for this workbook.
• Grant the NowMetrix user only the tracker access required for the export.
• Do not store the API key in a worksheet cell, formula, URL, screenshot, or comment.
• Do not upload the workbook to a public download area.
• Share the workbook only with people who are allowed to use the API key.
• Treat every workbook editor as able to inspect the Power Query and read its key.
• Protect access to OneDrive or SharePoint if the workbook is stored there.
• Revoke the dedicated key immediately if the workbook or account may be compromised.
• After rotating a key, update the first line of the query and refresh the workbook.