Difference between revisions of "FXLDataLookup and FXLDataPeriod Functions"

From Fusion Registry Wiki
Jump to navigation Jump to search
(→‎FXLDataLookup Formula)
 
(20 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
[[Category:Functions Reference FXLData]]
 
Introduced in H1.3.0.03 (January 2026).  
 
Introduced in H1.3.0.03 (January 2026).  
  
Line 21: Line 22:
 
depending on how many items and periods you pass.
 
depending on how many items and periods you pass.
  
−
'''Syntax'''
+
===== Syntax =====
 
FXLDataLookup(items, periods, sheetName, [autoRefresh], [errorMessages])
 
FXLDataLookup(items, periods, sheetName, [autoRefresh], [errorMessages])
  
−
'''Parameters'''
+
===== Parameters =====
  
 
'''items'''  
 
'''items'''  
Line 41: Line 42:
 
* OPTIONAL, Defaults to FALSE  
 
* OPTIONAL, Defaults to FALSE  
 
* TRUE/FALSE. When TRUE, returns descriptive messages (e.g., “Key Not Found”, “Non‑numeric Value”, “Divide By Zero”, “Invalid Power”, “Parse Error”).
 
* TRUE/FALSE. When TRUE, returns descriptive messages (e.g., “Key Not Found”, “Non‑numeric Value”, “Divide By Zero”, “Invalid Power”, “Parse Error”).
 +
 +
===== Supported Operators - items parameter =====
 +
'''Arithmetic'''
 +
* + (add)
 +
* - (subtract)
 +
* * (multiply)
 +
* / (divide)
 +
* ^ (power)
 +
* Standard arithmetic precedence applies;
 +
* parentheses can be used to control evaluation order
 +
 +
'''Unary signs:'''
 +
* leading -
 +
* and + are supported
 +
 +
'''Exponent constraint''': for ^, the right‑hand exponent must be numeric, either a literal or a cell reference to a numeric value; using a series key on the right‑hand side raises Invalid Power. The left‑hand/base may be a series key.
 +
 +
===== Wildcards - periods parameter =====
 +
'''Period wildcards''' let you pass a pattern like "2001*"; the function returns that period and all later periods already present in the referenced root grid (e.g., 2001, 2002, 2003).
 +
 +
==== FXLDataPeriod Formula ====
 +
FXLDataPeriod scans an existing root grid on a specified worksheet and returns the SDMX period(s) available in that grid, either the minimum, the maximum, or both, depending on the mode you pass.
 +
 +
===== Syntax =====
 +
FXLDataPeriod(sheetName, [what], [errorMessages])
 +
 +
===== Parameters =====
 +
'''sheetName''' - The worksheet containing the root grid with the expected header/layout
 +
'''what'''
 +
* OPTIONAL, Defaults to BOTH
 +
* MIN - returns the first period
 +
* MAX - returns the last period
 +
* BOTH - returns both the first and last periods
 +
'''errorMessages'''
 +
* OPTIONAL, Defaults to FALSE
 +
* TRUE/FALSE. When TRUE, returns descriptive messages (e.g., “Grid Not Found”).
 +
 +
=== FXLDataLookup Examples ===
 +
 +
The examples below are based on the following root grid sheet.
 +
 +
[[File:Example-spreadsheet.png|1000px]]
 +
 +
==== Basics ====
 +
Single value (one series + one period)
 +
 +
=FXLDataLookup("SE1:STERLING:ENGLAND","2002","RootGrid")
 +
 +
'''Result''': 99
 +
 +
'''Why''': Pulls the 2002 value for the series key.
 +
 +
---------------------------------------------------------------------------------------------------------
 +
==== Multiple series (range) for one period ====
 +
=FXLDataLookup(RootGrid!G5:G8,"2002","RootGrid")
 +
'''Result (row‑wise):'''
 +
 +
* SC1:STERLING:CHINA → 14
 +
* SG1:STERLING:GERMANY → 333
 +
* EI1:EURO:IRELAND → 15
 +
* EG1:EURO:GERMANY → 26
 +
 +
=FXLDataLookup(RootGrid!G5:G8,"2002","RootGrid")
 +
'''Result (row‑wise):'''
 +
 +
SC1:STERLING:CHINA → 14
 +
SG1:STERLING:GERMANY → 333
 +
EI1:EURO:IRELAND → 15
 +
EG1:EURO:GERMANY → 26
 +
 +
---------------------------------------------------------------------------------------------------------
 +
==== Multiple series × multiple periods (ranges) ====
 +
=FXLDataLookup(RootGrid!G5:G8, RootGrid!I2:J2, "RootGrid")
 +
 +
'''Result (examples):'''
 +
 +
* 2002 → 14, 333, 15, 26
 +
* 2003 → 45, 55, 12, 45
 +
 +
---------------------------------------------------------------------------------------------------------
 +
==== Auto‑refresh on (even if Excel calc mode is Manual) ====
 +
=FXLDataLookup("SE1:STERLING:ENGLAND","2002","RootGrid",TRUE)
 +
 +
'''Result''': 99 (same value; it just auto‑refreshes) [FXLDataLookupSamples | Excel]
 +
 +
===== Arithmetic with series =====
 +
 +
Addition
 +
=FXLDataLookup("SE1:STERLING:ENGLAND+EG1:EURO:GERMANY","2002","RootGrid")
 +
 +
Result: 125 (99 + 26)
 +
 +
---------------------------------------------------------------------------------------------------------
 +
'''Subtraction'''
 +
=FXLDataLookup("SE1:STERLING:ENGLAND-EG1:EURO:GERMANY","2002","RootGrid")
 +
 +
'''Result''': 73 (99 − 26)
 +
 +
---------------------------------------------------------------------------------------------------------
 +
'''Multiplication'''
 +
=FXLDataLookup("SE1:STERLING:ENGLAND*EG1:EURO:GERMANY","2002","RootGrid")
 +
 +
Result: 2574 (99 × 26)
 +
 +
---------------------------------------------------------------------------------------------------------
 +
'''Division'''
 +
=FXLDataLookup("SE1:STERLING:ENGLAND/EG1:EURO:GERMANY","2002","RootGrid")
 +
 +
'''Result''': 3.81 (99 ÷ 26)
 +
 +
===== Order of operations, exponent, parentheses, unary =====
 +
'''Exponent with numeric literal'''
 +
=FXLDataLookup("SE1:STERLING:ENGLAND*2^2","2002","RootGrid")
 +
 +
'''Result''': 396 (99 × 2²)
 +
 +
---------------------------------------------------------------------------------------------------------
 +
'''Parentheses respected'''
 +
=FXLDataLookup("(SE1:STERLING:ENGLAND)*(10-2)","2002","RootGrid")
 +
 +
Result: 792 (99 × (10−2))
 +
 +
---------------------------------------------------------------------------------------------------------
 +
'''Unary minus'''
 +
=FXLDataLookup("-SE1:STERLING:ENGLAND*2","2002","RootGrid")
 +
 +
'''Result''': -198 (−99 × 2)
 +
 +
---------------------------------------------------------------------------------------------------------
 +
'''Unary plus'''
 +
=FXLDataLookup("+(SE1:STERLING:ENGLAND-10000)","2002","RootGrid")
 +
 +
'''Result''': -9901 (+(99−10000))
 +
 +
---------------------------------------------------------------------------------------------------------
 +
'''Multiple operands in one go'''
 +
=FXLDataLookup("SE1:STERLING:ENGLAND*SE1:STERLING:ENGLAND-SI1:STERLING:IRELAND/EG1:EURO:GERMANY","2002","RootGrid")
 +
 +
'''Result''': 9799.62
 +
 +
---------------------------------------------------------------------------------------------------------
 +
===== Wildcards (period expansion) =====
 +
'''Expand 2001* to all matching periods'''
 +
 +
=FXLDataLookup(RootGrid!G5:G8, "2001*", "RootGrid")
 +
 +
'''Result''' (grid excerpt):
 +
 +
* 2001 → 16, 76, 11, 11
 +
* 2002 → 14, 333, 15, 26
 +
* 2003 → 45, 55, 12, 45
 +
(Order aligns with series in RootGrid!G5:G8)
 +
 +
===== Period helpers (using SDMXPeriod(...) inside) =====
 +
'''Min period'''
 +
 +
=FXLDataLookup("SE1:STERLING:ENGLAND", SDMXPeriod("RootGrid","MIN"), "RootGrid")
 +
 +
Result: 13 (value at min period 2001)
 +
 +
---------------------------------------------------------------------------------------------------------
 +
'''Max period'''
 +
 +
=FXLDataLookup("SE1:STERLING:ENGLAND", SDMXPeriod("RootGrid","MAX"), "RootGrid")
 +
 +
'''Result''': 45 (value at max period 2003)
 +
 +
---------------------------------------------------------------------------------------------------------
 +
'''Min and Max (2‑cell spill)'''
 +
 +
=FXLDataLookup("SE1:STERLING:ENGLAND", SDMXPeriod("RootGrid"), "RootGrid")
 +
 +
'''Result''' (two cells): 13, 45
 +
 +
'''Errors'''
 +
'''Sheet not found'''
 +
=FXLDataLookup("SE1:STERLING:ENGLAND","2002","NonExistantRootGrid",FALSE,TRUE)
 +
 +
'''Result''': Sheet Not Found
 +
 +
---------------------------------------------------------------------------------------------------------
 +
'''Key not found (typo in series key)'''
 +
 +
=FXLDataLookup("SE1:STERLING:ENGLANlD","2002","RootGrid",FALSE,TRUE)
 +
 +
'''Result''': Key Not Found
 +
 +
---------------------------------------------------------------------------------------------------------
 +
'''Non‑numeric value in grid'''
 +
 +
=FXLDataLookup("SI1:STERLING:IRELAND","2003","RootGrid",FALSE,TRUE)
 +
 +
'''Result''': Non-numeric Value SI1:STERLING:IRELAND (cell value is g)
 +
 +
---------------------------------------------------------------------------------------------------------
 +
'''Grid not found (bad RootGrid layout)'''
 +
 +
=FXLDataLookup("SE1:STERLING:ENGLAND","2002","RootGridAlternate",FALSE,TRUE)
 +
 +
'''Result''': Grid Not Found (header row not on row 2)
 +
 +
---------------------------------------------------------------------------------------------------------
 +
'''Divide by zero'''
 +
 +
=FXLDataLookup("SE1:STERLING:ENGLAND/(EG1:EURO:GERMANY-26)","2002","RootGrid",FALSE,TRUE)
 +
 +
'''Result''': Divide By Zero (26−26 = 0) [FXLDataLookupSamples | Excel]
 +
 +
---------------------------------------------------------------------------------------------------------
 +
'''Invalid power'''
 +
 +
=FXLDataLookup("SE1:STERLING:ENGLAND^(EG1:EURO:GERMANY-26)","2002","RootGrid",FALSE,TRUE)
 +
 +
'''Result''': Invalid Power (exponent expression based on series key is invalid, use a numeric literal or numeric cell)
 +
 +
---------------------------------------------------------------------------------------------------------
 +
'''Parse error (operator typo)'''
 +
 +
=FXLDataLookup("SE1:STERLING:ENGLAND**SE1:STERLING:ENGLAND-SI1:STERLING:IRELAND/EG1:EURO:GERMANY","2002","RootGrid",FALSE,TRUE)
 +
 +
'''Result''': Parse Error
 +
 +
---------------------------------------------------------------------------------------------------------
 +
=== FXLDataPeriod Examples ===
 +
The examples below are based on the following root grid sheet.
 +
 +
[[File:Root-grid.png|1000px]]
 +
 +
===== Minimum period (single value) =====
 +
 +
=SDMXPeriod("RootGrid","MIN")
 +
 +
'''Result''': 2001
 +
Why: Returns the earliest (minimum) SDMX period available on the root grid sheet.
 +
 +
---------------------------------------------------------------------------------------------------------
 +
===== Maximum period (single value) =====
 +
 +
=SDMXPeriod("RootGrid","MAX")
 +
 +
'''Result''': 2003
 +
Why: Returns the latest (maximum) SDMX period available on the root grid sheet.
 +
 +
---------------------------------------------------------------------------------------------------------
 +
===== Range (min and max — two‑cell spill) =====
 +
 +
=SDMXPeriod("RootGrid")
 +
 +
'''Result''' (two cells): 2001, 2003
 +
'''Why''': Returns both the MIN and MAX SDMX periods from the root grid sheet.
 +
 +
---------------------------------------------------------------------------------------------------------
 +
===== Error: sheet not found =====
 +
 +
=SDMXPeriod("NonExistantRootGrid",,TRUE)
 +
 +
'''Result''': Sheet Not Found
 +
'''Why''': The specified sheet name doesn’t exist, so the function returns a “Sheet Not Found” error.
 +
 +
---------------------------------------------------------------------------------------------------------
 +
 +
=== Troubleshooting ===
 +
===== MIN period isn’t the earliest in the grid =====
 +
 +
'''Why''': SDMXPeriod("…","MIN") returns the first period it finds in the grid header, which is typically (but not guaranteed to be) the earliest.
 +
 +
'''Fix''': Sort your period headers left→right so the earliest period is first.
 +
 +
---------------------------------------------------------------------------------------------------------
 +
===== MAX period isn’t the latest in the grid =====
 +
 +
'''Why''': SDMXPeriod("…","MAX") returns the last period it finds in the grid header, which is typically (but not guaranteed to be) the latest.
 +
 +
'''Fix''': Sort your period headers left→right so the latest period is last.
 +
 +
---------------------------------------------------------------------------------------------------------
 +
===== FXLDataLookup or SDMXPeriod behaves oddly in “vertical mode” =====
 +
 +
'''Why''': Both functions expect a horizontal grid (periods across columns) like the RootGrid examples; vertical layouts aren’t supported.
 +
 +
'''Fix''': Render the root grid with periods in the header row and values beneath them.
 +
 +
---------------------------------------------------------------------------------------------------------
 +
===== Grid not found =====
 +
 +
'''Why''': The grid header (dimensions + period labels) must be on row 2 of the root sheet; otherwise the grid isn’t detected.
 +
 +
'''Fix''': Move headers to row 2 on the root sheet.
 +
 +
---------------------------------------------------------------------------------------------------------
 +
===== Sheet not found =====
 +
 +
'''Why''': The sheet name you passed doesn’t match an actual worksheet.
 +
 +
'''Fix''': Use the exact sheet name that exists in the workbook.
 +
 +
---------------------------------------------------------------------------------------------------------
 +
===== Key not found =====
 +
 +
Why: The series key is misspelled or doesn’t exist in the root grid’s Series column.
 +
Fix: Copy–paste the key from the grid (avoid typos) and ensure the root grid includes full series keys.
 +
 +
---------------------------------------------------------------------------------------------------------
 +
===== Expected multiple cells, but only one cell returned =====
 +
 +
'''Why''': Multi‑cell (“spilled”) results require a modern Excel (later than 2016) that supports dynamic arrays.
 +
 +
---------------------------------------------------------------------------------------------------------
 +
===== Invalid power =====
 +
 +
'''Why''': The right‑hand side of ^ must be a numeric literal or a reference to a numeric cell; a series‑derived expression/key is invalid (see example using …^(EG1:EURO:GERMANY-26)).
 +
 +
'''Fix''': Replace the exponent with a number (e.g., ^2) or a cell that contains a number.
 +
 +
---------------------------------------------------------------------------------------------------------
 +
===== Non‑numeric value in grid =====
 +
 +
'''Why''': The target cell in the root grid holds text (e.g., g), so numeric calculation fails.
 +
 +
'''Fix''': Correct the source cell to a number or point the formula to a numeric period.
 +
 +
---------------------------------------------------------------------------------------------------------
 +
===== Divide by zero =====
 +
 +
'''Why''': Your denominator evaluates to 0 (e.g., EG1:EURO:GERMANY - 26 = 0).
 +
 +
'''Fix''': Choose a non‑zero reference.
 +
 +
---------------------------------------------------------------------------------------------------------
 +
===== Parse error =====
 +
 +
'''Why''': The items/expression string has a syntax error (operator typo, or missing parenthesis).
 +
 +
'''Fix''': Check operators and parentheses; compare against a working example and re‑enter carefully.

Latest revision as of 23:43, 22 January 2026

Introduced in H1.3.0.03 (January 2026).

Fusion XL Data - FXLDataLookup and FXLDataPeriod Formulas

Context

To date, FXLData has been used primarily to generate data cubes in Excel from a Fusion Registry by applying user‑defined selection criteria. The add‑in issues SDMX REST queries to the Fusion Registry and renders the results as a structured grid, typically one series per row (time across columns) or one series per column, so analysts can work with a familiar worksheet layout.

Problem

The current workflow is geared to building full grids/cubes from Fusion Registry; getting a single value or a small ad‑hoc slice often means running a broader query and then indexing/filtering the filled grid, which adds unnecessary steps for simple asks.

Solution

2 New Formulas have been introduced:

  • FXLDataLookup retrieves targeted measures directly from an existing root grid, returning a single value, short list, or small table. It also supports simple arithmetic, period wildcards, optional auto‑refresh, and explicit error messages.
  • FXLDataPeriod returns the minimum and/or maximum SDMX periods found on a specified root grid so lookups can use dataset bounds without manual date handling (with sheet‑not‑found error handling).

FXLDataLookup Formula

FXLDataLookup reads values from an existing root grid on a specified worksheet and returns the measures for the given series key(s) and period(s) as a

  • single value
  • short list
  • or small table

depending on how many items and periods you pass.

Syntax

FXLDataLookup(items, periods, sheetName, [autoRefresh], [errorMessages])

Parameters

items

  • A single series key (text)
  • a range of keys
  • or an expression combining series keys and numbers with supported operators.

periods

  • A single period
  • a range of periods
  • a wildcard (e.g., "2001*")

sheetName - The worksheet containing the root grid with the expected header/layout [autoRefresh]

  • OPTIONAL, Defaults to FALSE
  • TRUE/FALSE. When TRUE, forces updates only if Excel is not in automatic calculation mode.

[errorMessages]

  • OPTIONAL, Defaults to FALSE
  • TRUE/FALSE. When TRUE, returns descriptive messages (e.g., “Key Not Found”, “Non‑numeric Value”, “Divide By Zero”, “Invalid Power”, “Parse Error”).
Supported Operators - items parameter

Arithmetic

  • + (add)
  • - (subtract)
  • * (multiply)
  • / (divide)
  • ^ (power)
  • Standard arithmetic precedence applies;
  • parentheses can be used to control evaluation order

Unary signs:

  • leading -
  • and + are supported

Exponent constraint: for ^, the right‑hand exponent must be numeric, either a literal or a cell reference to a numeric value; using a series key on the right‑hand side raises Invalid Power. The left‑hand/base may be a series key.

Wildcards - periods parameter

Period wildcards let you pass a pattern like "2001*"; the function returns that period and all later periods already present in the referenced root grid (e.g., 2001, 2002, 2003).

FXLDataPeriod Formula

FXLDataPeriod scans an existing root grid on a specified worksheet and returns the SDMX period(s) available in that grid, either the minimum, the maximum, or both, depending on the mode you pass.

Syntax

FXLDataPeriod(sheetName, [what], [errorMessages])

Parameters

sheetName - The worksheet containing the root grid with the expected header/layout what

  • OPTIONAL, Defaults to BOTH
  • MIN - returns the first period
  • MAX - returns the last period
  • BOTH - returns both the first and last periods

errorMessages

  • OPTIONAL, Defaults to FALSE
  • TRUE/FALSE. When TRUE, returns descriptive messages (e.g., “Grid Not Found”).

FXLDataLookup Examples

The examples below are based on the following root grid sheet.

Example-spreadsheet.png

Basics

Single value (one series + one period)

=FXLDataLookup("SE1:STERLING:ENGLAND","2002","RootGrid")

Result: 99

Why: Pulls the 2002 value for the series key.

Multiple series (range) for one period

=FXLDataLookup(RootGrid!G5:G8,"2002","RootGrid") Result (row‑wise):

  • SC1:STERLING:CHINA → 14
  • SG1:STERLING:GERMANY → 333
  • EI1:EURO:IRELAND → 15
  • EG1:EURO:GERMANY → 26

=FXLDataLookup(RootGrid!G5:G8,"2002","RootGrid") Result (row‑wise):

SC1:STERLING:CHINA → 14 SG1:STERLING:GERMANY → 333 EI1:EURO:IRELAND → 15 EG1:EURO:GERMANY → 26

Multiple series × multiple periods (ranges)

=FXLDataLookup(RootGrid!G5:G8, RootGrid!I2:J2, "RootGrid")

Result (examples):

  • 2002 → 14, 333, 15, 26
  • 2003 → 45, 55, 12, 45

Auto‑refresh on (even if Excel calc mode is Manual)

=FXLDataLookup("SE1:STERLING:ENGLAND","2002","RootGrid",TRUE)

Result: 99 (same value; it just auto‑refreshes) [FXLDataLookupSamples | Excel]

Arithmetic with series

Addition =FXLDataLookup("SE1:STERLING:ENGLAND+EG1:EURO:GERMANY","2002","RootGrid")

Result: 125 (99 + 26)

Subtraction =FXLDataLookup("SE1:STERLING:ENGLAND-EG1:EURO:GERMANY","2002","RootGrid")

Result: 73 (99 − 26)

Multiplication =FXLDataLookup("SE1:STERLING:ENGLAND*EG1:EURO:GERMANY","2002","RootGrid")

Result: 2574 (99 × 26)

Division =FXLDataLookup("SE1:STERLING:ENGLAND/EG1:EURO:GERMANY","2002","RootGrid")

Result: 3.81 (99 ÷ 26)

Order of operations, exponent, parentheses, unary

Exponent with numeric literal =FXLDataLookup("SE1:STERLING:ENGLAND*2^2","2002","RootGrid")

Result: 396 (99 × 2²)

Parentheses respected =FXLDataLookup("(SE1:STERLING:ENGLAND)*(10-2)","2002","RootGrid")

Result: 792 (99 × (10−2))

Unary minus =FXLDataLookup("-SE1:STERLING:ENGLAND*2","2002","RootGrid")

Result: -198 (−99 × 2)

Unary plus =FXLDataLookup("+(SE1:STERLING:ENGLAND-10000)","2002","RootGrid")

Result: -9901 (+(99−10000))

Multiple operands in one go =FXLDataLookup("SE1:STERLING:ENGLAND*SE1:STERLING:ENGLAND-SI1:STERLING:IRELAND/EG1:EURO:GERMANY","2002","RootGrid")

Result: 9799.62

Wildcards (period expansion)

Expand 2001* to all matching periods

=FXLDataLookup(RootGrid!G5:G8, "2001*", "RootGrid")

Result (grid excerpt):

  • 2001 → 16, 76, 11, 11
  • 2002 → 14, 333, 15, 26
  • 2003 → 45, 55, 12, 45

(Order aligns with series in RootGrid!G5:G8)

Period helpers (using SDMXPeriod(...) inside)

Min period

=FXLDataLookup("SE1:STERLING:ENGLAND", SDMXPeriod("RootGrid","MIN"), "RootGrid")

Result: 13 (value at min period 2001)

Max period

=FXLDataLookup("SE1:STERLING:ENGLAND", SDMXPeriod("RootGrid","MAX"), "RootGrid")

Result: 45 (value at max period 2003)

Min and Max (2‑cell spill)

=FXLDataLookup("SE1:STERLING:ENGLAND", SDMXPeriod("RootGrid"), "RootGrid")

Result (two cells): 13, 45

Errors Sheet not found =FXLDataLookup("SE1:STERLING:ENGLAND","2002","NonExistantRootGrid",FALSE,TRUE)

Result: Sheet Not Found

Key not found (typo in series key)

=FXLDataLookup("SE1:STERLING:ENGLANlD","2002","RootGrid",FALSE,TRUE)

Result: Key Not Found

Non‑numeric value in grid

=FXLDataLookup("SI1:STERLING:IRELAND","2003","RootGrid",FALSE,TRUE)

Result: Non-numeric Value SI1:STERLING:IRELAND (cell value is g)

Grid not found (bad RootGrid layout)

=FXLDataLookup("SE1:STERLING:ENGLAND","2002","RootGridAlternate",FALSE,TRUE)

Result: Grid Not Found (header row not on row 2)

Divide by zero

=FXLDataLookup("SE1:STERLING:ENGLAND/(EG1:EURO:GERMANY-26)","2002","RootGrid",FALSE,TRUE)

Result: Divide By Zero (26−26 = 0) [FXLDataLookupSamples | Excel]

Invalid power

=FXLDataLookup("SE1:STERLING:ENGLAND^(EG1:EURO:GERMANY-26)","2002","RootGrid",FALSE,TRUE)

Result: Invalid Power (exponent expression based on series key is invalid, use a numeric literal or numeric cell)

Parse error (operator typo)

=FXLDataLookup("SE1:STERLING:ENGLAND**SE1:STERLING:ENGLAND-SI1:STERLING:IRELAND/EG1:EURO:GERMANY","2002","RootGrid",FALSE,TRUE)

Result: Parse Error

FXLDataPeriod Examples

The examples below are based on the following root grid sheet.

Root-grid.png

Minimum period (single value)

=SDMXPeriod("RootGrid","MIN")

Result: 2001 Why: Returns the earliest (minimum) SDMX period available on the root grid sheet.

Maximum period (single value)

=SDMXPeriod("RootGrid","MAX")

Result: 2003 Why: Returns the latest (maximum) SDMX period available on the root grid sheet.

Range (min and max — two‑cell spill)

=SDMXPeriod("RootGrid")

Result (two cells): 2001, 2003 Why: Returns both the MIN and MAX SDMX periods from the root grid sheet.

Error: sheet not found

=SDMXPeriod("NonExistantRootGrid",,TRUE)

Result: Sheet Not Found Why: The specified sheet name doesn’t exist, so the function returns a “Sheet Not Found” error.

Troubleshooting

MIN period isn’t the earliest in the grid

Why: SDMXPeriod("…","MIN") returns the first period it finds in the grid header, which is typically (but not guaranteed to be) the earliest.

Fix: Sort your period headers left→right so the earliest period is first.

MAX period isn’t the latest in the grid

Why: SDMXPeriod("…","MAX") returns the last period it finds in the grid header, which is typically (but not guaranteed to be) the latest.

Fix: Sort your period headers left→right so the latest period is last.

FXLDataLookup or SDMXPeriod behaves oddly in “vertical mode”

Why: Both functions expect a horizontal grid (periods across columns) like the RootGrid examples; vertical layouts aren’t supported.

Fix: Render the root grid with periods in the header row and values beneath them.

Grid not found

Why: The grid header (dimensions + period labels) must be on row 2 of the root sheet; otherwise the grid isn’t detected.

Fix: Move headers to row 2 on the root sheet.

Sheet not found

Why: The sheet name you passed doesn’t match an actual worksheet.

Fix: Use the exact sheet name that exists in the workbook.

Key not found

Why: The series key is misspelled or doesn’t exist in the root grid’s Series column. Fix: Copy–paste the key from the grid (avoid typos) and ensure the root grid includes full series keys.

Expected multiple cells, but only one cell returned

Why: Multi‑cell (“spilled”) results require a modern Excel (later than 2016) that supports dynamic arrays.

Invalid power

Why: The right‑hand side of ^ must be a numeric literal or a reference to a numeric cell; a series‑derived expression/key is invalid (see example using …^(EG1:EURO:GERMANY-26)).

Fix: Replace the exponent with a number (e.g., ^2) or a cell that contains a number.

Non‑numeric value in grid

Why: The target cell in the root grid holds text (e.g., g), so numeric calculation fails.

Fix: Correct the source cell to a number or point the formula to a numeric period.

Divide by zero

Why: Your denominator evaluates to 0 (e.g., EG1:EURO:GERMANY - 26 = 0).

Fix: Choose a non‑zero reference.

Parse error

Why: The items/expression string has a syntax error (operator typo, or missing parenthesis).

Fix: Check operators and parentheses; compare against a working example and re‑enter carefully.

Retrieved from "https://wiki.sdmxcloud.org/index.php?title=FXLDataLookup_and_FXLDataPeriod_Functions&oldid=8169"