"""Formulas from sheet 'Sensitivity' of 'pricing_calculator.xlsx' (pandas target).

Generated by Power Migrate 0.1.0a1. Copied formulas are column, row or
table operations on the sheet's DataFrame; other blocks run cell by cell over
the same frame. ``model.compute`` runs them in dependency order.
"""

from .. import xl
from ..frames import FrameBook, abs_, across, and_, average, averageif, averageifs, ceiling, cells, choose, col, concat, concatenate, count, counta, countblank, countif, countifs, date, datedif, day, div, edate, eomonth, eq, exact, exp, find, floor, fv, ge, gt, hlookup, iferror, ifna, ifs, index, int_, ipmt, irr, isblank, iserr, iserror, islogical, isna, isnumber, istext, le, left, len_, ln, log, log10, lower, lt, match, max_, maxifs, mid, min_, minifs, mod, month, n, ne, networkdays, not_, nper, npv, num, or_, pi, pmt, power, ppmt, product, proper, pv, rate, rept, right, round_, rounddown, roundup, search, shifted, sign, sqrt, substitute, sum_, sumif, sumifs, sumproduct, switch, text, textjoin, trim, trunc, upper, value, vlookup, weekday, where, workday, xlookup, xor, year, yearfrac


def range_b3_f7(book: FrameBook) -> None:
    r"""Sensitivity!B3:F7

    Sensitivity!B3:F7 (5 x 5 cells, filled).
    Excel (B3): =-PMT((APR+$A3)/12,B$2,Financed,-Balloon)
    Pattern (R1C1): =-PMT((APR+RC1)/12,R2C,Financed,-Balloon)
    Reads: Inputs!B16, Inputs!B17, RateCard!B12, Sensitivity!B2, Sensitivity!A3, name APR, name Balloon, name Financed
    """
    df = book.frame('Sensitivity')
    rows = slice(3, 7)
    cols = ['B', 'C', 'D', 'E', 'F']
    df.loc[rows, cols] = (-pmt(div((num(book.name_table('APR')) + num(cells(df, rows, cols, fixed_col='A'))), 12), cells(df, rows, cols, fixed_row=2), book.name_table('Financed'), (-num(book.name_table('Balloon')))))


def cell_b8(book: FrameBook) -> None:
    r"""Sensitivity!B8

    Sensitivity!B8 (one cell).
    Excel (B8): =-PMT((APR+A8)/12,B$2,Financed,-Balloon)
    Reads: Inputs!B16, Inputs!B17, RateCard!B12, Sensitivity!B2, Sensitivity!A8, name APR, name Balloon, name Financed
    """
    sensitivity = book['Sensitivity']
    sensitivity["B8"] = (-xl.PMT((book.name('APR') + sensitivity["A8"]) / 12, sensitivity["B2"], book.name('Financed'), (-book.name('Balloon'))))


def cell_c8(book: FrameBook) -> None:
    r"""Sensitivity!C8

    Sensitivity!C8 (one cell).
    Excel (C8): =-PMT((APR+A8)/12,C$2,Financed,-Balloon)
    Reads: Inputs!B16, Inputs!B17, RateCard!B12, Sensitivity!C2, Sensitivity!A8, name APR, name Balloon, name Financed
    """
    sensitivity = book['Sensitivity']
    sensitivity["C8"] = (-xl.PMT((book.name('APR') + sensitivity["A8"]) / 12, sensitivity["C2"], book.name('Financed'), (-book.name('Balloon'))))


def cell_d8(book: FrameBook) -> None:
    r"""Sensitivity!D8

    Sensitivity!D8 (one cell).
    Excel (D8): =-PMT((APR+A8)/12,D$2,Financed,-Balloon)
    Reads: Inputs!B16, Inputs!B17, RateCard!B12, Sensitivity!D2, Sensitivity!A8, name APR, name Balloon, name Financed
    """
    sensitivity = book['Sensitivity']
    sensitivity["D8"] = (-xl.PMT((book.name('APR') + sensitivity["A8"]) / 12, sensitivity["D2"], book.name('Financed'), (-book.name('Balloon'))))


def cell_e8(book: FrameBook) -> None:
    r"""Sensitivity!E8

    Sensitivity!E8 (one cell).
    Excel (E8): =-PMT((APR+A8)/12,E$2,Financed,-Balloon)
    Reads: Inputs!B16, Inputs!B17, RateCard!B12, Sensitivity!E2, Sensitivity!A8, name APR, name Balloon, name Financed
    """
    sensitivity = book['Sensitivity']
    sensitivity["E8"] = (-xl.PMT((book.name('APR') + sensitivity["A8"]) / 12, sensitivity["E2"], book.name('Financed'), (-book.name('Balloon'))))


def cell_f8(book: FrameBook) -> None:
    r"""Sensitivity!F8

    Sensitivity!F8 (one cell).
    Excel (F8): =-PMT((APR+A8)/12,F$2,Financed,-Balloon)
    Reads: Inputs!B16, Inputs!B17, RateCard!B12, Sensitivity!F2, Sensitivity!A8, name APR, name Balloon, name Financed
    """
    sensitivity = book['Sensitivity']
    sensitivity["F8"] = (-xl.PMT((book.name('APR') + sensitivity["A8"]) / 12, sensitivity["F2"], book.name('Financed'), (-book.name('Balloon'))))


def cheapest_option(book: FrameBook) -> None:
    r"""Cheapest option

    Sensitivity!B10 (one cell).
    Excel (B10): =MIN(B3:F8)
    Reads: Sensitivity!B3:F8
    """
    sensitivity = book['Sensitivity']
    sensitivity["B10"] = xl.MIN(sensitivity["B3:F8"])


def options_under_1_000_month(book: FrameBook) -> None:
    r"""Options under 1,000/month

    Sensitivity!B11 (one cell).
    Excel (B11): =COUNTIF(B3:F8,"<1000")
    Reads: Sensitivity!B3:F8
    """
    sensitivity = book['Sensitivity']
    sensitivity["B11"] = xl.COUNTIF(sensitivity["B3:F8"], '<1000')