public void testSYD() {
assertEquals(300.0, Vba.sYD(1000, 100, 5, 5));
assertEquals(240.0, Vba.sYD(1000, 100, 4, 5));
assertEquals(180.0, Vba.sYD(1000, 100, 3, 5));
assertEquals(120.0, Vba.sYD(1000, 100, 2, 5));
assertEquals(60.0, Vba.sYD(1000, 100, 1, 5));
}
public void testReplace6() {
// compare is currently ignored
assertEquals("azaz", Vba.replace("xyzxyz", "xy", "a", 1, -1, 1000));
assertEquals("azxyz", Vba.replace("xyzxyz", "xy", "a", 1, 1, 0));
assertEquals("azaz", Vba.replace("xyzxyz", "xy", "a", 1, 2, -6));
assertEquals("xyzazxyz", Vba.replace("xyzxyzxyz", "xy", "a", 2, 1, 11));
}
public void testNpv() {
double r, v[], npv, x;
r = 1;
v = new double[] {100, 200, 300, 400};
npv = Vba.nPV(r, v);
x = 162.5;
assertEquals(x, npv);
r = 2.5;
v = new double[] {1000, 666.66666, 333.33, 12.2768416};
npv = Vba.nPV(r, v);
x = 347.99232604144827;
assertEquals(x, npv, SMALL);
r = 12.33333;
v = new double[] {1000, 0, -900, -7777.5765};
npv = Vba.nPV(r, v);
x = 74.3742433377061;
assertEquals(x, npv, 1e-12);
r = 0.05;
v = new double[] {200000, 300000.55, 400000, 1000000, 6000000, 7000000, -300000};
npv = Vba.nPV(r, v);
x = 11342283.4233124;
assertEquals(x, npv, 1e-8);
}
public void testStrReverse() {
// odd length
assertEquals("cba", Vba.strReverse("abc"));
// even length
assertEquals("wxyz", Vba.strReverse("zyxw"));
// zero length
assertEquals("", Vba.strReverse(""));
}
public void testReplace4() {
assertEquals("azaz", Vba.replace("xyzxyz", "xy", "a", 1));
assertEquals("xyzaz", Vba.replace("xyzxyz", "xy", "a", 2));
assertEquals("xyzxyz", Vba.replace("xyzxyz", "xy", "a", 30));
// spec doesn't say, but assume starting before start of string is ok
assertEquals("azaz", Vba.replace("xyzxyz", "xy", "a", 0));
assertEquals("azaz", Vba.replace("xyzxyz", "xy", "a", -5));
}
public void testMIRR() {
double vals[] = {-1000, 50, 50, 50, 50, 50, 1050};
assertTrue(Math.abs(0.05 - Vba.MIRR(vals, 0.05, 0.05)) < 0.0000001);
vals = new double[] {-1000, 200, 200, 200, 200, 200, 200};
assertTrue(Math.abs(0.05263266 - Vba.MIRR(vals, 0.05, 0.05)) < 0.0000001);
vals = new double[] {-1000, 200, 200, 200, 200, 200, 200};
assertTrue(Math.abs(0.04490701 - Vba.MIRR(vals, 0.06, 0.04)) < 0.0000001);
}
public void testInStrRev() {
assertEquals(32, Vba.inStrRev("the quick brown fox jumps over the lazy dog", "the"));
assertEquals(1, Vba.inStrRev("the quick brown fox jumps over the lazy dog", "the", 16));
try {
Vba.inStrRev("the quick brown fox jumps over the lazy dog", "the", 0);
fail();
} catch (InvalidArgumentException e) {
assertTrue(e.getMessage().indexOf("-1 or a location") >= 0);
}
}
public void testSpace() {
assertEquals(" ", Vba.space(3));
assertEquals("", Vba.space(0));
try {
String s = Vba.space(-2);
fail("expected error, got " + s);
} catch (RuntimeException e) {
assertMessage(e, "NegativeArraySizeException");
}
}
public void testAscB() {
assertEquals(0x61, Vba.ascB("abc"));
assertEquals(0x34, Vba.ascB("\u1234abc")); // not sure about this
try {
Object o = Vba.ascB("");
fail("expected error, got " + o);
} catch (RuntimeException e) {
assertMessage(e, "StringIndexOutOfBoundsException");
}
}
public void testString() {
assertEquals("xxx", Vba.string(3, 'x'));
assertEquals("", Vba.string(0, 'y'));
try {
String s = Vba.string(-2, 'z');
fail("expected error, got " + s);
} catch (RuntimeException e) {
assertMessage(e, "NegativeArraySizeException");
}
assertEquals("", Vba.string(100, '\0'));
}
public void testAscW() {
// ascW behaves identically to asc
assertEquals(0x61, Vba.ascW("abc"));
assertEquals(0x1234, Vba.ascW("\u1234abc"));
try {
Object o = Vba.ascW("");
fail("expected error, got " + o);
} catch (RuntimeException e) {
assertMessage(e, "StringIndexOutOfBoundsException");
}
}
public void testMonthName() {
assertEquals("January", Vba.monthName(1, false));
assertEquals("Jan", Vba.monthName(1, true));
assertEquals("Dec", Vba.monthName(12, true));
try {
String s = Vba.monthName(0, true);
fail("expected error, got " + s);
} catch (RuntimeException e) {
assertMessage(e, "ArrayIndexOutOfBoundsException");
}
}
public void testReplace3() {
// replace with longer string
assertEquals("abczabcz", Vba.replace("xyzxyz", "xy", "abc"));
// replace with shorter string
assertEquals("wazwaz", Vba.replace("wxyzwxyz", "xy", "a"));
// replace with string which contains seek
assertEquals("wxyz", Vba.replace("xyz", "xy", "wxy"));
// replace with string which combines with following char to make seek
assertEquals("wxyzwx", Vba.replace("xyyzxy", "xy", "wx"));
// replace with empty string
assertEquals("wxyza", Vba.replace("wxxyyzxya", "xy", ""));
}
public void testNper() {
double f, r, y, p, x, n;
boolean t;
r = 0;
y = 7;
p = 2;
f = 3;
t = false;
n = Vba.nPer(r, y, p, f, t);
// can you believe it? excel returns nper as a fraction!??
x = -0.71428571429;
assertEquals(x, n, 1e-10);
// cross check with pv
r = 1;
y = 100;
p = -109.66796875;
f = 10000;
t = false;
n = Vba.nPer(r, y, p, f, t);
x = 10;
assertEquals(x, n, 1e-12);
r = 1;
y = 100;
p = -209.5703125;
f = 10000;
t = true;
n = Vba.nPer(r, y, p, f, t);
x = 10;
assertEquals(x, n, 1e-14);
// cross check with fv
r = 2;
y = 120;
f = -6409178400d;
p = 12000;
t = false;
n = Vba.nPer(r, y, p, f, t);
x = 12;
assertEquals(x, n, SMALL);
r = 2;
y = 120;
f = -6472951200d;
p = 12000;
t = true;
n = Vba.nPer(r, y, p, f, t);
x = 12;
assertEquals(x, n, SMALL);
}
public void testPmt() {
double f, r, y, p, x;
int n;
boolean t;
r = 0;
n = 3;
p = 2;
f = 7;
t = true;
y = Vba.pmt(r, n, p, f, t);
x = -3;
assertEquals(x, y);
// cross check with pv
r = 1;
n = 10;
p = -109.66796875;
f = 10000;
t = false;
y = Vba.pmt(r, n, p, f, t);
x = 100;
assertEquals(x, y);
r = 1;
n = 10;
p = -209.5703125;
f = 10000;
t = true;
y = Vba.pmt(r, n, p, f, t);
x = 100;
assertEquals(x, y);
// cross check with fv
r = 2;
n = 12;
f = -6409178400d;
p = 12000;
t = false;
y = Vba.pmt(r, n, p, f, t);
x = 120;
assertEquals(x, y);
r = 2;
n = 12;
f = -6472951200d;
p = 12000;
t = true;
y = Vba.pmt(r, n, p, f, t);
x = 120;
assertEquals(x, y);
}
public void testIRR() {
double vals[] = {-1000, 50, 50, 50, 50, 50, 1050};
assertTrue(Math.abs(0.05 - Vba.IRR(vals, 0.1)) < 0.0000001);
vals = new double[] {-1000, 200, 200, 200, 200, 200, 200};
assertTrue(Math.abs(0.05471796 - Vba.IRR(vals, 0.1)) < 0.0000001);
// what happens if the numbers are inversed? this may not be
// accurate
vals = new double[] {1000, -200, -200, -200, -200, -200, -200};
assertTrue(Math.abs(0.05471796 - Vba.IRR(vals, 0.1)) < 0.0000001);
}
public void testDatePart2() {
assertEquals(2008, Vba.datePart("yyyy", SAMPLE_DATE));
assertEquals(2, Vba.datePart("q", SAMPLE_DATE)); // 2nd quarter
assertEquals(4, Vba.datePart("m", SAMPLE_DATE));
assertEquals(5, Vba.datePart("w", SAMPLE_DATE)); // thursday
assertEquals(17, Vba.datePart("ww", SAMPLE_DATE));
assertEquals(115, Vba.datePart("y", SAMPLE_DATE));
assertEquals(19, Vba.datePart("h", SAMPLE_DATE));
assertEquals(10, Vba.datePart("n", SAMPLE_DATE));
assertEquals(45, Vba.datePart("s", SAMPLE_DATE));
}
public void testDdb() {
double cost, salvage, life, period, factor, result;
cost = 100;
salvage = 0;
life = 10;
period = 1;
factor = 2;
result = Vba.dDB(cost, salvage, life, period, factor);
assertEquals(20.0, result);
result = Vba.dDB(cost, salvage, life, period + 1, factor);
assertEquals(40.0, result);
result = Vba.dDB(cost, salvage, life, period + 2, factor);
assertEquals(60.0, result);
result = Vba.dDB(cost, salvage, life, period + 3, factor);
assertEquals(80.0, result);
}
public void testDate() {
final Date date = Vba.date();
assertNotNull(date);
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
assertEquals(0, calendar.get(Calendar.HOUR_OF_DAY));
assertEquals(0, calendar.get(Calendar.MILLISECOND));
}
public void testStr() {
assertEquals(" 0", Vba.str(0));
assertEquals(" 1", Vba.str(1));
assertEquals(" 10", Vba.str(10));
assertEquals(" 100", Vba.str(100));
assertEquals("-1", Vba.str(-1));
assertEquals("-10", Vba.str(-10));
assertEquals("-100", Vba.str(-100));
assertEquals("-10.123", Vba.str(-10.123));
assertEquals(" 10.123", Vba.str(10.123));
try {
Object o = Vba.oct("a");
fail("expected error, got " + o);
} catch (RuntimeException e) {
assertMessage(e, "Invalid parameter.");
}
}
public void testRight() {
assertEquals("xyz", Vba.right("abcxyz", 3));
// length=0 is OK
assertEquals("", Vba.right("abcxyz", 0));
// Spec says: "If greater than or equal to the number of characters in
// string, the entire string is returned."
assertEquals("abcxyz", Vba.right("abcxyz", 8));
assertEquals("", Vba.right("", 3));
// The VBA spec says that length<0 is error.
// Note: SSAS 2005 allows length<0, giving the same result as length=0.
// We favor the VBA spec over SSAS 2005.
if (Bug.Ssas2005Compatible) {
assertEquals("", Vba.right("xyz", -2));
} else {
try {
String s = Vba.right("xyz", -2);
fail("expected error, got " + s);
} catch (RuntimeException e) {
assertMessage(e, "StringIndexOutOfBoundsException");
}
}
assertEquals("World!", Vba.right("Hello World!", 6));
}
public void testFormatCurrency() {
assertEquals("$1.00", Vba.formatCurrency(1.0));
assertEquals("$0.00", Vba.formatCurrency(0.0));
assertEquals("$1.0", Vba.formatCurrency(1.0, 1));
assertEquals("$1", Vba.formatCurrency(1.0, 0));
assertEquals("$.10", Vba.formatCurrency(0.10, -1, 0));
assertEquals("$0.10", Vba.formatCurrency(0.10, -1, -1));
// todo: still need to implement parens customization
// assertEquals("-$0.10", Vba.formatCurrency(-0.10, -1, -1, -1));
assertEquals("($0.10)", Vba.formatCurrency(-0.10, -1, -1, 0));
assertEquals("$1,000.00", Vba.formatCurrency(1000.0, -1, -1, 0, 0));
assertEquals("$1000.00", Vba.formatCurrency(1000.0, -1, -1, 0, -1));
}
public void testCDate() throws ParseException {
Date date = new Date();
assertEquals(date, Vba.cDate(date));
assertNull(Vba.cDate(null));
// CInt rounds to the nearest even number
try {
assertEquals(DateFormat.getDateInstance().parse("Jan 12, 1952"), Vba.cDate("Jan 12, 1952"));
assertEquals(
DateFormat.getDateInstance().parse("October 19, 1962"), Vba.cDate("October 19, 1962"));
assertEquals(DateFormat.getTimeInstance().parse("4:35:47 PM"), Vba.cDate("4:35:47 PM"));
assertEquals(
DateFormat.getDateTimeInstance().parse("October 19, 1962 4:35:47 PM"),
Vba.cDate("October 19, 1962 4:35:47 PM"));
} catch (ParseException e) {
e.printStackTrace();
fail();
}
try {
Vba.cDate("Jan, 1952");
fail();
} catch (InvalidArgumentException e) {
assertTrue(e.getMessage().indexOf("Jan, 1952") >= 0);
}
}
public void testDateValue() {
Date date = new Date();
final Date date1 = Vba.dateValue(date);
Calendar calendar = Calendar.getInstance();
calendar.setTime(date1);
assertEquals(0, calendar.get(Calendar.HOUR_OF_DAY));
assertEquals(0, calendar.get(Calendar.MINUTE));
assertEquals(0, calendar.get(Calendar.SECOND));
assertEquals(0, calendar.get(Calendar.MILLISECOND));
}
public void testTypeName() {
assertEquals("Double", Vba.typeName(1.0));
assertEquals("Integer", Vba.typeName(1));
assertEquals("Float", Vba.typeName(1.0f));
assertEquals("Byte", Vba.typeName((byte) 1));
assertEquals("NULL", Vba.typeName(null));
assertEquals("String", Vba.typeName(""));
assertEquals("Date", Vba.typeName(new Date()));
}
public void testIsDate() throws ParseException {
// CInt rounds to the nearest even number
assertFalse(Vba.isDate(null));
assertTrue(Vba.isDate(new Date()));
assertTrue(Vba.isDate("Jan 12, 1952"));
assertTrue(Vba.isDate("October 19, 1962"));
assertTrue(Vba.isDate("4:35:47 PM"));
assertTrue(Vba.isDate("October 19, 1962 4:35:47 PM"));
assertFalse(Vba.isDate("Jan, 1952"));
}
public void testRate() {
double nPer, pmt, PV, fv, guess, result;
boolean type = false;
nPer = 12 * 30;
pmt = -877.57;
PV = 100000;
fv = 0;
guess = 0.10 / 12;
result = Vba.rate(nPer, pmt, PV, fv, type, guess);
// compare rate to pV calculation
double expRate = 0.0083333;
double expPV = Vba.pV(expRate, 12 * 30, -877.57, 0, false);
result = Vba.rate(12 * 30, -877.57, expPV, 0, false, 0.10 / 12);
assertTrue(Math.abs(expRate - result) < 0.0000001);
// compare rate to fV calculation
double expFV = Vba.fV(expRate, 12, -100, 0, false);
result = Vba.rate(12, -100, 0, expFV, false, 0.10 / 12);
assertTrue(Math.abs(expRate - result) < 0.0000001);
}
public void testDatePart3() {
assertEquals(5, Vba.datePart("w", SAMPLE_DATE, Calendar.SUNDAY));
assertEquals(4, Vba.datePart("w", SAMPLE_DATE, Calendar.MONDAY));
assertEquals(17, Vba.datePart("ww", SAMPLE_DATE, Calendar.SUNDAY));
assertEquals(18, Vba.datePart("ww", SAMPLE_DATE, Calendar.WEDNESDAY));
assertEquals(18, Vba.datePart("ww", SAMPLE_DATE, Calendar.THURSDAY));
assertEquals(17, Vba.datePart("ww", SAMPLE_DATE, Calendar.FRIDAY));
}
public void testOct() {
assertEquals("0", Vba.oct(0));
assertEquals("1", Vba.oct(1));
assertEquals("12", Vba.oct(10));
assertEquals("144", Vba.oct(100));
assertEquals("37777777777", Vba.oct(-1));
assertEquals("37777777766", Vba.oct(-10));
assertEquals("37777777634", Vba.oct(-100));
try {
Object o = Vba.oct("a");
fail("expected error, got " + o);
} catch (RuntimeException e) {
assertMessage(e, "Invalid parameter.");
}
}
public void testVal() {
assertEquals(-1615198.0, Vba.val(" - 1615 198th Street N.E."));
assertEquals(1615198.0, Vba.val(" 1615 198th Street N.E."));
assertEquals(1615.198, Vba.val(" 1615 . 198th Street N.E."));
assertEquals(1615.19, Vba.val(" 1615 . 19 . 8th Street N.E."));
assertEquals((double) 0xffff, Vba.val("&HFFFF"));
assertEquals(668.0, Vba.val("&O1234"));
}
