Power Query Templates

Power Query Templates

Paste-ready M queries for the most common ALM DataHub use cases. Drop one into Excel (Data → Get Data → From Other Sources → Blank Query → Advanced Editor), edit the few parameters at the top of the snippet, and Refresh. See /docs/api for the full function reference.

When set, Copy substitutes {{TOKEN}} with the value above. The token never leaves your browser. You'll need an API token to run these queries — start a free trial or sign in to create one.

let
    Token  = "{{TOKEN}}",
    Series = "sonia",  // sonia | bankrate | rpi | cpi | cpih | rpix | cpi_yoy | cpih_yoy | rpi_yoy
    From   = "2024-01-01",
    To     = "2024-12-31",

    Source   = Json.Document(Web.Contents("https://almdatahub.com/", [
        RelativePath = "api/v1/range/" & Series,
        Query        = [from = From, to = To],
        Headers      = [Authorization = "Bearer " & Token]
    ])),
    Rows     = Source[rows],
    Table    = Table.FromList(Rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(Table, "Column1", {"date", "value"})
in
    Expanded

let
    Token  = "{{TOKEN}}",
    Series = "gilt",   // gilt | ois
    Tenor  = 10,
    From   = "2024-01-01",
    To     = "2024-12-31",

    Source   = Json.Document(Web.Contents("https://almdatahub.com/", [
        RelativePath = "api/v1/range/" & Series,
        Query        = [tenor = Number.ToText(Tenor), from = From, to = To],
        Headers      = [Authorization = "Bearer " & Token]
    ])),
    Rows     = Source[rows],
    Table    = Table.FromList(Rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(Table, "Column1", {"date", "value"})
in
    Expanded

let
    Token = "{{TOKEN}}",
    On    = "2024-12-31",

    Url   = "https://almdatahub.com/" & "api/v1/lookup",
    Pairs = {"USD","EUR","JPY","CHF","CAD","AUD","NZD","NOK","SEK","DKK",
             "HKD","SGD","ZAR","CZK","HUF","PLN","ILS","CNY","KRW","TRY","INR","ERI"},

    Requests = List.Transform(Pairs, (p) => [
        id = p, fn = "FX", args = [pair = p, date = On]
    ]),
    Body     = Json.FromValue([requests = Requests]),
    Response = Json.Document(Web.Contents(Url, [
        Headers = [Authorization = "Bearer " & Token, #"Content-Type" = "application/json"],
        Content = Body
    ])),
    Results  = Response[results],
    Records  = List.Transform(Pairs, (p) => [pair = p, rate = Record.Field(Results, p)]),
    Output   = Table.FromRecords(Records)
in
    Output

let
    Token  = "{{TOKEN}}",
    Sex    = "male",       // male | female
    Period = "2022-2024",  // ONS period e.g. 2022-2024

    Source   = Json.Document(Web.Contents("https://almdatahub.com/", [
        RelativePath = "api/v1/range/mortality",
        Query        = [sex = Sex, period = Period],
        Headers      = [Authorization = "Bearer " & Token]
    ])),
    Rows     = Source[rows],
    Table    = Table.FromList(Rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(Table, "Column1", {"age", "value"})
in
    Expanded

let
    Token    = "{{TOKEN}}",
    On       = "2024-12-31",
    Currency = "GBP",         // GBP | EUR
    Sector   = "financial",   // financial | non-financial

    Url      = "https://almdatahub.com/" & "api/v1/lookup",
    Ratings  = {"AAA","AA","A","BBB","BB","B","CCC"},
    Tenors   = List.Numbers(1, 30, 1),

    Requests = List.Combine(List.Transform(Ratings, (r) =>
        List.Transform(Tenors, (t) => [
            id = r & "|" & Number.ToText(t),
            fn = "PRA_FS",
            args = [rating = r, tenor = t, date = On, currency = Currency, sector = Sector]
        ])
    )),
    Body     = Json.FromValue([requests = Requests]),
    Response = Json.Document(Web.Contents(Url, [
        Headers = [Authorization = "Bearer " & Token, #"Content-Type" = "application/json"],
        Content = Body
    ])),
    Results  = Response[results],
    Keys     = Record.FieldNames(Results),
    Records  = List.Transform(Keys, (k) =>
        let Parts = Text.Split(k, "|")
        in [tenor = Number.FromText(Parts{1}), rating = Parts{0}, value = Record.Field(Results, k)]
    ),
    Table1   = Table.FromRecords(Records),
    Pivoted  = Table.Pivot(Table1, Ratings, "rating", "value"),
    Sorted   = Table.Sort(Pivoted, {{"tenor", Order.Ascending}})
in
    Sorted

let
    Token     = "{{TOKEN}}",
    CurveType = "GILT_NOMINAL",  // GILT_NOMINAL | OIS | INFLATION | RFR_UK | RFR_EU | RFR_US | RFR_CA
    On        = "2024-12-31",
    Columns   = "both",          // spot | zcb | both
    // Optional overrides — leave as null to use auto-derived values.
    Alpha     = null,
    Ufr       = null,            // percentage, e.g. 4.0 for 4%

    // Build the Query record conditionally — Power Query's Web.Contents
    // Query option requires explicit fields, so add alpha/ufr only when
    // the user has overridden them.
    BaseQuery = [curve_type = CurveType, curve_date = On, columns = Columns],
    QueryWithAlpha = if Alpha = null then BaseQuery else Record.AddField(BaseQuery, "alpha", Number.ToText(Alpha)),
    FullQuery      = if Ufr   = null then QueryWithAlpha else Record.AddField(QueryWithAlpha, "ufr", Number.ToText(Ufr)),

    Source = Json.Document(Web.Contents("https://almdatahub.com/", [
        RelativePath = "api/v1/range/sw_curve",
        Query        = FullQuery,
        Headers      = [Authorization = "Bearer " & Token]
    ])),

    // Pull meta off the wrapper before drilling into rows.
    UfrUsed     = Source[ufr_used],
    UfrSource   = Source[ufr_source],
    AlphaUsed   = Source[alpha_used],
    AlphaSource = Source[alpha_source],

    Rows   = Source[rows],
    Table  = Table.FromList(Rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    ColList  = if Columns = "spot" then {"tenor_months", "spot"}
               else if Columns = "zcb" then {"tenor_months", "zcb"}
               else {"tenor_months", "spot", "zcb"},
    Expanded = Table.ExpandRecordColumn(Table, "Column1", ColList),

    // Append provenance columns — same value on every row, so users can
    // always see what UFR / alpha was used and where it came from.
    WithMeta = Table.AddColumn(
        Table.AddColumn(
            Table.AddColumn(
                Table.AddColumn(Expanded, "UFR_Used",     each UfrUsed),
                "UFR_Source",   each UfrSource
            ),
            "Alpha_Used",   each AlphaUsed
        ),
        "Alpha_Source", each AlphaSource
    )
in
    WithMeta

let
    Token     = "{{TOKEN}}",
    CurveType = "GILT_NOMINAL",
    Year      = 2025,
    Tenors    = {1, 2, 3, 5, 7, 10, 15, 20, 25, 30, 40},

    LastWorkday = (eom as date) as date =>
        let
            dow    = Date.DayOfWeek(eom, Day.Monday),
            offset = if dow = 5 then 1 else if dow = 6 then 2 else 0
        in
            Date.AddDays(eom, -offset),

    MonthEnds = List.Transform({1..12}, each
        Date.ToText(LastWorkday(Date.EndOfMonth(#date(Year, _, 1))), "yyyy-MM-dd")
    ),

    // One SW_CURVE GET per month-end (12 calls). Each response is the
    // full monthly curve to 60y; we slice rows where tenor_months = T*12
    // for each requested tenor T. Past-date responses are HTTP-cached
    // (Cache-Control: immutable), so opening this query in a future
    // Excel session is a no-op on the wire.
    // Use Web.Contents with RelativePath + Query (NOT a concatenated URL).
    // String-concatenated URLs make each call look like a new data source
    // to Power Query, forcing per-URL credential prompts. The base + Query
    // form binds credentials to the base URL once.
    FetchCurveByTenor = (d as text) as record =>
        let
            Source = Json.Document(Web.Contents("https://almdatahub.com/", [
                RelativePath = "api/v1/range/sw_curve",
                Query        = [
                    curve_type = CurveType,
                    curve_date = d,
                    columns    = "spot"
                ],
                Headers = [Authorization = "Bearer " & Token]
            ])),
            Rows   = Source[rows],
            ByTenor = List.Accumulate(Rows, [], (state, row) =>
                Record.AddField(state, Number.ToText(row[tenor_months]), row[spot])
            )
        in
            ByTenor,

    Records = List.Transform(MonthEnds, (d) =>
        let
            Curve   = FetchCurveByTenor(d),
            BaseRec = [date = d],
            WithTenors = List.Accumulate(Tenors, BaseRec, (state, t) =>
                Record.AddField(state, "T" & Number.ToText(t),
                    Record.FieldOrDefault(Curve, Number.ToText(t * 12), null))
            )
        in
            WithTenors
    ),

    Result = Table.FromRecords(Records)
in
    Result