Skip to content

README.md

Latest commit

 

History

History
138 lines (118 loc) · 6.21 KB

README.md

File metadata and controls

138 lines (118 loc) · 6.21 KB

xllify Standard Library

A collection of pure logic Excel functions in Lua 5.4, designed to complement Excel's built-in functions without duplicating them. xllify Assistant will use these where possible when suggesting solutions.

Design Principles

  1. No Excel Overlap - Functions only exist when they provide value beyond Excel's built-in capabilities
  2. Lua 5.4 Native - Work directly with Lua tables and types
  3. Self-Documenting - Function names clearly describe their purpose

Function overview

Conversion (2 functions)

Function Description
DECIMAL_TO_FRACTION Converts a decimal to a readable fraction string (e.g. 0.75 → 3/4
FRACTION_TO_DECIMAL Parses a fraction string and returns its decimal value

Date (9 functions)

Function Description
ADD_MONTHS Adds N calendar months to a date, snapping to end-of-month when needed
ADD_YEARS Adds N years to a date, handling Feb 29 leap year snapping
DATE_DIFF_LABEL Human-readable time difference label (\
FISCAL_QUARTER Fiscal quarter number given a date and fiscal year start month
IS_BUSINESS_DAY TRUE if a date falls on Monday through Friday
IS_LEAP_YEAR TRUE if a year is a leap year
START_OF_MONTH Returns the serial date of the first day of the month
WEEK_NUMBER_ISO Returns the ISO 8601 week number for a date
WORKDAYS_BETWEEN Count of Mon–Fri working days between two dates (exclusive)

Financial (7 functions)

Function Description
ANNUALISE_RETURN Converts a total return over N days to an annualised rate
BREAK_EVEN_UNITS Units needed to break even given fixed costs, price, and variable cost
COMPOUND_INTEREST Total value after compound interest: A = P(1 + r/n)^(nt)
DISCOUNT_FACTOR Present value discount factor: 1 / (1 + rate)^periods
DRAWDOWN Per-period drawdown from peak for a range of portfolio values
RULE_OF_72 Approximate years to double an investment at a given annual rate
SHARPE_RATIO Sharpe ratio given a returns range and risk-free rate

Logic (4 functions)

Function Description
ALL_OF TRUE if all values in a range satisfy a condition string
ANY_OF TRUE if any value in a range satisfies a condition string
DEFAULT_IF_ERROR Returns a default when input is error, blank, empty string, or zero
SWITCH_MAP Maps a value to an output using a paired key/value range

Lookup (4 functions)

Function Description
CLOSEST Returns the value in a range numerically closest to a target
COLLECT_UNIQUE Returns unique matching values as a spilled array
FUZZY Finds the closest matching string using fuzzy text matching
NTH_MATCH Returns the Nth matching value from a range

Math (11 functions)

Function Description
CLAMP Constrains a value between a minimum and maximum
COUNT_IF_BETWEEN Counts values in a range that fall between min and max
GEOMETRIC_MEAN Calculates the geometric mean of a range of positive numbers
HARMONIC_MEAN Calculates the harmonic mean of a range of positive numbers
MODE_RANGE Most frequently occurring value in a range, lowest on ties
NORMALIZE Calculates the z-score of a value given a mean and standard deviation
PERCENTILE_RANK Returns what percentile (0–100) a value sits at within a range
ROUND_TO_MULTIPLE Rounds a value to the nearest multiple
RUNNING_TOTAL Cumulative sum of a range, returned as a spilled column
STDDEV_RANGE Population standard deviation of a range
SUM_IF_BETWEEN Sums values where the criteria range falls between min and max

Text (8 functions)

Function Description
EXTRACT_DOMAIN Extracts the domain from an email address or URL
FIRST_NAME Extracts the first word from a full name string
INITIALS Extracts initials from a full name
LAST_NAME Extracts the last word from a full name string
REMOVE_ACCENTS Strips diacritics from accented characters
STRIP_NON_ALPHA Removes everything except letters and spaces from a string
STRIP_NON_NUMERIC Removes everything except digits, dot, and minus sign
WRAP_TEXT Inserts line breaks every N characters at word boundaries

Validation (4 functions)

Function Description
IS_BLANK_OR_ZERO TRUE if a value is blank, empty string, or zero
IS_CREDIT_CARD Validates a credit card number using the Luhn algorithm
IS_DATE_VALID TRUE if a value is a plausible Excel date serial (1900–2200)
IS_JSON TRUE if a string is valid JSON

Scripts

# Concatenate lib/<category>/*.lua into functions/<category>.lua
./scripts/build_functions.sh|ps1

# Generate index.json and update README tables from source
./scripts/generate_docs.sh|ps1

Function File Format

Each .lua file contains a single function with metadata for MCP indexing. The dense tagging and descriptions are intentional: stdlib functions need rich metadata so the code model can retrieve the right function from a large index. For functions you write yourself, this level of annotation isn't necessary.

--[[
@function CLAMP
@description Constrains a value between a minimum and maximum
@category Math
@tags clamp, limit, range, bound, constrain
@queries clamp value to range, constrain number between min max, limit value, bound number
@param value number The value to constrain
@param min_val number The minimum allowed value
@param max_val number The maximum allowed value
@returns number The clamped value
@examples
  CLAMP(15, 0, 10) → 10
  CLAMP(-5, 0, 10) → 0
  CLAMP(5, 0, 10) → 5
@see BETWEEN, NORMALIZE
]]

--- Constrains a value between a minimum and maximum
-- @param value number The value to constrain
-- @param min_val number Minimum allowed value
-- @param max_val number Maximum allowed value
-- @category Math
-- @test(15, 0, 10) == 10
-- @test(-5, 0, 10) == 0
-- @test(5, 0, 10) == 5
function CLAMP(value, min_val, max_val)
    return math.max(min_val, math.min(max_val, value))
end