Living Excel: Data That Updates Itself
Ready-made Excel template with Power Query. Set it up once, get current data forever. No VBA, no macros, no manual work.
Why Living Excel?
Automatic Updates
Data updates when you open the file or with a click. Always the latest market data without manual import.
No VBA Required
Based on Power Query (integrated in Excel 2016+). No macros, no security warnings, works everywhere.
Offline Capable
Once loaded, data can be analyzed offline. Updates only when needed and with internet connection.
How It Works
Download Excel Template
Download the pre-configured GBM Excel template
Enter API Key
One-time configuration of your GBM API key
- Open the downloaded Excel file
- Go to Data → Queries & Connections
- Right-click on "MILAN_Data" → Edit
- In Power Query Editor: Replace your_api_key_here with your actual API key
- Click Close & Load
Select Country and Metric
Customize the data source to your needs
In Power Query Editor, you can adjust the following parameters:
- Country: Country code (e.g. "DE", "FR", "PL")
- Metric: Metric ID (e.g. "dwelling_permits", "construction_volume_res")
- Period: "monthly", "quarterly" or "annual"
- StartDate: Start date in format "YYYY-MM-DD"
Update Data
Automatic or manual — you decide
Automatic on Open
Data is updated every time you open the file
Setting: Data → Queries & Connections → Properties → "Refresh data when opening the file"
Manual on Demand
Update data only when you want
Shortcut: Data → Refresh All or Ctrl+Alt+F5
Power Query M-Code
The Excel template contains the following Power Query code. You can customize it or use it as a basis for your own queries.
let
// Configuration
ApiKey = "your_api_key_here",
Country = "DE",
Metric = "dwelling_permits",
Period = "monthly",
StartDate = "2023-01-01",
// API Call
BaseUrl = "https://gbmapi.bl2020.com",
Response = Web.Contents(
BaseUrl & "/dataseries",
[
Headers = [
#"X-API-Key" = ApiKey,
#"Content-Type" = "application/json"
],
Query = [
seriesid = Metric,
country = Country,
period = Period,
startyear = Text.Start(StartDate, 4)
]
]
),
JsonResponse = Json.Document(Response),
DataList = JsonResponse[data],
TableFromList = Table.FromList(
DataList,
Splitter.SplitByNothing(),
null, null, ExtraValues.Error
),
ExpandedTable = Table.ExpandRecordColumn(
TableFromList, "Column1",
{"date", "value", "yoy_change", "mom_change", "ma_3m", "ma_12m"},
{"Date", "Value", "YoY %", "MoM %", "MA 3M", "MA 12M"}
),
TypedTable = Table.TransformColumnTypes(
ExpandedTable,
{
{"Date", type date},
{"Value", Int64.Type},
{"YoY %", type number},
{"MoM %", type number},
{"MA 3M", type number},
{"MA 12M", type number}
}
)
in
TypedTablelet
// Configuration
ApiKey = "your_api_key_here",
Country = "PL",
Sector = "residential",
// API Call
BaseUrl = "https://gbmapi.bl2020.com",
Response = Web.Contents(
BaseUrl & "/forecast?country=" & Country & "§or=" & Sector,
[
Headers = [#"X-API-Key" = ApiKey]
]
),
JsonResponse = Json.Document(Response),
ForecastRecord = JsonResponse[forecast],
ForecastTable = Record.ToTable(ForecastRecord),
RenamedColumns = Table.RenameColumns(
ForecastTable,
{{"Name", "Year"}, {"Value", "Volume (Billion EUR)"}}
),
TypedTable = Table.TransformColumnTypes(
RenamedColumns,
{
{"Year", Int64.Type},
{"Volume (Billion EUR)", type number}
}
)
in
TypedTableWhat's Included in the Template?
Pre-configured Queries
- • Time series data (Permits, Starts, Completions)
- • Construction volume forecasts
- • Nowcast data (current quarter)
- • Country metadata
Ready-made Visualizations
- • Line charts for time series
- • Bar charts for forecasts
- • Pivot tables for analysis
- • Formatted dashboards
Calculated Fields
- • Year-over-Year change (%)
- • Month-over-Month change (%)
- • 3-month moving average
- • 12-month moving average
Customizable Parameters
- • Easy change of country and metric
- • Flexible time period (start/end date)
- • Selectable data frequency
- • Multiple countries in parallel possible
System Requirements
Ready for Self-Updating Data?
Get started with the Living Excel template in minutes.
