convertExcelFormula

Syntax

convertExcelFormula(formula, colStart, colEnd, rowStart, rowEnd)

Arguments

formula is a string scalar/vector indicating an Excel formula.

colStart is a string scalar indicating the starting column of the data in Excel.

colEnd is a string scalar indicating the ending column of the data in Excel.

rowStart is a positive integer scalar indicating the starting row of the data in Excel.

rowEnd is an integer scalar indicating the ending row of the data in Excel. rowEnd must be greater or equal to rowStart.

Details

Convert Excel formula to DolphinDB expressions.

Currently, the function only supports the following conversions: the four arithmetic operations, logical operations, and aggregate functions.

Currently, it doesn’t support converting the expressions that operate on both rows and columns.

When applying an aggregate function on a single column, the aggregation will be performed on this column if the processing row number is equal to the actual row number. Otherwise, the aggregation will be performed on a moving window.

Examples

$ convertExcelFormula("A2+B2", "A", "Z", 2, 10);
col0+col1

$ convertExcelFormula("SUM(A2:C2)", "A", "Z", 2, 10);
rowSum(col0, col1, col2)

$ convertExcelFormula("SUM(A2)", "A", "Z", 2, 10);
cumsum(col0)

$ convertExcelFormula("SUM(A2:A5)", "A", "Z", 2, 10);
msum(col0, 4)

$ convertExcelFormula("SUM(A2:A10)", "A", "Z", 2, 10);
sum(col0)

$ convertExcelFormula(["=SUM(A1:A10)","IF(A1>0,B1,0"], "A", "D", 1, 10)
["sum(col0)","iif(col0>0,col1,0)"]