Excel Analytics Engine: From API to Insight

Advanced Excel-based dashboard powered by API, Power Query and DAX

Value Proposition
- Executive-ready analytics tool designed for decision-makers.
- All-in-Excel architecture — no external tools required.
- Back-end powered— Excel stays fast, clean, and scalable.
- Insight-first design — quick time-to-insight with clean UI.
- Professional storytelling — McKinsey-style custom visuals.
Technology Stack
- Data Source: World Bank REST API (SDMX-JSON)
- Backend: Power Query (M), VBA + JSON parsing
- Data Modeling: Excel Data Model / Power Pivot
- Metrics Engine: DAX formulas
- Frontend: Excel Charts + Slicers

Pipeline

REST API via VBA

Connecting to World Bank Data

Accesses World Bank Open Data via custom VBA macro
Executes RESTful API calls using XmlHttp
Parses JSON with tailored VBA parser (JsonConverter)
Offers Power Query–based API as a fallback option

Power Query (M)

Data Preparation

Extracts the latest values by grouping and slicing top 2 years
Applies weighted logic to aggregate relative indicators
Enforces clean schema with strict typing and structure
Assigns indicator types to drive downstream DAX logic
Delivers an analysis-ready, high-performance table to model

Power Pivot (Data Model)

Semantic Modeling

Structures fact and dimension tables with referential integrity
Establishes logical relationships and role-based lookups
Enables context-aware filters and dynamic aggregation

DAX

Business Logic Layer

Switches aggregation logic: SUM for absolutes, weighted AVG for relatives
Calculates YoY trends with error handling and fallback logic
Implements percentile-based scoring via z-normalization
Flags Top-N entities for insight-driven comparisons
Powers visuals with fast, modular measures

Excel
(All-in-One)

Visualization & Delivery

Performant, portable workbook — no volatile formulas
Interactive dashboards styled with consulting-grade visuals
Includes slicers, dynamic filters, and a clean UI for insights
Automates refresh via macro-triggered data load

Step 1. Connecting to the World Bank API

Start loading updated data from the World Bank API by clicking the 'Upload World Bank Data' button.

See Code & Excel File on GitHub

End-to-End Flow of API-Powered Import

This project connects directly to the World Bank Open Data API using native VBA.

The macro sends RESTful HTTP requests via `XmlHttp`, retrieves SDMX-JSON datasets, and parses them using a custom-built `JsonConverter` module.

Results are dynamically written into Excel tables, with support for parameters like countries, indicators, and years. No external libraries required.

Step 2: Data Preparation in Power Query

To keep the Excel workbook fast and scalable, all raw data transformations are offloaded to Power Query. In this stage, the World Bank JSON data is cleaned, reshaped, and enriched — converting nested records into structured, analysis-ready tables.

The query LatestData demonstrates this logic in action and serves as a canonical example of how raw API data is turned into business-relevant signals.

In the example below, LatestData performs the following steps:

Groups the dataset by country and indicator
Extracts the two latest available years (latest and previous)
Handles both absolute and relative indicators, including weights
Normalizes GDP to $B and ensures schema consistency

The resulting table feeds directly into the Data Model and supports a variety of KPIs, YoY comparisons, and ranking logic downstream.

let
    // === STEP 1: LOAD SOURCE DATA ===
    Source = Dataset,

    // === STEP 2: GROUP AND SORT ===
    GroupedRows = Table.Group(
        Source,
        {"Countries.Country", "Countries.Region", "Countries.Income Group", "Countries.Weight", "Indicators.Indicator", "Indicators.Unit", "Indicators.Type"},
        {
            {
                "GroupedData",
                each Table.FirstN(
                    Table.Sort(_, {{"Year", Order.Descending}}),
                    2
                ),
                type table [
                    Countries.Region = nullable text,
                    Countries.Country = text,
                    Countries.Income group = text,
                    Indicators.Indicator = text,
                    Indicators.Type = text,
                    Indicators.Unit = text,
                    Countries.Weight = nullable number,
                    Year = nullable number,
                    Value = nullable number
                ]
            }
        }
    ),

    // === STEP 3: EXTRACT STRUCTURED RECORDS ===
    Extracted = Table.AddColumn(
        GroupedRows,
        "ExtractedValues",
        each [
            Region = Record.FieldOrDefault([GroupedData]{0}, "Countries.Region"),
            Country = Record.FieldOrDefault([GroupedData]{0}, "Countries.Country"),
            Income group = Record.FieldOrDefault([GroupedData]{0}, "Countries.Income Group"),
            Weight = Record.FieldOrDefault([GroupedData]{0}, "Countries.Weight"),
            Indicator = Record.FieldOrDefault([GroupedData]{0}, "Indicators.Indicator"),
            Unit = Record.FieldOrDefault([GroupedData]{0}, "Indicators.Unit"),
            Type = Record.FieldOrDefault([GroupedData]{0}, "Indicators.Type"),
            ValueLastYear = try Number.From(Record.FieldOrDefault([GroupedData]{0}, "Value")) otherwise null,
            ValuePreviousYear = try Number.From(Record.FieldOrDefault([GroupedData]{1}, "Value")) otherwise null
        ]
    ),

    // === STEP 4: BUILD FINAL TABLE WITH EXPLICIT TYPE DEFINITION ===
    FinalTable = Table.FromRecords(Extracted[ExtractedValues]),

    Final = Table.TransformColumns(FinalTable, {
        {"Weight", each _, type nullable number},
        {"ValueLastYear", each Number.Round(Number.FromText(Text.From(_)), 4), type number},
        {"ValuePreviousYear", each Number.Round(Number.FromText(Text.From(_)), 4), type number}
    }),

    #"Filtered Rows" = Table.SelectRows(Final, each true)
in
    #"Filtered Rows"

Step 3: Data Modeling in Power Pivot

Once the data is cleaned and enriched in Power Query, it is loaded into Power Pivot for semantic modeling. This step builds logical relationships between dimension tables (e.g. Geo, Calendar, Indicators) and the fact table. The goal: enable efficient filtering, slicing, and context-aware calculations in DAX.

In this model, LatestData acts as the central fact table. All other dimension tables feed filter context into DAX calculations. By explicitly defining one-to-many relationships, we ensure fast queries, consistent slicing, and reusable logic across the dashboard.

Step 4: Business Logic with DAX

With a clean data model in place, DAX is used to define the business logic: dynamic measures, KPIs, rankings, and advanced calculations. The logic adapts to the indicator type (absolute or relative), handles missing data, and drives all visualizations in the dashboard.

Example: Aggregation by indicator type (Absolute/Relative)

These custom DAX measures enable flexible, insight-driven visualizations. By avoiding hardcoded fields and relying on context-aware logic, the model supports dynamic filters, time comparisons, and ranking logic — all without writing a single Excel formula in the grid.

LastYearValue:=SWITCH(
    TRUE();

    // Case 1: Absolute indicator (Type = "A")
    // Return a simple SUM of ValueLastYear
    HASONEVALUE(LatestData[Type]) &&
    VALUES(LatestData[Type]) = "A";
        SUM(LatestData[ValueLastYear]);

    // Case 2: Relative indicator (Type = "R")
    // Return a weighted AVERAGE of ValueLastYear using Weight
    HASONEVALUE(LatestData[Type]) &&
    VALUES(LatestData[Type]) = "R";
        DIVIDE(
            // Numerator: sum of (ValueLastYear × Weight)
            SUMX(
                LatestData;
                LatestData[ValueLastYear] * LatestData[Weight]
            );
            // Denominator: sum of weights
            SUMX(
                LatestData;
                LatestData[Weight]
            );
            // Fallback result in case of division by zero
            BLANK()
        );

    // Default case: no valid type selected — return BLANK
    BLANK()
)

Step 5: Interactive Dashboard in Excel.

The final step brings it all together into a sleek, professional-grade dashboard — directly inside Excel. Designed in a McKinsey-style format, it combines intuitive visual storytelling with dynamic interaction, all backed by the DAX logic layer and optimized data model.

Users can interact with region filters, switch between indicators, and instantly view ranked insights. Thanks to Power Pivot and DAX, even complex aggregations — such as weighted averages or YoY trends — are rendered in real-time with zero lag.

From API to Dashboard — Excel Engine in Action

I’m open to new job opportunities, projects, and collaborations — feel free to reach out.
Aleksei Ogarkov

Feel free to contact me.

Aleksei Ogarkov

Home

Excel Analytics Engine: From API to Insight

Value Proposition

Technology Stack