DOCSEARCHbyAlgolia
Submit Docs

xlcalculator

repository·master·Indexed 41 days ago

https://github.com/bradbase/xlcalculator

A Python library that evaluates Excel files by translating Excel functions into Python code, enabling Excel-style calculations without the Excel application. It supports model management, worksheet control, and a wide range of financial, logical, lookup, math, and statistical functions. Users can extend the library by registering custom Excel functions using the @xl.register() and @xl.validate_args decorators.

Tokens
4.1K
Snippets
9
Records
11
Agent score
1%

What's inside xlcalculator

  1. Overview of xlcalculator

    master

    xlcalculator is a Python library designed to read MS Excel files and translate Excel functions into Python code for evaluation. This allows you to perform Excel-style calculations within a Python environment without requiring the Excel application itself.

    Core Capabilities:

    • Model Management: Loading and saving Python-compatible states of Excel files.
    • Worksheet Control: Ability to ignore specific worksheets.
    • Focusing: Extracting sub-portions of a model by focusing on specific cell addresses or defined names.
    • Evaluation:
      • Individual cells
      • Defined Names (named cells or ranges)
      • Ranges
      • Shared formulas (operands: +, -, /, *, ==, <>, <=, >= on cells only)
      • Setting and getting cell values
      • Parsing dictionaries into the Model object

    Current Limitations:

    • Does not support Array Formulas or CSE Formulas (only shared formulas).
    • Specific functions like EXP and DB are not yet supported.
    • VLOOKUP only supports exact matches.
    • LN returns math.log() (which differs from Excel's LN).
    • YEARFRAC with Basis 1 or Actual/actual is accurate only to 3 decimal places.

  2. Set up the xlcalculator development environment

    master

    To set up a development environment for xlcalculator with testing support, use virtualenv and pip:

    # Create a virtual environment with Python 3.10
virtualenv -p 3.10 ve

# Install the package in editable mode with test dependencies
ve/bin/pip install -e .[test]

    To run tests, you can use pytest directly from the root directory:

    ve/bin/py.test -rw -s --tb=native

    Alternatively, use tox for automated testing:

    tox
    # Setup
virtualenv -p 3.10 ve
ve/bin/pip install -e .[test]

# Run tests
ve/bin/py.test -rw -s --tb=native
# OR
tox

  3. Unit testing Excel formulas with FlyingKoala

    master

    For users interested in unit testing formulas directly within their Excel workbooks, the project recommends using FlyingKoala.

    Examples of how to perform unit testing of formulas can be found in the flyingkoala repository under flyingkoala/unit_testing_formulas.

  4. Add or register custom Excel functions

    master

    You can extend xlcalculator by registering your own Python functions as Excel functions using the xlfunctions.xl.register() decorator.

    To ensure robust behavior, use the @xl.validate_args decorator. This decorator automatically converts and validates annotated arguments to match Excel's behavior (e.g., converting a string '1' to a number 1).

    Example Implementation:

    from xlcalculator.xlfunctions import xl

@xl.register()
@xl.validate_args
def ADDONE(num: xl.Number):
    return num + 1

    Testing Requirements for Contributors: If you contribute new functions, you must provide tests in two locations:

    1. tests/xlfunctions: Tests for the Python implementation.
    2. tests/xlfunctions_vs_excel: Comparison tests against actual Excel behavior.

  6. Supported Information functions in xlcalculator

    master

    The following information/diagnostic functions are supported by xlcalculator:

    • ISBLANK
    • ISERR
    • ISERROR
    • ISEVEN
    • ISNA
    • ISNUMBER
    • ISODD
    • ISTEXT
    • NA
    | Function        | xlcalculator |
|-----------------|--------------|
| ISBLANK         |      *       |
| ISERR           |      *       |
| ISERROR         |      *       |
| ISEVEN          |      *       |
| ISNA            |      *       |
| ISNUMBER        |      *       |
| ISODD           |      *       |
| ISTEXT          |      *       |
| NA              |      *       |

  7. Supported Financial functions in xlcalculator

    master

    The following financial functions are supported by xlcalculator (marked with * in the compatibility matrix):

    • IRR (Internal Rate of Return)
    • NPV (Net Present Value)
    • PMT (Payment)
    • PV (Present Value)
    • SLN (Straight-line depreciation)
    • VDB (Variable declining balance)
    • XIRR (Extended Internal Rate of Return)
    • XNPV (Extended Net Present Value)
    | Function        | xlcalculator |
|-----------------|--------------|
| IRR             |      *       |
| NPV             |      *       |
| PMT             |      *       |
| PV              |      *       |
| SLN             |      *       |
| VDB             |      *       |
| XIRR            |      *       |
| XNPV            |      *       |

  8. Supported Lookup and Reference functions in xlcalculator

    master

    The following lookup and reference functions are supported by xlcalculator:

    • CHOOSE
    • MATCH
    • VLOOKUP
    | Function        | xlcalculator |
|-----------------|--------------|
| CHOOSE          |      *       |
| MATCH           |      *       |
| VLOOKUP         |      *       |

  9. Supported Excel Functions in xlcalculator

    master

    The following table lists the Excel functions currently supported by xlcalculator and compares their support with other libraries like PyCel, formulas, and Koala.

    Functionxlcalculator
    CONCATSupported
    CONCATENATESupported
    EXACTSupported
    FINDSupported
    LEFTSupported
    LENSupported
    LOWERSupported
    MIDSupported
    REPLACESupported
    RIGHTSupported
    TRIMSupported
    UPPERSupported
    VALUENot Supported
    | Function        | xlcalculator | PyCel | formulas | Koala |
|-----------------|--------------|-------|----------|-------|
| CONCAT          |      *       |   *   |    *     |   *   |
| CONCATENATE     |      *       |   *   |    *     |   *   |
| EXACT           |      *       |       |          |       |
| FIND            |      *       |   *   |    *     |       |
| LEFT            |      *       |   *   |    *     |       |
| LEN             |      *       |   *   |    *     |       |
| LOWER           |      *       |   *   |    *     |       |
| MID             |      *       |   *   |    *     |   *   |
| REPLACE         |      *       |   *   |    *     |       |
| RIGHT           |      *       |   *   |    *     |       |
| TRIM            |      *       |   *   |    *     |       |
| UPPER           |      *       |   *   |    *     |       |
| VALUE           |              |   *   |          |   *   |

  10. Supported Statistical functions in xlcalculator

    master

    The following Statistical functions are supported by xlcalculator (indicated by * in the compatibility matrix):

    • AVERAGE (Mean)
    • COUNT (Count numbers)
    • COUNTA (Count non-empty cells)
    • COUNTIF (Count based on one criteria)
    • COUNTIFS (Count based on multiple criteria)
    • MAX (Maximum value)
    • MIN (Minimum value)

    Note: Functions like AVERAGEA, AVERAGEIF, LARGE, LINEST, etc., are not supported by xlcalculator.

    | Function        | xlcalculator | PyCel | formulas | Koala |
|-----------------|--------------|-------|----------|-------|
| AVERAGE         |      *       |   *   |     *    |   *   |
| COUNT           |      *       |   *   |    *     |   *   |
| COUNTA          |      *       |       |    *     |   *   |
| COUNTIF         |      *       |   *   |    *     |   *   |
| COUNTIFS        |      *       |   *   |          |       |
| MAX             |      *       |   *   |    *     |   *   |
| MIN             |      *       |   *   |    *     |   *   |

  11. Supported Math and Trigonometry functions in xlcalculator

    master

    The following Math and Trigonometry functions are supported by xlcalculator (indicated by * in the compatibility matrix):

    • ABS (Absolute value)
    • ACOS (Arccosine)
    • ASIN (Arcsine)
    • ASINH (Inverse hyperbolic sine)
    • ATAN (Arctangent)
    • ATAN2 (Arctangent of y/x)
    • CEILING (Round up to nearest multiple)
    • COS (Cosine)
    • COSH (Hyperbolic cosine)
    • DEGREES (Convert radians to degrees)
    • EVEN (Round up to nearest even integer)
    • EXP (Exponential)
    • FACT (Factorial)
    • FACTDOUBLE (Double factorial)
    • INT (Integer part)
    • LN (Natural logarithm)
    • LOG (Logarithm)
    • LOG10 (Base-10 logarithm)
    • MOD (Remainder)
    • PI (Pi)
    • POWER (Exponentiation)
    • RADIANS (Convert degrees to radians)
    • RAND (Random number)
    • RANDBETWEEN (Random integer between bounds)
    • ROUND (Round to specified digits)
    • ROUNDDOWN (Round towards zero)
    • ROUNDUP (Round away from zero)
    • SIGN (Sign of a number)
    • SIN (Sine)
    • SQRT (Square root)
    • SQRTPI (Square root of Pi)
    • SUM (Summation)
    • SUMIF (Sum based on one criteria)
    • SUMIFS (Sum based on multiple criteria)
    • SUMPRODUCT (Sum of products)
    • TAN (Tangent)
    • TRUNC (Truncate)

    Note: Functions like ACOT, ACOSH, COT, CSC, etc., are listed in the compatibility matrix but are not supported by xlcalculator.

    | Function        | xlcalculator | PyCel | formulas | Koala |
|-----------------|--------------|-------|----------|-------|
| ABS             |      *       |   *   |    *     |       |
| ACOS            |      *       |       |    *     |       |
| ASIN            |      *       |       |    *     |       |
| ASINH           |      *       |       |    *     |       |
| ATAN            |      *       |       |    *     |       |
| ATAN2           |      *       |   *   |    *     |       |
| CEILING         |      *       |   *   |    *     |       |
| COS             |      *       |       |    *     |       |
| COSH            |      *       |       |    *     |       |
| DEGREES         |     *        |       |    *     |       |
| EVEN            |      *       |   *   |    *     |       |
| EXP             |      *       |       |    *     |       |
| FACT            |      *       |   *   |    *     |       |
| FACTDOUBLE      |      *       |   *   |    *     |       |
| INT             |      *       |   *   |    *     |       |
| LN              |      *       |   *   |    *     |       |
| LOG             |      *       |   *   |    *     |       |
| LOG10           |      *       |       |    *     |       |
| MOD             |      *       |   *   |    *     |       |
| PI              |      *       |       |    *     |       |
| POWER           |      *       |   *   |    *     |       |
| RADIANS         |      *       |       |    *     |       |
| RAND            |      *       |       |    *     |       |
| RANDBETWEEN     |      *       |       |    *     |       |
| ROUND           |      *       |   *   |    *     |       |
| ROUNDDOWN       |      *       |   *   |    *     |       |
| ROUNDUP         |      *       |   *   |    *     |       |
| SIGN            |      *       |   *   |    *     |       |
| SIN             |      *       |       |    *     |       |
| SQRT            |      *       |       |    *     |       |
| SQRTPI          |      *       |       |    *     |       |
| SUM             |      *       |   *   |    *     |       |
| SUMIF           |      *       |   *   |    *     |       |
| SUMIFS          |      *       |   *   |          |       |
| SUMPRODUCT      |      *       |   *   |    *     |       |
| TAN             |      *       |       |    *     |       |
| TRUNC           |      *       |   *   |    *     |       |