Market data in Excel with Power Query

Beginner

Load markets and weekday performance into Excel tables that refresh with one click.

Store the key in a named cell

Put your key in a cell on a sheet you can hide, then name the cell ApiKey in the Name Box. Queries read it from there, so the key is not repeated in each query.

Create the Markets query

Choose Data → Get Data → From Other Sources → Blank Query, open Advanced Editor and paste:

Power Query (M)

let
    BaseUrl = "https://jokerprice.com",
    ApiKey = Excel.CurrentWorkbook(){[Name = "ApiKey"]}[Content]{0}[Column1],
    Response = Json.Document(Web.Contents(BaseUrl, [
        RelativePath = "api/v1/markets",
        Headers = [Authorization = "Bearer " & ApiKey]
    ])),
    Markets = Table.FromList(Response[markets], Splitter.SplitByNothing(), {"m"}),
    Base = Table.ExpandRecordColumn(Markets, "m", {"slug", "name", "listings", "nightly", "tracker"}),
    Nightly = Table.ExpandRecordColumn(Base, "nightly", {"median", "p25", "p75"}, {"median_sar", "p25_sar", "p75_sar"}),
    Tracker = Table.ExpandRecordColumn(Nightly, "tracker", {"maturity", "occupancy", "adr", "monthlyRevenuePerListing"}, {"maturity", "occupancy", "adr_sar", "revenue_per_listing_month"}),
    Typed = Table.TransformColumnTypes(Tracker, {{"listings", Int64.Type}, {"median_sar", type number}, {"p25_sar", type number}, {"p75_sar", type number}, {"occupancy", Percentage.Type}, {"adr_sar", type number}, {"revenue_per_listing_month", type number}})
in
    Typed

Name the query Markets and choose Close & Load.

Answer the prompts

When Excel asks how to connect to the URL, choose Anonymous: the key travels in the header, not as a Windows or web login. If you see a Formula.Firewall message, open Data → Get Data → Query Options → Privacy for this workbook and set both the workbook and the web source to the same privacy level (for example Organizational).

Add weekday performance

A second query reads /api/v1/metrics for one city and expands byWeekday. Weekday 0 is Sunday.

Power Query (M)

let
    BaseUrl = "https://jokerprice.com",
    ApiKey = Excel.CurrentWorkbook(){[Name = "ApiKey"]}[Content]{0}[Column1],
    City = "riyadh",
    Response = Json.Document(Web.Contents(BaseUrl, [
        RelativePath = "api/v1/metrics",
        Query = [city = City, past_days = "90", forward_days = "30"],
        Headers = [Authorization = "Bearer " & ApiKey]
    ])),
    Days = Table.FromRecords(Response[metrics][byWeekday]),
    Named = Table.AddColumn(Days, "day", each {"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"}{[weekday]}, type text),
    Typed = Table.TransformColumnTypes(Named, {{"occupancy", Percentage.Type}, {"adr", type number}})
in
    Typed

Insert a column chart on the result to see the weekend lift.

Refresh

Data → Refresh All re-runs both queries: two requests. Under Query Properties you can refresh on open; avoid short automatic intervals, since market reports change at most every 30 minutes.