示例#1
0
def count(*args): # Excel reference: https://support.office.com/en-us/article/COUNT-function-a59cd7fc-b623-4d93-87a4-d23bf411294c
    l = list(args)

    total = 0

    for arg in l:
        if type(arg) == list:
            total += len(filter(lambda x: is_number(x) and type(x) is not bool, arg)) # count inside a list
        elif is_number(arg): # int() is used for text representation of numbers
            total += 1

    return total
示例#2
0
def roundup_unwrapped(number, num_digits):
    # Excel reference: https://support.office.com/en-us/article/
    #   ROUNDUP-function-F8BC9B23-E795-47DB-8703-DB171D0C42A7

    number, num_digits = coerce_to_number(number), coerce_to_number(num_digits)

    if not is_number(number) or not is_number(num_digits):
        return VALUE_ERROR

    if isinstance(number, bool):
        number = int(number)

    quant = Decimal('1E{}{}'.format('+-'[num_digits >= 0], abs(num_digits)))
    return float(Decimal(repr(number)).quantize(quant, rounding=ROUND_UP))
示例#3
0
def xround(number, num_digits = 0): # Excel reference: https://support.office.com/en-us/article/ROUND-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c

    if not is_number(number):
        raise TypeError("%s is not a number" % str(number))
    if not is_number(num_digits):
        raise TypeError("%s is not a number" % str(num_digits))

    if num_digits >= 0: # round to the right side of the point
        return float(Decimal(repr(number)).quantize(Decimal(repr(pow(10, -num_digits))), rounding=ROUND_HALF_UP))
        # see https://docs.python.org/2/library/functions.html#round
        # and https://gist.github.com/ejamesc/cedc886c5f36e2d075c5

    else:
        return round(number, num_digits)
示例#4
0
def count(*args):
    # Excel reference: https://support.office.com/en-us/article/
    #   COUNT-function-a59cd7fc-b623-4d93-87a4-d23bf411294c

    total = 0

    for arg in args:
        if isinstance(arg, list):
            # count inside a list
            total += len(
                [x for x in arg if is_number(x) and not isinstance(x, bool)])
        else:
            total += int(is_number(arg))

    return total
示例#5
0
def count(*args):
    # Excel reference: https://support.office.com/en-us/article/
    #   COUNT-function-a59cd7fc-b623-4d93-87a4-d23bf411294c

    total = 0

    for arg in args:
        if isinstance(arg, list):
            # count inside a list
            total += len(
                [x for x in arg if is_number(x) and not isinstance(x, bool)])
        else:
            total += int(is_number(arg))

    return total
示例#6
0
    def type_convert_float(value):
        if is_number(value):
            value = float(value)
        else:
            value = None

        return value
示例#7
0
def npv(rate, *args):
    # Excel reference: https://support.office.com/en-us/article/
    #   NPV-function-8672CB67-2576-4D07-B67B-AC28ACF2A568

    rate += 1
    cashflow = [x for x in flatten(args, coerce=coerce_to_number)
                if is_number(x) and not isinstance(x, bool)]
    return sum(x * rate ** -i for i, x in enumerate(cashflow, start=1))
示例#8
0
def mod(number, divisor):
    # Excel reference: https://support.office.com/en-us/article/
    #   MOD-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3
    if number in ERROR_CODES:
        return number
    if divisor in ERROR_CODES:
        return divisor

    number, divisor = coerce_to_number(number), coerce_to_number(divisor)

    if divisor in (0, None):
        return DIV0

    if not is_number(number) or not is_number(divisor):
        return VALUE_ERROR

    return number % divisor
示例#9
0
def count(
    *args
):  # Excel reference: https://support.office.com/en-us/article/COUNT-function-a59cd7fc-b623-4d93-87a4-d23bf411294c
    l = list(args)

    total = 0

    for arg in l:
        if type(arg) == list:
            total += len([
                x for x in arg if is_number(x) and type(x) is not bool
            ])  # count inside a list
        elif is_number(
                arg):  # int() is used for text representation of numbers
            total += 1

    return total
示例#10
0
def is_leap_year(year):
    if not is_number(year):
        raise TypeError("%s must be a number" % str(year))
    if year <= 0:
        raise TypeError("%s must be strictly positive" % str(year))

    # Watch out, 1900 is a leap according to Excel =>
    # https://support.microsoft.com/en-us/kb/214326
    return year % 4 == 0 and year % 100 != 0 or year % 400 == 0 or year == 1900
示例#11
0
def xround(
    number,
    num_digits=0
):  # Excel reference: https://support.office.com/en-us/article/ROUND-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c

    if not is_number(number):
        raise TypeError("%s is not a number" % str(number))
    if not is_number(num_digits):
        raise TypeError("%s is not a number" % str(num_digits))

    if num_digits >= 0:  # round to the right side of the point
        return float(
            Decimal(repr(number)).quantize(Decimal(repr(pow(10, -num_digits))),
                                           rounding=ROUND_HALF_UP))
        # see https://docs.python.org/2/library/functions.html#round
        # and https://gist.github.com/ejamesc/cedc886c5f36e2d075c5

    else:
        return round(number, num_digits)
示例#12
0
def xl_round(number, num_digits=0):
    """
    The ROUND function rounds a number to a specified number of digits. For example,
    if cell A1 contains 23.7825, and you want to round that value to two decimal places,
    you can use the following formula:

    =ROUND(A1, 2)

    The result of this function is 23.78.

    :param number: Required. The number that you want to round.
    :param num_digits: Required. The number of digits to which you want to round the number argument.
    :return: the rounded number

    .. remarks::

        * If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.
        * If num_digits is 0, the number is rounded to the nearest integer.
        * If num_digits is less than 0, the number is rounded to the left of the decimal point.
        * To always round up (away from zero), use the ROUNDUP function.
        * To always round down (toward zero), use the ROUNDDOWN function.
        * To round a number to a specific multiple (for example, to round to the nearest 0.5), use the MROUND function.

    .. reference::
        https://support.office.com/en-us/article/ROUND-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c

    """

    if not is_number(number):
        raise TypeError("%s is not a number" % str(number))
    if not is_number(num_digits):
        raise TypeError("%s is not a number" % str(num_digits))

    # round to the right side of the point
    if num_digits >= 0:
        return float(Decimal(repr(number)).quantize(Decimal(repr(pow(10, -num_digits))), rounding=ROUND_HALF_UP))
        # see https://docs.python.org/2/library/functions.html#round
        # and https://gist.github.com/ejamesc/cedc886c5f36e2d075c5
    else:
        return round(number, num_digits)
示例#13
0
def mid(text, start_num, num_chars):
    # Excel reference: https://support.office.com/en-us/article/
    #   MID-MIDB-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028

    if text in ERROR_CODES:
        return text
    if start_num in ERROR_CODES:
        return start_num
    if num_chars in ERROR_CODES:
        return num_chars

    start_num = coerce_to_number(start_num)
    num_chars = coerce_to_number(num_chars)

    if not is_number(start_num) or not is_number(num_chars):
        return VALUE_ERROR

    if start_num < 1 or num_chars < 0:
        return VALUE_ERROR

    start_num = int(start_num) - 1

    return str(text)[start_num:start_num + int(num_chars)]
示例#14
0
def xround(number, num_digits=0):
    # Excel reference: https://support.office.com/en-us/article/
    #   ROUND-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c

    if number in ERROR_CODES:
        return number
    if num_digits in ERROR_CODES:
        return num_digits

    number, num_digits = coerce_to_number(number), coerce_to_number(num_digits)
    if not is_number(number) or not is_number(num_digits):
        return VALUE_ERROR

    num_digits = int(num_digits)
    if num_digits >= 0:  # round to the right side of the point
        return float(
            Decimal(repr(number)).quantize(Decimal(repr(pow(10, -num_digits))),
                                           rounding=ROUND_HALF_UP))
        # see https://docs.python.org/2/library/functions.html#round
        # and https://gist.github.com/ejamesc/cedc886c5f36e2d075c5

    else:
        return round(number, num_digits)
示例#15
0
def count(*args):
    """

    :param args:
    :return:

    .. reference::
        https://support.office.com/en-us/article/COUNT-function-a59cd7fc-b623-4d93-87a4-d23bf411294c
    """

    l = list(args)

    total = 0

    for arg in l:
        if isinstance(arg, list_types):
            # count inside a list
            total += len(list(filter(lambda x: is_number(x) and type(x) is not bool, arg)))
        # int() is used for text representation of numbers
        elif is_number(arg):
            total += 1

    return total
示例#16
0
def right(text, num_chars=1):
    # Excel reference:  https://support.office.com/en-us/article/
    #   RIGHT-RIGHTB-functions-240267EE-9AFA-4639-A02B-F19E1786CF2F

    if text in ERROR_CODES:
        return text
    if num_chars in ERROR_CODES:
        return num_chars

    num_chars = coerce_to_number(num_chars)

    if not is_number(num_chars) or num_chars < 0:
        return VALUE_ERROR

    if num_chars == 0:
        return ''
    else:
        return str(text)[-int(num_chars):]
示例#17
0
    def wrapper(*args):
        new_args = tuple(coerce_to_number(a, convert_all=True)
                         if i in param_indices else a
                         for i, a in enumerate(args))
        error = next((a for i, a in enumerate(new_args)
                      if i in param_indices and a in ERROR_CODES), None)
        if error:
            return error

        if any(i in param_indices and not is_number(a)
               for i, a in enumerate(new_args)):
            return VALUE_ERROR

        try:
            return f(*new_args)
        except ValueError as exc:
            if "math domain error" in str(exc):
                return NUM_ERROR
            raise  # pragma: no cover
示例#18
0
    def wrapper(*args):
        new_args = tuple(coerce_to_number(a, convert_all=True)
                         if i in param_indices else a
                         for i, a in enumerate(args))
        error = next((a for i, a in enumerate(new_args)
                      if i in param_indices and a in ERROR_CODES), None)
        if error:
            return error

        if any(i in param_indices and not is_number(a)
               for i, a in enumerate(new_args)):
            return VALUE_ERROR

        try:
            return f(*new_args)
        except ValueError as exc:
            if "math domain error" in str(exc):
                return NUM_ERROR
            raise  # pragma: no cover
示例#19
0
def yearfrac(start_date, end_date, basis = 0): # Excel reference: https://support.office.com/en-us/article/YEARFRAC-function-3844141e-c76d-4143-82b6-208454ddc6a8
    
    def actual_nb_days_ISDA(start, end): # needed to separate days_in_leap_year from days_not_leap_year
        y1, m1, d1 = start
        y2, m2, d2 = end

        days_in_leap_year = 0
        days_not_in_leap_year = 0

        year_range = range(y1, y2 + 1)

        for y in year_range:

            if y == y1 and y == y2:
                nb_days = date(y2, m2, d2) - date(y1, m1, d1)
            elif y == y1:
                nb_days = date(y1 + 1, 1, 1) - date(y1, m1, d1)
            elif y == y2:
                nb_days = date(y2, m2, d2) - date(y2, 1, 1)
            else:
                nb_days = 366 if is_leap_year(y) else 365

            if is_leap_year(y):
                days_in_leap_year += nb_days
            else:
                days_not_in_leap_year += nb_days

        return (days_not_in_leap_year, days_in_leap_year)

    def actual_nb_days_AFB_alter(start, end): # http://svn.finmath.net/finmath%20lib/trunk/src/main/java/net/finmath/time/daycount/DayCountConvention_ACT_ACT_YEARFRAC.java
        y1, m1, d1 = start
        y2, m2, d2 = end

        delta = date(*end) - date(*start)

        if delta <= 365:
            if is_leap_year(y1) and is_leap_year(y2):
                denom = 366
            elif is_leap_year(y1) and date(y1, m1, d1) <= date(y1, 2, 29):
                denom = 366
            elif is_leap_year(y2) and date(y2, m2, d2) >= date(y2, 2, 29):
                denom = 366
            else:
                denom = 365
        else:
            year_range = range(y1, y2 + 1)
            nb = 0

            for y in year_range:
                nb += 366 if is_leap_year(y) else 365

            denom = nb / len(year_range)

        return delta / denom

    if not is_number(start_date):
        raise TypeError("start_date %s must be a number" % str(start_date))
    if not is_number(end_date):
        raise TypeError("end_date %s must be number" % str(end_date))
    if start_date < 0:
        raise ValueError("start_date %s must be positive" % str(start_date))
    if end_date < 0:
        raise ValueError("end_date %s must be positive" % str(end_date))

    if start_date > end_date: # switch dates if start_date > end_date
        temp = end_date
        end_date = start_date
        start_date = temp 

    y1, m1, d1 = date_from_int(start_date)
    y2, m2, d2 = date_from_int(end_date)

    if basis == 0: # US 30/360
        d2 = 30 if d2 == 31 and (d1 == 31 or d1 == 30) else min(d2, 31)
        d1 = 30 if d1 == 31 else d1

        count = 360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)
        result = count / 360

    elif basis == 1: # Actual/actual
        result = actual_nb_days_AFB_alter((y1, m1, d1), (y2, m2, d2))

    elif basis == 2: # Actual/360
        result = (end_date - start_date) / 360

    elif basis == 3: # Actual/365
        result = (end_date - start_date) / 365

    elif basis == 4: # Eurobond 30/360
        d2 = 30 if d2 == 31 else d2
        d1 = 30 if d1 == 31 else d1

        count = 360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)
        result = count / 360

    else:
        raise ValueError("%d must be 0, 1, 2, 3 or 4" % basis)


    return result
示例#20
0
def yearfrac(start_date, end_date, basis=0):
    """
    Calculates the fraction of the year represented by the number of whole days between
    two dates (the start_date and the end_date). Use the YEARFRAC worksheet function to
    identify the proportion of a whole year's benefits or obligations to assign to a
    specific term.

    :param start_date: Required. A date that represents the start date.
    :param end_date: Required. A date that represents the end date.
    :param basis: Optional. The type of day count basis to use.
    :return: Fraction of the year between the two dates.

    .. reference::
        https://support.office.com/en-us/article/YEARFRAC-function-3844141e-c76d-4143-82b6-208454ddc6a8

    .. remarks::
        * Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations.
            By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448
            because it is 39,448 days after January 1, 1900.
        * All arguments are truncated to integers.
        * If start_date or end_date are not valid dates, YEARFRAC returns the #VALUE! error value.
        * If basis < 0 or if basis > 4, YEARFRAC returns the #NUM! error value.


    """

    def actual_nb_days_ISDA(start, end): # needed to separate days_in_leap_year from days_not_leap_year
        y1, m1, d1 = start
        y2, m2, d2 = end

        days_in_leap_year = 0
        days_not_in_leap_year = 0

        year_range = range(y1, y2 + 1)

        for y in year_range:

            if y == y1 and y == y2:
                nb_days = date(y2, m2, d2) - date(y1, m1, d1)
            elif y == y1:
                nb_days = date(y1 + 1, 1, 1) - date(y1, m1, d1)
            elif y == y2:
                nb_days = date(y2, m2, d2) - date(y2, 1, 1)
            else:
                nb_days = 366 if is_leap_year(y) else 365

            if is_leap_year(y):
                days_in_leap_year += nb_days
            else:
                days_not_in_leap_year += nb_days

        return days_not_in_leap_year, days_in_leap_year

    def actual_nb_days_AFB_alter(start, end):
        """
        .. note::
            Converted from: http://svn.finmath.net/finmath%20lib/trunk/src/main/java/net/finmath/time/daycount/DayCountConvention_ACT_ACT_YEARFRAC.java
        """
        y1, m1, d1 = start
        y2, m2, d2 = end

        delta = date(*end) - date(*start)

        if delta <= 365:
            if is_leap_year(y1) and is_leap_year(y2):
                denom = 366
            elif is_leap_year(y1) and date(y1, m1, d1) <= date(y1, 2, 29):
                denom = 366
            elif is_leap_year(y2) and date(y2, m2, d2) >= date(y2, 2, 29):
                denom = 366
            else:
                denom = 365
        else:
            year_range = range(y1, y2 + 1)
            nb = 0

            for y in year_range:
                nb += 366 if is_leap_year(y) else 365

            denom = nb / len(year_range)

        return delta / denom

    if not is_number(start_date):
        raise TypeError("start_date %s must be a number" % str(start_date))
    if not is_number(end_date):
        raise TypeError("end_date %s must be number" % str(end_date))
    if start_date < 0:
        raise ValueError("start_date %s must be positive" % str(start_date))
    if end_date < 0:
        raise ValueError("end_date %s must be positive" % str(end_date))

    # switch dates if start_date > end_date
    if start_date > end_date:
        temp = end_date
        end_date = start_date
        start_date = temp

    y1, m1, d1 = date_from_int(start_date)
    y2, m2, d2 = date_from_int(end_date)

    # US 30/360
    if basis == 0:
        d2 = 30 if d2 == 31 and (d1 == 31 or d1 == 30) else min(d2, 31)
        d1 = 30 if d1 == 31 else d1

        count = 360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)
        result = count / 360

    # Actual/actual
    elif basis == 1:
        result = actual_nb_days_AFB_alter((y1, m1, d1), (y2, m2, d2))

    # Actual/360
    elif basis == 2:
        result = (end_date - start_date) / 360

    # Actual/365
    elif basis == 3:
        result = (end_date - start_date) / 365

    # Eurobond 30/360
    elif basis == 4:
        d2 = 30 if d2 == 31 else d2
        d1 = 30 if d1 == 31 else d1

        result = (360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)) / 360

    else:
        raise ValueError("%d must be 0, 1, 2, 3 or 4" % basis)

    return result
示例#21
0
def test_is_number():
    assert is_number(1)
    assert is_number(0)
    assert is_number(-1)
    assert is_number(1.0)
    assert is_number(0.0)
    assert is_number(-1.0)
    assert is_number('1.0')
    assert is_number('0.0')
    assert is_number('-1.0')
    assert is_number(True)
    assert is_number(False)

    assert not is_number(None)
    assert not is_number('x')
示例#22
0
def yearfrac(
    start_date,
    end_date,
    basis=0
):  # Excel reference: https://support.office.com/en-us/article/YEARFRAC-function-3844141e-c76d-4143-82b6-208454ddc6a8
    def actual_nb_days_ISDA(
            start, end
    ):  # needed to separate days_in_leap_year from days_not_leap_year
        y1, m1, d1 = start
        y2, m2, d2 = end

        days_in_leap_year = 0
        days_not_in_leap_year = 0

        year_range = list(range(y1, y2 + 1))

        for y in year_range:

            if y == y1 and y == y2:
                nb_days = date(y2, m2, d2) - date(y1, m1, d1)
            elif y == y1:
                nb_days = date(y1 + 1, 1, 1) - date(y1, m1, d1)
            elif y == y2:
                nb_days = date(y2, m2, d2) - date(y2, 1, 1)
            else:
                nb_days = 366 if is_leap_year(y) else 365

            if is_leap_year(y):
                days_in_leap_year += nb_days
            else:
                days_not_in_leap_year += nb_days

        return (days_not_in_leap_year, days_in_leap_year)

    def actual_nb_days_AFB_alter(
        start, end
    ):  # http://svn.finmath.net/finmath%20lib/trunk/src/main/java/net/finmath/time/daycount/DayCountConvention_ACT_ACT_YEARFRAC.java
        y1, m1, d1 = start
        y2, m2, d2 = end

        delta = date(*end) - date(*start)

        if delta <= 365:
            if is_leap_year(y1) and is_leap_year(y2):
                denom = 366
            elif is_leap_year(y1) and date(y1, m1, d1) <= date(y1, 2, 29):
                denom = 366
            elif is_leap_year(y2) and date(y2, m2, d2) >= date(y2, 2, 29):
                denom = 366
            else:
                denom = 365
        else:
            year_range = list(range(y1, y2 + 1))
            nb = 0

            for y in year_range:
                nb += 366 if is_leap_year(y) else 365

            denom = nb / len(year_range)

        return delta / denom

    if not is_number(start_date):
        raise TypeError("start_date %s must be a number" % str(start_date))
    if not is_number(end_date):
        raise TypeError("end_date %s must be number" % str(end_date))
    if start_date < 0:
        raise ValueError("start_date %s must be positive" % str(start_date))
    if end_date < 0:
        raise ValueError("end_date %s must be positive" % str(end_date))

    if start_date > end_date:  # switch dates if start_date > end_date
        temp = end_date
        end_date = start_date
        start_date = temp

    y1, m1, d1 = date_from_int(start_date)
    y2, m2, d2 = date_from_int(end_date)

    if basis == 0:  # US 30/360
        d2 = 30 if d2 == 31 and (d1 == 31 or d1 == 30) else min(d2, 31)
        d1 = 30 if d1 == 31 else d1

        count = 360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)
        result = count / 360

    elif basis == 1:  # Actual/actual
        result = actual_nb_days_AFB_alter((y1, m1, d1), (y2, m2, d2))

    elif basis == 2:  # Actual/360
        result = (end_date - start_date) / 360

    elif basis == 3:  # Actual/365
        result = (end_date - start_date) / 365

    elif basis == 4:  # Eurobond 30/360
        d2 = 30 if d2 == 31 else d2
        d1 = 30 if d1 == 31 else d1

        count = 360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)
        result = count / 360

    else:
        raise ValueError("%d must be 0, 1, 2, 3 or 4" % basis)

    return result
示例#23
0
def test_is_number(data, result):
    assert is_number(data) == result
示例#24
0
def test_is_number():
    assert is_number(1)
    assert is_number(0)
    assert is_number(-1)
    assert is_number(1.0)
    assert is_number(0.0)
    assert is_number(-1.0)
    assert is_number('1.0')
    assert is_number('0.0')
    assert is_number('-1.0')
    assert is_number(True)
    assert is_number(False)

    assert not is_number(None)
    assert not is_number('x')
示例#25
0
def test_is_number(data, expected):
    assert is_number(data) == expected
示例#26
0
def yearfrac(start_date, end_date, basis=0):
    # Excel reference: https://support.office.com/en-us/article/
    #   YEARFRAC-function-3844141e-c76d-4143-82b6-208454ddc6a8

    def actual_nb_days_afb_alter(beg, end):
        # http://svn.finmath.net/finmath%20lib/trunk/src/main/java/net/
        #   finmath/time/daycount/DayCountConvention_ACT_ACT_YEARFRAC.java
        delta = date(*end) - date(*beg)

        if delta <= 365:
            if (is_leap_year(beg[0]) and date(*beg) <= date(beg[0], 2, 29) or
                    is_leap_year(end[0]) and date(*end) >= date(end[0], 2, 29)
                    or is_leap_year(beg[0]) and is_leap_year(end[0])):
                denom = 366
            else:
                denom = 365
        else:
            year_range = range(beg[0], end[0] + 1)
            nb = 0

            for y in year_range:
                nb += 366 if is_leap_year(y) else 365

            denom = nb / len(year_range)

        return delta / denom

    if not is_number(start_date):
        raise TypeError("start_date %s must be a number" % str(start_date))
    if not is_number(end_date):
        raise TypeError("end_date %s must be number" % str(end_date))
    if start_date < 0:
        raise ValueError("start_date %s must be positive" % str(start_date))
    if end_date < 0:
        raise ValueError("end_date %s must be positive" % str(end_date))

    if start_date > end_date:  # switch dates if start_date > end_date
        start_date, end_date = end_date, start_date

    y1, m1, d1 = date_from_int(start_date)
    y2, m2, d2 = date_from_int(end_date)

    if basis == 0:  # US 30/360
        d1 = min(d1, 30)
        d2 = max(d2, 30) if d1 == 30 else d2

        day_count = 360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)
        result = day_count / 360

    elif basis == 1:  # Actual/actual
        result = actual_nb_days_afb_alter((y1, m1, d1), (y2, m2, d2))

    elif basis == 2:  # Actual/360
        result = (end_date - start_date) / 360

    elif basis == 3:  # Actual/365
        result = (end_date - start_date) / 365

    elif basis == 4:  # Eurobond 30/360
        d2 = min(d2, 30)
        d1 = min(d1, 30)

        day_count = 360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)
        result = day_count / 360

    else:
        raise ValueError("basis: %d must be 0, 1, 2, 3 or 4" % basis)

    return result
示例#27
0
def match(lookup_value, lookup_range, match_type=1): # Excel reference: https://support.office.com/en-us/article/MATCH-function-e8dffd45-c762-47d6-bf89-533f4a37673a

    if list_like(lookup_range) is False:
        return ExcelError('#VALUE!', 'Lookup_range is not a Range')

    def type_convert(value):
        if type(value) == str:
            value = value.lower()
        elif type(value) == int:
            value = float(value)
        elif value is None:
            value = 0

        return value

    def type_convert_float(value):
        if is_number(value):
            value = float(value)
        else:
            value = None

        return value

    lookup_value = type_convert(lookup_value)

    range_values = [item for t in lookup_range for item in t if t is not None]  # filter None values to avoid asc/desc order errors
    # range_values = [x for x in lookup_range[0]) if x is not None] # filter None values to avoid asc/desc order errors
    range_length = len(range_values)

    if match_type == 1:
        # Verify ascending sort

        posMax = -1
        for i in range(range_length):
            current = type_convert(range_values[i])

            if i < range_length - 1:
                if current > type_convert(range_values[i + 1]):
                    return ExcelError('#VALUE!', 'for match_type 1, lookup_range must be sorted ascending')
            if current <= lookup_value:
                posMax = i
        if posMax == -1:
            return ExcelError('#VALUE!','no result in lookup_range for match_type 1')
        return posMax +1 #Excel starts at 1

    elif match_type == 0:
        # No string wildcard
        try:
            if is_number(lookup_value):
                lookup_value = float(lookup_value)
                output = [type_convert_float(x) for x in range_values].index(lookup_value) + 1
            else:
                output = [str(x).lower() for x in range_values].index(lookup_value) + 1
            return output
        except:
            return ExcelError('#VALUE!', '%s not found' % lookup_value)

    elif match_type == -1:
        # Verify descending sort
        posMin = -1
        for i in range((range_length)):
            current = type_convert(range_values[i])

            if i is not range_length-1 and current < type_convert(range_values[i+1]):
               return ExcelError('#VALUE!','for match_type -1, lookup_range must be sorted descending')
            if current >= lookup_value:
               posMin = i
        if posMin == -1:
            return ExcelError('#VALUE!', 'no result in lookup_range for match_type -1')
        return posMin +1 #Excel starts at 1