Excel and Power BI

Power Query (the data engine of Excel and Power BI Desktop) reads JSON over HTTPS with a custom header. Three steps.

1. Get an access token

   Follow Authentication with any OAuth-capable client, and copy the access token. It is valid for one hour; a scheduled refresh in the Power BI service needs the server setup.

2. Connect

   In Excel: Data → Get Data → From Other Sources → From Web. In Power BI Desktop: Get data → Web. Choose Advanced, enter the endpoint URL and add the HTTP header Authorization with the value Bearer <access_token>.

3. Shape the table

   Power Query opens the JSON record. Click the list you want (for example funds), then To Table, then expand the column to get one column per field.

#Ready-made queries

Paste these in Advanced Editor (replace the token).

let
    Source = Json.Document(Web.Contents(
        "https://jolv-mcp.vercel.app/v1/get_fund_synthesis",
        [Headers = [Authorization = "Bearer <access_token>"]])),
    Funds = Table.FromRecords(Source[funds]),
    Typed = Table.TransformColumnTypes(Funds, {
        {"nav_k_eur", type number}, {"distributed_k_eur", type number}, {"cost_k_eur", type number},
        {"tvpi", type number}, {"dpi", type number}, {"rvpi", type number}, {"net_irr_pct", type number},
        {"nav_as_of_date", type date}})
in
    Typed

let
    FundId = "<fund uuid>",
    Source = Json.Document(Web.Contents(
        "https://jolv-mcp.vercel.app/v1/list_portfolio",
        [Query = [fund_id = FundId], Headers = [Authorization = "Bearer <access_token>"]])),
    Holdings = Table.FromRecords(Source[holdings])
in
    Holdings

let
    Source = Json.Document(Web.Contents(
        "https://jolv-mcp.vercel.app/v1/list_campaigns",
        [Query = [updated_since = "2026-01-01"], Headers = [Authorization = "Bearer <access_token>"]])),
    Campaigns = Table.FromRecords(Source[campaigns])
in
    Campaigns

#Units and formats

• Amounts are in euros (€), thousands (€k) or millions (€m) depending on the field. The unit is in the field name (nav_k_eur) and in the API reference. Convert once in Power Query, not in every report.
• Multiples are plain numbers (1.57 means 1.57×). Percentages are plain numbers (14.2 means 14.2 %).
• Dates are ISO (2025-12-31); set the column type to date.
• null means "not published" or "not applicable", never zero.

#Refresh

Each refresh calls the API again. A token older than one hour fails with 401: edit the header with a fresh token, or move to a server integration for scheduled refreshes.