Last active
May 3, 2024 23:56
-
-
Save edugca/5cf477673b7d8c76431ed9f52c09f7e7 to your computer and use it in GitHub Desktop.
xlMATRIX
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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)) ); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment