diff options
| -rw-r--r-- | calc.go | 142 | ||||
| -rw-r--r-- | calc_test.go | 36 |
2 files changed, 178 insertions, 0 deletions
@@ -130,6 +130,40 @@ var ( regexp.MustCompile(`^` + df3 + `$`), regexp.MustCompile(`^` + df4 + `$`), } + addressFmtMaps = map[string]func(col, row int) (string, error){ + "1_TRUE": func(col, row int) (string, error) { + return CoordinatesToCellName(col, row, true) + }, + "1_FALSE": func(col, row int) (string, error) { + return fmt.Sprintf("R%dC%d", row, col), nil + }, + "2_TRUE": func(col, row int) (string, error) { + column, err := ColumnNumberToName(col) + if err != nil { + return "", err + } + return fmt.Sprintf("%s$%d", column, row), nil + }, + "2_FALSE": func(col, row int) (string, error) { + return fmt.Sprintf("R%dC[%d]", row, col), nil + }, + "3_TRUE": func(col, row int) (string, error) { + column, err := ColumnNumberToName(col) + if err != nil { + return "", err + } + return fmt.Sprintf("$%s%d", column, row), nil + }, + "3_FALSE": func(col, row int) (string, error) { + return fmt.Sprintf("R[%d]C%d", row, col), nil + }, + "4_TRUE": func(col, row int) (string, error) { + return CoordinatesToCellName(col, row, false) + }, + "4_FALSE": func(col, row int) (string, error) { + return fmt.Sprintf("R[%d]C[%d]", row, col), nil + }, + } ) // cellRef defines the structure of a cell reference. @@ -266,6 +300,7 @@ type formulaFuncs struct { // ACOSH // ACOT // ACOTH +// ADDRESS // AMORDEGRC // AMORLINC // AND @@ -457,6 +492,7 @@ type formulaFuncs struct { // POISSON // POWER // PPMT +// PRICEDISC // PRODUCT // PROPER // QUARTILE @@ -7644,6 +7680,61 @@ func (fn *formulaFuncs) IF(argsList *list.List) formulaArg { // Lookup and Reference Functions +// ADDRESS function takes a row and a column number and returns a cell +// reference as a text string. The syntax of the function is: +// +// ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text]) +// +func (fn *formulaFuncs) ADDRESS(argsList *list.List) formulaArg { + if argsList.Len() < 2 { + return newErrorFormulaArg(formulaErrorVALUE, "ADDRESS requires at least 2 arguments") + } + if argsList.Len() > 5 { + return newErrorFormulaArg(formulaErrorVALUE, "ADDRESS requires at most 5 arguments") + } + rowNum := argsList.Front().Value.(formulaArg).ToNumber() + if rowNum.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + if rowNum.Number >= TotalRows { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + colNum := argsList.Front().Next().Value.(formulaArg).ToNumber() + if colNum.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + absNum := newNumberFormulaArg(1) + if argsList.Len() >= 3 { + absNum = argsList.Front().Next().Next().Value.(formulaArg).ToNumber() + if absNum.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + } + if absNum.Number < 1 || absNum.Number > 4 { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + a1 := newBoolFormulaArg(true) + if argsList.Len() >= 4 { + a1 = argsList.Front().Next().Next().Next().Value.(formulaArg).ToBool() + if a1.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + } + var sheetText string + if argsList.Len() == 5 { + sheetText = trimSheetName(argsList.Back().Value.(formulaArg).Value()) + } + if len(sheetText) > 0 { + sheetText = fmt.Sprintf("%s!", sheetText) + } + formatter := addressFmtMaps[fmt.Sprintf("%d_%s", int(absNum.Number), a1.Value())] + addr, err := formatter(int(colNum.Number), int(colNum.Number)) + if err != nil { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + return newStringFormulaArg(fmt.Sprintf("%s%s", sheetText, addr)) +} + // CHOOSE function returns a value from an array, that corresponds to a // supplied index number (position). The syntax of the function is: // @@ -9425,6 +9516,57 @@ func (fn *formulaFuncs) PPMT(argsList *list.List) formulaArg { return fn.ipmt("PPMT", argsList) } +// PRICEDISC function calculates the price, per $100 face value of a +// discounted security. The syntax of the function is: +// +// PRICEDISC(settlement,maturity,discount,redemption,[basis]) +// +func (fn *formulaFuncs) PRICEDISC(argsList *list.List) formulaArg { + if argsList.Len() != 4 && argsList.Len() != 5 { + return newErrorFormulaArg(formulaErrorVALUE, "PRICEDISC requires 4 or 5 arguments") + } + args := list.New().Init() + args.PushBack(argsList.Front().Value.(formulaArg)) + settlement := fn.DATEVALUE(args) + if settlement.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + args.Init() + args.PushBack(argsList.Front().Next().Value.(formulaArg)) + maturity := fn.DATEVALUE(args) + if maturity.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + if maturity.Number <= settlement.Number { + return newErrorFormulaArg(formulaErrorNUM, "PRICEDISC requires maturity > settlement") + } + discount := argsList.Front().Next().Next().Value.(formulaArg).ToNumber() + if discount.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + if discount.Number <= 0 { + return newErrorFormulaArg(formulaErrorNUM, "PRICEDISC requires discount > 0") + } + redemption := argsList.Front().Next().Next().Next().Value.(formulaArg).ToNumber() + if redemption.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + if redemption.Number <= 0 { + return newErrorFormulaArg(formulaErrorNUM, "PRICEDISC requires redemption > 0") + } + basis := newNumberFormulaArg(0) + if argsList.Len() == 5 { + if basis = argsList.Back().Value.(formulaArg).ToNumber(); basis.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + } + frac := yearFrac(settlement.Number, maturity.Number, int(basis.Number)) + if frac.Type != ArgNumber { + return frac + } + return newNumberFormulaArg(redemption.Number * (1 - discount.Number*frac.Number)) +} + // RRI function calculates the equivalent interest rate for an investment with // specified present value, future value and duration. The syntax of the // function is: diff --git a/calc_test.go b/calc_test.go index 7262fa9..38b5f5f 100644 --- a/calc_test.go +++ b/calc_test.go @@ -1188,6 +1188,17 @@ func TestCalcCellValue(t *testing.T) { `=IF(FALSE,0,ROUND(4/2,0))`: "2", `=IF(TRUE,ROUND(4/2,0),0)`: "2", // Excel Lookup and Reference Functions + // ADDRESS + "=ADDRESS(1,1,1,TRUE)": "$A$1", + "=ADDRESS(1,1,1,FALSE)": "R1C1", + "=ADDRESS(1,1,2,TRUE)": "A$1", + "=ADDRESS(1,1,2,FALSE)": "R1C[1]", + "=ADDRESS(1,1,3,TRUE)": "$A1", + "=ADDRESS(1,1,3,FALSE)": "R[1]C1", + "=ADDRESS(1,1,4,TRUE)": "A1", + "=ADDRESS(1,1,4,FALSE)": "R[1]C[1]", + "=ADDRESS(1,1,4,TRUE,\"\")": "A1", + "=ADDRESS(1,1,4,TRUE,\"Sheet1\")": "Sheet1!A1", // CHOOSE "=CHOOSE(4,\"red\",\"blue\",\"green\",\"brown\")": "brown", "=CHOOSE(1,\"red\",\"blue\",\"green\",\"brown\")": "red", @@ -1327,6 +1338,9 @@ func TestCalcCellValue(t *testing.T) { // PPMT "=PPMT(0.05/12,2,60,50000)": "-738.2918003208238", "=PPMT(0.035/4,2,8,0,5000,1)": "-606.1094824182949", + // PRICEDISC + "=PRICEDISC(\"04/01/2017\",\"03/31/2021\",2.5%,100)": "90", + "=PRICEDISC(\"04/01/2017\",\"03/31/2021\",2.5%,100,3)": "90", // RRI "=RRI(10,10000,15000)": "0.0413797439924106", // SLN @@ -2279,6 +2293,17 @@ func TestCalcCellValue(t *testing.T) { "=IF(0,1,2,3)": "IF accepts at most 3 arguments", "=IF(D1,1,2)": "strconv.ParseBool: parsing \"Month\": invalid syntax", // Excel Lookup and Reference Functions + // ADDRESS + "=ADDRESS()": "ADDRESS requires at least 2 arguments", + "=ADDRESS(1,1,1,TRUE,\"Sheet1\",0)": "ADDRESS requires at most 5 arguments", + "=ADDRESS(\"\",1,1,TRUE)": "#VALUE!", + "=ADDRESS(1,\"\",1,TRUE)": "#VALUE!", + "=ADDRESS(1,1,\"\",TRUE)": "#VALUE!", + "=ADDRESS(1,1,1,\"\")": "#VALUE!", + "=ADDRESS(1,1,0,TRUE)": "#NUM!", + "=ADDRESS(1,16385,2,TRUE)": "#VALUE!", + "=ADDRESS(1,16385,3,TRUE)": "#VALUE!", + "=ADDRESS(1048576,1,1,TRUE)": "#VALUE!", // CHOOSE "=CHOOSE()": "CHOOSE requires 2 arguments", "=CHOOSE(\"index_num\",0)": "CHOOSE requires first argument of type number", @@ -2549,6 +2574,17 @@ func TestCalcCellValue(t *testing.T) { "=PPMT(0,0,0,\"\",0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", "=PPMT(0,0,0,0,\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax", "=PPMT(0,0,0,0,0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + // PRICEDISC + "=PRICEDISC()": "PRICEDISC requires 4 or 5 arguments", + "=PRICEDISC(\"\",\"03/31/2021\",95,100)": "#VALUE!", + "=PRICEDISC(\"04/01/2016\",\"\",95,100)": "#VALUE!", + "=PRICEDISC(\"04/01/2016\",\"03/31/2021\",\"\",100)": "#VALUE!", + "=PRICEDISC(\"04/01/2016\",\"03/31/2021\",95,\"\")": "#VALUE!", + "=PRICEDISC(\"04/01/2016\",\"03/31/2021\",95,100,\"\")": "#NUM!", + "=PRICEDISC(\"03/31/2021\",\"04/01/2016\",95,100)": "PRICEDISC requires maturity > settlement", + "=PRICEDISC(\"04/01/2016\",\"03/31/2021\",0,100)": "PRICEDISC requires discount > 0", + "=PRICEDISC(\"04/01/2016\",\"03/31/2021\",95,0)": "PRICEDISC requires redemption > 0", + "=PRICEDISC(\"04/01/2016\",\"03/31/2021\",95,100,5)": "invalid basis", // RRI "=RRI()": "RRI requires 3 arguments", "=RRI(\"\",\"\",\"\")": "#NUM!", |