xlMATRIX

xlMATRIX_Gists.lambda

/* xlMATRIX v. 1.1 */
/* Author: Eduardo G. C. Amaral */
/* Source: https://github.com/edugca/xlMATRIX */

/*
Name: ADDCONST
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Add a column of ones to a matrix

Parameters:
matrix	matrix to be horizontally concatenated with a column vector of ones.
[position]	position where the column vector of ones should be inserted; if omitted equals 1.
[header]	if not omitted, adds this argument to the first row of the ones column.

*/
ADDCONST=LAMBDA(matrix,[position],[header], LET( position, IF( ISOMITTED(position), 1, position), header, IF( OR( ISOMITTED(header), ISBLANK(header)), FALSE, header), values, IF( OR( position<1, position>COLUMNS(matrix)+1), "#position exceeds matrix columns", IF(position=1, HSTACK( ONES(ROWS(matrix),1), SLICE(matrix,0,0,1,-1) ), IF(position=COLUMNS(matrix)+1, HSTACK(matrix, ONES(ROWS(matrix),1) ), HSTACK( SLICE(matrix,0,0,1,position-1), ONES(ROWS(matrix),1), SLICE(matrix,0,0,position,-1) ) ) ) ), IF(header=FALSE, values, SLICE(values,1,1,position,position,header) ) ) );

/*
Name: ADDTREND
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Add a column with a deterministic trend to a matrix

Parameters:
matrix	matrix to be horizontally concatenated with a column vector with a deterministic trend.
[position]	position where the column vector with the deterministic trend should be inserted; if omitted equals 1.
[nPower]	power of the deterministic trend; if omitted equals 1.
[header]	if not omitted, adds this argument to the first row of the trend column.

*/
ADDTREND=LAMBDA(matrix,[position],[nPower],[header], LET( position, IF( OR( ISOMITTED(position), ISBLANK(position)), 1, position), nPower, IF( OR( ISOMITTED(nPower), ISBLANK(nPower)), 1, nPower), header, IF( OR( ISOMITTED(header), ISBLANK(header)), FALSE, header), values, IF( OR( position<1, position>COLUMNS(matrix)+1), "#position exceeds matrix columns", IF(position=1, HSTACK( SEQUENCE(ROWS(matrix),1,IF(header=FALSE,1,0))^nPower, SLICE(matrix,0,0,1,-1) ), IF(position=COLUMNS(matrix)+1, HSTACK(matrix, SEQUENCE(ROWS(matrix),1,IF(header=FALSE,1,0))^nPower ), HSTACK( SLICE(matrix,0,0,1,position-1), SEQUENCE(ROWS(matrix),1,IF(header=FALSE,1,0))^nPower, SLICE(matrix,0,0,position,-1) ) ) ) ), IF(header=FALSE, values, SLICE(values,1,1,position,position,header) ) ) );

/*
Name: AGGREGBYDATE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Aggregate a time series to a lower time frequency

Parameters:
dates	a vector of dates
values	a vector of values
highFreq	the frequency of the dates vector
lowFreq	the frequency to which to aggregate the series
[method]	one of the aggregation types allowed: "avg" (default), "sum" or "eop" (end of period)
[onlyValues]	a boolean, default is FALSE (return dates and values)

*/
AGGREGBYDATE=LAMBDA(dates,values,highFreq,lowFreq,[method],[onlyValues],IF(highFreq < lowFreq, "# Cannot disaggregate to lower frequencies", LET(method, IF(ISOMITTED(method), "avg", method), onlyValues, IF(ISOMITTED(onlyValues), FALSE, onlyValues), step1, MAP(dates, LAMBDA(x, FLOOR(DAYS360(DATE(YEAR(x), 1, 1), x), 30))), step2, MAP(step1, LAMBDA(x, MOD(x, 360 / lowFreq))), step3, SCAN(0, step2, LAMBDA(accum,x, IF(x > accum, accum, accum + 1))), step4_0, DATE(YEAR(dates), (12 / lowFreq) * ROUNDUP(MONTH(dates) / (12 / lowFreq), 0) - (12 / lowFreq) + 1, 1), completeDates, SCAN(0, step4_0, LAMBDA(accum,x, SUM(1 * (step4_0 = x)) = highFreq / lowFreq)), step4_dates, FILTER(step4_0, completeDates), step4_values, FILTER(values, completeDates), step5, SWITCH(method, "avg", MAKEARRAY(ROWS(step4_dates), 1, LAMBDA(row,col, SUM(IF(step4_dates = INDEX(step4_dates, row), step4_values, 0)) / (highFreq / lowFreq))), "sum", MAKEARRAY(ROWS(step4_dates), 1, LAMBDA(row,col, SUM(IF(step4_dates = INDEX(step4_dates, row), step4_values, 0)))), "eop", MAKEARRAY(ROWS(step4_dates), 1, LAMBDA(row,col, TAKE(FILTER(step4_values, step4_dates = INDEX(step4_dates, row)), -1))), ), step6, UNIQUE(IF(onlyValues, step5, HSTACK(step4_dates, step5))), step6)));

/*
Name: ALL
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Determine if all array elements are nonzero or true

Parameters:
matrix	matrix to be tested
[dim]	tests whether all elements along dimension dim are nonzero or logical 1 (true); default is "all" (all dimensions)

*/
ALL=LAMBDA(matrix,[dim], LET( dim, IF( ISOMITTED(dim),"all", dim), IF(dim="all", IF( ( SUM(1*(matrix=FALSE)) + SUM(1*(matrix=0))  ) = 0, TRUE, FALSE ), IF(dim=1, BYCOL(matrix, LAMBDA(col, IF( ( SUM(1*(col=FALSE)) + SUM(1*(col=0))  ) = 0, TRUE, FALSE ) )), IF(dim=2, BYROW(matrix, LAMBDA(row, IF( ( SUM(1*(row=FALSE)) + SUM(1*(row=0))  ) = 0, TRUE, FALSE ) )), #VALUE!) ) ) ));

/*
Name: ANOVA
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Analysis of variance (1 factor or one-way)

Parameters:
groupNames	Vector of group names
groupVectors	Matrix in which each column is a group vector
[significance]	Significance degree for the F Test. If omitted, equals 5%

*/
ANOVA=LAMBDA(groupNames,groupVectors,[significance], LET( significance, IF(ISOMITTED(significance), 0.05, significance), nGroups,LENGTH(groupNames), nObs, BYCOL(groupVectors,LAMBDA(col, COUNTA(col))), groupSum, BYCOL(groupVectors,LAMBDA(col, SUM(col))), groupAverage, BYCOL(groupVectors,LAMBDA(col, AVERAGE(col))), groupSumSqr, BYCOL(groupVectors,LAMBDA(col, DEVSQ(col))), groupVar, BYCOL(groupVectors,LAMBDA(col, VAR.S(col))), SSbetween, SUM( ( groupAverage-AVERAGE(groupAverage) )^2 * nObs ), SSwithin, SUM( groupSumSqr ), SStotal, SSbetween + SSwithin, DFbetween, nGroups - 1, DFwithin, SUM(nObs) - DFbetween - 1, DFtotal, DFbetween + DFwithin, F, ( SSbetween/DFbetween ) / (SSwithin / DFwithin ), pValue, F.DIST.RT( F, DFbetween, DFwithin ), Fcrit, F.INV.RT( significance, DFbetween, DFwithin ), lineANOVA1, {"SUMMARY","","","","","",""}, lineANOVA1lab, {"Groups","Count","Sum","Average","Variance","",""}, lineANOVA1res, HSTACK( VEC(groupNames), VEC(nObs), VEC(groupSum), VEC(groupAverage), VEC(groupVar), REPMAT("",nGroups,2) ), lineANOVA2, {"","","","","","","";"ANOVA","","","","","","";"Source of variation","SS","df","MS","F","P-value","F-crit"}, lineANOVA2SS, VSTACK( SSbetween, SSwithin, "", SStotal), lineANOVA2DF, VSTACK( DFbetween, DFwithin, "", DFtotal), lineANOVA2MS, VSTACK( SSbetween / DFbetween, SSwithin / DFwithin, "", "" ), lineANOVA2F, VSTACK( F, "", "", "" ), lineANOVA2p, VSTACK( pValue, "", "", "" ), lineANOVA2Fcrit, VSTACK( Fcrit, "", "", "" ), VSTACK( lineANOVA1, lineANOVA1lab, lineANOVA1res, lineANOVA2, HSTACK( {"Between groups";"Within groups";"";"Total"}, lineANOVA2SS, lineANOVA2DF, lineANOVA2MS, lineANOVA2F, lineANOVA2p, lineANOVA2Fcrit ) )));

/*
Name: ANY
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Determine if any array elements are nonzero

Parameters:
matrix	matrix to be tested
[dim]	tests whether any element along dimension dim is nonzero or logical 1 (true); default is "all" (all dimensions)

*/
ANY=LAMBDA(matrix,[dim], LET( dim, IF( ISOMITTED(dim),"all", dim), IF(dim="all", IF( ( SUM(1*(matrix=FALSE)) + SUM(1*(matrix=0))  ) <> ROWS(matrix)*COLUMNS(matrix), TRUE, FALSE ), IF(dim=1, BYCOL(matrix, LAMBDA(col, IF( ( SUM(1*(col=FALSE)) + SUM(1*(col=0))  ) <> ROWS(col), TRUE, FALSE ) )), IF(dim=2, BYROW(matrix, LAMBDA(row, IF( ( SUM(1*(row=FALSE)) + SUM(1*(row=0))  ) <> COLUMNS(row), TRUE, FALSE ) )), #VALUE!) ) ) ));

/*
Name: APPLYCOLS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Apply lambda function to each column

Parameters:
array	array to whose columns will be applied the lambda function func.
func	lambda function.
paramArrayPos	position of the array parameter inside of the lambda function func.
[param1]	argument of the 1st parameter of the lambda function, excluding the array parameter position.
[param2]	argument of the 2nd parameter of the lambda function, excluding the array parameter position.
[param3]	argument of the 3rd parameter of the lambda function, excluding the array parameter position.

*/
APPLYCOLS=LAMBDA(array,func,paramArrayPos,[param1],[param2],[param3], LET( nParams, NOT(ISOMITTED(param1)) + NOT(ISOMITTED(param2)) + NOT(ISOMITTED(param3)), nCols, COLUMNS(array), colVals, INDEX(array,0,1), fcol, SWITCH( nParams, 0, func(colVals), 1,  SWITCH( paramArrayPos, 1, func(colVals, param1), 2, func(param1, colVals)), 2,  SWITCH( paramArrayPos, 1, func(colVals, param1, param2), 2, func(param1, colVals, param2)), 3,  SWITCH( paramArrayPos, 1, func(colVals, param1, param2, param3), 2, func(param1, colVals, param2, param3), 3, func(param1, param2, colVals)) ), IF(nCols = 1, fcol, HSTACK( fcol, APPLYCOLS(DROP(array,0,1), func, paramArrayPos, param1, param2, param3))) ));

/*
Name: APPLYROWS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Apply lambda function to each row

Parameters:
array	array to whose rows will be applied the lambda function func.
func	lambda function.
[paramArrayPos]	position of the array parameter inside of the lambda function func.
[param1]	argument of the 1st parameter of the lambda function, excluding the array parameter position.
[param2]	argument of the 2nd parameter of the lambda function, excluding the array parameter position.
[param3]	argument of the 3rd parameter of the lambda function, excluding the array parameter position.

*/
APPLYROWS=LAMBDA(array,func,paramArrayPos,[param1],[param2],[param3], LET( nParams, NOT(ISOMITTED(param1)) + NOT(ISOMITTED(param2)) + NOT(ISOMITTED(param3)), nLins, ROWS(array), linVals, INDEX(array,1,0), flin, SWITCH( nParams, 0, func(linVals), 1,  SWITCH( paramArrayPos, 1, func(linVals, param1), 2, func(param1, linVals)), 2,  SWITCH( paramArrayPos, 1, func(linVals, param1, param2), 2, func(param1, linVals, param2)), 3,  SWITCH( paramArrayPos, 1, func(linVals, param1, param2, param3), 2, func(param1, linVals, param2, param3), 3, func(param1, param2, linVals)) ), IF(nLins = 1, flin, VSTACK( flin, APPLYROWS(DROP(array,1,0), func, paramArrayPos, param1, param2, param3))) ));

/*
Name: AR_COEFF
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
AR_COEFF=LAMBDA(y,[nLags],[nLeads],[includeConstant],LET( y, VEC(y), N, ROWS(y), nLags, IF(OR(ISOMITTED(nLags), ISBLANK(nLags)), 1, nLags), nLeads, IF(OR(ISOMITTED(nLeads), ISBLANK(nLeads)), 0, nLeads), includeConstant, IF(OR(ISOMITTED(includeConstant), ISBLANK(includeConstant)), TRUE, includeConstant), XLags, IF(nLags=0, FALSE, LAG(y,nLags,TRUE,TRUE,FALSE,FALSE,TRUE)), XLeads, IF(nLeads=0, FALSE, LEAD(y,nLeads,TRUE,TRUE,FALSE,FALSE,TRUE)), x, IF(nLeads=0, XLags, IF(nLags=0, XLeads, HSTACK(XLags,XLeads))), Xconst, IF(includeConstant, ADDCONST(x), x), data, RMMISSING(HSTACK(y,Xconst),1), OLS_COEFF(TAKE(data,,1),DROP(data,,1)) ));

/*
Name: AR_FORECAST
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
AR_FORECAST=LAMBDA(y,[nPeriods],[nLags],[includeConstant],LET( y, VEC(y), nPeriods, IF(OR(ISOMITTED(nPeriods), ISBLANK(nPeriods)), 1, nPeriods), nLags, IF(OR(ISOMITTED(nLags), ISBLANK(nLags)), 1, nLags), includeConstant, IF(OR(ISOMITTED(includeConstant), ISBLANK(includeConstant)), TRUE, includeConstant), arCoeff, AR_COEFF(y,nLags,0,includeConstant), yLast, TAKE(y,-nLags), yForecast, REDUCE(yLast, SEQUENCE(nPeriods), LAMBDA(accum,period, VSTACK(accum, LET( newX, TRANSPOSE(TAKE(accum,-nLags)), OLS_FITTED(1, IF(includeConstant, ADDCONST(newX), newX), arCoeff) )) )), DROP(yForecast,nLags) ));

/*
Name: BKFILTER
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Baxter-King filter for trend and cyclical components

Parameters:
values	Vector of values.
[trendCycle]	If "t" or omitted returns the trend. If "c" returns the cycle.
[lowerCutoff]	Lower cutoff period for the cyclical component, in units of data periodicity, specified as a numeric scalar. Usually one should use: annual data, 2; quarterly data, 6; monthly data, 18. Default is 6.
[upperCutoff]	Upper cutoff period for the cyclical component, in units of data periodicity, specified as a numeric scalar. Usually one should use: annual data, 8; quarterly data, 32; monthly data, 96. Default is 32.
[lagLength]	Number of lags of the symmetric moving average, specified as a positive integer less than (numObs-1)/2 or vector of such positive integers. Usually one should use: annual data, 3; quarterly data, 12; monthly data, 36. Default is 12.
[drift]	Boolean. If TRUE, time series is random walk with possibly a nonzero drift or time series is stationary about a linear time trend. If FALSE, there is no drift or time trend in time series. Default is TRUE.

*/
BKFILTER=LAMBDA(values,[trendCycle],[lowerCutoff],[upperCutoff],[lagLength],[drift],LET(y, VEC(values), t, ROWS(y), trendCycle, IF(OR(ISOMITTED(trendCycle), ISBLANK(trendCycle)), "t", LOWER(trendCycle)), lowerCutoff, IF(OR(ISOMITTED(lowerCutoff), ISBLANK(lowerCutoff)), 6, lowerCutoff), upperCutoff, IF(OR(ISOMITTED(upperCutoff), ISBLANK(upperCutoff)), 32, upperCutoff), lagLength, IF(OR(ISOMITTED(lagLength), ISBLANK(lagLength)), 12, lagLength), drift, IF(OR(ISOMITTED(drift), ISBLANK(drift)), TRUE, drift), f_undrift, LAMBDA(x, x - SEQUENCE(t, 1, 0) * (INDEX(x, t) - INDEX(x, 1)) / (t - 1)), yDrift, IF(drift, f_undrift(y), y), nfix, lagLength, a, 2 * PI() / upperCutoff, B, 2 * PI() / lowerCutoff, j, SEQUENCE(2 * t), BB, VSTACK((B - a) / PI(), (SIN(j * B) - SIN(j * a)) / (j * PI())), AA, ZEROS(t, t), g, 1, cc, INDEX(g, 1), R_0, INDEX(BB, 1) * INDEX(cc, 1), r, SCAN(0, SEQUENCE(t), LAMBDA(accum,j, IF(j = 1, PI() * INDEX(BB, 1) * INDEX(cc, 1), accum - 2 * PI() * INDEX(BB, j - 1) * INDEX(cc, 1)))), bb_0, ZEROS(2 * nfix + 1, 1), bb_1, SLICE(bb_0, nfix + 1, 2 * nfix + 1, 1, 1, INDEX(BB, SEQUENCE(nfix + 1))), bb_2, SLICE(bb_1, 1, nfix, 1, 1, FLIPUD(INDEX(BB, SEQUENCE(nfix, 1, 2)))), bb_3t, TRANSPOSE(bb_2 - SUM(bb_2) / (2 * nfix + 1)), origin, EXPAND(REPMAT(bb_3t, t - 2 * nfix, 1), t - 2 * nfix, t, 0), rowMatrix, REPMAT(SEQUENCE(ROWS(origin)), 1, t), colMatrix, MAKEARRAY(ROWS(origin), COLUMNS(origin), LAMBDA(row,col, IF(row = col, 1, IF(row > col, t, col - row + 1)))), AA_1, VSTACK(ZEROS(nfix, t), INDEX(origin, rowMatrix, colMatrix), ZEROS(nfix, t)), cycle, VSTACK(NA() * SEQUENCE(nfix), CHOOSEROWS(MMULT(AA_1, yDrift), SEQUENCE(t - 2 * nfix, 1, nfix + 1)), NA() * SEQUENCE(nfix)), final, IF(trendCycle = "t", y - cycle, IF(trendCycle = "c", cycle, "#trendCycle must be either ""t"" or ""c""")), final));

/*
Name: BLKDIAG
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Create a block diagonal matrix from existing matrices

Parameters:
matrixA	Matrix on the upper-left of the diagonal
matrixB	Matrix on the lower-right of the diagonal

*/
BLKDIAG=LAMBDA(matrixA,matrixB,  LET(  nn,ROWS(matrixA) + ROWS(matrixB),  mm,COLUMNS(matrixA) + COLUMNS(matrixB),  nseq, IF(MOD(SEQUENCE(nn,1),ROWS(matrixA))=0, ROWS(matrixA), MOD(SEQUENCE(nn,1),ROWS(matrixA))),  mseq, IF(MOD(SEQUENCE(1,mm),COLUMNS(matrixA))=0, COLUMNS(matrixA), MOD(SEQUENCE(1,mm),COLUMNS(matrixA))),  MAKEARRAY(nn,mm,LAMBDA(row,col,  IF( OR( AND(row <= ROWS(matrixA), col > COLUMNS(matrixA) ), AND(row > ROWS(matrixA), col <= COLUMNS(matrixA) )  ), 0,  IF( row <= ROWS(matrixA), INDEX(matrixA,INDEX(nseq,row),INDEX(mseq,1,col)), INDEX(matrixB,INDEX(nseq,row),INDEX(mseq,1,col)) )  ) ))  ));

/*
Name: BOOTSTRP
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Bootstrap sampling

Parameters:
nboot	Draws nboot bootstrap data samples from data with replacement
bootfun	Computes statistics on each sample using bootfun
data	Data which will be sampled

*/
BOOTSTRP=LAMBDA(nboot,bootfun,data, SCAN(0,SEQUENCE(nboot),LAMBDA(accum,x, bootfun( SAMPLE(data,ROWS(data),1,FALSE) ) )) );

/*
Name: CFFILTER
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Christiano-Fitzgerald filter for trend and cyclical components

Parameters:
values	Vector of values.
[trendCycle]	If "t" or omitted returns the trend. If "c" returns the cycle.
[lowerCutoff]	Lower cutoff period for the cyclical component, in units of data periodicity, specified as a numeric scalar. Usually one should use: annual data, 2; quarterly data, 6; monthly data, 18. Default is 6.
[upperCutoff]	Upper cutoff period for the cyclical component, in units of data periodicity, specified as a numeric scalar. Usually one should use: annual data, 8; quarterly data, 32; monthly data, 96. Default is 32.
[lagLength]	Number of lags of the symmetric moving average, specified as a positive integer less than (numObs-1)/2 or vector of such positive integers. Usually one should use: annual data, 3; quarterly data, 12; monthly data, 36. Default is 12. Only used when [filterType] is "fixed".
[filterType]	Finite-sample approximation of ideal bandpass filter. It can be "asymmetric", "symmetric" or "fixed" (fixed-length). Default is "asymmetric".
[root]	Boolean. If TRUE, there is unit root in the time series. If FALSE, there is no unit root in the time series. Default is TRUE.
[drift]	Boolean. If TRUE, time series is random walk with possibly a nonzero drift or time series is stationary about a linear time trend. If FALSE, there is no drift or time trend in time series. Default is TRUE.

*/
CFFILTER=LAMBDA(values,[trendCycle],[lowerCutoff],[upperCutoff],[lagLength],[root],[drift],[filterType],LET(y, VEC(values), t, ROWS(y), trendCycle, IF(OR(ISOMITTED(trendCycle), ISBLANK(trendCycle)), "t", LOWER(trendCycle)), lowerCutoff, IF(OR(ISOMITTED(lowerCutoff), ISBLANK(lowerCutoff)), 6, lowerCutoff), upperCutoff, IF(OR(ISOMITTED(upperCutoff), ISBLANK(upperCutoff)), 32, upperCutoff), lagLength, IF(OR(ISOMITTED(lagLength), ISBLANK(lagLength)), 12, lagLength), root, IF(OR(ISOMITTED(root), ISBLANK(root)), TRUE, root), drift, IF(OR(ISOMITTED(drift), ISBLANK(drift)), TRUE, drift), filterType, IF(OR(ISOMITTED(filterType), ISBLANK(filterType)), "asymmetric", filterType), f_undrift, LAMBDA(x, x - SEQUENCE(t, 1, 0) * (INDEX(x, t) - INDEX(x, 1)) / (t - 1)), yDrift, IF(drift, f_undrift(y), y), nfix, lagLength, a, 2 * PI() / upperCutoff, B, 2 * PI() / lowerCutoff, j, SEQUENCE(2 * t), BB, VSTACK((B - a) / PI(), (SIN(j * B) - SIN(j * a)) / (j * PI())), zeroLine, ZEROS(1, t), g, 1, cc, INDEX(g, 1), R_0, INDEX(BB, 1) * INDEX(cc, 1), r, SCAN(0, SEQUENCE(t), LAMBDA(accum,j, IF(j = 1, PI() * INDEX(BB, 1) * INDEX(cc, 1), accum - 2 * PI() * INDEX(BB, j - 1) * INDEX(cc, 1)))), filtAsymmCycle, IF(filterType <> "asymmetric", NA(), LET(AA_1, REDUCE(0, SEQUENCE(t), LAMBDA(accum,i, LET(beforeRoot, IF(i = 1, TRANSPOSE(INDEX(BB, SEQUENCE(t - i + 1), 0)), HSTACK(ZEROS(1, i - 1), TRANSPOSE(INDEX(BB, SEQUENCE(t - i + 1), 0)))), afterRoot, IF(root = FALSE, beforeRoot, SLICE(beforeRoot, i, i, t, t, INDEX(r, t + 1 - i) / (2 * PI()))), IF(i = 1, afterRoot, VSTACK(accum, afterRoot))))), AA_2, SLICE(AA_1, 1, 1, 1, 1, INDEX(AA_1, t, t)), AA_3, AA_2 + FLIPUD(FLIPLR(TRIU(AA_2, 1))), cycle, CHOOSEROWS(MMULT(AA_3, yDrift), SEQUENCE(t, 1, 1)), cycle)), filtSymmCycle, IF(filterType <> "symmetric", NA(), LET(AA_1, REDUCE(zeroLine, SEQUENCE(ROUNDUP(t / 2, 0) - 2 + 1, 1, 2), LAMBDA(accum,i, LET(AAA_1, SLICE(zeroLine, 1, 1, i, i + i - 1, TRANSPOSE(INDEX(BB, SEQUENCE(i), 0))), AAA_2, IF(root = FALSE, AAA_1, SLICE(AAA_1, 1, 1, i + i - 1, i + i - 1, INDEX(r, i - 1 + 1) / (2 * PI()))), AAA_3, SLICE(AAA_2, 1, 1, 1, i - 1, FLIPLR(INDEX(AAA_2, 1, SEQUENCE(1, i + i - 1 - i - 1 + 1, i + 1)))), VSTACK(accum, AAA_3)))), AA_2, VSTACK(AA_1, ZEROS(t - (ROUNDUP(t / 2, 0) - 2 + 1) + 1, t)), AA_3, SLICE(AA_2, ROUNDUP(t / 2, 0) + 1, t, 0, 0, FLIPUD(FLIPLR(SLICE(AA_2, 1, ROUNDDOWN(t / 2, 0), 0, 0)))), cycle, MMULT(AA_3, yDrift), cycle)), filtFixedCycle, IF(filterType <> "fixed", NA(), LET(BBB_0, ZEROS(2 * nfix + 1, 1), BBB_1, SLICE(BBB_0, nfix + 1, 2 * nfix + 1, 0, 0, INDEX(BB, SEQUENCE(nfix + 1))), BBB_2, SLICE(BBB_1, 1, nfix, 0, 0, FLIPUD(INDEX(BB, SEQUENCE(nfix + 1 - 1, 1, 2)))), BBB_3, IF(root = FALSE, BBB_2, SLICE(BBB_2, 2 * nfix + 1, 2 * nfix + 1, 0, 0, INDEX(r, nfix + 1) / (2 * PI()))), BBB_4, IF(root = FALSE, BBB_3, SLICE(BBB_3, 1, 1, 0, 0, INDEX(r, nfix + 1) / (2 * PI()))), AA_1, REDUCE(ZEROS(nfix, t), SEQUENCE(t - nfix - nfix - 1 + 1, 1, nfix + 1), LAMBDA(accum,i, LET(AAA_1, SLICE(zeroLine, 1, 1, i - nfix, i + nfix, TRANSPOSE(BBB_4)), VSTACK(accum, AAA_1)))), AAdrift, IF(drift = FALSE, AA_1, ), cycle, VSTACK(NA() * SEQUENCE(nfix), CHOOSEROWS(MMULT(AA_1, yDrift), SEQUENCE(t - 2 * nfix, 1, nfix + 1)), NA() * SEQUENCE(nfix)), cycle)), cycle, SWITCH(filterType, "asymmetric", filtAsymmCycle, "symmetric", filtSymmCycle, "fixed", filtFixedCycle), final, IF(trendCycle = "t", y - cycle, IF(trendCycle = "c", cycle, "#trendCycle must be either ""t"" or ""c""")), final));

/*
Name: CHOWTEST
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Chow test for structural change

Parameters:
y	dependent variable.
X	independent variables.
indexBreak	index of the observation which should be tested for a structural break; if empty or 0 and [return] is 4, iterates all break indexes possible.
[return]	1 (default) returns the decision; 2 returns the test statistic; 3 returns the p-value of the test statistic; 4 returns a row-vector with {Break Index, N Pre, N After, RSS Pre, RSS After, F-stat, p-value} or, if indexBreak = 0 or empty, iterates all break indexes possible.
[significance]	sets the significance of the test. Default is 5%.

*/
CHOWTEST=LAMBDA(y,x,indexBreak,[return],[significance], LET( indexBreak, IF(OR(ISOMITTED(indexBreak), ISBLANK(indexBreak)), 0, indexBreak), return, IF(OR(ISOMITTED(return), ISBLANK(return)), 1, return), significance, IF(OR(ISOMITTED(significance), ISBLANK(significance)), 5%, significance), k, COLUMNS(x), N, LENGTH(y), beforeN, indexBreak-1, afterN, (LENGTH(y)-indexBreak+1), combinedRSS, OLS_RSS(y,x), beforeRSS, OLS_RSS(TAKE(y,beforeN),TAKE(x,beforeN)), afterRSS, OLS_RSS(TAKE(y,-afterN),TAKE(x,-afterN)), testStat, ((combinedRSS - (beforeRSS + afterRSS))/k) / ((beforeRSS+afterRSS)/(beforeN + afterN - 2*k)), pValueStat, F.DIST.RT(testStat, k, beforeN + afterN - 2*k), decision, pValueStat <= significance, summary, HSTACK(indexBreak, beforeN, afterN, beforeRSS, afterRSS, testStat, pValueStat), final, CHOOSE(return, decision, testStat, pValueStat, IF(indexBreak<>0, summary, LET(headers, HSTACK("Break Index","N Pre", "N Post", "RSS Pre", "RSS Post", "F-stat", "p-value"), VSTACK(headers, REDUCE(SLICE(EXPAND({""},N,7,""),,,1,1,SEQUENCE(N,1)),SEQUENCE(N,1), LAMBDA(accum,idx, IF(OR(idx <= k, idx > N-k), accum, SLICE(accum,idx,idx,,, CHOWTEST(y,x,idx,return,significance) )))) )))), final ));

/*
Name: CIRCSHIFT
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Shift array circularly

Parameters:
A	matrix that will be shifted.
K	positions to be shifted.
[dim]	dimension that will be shifted. If omitted, it is the first dimension of A whose size does not equal 1, that is, if number of rows is not equal to 1, then it is 1.

*/
CIRCSHIFT=LAMBDA(a,k,[dim], LET( N, ROWS(a), m, COLUMNS(a), dim, IF(OR(ISOMITTED(dim), ISBLANK(dim)), IF(N>1, 1, 2), dim), t, IF(dim=1, N, m), shift, IF(dim=1, 1+MOD(INT(k)-1, N), 1+MOD(INT(k)-1, m)), indices, VSTACK(SEQUENCE(shift, 1, t-shift+1), SEQUENCE(t-shift,1)), final, IF(t=shift, a, IF(dim=1, CHOOSEROWS(a, indices), CHOOSECOLS(a, indices)) ), final ));

/*
Name: COMPLEMENT
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Return the elements in a continuous range of integers that do not belong to a matrix

Parameters:
matrix	matrix used for comparison
[minInteger]	the largest integer of the range. If omitted, it is the smallest value of the matrix
[maxInteger]	the largest integer of the range. If omitted, it is the largest value of the matrix

*/
COMPLEMENT=LAMBDA(matrix,[minInteger],[maxInteger], LET( minInteger, IF( ISOMITTED(minInteger), MIN(matrix), minInteger ), maxInteger, IF( ISOMITTED(maxInteger), MAX(matrix), maxInteger ), IFERROR( UNIQUE(VSTACK(VEC(matrix), SEQUENCE(maxInteger-minInteger+1,1,minInteger)),FALSE,TRUE), NA()) ));

/*
Name: CONTAGION_COKURTOSIS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Test for contagion using the Cokurtosis test.

Parameters:
sampleNonCrisis1	First sample of returns during non-crisis period
sampleNonCrisis2	Second sample of returns during non-crisis period
sampleCrisis1	First sample of returns during crisis period
sampleCrisis2	Second sample of returns during crisis period
returnPValue	Boolean. TRUE or omitted, returns the p-value of the test. FALSE, returns its F-statistic.
returnInverse	Boolean. FALSE or omitted, runs the test from sample 1 to sample 2. TRUE, returns the test from sample 2 to sample 1.

*/
CONTAGION_COKURTOSIS=LAMBDA(sampleNonCrisis_1,sampleNonCrisis_2,sampleCrisis_1,sampleCrisis_2,[returnPValue],[returnInverse],LET(volNonCrisis_1, STDEV.S(sampleNonCrisis_1), volCrisis_1, STDEV.S(sampleCrisis_1), meanNonCrisis_1, AVERAGE(sampleNonCrisis_1), meanCrisis_1, AVERAGE(sampleCrisis_1), nNonCrisis_1, COUNTA(sampleNonCrisis_1), nCrisis_1, COUNTA(sampleCrisis_1), volNonCrisis_2, STDEV.S(sampleNonCrisis_2), volCrisis_2, STDEV.S(sampleCrisis_2), meanNonCrisis_2, AVERAGE(sampleNonCrisis_2), meanCrisis_2, AVERAGE(sampleCrisis_2), nNonCrisis_2, COUNTA(sampleNonCrisis_2), nCrisis_2, COUNTA(sampleCrisis_2), corrNonCrisis, CORREL(sampleNonCrisis_1, sampleNonCrisis_2), corrCrisis, CORREL(sampleCrisis_1, sampleCrisis_2), corrCrisisAdj, corrCrisis / SQRT(1 + (volCrisis_1 ^ 2 - volNonCrisis_1 ^ 2) / (volNonCrisis_1 ^ 2) * (1 - corrCrisis ^ 2)), coKurNonCrisis_1_3, AVERAGE((sampleNonCrisis_1 - meanNonCrisis_1) / volNonCrisis_1 * ((sampleNonCrisis_2 - meanNonCrisis_2) / volNonCrisis_2) ^ 3) - (3 * corrNonCrisis), coKurCrisis_1_3, AVERAGE((sampleCrisis_1 - meanCrisis_1) / volCrisis_1 * ((sampleCrisis_2 - meanCrisis_2) / volCrisis_2) ^ 3) - (3 * corrCrisisAdj), coKurNonCrisis_3_1, AVERAGE(((sampleNonCrisis_1 - meanNonCrisis_1) / volNonCrisis_1) ^ 3 * ((sampleNonCrisis_2 - meanNonCrisis_2) / volNonCrisis_2)) - (3 * corrNonCrisis), coKurCrisis_3_1, AVERAGE(((sampleCrisis_1 - meanCrisis_1) / volCrisis_1) ^ 3 * ((sampleCrisis_2 - meanCrisis_2) / volCrisis_2)) - (3 * corrCrisisAdj), chiSquared_1_3, ((coKurCrisis_1_3 - coKurNonCrisis_1_3) / SQRT((18 * corrCrisisAdj ^ 2 + 6) / nCrisis_1 + (18 * corrNonCrisis ^ 2 + 6) / nNonCrisis_1)) ^ 2, chiSquared_3_1, ((coKurCrisis_3_1 - coKurNonCrisis_3_1) / SQRT((18 * corrCrisisAdj ^ 2 + 6) / nCrisis_1 + (18 * corrNonCrisis ^ 2 + 6) / nNonCrisis_1)) ^ 2, pTest_1_3, CHISQ.DIST.RT(chiSquared_1_3, 1), pTest_3_1, CHISQ.DIST.RT(chiSquared_3_1, 1), IF(OR(ISOMITTED(returnInverse), returnInverse = FALSE), IF(OR(ISOMITTED(returnPValue), returnPValue = FALSE), chiSquared_1_3, pTest_1_3), IF(OR(ISOMITTED(returnPValue), returnPValue = FALSE), chiSquared_3_1, pTest_3_1))));

/*
Name: CONTAGION_COSKEWNESS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Test for contagion using the Coskewness test.

Parameters:
sampleNonCrisis1	First sample of returns during non-crisis period
sampleNonCrisis2	Second sample of returns during non-crisis period
sampleCrisis1	First sample of returns during crisis period
sampleCrisis2	Second sample of returns during crisis period
returnPValue	Boolean. TRUE or omitted, returns the p-value of the test. FALSE, returns its F-statistic.
returnInverse	Boolean. FALSE or omitted, runs the test from sample 1 to sample 2. TRUE, returns the test from sample 2 to sample 1.

*/
CONTAGION_COSKEWNESS=LAMBDA(sampleNonCrisis_1,sampleNonCrisis_2,sampleCrisis_1,sampleCrisis_2,[returnPValue],[returnInverse],LET(volNonCrisis_1, STDEV.S(sampleNonCrisis_1), volCrisis_1, STDEV.S(sampleCrisis_1), meanNonCrisis_1, AVERAGE(sampleNonCrisis_1), meanCrisis_1, AVERAGE(sampleCrisis_1), nNonCrisis_1, COUNTA(sampleNonCrisis_1), nCrisis_1, COUNTA(sampleCrisis_1), volNonCrisis_2, STDEV.S(sampleNonCrisis_2), volCrisis_2, STDEV.S(sampleCrisis_2), meanNonCrisis_2, AVERAGE(sampleNonCrisis_2), meanCrisis_2, AVERAGE(sampleCrisis_2), nNonCrisis_2, COUNTA(sampleNonCrisis_2), nCrisis_2, COUNTA(sampleCrisis_2), corrNonCrisis, CORREL(sampleNonCrisis_1, sampleNonCrisis_2), corrCrisis, CORREL(sampleCrisis_1, sampleCrisis_2), corrCrisisAdj, corrCrisis / SQRT(1 + (volCrisis_1 ^ 2 - volNonCrisis_1 ^ 2) / (volNonCrisis_1 ^ 2) * (1 - corrCrisis ^ 2)), coSkewNonCrisis_1_2, AVERAGE((sampleNonCrisis_1 - meanNonCrisis_1) / volNonCrisis_1 * ((sampleNonCrisis_2 - meanNonCrisis_2) / volNonCrisis_2) ^ 2), coSkewCrisis_1_2, AVERAGE((sampleCrisis_1 - meanCrisis_1) / volCrisis_1 * ((sampleCrisis_2 - meanCrisis_2) / volCrisis_2) ^ 2), coSkewNonCrisis_2_1, AVERAGE(((sampleNonCrisis_1 - meanNonCrisis_1) / volNonCrisis_1) ^ 2 * ((sampleNonCrisis_2 - meanNonCrisis_2) / volNonCrisis_2)), coSkewCrisis_2_1, AVERAGE(((sampleCrisis_1 - meanCrisis_1) / volCrisis_1) ^ 2 * ((sampleCrisis_2 - meanCrisis_2) / volCrisis_2)), chiSquared_1_2, ((coSkewCrisis_1_2 - coSkewNonCrisis_1_2) / SQRT((4 * corrCrisisAdj ^ 2 + 2) / nCrisis_1 + (4 * corrNonCrisis ^ 2 + 2) / nNonCrisis_1)) ^ 2, chiSquared_2_1, ((coSkewCrisis_2_1 - coSkewNonCrisis_2_1) / SQRT((4 * corrCrisisAdj ^ 2 + 2) / nCrisis_1 + (4 * corrNonCrisis ^ 2 + 2) / nNonCrisis_1)) ^ 2, pTest_1_3, CHISQ.DIST.RT(chiSquared_1_2, 1), pTest_3_1, CHISQ.DIST.RT(chiSquared_2_1, 1), IF(OR(ISOMITTED(returnInverse), returnInverse = FALSE), IF(OR(ISOMITTED(returnPValue), returnPValue = FALSE), chiSquared_1_2, pTest_1_3), IF(OR(ISOMITTED(returnPValue), returnPValue = FALSE), chiSquared_2_1, pTest_3_1))));

/*
Name: CONTAGION_COVOLATILITY
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Test for contagion using the Covolatility test.

Parameters:
sampleNonCrisis1	First sample of returns during non-crisis period
sampleNonCrisis2	Second sample of returns during non-crisis period
sampleCrisis1	First sample of returns during crisis period
sampleCrisis2	Second sample of returns during crisis period
returnPValue	Boolean. TRUE or omitted, returns the p-value of the test. FALSE, returns its F-statistic.

*/
CONTAGION_COVOLATILITY=LAMBDA(sampleNonCrisis_1,sampleNonCrisis_2,sampleCrisis_1,sampleCrisis_2,[returnPValue],LET(volNonCrisis_1, STDEV.S(sampleNonCrisis_1), volCrisis_1, STDEV.S(sampleCrisis_1), meanNonCrisis_1, AVERAGE(sampleNonCrisis_1), meanCrisis_1, AVERAGE(sampleCrisis_1), nNonCrisis_1, COUNTA(sampleNonCrisis_1), nCrisis_1, COUNTA(sampleCrisis_1), volNonCrisis_2, STDEV.S(sampleNonCrisis_2), volCrisis_2, STDEV.S(sampleCrisis_2), meanNonCrisis_2, AVERAGE(sampleNonCrisis_2), meanCrisis_2, AVERAGE(sampleCrisis_2), nNonCrisis_2, COUNTA(sampleNonCrisis_2), nCrisis_2, COUNTA(sampleCrisis_2), corrNonCrisis, CORREL(sampleNonCrisis_1, sampleNonCrisis_2), corrCrisis, CORREL(sampleCrisis_1, sampleCrisis_2), corrCrisisAdj, corrCrisis / SQRT(1 + (volCrisis_1 ^ 2 - volNonCrisis_1 ^ 2) / (volNonCrisis_1 ^ 2) * (1 - corrCrisis ^ 2)), coVolNonCrisis, AVERAGE((sampleNonCrisis_1 - meanNonCrisis_1) ^ 2 / volNonCrisis_1 ^ 2 * (sampleNonCrisis_2 - meanNonCrisis_2) ^ 2 / volNonCrisis_2 ^ 2) - (1 + 2 * corrNonCrisis) ^ 2, coVolCrisis, AVERAGE((sampleCrisis_1 - meanCrisis_1) ^ 2 / volCrisis_1 ^ 2 * (sampleCrisis_2 - meanCrisis_2) ^ 2 / volCrisis_2 ^ 2) - (1 + 2 * corrCrisisAdj) ^ 2, chiSquared, ((coVolCrisis - coVolNonCrisis) / SQRT((4 * corrCrisisAdj ^ 4 + 16 * corrCrisisAdj ^ 2 + 4) / nCrisis_1 + (4 * corrNonCrisis ^ 4 + 16 * corrNonCrisis ^ 2 + 4) / nNonCrisis_1)) ^ 2, pTest, CHISQ.DIST.RT(chiSquared, 1), IF(returnPValue, pTest, chiSquared)));

/*
Name: CONTAGION_FORBESRIGOBON
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Test for contagion using the Forbes and Rigobon test.

Parameters:
sampleNonCrisis1	First sample of returns during non-crisis period
sampleNonCrisis2	Second sample of returns during non-crisis period
sampleCrisis1	First sample of returns during crisis period
sampleCrisis2	Second sample of returns during crisis period
returnPValue	Boolean. TRUE or omitted, returns the p-value of the test. FALSE, returns its F-statistic.

*/
CONTAGION_FORBESRIGOBON=LAMBDA(sampleNonCrisis_1,sampleNonCrisis_2,sampleCrisis_1,sampleCrisis_2,[returnPValue],LET(volNonCrisis_1, STDEV.S(sampleNonCrisis_1), volCrisis_1, STDEV.S(sampleCrisis_1), meanNonCrisis_1, AVERAGE(sampleNonCrisis_1), meanCrisis_1, AVERAGE(sampleCrisis_1), nNonCrisis_1, COUNTA(sampleNonCrisis_1), nCrisis_1, COUNTA(sampleCrisis_1), volNonCrisis_2, STDEV.S(sampleNonCrisis_2), volCrisis_2, STDEV.S(sampleCrisis_2), meanNonCrisis_2, AVERAGE(sampleNonCrisis_2), meanCrisis_2, AVERAGE(sampleCrisis_2), nNonCrisis_2, COUNTA(sampleNonCrisis_2), nCrisis_2, COUNTA(sampleCrisis_2), corrNonCrisis, CORREL(sampleNonCrisis_1, sampleNonCrisis_2), corrCrisis, CORREL(sampleCrisis_1, sampleCrisis_2), corrCrisisAdj, corrCrisis / SQRT(1 + (volCrisis_1 ^ 2 - volNonCrisis_1 ^ 2) / (volNonCrisis_1 ^ 2) * (1 - corrCrisis ^ 2)), zStat, (1 / 2 * LN((1 + corrCrisisAdj) / (1 - corrCrisisAdj)) - 1 / 2 * LN((1 + corrNonCrisis) / (1 - corrNonCrisis))) / (SQRT(1 / (nCrisis_1 - 3) + 1 / (nNonCrisis_1 - 3))), pTest, 2 * (1 - NORM.S.DIST(ABS(zStat), 1)), IF(returnPValue, pTest, zStat)));

/*
Name: CONTAINS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Checks whether a string contains a certain sequence of characters

Parameters:
text	The text whose content shall be checked
sequence	The sequence to be found in the text argument
[start_num]	The first character position from which the text shall be checked for the presence of the sequece. If omitted, it is 1 (first character).
[case_sensitive]	Boolean that indicates whether comparison shall be case sensitive. If omitted, it is FALSE.

*/
CONTAINS=LAMBDA(text,sequence,[start_num],[case_sensitive], LET( start_num, IF(ISOMITTED(start_num), 1,start_num), case_sensistive, IF(ISOMITTED(case_sensistive), FALSE, case_sensitive), IF( case_sensistive, NOT(ISERROR(FIND(sequence, text, start_num))), NOT(ISERROR(SEARCH(sequence, text, start_num))) ) ));

/*
Name: CONV
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Convolution and polynomial multiplication

Parameters:
u	first vector
v	second vector
[shape]	Subsection of the convolution to be returned. It can be "full" (default), "same" (central part of the convolution of the same size as u) or "valid" (only those parts of the convolution that are computed without the zero-padded edges). Using the "valid" option, length(w) is max(length(u)-length(v)+1, 0), except when length(v) is zero. If length(v) = 0, then length(w) = length(u).

*/
CONV=LAMBDA(u,v,[shape],LET(u, VEC(u), v, VEC(v), m, ROWS(u), N, ROWS(v), shape, IF(OR(ISOMITTED(shape), ISBLANK(shape)), "full", shape), prematrix, u * TRANSPOSE(v), matrix, EXPAND(prematrix, ROWS(prematrix), (MAX(COLUMNS(prematrix), ROWS(prematrix)) - 1) * 2, 0), THUNK, LAMBDA(x, LAMBDA(x)), EXPAND_THUNK_H, LAMBDA(arraythunks, IFERROR(DROP(REDUCE(0, arraythunks, LAMBDA(init,arraythunks, HSTACK(init, arraythunks()))), , 1), "")), out, TRANSPOSE(BYCOL(IFERROR(INDEX(matrix, SEQUENCE(ROWS(matrix)), EXPAND_THUNK_H(BYCOL(SEQUENCE(, COLUMNS(matrix)), LAMBDA(a, THUNK(FLIPUD(SEQUENCE(@a, 1, 1, 1))))))), ""), LAMBDA(a, SUM(a)))), final, VSTACK(INDEX(out, SEQUENCE(ROWS(u) + ROWS(v) - 2)), TAKE(u, -1) * TAKE(v, -1)), SWITCH(shape, "full", final, "same", INDEX(final, SEQUENCE(m, 1, 2)), "valid", IFERROR(INDEX(final, SEQUENCE(MAX(m - MAX(0, N - 1)), 1, N)), NA()))));

/*
Name: CORRCOEF
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Correlation coefficients

Parameters:
matrix	matrix for which correlation coefficients will be calculated
[dimension]	If 2 or omitted it considers each column as a variable. If 1, it considers each row as a variable.

*/
CORRCOEF=LAMBDA(matrix,[dimension], LET( dimension,IF(ISOMITTED(dimension),2,IF(OR(dimension=1,dimension=2),dimension,0)), SWITCH(dimension, 1,MAKEARRAY(ROWS(matrix),ROWS(matrix),LAMBDA(row,col,CORREL(INDEX(matrix,row,0),INDEX(matrix,col,0)))), 2,MAKEARRAY(COLUMNS(matrix),COLUMNS(matrix),LAMBDA(row,col,CORREL(INDEX(matrix,0,row),INDEX(matrix,0,col)))), "# Dimension must be either 1 (rows) or 2 (columns)" )));

/*
Name: CORREL_DIFF_TEST
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Test the difference between the Pearson correlations of two samples. Null hypothesis is that correlations are the same.

Parameters:
x_sample_1	x variable of sample 1
y_sample_1	y variable of sample 1
x_sample_2	x variable of sample 2
y_sample_2	y variable of sample 2
[returnZstat]	Boolean. Default is False. If False, returns the p-value of the Z-statistic. If True, returns the Z-statistic.

*/
CORREL_DIFF_TEST=LAMBDA(x_Sample_1,y_sample_1,x_sample_2,y_sample_2,[returnZstat], LET( returnZstat, IF(OR(ISOMITTED(returnZstat), ISBLANK(returnZstat)), FALSE, returnZstat), n_1, COUNTA(x_Sample_1), n_2, COUNTA(x_sample_2), rFisher_1, FISHER(CORREL(x_Sample_1, y_sample_1)), rFisher_2, FISHER(CORREL(x_sample_2, y_sample_2)), stdFisher, SQRT(1/(n_1-3) + 1/(n_2-3)), zStat, (rFisher_1 - rFisher_2)/stdFisher, pVal, 2*(1 - NORM.S.DIST(ABS(zStat), TRUE)), IF(returnZstat, zStat, pVal) ));

/*
Name: COV
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Covariance

Parameters:
A	matrix for which covariance will be calculated
[B]	if provided, calculates the covariance between A and B
[dimension]	If 2 or omitted it considers each column as a variable. If 1, it considers each row as a variable.
[w]	specifies the normalization weight for any of the previous syntaxes. When w = 0 (default), C is normalized by the number of observations-1. When w = 1, it is normalized by the number of observations.

*/
COV=LAMBDA(a,[B],[dimension],[w], LET( useB, IF(OR(ISOMITTED(B),ISBLANK(B)), FALSE, TRUE), dimension, IF(OR(ISOMITTED(dimension),ISBLANK(dimension)),2,IF(OR(dimension=1,dimension=2),dimension,0)), w, IF(OR(ISOMITTED(w),ISBLANK(w)),0,w), IF(useB, IF(w=0, COVARIANCE.S(a,B), COVARIANCE.P(a,B)), SWITCH(dimension, 1,LAMBDA(p,MMULT(p,TRANSPOSE(p)) / (COLUMNS(p) - SWITCH(w, 0, 1, 1, 0)) )(a), 2,LAMBDA(p,MMULT(TRANSPOSE(p),p) / (ROWS(p) - SWITCH(w, 0, 1, 1, 0)) )(a), "# Dimension must be either 1 (rows) or 2 (columns)" ))));

/*
Name: DETREND
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Detrend a time series

Parameters:
values	Vector of values
type	Power of the trend; if omitted equals 1. Use "LOG" for log-detrending.

*/
DETREND=LAMBDA(values,[type], LET(nPower, IF( OR(ISOMITTED(type), UPPER(type) = "LOG"), 1, type), IF(ISNUMBER(type), values - OLS_FITTED(values, ADDCONST(SEQUENCE(ROWS(values))^nPower)), EXP( LN(values) ) - EXP(  OLS_FITTED(LN(values), ADDCONST(SEQUENCE(ROWS(values))^nPower)) ) ) ) );

/*
Name: DFTABLE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Critical values for the (Augmented) Dicky-Fuller test

Parameters:
nObs	Number of observations
[significance]	Significance
[constant]	Includes a constant in the test equation
[trend]	Includes a linear trend in the test. If omitted equals false. If true, constant must be true
Obs:	
Values are approximations.	

*/
DFTABLE=LAMBDA(nObs,[significance],[constant],[trend], LET( significance,IF(ISOMITTED(significance),FALSE,significance), constant,IF(ISOMITTED(constant),FALSE,constant), trend,IF(ISOMITTED(trend),FALSE,trend), noConstant_noTrend, {-2.56574,-2.22213,-1.941,-1.61682;-2.2358,-1.15384,-0.2686,0.2656;-3.627,-3.4829,-3.365,-2.714;0,17.17265,31.223,25.364}, constant_noTrend, {-3.43035,-3.1175,-2.86154,-256677;-6.5393,-4.53235,-2.8903,-1.5384;-16.786,-9.8824,-4.234,-2.809;-79.433,-57.7669,-40.04,0}, constant_trend, {-3.95877,-3.65722,-3.41049,-3.12705;-9.0531,-6.48862,-4.3904,-2.5856;-28.428,-17.7624,-9.036,-3.925;-134.155,-85.3255,-45.374,-22.38}, matrix, IFS( AND(constant,trend), constant_trend, constant, constant_noTrend, trend, #VALUE!, TRUE, noConstant_noTrend ), colIdx, IFS(significance = 0.01, 1, significance = 0.025, 2, significance = 0.05, 3, significance = 0.1, 4, TRUE, #VALUE!), N, nObs, SLICE(matrix,1,1,colIdx,colIdx) + SLICE(matrix,2,2,colIdx,colIdx)/N + SLICE(matrix,3,3,colIdx,colIdx)/(N^2) + SLICE(matrix,4,4,colIdx,colIdx)/(N^3) ));

/*
Name: DIAG
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Returns the diagonal of a matrix

Parameters:
matrix	The matrix whose diagonal one wants to extract. If a vector, function returns a matrix with that vector as diagonal.
k	k=0 (default) is the main diagonal; k>0 is above the main diagonal; k<0 is below the main diagonal

*/
DIAG=LAMBDA(matrix,[k], LET(k,IF(ISOMITTED(k),0,k), IF( AND(ROWS(matrix)>1, COLUMNS(matrix)>1), FILTER(VEC(matrix),VEC(MAKEARRAY(ROWS(matrix),COLUMNS(matrix),LAMBDA(lin,col,col=(lin+k))))), IF(ROWS(matrix)=1, matrix*MUNIT(COLUMNS(matrix)), IF(COLUMNS(matrix)=1,matrix*MUNIT(ROWS(matrix)) )))) );

/*
Name: DIFF
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Differences between adjacent elements

Parameters:
values	Vector of values
[n]	Times the operator should be applied to the vector. If omitted, it is 1
[dim]	Dimension along the operator should be applied. If omitted, it is 1

*/
DIFF=LAMBDA(values,[n],[dim], LET( N, IF( OR( ISOMITTED(n), ISBLANK(n) ), 1, n), dim, IF( OR( ISOMITTED(dim), ISBLANK(dim) ), 1, dim), IF(dim=1, IF(N=0, values, DIFF( SLICE(values,2,-1) - SLICE(values,1,-2), N-1, dim)), IF(dim=2, IF(N=0, values, DIFF( SLICE(values,0,0,2,-1) - SLICE(values,0,0,1,-2), N-1, dim)), "# dim must be either 1 or 2" )) ) );

/*
Name: DIM
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Array length at specified dimension

Parameters:
matrix	matrix used for calculation
dim	dimension whose length is to be returned; if omitted, returns a row-vector with the length of each dimension in order

*/
DIM=LAMBDA(matrix,[dim], LET(dim, IF(ISOMITTED(dim), 3, dim), CHOOSE(dim, ROWS(matrix), COLUMNS(matrix), VSTACK(ROWS(matrix),COLUMNS(matrix)))) );

/*
Name: DRAWDOWN
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Calculates drawdown for a time series

Parameters:
values	values of the time series
[absolute]	Boolean. Default is FALSE (relative drawdown). If TRUE, returns the absolute drawdown.

*/
DRAWDOWN=LAMBDA(values,[absolute], LET( absolute, IF(OR(ISOMITTED(absolute), ISBLANK(absolute)), FALSE, absolute), maxValues, SCAN(MIN(values), values, LAMBDA(accum,val, MAXA(accum, val))), IF(absolute, (values - maxValues), -(1 - values/maxValues)) ));

/*
Name: DUMMYVAR
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Create alphabetically ordered dummy variables from categorical ones

Parameters:
categories	vector with categorical values, i.e. months of a date vector.
[exclude]	index of the excluded categorical value. If TRUE, excludes the first alphabetically ordered category.

*/
DUMMYVAR=LAMBDA(categories,[exclude], LET( check, IF( XOR( ROWS(categories)>1, COLUMNS(categories)>1 ), TRUE, FALSE), categories, IF(ROWS(categories)>1, categories, TRANSPOSE(categories) ), uniqueCategories, SORT( UNIQUE(categories) ), nCategories, COUNTA(uniqueCategories), matrix, 1 * ( REPMAT(categories,1,nCategories)=REPMAT( TRANSPOSE(uniqueCategories),ROWS(categories),1) ), IF( check=FALSE, "# categories must be a 1-column or 1-row vector", IF( OR( ISOMITTED(exclude), exclude = FALSE), matrix, SLICE(matrix, 0, 0, FILTER(SEQUENCE(1,nCategories), SEQUENCE(1,nCategories)<> (1*exclude) ), 0) ) ) ) );

/*
Name: EIGENVALUES
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Real eigenvalues of a squared matrix calculated using QR decomposition

Parameters:
matrix	squared matrix for which real eigenvalues will be numerically calculated
[numIter]	numer of iterations until stop the algorithm. If omitted, it is 20.
[order]	integer that indicates whether eigenvalues are listed assorted (0) or in ascending (1), descending (-1), absolute ascending (2), or absolute descending (-2) order. Default is 0.

*/
EIGENVALUES=LAMBDA(matrix,[numIter],[order], LET( nCols, COLUMNS(matrix), numIter,IF(OR(ISOMITTED(numIter), ISBLANK(numIter)), 20, numIter), order,IF(ISOMITTED(order), 0, order), candIter, REDUCE(FACTORIZE_QR(matrix), SEQUENCE(numIter),LAMBDA(accum,x, FACTORIZE_QR( MMULT(TAKE(accum,, IF(ISEVEN(x),-1,1)*nCols), TAKE(accum,,-IF(ISEVEN(x),-1,1)*nCols)) ) )), vals, DIAG(TAKE(candIter,,-nCols)), SWITCH(order, 0, vals, 1, SORT(vals,,1), -1, SORT(vals,,-1), 2, SORTBY(vals, MATCH(ABS(vals), SORT(ABS(vals)),0)), -2, SORTBY(vals, MATCH(ABS(vals), SORT(ABS(vals), ,-1 ),0))) ));

/*
Name: EIGENVECTORS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Real eigenvectors of a squared matrix calculated using QR decomposition

Parameters:
matrix	squared matrix for which real eigenvectos will be numerically calculated
[numIter]	numer of iterations until stop the algorithm. If omitted, it is 20.
[normalized]	boolean that indicates eigenvectors should be normalized. Default is false
[order]	integer that indicates whether eigenvectors are combined assorted (0) or in ascending (1), descending (-1), absolute ascending (2), or absolute descending (-2) order of eigenvalues. Default is 0.

*/
EIGENVECTORS=LAMBDA(matrix,[numIter],[normalized],[order],LET( norm, ROWS(matrix)*MAX(ABS(matrix)), numIter,IF(OR(ISOMITTED(numIter), ISBLANK(numIter)), 20, numIter), normalized,IF(ISOMITTED(normalized), FALSE, normalized), order,IF(ISOMITTED(order), 0, order), eigVals, EIGENVALUES(matrix,numIter,order), N, ROWS(matrix), eigVecs, MAKEARRAY(N,N, LAMBDA(row,col, LET(eigMatrix, matrix-INDEX(eigVals,col)*MUNIT(ROWS(matrix)), eigVector, VSTACK(1, MMULT( MINVERSE( DROP(eigMatrix,1,1) ), -DROP(INDEX(eigMatrix,0,1),1) ) ), INDEX(eigVector,row) )) ), normEigVecs, MAKEARRAY(N, N, LAMBDA(row,col, INDEX(eigVecs, row, col)/MNORM(INDEX(eigVecs,0,col), 2))), IF(normalized, normEigVecs, eigVecs) ));

/*
Name: ENDSWITH
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Checks whether a string ends with a certain sequence of characters

Parameters:
text	The text whose first characters shall be checked
sequence	The sequence to be compared with the last characters of the text argument
[start_num]	The first character position, counting from the last character, from which the text shall be checked for the presence of the sequece. If omitted, it is 1 (first character).
[case_sensitive]	Boolean that indicates whether comparison shall be case sensitive. If omitted, it is FALSE.

*/
ENDSWITH=LAMBDA(text,sequence,[start_num],[case_sensitive], LET( start_num, IF(ISOMITTED(start_num), 1,start_num), case_sensistive, IF(ISOMITTED(case_sensistive), FALSE, case_sensitive), CONTAINS(RIGHT(MID(text, 1, LEN(text) - start_num + 1), LEN(sequence)), sequence, 1, case_sensistive) ));

/*
Name: EVALS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Evaluates a formula expression (workbook file will have to be saved with the .xlsm extension)

Parameters:
expression	formula to be avaluated
	
Observations	

*/
EVALS=LAMBDA(expression, EVALUATE(expression));

/*
Name: EVERY
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Create alternate sequence of consecutive integers

Parameters:
lb	Lower bound of the sequence
ub	Upper bound of the sequence
[spacing]	Each subsequence length

*/
EVERY=LAMBDA(lb,ub,[spacing], LET(nLags, (ub-lb+1)/spacing, nElems, spacing, DROP(REDUCE(0,SEQUENCE(nElems,1,lb), LAMBDA(accum,val, VSTACK(accum, SEQUENCE(nLags, 1, val, nElems)))),1)));

/*
Name: EYE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Returns an identity matrix

Parameters:
n	Dimension of the identity matrix
[m]	Trims or expands (with zeros) the matrix so as it has m columns. If omitted, it is n.

*/
EYE=LAMBDA(N,[m], LET(m, IF(ISOMITTED(m),N,m), IF(N=m,MUNIT(N),HSTACK(MUNIT(N),ZEROS(N,m-N)))));

/*
Name: FACTORIZE_CHOL
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Factorizes a positive-definite matrix into a lower-triangular matrix (L) and an upper-triangular matrix (U).

Parameters:
matrix	positive-definite matrix which will be decomposed
[returnObject]	If "L" or omitted, returns the lower-triangular matrix. If "U", returns the upper-triangular matrix. If "LU", returns [L | U].
[lastCol]	internal parameter for recursion (you do not need to specify it)
[originalMatrix]	internal parameter for recursion (you do not need to specify it)

*/
FACTORIZE_CHOL=LAMBDA(matrix,[returnObject],[lastCol],[originalMatrix], LET( returnObject, IF(OR(ISOMITTED(returnObject), ISBLANK(returnObject)), "L", returnObject), lastRow, IF(ISOMITTED(lastRow), 1, lastRow), lastCol, IF(ISOMITTED(lastCol), 1, lastCol), lowerMatrix, IF(ISOMITTED(originalMatrix), ZEROS(ROWS(matrix),COLUMNS(matrix)), matrix), originalMatrix, IF(ISOMITTED(originalMatrix), matrix, originalMatrix), newMatrix, REDUCE(lowerMatrix, SEQUENCE(ROWS(matrix)), LAMBDA(accum,row, IF(AND(lastCol=1,row=1), SLICE(accum, row, row, lastCol, lastCol, SQRT( INDEX(originalMatrix,1,1) ) ), IF(AND(lastCol=1, row>1), SLICE(accum, row, row, lastCol, lastCol, INDEX(originalMatrix,row,1) / SQRT( INDEX(originalMatrix,1,1) ) ), IF(AND(lastCol>1, row<lastCol), SLICE(accum, row, row, lastCol, lastCol, 0 ), IF(AND(lastCol>1, row=lastCol), SLICE(accum, row, row, lastCol, lastCol, SQRT( INDEX(originalMatrix,lastCol,lastCol) - SUM(INDEX(accum, lastCol, SEQUENCE(1, lastCol-1))^2) ) ), IF(AND(lastCol>1, row>lastCol), SLICE(accum, row, row, lastCol, lastCol, (INDEX(originalMatrix, row, lastCol) - SUM( MAP(INDEX(accum, row, SEQUENCE(1, lastCol-1)), LAMBDA(val, IMCONJUGATE(val))) * MAP(INDEX(accum, lastCol, SEQUENCE(1, lastCol-1)), LAMBDA(val, IMCONJUGATE(val))) )) / INDEX(accum,lastCol,lastCol) ), ))))) )), IF(NOT(lastCol=COLUMNS(matrix)), FACTORIZE_CHOL(newMatrix, returnObject,lastCol+1, originalMatrix), IFERROR(SWITCH(returnObject, "L", newMatrix, "U", TRANSPOSE(newMatrix), "LU", HSTACK(newMatrix, TRANSPOSE(newMatrix))), "#ERROR: matrix must be positive-definite")) ));

/*
Name: FACTORIZE_QR
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Factorizes a matrix into its QR decomposition (Q is orthogonal and R is upper-triangular) using the Gram-Schmidt algorithm.

Parameters:
matrix	matrix which will be decomposed
[returnObject]	If "QR" or omitted, returns [Q|R]. If "Q", returns [Q]. If "R", returns [R].
[lastCol]	internal parameter for recursion (you do not need to specify it)
[normalize]	internal parameter for recursion (you do not need to specify it)
[originalMatrix]	internal parameter for recursion (you do not need to specify it)

*/
FACTORIZE_QR=LAMBDA(matrix,[returnObject],[lastCol],[normalize],[originalMatrix],LET(returnObject, IF(OR(ISOMITTED(returnObject), ISBLANK(returnObject)), "QR", returnObject), lastCol, IF(OR(ISOMITTED(lastCol), ISBLANK(lastCol)), 1, lastCol), normalize, IF(OR(ISOMITTED(normalize), ISBLANK(normalize)), TRUE, normalize), originalMatrix, IF(OR(ISOMITTED(originalMatrix), ISBLANK(originalMatrix)), matrix, originalMatrix), newMatrix, MAKEARRAY(ROWS(matrix), ROWS(matrix), LAMBDA(r,c, IF(c < lastCol, INDEX(matrix, r, c), IF(AND(c = lastCol, normalize), INDEX(matrix, r, c) / MNORM(INDEX(matrix, , c), 2), IF(AND(c = lastCol, normalize = FALSE), INDEX(INDEX(matrix, , c) - REDUCE(0, SEQUENCE(lastCol - 1), LAMBDA(accum,x, accum + SUM(INDEX(matrix, , c) * INDEX(matrix, , x)) * INDEX(matrix, , x))), r, 1), IF(c > COLUMNS(originalMatrix), 1, INDEX(matrix, r, c))))))), IF(NOT(AND(lastCol = COLUMNS(matrix), normalize)), IF(normalize, FACTORIZE_QR(newMatrix, returnObject, lastCol + 1, FALSE, originalMatrix), FACTORIZE_QR(newMatrix, returnObject, lastCol, TRUE, originalMatrix)), LET(Q, newMatrix, r, MAKEARRAY(ROWS(matrix), COLUMNS(originalMatrix), LAMBDA(r,c, IF(AND(r > 1, r > c), 0, SUM(INDEX(originalMatrix, , c) * INDEX(Q, , r))))), adjustedQ, MMULT(Q, DIAG(SIGN(DIAG(Q)))), adjustedR, MMULT(DIAG(SIGN(DIAG(Q))), r), SWITCH(returnObject, "QR", HSTACK(adjustedQ, adjustedR), "Q", adjustedQ, "R", adjustedR)))));

/*
Name: FILLMISSING
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
FILLMISSING=LAMBDA(matrix,filling, MAP(matrix, LAMBDA(item, IF(OR(ISERROR(item), ISBLANK(item)),filling,item))) );

/*
Name: FINANCE_VAR
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Value at risk, both unconditional and conditional (expected shortfall)

Parameters:
portfolioPrices	prices of each asset in the portfolio, where each column is one asset and rows are time observations
[portfolioWeights]	weights of each asset in the portfolio. Default is 1 for all assets.
[confidenceInterval]	size of confidence intervals one wants to retrieve. If conditionalVaRsteps = 0, it can be a vector. Default is 95%.
[typeVaR]	type of VaR. It can be "vcv" (variance-covariance), "vcvCond" (exact conditional variance-covariance), "vcvCondSteps" (approximate conditional variance-covariance by averaging intermediate points), "vcvCondSim" (approximate conditional variance-covariance by averaging simulated points), "mvcv" (modified value-at-risk as in Favre and Galeano, 2002), "hist" (historical), or "histBRW" (historical with BRW recency bias adjustment). Default is "vcv".
[distribution]	distribution name if typeVaR = "vcv", "vcvCondSteps" or "vcvCondSim". It can be "normal" (default), "logistic", "hypersecant", "laplace", or "cauchy".
[conditionalVaRparam]	number of points used for approximating conditional VaR. The larger, the more precise. Default is 0 (unconditional). If approximate conditional, it should be a positive integer, i.e. 500. 
[brwLambda]	lambda parameter of the BRW recency bias adjustment. Default is 0,99. Only used if [typeVaR] is "histBRW".

*/
FINANCE_VAR=LAMBDA(portfolioPrices,[portfolioWeights],[confidenceInterval],[typeVaR],[distribution],[conditionalVaRparam],[brwLambda],LET(portfolioWeights, IF(OR(ISOMITTED(portfolioWeights), ISBLANK(portfolioWeights)), EXPAND(1, 1, COLUMNS(portfolioPrices), 1), TOROW(portfolioWeights)), typeVaR, IF(OR(ISOMITTED(typeVaR), ISBLANK(typeVaR)), "vcv", typeVaR), distribution, IF(OR(ISOMITTED(distribution), ISBLANK(distribution)), "normal", distribution), brwLambda, IF(OR(ISOMITTED(brwLambda), ISBLANK(brwLambda)), 0.99, brwLambda), conditionalVaRparam, IF(OR(ISOMITTED(conditionalVaRparam), ISBLANK(conditionalVaRparam)), 0, conditionalVaRparam), confidenceInterval, IF(OR(ISOMITTED(confidenceInterval), ISBLANK(confidenceInterval)), SWITCH(typeVaR, "vcvCondSteps", TRANSPOSE(DROP(LINSPACE(0.95, 1, conditionalVaRparam + 1), -1)), "vcvCondSim", RANDARRAY(conditionalVaRparam, 1, 0.95, 1, FALSE), 0.95), SWITCH(typeVaR, "vcvCondSteps", TRANSPOSE(DROP(LINSPACE(confidenceInterval, 1, conditionalVaRparam + 1), -1)), "vcvCondSim", RANDARRAY(conditionalVaRparam, 1, confidenceInterval, 1, FALSE), TOCOL(confidenceInterval))), portfolioReturns, (DROP(portfolioPrices, 1) / DROP(portfolioPrices, -1) - 1), weightedPortfolioReturn, BYROW(portfolioReturns * portfolioWeights, LAMBDA(row, SUM(row))) / SUM(portfolioWeights), covPortfolioReturns, COV(portfolioReturns), portfolioVar, MMULT(portfolioWeights, MMULT(covPortfolioReturns, TRANSPOSE(portfolioWeights))), portfolioStd, SQRT(portfolioVar), portfolioMean, AVERAGE(weightedPortfolioReturn), portfolioMedian, MEDIAN(weightedPortfolioReturn), portfolioSkew, SKEW(weightedPortfolioReturn), portfolioKurt, KURT(weightedPortfolioReturn), zStat, NORM.S.INV(1 - confidenceInterval), vcvVAR, SWITCH(distribution, "normal", portfolioMean + zStat * portfolioStd, "logistic", portfolioMean + (portfolioStd * SQRT(3) / PI()) * LN((1 - confidenceInterval) / confidenceInterval), "hypersecant", portfolioMean + 2 * portfolioStd / PI() * LN(TAN(PI() / 2 * (1 - confidenceInterval))), "laplace", portfolioMedian + AVERAGE(ABS(weightedPortfolioReturn - portfolioMean)) * LN(2 * (1 - confidenceInterval)), "cauchy", portfolioMedian + 1 / 2 * (PERCENTILE.EXC(weightedPortfolioReturn, 0.75) - PERCENTILE.EXC(weightedPortfolioReturn, 0.25)) * TAN(PI() * (1 - confidenceInterval) - PI() / 2), ), vcvVaRModified, IF(typeVaR <> "mcvc", FALSE, portfolioMean + (zStat + 1 / 6 * (zStat ^ 2 - 1) * portfolioSkew + 1 / 24 * (zStat ^ 3 - 3 * zStat) * portfolioKurt + 1 / 36 * (2 * zStat ^ 3 - 5 * zStat) * portfolioSkew ^ 2) * portfolioStd), vcvVaRcondSim, IF(typeVaR <> "vcvCondSim", FALSE, MIN(vcvVAR) * AVERAGE(1 * (vcvVAR < RANDARRAY(ROWS(vcvVAR), 1, MIN(vcvVAR), 0)))), vcvVARcond, IF(typeVaR <> "vcvCond", FALSE, portfolioMean - portfolioStd * NORM.S.DIST((vcvVAR - portfolioMean) / portfolioStd, 0) / NORM.S.DIST((vcvVAR - portfolioMean) / portfolioStd, 1)), hsVAR, IF(typeVaR <> "hist", FALSE, PERCENTILE.EXC(weightedPortfolioReturn, 1 - confidenceInterval)), hsVAR_BRW, IF(typeVaR <> "histBRW", FALSE, LET(recency, SEQUENCE(ROWS(weightedPortfolioReturn), 1, ROWS(weightedPortfolioReturn) - 1, -1), rankedReturn, SORT(HSTACK(weightedPortfolioReturn, brwLambda ^ recency / SUM(brwLambda ^ recency)), 1, 1, FALSE), rankedReturnCumm, SCAN(0, INDEX(rankedReturn, 0, 2), LAMBDA(accum,x, accum + x)), INDEX(rankedReturn, MATCH(1 - confidenceInterval, rankedReturnCumm, 1), 1))), result, SWITCH(typeVaR, "vcv", vcvVAR, "vcvCondSteps", vcvVAR, "vcvCondSim", vcvVaRcondSim, "vcvCond", vcvVARcond, "mvcv", vcvVaRModified, "hist", hsVAR, "histBRW", hsVAR_BRW), resultFinal, BYROW(result, LAMBDA(row, AVERAGE(RMMISSING(row, 2)))), IF(distribution = "normal", resultFinal, IF(ISMEMBER(typeVaR, {"vcv";"vcvCondSteps";"vcvCondSim"}), resultFinal, "# " & typeVaR & " does not accept distribution argument"))));

/*
Name: FLIPLR
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Flip array left to right

Parameters:
matrix	The array/matrix that will be flipped.
[nSize]	Size of the group of columns that should be sorted from left to right. If omitted, it is 1.

*/
FLIPLR=LAMBDA(matrix,[nSize],LET( nSize,IF(OR(ISOMITTED(nSize), ISBLANK(nSize)),1,nSize), nLins,ROWS(matrix),nCols,COLUMNS(matrix), CHOOSECOLS(matrix, MAKEARRAY(1,nCols, LAMBDA(lin,col, nSize*ROUNDDOWN((nCols - col)/nSize,0) + SWITCH(MOD(col,nSize),0,nSize,MOD(col,nSize))  )) )));

/*
Name: FLIPUD
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Flip array up to down

Parameters:
matrix	The array/matrix that will be flipped.
[nSize]	Size of the group of rows that should be sorted from up to down. If omitted, it is 1.

*/
FLIPUD=LAMBDA(matrix,[nSize],LET( nSize,IF(OR(ISOMITTED(nSize), ISBLANK(nSize)),1,nSize), nLins,ROWS(matrix),nCols,COLUMNS(matrix), CHOOSEROWS(matrix, MAKEARRAY(nLins,1, LAMBDA(lin,col, nSize*ROUNDDOWN((nLins - lin)/nSize,0) + SWITCH(MOD(lin,nSize),0,nSize,MOD(lin,nSize))  )) )));

/*
Name: FULL
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Convert sparse matrix to full matrix

Parameters:

*/
FULL=LAMBDA(s, LET(n, INDEX(s, 1, 1), m, INDEX(s, 1, 2), default, INDEX(s, 1, 3), IF(OR(n = 0, m = 0), #NULL!, LET(s, DROP(s, 1, 0), idxsLin, INDEX(s, 0, 1), idxsCol, INDEX(s, 0, 2), baseMatrix, EXPAND(default, n, m, default), final, IF(ISERR(INDEX(s, 1, 1)), baseMatrix, REDUCE(baseMatrix, SEQUENCE(ROWS(s)), LAMBDA(accum,x, LET(lin, INDEX(idxsLin, x, 1), col, INDEX(idxsCol, x, 1), SLICE(accum, lin, lin, col, col, INDEX(s, x, 3)))))), final))));

/*
Name: FZERO
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Find the roots of a function (workbook file will have to be saved with the .xlsm extension)

Parameters:
expression	function expression in terms of variable x
[xMin]	minimum number candidate to expression root. Default is -1.
[xMax]	maximum number candidate to expression root. Default is 1.
[xStep]	size of the sweeping step between xMin and xMax (precision). Default is 1/500.

*/
FZERO=LAMBDA(expression,[xMin],[xMax],[xStep], LET( expression, LOWER(expression), nExp, LAMBDA(x, EVALS(SUBSTITUTE(expression, "x", x))), defRange, 1, xMin,IF(OR(ISOMITTED(xMin), ISBLANK(xMin)), -defRange, xMin), xMax,IF(OR(ISOMITTED(xMax), ISBLANK(xMax)), defRange, xMax), xStep,IF(OR(ISOMITTED(xStep), ISBLANK(xStep)), defRange/500, xStep), candX, xMin + xStep * SEQUENCE((xMax-xMin)/xStep + 1,1, 0), results, MAP(candX, LAMBDA(x, nExp(x))), signChgs, MAKEARRAY(ROWS(results),1,LAMBDA(row,col,IF(row=1,1,IF(OR(SIGN(INDEX(results,row,1)) = 0, SIGN(INDEX(results,row,1)) = -SIGN(INDEX(results,row-1,1))), -1, 1) ))), INDEX(candX, NOZERO(signChgs=-1), 1) ));

/*
Name: GET
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
GET=LAMBDA(matrix,position,[patch],[remove],[rowsToCols], LET( nRows, ROWS(matrix), nCols, COLUMNS(matrix), remove, IF(ISOMITTED(remove), FALSE,remove), rowsToCols, IF(ISOMITTED(rowsToCols), TRUE, rowsToCols), IF(ISOMITTED(patch), IF(remove=FALSE, IF(AND(nRows>1,nCols>1),INDEX(VEC(matrix,rowsToCols),position,1), IF(nRows>1, INDEX(matrix,position,1), INDEX(matrix,1,position) )), IF(AND(nRows>1,nCols>1),SLICE(VEC(matrix,rowsToCols),position,position,1,1,,TRUE), IF(nRows>1, SLICE(matrix,position,position,1,1,,TRUE), SLICE(matrix,1,1,position,position,,TRUE) )) ), IF(AND(nRows>1,nCols>1),SLICE(VEC(matrix,rowsToCols),position,position,1,1,patch), IF(nRows>1, SLICE(matrix,position,position,1,1,patch), SLICE(matrix,1,1,position,position,patch) )) )));

/*
Name: GRANGER
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Granger causality

Parameters:
predicted	Vector of predicted values
predictors	Vector of predictor values
lags	Amount of lags to be tested
[returnPValue]	If TRUE (or omitted), returns the p-value. If FALSE, returns the F-statistic

*/
GRANGER=LAMBDA(predicted,predictors,nLags,[returnPValue], LET( returnPValue, IF( ISOMITTED(returnPValue), TRUE, FALSE), dataRestrictedBeforeCleaning, HSTACK(predicted, ADDCONST( LAG(predicted, nLags, TRUE, TRUE) ) ), dataUnrestrictedBeforeCleaning, HSTACK( dataRestrictedBeforeCleaning, LAG(predictors, nLags, TRUE, TRUE) ), dataUnrestricted, RMMISSING(dataUnrestrictedBeforeCleaning, 1), dataRestricted, SLICE( dataUnrestricted, 0, 0, 1, 2 + nLags ), sumSquaredResidRes, SUM(OLS_RESIDUALS( SLICE(dataRestricted, 0, 0, 1, 1), SLICE(dataRestricted, 0, 0, 2, -1) )^2), sumSquaredResidUnr, SUM(OLS_RESIDUALS( SLICE(dataUnrestricted, 0, 0, 1, 1), SLICE(dataUnrestricted, 0, 0, 2, -1) )^2), m, nLags, N, DIM(dataUnrestricted,1), k, 1 + 2* nLags, Fstat, ( (sumSquaredResidRes - sumSquaredResidUnr)/ m) / (sumSquaredResidUnr / (N-k) ), pValue, FDIST(Fstat, m, N-k), IF(returnPValue, pValue, Fstat) ));

/*
Name: HASCONST
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Checks whether a matrix has a constant column or row

Parameters:
matrix	matrix
[dim]	It is 1 or 2. If omitted, it is 1 (sweeps row-wise searching for constant columns)
[hasHeader]	boolean. If omitted, it is FALSE.

*/
HASCONST=LAMBDA(matrix,[dim],[hasHeader], LET( dim, IF(OR(ISOMITTED(dim),ISBLANK(dim)), 1, dim), hasHeader, IF(OR(ISOMITTED(hasHeader),ISBLANK(hasHeader)), FALSE, hasHeader), IF(dim=1, SUM(1*BYCOL(INDEX(matrix,1+1*hasHeader,0)=DROP(matrix,1*hasHeader), LAMBDA(col, SUM(1*col)=ROWS(col))))>=1, SUM(1*BYROW(INDEX(matrix,0,1+1*hasHeader)=DROP(matrix,0,1*hasHeader), LAMBDA(row, SUM(1*row)=COLUMNS(row))))>=1 ) ));

/*
Name: HENDFILTER
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Henderson filter for trend and cyclical components

Parameters:
values	Vector of values.
[trendCycle]	If "t" or omitted returns the trend. If "c" returns the cycle.
[nTerms]	Number of terms for the filter. It should be either 5, 7, 9, 13 or 23.
[endPointAsymmetric]	Boolean. If FALSE (default), returns N/A for end points. If TRUE, calculates end points with asymmetric filter.

*/
HENDFILTER=LAMBDA(values,[trendCycle],[nTerms],[endPointAsymmetric],     LET(         y, VEC(values),         N, ROWS(y),         trendCycle, IF(OR(ISOMITTED(trendCycle), ISBLANK(trendCycle)), "t", LOWER(trendCycle)),         nTerms, IF(OR(ISOMITTED(nTerms), ISBLANK(nTerms)), 13, nTerms),         endPointAsymmetric, IF(             OR(ISOMITTED(endPointAsymmetric), ISBLANK(endPointAsymmetric)),             FALSE,             endPointAsymmetric         ),         weights, HENDFILTER_WEIGHTS(nTerms, FALSE),         trend, LET(             m, INT((nTerms - 1) / 2),             SCAN(                 0,                 SEQUENCE(N),                 LAMBDA(accum,i,                     IF(                         i <= m,                         IF(                             endPointAsymmetric = FALSE,                             NA(),                             SUM(                                 INDEX(y, SEQUENCE(i + m)) *                                     FLIPUD(HENDFILTER_WEIGHTS(nTerms, TRUE, i + m))                             )                         ),                         IF(                             AND(i > m, i <= N - m),                             SUM(INDEX(y, SEQUENCE(i + m - i + m + 1, 1, i - m)) * weights),                             IF(                                 endPointAsymmetric = FALSE,                                 NA(),                                 SUM(                                     INDEX(y, SEQUENCE(N - i + m + 1, 1, i - m)) *                                         HENDFILTER_WEIGHTS(nTerms, TRUE, N - i + 1 + m)                                 )                             )                         )                     )                 )             )         ),         final, IF(             trendCycle = "t",             trend,             IF(trendCycle = "c", y - trend, "#trendCycle must be either ""t"" or ""c""")         ),         final     ) );

/*
Name: HENDFILTER_WEIGHTS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Henderson filter weights

Parameters:
nTerms	Number of terms for the filter. It is usually either 5, 7 (quarterly data), 9, 13 (monthly data) or 23.
[asymmetric]	Boolean. If FALSE (default), returns weights of the symmetrical filter. If TRUE, returns weights of the asymmetrical filter.
[nWeights]	Number of asymmetrical weights to be returned.

*/
HENDFILTER_WEIGHTS=LAMBDA(nTerms,[asymmetric],[nWeights],LET(asymmetric, IF(OR(ISOMITTED(asymmetric), ISBLANK(asymmetric)), FALSE, asymmetric), nWeights, IF(OR(ISOMITTED(nWeights), ISBLANK(nWeights)), nTerms - 1, nWeights), symmetricWeights, LAMBDA(N, LET(m, INT((N - 1) / 2), m_1, (m + 1) * (m + 1), m_2, (m + 2) * (m + 2), m_3, (m + 3) * (m + 3), d, 8 * (m + 2) * (m_2 - 1) * (4 * m_2 - 1) * (4 * m_2 - 9) * (4 * m_2 - 25), wFinal, REDUCE(ZEROS(N, 1), SEQUENCE(m + 1, 1, 0), LAMBDA(accum,j, LET(j_2, j * j, v, (315 * (m_1 - j_2) * (m_2 - j_2) * (m_3 - j_2) * (3 * m_2 - 11 * j_2 - 16)) / d, w, SLICE(accum, m + 1 + j, m + 1 + j, 1, 1, v), IF(j > 0, SLICE(w, m + 1 - j, m + 1 - j, 1, 1, v), w)))), wFinal)), asymmetricWeights, LAMBDA(N,mw,w, LET(sumResidual, SUM(INDEX(w, SEQUENCE(N - mw - 1 + 1, 1, mw + 1))), sumEnd, REDUCE(0, SEQUENCE(N - mw - 1 + 1, 1, mw + 1), LAMBDA(accum,i, accum + ((i) - ((mw + 1) / 2)) * INDEX(w, i))), ic, IF(AND(N >= 13, N < 15), 3.5, IF(N >= 15, 4.5, 1)), b2s2, (4 / PI()) / (ic * ic), f_1, sumResidual / mw, u, SCAN(0, SEQUENCE(mw, 1, 1), LAMBDA(accum,r, LET(calc_1, (r - (mw + 1) / 2) * b2s2, calc_2, 1 + (mw * (mw - 1) * (mw + 1) / 12) * b2s2, INDEX(w, r) + f_1 + (calc_1 / calc_2) * sumEnd))), u)), w, symmetricWeights(nTerms), IF(asymmetric = FALSE, w, asymmetricWeights(nTerms, nWeights, w))));

/*
Name: HFILTER
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Hamilton filter for trend and cyclical components

Parameters:
values	Vector of values.
[trendCycle]	If "t" or omitted returns the trend. If "c" returns the cycle.
[leadLength]	Filter weight regression response variable lead h. Default is 8. Usually one should use twice the periodicity of the data. This means: annual data, 2; quarterly data, 8; monthly data, 24.
[lagLength]	Number of consecutive lags p. Default is 4. Usually one should use the periodicity of the data. This means: annual data, 1; quarterly data, 4; monthly data, 12.

*/
HFILTER=LAMBDA(values,[trendCycle],[leadLength],[lagLength], LET( y, VEC(values), N, ROWS(y), trendCycle, IF( OR(ISOMITTED(trendCycle),ISBLANK(trendCycle)), "t", LOWER(trendCycle) ), leadLength, IF( OR(ISOMITTED(leadLength),ISBLANK(leadLength)), 8, leadLength ), lagLength, IF( OR(ISOMITTED(lagLength),ISBLANK(lagLength)), 4, lagLength ), yLags, HSTACK(DROP(y,leadLength), ONES(N-leadLength,1), DROP(y,-leadLength), LAG(DROP(y,-leadLength), lagLength-1,TRUE,TRUE,FALSE,FALSE,TRUE)), yLagsClean, RMMISSING(yLags), trend, VSTACK(NA()*SEQUENCE(leadLength+lagLength-1), OLS_FITTED(TAKE(yLagsClean,,1), DROP(yLagsClean,,1))), final, IF(trendCycle = "t", trend, IF(trendCycle = "c", y - trend, "#trendCycle must be either ""t"" or ""c""") ), final ));

/*
Name: HORZCAT
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
HORZCAT=LAMBDA(matrixA,matrixB, LET( LinsA, IFERROR(ROWS(matrixA), 1), LinsB, IFERROR(ROWS(matrixB), 1), ColsA, IFERROR(COLUMNS(matrixA), 1), ColsB, IFERROR(COLUMNS(matrixB), 1), DataC, INDEX(matrixA, SEQUENCE(MAX(LinsA,LinsB)), SEQUENCE(1,ColsA) ), DataD, INDEX(matrixB, SEQUENCE(MAX(LinsA,LinsB)), SEQUENCE(1,ColsB) ), NumRows, ROWS(DataC), SeqCol, SEQUENCE( NumRows ), SeqRow, SEQUENCE(1, ColsA + ColsB), IFS(ISOMITTED(matrixA), matrixB, ISOMITTED(matrixB), matrixA, TRUE, IF( SeqRow <= ColsA, INDEX( DataC, SeqCol,SeqRow), INDEX( DataD, SeqCol, SeqRow-ColsA ) ) ) ) );

/*
Name: HORZCAT_EX
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
HORZCAT_EX=LAMBDA(matrixA,matrixB,excludeValue, LET( testA, ALL(matrixA=excludeValue), testB, ALL(matrixB=excludeValue), IFS(AND(testA,testB), NA(), testA, matrixB, testB, matrixA, TRUE, HSTACK(matrixA,matrixB)) ));

/*
Name: HPFILTER
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Hodrick-Prescott filter for trend and cyclical components

Parameters:
values	Vector of values
smoothing	Trend component smoothing parameter
[trendCycle]	If "t" or omitted returns the trend. If "c" returns the cycle
[filterType]	It can be 1 (one-sided by Kalman filter) or 2 (two-sided). Default is 2.

*/
HPFILTER=LAMBDA(values,lambda,[trendCycle],[filterType], LET( y, VEC(values), t, ROWS(y), trendCycle, IF( OR(ISOMITTED(trendCycle), ISBLANK(trendCycle)), "t", LOWER(trendCycle) ), filterType, IF( OR(ISOMITTED(filterType), ISBLANK(filterType)), 2, filterType ), IF(AND(filterType<>1, filterType<>2), "# filterType must be either 1 (one-sided) or 2 (two-sided).", IF(filterType = 2, LET( ident, EYE(t), pentad0, VSTACK( {1;5;6}, 6 * ONES(t - 6,1), {6;5;1} ), pentad1, VSTACK( {-2;-4}, -4 * ONES(t - 5,1), {-4;-2} ), pentad2, ONES(t-2,1), pentad, LAMBDA(matrix0,matrix1,matrix2, MAKEARRAY( LENGTH(matrix0), LENGTH(matrix0), LAMBDA(row,col, IF( row = col, INDEX(matrix0,row,1), IF( OR(row=col+1, col=row+1), INDEX(matrix1, MIN(row,col),1), IF( OR(row=col+2, col=row+2), INDEX(matrix2, MIN(row,col),1), 0 ) ) ) )  ) )(pentad0,pentad1,pentad2), trend, MMULT( MINVERSE( lambda * pentad + ident), y), final, IF(trendCycle = "t", trend, IF(trendCycle = "c", y - trend, "#trendCycle must be either ""t"" or ""c""")), IF(filterType=2, final, IF(t<=7, final, VSTACK(HPFILTER(DROP(y,-1),lambda,trendCycle,filterType), TAKE(final,-1)))) ), LET( Q, 1/lambda, F, {2,-1;1,0}, h, {1,0}, QQ, EXPAND(Q,2,2,0), r, 1, x, VSTACK(2*INDEX(y,1)-INDEX(y,2), 3*INDEX(y,1)-2*INDEX(y,2)), p, {100000,0;0,100000}, filter, REDUCE(ZEROS(t,6),SEQUENCE(t), LAMBDA(accum,j, LET( flat, INDEX(accum,j-1), XX, IF(j=1, x, TRANSPOSE(TAKE(flat,1,2)) ), PP, IF(j=1, p, RESHAPE(TAKE(flat,1,-4),2,2) ), update, KFILTER_UPDATE(F,h,QQ,r,INDEX(y,j),XX,PP,"xP"), updateFlat, TOROW(update,0,TRUE), SLICE(accum, j, j, 1, 6, updateFlat) ))), trend, CHOOSECOLS(filter,2), final, IF(trendCycle = "t", trend, IF(trendCycle = "c", y - trend, "#trendCycle must be either ""t"" or ""c""")), final ))) ));

/*
Name: IORDER
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Returns the integration order of a vector

Parameters:
values	Vector of values
[automatic]	If FALSE, uses constant and trend parameters. If AIC or BIC picks the specification that minimizes that information criterion and ignores constant and trend parameters.
[nLags]	Maximum number of lags for the Augmented Dickey-Fuller test
[significance]	Significance

*/
IORDER=LAMBDA(values,[automatic],[nLags],[significance],[maxDiffs], LET( automatic, IF(ISOMITTED(automatic), FALSE, automatic), nLags,IF(ISOMITTED(nLags), 0, nLags), significance, IF(ISOMITTED(significance), FALSE, significance), constant, FALSE, trend, FALSE, maxDiffs, IF(OR( ISOMITTED(maxDiffs), ISBLANK(maxDiffs)), 2, maxDiffs ), IF(ISSTATIONARY(values, automatic, nLags, significance, constant, trend), 0, IF(maxDiffs = 0, NA(), 1 + IORDER( DIFF(values,1,1), automatic, nLags, significance, maxDiffs - 1) )) ));

/*
Name: ISCOINTEGRATED
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Test whether two vectors are cointegrated (Engle–Granger method)

Parameters:
valuesA	First vector of values
valuesB	Second vector of values
[automatic]	If FALSE, uses constant and trend parameters. If AIC or BIC picks the specification that minimizes that information criterion and ignores constant and trend parameters.
[nLags]	Maximum number of lags for the Augmented Dickey-Fuller test
[significance]	Significance

*/
ISCOINTEGRATED=LAMBDA(valuesA,valuesB,[automatic],[nLags],[significance],[maxDiffs], LET( automatic, IF(ISOMITTED(automatic), FALSE, automatic), nLags,IF(ISOMITTED(nLags), 0, nLags), significance, IF(ISOMITTED(significance), FALSE, significance), constant, FALSE, trend, FALSE, maxDiffs, IF(OR( ISOMITTED(maxDiffs), ISBLANK(maxDiffs)), 2, maxDiffs ), orderA, IORDER(valuesA, automatic, nLags, significance, maxDiffs), orderB, IORDER(valuesB, automatic, nLags, significance, maxDiffs), IF(orderA<>orderB, "# both series must be integrated at the same order: valuesA is I("& orderA & ") and valuesB is I(" & orderB & ")", LET(orderC, IORDER(OLS_RESIDUALS(valuesA, ADDCONST(valuesB)), automatic, nLags, significance, maxDiffs), IF(AND(orderA>orderC, orderB>orderC), TRUE, FALSE) ) )));

/*
Name: ISEMPTY
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
ISEMPTY=LAMBDA(values, LEN(values)=0 );

/*
Name: ISMEMBER
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Checks whether a matrix contains a certain element

Parameters:
element	The sequence to be found in the text argument
matrix	The text whose content shall be checked
[start_pos]	The first matrix position from which the element shall be searched. If omitted, it is 1.
[case_sensitive]	Boolean that indicates whether comparison shall be case sensitive in case of string values. If omitted, it is FALSE.

*/
ISMEMBER=LAMBDA(element,matrix,[start_num],[case_sensitive],LET(start_num, IF(ISOMITTED(start_num), 1, start_num), case_sensitive, IF(ISOMITTED(case_sensitive), FALSE, case_sensitive), matrix, DROP(VEC(matrix), start_num - 1, 0), elementFound, NOT(ISERROR(MATCH(element, matrix, 0))), IF(elementFound, CONTAINS(element, INDEX(matrix, MATCH(element, matrix, 0)), 1, case_sensitive), FALSE)));

/*
Name: ISMULTICOLLINEAR
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Test for multicollinearity using either the variance inflation factor test, the eigenvalues test or the condition index test

Parameters:
matrix	matrix in which each column is a variable
[returnTest]	If 0 or omitted returns returns the result of the variance inflation factor test. If 1, returns the result of the minimum eigenvalues test. If 2, returns the result of the condition index test.
[maxInflationVarFactor]	Double. Default is 5.
[minEigenvalueCondition]	Double. Default is 0.01.
[maxConditionIndex]	Double. Default is 50.
[eigenMin]	minimum number candidate to eigenvalue. Default is -ROWS(matrix)*MAX(ABS(matrix)).
[eigenMax]	maximum number candidate to eigenvalue. Default is ROWS(matrix)*MAX(ABS(matrix)).
[eigenStep]	size of the sweeping step between eigenMin and eigenMax (precision). Default is ROWS(matrix)*MAX(ABS(matrix))/500.

*/
ISMULTICOLLINEAR=LAMBDA(matrix,[returnTest],[maxInflationVarFactor],[mineigenvalueCondition],[maxConditionIndex],[eigenMin],[eigenMax],[eigenStep], LET( returnTest,IF(OR(ISOMITTED(returnTest),ISBLANK(returnTest)), 0, returnTest), maxInflationVarFactor,IF(OR(ISOMITTED(maxInflationVarFactor),ISBLANK(maxInflationVarFactor)), 5, maxInflationVarFactor), mineigenvalueCondition,IF(OR(ISOMITTED(mineigenvalueCondition),ISBLANK(mineigenvalueCondition)), 0.01, mineigenvalueCondition), maxConditionIndex,IF(OR(ISOMITTED(maxConditionIndex),ISBLANK(maxConditionIndex)), 50, maxConditionIndex), inflationFactors, VARINFLATIONFACTOR(INDEX(matrix,,1),DROP(matrix,0,1),TRUE), corrcoef_matrix, CORRCOEF(matrix), eigenvals_list, EIGENVALUES(corrcoef_matrix,eigenMin,eigenMax,eigenStep), min_eigenval, MIN(eigenvals_list), max_eigenval, MAX(eigenvals_list), conditionIndex, SQRT(max_eigenval / min_eigenval), SWITCH(returnTest, 0, MAX(inflationFactors) >= maxInflationVarFactor, 1, min_eigenval <= mineigenvalueCondition, 2, conditionIndex >= maxConditionIndex, "# ReturnTest must be 0/omitted (Inflation Var Factor), 1 (Eigenvalue Condition) or 2 (Condition Index)") ));

/*
Name: ISOUTLIER
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Detect outliers in a matrix

Parameters:
matrix	The matrix whose rows or columns with outliers should be detected.
dim	Specifies the dimension of the matrix to operate along; default is 1.
method	The method for identifying an outlier. Only available is "MAD" = median absolute deviation.
lb	Lower-bound parameter for the outlier detection method.
ub	Upper-bound parameter for the outlier detection method.

*/
ISOUTLIER=LAMBDA(matrix,[dim],[method],[lb],[ub], LET( dim,IF(ISOMITTED(dim),1,dim), method,IF(ISOMITTED(method),"MAD",method), lb,IF(ISOMITTED(lb),3,lb), ub,IF(ISOMITTED(ub),3,ub), identOut, IF( dim=1, MAKEARRAY(ROWS(matrix),COLUMNS(matrix), LAMBDA(lin,col, LET(coll, RMMISSING(INDEX(matrix,0,col),dim), mad, MEDIAN(ABS(coll-MEDIAN(coll))), OR( (INDEX(matrix,lin,col) - MEDIAN(coll)) > ub*mad, (MEDIAN(coll) - INDEX(matrix,lin,col)) > lb*mad ) ))), MAKEARRAY(ROWS(matrix),COLUMNS(matrix), LAMBDA(lin,col,  LET(linn, RMMISSING(INDEX(matrix,lin,0),dim), mad, MEDIAN(ABS(linn-MEDIAN(linn))), OR( (INDEX(matrix,lin,col) - MEDIAN(linn)) > ub*mad, (MEDIAN(linn) - INDEX(matrix,lin,col)) > lb*mad ) ))) ), identOut ));

/*
Name: ISSTATIONARY
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Tests whether a vector is stationary according to the Dickey-Fuller test

Parameters:
values	Vector of values
[automatic]	If FALSE, uses constant and trend parameters. If AIC or BIC picks the specification that minimizes that information criterion and ignores constant and trend parameters.
[nLags]	Maximum number of lags for the Augmented Dickey-Fuller test
[significance]	Significance
[constant]	Includes a constant in the test equation
[trend]	Includes a linear trend in the test. If omitted equals false. If true, constant must be true, otherwise returns #VALUE!

*/
ISSTATIONARY=LAMBDA(values,[automatic],[nLags],[significance],[constant],[trend], LET( automatic, IF(ISOMITTED(automatic), FALSE, automatic), nLags,IF(ISOMITTED(nLags), 0, nLags), significance, IF(ISOMITTED(significance), FALSE, significance), constant, IF(ISOMITTED(constant), FALSE, constant), trend, IF(ISOMITTED(trend), FALSE, trend ), diffValues_1, SLICE(values,2,-1) - SLICE(values,1,-2), X_noConst, LAG(values,1,FALSE), X_const, ADDCONST(LAG(values,1,FALSE)), X_constTrend, ADDCONST(ADDTREND(LAG(values,1,FALSE))), IF( OR(automatic = "AIC", automatic = "BIC"), LET(tableIC, HSTACK( OLS_OPTIMAL_LAG(diffValues_1, X_noConst, nLags, FALSE, automatic, TRUE,TRUE), OLS_OPTIMAL_LAG(diffValues_1, X_const, nLags, FALSE, automatic, TRUE,TRUE), OLS_OPTIMAL_LAG(diffValues_1, X_constTrend, nLags, FALSE, automatic, TRUE, TRUE)), idxRowAndCol, NOZERO(tableIC=MIN(tableIC),1,"FIRST",TRUE), constant, INDEX(idxRowAndCol,1,2) > 1, trend, INDEX(idxRowAndCol,1,2) > 2, pickedX,  CHOOSE(INDEX(idxRowAndCol,1,2), X_noConst, X_const, X_constTrend), LET( x, HSTACK( pickedX, LAG(diffValues_1, INDEX(idxRowAndCol,1,1) - 1,TRUE,TRUE) ), testResult, LET(matrix, RMMISSING(HSTACK(diffValues_1,x)), y, INDEX(matrix,0,1), x, SLICE(matrix,0,0,2,-1),SLICE(OLS_COEFF_TSTAT(y, x),-1,-1) < DFTABLE(LENGTH(values), significance, constant, trend)), testResult )), LET(x, IFS(AND(constant,trend), X_constTrend, constant, X_const, trend, #VALUE!, TRUE, X_noConst ), testResult, LET(matrix, RMMISSING(HSTACK(diffValues_1,x)), y, INDEX(matrix,0,1), x, SLICE(matrix,0,0,2,-1),SLICE(OLS_COEFF_TSTAT(y, x),-1,-1) < DFTABLE(LENGTH(values), significance, constant, trend)), testResult ))));

/*
Name: ISUNIQUE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Checks whether a value is unique in a range

Parameters:
value	value to be checked uniqueness
matrix	matrix used for comparison

*/
ISUNIQUE=LAMBDA(value,matrix, SUM(1*(matrix=value)) = 1 );

/*
Name: JACKKNIFE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Jackknife sampling (or leave-N-out)

Parameters:
nout	It must be either 1 or 2. Draws all possible data samples without replacement leaving nout observations out.
jackfun	Computes statistics on each sample using jackfun
data	Data which will be sampled

*/
JACKKNIFE=LAMBDA(nout,jackfun,data, IF(AND(nout<>1,nout<>2), "# nout must be either 1 or 2", LET( nLins, ROWS(data), idxs, IF(nout=1, SEQUENCE(nLins), RMMISSING(VEC(MAKEARRAY(nLins,nLins,LAMBDA(row,col, IF(row<>col, row&";"&col, #N/A)))),1)), SCAN(0,idxs,LAMBDA(accum,x, jackfun( SLICE(data,VALUE(TEXTSPLIT(x,"-",";")),VALUE(TEXTSPLIT(x,"-",";")),0,0,,TRUE) ) )) )));

/*
Name: KFILTER
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Kalman filter estimation

Parameters:
y	Vector of observations. Limited to 1 variable.
F	The state transition matrix.
H	The observation matrix.
Q	The variance-covariance matrix of the errors in the state equation.
r	The variance of the error in the observation equation.
[x]	Initial value vector for state estimation. If omitted, it sets zero for all states.
[P]	Intial value for the MSE. If omitted, it sets an identity matrix multiplied by 10,000.
[return]	If "ALL" (default), returns [x, P, Kgain] updated. If "x", returns x updated. If "P", returns P updated. If "Kgain", returns optimal Kalman gain. If "xP", returns [x, P]. All results are flatted in the sense that each row represents one period so that x, P and Kgain are squeezed column-wise into a row.

*/
KFILTER=LAMBDA(y,F,H,Q,r,[x],[P],[return],     LET(         return, IF(OR(ISOMITTED(return), ISBLANK(return)), "ALL", return),         y, VEC(y),         t, ROWS(y),         k, ROWS(F),         x, IF(OR(ISOMITTED(x), ISBLANK(x)), ZEROS(k, 1), x),         P, IF(OR(ISOMITTED(P), ISBLANK(P)), 10000 * EYE(k), P),         filter, REDUCE(             ZEROS(t, k + k ^ 2 + k),             SEQUENCE(t),             LAMBDA(accum,j,                 LET(                     flat, INDEX(accum, j - 1),                     XX, IF(j = 1, x, TRANSPOSE(TAKE(flat, 1, k))),                     PP, IF(j = 1, P, RESHAPE(CHOOSECOLS(flat, SEQUENCE(1, k ^ 2, k + 1)), k, k)),                     update, KFILTER_UPDATE(F, H, Q, r, INDEX(y, j), XX, PP, "ALL"),                     updateFlat, TOROW(update, 0, TRUE),                     SLICE(accum, j, j, 1, k + k ^ 2 + k, updateFlat)                 )             )         ),         LET(             xFiltered, TAKE(filter, , k),             PFIltered, CHOOSECOLS(filter, SEQUENCE(1, k ^ 2, k + 1)),             kGainFiltered, CHOOSECOLS(filter, SEQUENCE(1, k, k + k ^ 2 + 1)),             final, SWITCH(                 return,                 "ALL", HSTACK(xFiltered, PFIltered, kGainFiltered),                 "xP", HSTACK(xFiltered, PFIltered),                 "x", xFiltered,                 "P", PFIltered,                 "Kgain", kGainFiltered,                 "# return must be either ALL, xP, x, P, or Kgain"             ),             final         )     ) );

/*
Name: KFILTER_UPDATE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Updates the Kalman filter estimation of the state and MSE

Parameters:
F	The state transition matrix.
H	The observation matrix.
Q	The variance-covariance matrix of the errors in the state equation.
R	The variance of the error in the observation equation.
obs	The new observation. Limited to 1 variable.
[x]	Initial value vector for state estimation. If omitted, it sets zero for all states.
[P]	Intial value for the MSE.  If omitted, it sets an identity matrix multiplied by 10,000.
[return]	If "ALL" (default), returns [x, P, Kgain] updated. If "x", returns x updated. If "P", returns P updated. If "Kgain", returns optimal Kalman gain. If "xP", returns [x, P].

*/
KFILTER_UPDATE=LAMBDA(F,h,Q,r,obs,[x],[P],[return],     LET(         x, IF(OR(ISOMITTED(x), ISBLANK(x)), ZEROS(ROWS(F), 1), x),         P, IF(OR(ISOMITTED(P), ISBLANK(P)), 10000 * EYE(ROWS(F)), P),         return, IF(OR(ISOMITTED(return), ISBLANK(return)), "ALL", return),         s, MMULT(MMULT(h, P), TRANSPOSE(h)) + r,         k, MMULT(MMULT(F, P), TRANSPOSE(h)),         Kgain, k / s,         x_, MMULT(F, x) + MMULT(Kgain, (obs - MMULT(h, x))),         Temp, F - MMULT(Kgain, h),         P_, MMULT(MMULT(Temp, P), TRANSPOSE(Temp)),         Pmse, P_ + Q + MMULT(MMULT(Kgain, r), TRANSPOSE(Kgain)),         final, SWITCH(             return,             "ALL", HSTACK(x_, Pmse, Kgain),             "xP", HSTACK(x_, Pmse),             "x", x_,             "P", Pmse,             "Kgain", Kgain,             "# return must be either ALL, xP, x, P, or Kgain"         ),         final     ) );

/*
Name: LAG
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Lag a vector

Parameters:
values	Matrix of column-vectors
[nLags]	Lag shift. If omitted equals 1
[fillWithNA]	If omitted equals true
[allLags]	If TRUE returns a matrix with shifted lags from 1 to nLAGS; default is FALSE
[descending]	If TRUE, reverses columns order; default is FALSE
[headerFirstRow]	If TRUE, ignores first row and adds headers to the lag columns in the format of a suffix "_LAG#" to the variable name; default is FALSE.
[sameLagsTogether]	If TRUE or omitted, keeps columns with same lags together. If FALSE, places all lags of the same variable side by side.

*/
LAG=LAMBDA(values,[nLags],[fillWithNA],[allLags],[descending],[headerFirstRow],[sameLagsTogether], LET( nLags, IF(ISOMITTED(nLags), 1, nLags), fillWithNA, IF(ISOMITTED(fillWithNA), TRUE, fillWithNA ), allLags, IF(ISOMITTED(allLags), FALSE,allLags), descending, IF(ISOMITTED(descending), FALSE, descending), headerFirstRow, IF(ISOMITTED(headerFirstRow), FALSE, headerFirstRow), sameLagsTogether, IF(ISOMITTED(sameLagsTogether), TRUE, sameLagsTogether), nCols, COLUMNS(values), headers,IF(headerFirstRow, REPMAT(SLICE(values,1,1) & "_LAG", 1, nLags * LARGE(1*allLags, 1)) & FLIPLR(REPMAT(SEQUENCE(1,nLags * LARGE(1*allLags, 1)),1,nCols, TRUE), nCols), FALSE), values,IF(headerFirstRow,SLICE(values,2,-1),values), resultMatrix, IF( OR( allLags = FALSE, nLags = 1), IF(fillWithNA, VSTACK( NA()*ONES(nLags,nCols), SLICE(values,1,-nLags-1) ), SLICE(values,1,-nLags-1) ), IF(fillWithNA, HSTACK( VSTACK( NA()*ONES(nLags,nCols), SLICE(values,1,-nLags-1) ), LAG(values,nLags-1,fillWithNA,allLags,TRUE) ), HSTACK(SLICE(values,1,-nLags-1), LAG(values,nLags-1,fillWithNA,allLags,TRUE) ) )), resultMatrixHeaders, IF(headerFirstRow,VSTACK(headers, resultMatrix), resultMatrix), resultOrder, IF(descending,resultMatrixHeaders,FLIPLR(resultMatrixHeaders, nCols)), IF(sameLagsTogether, resultOrder, CHOOSECOLS(resultOrder, EVERY(1,nLags*nCols,nCols))) ));

/*
Name: LEAD
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Lead a vector

Parameters:
values	Matrix of column-vectors
[nLeads]	Lead shift. If omitted equals 1
[fillWithNA]	If omitted equals true
[allLeads]	If TRUE returns a matrix with shifted leads from nLeads to 1; default is FALSE
[descending]	If TRUE, reverses columns order; default is FALSE
[headerFirstRow]	If TRUE, ignores first row and adds headers to the lag columns in the format of a suffix "_LEAD#" to the variable name; default is FALSE.
[sameLeadsTogether]	If TRUE or omitted, keeps columns with same leads together. If FALSE, places all leads of the same variable side by side.

*/
LEAD=LAMBDA(values,[nLeads],[fillWithNA],[allLeads],[descending],[headerFirstRow],[sameLeadsTogether], LET( nLeads, IF(ISOMITTED(nLeads), 1, nLeads), fillWithNA, IF(ISOMITTED(fillWithNA), TRUE, fillWithNA ), allLeads, IF(ISOMITTED(allLeads), FALSE,allLeads), descending, IF(ISOMITTED(descending), FALSE, descending), headerFirstRow, IF(ISOMITTED(headerFirstRow), FALSE, headerFirstRow), sameLeadsTogether, IF(ISOMITTED(sameLeadsTogether), TRUE, sameLeadsTogether), nCols,COLUMNS(values), headers,IF(headerFirstRow, REPMAT(SLICE(values,1,1) & "_LEAD", 1, nLeads * LARGE(1*allLeads, 1)) & FLIPLR(REPMAT(SEQUENCE(1,nLeads * LARGE(1*allLeads, 1)), 1, nCols, TRUE),nCols), FALSE), values,IF(headerFirstRow,SLICE(values,2,-1),values), resultMatrix, IF( OR( allLeads = FALSE, nLeads = 1), IF(fillWithNA, VSTACK( SLICE(values,nLeads+1,-1), NA()*ONES(nLeads,nCols) ), SLICE(values,nLeads+1,-1) ), IF(fillWithNA, HSTACK( VSTACK( SLICE(values,nLeads+1,-1), NA()*ONES(nLeads,nCols) ), LEAD(values,nLeads-1,fillWithNA,allLeads,TRUE) ), HSTACK( SLICE(values,nLeads+1,-1),  LEAD(values,nLeads-1,fillWithNA,allLeads,TRUE)  ) )), resultMatrixHeaders, IF(headerFirstRow,VSTACK(headers, resultMatrix), resultMatrix), resultOrder, IF(descending,resultMatrixHeaders,FLIPLR(resultMatrixHeaders,COLUMNS(values))), IF(sameLeadsTogether, resultOrder, CHOOSECOLS(resultOrder, EVERY(1,nLeads*nCols,nCols))) ));

/*
Name: LENGTH
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Length of largest array dimension

Parameters:

*/
LENGTH=LAMBDA(matrix, MAX(ROWS(matrix),COLUMNS(matrix)) );

/*
Name: LINSOLVE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Solve linear system of equations (AX = B) using QR decomposition. Only determined systems.

Parameters:
A	matrix with the coefficients of the linear system
B	result vector

*/
LINSOLVE=LAMBDA(a,B, LET( QR, FACTORIZE_QR(a), Q, TAKE(QR,,ROWS(QR)), r, TAKE(QR,,-(COLUMNS(QR)-ROWS(QR))), MMULT(MINVERSE(r), MMULT(TRANSPOSE(Q),B)) ) );

/*
Name: LINSPACE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Generate linearly spaced vector

Parameters:
lb	Lower bound of the vector
ub	Upper bound of the vector
[n]	Number of points

*/
LINSPACE=LAMBDA(lb,ub,[n], LET(N, IF(ISOMITTED(n),100,n), lb + (SEQUENCE(N)-1)*(ub - lb)/(N-1) ) );

/*
Name: M
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
M=LAMBDA(matrix,ref,[patch], LET(ref, MID(ref,2,LEN(ref)-2), idxs, SPLIT(ref,","), idxRows, IF( LEN(SPLIT( INDEX(idxs,1), ":")) = 0, SPLIT( INDEX(idxs,1), ":") * #VALUE!, IFERROR(VALUE(SPLIT( INDEX(idxs,1), ":")), SPLIT( INDEX(idxs,1), ":")) ), idxRowLB, IF( ISERR( INDEX(idxRows,1,1) ), 1, IF(INDEX(idxRows,1,1) = 0, 1, INDEX(idxRows,1,1) ) ), idxRowUB, IF( COLUMNS(idxRows) = 2, IF( ISERR( INDEX(idxRows,1,2) ), ROWS(matrix), IF( INDEX(idxRows,1,2) = 0, ROWS(matrix), INDEX(idxRows,1,2) ) ), IF(INDEX(idxRows,1,1)=0,ROWS(matrix),idxRowLB) ), idxCols, IF( LEN(SPLIT( INDEX(idxs,2), ":")) = 0, SPLIT( INDEX(idxs,2), ":") * #VALUE!, IFERROR(VALUE( SPLIT( INDEX(idxs,2), ":") ), SPLIT( INDEX(idxs,2), ":")) ), idxColLB, IF( ISERR( INDEX(idxCols,1,1) ), 1, IF( INDEX(idxCols,1,1) = 0, 1, INDEX(idxCols,1,1) ) ), idxColUB, IF( COLUMNS(idxCols) = 2, IF( ISERR( INDEX(idxCols,1,2) ), COLUMNS(matrix), IF( INDEX(idxCols,1,2) = 0, COLUMNS(matrix), INDEX(idxCols,1,2) ) ), IF(INDEX(idxCols,1,1)=0,COLUMNS(matrix),idxColLB) ), SLICE(matrix,idxRowLB,idxRowUB,idxColLB,idxColUB,patch,FALSE)) );

/*
Name: MAFILTER
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Moving average filter for trend and cyclical components

Parameters:
values	Vector of values.
[trendCycle]	If "t" or omitted returns the trend. If "c" returns the cycle.
[windowLength]	The length of the window. Default is 1.
[type]	Type of moving average. Only "simple" is available.
[centered]	Boolean. If FALSE (default), returns trailing moving average. If TRUE, returns centered.

*/
MAFILTER=LAMBDA(values,[trendCycle],[windowLength],[type],[centered], LET(y, VEC(values), trendCycle, IF(OR(ISOMITTED(trendCycle), ISBLANK(trendCycle)), "t", LOWER(trendCycle)), windowLength, IF(OR(ISOMITTED(windowLength), ISBLANK(windowLength)), 1, windowLength), centered, IF(OR(ISOMITTED(centered), ISBLANK(centered)), FALSE, centered), lagWindow, ROUNDDOWN(windowLength / 2, 0), evenWindow, ISEVEN(windowLength), trendTrailing, LAMBDA(values,window, IF(window = 1, values, MAKEARRAY(ROWS(values), COLUMNS(values), LAMBDA(row,col, IF(row < window, NA(), AVERAGE(INDEX(values, SEQUENCE(window, 1, row - window + 1), col))))))), trendCentered, LAMBDA(values,window, IF(window = 1, values, MAKEARRAY(ROWS(values), COLUMNS(values), LAMBDA(row,col, IF(IF(evenWindow, row < lagWindow, row <= lagWindow), NA(), AVERAGE(INDEX(values, SEQUENCE(window, 1, row - lagWindow + 1 * evenWindow), col))))))), trend, IF(centered = FALSE, trendTrailing(y, windowLength), IF(evenWindow, trendTrailing(trendCentered(y, windowLength), 2), trendCentered(y, windowLength))), final, IF(trendCycle = "t", trend, IF(trendCycle = "c", y - trend, "#trendCycle must be either ""t"" or ""c""")), final));

/*
Name: MAHAL
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Mahalanobis distance

Parameters:
Y	sample
X	reference sample

*/
MAHAL=LAMBDA(y,x,LET( centered, y-AVERAGE(x), covMatrixInv, MINVERSE(COV(x,,,0)), result, MMULT(MMULT(centered, covMatrixInv), TRANSPOSE(centered)), final, DIAG(result), final ));

/*
Name: MNORM
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Returns a matrix p-norm

Parameters:
matrix	matrix for which the norm will be calculated
[norm]	Order of the p-norm. Default is 1.

*/
MNORM=LAMBDA(matrix,[norm],LET( norm,IF(ISOMITTED(norm), 1, norm), SUM(ABS(matrix)^norm)^(1/norm) ));

/*
Name: MOVAVG
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Moving average of a matrix

Parameters:
values	Vector of values
[n]	Window of the operator. If omitted, it is 1
[dim]	Dimension along the operator should be applied. If omitted, it is 1
[type]	If omitted, it is "SIMPLE". Only "SIMPLE" is available.
[fillWithNA]	If TRUE or omitted, fills initial points with NA. If FALSE, does not include initial points.

*/
MOVAVG=LAMBDA(values,[n],[dim],[type],[fillWithNA], LET( type, IF( OR( ISOMITTED(type), ISBLANK(type) ), "SIMPLE", type), N, IF( OR( ISOMITTED(n), ISBLANK(n) ), 1, n), dim, IF( OR( ISOMITTED(dim), ISBLANK(dim) ), 1, dim), fillWithNA, IF( OR( ISOMITTED(fillWithNA), ISBLANK(fillWithNA) ), TRUE, fillWithNA), dropParam, IF(fillWithNA, 0, N-1), IF(dim=1, DROP( IF(N=1, values, MAKEARRAY(ROWS(values), COLUMNS(values), LAMBDA(row,col, IF(row < N, NA(), AVERAGE(INDEX(values, SEQUENCE(N,1,row-N+1), col))))) ), dropParam, 0), IF(dim=2, DROP( IF(N=1, values, MAKEARRAY(ROWS(values), COLUMNS(values), LAMBDA(row,col, IF(col < N, NA(), AVERAGE(INDEX(values, row, SEQUENCE(1,N,col-N+1)))))) ), 0, dropParam), "# dim must be either 1 or 2" )) ) );

/*
Name: MOVPROD
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Moving product of a matrix

Parameters:
values	Vector of values
[n]	Window of the operator. If omitted, it is 1
[dim]	Dimension along the operator should be applied. If omitted, it is 1
[type]	If omitted, it is "SIMPLE". Only "SIMPLE" is available.
[fillWithNA]	If TRUE or omitted, fills initial points with NA. If FALSE, does not include initial points.

*/
MOVPROD=LAMBDA(values,[n],[dim],[type],[fillWithNA], LET( type, IF( OR( ISOMITTED(type), ISBLANK(type) ), "SIMPLE", type), N, IF( OR( ISOMITTED(n), ISBLANK(n) ), 1, n), dim, IF( OR( ISOMITTED(dim), ISBLANK(dim) ), 1, dim), fillWithNA, IF( OR( ISOMITTED(fillWithNA), ISBLANK(fillWithNA) ), TRUE, fillWithNA), dropParam, IF(fillWithNA, 0, N-1), IF(dim=1, DROP( IF(N=1, values, MAKEARRAY(ROWS(values), COLUMNS(values), LAMBDA(row,col, IF(row < N, NA(), PRODUCT(INDEX(values, SEQUENCE(N,1,row-N+1), col))))) ), dropParam, 0), IF(dim=2, DROP( IF(N=1, values, MAKEARRAY(ROWS(values), COLUMNS(values), LAMBDA(row,col, IF(col < N, NA(), PRODUCT(INDEX(values, row, SEQUENCE(1,N,col-N+1)))))) ), 0, dropParam), "# dim must be either 1 or 2" )) ) );

/*
Name: MOVSUM
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Moving sum of a matrix

Parameters:
values	Vector of values
[n]	Window of the operator. If omitted, it is 1
[dim]	Dimension along the operator should be applied. If omitted, it is 1
[type]	If omitted, it is "SIMPLE". Only "SIMPLE" is available.
[fillWithNA]	If TRUE or omitted, fills initial points with NA. If FALSE, does not include initial points.

*/
MOVSUM=LAMBDA(values,[n],[dim],[type],[fillWithNA], LET( type, IF( OR( ISOMITTED(type), ISBLANK(type) ), "SIMPLE", type), N, IF( OR( ISOMITTED(n), ISBLANK(n) ), 1, n), dim, IF( OR( ISOMITTED(dim), ISBLANK(dim) ), 1, dim), fillWithNA, IF( OR( ISOMITTED(fillWithNA), ISBLANK(fillWithNA) ), TRUE, fillWithNA), dropParam, IF(fillWithNA, 0, N-1), IF(dim=1, DROP( IF(N=1, values, MAKEARRAY(ROWS(values), COLUMNS(values), LAMBDA(row,col, IF(row < N, NA(), SUM(INDEX(values, SEQUENCE(N,1,row-N+1), col))))) ), dropParam, 0), IF(dim=2, DROP( IF(N=1, values, MAKEARRAY(ROWS(values), COLUMNS(values), LAMBDA(row,col, IF(col < N, NA(), SUM(INDEX(values, row, SEQUENCE(1,N,col-N+1)))))) ), 0, dropParam), "# dim must be either 1 or 2" )) ) );

/*
Name: MULTIFILTER
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Filter dataset based on (multiple) criteria passed as string

Parameters:
array	The matrix whose rows will be filtered.
include	Criteria passed as string in the format [column name][operator][value], where [operator] is one of =, <>, >, >=, <, or <=. If [value] is a string, then it must be in-between double quotes, as "value" (it is not allowed to have double quotes in the middle of the string). Multiple criteria should be separated by ";".
[if_empty]	Value returned in case there are no filtered rows. If omitted, it returns #N/A.
[headers]	The name of the columns of the array. If omitted, headers are set as the first row of [array] which is then excluded from the filtering rows.

*/
MULTIFILTER=LAMBDA(array,include,[if_empty],[headers], LET( criteriaDelim, ";", if_empty, IF(ISOMITTED(if_empty), NA(), if_empty), headers, IF(ISOMITTED(headers), INDEX(array,1,0), headers), array, IF(ISOMITTED(headers), SLICE(array,2,-1), array), include, SPLIT(include, criteriaDelim), actualValue, LAMBDA(value, LET(value,TRIM(value), IF( AND( LEFT(value,1) = """", RIGHT(value,1) = """" ),  MID(value, 2, LEN(value) - 2), VALUE(value) ) ) ), IFERROR( REDUCE(array, include, LAMBDA(accum,criterion,  FILTER(accum, IFS( NOT(ISERR(SEARCH("<>",criterion))), LET( crit, LAMBDA(idx, INDEX(SPLIT(criterion, "<>"),1,idx)), colCrit, MATCH( crit(1), headers, 0 ), INDEX(accum,0,colCrit) <> actualValue(crit(2)) ), NOT(ISERR(SEARCH(">=",criterion))), LET( crit, LAMBDA(idx, INDEX(SPLIT(criterion, ">="),1,idx)), colCrit, MATCH( crit(1), headers, 0 ), INDEX(accum,0,colCrit) >= actualValue(crit(2)) ), NOT(ISERR(SEARCH("<=",criterion))), LET( crit, LAMBDA(idx, INDEX(SPLIT(criterion, "<="),1,idx)), colCrit, MATCH( crit(1), headers, 0 ), INDEX(accum,0,colCrit) <= actualValue(crit(2)) ), NOT(ISERR(SEARCH("=",criterion))), LET( crit, LAMBDA(idx, INDEX(SPLIT(criterion, "="),1,idx)), colCrit, MATCH( crit(1), headers, 0 ), INDEX(accum,0,colCrit) = actualValue(crit(2)) ), NOT(ISERR(SEARCH(">",criterion))), LET( crit, LAMBDA(idx, INDEX(SPLIT(criterion, ">"),1,idx)), colCrit, MATCH( crit(1), headers, 0 ), INDEX(accum,0,colCrit) > actualValue(crit(2)) ), NOT(ISERR(SEARCH("<",criterion))), LET( crit, LAMBDA(idx, INDEX(SPLIT(criterion, "<"),1,idx)), colCrit, MATCH( crit(1), headers, 0 ), INDEX(accum,0,colCrit) < actualValue(crit(2)) ) ) ))), if_empty) ) );

/*
Name: NNZ
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Number of nonzero (nor FALSE) matrix elements

Parameters:

*/
NNZ=LAMBDA(matrix, COUNTA(matrix) - SUM(IF(matrix=FALSE,1,0)) - SUM(IF(matrix=0,1,0)) );

/*
Name: NOZERO
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
NOZERO=LAMBDA(matrix,[n],[direction],[returnRowAndCol], LET( N, IF(OR(ISOMITTED(n),n<1), ROWS(matrix)*COLUMNS(matrix),n), direction, IF(ISOMITTED(direction), "first", LOWER(direction) ), returnRowAndCol, IF(ISOMITTED(returnRowAndCol), FALSE, returnRowAndCol), vecMatrix, 1*(VEC(matrix)*1<>0), nElems, SUM(vecMatrix), IF(nElems = 0, NA(), LET( filtered, FILTER(SEQUENCE(ROWS(matrix)*COLUMNS(matrix)),vecMatrix), filteredOrdered, IF(direction = "first", filtered, IF( direction = "last", FLIPUD(filtered), #VALUE!) ), LET(idxs, INDEX( filteredOrdered, SEQUENCE( MIN(COUNTA(filteredOrdered),N) ), 0 ), IF(returnRowAndCol, HSTACK( IF(MOD(idxs,ROWS(matrix))=0,ROWS(matrix),MOD(idxs,ROWS(matrix))), ROUNDUP(idxs/COLUMNS(matrix),0) ), idxs) )))));

/*
Name: NUMEL
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Number of array elements

Parameters:

*/
NUMEL=LAMBDA(matrix, ROWS(matrix)*COLUMNS(matrix) );

/*
Name: OLS_AIC
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_AIC=LAMBDA(y,x,[coeff],LET(bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), 2*DIM(x,2) - 2*OLS_LOGLIKELIHOOD(y,x,coeff)  ));

/*
Name: OLS_BIC
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_BIC=LAMBDA(y,x,[coeff],LET(bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), DIM(x,2)*LN(LENGTH(y)) - 2*OLS_LOGLIKELIHOOD(y,x,coeff)  ));

/*
Name: OLS_COEFF
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_COEFF=LAMBDA(y,x,LET( XX, MINVERSE(MMULT(TRANSPOSE(x),x)), XY, MMULT(TRANSPOSE(x),y), MMULT(XX,XY)));

/*
Name: OLS_COEFF_PVALUE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_COEFF_PVALUE=LAMBDA(y,x,[coeff],LET(bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), tstat, bbeta / DIAG(SQRT(OLS_VARCOV(y,x,coeff))), T.DIST.2T(ABS(tstat), LENGTH(y) - DIM(x,2) )));

/*
Name: OLS_COEFF_PVALUE_HAC
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_COEFF_PVALUE_HAC=LAMBDA(y,x,[coeff],[type],[lags], LET( bbeta, IF(OR(ISOMITTED(coeff), ISBLANK(coeff)), OLS_COEFF(y,x), VEC(coeff)), tstat, bbeta / DIAG(SQRT(OLS_VARCOV_HAC(y,x,coeff,type,lags))), T.DIST.2T(ABS(tstat), LENGTH(y) - DIM(x,2)) ));

/*
Name: OLS_COEFF_PVALUE_HC
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_COEFF_PVALUE_HC=LAMBDA(y,x,[coeff],[type], LET( bbeta, IF(OR(ISOMITTED(coeff), ISBLANK(coeff)), OLS_COEFF(y,x), VEC(coeff)), tstat, bbeta / DIAG(SQRT(OLS_VARCOV_HAC(y,x,coeff,type))), T.DIST.2T(ABS(tstat), LENGTH(y) - DIM(x,2)) ));

/*
Name: OLS_COEFF_STDEV
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_COEFF_STDEV=LAMBDA(y,x,[coeff],LET(bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), DIAG(SQRT(OLS_VARCOV(y,x,coeff))) ));

/*
Name: OLS_COEFF_STDEV_HAC
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_COEFF_STDEV_HAC=LAMBDA(y,x,[coeff],[type],[lags], LET( bbeta, IF(OR(ISOMITTED(coeff),ISBLANK(coeff)), OLS_COEFF(y,x), VEC(coeff)), DIAG(SQRT(OLS_VARCOV_HAC(y,x,coeff,type,lags))) ));

/*
Name: OLS_COEFF_STDEV_HC
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_COEFF_STDEV_HC=LAMBDA(y,x,[coeff],[type], LET( bbeta, IF(OR(ISOMITTED(coeff),ISBLANK(coeff)), OLS_COEFF(y,x), VEC(coeff)), DIAG(SQRT(OLS_VARCOV_HAC(y,x,coeff,type))) ));

/*
Name: OLS_COEFF_TSTAT
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_COEFF_TSTAT=LAMBDA(y,x,[coeff],LET(bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), bbeta / DIAG(SQRT(OLS_VARCOV(y,x,coeff))) ));

/*
Name: OLS_COEFF_TSTAT_HAC
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_COEFF_TSTAT_HAC=LAMBDA(y,x,[coeff],[type],[lags], LET( bbeta, IF(OR(ISOMITTED(coeff), ISBLANK(coeff)), OLS_COEFF(y,x), VEC(coeff)), bbeta / DIAG(SQRT(OLS_VARCOV_HAC(y,x,coeff,type,lags))) ));

/*
Name: OLS_COEFF_TSTAT_HC
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_COEFF_TSTAT_HC=LAMBDA(y,x,[coeff],[type], LET( bbeta, IF(OR(ISOMITTED(coeff), ISBLANK(coeff)), OLS_COEFF(y,x), VEC(coeff)), bbeta / DIAG(SQRT(OLS_VARCOV_HAC(y,x,coeff,type))) ));

/*
Name: OLS_CONF_INTERVAL
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_CONF_INTERVAL=LAMBDA(Xtarget,significance,y,x,[coeff],LET(Xtarget, TRANSPOSE(Xtarget), bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), df, LENGTH(y)-DIM(x,2), MSres, SUM(OLS_RESIDUALS(y,x,bbeta)^2)/df, seFIT, SQRT( MSres * MMULT(MMULT(TRANSPOSE(Xtarget), MINVERSE(MMULT(TRANSPOSE(x),x))),Xtarget) ), fcst, TRANSPOSE(BYCOL(Xtarget, LAMBDA(col, SUM(col*bbeta)))), tcrit, T.DIST.2T(significance, df), HSTACK(fcst-tcrit*DIAG(seFIT), fcst,fcst+tcrit*DIAG(seFIT)) ));

/*
Name: OLS_COOKS_DISTANCE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_COOKS_DISTANCE=LAMBDA(y,x,[coeff],[returnCriticalStatus],[criticalThreshold], LET( bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), returnCriticalStatus, IF(ISOMITTED(returnCriticalStatus),FALSE,returnCriticalStatus), criticalThreshold, IF(ISOMITTED(criticalThreshold), 1, criticalThreshold), levValues, OLS_LEVERAGE(y,x,bbeta), mse, MMULT(TRANSPOSE(OLS_RESIDUALS(y,x,bbeta)),OLS_RESIDUALS(y,x,bbeta)) / (LENGTH(y)-DIM(x,2)), cooksDist, OLS_RESIDUALS(y,x,bbeta)^2/(DIM(x,2)*mse) * (levValues/((1-levValues)^2)), IF( returnCriticalStatus=FALSE, cooksDist, cooksDist>=criticalThreshold ) ));

/*
Name: OLS_DURBINWATSON
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_DURBINWATSON=LAMBDA(y,x,[coeff], LET( bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), res, OLS_RESIDUALS(y,x,bbeta), dw, SUM((DROP(res,1) - DROP(res,-1))^2) / SUM(res^2), dw ));

/*
Name: OLS_ESS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_ESS=LAMBDA(y,x,[coeff], LET( bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), hasConst, HASCONST(x,1,FALSE), IF(hasConst, VAR.P(OLS_FITTED(y,x,bbeta))*LENGTH(y), SUM(OLS_FITTED(y,x,bbeta)^2)) ));

/*
Name: OLS_FITTED
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_FITTED=LAMBDA(y,x,[coeff],LET(bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), MMULT(x,bbeta)));

/*
Name: OLS_FSTAT
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_FSTAT=LAMBDA(y,x,[coeff], LET( bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), kRestricted, IF(HASCONST(x,1,FALSE), 1, 0), (OLS_R2(y,x,coeff)/(DIM(x,2)-kRestricted))/((1-OLS_R2(y,x,coeff))/(LENGTH(y)-DIM(x,2))) ));

/*
Name: OLS_FSTAT_PVALUE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_FSTAT_PVALUE=LAMBDA(y,x,[coeff], LET( bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), kRestricted, IF(HASCONST(x,1,FALSE), 1, 0), F.DIST.RT(OLS_FSTAT(y,x,bbeta),DIM(x,2)-kRestricted,LENGTH(y)-DIM(x,2)) ));

/*
Name: OLS_HQC
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_HQC=LAMBDA(y,x,[coeff],LET(bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), 2*DIM(x,2)*LN(LN(LENGTH(y))) - 2*OLS_LOGLIKELIHOOD(y,x,coeff)  ));

/*
Name: OLS_LEVERAGE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_LEVERAGE=LAMBDA(y,x,[coeff],[returnCriticalStatus],[criticalThreshold], LET( bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), returnCriticalStatus, IF(ISOMITTED(returnCriticalStatus),FALSE,returnCriticalStatus), criticalThreshold, IF(ISOMITTED(criticalThreshold), 3*DIM(x,2)/LENGTH(y), criticalThreshold), levValues, DIAG(MMULT(MMULT(x,MINVERSE(MMULT(TRANSPOSE(x),x))),TRANSPOSE(x))), IF( returnCriticalStatus=FALSE, levValues, levValues>=criticalThreshold ) ));

/*
Name: OLS_LOGLIKELIHOOD
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_LOGLIKELIHOOD=LAMBDA(y,x,[coeff], LET( bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), resid, OLS_RESIDUALS(y,x,bbeta), -LENGTH(y)/2*(1 + LN(2*PI()) - LN(LENGTH(y))) -LENGTH(y)/2*LN(SUMSQ(resid)) ));

/*
Name: OLS_OPTIMAL_LAG
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_OPTIMAL_LAG=LAMBDA(y,x,nLags,[addConstant],[criterion],[returnCriterionValues],[includeZeroLag], LET( criterion, IF(ISOMITTED(criterion), "BIC", UPPER(criterion)), returnCriterionValues, IF(ISOMITTED(returnCriterionValues), FALSE, returnCriterionValues), includeZeroLag, IF(ISOMITTED(includeZeroLag), FALSE, includeZeroLag), X_1, IF( OR( ISOMITTED(addConstant), addConstant), ADDCONST(x), x), X_2, RMMISSING( HSTACK(y, X_1, LAG(y,nLags,TRUE,TRUE,FALSE) ) ), y, SLICE(X_2,0,0,1,1), IF( AND(criterion <> "AIC", criterion <> "BIC"), "# Criterion must be AIC or BIC", IF( DIM(X_2,1) <= DIM(X_2,2), "# Number of parameters must be smaller than the non-missing sample size (after adding lags)", LET( critFunc, SWITCH(criterion,"AIC",LAMBDA(funcY,funcX,OLS_AIC(funcY,funcX)), "BIC",LAMBDA(funcY,funcX,OLS_BIC(funcY,funcX))), critValues, MAP(SEQUENCE(nLags + includeZeroLag), LAMBDA(row, critFunc(y, SLICE(X_2,0,0,2,DIM(X_2,2) - (nLags - row + includeZeroLag))) ) ), IF(returnCriterionValues, critValues, MATCH( MIN(critValues), critValues, 0) - includeZeroLag ) )))));

/*
Name: OLS_PRED_INTERVAL
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_PRED_INTERVAL=LAMBDA(Xtarget,significance,y,x,[coeff],LET(Xtarget, TRANSPOSE(Xtarget), bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), df, LENGTH(y)-DIM(x,2), MSres, SUM(OLS_RESIDUALS(y,x,bbeta)^2)/df, sePRED, SQRT( MSres * (1 + MMULT(MMULT(TRANSPOSE(Xtarget), MINVERSE(MMULT(TRANSPOSE(x),x))),Xtarget)) ), fcst, TRANSPOSE(BYCOL(Xtarget, LAMBDA(col, SUM(col*bbeta)))), tcrit, T.DIST.2T(significance, df), HSTACK(fcst-tcrit*DIAG(sePRED), fcst,fcst+tcrit*DIAG(sePRED)) ));

/*
Name: OLS_R2
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_R2=LAMBDA(y,x,[coeff],LET(bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), OLS_ESS(y,x,bbeta)/(OLS_ESS(y,x,bbeta) + OLS_RSS(y,x,bbeta))  ));

/*
Name: OLS_R2_ADJUSTED
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_R2_ADJUSTED=LAMBDA(y,x,[coeff],LET(bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), 1 - (1-OLS_R2(y,x,coeff))*(LENGTH(y)-1)/(LENGTH(y)-DIM(x,2)) ));

/*
Name: OLS_RESIDUALS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_RESIDUALS=LAMBDA(y,x,[coeff],LET(bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), y - MMULT(x,bbeta)));

/*
Name: OLS_RESIDUALS_AR
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_RESIDUALS_AR=LAMBDA(y,x,[coeff],[order], LET( bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), order, IF(ISOMITTED(order), 1, order), rho, OLS_COEFF(DROP(OLS_RESIDUALS(y,x,bbeta),order), DROP(OLS_RESIDUALS(y,x,bbeta),-order)), rho ));

/*
Name: OLS_RESIDUALS_PARTIAL
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_RESIDUALS_PARTIAL=LAMBDA(y,x,[coeff],LET(bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), res, y - MMULT(x,bbeta), resMat, REPMAT(res, 1, DIM(x,2)), MAKEARRAY(LENGTH(y), DIM(x,2), LAMBDA(row,col, INDEX(resMat,row,col) + INDEX(bbeta,col,1)*INDEX(x,row,col) ))));

/*
Name: OLS_RESIDUALS_STD
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_RESIDUALS_STD=LAMBDA(y,x,[coeff],[standardized], LET( bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), standardized, IF(ISOMITTED(standardized), TRUE, standardized), res, y - MMULT(x,bbeta), N, LENGTH(y), df, N - DIM(x,2), MSres, SUM(res^2)/df, SWITCH(standardized, FALSE, res, TRUE, res / SQRT(MSres), "N", res / SQRT(MSres), "T", res / SQRT(MSres*(1-OLS_LEVERAGE(y,x,bbeta))), "TOUT", LET(lev, OLS_LEVERAGE(y,x,bbeta), MSresi, ( (N-df+1)*MSres - res^2 / (1-lev) ) / (N - df), res / SQRT(MSresi * (1 - lev)) ), "# param standardized must be FALSE (default), TRUE (or ""N""), ""T"" or ""TOUT""" ) ));

/*
Name: OLS_RSS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_RSS=LAMBDA(y,x,[coeff], LET( bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), SUM(OLS_RESIDUALS(y,x,bbeta)^2) ));

/*
Name: OLS_STDEV
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_STDEV=LAMBDA(y,x,[coeff],LET(bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), SQRT(SUMSQ(OLS_RESIDUALS(y,x,bbeta))/(LENGTH(y)-DIM(x,2)))  ));

/*
Name: OLS_SUMMARY
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_SUMMARY=LAMBDA(y,x,[hasLabels],[addConstToX],[addTrendToX],[robustStd],[robustType],[lagsHAC],LET(hasLabels, IF(OR(ISOMITTED(hasLabels), ISBLANK(hasLabels)), FALSE, hasLabels), addConstToX, IF(OR(ISOMITTED(addConstToX), ISBLANK(addConstToX)), FALSE, addConstToX), addTrendToX, IF(OR(ISOMITTED(addTrendToX), ISBLANK(addTrendToX)), FALSE, addTrendToX), robustStd, IF(OR(ISOMITTED(robustStd), ISBLANK(robustStd)), FALSE, robustStd), robustType, IF(OR(ISOMITTED(robustType), ISBLANK(robustType)), SWITCH(robustStd, FALSE, "", "HAC", "Bartlett", "HC", "HC0"), robustType), lagsHAC, IF(OR(ISOMITTED(lagsHAC), ISBLANK(lagsHAC)), ROUNDDOWN(4 * (LENGTH(y) / 100) ^ (2 / 9), 0), lagsHAC), Xtrend, IF(1 * addTrendToX > 0, ADDTREND(x, 1, 1, IF(hasLabels, "trend", FALSE)), x), Xconst, IF(addConstToX, ADDCONST(Xtrend, 1, IF(hasLabels, "const", FALSE)), Xtrend), yLabel, IF(hasLabels, TAKE(y, 1), "Y"), xLabels, IF(hasLabels, VEC(TAKE(Xconst, 1)), VERTCAT_EX(VERTCAT_EX(IF(addConstToX = TRUE, "const", "ex"), IF(addTrendToX * 1 > 0, "trend", "ex"), "ex"), "X" & SEQUENCE(COLUMNS(x)), "ex")), y, IF(hasLabels, DROP(y, 1), y), x, IF(hasLabels, DROP(Xconst, 1), Xconst), bbeta, OLS_COEFF(y, x), std, IF(robustStd = FALSE, OLS_COEFF_STDEV(y, x, bbeta), SWITCH(robustStd, "HAC", OLS_COEFF_STDEV_HAC(y, x, bbeta, robustType, lagsHAC), "HC", OLS_COEFF_STDEV_HC(y, x, bbeta, robustType))), tstat, IF(robustStd = FALSE, OLS_COEFF_TSTAT(y, x, bbeta), SWITCH(robustStd, "HAC", OLS_COEFF_TSTAT_HAC(y, x, bbeta, robustType, lagsHAC), "HC", OLS_COEFF_TSTAT_HC(y, x, bbeta, robustType))), pValue, IF(robustStd = FALSE, OLS_COEFF_PVALUE(y, x, bbeta), SWITCH(robustStd, "HAC", OLS_COEFF_PVALUE_HAC(y, x, bbeta, robustType, lagsHAC), "HC", OLS_COEFF_PVALUE_HC(y, x, bbeta, robustType))), stars, MAKEARRAY(ROWS(pValue), 1, LAMBDA(row,col, LET(val, INDEX(pValue, row), IFS(val <= 0.01, "***", val <= 0.05, "**", val <= 0.1, "*", val > 0.1, "")))), meanDepVar, AVERAGE(y), stdevDepVar, STDEV.S(y), sumSqRes, OLS_RSS(y, x, bbeta), stdevReg, OLS_STDEV(y, x, bbeta), rSquared, OLS_R2(y, x, bbeta), rSquaredAdj, OLS_R2_ADJUSTED(y, x, bbeta), Fstat, OLS_FSTAT(y, x, bbeta), fStatPvalue, OLS_FSTAT_PVALUE(y, x, bbeta), logLik, OLS_LOGLIKELIHOOD(y, x, bbeta), infoCritAkaike, OLS_AIC(y, x, bbeta), infoCritSchwarz, OLS_BIC(y, x, bbeta), infoCritHQ, OLS_HQC(y, x, bbeta), resAR, OLS_RESIDUALS_AR(y, x, bbeta, 1), durbinWatson, OLS_DURBINWATSON(y, x, bbeta), titleLabel, EXPAND("Regression summary: " & yLabel, 1, 6, ""), stdLabel, EXPAND("Standard errors: " & SWITCH(robustStd, FALSE, "Ordinary", "HAC", "HAC; bandwith " & lagsHAC & ", Kernel " & robustType, "HC", "HC of type " & robustType), 1, 6, ""), colLabel, HSTACK("", "coefficient", "standard error", "t-stat", "p-value", ""), rowLabel, xLabels, emptyLabel, EXPAND("", 1, 6, ""), mainTable, VSTACK(titleLabel, stdLabel, colLabel, HSTACK(rowLabel, bbeta, std, tstat, pValue, stars)), otherStats, HSTACK(VSTACK("Mean of dependent var.", "Sum of squared res.", "R-squared", "F", "Loglikelihood", "Schwarz criterion", "AR coeff. of residuals"), VSTACK(meanDepVar, sumSqRes, rSquared, Fstat, logLik, infoCritSchwarz, resAR), EXPAND("", 7, 1, ""), VSTACK("Std of dependent var.", "Std of regression", "R-squared adjusted", "P-value(F)", "Akaike criterion", "Hannan-quinn criterion", "Durbin-Watson"), VSTACK(stdevDepVar, stdevReg, rSquaredAdj, fStatPvalue, infoCritAkaike, infoCritHQ, durbinWatson), EXPAND("", 7, 1, "")), IFERROR(VSTACK(mainTable, emptyLabel, otherStats), "# Error: check inputs")));

/*
Name: OLS_TSS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_TSS=LAMBDA(y,x,[coeff],LET(bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)), OLS_ESS(y,x,bbeta) + OLS_RSS(y,x,bbeta)  ));

/*
Name: OLS_VARCOV
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_VARCOV=LAMBDA(y,x,[coeff],LET(bbeta, IF(ISOMITTED(coeff), OLS_COEFF(y,x), VEC(coeff)),XX,MINVERSE(MMULT(TRANSPOSE(x),x)), ( SUM(OLS_RESIDUALS(y,x,bbeta)^2) / (ROWS(x) - COLUMNS(x) ) ) *XX));

/*
Name: OLS_VARCOV_HAC
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_VARCOV_HAC=LAMBDA(y,x,[coeff],[type],[lags], LET( bbeta, IF(OR(ISOMITTED(coeff), ISBLANK(coeff)), OLS_COEFF(y,x), VEC(coeff)), N, LENGTH(y), k, COLUMNS(x), type, IF(OR(ISOMITTED(type), ISBLANK(type)), "Bartlett", type), lags, IF(OR(ISOMITTED(lags), ISBLANK(lags)), ROUNDDOWN(4*(N/100)^(2/9),0), lags), XX, MINVERSE(MMULT(TRANSPOSE(x),x)), resMatrix, MMULT(OLS_RESIDUALS(y,x,bbeta),TRANSPOSE(OLS_RESIDUALS(y,x,bbeta))), weightMatrix, SWITCH( type, "Bartlett", MAKEARRAY(N,N, LAMBDA(lin,col, IF(ABS(col-lin)>lags, 0, (1-ABS(col-lin)/(lags+1)) * INDEX(resMatrix,lin,col)) ) ), "Parzen", MAKEARRAY(N,N, LAMBDA(lin,col, LET(a, ABS(col-lin)/(lags+1), IF(a > 1, 0, IF(a > 0.5, 2*(1-a)^3,  1-6*a^2+6*a^3)) * INDEX(resMatrix,lin,col)) ) ) ), covMatrix, MMULT( MMULT(XX, MMULT(MMULT(TRANSPOSE(x),weightMatrix),x)),XX), covMatrix ));

/*
Name: OLS_VARCOV_HC
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
OLS_VARCOV_HC=LAMBDA(y,x,[coeff],[type], LET( bbeta, IF(OR(ISOMITTED(coeff), ISBLANK(coeff)), OLS_COEFF(y,x), VEC(coeff)), N, LENGTH(y), k, COLUMNS(x), type, IF(OR(ISOMITTED(type), ISBLANK(type)), "HC0", type), XX, MINVERSE(MMULT(TRANSPOSE(x),x)), resMatrix, OLS_RESIDUALS(y,x,bbeta), leverage, OLS_LEVERAGE(y,x,bbeta), weightMatrix, SWITCH(type, "HC0", MAKEARRAY(N,N, LAMBDA(lin,col, IF(col<>lin,0, INDEX(resMatrix,lin)^2) ) ), "HC1", MAKEARRAY(N,N, LAMBDA(lin,col, IF(col<>lin,0, N/(N-k) * INDEX(resMatrix,lin)^2) ) ), "HC2", MAKEARRAY(N,N, LAMBDA(lin,col, IF(col<>lin,0, 1/(1 - INDEX(leverage,lin))*INDEX(resMatrix,lin)^2) ) ), "HC3", MAKEARRAY(N,N, LAMBDA(lin,col, IF(col<>lin,0, 1/((1 - INDEX(leverage,lin))^2)*INDEX(resMatrix,lin)^2) ) ), "HC4", MAKEARRAY(N,N, LAMBDA(lin,col, IF(col<>lin,0, 1/((1 - INDEX(leverage,lin))^MIN(4,INDEX(leverage,lin)/AVERAGE(leverage)))*INDEX(resMatrix,lin)^2) ) ), ), covMatrix, MMULT( MMULT(XX, MMULT(MMULT(TRANSPOSE(x),weightMatrix),x)),XX), covMatrix ));

/*
Name: ONES
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Returns a matrix of ones

Parameters:
n	Dimension of the matrix of ones to be returned
[m]	If provided, it sets the number of columns of the matrix. If omitted, it is n.

*/
ONES=LAMBDA(N,[m], LET(m, IF(OR(ISOMITTED(m),ISBLANK(m)),N,m), EXPAND(1, N, m, 1)) );

/*
Name: PCA
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Principal component analysis of raw data

Parameters:
matrix	matrix for which the factors will be calculated. Columns are variables.
[returnObject]	all, in order of importance: default is 1 is (eigenvectors, loadings or "feature vector"); 2 is (principal components or scores); 3 is (eigenvalues); 4 is (Hotelling t-squared statistic of each observation calculated with all components); 5 is (percentage of variance explained); 6 is (mean of variables)
[nFactors]	number of factors to be calculated. Default is 1
[standardize]	boolean. If omitted, FALSE. If TRUE, standardizes matrix before analysis.
[isPopulation]	boolean. If omitted, FALSE. If TRUE, treats matrix as population data, applying population covariance and population standand deviation methods.
[numIter]	numer of iterations until stop the eigenvectors algorithm. If omitted, it is 20.

*/
PCA=LAMBDA(matrix,[returnObject],[nFactors],[standardize],[isPopulation],[numIter],LET(returnObject, IF(ISOMITTED(returnObject), 1, returnObject), nFactors, IF(ISOMITTED(nFactors), 1, nFactors), standardize, IF(ISOMITTED(standardize), FALSE, standardize), isPopulation, IF(ISOMITTED(isPopulation), FALSE, isPopulation), numIter, IF(ISOMITTED(numIter), 20, numIter), standardized, IF(standardize = FALSE, matrix, MAKEARRAY(ROWS(matrix), COLUMNS(matrix), LAMBDA(row,col, (INDEX(matrix, row, col) - AVERAGE(INDEX(matrix, 0, col))) / IF(isPopulation = FALSE, STDEV.S(INDEX(matrix, 0, col)), STDEV.P(INDEX(matrix, 0, col)))))), covs, MAKEARRAY(COLUMNS(standardized), COLUMNS(standardized), LAMBDA(row,col, IF(isPopulation = FALSE, COVARIANCE.S(INDEX(standardized, 0, row), INDEX(standardized, 0, col)), COVARIANCE.P(INDEX(standardized, 0, row), INDEX(standardized, 0, col))))), eigenValues, EIGENVALUES(covs, numIter, -1), proportionVar, 100 * eigenValues / SUM(eigenValues), eigenVectors, EIGENVECTORS(covs, numIter, TRUE, -1), mu, TRANSPOSE(BYCOL(matrix, LAMBDA(col, AVERAGE(col)))), scores, MMULT(standardized - IF(standardize, 0, TRANSPOSE(mu)), CHOOSECOLS(eigenVectors, SEQUENCE(nFactors))), tsquared, MAKEARRAY(ROWS(matrix), 1, LAMBDA(row,col, SUMPRODUCT(INDEX(scores, row) ^ 2, 1 / TRANSPOSE(eigenValues)))), final, CHOOSE(returnObject, TAKE(eigenVectors, , nFactors), scores, TAKE(eigenValues, nFactors), tsquared, TAKE(proportionVar, nFactors), mu), final));

/*
Name: RANKRANGE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Rank each column or row of a range independently. Range may contain text values.

Parameters:
range	matrix used for comparison
[rankRows]	If TRUE (default), rank columns of each row. If FALSE, rank rows of each column.
[asc]	If TRUE (default) ranks in ascending order. If FALSE, ranks in descending order.
[uniqueRanksOnly]	If FALSE (default) repeated values have the same rank. If FALSE, each repeated value has a unique rank in ascending order as it appears from left to right (sorted rows) or from up to down (sorted columns).

*/
RANKRANGE=LAMBDA(range,[rankRows],[asc],[uniqueRanksOnly],     LET(         asc, IF(OR(ISOMITTED(asc),ISBLANK(asc)),TRUE,asc),         uniqueRanksOnly, IF(OR(ISOMITTED(uniqueRanksOnly),ISBLANK(uniqueRanksOnly)),FALSE,uniqueRanksOnly),         nRows, ROWS(range),         mCols, COLUMNS(range),         SWITCH(IF(rankRows,1), 1,             LET(nonExclusive, MAKEARRAY(ROWS(range), COLUMNS(range), LAMBDA(row,col,             IF(asc, SUM(--(INDEX(range,row,0) < INDEX(range,row,col)))+1, SUM(--(INDEX(range,row,0) > INDEX(range,row,col)))+1)             )),             IF(uniqueRanksOnly=FALSE, nonExclusive,             MAKEARRAY(ROWS(range), COLUMNS(range), LAMBDA(row,col,             INDEX(nonExclusive, row, col) + SUM(1*(INDEX(nonExclusive, row, col)=SLICE(nonExclusive,row,row,1,col))) - 1             ))             )),             LET(nonExclusive, MAKEARRAY(ROWS(range), COLUMNS(range), LAMBDA(row,col,             IF(asc, SUM(--(INDEX(range,0,col) < INDEX(range,row,col)))+1, SUM(--(INDEX(range,0,col) > INDEX(range,row,col)))+1)             )),             IF(uniqueRanksOnly=FALSE, nonExclusive,             MAKEARRAY(ROWS(range), COLUMNS(range), LAMBDA(row,col,             INDEX(nonExclusive, row, col) + SUM(1*(INDEX(nonExclusive, row, col)=SLICE(nonExclusive,1,row,col,col))) - 1             ))             ))         )     ) );

/*
Name: REPLACEALL
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
REPLACEALL=LAMBDA(text,oldChars,newText,[treatCharsAsText], LET(treatCharsAsText, IF(ISOMITTED(treatCharsAsText), TRUE, treatCharsAsText), oldChars, IF(treatCharsAsText, VEC(oldChars), TEXTJOIN("",TRUE,oldChars) ), IF(treatCharsAsText, IF(AND(LENGTH(oldChars)=1,oldChars=""), text, REPLACEALL( SUBSTITUTE( text, SLICE(oldChars,1,1), newText), IFERROR(SLICE(oldChars,2,-1), ""), newText,treatCharsAsText)), IF(oldChars ="", text, REPLACEALL( SUBSTITUTE( text, LEFT(oldChars,1), newText), MID(oldChars, 2, LEN(oldChars)-1), newText,treatCharsAsText) ))));

/*
Name: REPMAT
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Repeat copies of array

Parameters:
matrix	Matrix to be copied
[n]	Copies the matrix so as it has n rows of copies. If omitted, it is 1.
[m]	Copies the matrix so as it has m columns of copies. If omitted, it is 1.
[expandFromWithin]	Boolean. If omitted, it is FALSE. If TRUE, the array is expanded by consecutively repeating each element in the [n] and [m] directions.

*/
REPMAT=LAMBDA(matrix,[n],[m],[expandFromWithin], LET( N, IF( OR( ISOMITTED(n), n<1 ),1,n), m, IF( OR( ISOMITTED(m), m<1),1,m), expandFromWithin, IF( OR( ISOMITTED(expandFromWithin), ISBLANK(expandFromWithin)), FALSE, expandFromWithin), result, IF(expandFromWithin=FALSE, LET( nn,N*ROWS(matrix), mm,m*COLUMNS(matrix), nseq, IF(MOD(SEQUENCE(nn,1),ROWS(matrix))=0, ROWS(matrix), MOD(SEQUENCE(nn,1),ROWS(matrix))), mseq, IF(MOD(SEQUENCE(1,mm),COLUMNS(matrix))=0, COLUMNS(matrix), MOD(SEQUENCE(1,mm),COLUMNS(matrix))), MAKEARRAY(nn,mm,LAMBDA(row,col,INDEX(matrix,INDEX(nseq,row),INDEX(mseq,1,col)))) ), LET( matrixV, REDUCE(matrix, SEQUENCE(ROWS(matrix)), LAMBDA(accum,x, VSTACK(accum, REPMAT(INDEX(matrix,x,0),N,1)))), matrixH, REDUCE(matrixV, SEQUENCE(COLUMNS(matrix)), LAMBDA(accum,x, HSTACK(accum, REPMAT(INDEX(matrixV,0,x),1,m)))), DROP(matrixH,ROWS(matrix),COLUMNS(matrix)) )), result ));

/*
Name: RESHAPE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Reshape array

Parameters:
matrix	The matrix that will be reshaped.
n	Number of rows
m	Number of columns
[rowsToCols]	Boolean that tells whether individual rows should be transposed before reshape. Default is FALSE.

*/
RESHAPE=LAMBDA(matrix,N,m,[rowsToCols], LET( rowsToCols, IF(ISOMITTED(rowsToCols),FALSE,rowsToCols), vecMatrix, VEC( IF(rowsToCols, matrix, TRANSPOSE(matrix)) ), IF( N+1 <= ROWS(vecMatrix), HSTACK( SLICE( vecMatrix, 1, N ), RESHAPE( SLICE( vecMatrix, N+1, ROWS(vecMatrix)  ), N,  m, TRUE) ), vecMatrix ) ));

/*
Name: REVERSECOLS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
REVERSECOLS=LAMBDA(matrix,LET(matrix2,TRANSPOSE(matrix),TRANSPOSE(SORTBY(matrix2,SEQUENCE(ROWS(matrix2),1),-1))));

/*
Name: REVERSEROWS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
REVERSEROWS=LAMBDA(matrix, SORTBY(matrix, SEQUENCE(ROWS(matrix),1),-1) );

/*
Name: RMMISSING
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Remove rows or columns with missing entries

Parameters:
matrix	The matrix whose rows or columns with missing entries will be removed. Missing entries are empty cells or Excel errors, including #N/A.
dim	Specifies the dimension of the matrix to operate along; default is 1.

*/
RMMISSING=LAMBDA(matrix,[dim], LET(dim,IF(ISOMITTED(dim),1,dim), IF(dim = 1, FILTER( matrix, BYROW(matrix,LAMBDA(row, SUM(1*ISERROR(row)) + SUM(1*ISBLANK(row)) ))=0 ), FILTER( matrix, BYCOL(matrix,LAMBDA(col, SUM(1*ISERROR(col)) + SUM(1*ISBLANK(col)) ))=0 ) ) ));

/*
Name: RMOUTLIERS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Remove rows or columns with outliers

Parameters:
matrix	The matrix whose rows or columns with outliers will be removed.
dim	Specifies the dimension of the matrix to operate along; default is 1.
method	The method for identifying an outlier. Only available is "MAD" = median absolute deviation.
lb	Lower-bound parameter for the outlier detection method.
ub	Upper-bound parameter for the outlier detection method.

*/
RMOUTLIERS=LAMBDA(matrix,[dim],[method],[lb],[ub], LET( dim,IF(ISOMITTED(dim),1,dim), method,IF(ISOMITTED(method),"MAD",method), lb,IF(ISOMITTED(lb),3,lb), ub,IF(ISOMITTED(ub),3,ub), identOut, 1*ISOUTLIER(matrix,dim,method,lb,ub), IF(dim = 1, FILTER( matrix, BYROW(identOut,LAMBDA(row, SUM(IFERROR(row,0))))=0 ), FILTER( matrix, BYCOL(identOut,LAMBDA(col, SUM(IFERROR(col,0)) ))=0 ) ) ));

/*
Name: RWFILTER
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Random walk filter for trend and cyclical components

Parameters:
values	Vector of values.
[trendCycle]	If "t" or omitted returns the trend. If "c" returns the cycle.
[lowerCutoff]	Lower cutoff period for the cyclical component, in units of data periodicity, specified as a numeric scalar. Usually one should use: annual data, 2; quarterly data, 6; monthly data, 18. Default is 6.
[upperCutoff]	Upper cutoff period for the cyclical component, in units of data periodicity, specified as a numeric scalar. Usually one should use: annual data, 8; quarterly data, 32; monthly data, 96. Default is 32.
[root]	Boolean. If TRUE, there is unit root in the time series. If FALSE, there is no unit root in the time series. Default is TRUE.
[drift]	Boolean. If TRUE, time series is random walk with possibly a nonzero drift or time series is stationary about a linear time trend. If FALSE, there is no drift or time trend in time series. Default is TRUE.

*/
RWFILTER=LAMBDA(values,[trendCycle],[lowerCutoff],[upperCutoff],[root],[drift], LET( trendCycle, IF(OR(ISOMITTED(trendCycle), ISBLANK(trendCycle)), "t", LOWER(trendCycle)), root, IF(OR(ISOMITTED(root), ISBLANK(root)), TRUE, root), drift, IF(OR(ISOMITTED(drift), ISBLANK(drift)), TRUE, drift), CFFILTER(values, trendCycle, lowerCutoff, upperCutoff, -1, root, drift, "asymmetric") ));

/*
Name: SAMPLE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
SAMPLE=LAMBDA(matrix,N,[dim],[withoutReplacement], LET( dim,IF(ISOMITTED(dim),1,dim), withoutReplacement,IF(ISOMITTED(withoutReplacement),TRUE,withoutReplacement), matrixClass, IF(dim=1,matrix,IF(dim=2,TRANSPOSE(matrix),#VALUE!)), matrixSample, IF(withoutReplacement = FALSE, SLICE(matrixClass, RANDARRAY(N,1,1,ROWS(matrixClass),TRUE), 0 ), IF(withoutReplacement = TRUE, SLICE(SORTBY(matrixClass, RANDARRAY(ROWS(matrixClass))), SEQUENCE(N), 0), #VALUE!)), IF(dim=1,matrixSample,TRANSPOSE(matrixSample)) ));

/*
Name: SEASADJ
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Seasonally adjust a (stationarized) time series with seasonal dummies

Parameters:
dates	Vector of dates. Frequency must be lower or equal to monthly and greater or equal to yearly.
values	Vector of values.

*/
SEASADJ=LAMBDA(dates,values, LET(dummies, DUMMYVAR( MONTH(dates), FALSE),  AVERAGE(values) + OLS_RESIDUALS(values, dummies  ) ) );

/*
Name: SEASFACTOR
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Seasonal factors of a stationarized time series

Parameters:
dates	Vector of dates. Frequency must be lower or equal to monthly and greater or equal to yearly.
values	Vector of values.

*/
SEASFACTOR=LAMBDA(dates,values, LET(dummies, DUMMYVAR( MONTH(dates), FALSE),  SEASADJ(dates, values) - values ) );

/*
Name: SHIFT
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Shift array by rows and/or columns

Parameters:
A	Matrix that will be shifted.
[shiftRows]	Rows to be shifted, default is 0. Positive integers shift downward, negative ones shift upwards.
[shiftCols]	Columns to be shifted, default is 0.  Positive integers shift to the right, negative ones shift to the left.
[fill]	Fillling value for expanded rows and columns. Default is 0.

*/
SHIFT=LAMBDA(a,[shiftRows],[shiftCols],[fill], LET(fill, IF(OR(ISOMITTED(fill), ISBLANK(fill)), 0, fill), shiftRows, IF(OR(ISOMITTED(shiftRows), ISBLANK(shiftRows)), 0, shiftRows), shiftCols, IF(OR(ISOMITTED(shiftCols), ISBLANK(shiftCols)), 0, shiftCols), shiftRowsAbs, ABS(shiftRows), shiftColsAbs, ABS(shiftCols), N, ROWS(a), m, COLUMNS(a), Arow, IF(shiftRows = 0, a, IF(shiftRows > 0, VSTACK(EXPAND(fill, shiftRowsAbs, m, fill), a), VSTACK(a, EXPAND(fill, shiftRowsAbs, m, fill)))), Acol, IF(shiftCols = 0, Arow, IF(shiftCols > 0, HSTACK(EXPAND(fill, N + shiftRowsAbs, shiftColsAbs, fill), Arow), HSTACK(Arow, EXPAND(fill, N + shiftRowsAbs, shiftColsAbs, fill)))), TAKE(Acol, IF(shiftRows >= 0, N, -N), IF(shiftCols >= 0, m, -m))));

/*
Name: SLICE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
SLICE=LAMBDA(matrix,[lin_start],[lin_end],[col_start],[col_end],[patch],[remove],LET(nLins, ROWS(matrix), nCols, COLUMNS(matrix), lin_start, IF(OR(ISOMITTED(lin_start), lin_start = 0), 1, IF(ISTEXT(lin_start), MATCH(lin_start, INDEX(matrix, 0, 1), 0), lin_start)), lin_end, IF(OR(ISOMITTED(lin_end), lin_end = 0), nLins, IF(ISTEXT(lin_end), MATCH(lin_end, INDEX(matrix, 0, 1), 0), lin_end)), col_start, IF(OR(ISOMITTED(col_start), col_start = 0), 1, IF(ISTEXT(col_start), MATCH(col_start, INDEX(matrix, 1, 0), 0), col_start)), col_end, IF(OR(ISOMITTED(col_end), col_end = 0), nCols, IF(ISTEXT(col_end), MATCH(col_end, INDEX(matrix, 1, 0), 0), col_end)), remove, IF(OR(ISOMITTED(remove), remove = 0), FALSE, remove), LET(lin_start, IF(ABS(lin_start) > nLins, SIGN(lin_start) * nLins, lin_start), lin_end, IF(ABS(lin_end) > nLins, SIGN(lin_end) * nLins, lin_end), col_start, IF(ABS(col_start) > nCols, SIGN(col_start) * nCols, col_start), col_end, IF(ABS(col_end) > nCols, SIGN(col_end) * nCols, col_end), idxLins, SEQUENCE(IF(lin_end > 0, lin_end, nLins + lin_end + 1) - IF(lin_start > 0, lin_start, nLins + lin_start + 1) + 1, 1, IF(lin_start > 0, lin_start, nLins + lin_start + 1)), idxCols, SEQUENCE(1, IF(col_end > 0, col_end, nCols + col_end + 1) - IF(col_start > 0, col_start, nCols + col_start + 1) + 1, IF(col_start > 0, col_start, nCols + col_start + 1)), result, IF(ISOMITTED(patch), IF(remove = FALSE, INDEX(matrix, idxLins, idxCols), LET(remLins, COMPLEMENT(idxLins, 1, nLins), remCols, TRANSPOSE(COMPLEMENT(idxCols, 1, nCols)), INDEX(matrix, remLins, remCols))), LET(patchLeft, IF(col_start - 1 >= 1, HSTACK(M(matrix, "[" & (lin_start) & ":" & (lin_end) & ",1:" & (col_start - 1) & "]"), patch), patch), patchRight, IF(col_end + 1 <= nCols, HSTACK(patchLeft, M(matrix, "[" & (lin_start) & ":" & (lin_end) & "," & (col_end + 1) & ":]")), patchLeft), patchUpper, IF(lin_start - 1 >= 1, VSTACK(M(matrix, "[1:" & (lin_start - 1) & ",:]"), patchRight), patchRight), patchLower, IF(lin_end + 1 <= nLins, VSTACK(patchUpper, M(matrix, "[" & (lin_end + 1) & ":,:]")), patchUpper), IF(AND(ROWS(patch) = (lin_end - lin_start + 1), COLUMNS(patch) = (col_end - col_start + 1)), patchLower, "# Patch range size is different"))), result)));

/*
Name: SORTRANGE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Sort each column or row of a range independently. Range may contain text values.

Parameters:
range	matrix used for comparison
[sortRows]	If TRUE (default), sort columns of each row. If FALSE, sort rows of each column.
[asc]	If TRUE (default) sorts in ascending order. If FALSE, sorts in descending order.

*/
SORTRANGE=LAMBDA(range,[sortRows],[asc],     LET(         asc, IF(ISOMITTED(asc),TRUE,asc),         nRows, ROWS(range),         mCols, COLUMNS(range),         SWITCH(IF(sortRows,1), 1,             MAKEARRAY(nRows,mCols,                 LAMBDA(i,j,                     INDEX( BYROW(range,                             LAMBDA(value,                                 IF( asc,                                     LET(sortedValue, SORT(value,,,TRUE), SLICE(sortedValue,1,1,j,j)),                                     LET(sortedValue, SORT(value,,-1,TRUE), SLICE(sortedValue, 1,1,j,j))                                 )                             )                     ), i, 1)                 )             ),             MAKEARRAY(nRows,mCols,                 LAMBDA(i,j,                     INDEX( BYCOL(range,                         LAMBDA(value,                             IF( asc,                                 LET(sortedValue, SORT(value,,,FALSE), SLICE(sortedValue, i, i, 1, 1)),                                 LET(sortedValue, SORT(value,,-1,FALSE), SLICE(sortedValue, i, i, 1, 1))                             )                         )                     ), 1, j)                 )             )         )     ) );

/*
Name: SPARSE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Create sparse matrix

Parameters:
[A]	The matrix in full form that will be converted to sparse. Sparse format is a 3-column matrix in which first column is row index, second column is column index, and third column is default element value; all default elements are squeezed out; and first row is the dimension of matrix A as [number of rows, number of columns, default element]. If A is omitted or is empty, it returns an empty sparse matrix.
[default]	Default element. If omitted, it is 0.

*/
SPARSE=LAMBDA([a],[default],     LET(         default, IF(OR(ISOMITTED(default), ISBLANK(default)), 0, default),         IF(             OR(ISOMITTED(a), ISBLANK(a)),             HSTACK({0,0}, default),             LET(                 elems, VEC(a),                 linCol, REPMAT(SEQUENCE(ROWS(a), 1), COLUMNS(a), 1, FALSE),                 colCol, REPMAT(SEQUENCE(COLUMNS(a), 1), ROWS(a), 1, TRUE),                 final, HSTACK(linCol, colCol, elems),                 filtered, FILTER(final, elems <> default),                 head, EXPAND(TRANSPOSE(DIM(a)), 1, 3, default),                 IF(ISERR(INDEX(filtered, 1, 1)), head, VSTACK(head, filtered))             )         )     ) );

/*
Name: SPARSEDIAG
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Returns the diagonal of a sparse matrix as a sparse matrix

Parameters:
matrix	The sparse matrix whose diagonal matrix one wants to extract.
k	k=0 (default) is the main diagonal; k>0 is above the main diagonal; k<0 is below the main diagonal

*/
SPARSEDIAG=LAMBDA(matrix,[k], LET(k, IF(OR(ISOMITTED(k), ISBLANK(k)), 0, k), N, INDEX(matrix, 1, 1), m, INDEX(matrix, 1, 2), default, INDEX(matrix, 1, 3), head, TAKE(matrix, 1), s, DROP(matrix, 1, 0), idxsLin, INDEX(s, 0, 1), idxsCol, INDEX(s, 0, 2), final, FILTER(s, idxsLin = (idxsCol - k), NA()), IF(ISERROR(INDEX(final, 1, 1)), HSTACK(0, 0, default), VSTACK(head, final))));

/*
Name: SPARSEDOT
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Multiply element-wise two sparse matrices

Parameters:
A	The first sparse matrix.
B	The second sparse matrix.
[default]	Default element. If omitted, it is the default element of matrix A.

*/
SPARSEDOT=LAMBDA(A,B,[default], SPARSEWISE(A, B, PRODUCT, default));

/*
Name: SPARSEEYE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Returns a sparse identity matrix

Parameters:
n	Dimension of the sparse identity matrix
[m]	Trims or expands (with zeros) the matrix so as it has m columns
[default]	Default element. If omitted, it is 0.

*/
SPARSEEYE=LAMBDA(n,[m],[default], LET( default, IF(OR(ISOMITTED(default), ISBLANK(default)), 0, default), m, IF(OR(ISOMITTED(m), ISBLANK(m)), n, m), nLins, MIN(n,m), head, HSTACK(n,m,default), A, IF(default <> 1, HSTACK(SEQUENCE(nLins),SEQUENCE(nLins),ONES(nLins,1)), NA()), final, IF(ISERROR(INDEX(A,1,1)), head, VSTACK(head, A)), final ));

/*
Name: SPARSEHSTACK
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Concatenate horizontally two sparse matrices

Parameters:
A	The first sparse matrix.
B	The second sparse matrix.
[default]	Default element. If omitted, it is the default element of matrix A.

*/
SPARSEHSTACK=LAMBDA(A,B,[default],     LET(         default, IF(OR(ISOMITTED(default), ISBLANK(default)), INDEX(A, 1, 3), default),         defaultA, INDEX(A, 1, 3),         defaultB, INDEX(B, 1, 3),         A, IF(defaultA = default, A, SPARSE(FULL(A), default)),         B, IF(defaultB = default, B, SPARSE(FULL(B), default)),         headA, TAKE(A, 1),         headB, TAKE(B, 1),         nA, INDEX(A, 1, 1),         nB, INDEX(B, 1, 1),         mA, INDEX(A, 1, 2),         mB, INDEX(B, 1, 2),         headC, HSTACK(MAX(nA, nB), mA + mB, default),         hA, DROP(A, 1),         hB, DROP(B, 1),         hBshift, HSTACK(             CHOOSECOLS(hB, 1),             CHOOSECOLS(hB, 2) + mA,             CHOOSECOLS(hB, 3)         ),         VSTACK(headC, hA, hBshift)     ) );

/*
Name: SPARSEMINUS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Subtract two sparse matrices

Parameters:
A	The sparse matrix that will be subtracted.
B	The sparse matrix that will subtract.
[default]	Default element. If omitted, it is the default element of matrix A.

*/
SPARSEMINUS=LAMBDA(A,B,[default], LET(Bvalues, -DROP(INDEX(B, 0, 3), 1), B, HSTACK(TAKE(B, , 2), VSTACK(INDEX(B, 1, 3), Bvalues)), SPARSEWISE(A, B, SUM, default)));

/*
Name: SPARSEMULT
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Multiply two sparse matrices

Parameters:
A	The first sparse matrix.
B	The second sparse matrix.
[default]	Default element. If omitted, it is the default element of matrix A.

*/
SPARSEMULT=LAMBDA(A,B,[default],     LET(         default, IF(OR(ISOMITTED(default), ISBLANK(default)), INDEX(A, 1, 3), default),         A, IF(default = INDEX(A, 1, 3), A, SPARSE(FULL(A), default)),         B, IF(default = INDEX(B, 1, 3), B, SPARSE(FULL(B), default)),         rightSize, SUM(1 * (INDEX(A, 1, 2) = INDEX(B, 1, 1))) = 1,         IF(             rightSize = FALSE,             "# sparse matrix A must have as many columns as B rows",             LET(                 nLins, INDEX(A, 1, 1),                 nCols, INDEX(B, 1, 2),                 head, HSTACK(nLins, nCols, default),                 hA, DROP(A, 1),                 hB, DROP(B, 1),                 rowsA, INDEX(hA, 0, 1),                 colsA, INDEX(hA, 0, 2),                 valsA, INDEX(hA, 0, 3),                 rowsB, INDEX(hB, 0, 1),                 colsB, INDEX(hB, 0, 2),                 valsB, INDEX(hB, 0, 3),                 maxColA, MAX(colsA),                 final, MAKEARRAY(                     nLins,                     nCols,                     LAMBDA(row,col,                         LET(                             hAfilt, FILTER(hA, rowsA = row, HSTACK(row, 1, default)),                             hBfilt, FILTER(hB, colsB = col, HSTACK(1, col, default)),                             hAvals, XLOOKUP(                                 SEQUENCE(maxColA),                                 INDEX(hAfilt, 0, 2),                                 INDEX(hAfilt, 0, 3),                                 default                             ),                             hBvals, XLOOKUP(                                 SEQUENCE(maxColA),                                 INDEX(hBfilt, 0, 1),                                 INDEX(hBfilt, 0, 3),                                 default                             ),                             SUM(hAvals * hBvals)                         )                     )                 ),                 SPARSE(final, default)             )         )     ) );

/*
Name: SPARSEONES
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Returns a sparse matrix of ones

Parameters:
n	Dimension of the matrix of ones to be returned.
[m]	If provided, it sets the number of columns of the matrix. If omitted, it is n.
[default]	Default element. If omitted, it is 0.

*/
SPARSEONES=LAMBDA(n,[m],[default], LET( default, IF(OR(ISOMITTED(default), ISBLANK(default)), 0, default), m, IF(OR(ISOMITTED(m), ISBLANK(m)), n, m), nLins, MIN(n,m), head, HSTACK(n,m,default), A, IF(default <> 1, HSTACK(REPMAT(SEQUENCE(nLins),m,1,TRUE),REPMAT(SEQUENCE(m),nLins,1),ONES(nLins*m,1)), NA()), final, IF(ISERROR(INDEX(A,1,1)), head, VSTACK(head, A)), final ));

/*
Name: SPARSEPLUS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Sum two sparse matrices

Parameters:
A	The first sparse matrix.
B	The second sparse matrix.
[default]	Default element. If omitted, it is the default element of matrix A.

*/
SPARSEPLUS=LAMBDA(A,B,[default],SPARSEWISE(A,B,SUM,default));

/*
Name: SPARSEPOWER
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Raise sparse matrix elements to a specific power

Parameters:
A	The sparse matrix.
power	The power each element will be raised.
[raiseDefault]	Boolean. If TRUE (default), all elements are raised to [power]. If FALSE, only sparse elements are raised to [power].

*/
SPARSEPOWER=LAMBDA(A,power,[raiseDefault],     LET(         raiseDefault, IF(             OR(ISOMITTED(raiseDefault), ISBLANK(raiseDefault)),             TRUE,             raiseDefault         ),         HSTACK(             TAKE(A, , 2),             IF(                 ROWS(A) = 1,                 IF(raiseDefault, INDEX(A, 1, 3) ^ power, INDEX(A, 1, 3)),                 VSTACK(                     IF(raiseDefault, INDEX(A, 1, 3) ^ power, INDEX(A, 1, 3)),                     DROP(TAKE(A, , -1), 1) ^ power                 )             )         )     ) );

/*
Name: SPARSESLICE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Slice a sparse matrix

Parameters:
matrix	Sparse matrix.
lin_start	First row.
lin_end	Last row.
col_start	First column.
col_end	Last column.

*/
SPARSESLICE=LAMBDA(matrix,[lin_start],[lin_end],[col_start],[col_end],     LET(         lin_start, IF(             OR(ISOMITTED(lin_start), lin_start = 0),             1,             IF(                 ISTEXT(lin_start),                 MATCH(lin_start, INDEX(matrix, 0, 1), 0),                 lin_start             )         ),         lin_end, IF(             OR(ISOMITTED(lin_end), lin_end = 0),             ROWS(matrix),             IF(                 ISTEXT(lin_end),                 MATCH(lin_end, INDEX(matrix, 0, 1), 0),                 lin_end             )         ),         col_start, IF(             OR(ISOMITTED(col_start), col_start = 0),             1,             IF(                 ISTEXT(col_start),                 MATCH(col_start, INDEX(matrix, 1, 0), 0),                 col_start             )         ),         col_end, IF(             OR(ISOMITTED(col_end), col_end = 0),             COLUMNS(matrix),             IF(                 ISTEXT(col_end),                 MATCH(col_end, INDEX(matrix, 1, 0), 0),                 col_end             )         ),         head, TAKE(matrix, 1),         hMatrix, DROP(matrix, 1),         rows, CHOOSECOLS(hMatrix, 1),         cols, CHOOSECOLS(hMatrix, 2),         vals, CHOOSECOLS(hMatrix, 3),         filtered, FILTER(             hMatrix,             (rows >= lin_start) * (rows <= lin_end) * (cols >= col_start) *                 (cols <= col_end),             NA()         ),         newRows, CHOOSECOLS(filtered, 1),         newCols, CHOOSECOLS(filtered, 2),         newVals, CHOOSECOLS(filtered, 3),         newHead, HSTACK(             lin_end - lin_start + 1,             col_end - col_start + 1,             INDEX(head, 1, 3)         ),         resized, HSTACK(             newRows - MIN(newRows) + 1,             newCols - MIN(newCols) + 1,             newVals         ),         IF(             ISERROR(INDEX(filtered, 1, 1)),             newHead,             VSTACK(newHead, resized)         )     ) );

/*
Name: SPARSET
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Returns the transpose of a sparse matrix as a sparse matrix

Parameters:

*/
SPARSET=LAMBDA(matrix, CHOOSECOLS(matrix, 2, 1, 3) );

/*
Name: SPARSEVSTACK
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Pile up two sparse matrices

Parameters:
A	The first sparse matrix.
B	The second sparse matrix.
[default]	Default element. If omitted, it is the default element of matrix A.

*/
SPARSEVSTACK=LAMBDA(A,B,[default],     LET(         default, IF(OR(ISOMITTED(default), ISBLANK(default)), INDEX(A, 1, 3), default),         defaultA, INDEX(A, 1, 3),         defaultB, INDEX(B, 1, 3),         A, IF(defaultA = default, A, SPARSE(FULL(A), default)),         B, IF(defaultB = default, B, SPARSE(FULL(B), default)),         headA, TAKE(A, 1),         headB, TAKE(B, 1),         nA, INDEX(A, 1, 1),         nB, INDEX(B, 1, 1),         mA, INDEX(A, 1, 2),         mB, INDEX(B, 1, 2),         headC, HSTACK(nA + nB, MAX(mA, mB), default),         hA, DROP(A, 1),         hB, DROP(B, 1),         hBshift, HSTACK(CHOOSECOLS(hB, 1) + nA, CHOOSECOLS(hB, 2, 3)),         VSTACK(headC, hA, hBshift)     ) );

/*
Name: SPARSEWISE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Run element-wise operations between two sparse matrices

Parameters:
A	The first sparse matrix.
B	The second sparse matrix.
[operation]	Name of the operation that will take place. It can be any eta Excel function or a lambda function that takes 1 argument.
[default]	Default element. If omitted, it is the default element of matrix A.

*/
SPARSEWISE=LAMBDA(a,B,[operation],[default], LET(default, IF(OR(ISOMITTED(default), ISBLANK(default)), INDEX(a, 1, 3), default), operation, IF(OR(ISOMITTED(operation), ISBLANK(operation)), SUM, operation), a, IF(default = INDEX(a, 1, 3), a, SPARSE(FULL(a), default)), B, IF(default = INDEX(B, 1, 3), B, SPARSE(FULL(B), default)), sameSize, SUM(1 * (INDEX(a, 1, {1,2}) = INDEX(B, 1, {1,2}))) = 2, IF(sameSize = FALSE, "# sparse matrices have different dimensions", LET(head, TAKE(a, 1), c, VSTACK(DROP(a, 1), DROP(B, 1)), d, GROUPBY(TAKE(c, , 2), INDEX(c, 0, 3), operation, FALSE, FALSE), VSTACK(head, d)))));

/*
Name: SPARSEZEROS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Returns a sparse matrix of zeros

Parameters:
n	Dimension of the matrix of zeros to be returned.
[m]	If provided, it sets the number of columns of the matrix. If omitted, it is n.
[default]	Default element. If omitted, it is 0.

*/
SPARSEZEROS=LAMBDA(n,[m],[default], LET( default, IF(OR(ISOMITTED(default), ISBLANK(default)), 0, default), m, IF(OR(ISOMITTED(m), ISBLANK(m)), n, m), nLins, MIN(n,m), head, HSTACK(n,m,default), A, IF(default <> 0, HSTACK(REPMAT(SEQUENCE(nLins),m,1,TRUE),REPMAT(SEQUENCE(m),nLins,1),ZEROS(nLins*m,1)), NA()), final, IF(ISERROR(INDEX(A,1,1)), head, VSTACK(head, A)), final ));

/*
Name: SPLIT
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
SPLIT=LAMBDA(txt,delim,[n],[list], LET( p,IFERROR(FIND(delim,txt),0), delimLength, LEN(delim), list,IF(ISOMITTED(list), {""}, list), IF(p,SPLIT(MID(txt,p+delimLength,9999999),delim,n+1, IF(SEQUENCE(1,n+1)<n+1,list,LEFT(txt,p-1))), IF(n=0,txt,IF(SEQUENCE(1,n+1)<n+1,list,txt)) ) ) );

/*
Name: SPLITALL
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
SPLITALL=LAMBDA(text,delim, LET( delim, VEC(delim), text, SUBSTITUTE(text, INDEX(delim,1,1),"</y><y>"), IF( LENGTH(delim) = 1, TRANSPOSE( FILTERXML("<x><y>"& text & "</y></x>", "//y") ), SPLITALL(text, SLICE(delim,2,-1)) ) ));

/*
Name: SPLITTWO
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
SPLITTWO=LAMBDA(txt,delim1,delim2,[returnedObject], LET( returnedObject, IF(ISOMITTED(returnedObject), "ELEMENTS", UPPER(returnedObject)), txtVec, TEXTTOCHAR(txt), posPlus, NOZERO( txtVec = delim1 ), posMin, NOZERO( txtVec = delim2 ), posAll, RMMISSING( SORT( VEC( HSTACK(posPlus,posMin) ) ) ), posStart,  IF(SUM(ISERR(posAll)*1), 1, VSTACK(1, posAll + 1 )), posEnd, IF( SUM(ISERR(posAll)*1), LEN(txt), VSTACK(posAll - 1, LEN(txt)) ), items, MID(txt,posStart,posEnd-posStart+1), itemsPlus, VSTACK(1, MAP(posAll, LAMBDA(val, SUM(1*(val=posPlus))))), itemsMin, 1*NOT(itemsPlus), SWITCH(returnedObject, "ELEMENTS", items, "DELIM1POS", itemsPlus, "DELIM2POS", itemsMin ) ) );

/*
Name: STARTSWITH
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Checks whether a string starts with a certain sequence of characters

Parameters:
text	The text whose first characters shall be checked
sequence	The sequence to be compared with the first characters of the text argument
[start_num]	The first character position from which the text shall be checked for the presence of the sequece. If omitted, it is 1 (first character).
[case_sensitive]	Boolean that indicates whether comparison shall be case sensitive. If omitted, it is FALSE.

*/
STARTSWITH=LAMBDA(text,sequence,[start_num],[case_sensitive], LET( start_num, IF(ISOMITTED(start_num), 1,start_num), case_sensistive, IF(ISOMITTED(case_sensistive), FALSE, case_sensitive), CONTAINS(LEFT(MID(text, start_num, LEN(text) - start_num + 1), LEN(sequence)), sequence, 1, case_sensistive) ));

/*
Name: STATIONARIZE
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Stationarize a vector

Parameters:
values	Vector of values
[automatic]	If FALSE, uses constant and trend parameters. If AIC or BIC picks the specification that minimizes that information criterion and ignores constant and trend parameters.
[nLags]	Maximum number of lags for the Augmented Dickey-Fuller test
[significance]	Significance
[constant]	Includes a constant in the test equation
[trend]	Includes a linear trend in the test. If omitted equals false. If true, constant must be true

*/
STATIONARIZE=LAMBDA(values,[automatic],[nLags],[significance],[constant],[trend],[maxDiffs], LET( automatic, IF(ISOMITTED(automatic), FALSE, automatic), nLags,IF(ISOMITTED(nLags), 0, nLags), significance, IF(ISOMITTED(significance), FALSE, significance), constant, IF(ISOMITTED(constant), FALSE, constant), trend, IF(ISOMITTED(trend), FALSE, trend ), maxDiffs, IF(OR( ISOMITTED(maxDiffs), ISBLANK(maxDiffs)), 2, maxDiffs ), IF(ISSTATIONARY(values, automatic, nLags, significance, constant, trend), values, IF(maxDiffs = 0, values, STATIONARIZE( DIFF(values,1,1), automatic, nLags, significance, constant, trend, maxDiffs - 1) )) ));

/*
Name: SUBS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Evaluates a formula expression after replacing variables with numbers (workbook file will have to be saved with the .xlsm extension)

Parameters:
s	formula to be avaluated
old	formula to be avaluated
new	formula to be avaluated

*/
SUBS=LAMBDA(s,old,new, EVALS(SUBSTITUTE(s,old,new)));

/*
Name: TEXTTOARRAY
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Convert a text that represents a matrix into an array

Parameters:
matrixText	The text that represents a matrix.
[lineChar]	The character that breaks the line of a matrix. If omitted, it is used Excel's locale character.
[colChar]	The character that breaks the column of a matrix. If omitted, it is used Excel's locale character.

*/
TEXTTOARRAY=LAMBDA(matrixText,[lineChar],[colChar], LET( lineChar, IF(ISOMITTED(lineChar), MID( ARRAYTOTEXT({1;1},1), 3, 1), lineChar), colChar, IF(ISOMITTED(colChar), MID( ARRAYTOTEXT({1,1},1), 3, 1), colChar), rows, TRANSPOSE( SPLIT( LET( matrix, matrixText, MID( matrix, 2, LEN(matrix) -2)), lineChar) ), actualValue, LAMBDA(value, LET(value,TRIM(value), IF( AND( LEFT(value,1) = """", RIGHT(value,1) = """" ),  MID(value, 2, LEN(value) - 2), VALUE(value) ) ) ), MAKEARRAY(LENGTH(rows), LENGTH( SPLIT( INDEX(rows,1,1), colChar ) ), LAMBDA(row,col,  actualValue( INDEX( SPLIT( INDEX(rows,row,1), colChar ), 1, col ) ) ) ) ));

/*
Name: TEXTTOCHAR
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
TEXTTOCHAR=LAMBDA(txt,MID(txt,SEQUENCE(LEN(txt)),1));

/*
Name: TRIL
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Returns the lower triangular part of a matrix

Parameters:
matrix	The matrix whose lower triangular part one wants to extract.
k	k=0 (default); k> or k<0 returns the elements on and below the kth diagonal of the matrix.

*/
TRIL=LAMBDA(matrix,[k],MAKEARRAY(ROWS(matrix),COLUMNS(matrix),LAMBDA(lin,col,col<=(lin+k)))*matrix);

/*
Name: TRIU
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Returns the upper triangular part of a matrix

Parameters:
matrix	The matrix whose upper triangular part one wants to extract.
k	k=0 (default); k> or k<0 returns the elements on and below the kth diagonal of the matrix.

*/
TRIU=LAMBDA(matrix,[k],MAKEARRAY(ROWS(matrix),COLUMNS(matrix),LAMBDA(lin,col,col>=(lin+k)))*matrix);

/*
Name: TSOPER
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Time series operation

Parameters:
dates	Vector of dates
values	Vector of values
operation	String with the operation to be applied to the time series. Operations available are: V%(n), VA(n), MA(n), LAG(n), LEAD(n), IDX(refDate), B100(refDate), CR(n), V%ACC(refDate), and VAACC(refDate). Successive operations are separated by ";".

*/
TSOPER=LAMBDA(dates,values,operation, IF(OR(ISOMITTED(operation), operation=""), values, LET( oper, UPPER(LEFT(operation, FIND("(", operation) - 1)), lag, VALUE(MID(operation, FIND("(", operation) + 1, -1 + FIND(")", operation) - FIND("(", operation) )), result, SWITCH(oper, "V%", VSTACK( SEQUENCE(lag)*NA(), DROP(values, lag) / DROP(values, -lag) - 1), "VA", VSTACK( SEQUENCE(lag)*NA(), DROP(values, lag) - DROP(values, -lag) ), "LAG", VSTACK( SEQUENCE(lag)*NA(), DROP(values, -lag) ), "LEAD", VSTACK( DROP(values, lag), SEQUENCE(lag)*NA() ), "MA", VSTACK( SEQUENCE(lag)*NA(), MAKEARRAY(ROWS(values), 1, LAMBDA(row,col, AVERAGE(INDEX(values, SEQUENCE(lag, 1,row-lag+1))) )) ), "CR", (1 + values)^lag - 1, "IDX", values / INDEX(values,  MATCH(lag, dates, 0)), "B100", 100 * values / INDEX(values,  MATCH(lag, dates, 0)), "V%ACC", values / INDEX(values,  MATCH(lag, dates, 0)) - 1, "VAACC", values - INDEX(values,  MATCH(lag, dates, 0)), ), IF( ISERROR( FIND(";", operation) ), result, TSOPER(dates, result, MID(operation, FIND(";", operation) + 1, 999999999)) ) )));

/*
Name: TSTREND
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Trend of a time series

Parameters:
values	Vector of values
nPower	Power of the trend; if omitted equals 1

*/
TSTREND=LAMBDA(values,[nPower], LET(nPower, IF(ISOMITTED(nPower), 1, nPower),  OLS_FITTED(values, ADDCONST(SEQUENCE(ROWS(values))^nPower)) ) );

/*
Name: VARINFLATIONFACTOR
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Calculates the variance inflation factor of a linear regression

Parameters:
y	Explained variable.
X	Explanatory variables.
[returnAllFactors]	Boolean. If FALSE or omitted, returns a single variance inflation factor. If TRUE, returns the variance inflation factor of all possible regressions among y and X variables in columns order [y X].

*/
VARINFLATIONFACTOR=LAMBDA(y,x,[returnAllFactors], LET( returnAllFactors,IF(ISOMITTED(returnAllFactors), FALSE, returnAllFactors), IF(returnAllFactors, LET( x, HSTACK(y,x), MAP( SEQUENCE(COLUMNS(x),1),  LAMBDA(iVar, 1/(1 - OLS_R2(INDEX(x,,iVar), ADDCONST(SLICE(x,,,iVar,iVar,,TRUE) ) )) )) ), 1/(1 - OLS_R2(y,ADDCONST(x))) )));

/*
Name: VEC
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Turns an array into a vector by piling up column or row values

Parameters:
matrix	The matrix that will be turned into a vector.
[rowsToCols]	Boolean that tells whether individual rows should be transposed before reshape. Default is FALSE.

*/
VEC=LAMBDA(matrix,[rowsToCols], LET( rowsToCols, IF(ISOMITTED(rowsToCols),FALSE,rowsToCols), matrix, IF(rowsToCols, matrix, TRANSPOSE(matrix)), INDEX(matrix, ROUNDUP(SEQUENCE(ROWS(matrix)*COLUMNS(matrix))/COLUMNS(matrix),0), MOD(SEQUENCE(ROWS(matrix)*COLUMNS(matrix))-1,COLUMNS(matrix))+1 )) );

/*
Name: VERTCAT
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
VERTCAT=LAMBDA(matrixA,matrixB, LET( LinsA, IFERROR(ROWS(matrixA), 1), LinsB, IFERROR(ROWS(matrixB), 1), ColsA, IFERROR(COLUMNS(matrixA), 1), ColsB, IFERROR(COLUMNS(matrixB), 1), DataA, INDEX(matrixA, SEQUENCE(LinsA), SEQUENCE(1,MAX(ColsA,ColsB)) ), DataB, INDEX(matrixB, SEQUENCE(LinsB), SEQUENCE(1,MAX(ColsA,ColsB)) ), NumCols, COLUMNS(DataA), SeqCol, SEQUENCE( LinsA + LinsB ), SeqRow, SEQUENCE( 1, NumCols), IFS(ISOMITTED(matrixA), matrixB, ISOMITTED(matrixB), matrixA, TRUE, IF( SeqCol <= LinsA, INDEX( DataA, SeqCol, SeqRow ), INDEX( DataB, SeqCol - LinsA, SeqRow ) ) ) ) );

/*
Name: VERTCAT_EX
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX

*/
VERTCAT_EX=LAMBDA(matrixA,matrixB,excludeValue, LET( testA, ALL(matrixA=excludeValue), testB, ALL(matrixB=excludeValue), IFS(AND(testA,testB), NA(), testA, matrixB, testB, matrixA, TRUE, VSTACK(matrixA,matrixB)) ));

/*
Name: ZEROS
Author: Eduardo G. C. Amaral
Source: https://github.com/edugca/xlMATRIX
Description: Returns a matrix of zeros

Parameters:
n	Dimension of the matrix of zeros to be returned.
[m]	If provided, it sets the number of columns of the matrix. If omitted, it is n.

*/
ZEROS=LAMBDA(N,[m], LET(m, IF(OR(ISOMITTED(m),ISBLANK(m)),N,m), EXPAND(0, N, m, 0)) );