def mod(nb, q): # Excel Reference: https://support.office.com/en-us/article/MOD-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3 if not isinstance(nb, int): return ExcelError('#VALUE!', '%s is not an integer' % str(nb)) elif not isinstance(q, int): return ExcelError('#VALUE!', '%s is not an integer' % str(q)) else: return nb % q
def xnpv(rate, values, dates, lim_rate = True): # Excel reference: https://support.office.com/en-us/article/XNPV-function-1b42bbf6-370f-4532-a0eb-d67c16b664b7 """ Function to calculate the net present value (NPV) using payments and non-periodic dates. It resembles the excel function XPNV(). :param rate: the discount rate. :param values: the payments of which at least one has to be negative. :param dates: the dates as excel dates (e.g. 43571 for 16/04/2019). :return: a float being the NPV. """ if isinstance(values, Range): values = values.values if isinstance(dates, Range): dates = dates.values if len(values) != len(dates): return ExcelError('#NUM!', '`values` range must be the same length as `dates` range in XNPV, %s != %s' % (len(values), len(dates))) if lim_rate and rate < 0: return ExcelError('#NUM!', '`excel cannot handle a negative `rate`' % (len(values), len(dates))) xnpv = 0 for v, d in zip(values, dates): xnpv += v / np.power(1.0 + rate, (d - dates[0]) / 365) return xnpv
def lookup(value, lookup_range, result_range = None): # Excel reference: https://support.office.com/en-us/article/LOOKUP-function-446d94af-663b-451d-8251-369d5e3864cb # TODO if not isinstance(value,(int,float)): return Exception("Non numeric lookups (%s) not supported" % value) # TODO: note, may return the last equal value # index of the last numeric value lastnum = -1 for i,v in enumerate(lookup_range.values): if isinstance(v,(int,float)): if v > value: break else: lastnum = i output_range = result_range.values if result_range is not None else lookup_range.values if lastnum < 0: return ExcelError('#VALUE!', 'No numeric data found in the lookup range') else: if i == 0: return ExcelError('#VALUE!', 'All values in the lookup range are bigger than %s' % value) else: if i >= len(lookup_range)-1: # return the biggest number smaller than value return output_range[lastnum] else: return output_range[i-1]
def vlookup(lookup_value, table_array, col_index_num, range_lookup = True): # https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1 if not isinstance(table_array, Range): return ExcelError('#VALUE', 'table_array should be a Range') if col_index_num > table_array.ncols: return ExcelError('#VALUE', 'col_index_num is greater than the number of cols in table_array') first_column = table_array.get(0, 1) result_column = table_array.get(0, col_index_num) if not range_lookup: if lookup_value not in first_column.values: return ExcelError('#N/A', 'lookup_value not in first column of table_array') else: i = first_column.values.index(lookup_value) ref = first_column.order[i] else: i = None for v in first_column.values: if lookup_value >= v: i = first_column.values.index(v) ref = first_column.order[i] else: break if i is None: return ExcelError('#N/A', 'lookup_value smaller than all values of table_array') return Range.find_associated_value(ref, result_column)
def date( year, month, day ): # Excel reference: https://support.office.com/en-us/article/DATE-function-e36c0c8c-4104-49da-ab83-82328b832349 if type(year) != int: return ExcelError('#VALUE!', '%s is not an integer' % str(year)) if type(month) != int: return ExcelError('#VALUE!', '%s is not an integer' % str(month)) if type(day) != int: return ExcelError('#VALUE!', '%s is not an integer' % str(day)) if year < 0 or year > 9999: return ExcelError( '#VALUE!', 'Year must be between 1 and 9999, instead %s' % str(year)) if year < 1900: year = 1900 + year year, month, day = normalize_year( year, month, day) # taking into account negative month and day values date_0 = datetime(1900, 1, 1) date = datetime(year, month, day) result = (datetime(year, month, day) - date_0).days + 2 if result <= 0: return ExcelError('#VALUE!', 'Date result is negative') else: return result
def month(serial_number): # Excel reference: https://support.office.com/en-us/article/month-function-579a2881-199b-48b2-ab90-ddba0eba86e8 if not is_number(serial_number): return ExcelError('#VALUE!', 'start_date %s must be a number' % str(serial_number)) if serial_number < 0: return ExcelError('#VALUE!', 'start_date %s must be positive' % str(serial_number)) y1, m1, d1 = date_from_int(serial_number) return m1
def year(serial_number): # Excel reference: https://support.office.com/en-us/article/year-function-c64f017a-1354-490d-981f-578e8ec8d3b9 if not is_number(serial_number): return ExcelError('#VALUE!', 'start_date %s must be a number' % str(serial_number)) if serial_number < 0: return ExcelError('#VALUE!', 'start_date %s must be positive' % str(serial_number)) y1, m1, d1 = date_from_int(serial_number) return y1
def choose(index_num, *values): # Excel reference: https://support.office.com/en-us/article/CHOOSE-function-fc5c184f-cb62-4ec7-a46e-38653b98f5bc index = int(index_num) if index <= 0 or index > 254: return ExcelError('#VALUE!', '%s must be between 1 and 254' % str(index_num)) elif index > len(values): return ExcelError('#VALUE!', '%s must not be larger than the number of values: %s' % (str(index_num), len(values))) else: return values[index - 1]
def concatenate(*args): if tuple(flatten(args)) != args: return ExcelError('#VALUE', 'Could not process arguments %s' % (args)) cat_string = ''.join(str(a) for a in args) if len(cat_string) > CELL_CHARACTER_LIMIT: return ExcelError('#VALUE', 'Too long. concatentaed string should be no longer than %s but is %s' % (CELL_CHARACTER_LIMIT, len(cat_String))) return cat_string
def power(number, power): if number == power == 0: # Really excel? What were you thinking? return ExcelError('#NUM!', 'Number and power cannot both be zero' % str(number)) if power < 1 and number < 0: return ExcelError('#NUM!', '%s must be non-negative' % str(number)) return np.power(number, power)
def xnpv( rate, values, dates, lim_rate_low=True, lim_rate_high=False ): # Excel reference: https://support.office.com/en-us/article/XNPV-function-1b42bbf6-370f-4532-a0eb-d67c16b664b7 """ Function to calculate the net present value (NPV) using payments and non-periodic dates. It resembles the excel function XPNV(). :param rate: the discount rate. :param values: the payments of which at least one has to be negative. :param dates: the dates as excel dates (e.g. 43571 for 16/04/2019). :param lim_rate_low: to limit the rate below 0. :param lim_rate_high: to limit the rate above 1000 to avoid overflow errors. :return: a float being the NPV. """ if isinstance(values, Range): values = values.values if isinstance(dates, Range): dates = dates.values if is_not_number_input(rate): return numeric_error(rate, 'rate') if is_not_number_input(values): return numeric_error(values, 'values') if is_not_number_input(dates): return numeric_error(dates, 'dates') if len(values) != len(dates): return ExcelError( '#NUM!', '`values` range must be the same length as `dates` range in XNPV, %s != %s' % (len(values), len(dates))) if lim_rate_low and rate < 0: return ExcelError( '#NUM!', '`excel cannot handle a negative `rate`' % (len(values), len(dates))) if lim_rate_high and rate > 1000: raise ExcelError( '#NUM!', '`will result in an overflow error due to high `rate`') xnpv = 0 with np.errstate(all='raise'): for v, d in zip(values, dates): xnpv += v / np.power(1.0 + rate, (d - dates[0]) / 365) return xnpv
def substitute(*args): if tuple(flatten(args)) != args: return ExcelError('#VALUE', 'Could not process arguments %s' % (args)) cat_string = str(args[0]).replace(str(args[1]), str(args[2])) if len(cat_string) > CELL_CHARACTER_LIMIT: return ExcelError( '#VALUE', 'Too long. substituted string should be no longer than %s but is %s' % (CELL_CHARACTER_LIMIT, len(cat_string))) return cat_string
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): return ExcelError('#VALUE!', '%s is not a number' % str(number)) if not is_number(num_digits): return ExcelError('#VALUE!', '%s is not a number' % str(num_digits)) number = float(number) # if you don't Spreadsheet.dump/load, you might end up with Long numbers, which Decimal doesn't accept 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)
def roundup(number, num_digits = 0): # Excel reference: https://support.office.com/en-us/article/ROUNDUP-function-f8bc9b23-e795-47db-8703-db171d0c42a7 if not is_number(number): return ExcelError('#VALUE!', '%s is not a number' % str(number)) if not is_number(num_digits): return ExcelError('#VALUE!', '%s is not a number' % str(num_digits)) number = float(number) # if you don't Spreadsheet.dump/load, you might end up with Long numbers, which Decimal doesn't accept 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_UP)) # see https://docs.python.org/2/library/functions.html#round # and https://gist.github.com/ejamesc/cedc886c5f36e2d075c5 else: return ceil(number / pow(10, -num_digits)) * pow(10, -num_digits)
def irr(values, guess=None): """ Function to calculate the internal rate of return (IRR) using payments and periodic dates. It resembles the excel function IRR(). Excel reference: https://support.office.com/en-us/article/IRR-function-64925eaa-9988-495b-b290-3ad0c163c1bc :param values: the payments of which at least one has to be negative. :param guess: an initial guess which is required by Excel but isn't used by this function. :return: a float being the IRR. """ if isinstance(values, Range): values = values.values if is_not_number_input(values): return numeric_error(values, 'values') if guess is not None and guess != 0: raise ValueError('guess value for excellib.irr() is %s and not 0' % guess) else: try: return np.irr(values) except Exception as e: return ExcelError('#NUM!', e)
def countifs(*args): # Excel reference: https://support.office.com/en-us/article/COUNTIFS-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842 arg_list = list(args) l = len(arg_list) if l % 2 != 0: return ExcelError('#VALUE!', 'excellib.countifs() must have a pair number of arguments, here %d' % l) if l >= 2: indexes = find_corresponding_index(args[0].values, args[1]) # find indexes that match first layer of countif remaining_ranges = [elem for i, elem in enumerate(arg_list[2:]) if i % 2 == 0] # get only ranges remaining_criteria = [elem for i, elem in enumerate(arg_list[2:]) if i % 2 == 1] # get only criteria # verif that all Ranges are associated COULDNT MAKE THIS WORK CORRECTLY BECAUSE OF RECURSION # association_type = None # temp = [args[0]] + remaining_ranges # for index, range in enumerate(temp): # THIS IS SHIT, but works ok # if type(range) == Range and index < len(temp) - 1: # asso_type = range.is_associated(temp[index + 1]) # print 'asso', asso_type # if association_type is None: # association_type = asso_type # elif associated_type != asso_type: # association_type = None # break # print 'ASSO', association_type # if association_type is None: # return ValueError('All items must be Ranges and associated') filtered_remaining_ranges = [] for range in remaining_ranges: # filter items in remaining_ranges that match valid indexes from first countif layer filtered_remaining_cells = [] filtered_remaining_range = [] for index, item in enumerate(range.values): if index in indexes: filtered_remaining_cells.append(range.addresses[index]) # reconstructing cells from indexes filtered_remaining_range.append(item) # reconstructing values from indexes # WARNING HERE filtered_remaining_ranges.append(Range(filtered_remaining_cells, filtered_remaining_range)) new_tuple = () for index, range in enumerate(filtered_remaining_ranges): # rebuild the tuple that will be the argument of next layer new_tuple += (range, remaining_criteria[index]) return min(countifs(*new_tuple), len(indexes)) # only consider the minimum number across all layer responses else: return float('inf')
def mid( text, start_num, num_chars ): # Excel reference: https://support.office.com/en-us/article/MID-MIDB-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028 text = str(text) if type(start_num) != int: return ExcelError('#VALUE!', '%s is not an integer' % str(start_num)) if type(num_chars) != int: return ExcelError('#VALUE!', '%s is not an integer' % str(num_chars)) if start_num < 1: return ExcelError('#VALUE!', '%s is < 1' % str(start_num)) if num_chars < 0: return ExcelError('#VALUE!', '%s is < 0' % str(num_chars)) return text[start_num:num_chars]
def mid(text, start_num, num_chars): # Excel reference: https://support.office.com/en-us/article/MID-MIDB-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028 text = str(text) if len(text) > CELL_CHARACTER_LIMIT: return ExcelError('#VALUE!', 'text is too long. Is %s needs to be %s or less.' % (len(text), CELL_CHARACTER_LIMIT)) if type(start_num) != int: return ExcelError('#VALUE!', '%s is not an integer' % str(start_num)) if type(num_chars) != int: return ExcelError('#VALUE!', '%s is not an integer' % str(num_chars)) if start_num < 1: return ExcelError('#VALUE!', '%s is < 1' % str(start_num)) if num_chars < 0: return ExcelError('#VALUE!', '%s is < 0' % str(num_chars)) return text[(start_num - 1): (start_num - 1 + num_chars)]
def eomonth(start_date, months): # Excel reference: https://support.office.com/en-us/article/eomonth-function-7314ffa1-2bc9-4005-9d66-f49db127d628 if not is_number(start_date): return ExcelError('#VALUE!', 'start_date %s must be a number' % str(start_date)) if start_date < 0: return ExcelError('#VALUE!', 'start_date %s must be positive' % str(start_date)) if not is_number(months): return ExcelError('#VALUE!', 'months %s must be a number' % str(months)) y1, m1, d1 = date_from_int(start_date) start_date_d = datetime.date(year=y1, month=m1, day=d1) end_date_d = start_date_d + relativedelta(months=months) y2 = end_date_d.year m2 = end_date_d.month d2 = monthrange(y2, m2)[1] res = int(int_from_date(datetime.date(y2, m2, d2))) return res
def xirr(values, dates, guess=0): """ Function to calculate the internal rate of return (IRR) using payments and non-periodic dates. It resembles the excel function XIRR(). Excel reference: https://support.office.com/en-ie/article/xirr-function-de1242ec-6477-445b-b11b-a303ad9adc9d :param values: the payments of which at least one has to be negative. :param dates: the dates as excel dates (e.g. 43571 for 16/04/2019). :param guess: an initial guess which is required by Excel but isn't used by this function. :return: a float being the IRR. """ if isinstance(values, Range): values = values.values if all(value < 0 for value in values): return 0 if isinstance(dates, Range): dates = dates.values if is_not_number_input(values): return numeric_error(values, 'values') if is_not_number_input(dates): return numeric_error(dates, 'dates') if guess is not None and guess != 0: raise ValueError('guess value for excellib.irr() is %s and not 0' % guess) else: try: try: return scipy.optimize.newton( lambda r: xnpv(r, values, dates, lim_rate_low=False, lim_rate_high=True), 0.0) except (RuntimeError, FloatingPointError, ExcelError): # Failed to converge? return scipy.optimize.brentq( lambda r: xnpv(r, values, dates, lim_rate_low=False, lim_rate_high=True), -1.0, 1e5) except Exception: return ExcelError('#NUM', 'IRR did not converge.')
def irr( values, guess=None ): # Excel reference: https://support.office.com/en-us/article/IRR-function-64925eaa-9988-495b-b290-3ad0c163c1bc # Numpy reference: http://docs.scipy.org/doc/numpy-1.10.0/reference/generated/numpy.irr.html if (isinstance(values, Range)): values = values.values if guess is not None and guess != 0: raise ValueError('guess value for excellib.irr() is %s and not 0' % guess) else: try: return np.irr(values) except Exception as e: return ExcelError('#NUM!', e)
def xnpv( *args ): # Excel reference: https://support.office.com/en-us/article/XNPV-function-1b42bbf6-370f-4532-a0eb-d67c16b664b7 rate = args[0] # ignore non numeric cells and boolean cells values = extract_numeric_values(args[1]) dates = extract_numeric_values(args[2]) if len(values) != len(dates): return ExcelError( '#NUM!', '`values` range must be the same length as `dates` range in XNPV, %s != %s' % (len(values), len(dates))) xnpv = 0 for v, d in zip(values, dates): xnpv += v / np.power(1.0 + rate, (d - dates[0]) / 365) return xnpv
def sumproduct(*ranges): # Excel reference: https://support.office.com/en-us/article/SUMPRODUCT-function-16753e75-9f68-4874-94ac-4d2145a2fd2e range_list = list(ranges) for r in range_list: # if a range has no values (i.e if it's empty) if len(r.values) == 0: return 0 for range in range_list: for item in range.values: # If there is an ExcelError inside a Range, sumproduct should output an ExcelError if isinstance(item, ExcelError): return ExcelError("#N/A", "ExcelErrors are present in the sumproduct items") reduce(check_length, range_list) # check that all ranges have the same size return reduce(lambda X, Y: X + Y, reduce(lambda x, y: Range.apply_all('multiply', x, y), range_list).values)
def offset(reference, rows, cols, height=None, width=None): # Excel reference: https://support.office.com/en-us/article/OFFSET-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66 # This function accepts a list of addresses # Maybe think of passing a Range as first argument for i in [reference, rows, cols, height, width]: if isinstance(i, ExcelError) or i in ErrorCodes: return i rows = int(rows) cols = int(cols) # get first cell address of reference if is_range(reference): ref = resolve_range(reference, should_flatten = True)[0][0] else: ref = reference ref_sheet = '' end_address = '' if '!' in ref: ref_sheet = ref.split('!')[0] + '!' ref_cell = ref.split('!')[1] else: ref_cell = ref found = re.search(CELL_REF_RE, ref) new_col = col2num(found.group(1)) + cols new_row = int(found.group(2)) + rows if new_row <= 0 or new_col <= 0: return ExcelError('#VALUE!', 'Offset is out of bounds') start_address = str(num2col(new_col)) + str(new_row) if (height is not None and width is not None): if type(height) != int: return ExcelError('#VALUE!', '%d must not be integer' % height) if type(width) != int: return ExcelError('#VALUE!', '%d must not be integer' % width) if height > 0: end_row = new_row + height - 1 else: return ExcelError('#VALUE!', '%d must be strictly positive' % height) if width > 0: end_col = new_col + width - 1 else: return ExcelError('#VALUE!', '%d must be strictly positive' % width) end_address = ':' + str(num2col(end_col)) + str(end_row) elif height and not width or not height and width: return ExcelError('Height and width must be passed together') return ref_sheet + start_address + end_address
def vdb( cost, salvage, life, start_period, end_period, factor=2, no_switch=False ): # Excel reference: https://support.office.com/en-us/article/VDB-function-dde4e207-f3fa-488d-91d2-66d55e861d73 for arg in [ cost, salvage, life, start_period, end_period, factor, no_switch ]: if isinstance(arg, ExcelError) or arg in ErrorCodes: return arg for arg in [cost, salvage, life, start_period, end_period, factor]: if not isinstance(arg, (float, int, long)): return ExcelError( '#VALUE', 'Arg %s should be an int, float or long, instead: %s' % (arg, type(arg))) start_period = start_period end_period = end_period sln_depr = sln(cost, salvage, life) depr_rate = factor / life acc_depr = 0 depr = 0 switch_to_sln = False sln_depr = 0 result = 0 start_life = 0 delta_life = life % 1 if delta_life > 0: # to handle cases when life is not an integer end_life = int(life + 1) else: end_life = int(life) periods = range(start_life, end_life) if int(start_period) != start_period: delta_start = abs(int(start_period) - start_period) depr = (cost - acc_depr) * depr_rate * delta_start acc_depr += depr start_life = 1 periods = map(lambda x: x + 0.5, periods) for index, current_year in enumerate(periods): if not no_switch: # no_switch = False (Default Case) if switch_to_sln: depr = sln_depr else: depr = (cost - acc_depr) * depr_rate acc_depr += depr temp_sln_depr = sln(cost, salvage, life) if depr < temp_sln_depr: switch_to_sln = True fixed_remaining_years = life - current_year - 1 fixed_remaining_cost = cost - acc_depr # we need to check future sln: current depr should never be smaller than sln to come sln_depr = sln(fixed_remaining_cost, salvage, fixed_remaining_years) if sln_depr > depr: # if it's the case, we switch to sln earlier than the regular case # cancel what has been done acc_depr -= depr fixed_remaining_years += 1 fixed_remaining_cost = cost - acc_depr # recalculate depreciation sln_depr = sln(fixed_remaining_cost, salvage, fixed_remaining_years) depr = sln_depr acc_depr += depr else: # no_switch = True depr = (cost - acc_depr) * depr_rate acc_depr += depr delta_start = abs(current_year - start_period) if delta_start < 1 and delta_start != 0: result += depr * (1 - delta_start) elif current_year >= start_period and current_year < end_period: delta_end = abs(end_period - current_year) if delta_end < 1 and delta_end != 0: result += depr * delta_end else: result += depr return result
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): return ExcelError('#VALUE!', 'start_date %s must be a number' % str(start_date)) if not is_number(end_date): return ExcelError('#VALUE!', 'end_date %s must be number' % str(end_date)) if start_date < 0: return ExcelError('#VALUE!', 'start_date %s must be positive' % str(start_date)) if end_date < 0: return ExcelError('#VALUE!', '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: return ExcelError('#VALUE!', '%d must be 0, 1, 2, 3 or 4' % basis) return result
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 not isinstance(lookup_range, Range): 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 lookup_value = type_convert(lookup_value) range_values = filter( lambda x: x is not None, lookup_range.values ) # 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 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 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: return [type_convert(x) for x in range_values].index(lookup_value) + 1 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
def index( my_range, row, col=None ): # Excel reference: https://support.office.com/en-us/article/INDEX-function-a5dcf0dd-996d-40a4-a822-b56b061328bd for i in [my_range, row, col]: if isinstance(i, ExcelError) or i in ErrorCodes: return i row = int(row) if row is not None else row col = int(col) if col is not None else col if isinstance(my_range, Range): cells = my_range.addresses nr = my_range.nrows nc = my_range.ncols else: cells, nr, nc = my_range if nr > 1 or nc > 1: a = np.array(cells) cells = a.flatten().tolist() nr = int(nr) nc = int(nc) if type(cells) != list: return ExcelError('#VALUE!', '%s must be a list' % str(cells)) if row is not None and not is_number(row): return ExcelError('#VALUE!', '%s must be a number' % str(row)) if row == 0 and col == 0: return ExcelError('#VALUE!', 'No index asked for Range') if row is not None and row > nr: return ExcelError('#VALUE!', 'Index %i out of range' % row) if nr == 1: col = row if col is None else col return cells[int(col) - 1] if nc == 1: return cells[int(row) - 1] else: # could be optimised if col is None or row is None: return ExcelError( '#VALUE!', 'Range is 2 dimensional, can not reach value with 1 arg as None' ) if not is_number(col): return ExcelError('#VALUE!', '%s must be a number' % str(col)) if col > nc: return ExcelError('#VALUE!', 'Index %i out of range' % col) indices = range(len(cells)) if row == 0: # get column filtered_indices = filter(lambda x: x % nc == col - 1, indices) filtered_cells = map(lambda i: cells[i], filtered_indices) return filtered_cells elif col == 0: # get row filtered_indices = filter(lambda x: int(x / nc) == row - 1, indices) filtered_cells = map(lambda i: cells[i], filtered_indices) return filtered_cells else: return cells[(row - 1) * nc + (col - 1)]
def yearfrac(start_date, end_date, basis=0): """ Function to calculate the fraction of the year between two dates Excel reference: https://support.office.com/en-us/article/YEARFRAC-function-3844141e-c76d-4143-82b6-208454ddc6a8 :param values: the payments of which at least one has to be negative. :param dates: the dates as excel dates (e.g. 43571 for 16/04/2019). :param guess: an initial guess which is required by Excel but isn't used by this function. :return: a float being the IRR. """ 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 <= 366: 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): return ExcelError('#VALUE!', 'start_date %s must be a number' % str(start_date)) if not is_number(end_date): return ExcelError('#VALUE!', 'end_date %s must be number' % str(end_date)) if start_date < 0: return ExcelError('#VALUE!', 'start_date %s must be positive' % str(start_date)) if end_date < 0: return ExcelError('#VALUE!', 'end_date %s must be positive' % str(end_date)) if not isinstance(basis, (int, float)): return ExcelError('#VALUE!', 'basis %s must be numeric' % str(basis)) basis = int(basis) # parse potential float to int if basis < 0 or basis > 4: return ExcelError('#NUM!', 'basis %s must be between 0 and 4' % str(basis)) 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: return ExcelError('#VALUE!', '%d must be 0, 1, 2, 3 or 4' % basis) return result
def sqrt(number): if number < 0: return ExcelError('#NUM!', '%s must be non-negative' % str(index_num)) return np.sqrt(number)