summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--calc.go138
-rw-r--r--calc_test.go26
2 files changed, 146 insertions, 18 deletions
diff --git a/calc.go b/calc.go
index 97ba373..7d27cb8 100644
--- a/calc.go
+++ b/calc.go
@@ -296,6 +296,7 @@ type formulaFuncs struct {
// Supported formula functions:
//
// ABS
+// ACCRINT
// ACCRINTM
// ACOS
// ACOSH
@@ -439,6 +440,7 @@ type formulaFuncs struct {
// ISODD
// ISTEXT
// ISO.CEILING
+// ISOWEEKNUM
// ISPMT
// KURT
// LARGE
@@ -7025,6 +7027,39 @@ func (fn *formulaFuncs) DAYS(argsList *list.List) formulaArg {
return newNumberFormulaArg(end - start)
}
+// ISOWEEKNUM function returns the ISO week number of a supplied date. The
+// syntax of the function is:
+//
+// ISOWEEKNUM(date)
+//
+func (fn *formulaFuncs) ISOWEEKNUM(argsList *list.List) formulaArg {
+ if argsList.Len() != 1 {
+ return newErrorFormulaArg(formulaErrorVALUE, "ISOWEEKNUM requires 1 argument")
+ }
+ date := argsList.Front().Value.(formulaArg)
+ num := date.ToNumber()
+ weeknum := 0
+ if num.Type != ArgNumber {
+ dateString := strings.ToLower(date.Value())
+ if !isDateOnlyFmt(dateString) {
+ if _, _, _, _, _, err := strToTime(dateString); err.Type == ArgError {
+ return err
+ }
+ }
+ y, m, d, _, err := strToDate(dateString)
+ if err.Type == ArgError {
+ return err
+ }
+ _, weeknum = time.Date(y, time.Month(m), d, 0, 0, 0, 0, time.UTC).ISOWeek()
+ } else {
+ if num.Number < 0 {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ _, weeknum = timeFromExcelTime(num.Number, false).ISOWeek()
+ }
+ return newNumberFormulaArg(float64(weeknum))
+}
+
// MONTH function returns the month of a date represented by a serial number.
// The month is given as an integer, ranging from 1 (January) to 12
// (December). The syntax of the function is:
@@ -7317,7 +7352,6 @@ func (fn *formulaFuncs) WEEKDAY(argsList *list.List) formulaArg {
if argsList.Len() > 2 {
return newErrorFormulaArg(formulaErrorVALUE, "WEEKDAY allows at most 2 arguments")
}
-
sn := argsList.Front().Value.(formulaArg)
num := sn.ToNumber()
weekday, returnType := 0, 1
@@ -8915,6 +8949,69 @@ func (fn *formulaFuncs) ENCODEURL(argsList *list.List) formulaArg {
// Financial Functions
+// validateFrequency check the number of coupon payments per year if be equal to 1, 2 or 4.
+func validateFrequency(freq float64) bool {
+ return freq == 1 || freq == 2 || freq == 4
+}
+
+// ACCRINT function returns the accrued interest for a security that pays
+// periodic interest. The syntax of the function is:
+//
+// ACCRINT(issue,first_interest,settlement,rate,par,frequency,[basis],[calc_method])
+//
+func (fn *formulaFuncs) ACCRINT(argsList *list.List) formulaArg {
+ if argsList.Len() < 6 {
+ return newErrorFormulaArg(formulaErrorVALUE, "ACCRINT requires at least 6 arguments")
+ }
+ if argsList.Len() > 8 {
+ return newErrorFormulaArg(formulaErrorVALUE, "ACCRINT allows at most 8 arguments")
+ }
+ args := list.New().Init()
+ args.PushBack(argsList.Front().Value.(formulaArg))
+ issue := fn.DATEVALUE(args)
+ if issue.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+ }
+ args.Init()
+ args.PushBack(argsList.Front().Next().Value.(formulaArg))
+ fi := fn.DATEVALUE(args)
+ if fi.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+ }
+ args.Init()
+ args.PushBack(argsList.Front().Next().Next().Value.(formulaArg))
+ settlement := fn.DATEVALUE(args)
+ if settlement.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+ }
+ rate := argsList.Front().Next().Next().Next().Value.(formulaArg).ToNumber()
+ par := argsList.Front().Next().Next().Next().Next().Value.(formulaArg).ToNumber()
+ frequency := argsList.Front().Next().Next().Next().Next().Next().Value.(formulaArg).ToNumber()
+ if rate.Type != ArgNumber || par.Type != ArgNumber || frequency.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ if !validateFrequency(frequency.Number) {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ basis := newNumberFormulaArg(0)
+ if argsList.Len() >= 7 {
+ if basis = argsList.Front().Next().Next().Next().Next().Next().Next().Value.(formulaArg).ToNumber(); basis.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ }
+ cm := newBoolFormulaArg(true)
+ if argsList.Len() == 8 {
+ if cm = argsList.Back().Value.(formulaArg).ToBool(); cm.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+ }
+ }
+ frac1 := yearFrac(issue.Number, settlement.Number, int(basis.Number))
+ if frac1.Type != ArgNumber {
+ return frac1
+ }
+ return newNumberFormulaArg(par.Number * rate.Number * frac1.Number)
+}
+
// ACCRINTM function returns the accrued interest for a security that pays
// interest at maturity. The syntax of the function is:
//
@@ -10023,6 +10120,27 @@ func (fn *formulaFuncs) PV(argsList *list.List) formulaArg {
return newNumberFormulaArg((((1-math.Pow(1+rate.Number, nper.Number))/rate.Number)*pmt.Number*(1+rate.Number*t.Number) - fv.Number) / math.Pow(1+rate.Number, nper.Number))
}
+// rate is an implementation of the formula function RATE.
+func (fn *formulaFuncs) rate(nper, pmt, pv, fv, t, guess formulaArg, argsList *list.List) formulaArg {
+ maxIter, iter, close, epsMax, rate := 100, 0, false, 1e-6, guess.Number
+ for iter < maxIter && !close {
+ t1 := math.Pow(rate+1, nper.Number)
+ t2 := math.Pow(rate+1, nper.Number-1)
+ rt := rate*t.Number + 1
+ p0 := pmt.Number * (t1 - 1)
+ f1 := fv.Number + t1*pv.Number + p0*rt/rate
+ f2 := nper.Number*t2*pv.Number - p0*rt/math.Pow(rate, 2)
+ f3 := (nper.Number*pmt.Number*t2*rt + p0*t.Number) / rate
+ delta := f1 / (f2 + f3)
+ if math.Abs(delta) < epsMax {
+ close = true
+ }
+ iter++
+ rate -= delta
+ }
+ return newNumberFormulaArg(rate)
+}
+
// RATE function calculates the interest rate required to pay off a specified
// amount of a loan, or to reach a target amount on an investment, over a
// given period. The syntax of the function is:
@@ -10069,23 +10187,7 @@ func (fn *formulaFuncs) RATE(argsList *list.List) formulaArg {
return guess
}
}
- maxIter, iter, close, epsMax, rate := 100, 0, false, 1e-6, guess.Number
- for iter < maxIter && !close {
- t1 := math.Pow(rate+1, nper.Number)
- t2 := math.Pow(rate+1, nper.Number-1)
- rt := rate*t.Number + 1
- p0 := pmt.Number * (t1 - 1)
- f1 := fv.Number + t1*pv.Number + p0*rt/rate
- f2 := nper.Number*t2*pv.Number - p0*rt/math.Pow(rate, 2)
- f3 := (nper.Number*pmt.Number*t2*rt + p0*t.Number) / rate
- delta := f1 / (f2 + f3)
- if math.Abs(delta) < epsMax {
- close = true
- }
- iter++
- rate -= delta
- }
- return newNumberFormulaArg(rate)
+ return fn.rate(nper, pmt, pv, fv, t, guess, argsList)
}
// RECEIVED function calculates the amount received at maturity for a fully
diff --git a/calc_test.go b/calc_test.go
index 6d46154..b241db8 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -1028,6 +1028,11 @@ func TestCalcCellValue(t *testing.T) {
"=DAYS(2,1)": "1",
"=DAYS(INT(2),INT(1))": "1",
"=DAYS(\"02/02/2015\",\"01/01/2015\")": "32",
+ // ISOWEEKNUM
+ "=ISOWEEKNUM(42370)": "53",
+ "=ISOWEEKNUM(\"42370\")": "53",
+ "=ISOWEEKNUM(\"01/01/2005\")": "53",
+ "=ISOWEEKNUM(\"02/02/2005\")": "5",
// MONTH
"=MONTH(42171)": "6",
"=MONTH(\"31-May-2015\")": "5",
@@ -1315,6 +1320,9 @@ func TestCalcCellValue(t *testing.T) {
// ENCODEURL
"=ENCODEURL(\"https://xuri.me/excelize/en/?q=Save As\")": "https%3A%2F%2Fxuri.me%2Fexcelize%2Fen%2F%3Fq%3DSave%20As",
// Financial Functions
+ // ACCRINT
+ "=ACCRINT(\"01/01/2012\",\"04/01/2012\",\"12/31/2013\",8%,10000,4,0,TRUE)": "1600",
+ "=ACCRINT(\"01/01/2012\",\"04/01/2012\",\"12/31/2013\",8%,10000,4,0,FALSE)": "1600",
// ACCRINTM
"=ACCRINTM(\"01/01/2012\",\"12/31/2012\",8%,10000)": "800",
"=ACCRINTM(\"01/01/2012\",\"12/31/2012\",8%,10000,3)": "800",
@@ -2262,6 +2270,11 @@ func TestCalcCellValue(t *testing.T) {
"=DAYS(0,\"\")": "#VALUE!",
"=DAYS(NA(),0)": "#VALUE!",
"=DAYS(0,NA())": "#VALUE!",
+ // ISOWEEKNUM
+ "=ISOWEEKNUM()": "ISOWEEKNUM requires 1 argument",
+ "=ISOWEEKNUM(\"\")": "#VALUE!",
+ "=ISOWEEKNUM(\"January 25, 100\")": "#VALUE!",
+ "=ISOWEEKNUM(-1)": "#NUM!",
// MONTH
"=MONTH()": "MONTH requires exactly 1 argument",
"=MONTH(0,0)": "MONTH requires exactly 1 argument",
@@ -2505,6 +2518,19 @@ func TestCalcCellValue(t *testing.T) {
// ENCODEURL
"=ENCODEURL()": "ENCODEURL requires 1 argument",
// Financial Functions
+ // ACCRINT
+ "=ACCRINT()": "ACCRINT requires at least 6 arguments",
+ "=ACCRINT(\"01/01/2012\",\"04/01/2012\",\"12/31/2013\",8%,10000,4,1,FALSE,0)": "ACCRINT allows at most 8 arguments",
+ "=ACCRINT(\"\",\"04/01/2012\",\"12/31/2013\",8%,10000,4,1,FALSE)": "#VALUE!",
+ "=ACCRINT(\"01/01/2012\",\"\",\"12/31/2013\",8%,10000,4,1,FALSE)": "#VALUE!",
+ "=ACCRINT(\"01/01/2012\",\"04/01/2012\",\"\",8%,10000,4,1,FALSE)": "#VALUE!",
+ "=ACCRINT(\"01/01/2012\",\"04/01/2012\",\"12/31/2013\",\"\",10000,4,1,FALSE)": "#NUM!",
+ "=ACCRINT(\"01/01/2012\",\"04/01/2012\",\"12/31/2013\",8%,\"\",4,1,FALSE)": "#NUM!",
+ "=ACCRINT(\"01/01/2012\",\"04/01/2012\",\"12/31/2013\",8%,10000,3)": "#NUM!",
+ "=ACCRINT(\"01/01/2012\",\"04/01/2012\",\"12/31/2013\",8%,10000,\"\",1,FALSE)": "#NUM!",
+ "=ACCRINT(\"01/01/2012\",\"04/01/2012\",\"12/31/2013\",8%,10000,4,\"\",FALSE)": "#NUM!",
+ "=ACCRINT(\"01/01/2012\",\"04/01/2012\",\"12/31/2013\",8%,10000,4,1,\"\")": "#VALUE!",
+ "=ACCRINT(\"01/01/2012\",\"04/01/2012\",\"12/31/2013\",8%,10000,4,5,FALSE)": "invalid basis",
// ACCRINTM
"=ACCRINTM()": "ACCRINTM requires 4 or 5 arguments",
"=ACCRINTM(\"\",\"01/01/2012\",8%,10000)": "#VALUE!",
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-19 18:17:23 +0000