================
Excel Calculator
================
.. image:: https://travis-ci.org/bradbase/xlcalculator.png?branch=master
:target: https://travis-ci.org/bradbase/xlcalculator
.. image:: https://coveralls.io/repos/github/bradbase/xlcalculator/badge.svg?branch=master
:target: https://coveralls.io/github/bradbase/xlcalculator?branch=master
.. image:: https://img.shields.io/pypi/v/xlcalculator.svg
:target: https://pypi.python.org/pypi/xlcalculator
.. image:: https://img.shields.io/pypi/pyversions/xlcalculator.svg
:target: https://pypi.python.org/pypi/xlcalculator/
.. image:: https://img.shields.io/pypi/status/xlcalculator.svg
:target: https://pypi.org/project/xlcalculator/
xlcalculator is a Python library that reads MS Excel files and, to the extent
of supported functions, can translate the Excel functions into Python code and
subsequently evaluate the generated Python code. Essentially doing the Excel
calculations without the need for Excel.
xlcalculator is a modernization of the
`koala2 <https://github.com/vallettea/koala>`_ library.
``xlcalculator`` currently supports:
* Loading an Excel file into a Python compatible state
* Saving Python compatible state
* Loading Python compatible state
* Ignore worksheets
* Extracting sub-portions of a model. "focussing" on provided cell addresses
or defined names
* Evaluating
* Individual cells
* Defined Names (a "named cell" or range)
* Ranges
* Shared formulas `not an Array Formula <https://stackoverflow.com/questions/1256359/what-is-the-difference-between-a-shared-formula-and-an-array-formula>`_
* Operands (+, -, /, \*, ==, <>, <=, >=)
* on cells only
* Set cell value
* Get cell value
* `Parsing a dict into the Model object <https://stackoverflow.com/questions/31260686/excel-formula-evaluation-in-pandas/61586912#61586912>`_
* Code is in examples\\third_party_datastructure
* Functions are at the bottom of this README
* LN
- Python Math.log() differs from Excel LN. Currently returning
Math.log()
* VLOOKUP
- Exact match only
* YEARFRAC
- Basis 1, Actual/actual, is only within 3 decimal places
Not currently supported:
* Array Formulas or CSE Formulas (not a shared formula): https://stackoverflow.com/questions/1256359/what-is-the-difference-between-a-shared-formula-and-an-array-formula or https://support.office.com/en-us/article/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7#ID0EAAEAAA=Office_2013_-_Office_2019)
* Functions required to complete testing as per Microsoft Office Help
website for SQRT and LN
* EXP
* DB
Run tests
---------
Setup your environment::
virtualenv -p 3.10 ve
ve/bin/pip install -e .[test]
From the root xlcalculator directory::
ve/bin/py.test -rw -s --tb=native
Or simply use ``tox``::
tox
Run Example
-----------
From the examples/common_use_case directory::
python use_case_01.py
Adding/Registering Excel Functions
----------------------------------
Excel function support can be easily added.
Fundamental function support is found in the xlfunctions directory. The
functions are thematically organised in modules.
Excel functions can be added by any code using the
``xlfunctions.xl.register()`` decorator. Here is a simple example:
.. code-block:: Python
from xlcalculator.xlfunctions import xl
@xl.register()
@xl.validate_args
def ADDONE(num: xl.Number):
return num + 1
The `@xl.validate_args` decorator will ensure that the annotated arguments are
converted and validated. For example, even if you pass in a string, it is
converted to a number (in typical Excel fashion):
.. code-block:: Python
>>> ADDONE(1):
2
>>> ADDONE('1'):
2
If you would like to contribute functions, please create a pull request. All
new functions should be accompanied by sufficient tests to cover the
functionality. Tests need to be written for both the Python implementation of
the function (tests/xlfunctions) and a comparison with Excel
(tests/xlfunctions_vs_excel).
Excel number precision
----------------------
Excel number precision is a complex discussion.
It has been discussed in a `Wikipedia
page <https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel>`_.
The fundamentals come down to floating point numbers and a contention between
how they are represented in memory Vs how they are stored on disk Vs how they
are presented on screen. A `Microsoft
article <https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/>`_
explains the contention.
This project is attempting to take care while reading numbers from the Excel
file to try and remove a variety of representation errors.
Further work will be required to keep numbers in-line with Excel throughout
different transformations.
From what I can determine this requires a low-level implementation of a
numeric datatype (C or C++, Cython??) to replicate its behaviour. Python
built-in numeric types don't replicate behaviours appropriately.
Unit testing Excel formulas directly from the workbook.
-------------------------------------------------------
If you are interested in unit testing formulas in your workbook, you can use
`FlyingKoala <https://github.com/bradbase/flyingkoala>`_. An example on how can
be found
`here <https://github.com/bradbase/flyingkoala/tree/master/flyingkoala/unit_testing_formulas>`_.
TODO
----
- Do not treat ranges as a granular AST node it instead as an operation ":" of
two cell references to create the range. That will make implementing
features like ``A1:OFFSET(...)`` easy to implement.
- Support for alternative range evaluation: by ref (pointer), by expr (lazy
eval) and current eval mode.
* Pointers would allow easy implementations of functions like OFFSET().
* Lazy evals will allow efficient implementation of IF() since execution
of true and false expressions can be delayed until it is decided which
expression is needed.
- Implement array functions. It is really not that hard once a proper
RangeData class has been implemented on which one can easily act with scalar
functions.
- Improve testing
- Refactor model and evaluator to use pass-by-object-reference for values of
cells which then get "used"/referenced by ranges, defined names and formulas
- Handle multi-file addresses
- Improve integration with pyopenxl for reading and writing files `example of
problem space <https://stackoverflow.com/questions/40248564/pre-calculate-excel-formulas-when-exporting-data-with-python>`_
Supported Functions
-------------------
Compatibility
-------------
+-----------------+--------------+-------+----------+-------+
| Function | xlcalculator | PyCel | formulas | Koala |
+-----------------+--------------+-------+----------+-------+
| FLOOR | * | * | * | |
+-----------------+--------------+-------+----------+-------+
Date and Time
-------------
+-----------------+--------------+-------+----------+-------+
| Function | xlcalculator | PyCel | formulas | Koala |
+-----------------+--------------+-------+----------+-------+
| DATE | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| DATEDIF | * | | | |
+-----------------+--------------+-------+----------+-------+
| DATEVALUE | | * | * | |
+-----------------+--------------+-------+----------+-------+
| DAY | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| DAYS | * | | | |
+-----------------+--------------+-------+----------+-------+
| EDATE | * | * | | |
+-----------------+--------------+-------+----------+-------+
| EOMONTH | * | * | | * |
+-----------------+--------------+-------+----------+-------+
| HOUR | | * | * | |
+-----------------+--------------+-------+----------+-------+
| ISOWEEKNUM | * | | | |
+-----------------+--------------+-------+----------+-------+
| MINUTE | | * | * | |
+-----------------+--------------+-------+----------+-------+
| MONTH | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| NOW | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| SECOND | | * | * | |
+-----------------+--------------+-------+----------+-------+
| TIME | | | * | |
+-----------------+--------------+-------+----------+-------+
| TIMEVALUE | | * | * | |
+-----------------+--------------+-------+----------+-------+
| TODAY | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| WEEKDAY | * | * | | |
+-----------------+--------------+-------+----------+-------+
| YEAR | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| YEARFRAC | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
Engineering
-----------
+-----------------+--------------+-------+----------+-------+
| Function | xlcalculator | PyCel | formulas | Koala |
+-----------------+--------------+-------+----------+-------+
| BIN2DEC | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| BIN2HEX | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| BIN2OCT | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| DEC2BIN | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| DEC2HEX | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| DEC2OCT | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| HEX2BIN | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| HEX2DEC | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| HEX2OCT | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| OCT2BIN | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| OCT2DEC | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| OCT2HEX | * | * | * | |
+-----------------+--------------+-------+----------+-------+
Financial
---------
+-----------------+--------------+-------+----------+-------+
| Function | xlcalculator | PyCel | formulas | Koala |
+-----------------+--------------+-------+----------+-------+
| IRR | * | | * | * |
+-----------------+--------------+-------+----------+-------+
| NPV | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| PMT | * | | | * |
+-----------------+--------------+-------+----------+-------+
| PV | * | | | |
+-----------------+--------------+-------+----------+-------+
| SLN | * | | | * |
+-----------------+--------------+-------+----------+-------+
| VDB | * | | | * |
+-----------------+--------------+-------+----------+-------+
| XIRR | * | | * | * |
+-----------------+--------------+-------+----------+-------+
| XNPV | * | | * | * |
+-----------------+--------------+-------+----------+-------+
Information
-----------
+-----------------+--------------+-------+----------+-------+
| Function | xlcalculator | PyCel | formulas | Koala |
+-----------------+--------------+-------+----------+-------+
| ISBLANK | * | | | * |
+-----------------+--------------+-------+----------+-------+
| ISERR | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| ISERROR | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| ISEVEN | * | * | | |
+-----------------+--------------+-------+----------+-------+
| ISNA | * | * | | * |
+-----------------+--------------+-------+----------+-------+
| ISNUMBER | * | * | | |
+-----------------+--------------+-------+----------+-------+
| ISODD | * | * | | |
+-----------------+--------------+-------+----------+-------+
| ISTEXT | * | * | | * |
+-----------------+--------------+-------+----------+-------+
| NA | * | | * | |
+-----------------+--------------+-------+----------+-------+
Logical
-------
+-----------------+--------------+-------+----------+-------+
| Function | xlcalculator | PyCel | formulas | Koala |
+-----------------+--------------+-------+----------+-------+
| AND | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| FALSE | * | | * | |
+-----------------+--------------+-------+----------+-------+
| IF | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| IFERROR | | * | * | * |
+-----------------+--------------+-------+----------+-------+
| IFS | | * | | |
+-----------------+--------------+-------+----------+-------+
| NOT | | * | * | |
+-----------------+--------------+-------+----------+-------+
| OR | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| SWITCH | | | * | |
+-----------------+--------------+-------+----------+-------+
| TRUE | * | | * | |
+-----------------+--------------+-------+----------+-------+
| XOR | | * | * | |
+-----------------+--------------+-------+----------+-------+
Lookup and reference
--------------------
+-----------------+--------------+-------+----------+-------+
| Function | xlcalculator | PyCel | formulas | Koala |
+-----------------+--------------+-------+----------+-------+
| CHOOSE | * | | | * |
+-----------------+--------------+-------+----------+-------+
| COLUMN | | * | * | |
+-----------------+--------------+-------+----------+-------+
| COLUMNS | | | | * |
+-----------------+--------------+-------+----------+-------+
| HLOOKUP | | * | * | |
+-----------------+--------------+-------+----------+-------+
| INDEX | | * | * | * |
+-----------------+--------------+-------+----------+-------+
| INDIRECT | | * | | |
+-----------------+--------------+-------+----------+-------+
| LOOKUP | | * | * | * |
+-----------------+--------------+-------+----------+-------+
| MATCH | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| OFFSET | | * | | * |
+-----------------+--------------+-------+----------+-------+
| ROW | | * | * | |
+-----------------+--------------+-------+----------+-------+
| ROWS | | | | * |
+-----------------+--------------+-------+----------+-------+
| VLOOKUP | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
Math and Trigonometry
---------------------
+-----------------+--------------+-------+----------+-------+
| Function | xlcalculator | PyCel | formulas | Koala |
+-----------------+--------------+-------+----------+-------+
| ABS | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| ACOS | * | | * | |
+-----------------+--------------+-------+----------+-------+
| ACOSH | * | | * | |
+-----------------+--------------+-------+----------+-------+
| ACOT | | | * | |
+-----------------+--------------+-------+----------+-------+
| ACOTH | | | * | |
+-----------------+--------------+-------+----------+-------+
| ARABIC | | | * | |
+-----------------+--------------+-------+----------+-------+
| ASIN | * | | * | |
+-----------------+--------------+-------+----------+-------+
| ASINH | * | | * | |
+-----------------+--------------+-------+----------+-------+
| ATAN | * | | * | |
+-----------------+--------------+-------+----------+-------+
| ATAN2 | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| ATANH | | | * | |
+-----------------+--------------+-------+----------+-------+
| CEILING | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| CEILING.MATH | | * | * | |
+-----------------+--------------+-------+----------+-------+
| CEILING.PRECISE | | * | * | |
+-----------------+--------------+-------+----------+-------+
| COS | * | | * | |
+-----------------+--------------+-------+----------+-------+
| COSH | * | | * | |
+-----------------+--------------+-------+----------+-------+
| COT | | | * | |
+-----------------+--------------+-------+----------+-------+
| COTH | | | * | |
+-----------------+--------------+-------+----------+-------+
| CSC | | | * | |
+-----------------+--------------+-------+----------+-------+
| CSCH | | | * | |
+-----------------+--------------+-------+----------+-------+
| DECIMAL | | | * | |
+-----------------+--------------+-------+----------+-------+
| DEGREES | * | | * | |
+-----------------+--------------+-------+----------+-------+
| EVEN | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| EXP | * | | * | |
+-----------------+--------------+-------+----------+-------+
| FACT | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| FACTDOUBLE | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| FLOOR.MATH | | * | * | |
+-----------------+--------------+-------+----------+-------+
| FLOOR.PRECISE | | * | * | |
+-----------------+--------------+-------+----------+-------+
| GCD | | | * | |
+-----------------+--------------+-------+----------+-------+
| INT | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| ISO.CEILING | | | * | |
+-----------------+--------------+-------+----------+-------+
| LCM | | | * | |
+-----------------+--------------+-------+----------+-------+
| LN | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| LOG | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| LOG10 | * | | * | |
+-----------------+--------------+-------+----------+-------+
| MOD | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| MROUND | | | * | |
+-----------------+--------------+-------+----------+-------+
| ODD | | * | * | |
+-----------------+--------------+-------+----------+-------+
| PI | * | | * | |
+-----------------+--------------+-------+----------+-------+
| POWER | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| RADIANS | * | | * | |
+-----------------+--------------+-------+----------+-------+
| RAND | * | | * | * |
+-----------------+--------------+-------+----------+-------+
| RANDBETWEEN | * | | * | * |
+-----------------+--------------+-------+----------+-------+
| ROMAN | | | * | |
+-----------------+--------------+-------+----------+-------+
| ROUND | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| ROUNDDOWN | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| ROUNDUP | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| SEC | | | * | |
+-----------------+--------------+-------+----------+-------+
| SECH | | | * | |
+-----------------+--------------+-------+----------+-------+
| SIGN | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| SIN | * | | * | |
+-----------------+--------------+-------+----------+-------+
| SINH | | | * | |
+-----------------+--------------+-------+----------+-------+
| SQRT | * | | * | * |
+-----------------+--------------+-------+----------+-------+
| SQRTPI | * | | * | |
+-----------------+--------------+-------+----------+-------+
| SUM | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| SUMIF | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| SUMIFS | * | * | | * |
+-----------------+--------------+-------+----------+-------+
| SUMPRODUCT | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| TAN | * | | * | |
+-----------------+--------------+-------+----------+-------+
| TANH | | | * | |
+-----------------+--------------+-------+----------+-------+
| TRUNC | * | * | * | |
+-----------------+--------------+-------+----------+-------+
Statistical
-----------
+-----------------+--------------+-------+----------+-------+
| Function | xlcalculator | PyCel | formulas | Koala |
+-----------------+--------------+-------+----------+-------+
| AVERAGE | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| AVERAGEA | | | * | |
+-----------------+--------------+-------+----------+-------+
| AVERAGEIF | | * | * | |
+-----------------+--------------+-------+----------+-------+
| AVERAGEIFS | | * | | |
+-----------------+--------------+-------+----------+-------+
| COUNT | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| COUNTA | * | | * | * |
+-----------------+--------------+-------+----------+-------+
| COUNTBLANK | | | * | |
+-----------------+--------------+-------+----------+-------+
| COUNTIF | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| COUNTIFS | * | * | | * |
+-----------------+--------------+-------+----------+-------+
| LARGE | | * | * | |
+-----------------+--------------+-------+----------+-------+
| LINEST | | * | | * |
+-----------------+--------------+-------+----------+-------+
| MAX | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| MAXA | | | * | |
+-----------------+--------------+-------+----------+-------+
| MAXIFS | | * | | |
+-----------------+--------------+-------+----------+-------+
| MIN | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| MINA | | | * | |
+-----------------+--------------+-------+----------+-------+
| MINIFS | | * | | |
+-----------------+--------------+-------+----------+-------+
| SMALL | | * | * | |
+-----------------+--------------+-------+----------+-------+
Text
----
+-----------------+--------------+-------+----------+-------+
| Function | xlcalculator | PyCel | formulas | Koala |
+-----------------+--------------+-------+----------+-------+
| CONCAT | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| CONCATENATE | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| EXACT | * | | | |
+-----------------+--------------+-------+----------+-------+
| FIND | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| LEFT | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| LEN | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| LOWER | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| MID | * | * | * | * |
+-----------------+--------------+-------+----------+-------+
| REPLACE | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| RIGHT | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| TRIM | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| UPPER | * | * | * | |
+-----------------+--------------+-------+----------+-------+
| VALUE | | * | | * |
+-----------------+--------------+-------+----------+-------+
=======
CHANGES
=======
0.5.0 (2023-02-06)
------------------
- Added support for Python 3.10, dropped 3.8.
- Upgraded requirements.txt to latest versions.
* `yearfrac==0.4.4` was incompatible with latest setuptools.
* `openpyxl` had API changes that were addressed and tests fixed.
0.4.2 (2021-05-17)
------------------
- Make sure that decimal rounding is only set in context and not system wide.
0.4.1 (2021-05-14)
------------------
- Fixed cross-sheet references.
0.4.0 (2021-05-13)
------------------
- Pass ``ignore_hidden`` from ``read_and_parse_archive()`` to
``parse_archive()``
- Add Excel tests for ``IF()``.
- Add ``NOT()`` function.
- Implemented ``BIN2OCT()``, ``BIN2DEC()``, ``BIN2HEX()``, ``OCT2BIN()``,
``OCT2DEC()``, ``OCT2HEX()``, ``DEC2BIN()``, ``DEC2OCT()``, ``DEC2HEX()``,
``HEX2BIN()``, ``HEX2OCT()``, ``HEX2DEC()``.
- Drop Python 3.7 support.
0.3.0 (2021-05-13)
------------------
- Add support for cross-sheet references.
- Make ``*IF()`` functions case insensitive to properly adhere to Excel specs.
- Support for Python 3.9.
0.2.13 (2020-12-02)
-------------------
- Add functions: ``FALSE()``, ``TRUE()``, ``ATAN2()``, ``ACOS()``,
``DEGREES()``, ``ARCCOSH()``, ``ASIN()``, ``ASINH()``, ``ATAN()``,
``CEILING()``, ``COS()``, ``RADIANS()``, ``COSH()``, ``EXP()``, ``EVEN()``,
``FACT()``, ``FACTDOUBLE()``, ``INT()``, ``LOG()``, ``LOG10()``. ``RAND()``,
``RANDBETWRRN()``, ``SIGN()``, ``SIN()``, ``SQRTPI()``, ``TAN()``
0.2.12 (2020-11-28)
-------------------
- Add functions: ``PV()``, ``XIRR()``, ``ISEVEN()``, ``ISODD()``,
``ISNUMBER()``, ``ISERROR()``, ``FLOOR()``, ``ISERR()``
- Bugfix unary operator needed to be right associated to handle cases of
double use eg; double-negative.. --4 == 4
0.2.11 (2020-11-16)
-------------------
- Add functions: ``DAY()``, ``YEAR()``, ``MONTH()``, ``NOW()``, ``WEEKDAY()``
``EDATE()``, ``EOMONTH()``, ``DAYS()``, ``ISOWEEKNUM()``, ``DATEDIF()``
``FIND()``, ``LEFT()``, ``LEN()``, ``LOWER()``, ``REPLACE()``, ``TRIM()``
``UPPER()``, ``EXACT()``
0.2.10 (2020-10-30)
-------------------
- Support CONCATENATE
- Update setup.py classifiers, licence and keywords
0.2.9 (2020-09-26)
------------------
- Bugfix ModelCompiler.read_and_parse_dict() where a dict being parsed into a
Model through ModelCompiler was triggering AttributeError on calling
xlcalculator.xlfunctions.xl. It's a leftover from moving xlfunctions into
xlcalculator. There has been a test included.
0.2.8 (2020-09-22)
------------------
- Fix implementation of ``ISNA()`` and ``NA()``.
- Impement ``MATCH()``.
0.2.7 (2020-09-22)
------------------
- Add functions: ``ISBLANK()``, ``ISNA()``, ``ISTEXT()``, ``NA()``
0.2.6 (2020-09-21)
------------------
- Add ``COUNTIIF()`` and ``COUNTIFS()`` function support.
0.2.5 (2020-09-21)
------------------
- Add ``SUMIFS()`` support.
0.2.4 (2020-09-09)
------------------
- Updated README with supported functions.
- Fix bug in ModelCompiler extract method where a defined name cell was being
overwritten with the cell from one of the terms contained within the formula.
Added a test for this.
- Move version of yearfrac to 0.4.4. That project has removed a dependency
on the package six.
0.2.3 (2020-08-18)
------------------
- In-boarded xlfunctions.
- Bugfix COUNTA.
* Now supports 256 arguments.
- Updated README. Includes words on xlfunction.
- Changed licence from GPL-3 style to MIT Style.
0.2.2 (2020-05-28)
------------------
- Make dependency resolution part of the execution.
* AST eval'ing takes care of depedency resolution.
* Provide cycle detection with reporting.
* Implemented a specific evaluation context. That makes cache control,
namespace customization and data encapsulation much easier.
- Add more tokenizer tests to increase coverage.
0.2.1 (2020-05-28)
------------------
- Use a less intrusive way to patch ``openpyxl``. Instead of permanently
patching the reader to support cached formula values, ``mock`` is used to
only patch the reader while reading the workbook.
This way the patches do not interfere with other packages not expecting
these new classes.
0.2.0 (2020-05-28)
------------------
- Support for delayed node evaluation by wrapping them into expressions. The
function will eval the expression when needed.
- Support for native Excel data types.
- Enable and update Excel file based function tests that are now working
properly.
- Flake8 source code.
0.1.0 (2020-05-25)
------------------
- Refactored ``xlcalculator`` types to be more compact.
- Reimplemented evaluation engine to not generate Python code anymore, but
build a proper AST from the AST nodes. Each AST node supports an `eval()`
function that knows how to compute a result.
This removes a lot of complexities around trying to determine the evaluation
context at code creation time and encoding the context as part of the
generated code.
- Removal of all special function handling.
- Use of new `xlfunctions` implementation.
- Use Openpyxl to load the Excel files. This provides shared formula support
for free.
0.0.1b (2020-05-03)
-------------------
- Initial release.
Raw data
{
"_id": null,
"home_page": "https://github.com/bradbase/xlcalculator",
"name": "xlcalculator",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.9",
"maintainer_email": "",
"keywords": "xls,Excel,spreadsheet,workbook,data analysis,analysisreading excel,excel formula,excel formulas,excel equations,excel equation,formula,formulas,equation,equations,timeseries,time series,research,scenario analysis,scenario,modelling,model,unit testing,testing,audit,calculation,evaluation,data science,openpyxl",
"author": "Bradley van Ree",
"author_email": "brads@bradbase.net",
"download_url": "https://files.pythonhosted.org/packages/de/07/88e3f269c032c089636176ae97d8915e2390312413a2df8f6809d6ccd784/xlcalculator-0.5.0.tar.gz",
"platform": null,
"description": "================\nExcel Calculator\n================\n\n\n.. image:: https://travis-ci.org/bradbase/xlcalculator.png?branch=master\n :target: https://travis-ci.org/bradbase/xlcalculator\n\n.. image:: https://coveralls.io/repos/github/bradbase/xlcalculator/badge.svg?branch=master\n :target: https://coveralls.io/github/bradbase/xlcalculator?branch=master\n\n.. image:: https://img.shields.io/pypi/v/xlcalculator.svg\n :target: https://pypi.python.org/pypi/xlcalculator\n\n.. image:: https://img.shields.io/pypi/pyversions/xlcalculator.svg\n :target: https://pypi.python.org/pypi/xlcalculator/\n\n.. image:: https://img.shields.io/pypi/status/xlcalculator.svg\n :target: https://pypi.org/project/xlcalculator/\n\nxlcalculator is a Python library that reads MS Excel files and, to the extent\nof supported functions, can translate the Excel functions into Python code and\nsubsequently evaluate the generated Python code. Essentially doing the Excel\ncalculations without the need for Excel.\n\nxlcalculator is a modernization of the\n`koala2 <https://github.com/vallettea/koala>`_ library.\n\n``xlcalculator`` currently supports:\n\n* Loading an Excel file into a Python compatible state\n* Saving Python compatible state\n* Loading Python compatible state\n* Ignore worksheets\n* Extracting sub-portions of a model. \"focussing\" on provided cell addresses\n or defined names\n* Evaluating\n\n * Individual cells\n * Defined Names (a \"named cell\" or range)\n * Ranges\n * Shared formulas `not an Array Formula <https://stackoverflow.com/questions/1256359/what-is-the-difference-between-a-shared-formula-and-an-array-formula>`_\n\n * Operands (+, -, /, \\*, ==, <>, <=, >=)\n * on cells only\n\n * Set cell value\n * Get cell value\n * `Parsing a dict into the Model object <https://stackoverflow.com/questions/31260686/excel-formula-evaluation-in-pandas/61586912#61586912>`_\n\n * Code is in examples\\\\third_party_datastructure\n\n * Functions are at the bottom of this README\n\n * LN\n - Python Math.log() differs from Excel LN. Currently returning\n Math.log()\n\n * VLOOKUP\n - Exact match only\n\n * YEARFRAC\n - Basis 1, Actual/actual, is only within 3 decimal places\n\nNot currently supported:\n\n * Array Formulas or CSE Formulas (not a shared formula): https://stackoverflow.com/questions/1256359/what-is-the-difference-between-a-shared-formula-and-an-array-formula or https://support.office.com/en-us/article/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7#ID0EAAEAAA=Office_2013_-_Office_2019)\n\n * Functions required to complete testing as per Microsoft Office Help\n website for SQRT and LN\n * EXP\n * DB\n\nRun tests\n---------\n\nSetup your environment::\n\n virtualenv -p 3.10 ve\n ve/bin/pip install -e .[test]\n\nFrom the root xlcalculator directory::\n\n ve/bin/py.test -rw -s --tb=native\n\nOr simply use ``tox``::\n\n tox\n\n\nRun Example\n-----------\n\nFrom the examples/common_use_case directory::\n\n python use_case_01.py\n\n\n\nAdding/Registering Excel Functions\n----------------------------------\n\nExcel function support can be easily added.\n\nFundamental function support is found in the xlfunctions directory. The\nfunctions are thematically organised in modules.\n\nExcel functions can be added by any code using the\n``xlfunctions.xl.register()`` decorator. Here is a simple example:\n\n.. code-block:: Python\n\n from xlcalculator.xlfunctions import xl\n\n @xl.register()\n @xl.validate_args\n def ADDONE(num: xl.Number):\n return num + 1\n\nThe `@xl.validate_args` decorator will ensure that the annotated arguments are\nconverted and validated. For example, even if you pass in a string, it is\nconverted to a number (in typical Excel fashion):\n\n.. code-block:: Python\n\n >>> ADDONE(1):\n 2\n >>> ADDONE('1'):\n 2\n\nIf you would like to contribute functions, please create a pull request. All\nnew functions should be accompanied by sufficient tests to cover the\nfunctionality. Tests need to be written for both the Python implementation of\nthe function (tests/xlfunctions) and a comparison with Excel\n(tests/xlfunctions_vs_excel).\n\n\n\nExcel number precision\n----------------------\n\nExcel number precision is a complex discussion.\n\nIt has been discussed in a `Wikipedia\npage <https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel>`_.\n\nThe fundamentals come down to floating point numbers and a contention between\nhow they are represented in memory Vs how they are stored on disk Vs how they\nare presented on screen. A `Microsoft\narticle <https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/>`_\nexplains the contention.\n\nThis project is attempting to take care while reading numbers from the Excel\nfile to try and remove a variety of representation errors.\n\nFurther work will be required to keep numbers in-line with Excel throughout\ndifferent transformations.\n\nFrom what I can determine this requires a low-level implementation of a\nnumeric datatype (C or C++, Cython??) to replicate its behaviour. Python\nbuilt-in numeric types don't replicate behaviours appropriately.\n\n\nUnit testing Excel formulas directly from the workbook.\n-------------------------------------------------------\n\nIf you are interested in unit testing formulas in your workbook, you can use\n`FlyingKoala <https://github.com/bradbase/flyingkoala>`_. An example on how can\nbe found\n`here <https://github.com/bradbase/flyingkoala/tree/master/flyingkoala/unit_testing_formulas>`_.\n\n\nTODO\n----\n\n- Do not treat ranges as a granular AST node it instead as an operation \":\" of\n two cell references to create the range. That will make implementing\n features like ``A1:OFFSET(...)`` easy to implement.\n\n- Support for alternative range evaluation: by ref (pointer), by expr (lazy\n eval) and current eval mode.\n\n * Pointers would allow easy implementations of functions like OFFSET().\n\n * Lazy evals will allow efficient implementation of IF() since execution\n of true and false expressions can be delayed until it is decided which\n expression is needed.\n\n- Implement array functions. It is really not that hard once a proper\n RangeData class has been implemented on which one can easily act with scalar\n functions.\n\n- Improve testing\n\n- Refactor model and evaluator to use pass-by-object-reference for values of\n cells which then get \"used\"/referenced by ranges, defined names and formulas\n\n- Handle multi-file addresses\n\n- Improve integration with pyopenxl for reading and writing files `example of\n problem space <https://stackoverflow.com/questions/40248564/pre-calculate-excel-formulas-when-exporting-data-with-python>`_\n\n\n\nSupported Functions\n-------------------\n\n\nCompatibility\n-------------\n\n\n +-----------------+--------------+-------+----------+-------+\n | Function | xlcalculator | PyCel | formulas | Koala |\n +-----------------+--------------+-------+----------+-------+\n | FLOOR | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n\n\nDate and Time\n-------------\n\n +-----------------+--------------+-------+----------+-------+\n | Function | xlcalculator | PyCel | formulas | Koala |\n +-----------------+--------------+-------+----------+-------+\n | DATE | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | DATEDIF | * | | | |\n +-----------------+--------------+-------+----------+-------+\n | DATEVALUE | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | DAY | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | DAYS | * | | | |\n +-----------------+--------------+-------+----------+-------+\n | EDATE | * | * | | |\n +-----------------+--------------+-------+----------+-------+\n | EOMONTH | * | * | | * |\n +-----------------+--------------+-------+----------+-------+\n | HOUR | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | ISOWEEKNUM | * | | | |\n +-----------------+--------------+-------+----------+-------+\n | MINUTE | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | MONTH | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | NOW | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | SECOND | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | TIME | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | TIMEVALUE | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | TODAY | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | WEEKDAY | * | * | | |\n +-----------------+--------------+-------+----------+-------+\n | YEAR | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | YEARFRAC | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n\n\nEngineering\n-----------\n\n\n +-----------------+--------------+-------+----------+-------+\n | Function | xlcalculator | PyCel | formulas | Koala |\n +-----------------+--------------+-------+----------+-------+\n | BIN2DEC | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | BIN2HEX | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | BIN2OCT | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | DEC2BIN | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | DEC2HEX | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | DEC2OCT | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | HEX2BIN | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | HEX2DEC | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | HEX2OCT | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | OCT2BIN | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | OCT2DEC | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | OCT2HEX | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n\n\nFinancial\n---------\n\n\n +-----------------+--------------+-------+----------+-------+\n | Function | xlcalculator | PyCel | formulas | Koala |\n +-----------------+--------------+-------+----------+-------+\n | IRR | * | | * | * |\n +-----------------+--------------+-------+----------+-------+\n | NPV | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | PMT | * | | | * |\n +-----------------+--------------+-------+----------+-------+\n | PV | * | | | |\n +-----------------+--------------+-------+----------+-------+\n | SLN | * | | | * |\n +-----------------+--------------+-------+----------+-------+\n | VDB | * | | | * |\n +-----------------+--------------+-------+----------+-------+\n | XIRR | * | | * | * |\n +-----------------+--------------+-------+----------+-------+\n | XNPV | * | | * | * |\n +-----------------+--------------+-------+----------+-------+\n\n\nInformation\n-----------\n\n\n +-----------------+--------------+-------+----------+-------+\n | Function | xlcalculator | PyCel | formulas | Koala |\n +-----------------+--------------+-------+----------+-------+\n | ISBLANK | * | | | * |\n +-----------------+--------------+-------+----------+-------+\n | ISERR | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | ISERROR | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | ISEVEN | * | * | | |\n +-----------------+--------------+-------+----------+-------+\n | ISNA | * | * | | * |\n +-----------------+--------------+-------+----------+-------+\n | ISNUMBER | * | * | | |\n +-----------------+--------------+-------+----------+-------+\n | ISODD | * | * | | |\n +-----------------+--------------+-------+----------+-------+\n | ISTEXT | * | * | | * |\n +-----------------+--------------+-------+----------+-------+\n | NA | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n\n\nLogical\n-------\n\n\n +-----------------+--------------+-------+----------+-------+\n | Function | xlcalculator | PyCel | formulas | Koala |\n +-----------------+--------------+-------+----------+-------+\n | AND | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | FALSE | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n | IF | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | IFERROR | | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | IFS | | * | | |\n +-----------------+--------------+-------+----------+-------+\n | NOT | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | OR | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | SWITCH | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | TRUE | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n | XOR | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n\n\nLookup and reference\n--------------------\n\n\n +-----------------+--------------+-------+----------+-------+\n | Function | xlcalculator | PyCel | formulas | Koala |\n +-----------------+--------------+-------+----------+-------+\n | CHOOSE | * | | | * |\n +-----------------+--------------+-------+----------+-------+\n | COLUMN | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | COLUMNS | | | | * |\n +-----------------+--------------+-------+----------+-------+\n | HLOOKUP | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | INDEX | | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | INDIRECT | | * | | |\n +-----------------+--------------+-------+----------+-------+\n | LOOKUP | | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | MATCH | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | OFFSET | | * | | * |\n +-----------------+--------------+-------+----------+-------+\n | ROW | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | ROWS | | | | * |\n +-----------------+--------------+-------+----------+-------+\n | VLOOKUP | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n\n\nMath and Trigonometry\n---------------------\n\n\n +-----------------+--------------+-------+----------+-------+\n | Function | xlcalculator | PyCel | formulas | Koala |\n +-----------------+--------------+-------+----------+-------+\n | ABS | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | ACOS | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n | ACOSH | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n | ACOT | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | ACOTH | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | ARABIC | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | ASIN | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n | ASINH | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n | ATAN | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n | ATAN2 | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | ATANH | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | CEILING | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | CEILING.MATH | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | CEILING.PRECISE | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | COS | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n | COSH | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n | COT | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | COTH | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | CSC | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | CSCH | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | DECIMAL | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | DEGREES | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n | EVEN | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | EXP | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n | FACT | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | FACTDOUBLE | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | FLOOR.MATH | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | FLOOR.PRECISE | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | GCD | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | INT | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | ISO.CEILING | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | LCM | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | LN | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | LOG | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | LOG10 | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n | MOD | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | MROUND | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | ODD | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | PI | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n | POWER | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | RADIANS | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n | RAND | * | | * | * |\n +-----------------+--------------+-------+----------+-------+\n | RANDBETWEEN | * | | * | * |\n +-----------------+--------------+-------+----------+-------+\n | ROMAN | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | ROUND | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | ROUNDDOWN | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | ROUNDUP | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | SEC | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | SECH | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | SIGN | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | SIN | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n | SINH | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | SQRT | * | | * | * |\n +-----------------+--------------+-------+----------+-------+\n | SQRTPI | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n | SUM | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | SUMIF | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | SUMIFS | * | * | | * |\n +-----------------+--------------+-------+----------+-------+\n | SUMPRODUCT | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | TAN | * | | * | |\n +-----------------+--------------+-------+----------+-------+\n | TANH | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | TRUNC | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n\n\nStatistical\n-----------\n\n\n +-----------------+--------------+-------+----------+-------+\n | Function | xlcalculator | PyCel | formulas | Koala |\n +-----------------+--------------+-------+----------+-------+\n | AVERAGE | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | AVERAGEA | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | AVERAGEIF | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | AVERAGEIFS | | * | | |\n +-----------------+--------------+-------+----------+-------+\n | COUNT | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | COUNTA | * | | * | * |\n +-----------------+--------------+-------+----------+-------+\n | COUNTBLANK | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | COUNTIF | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | COUNTIFS | * | * | | * |\n +-----------------+--------------+-------+----------+-------+\n | LARGE | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | LINEST | | * | | * |\n +-----------------+--------------+-------+----------+-------+\n | MAX | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | MAXA | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | MAXIFS | | * | | |\n +-----------------+--------------+-------+----------+-------+\n | MIN | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | MINA | | | * | |\n +-----------------+--------------+-------+----------+-------+\n | MINIFS | | * | | |\n +-----------------+--------------+-------+----------+-------+\n | SMALL | | * | * | |\n +-----------------+--------------+-------+----------+-------+\n\n\nText\n----\n\n\n +-----------------+--------------+-------+----------+-------+\n | Function | xlcalculator | PyCel | formulas | Koala |\n +-----------------+--------------+-------+----------+-------+\n | CONCAT | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | CONCATENATE | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | EXACT | * | | | |\n +-----------------+--------------+-------+----------+-------+\n | FIND | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | LEFT | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | LEN | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | LOWER | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | MID | * | * | * | * |\n +-----------------+--------------+-------+----------+-------+\n | REPLACE | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | RIGHT | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | TRIM | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | UPPER | * | * | * | |\n +-----------------+--------------+-------+----------+-------+\n | VALUE | | * | | * |\n +-----------------+--------------+-------+----------+-------+\n\n\n=======\nCHANGES\n=======\n\n0.5.0 (2023-02-06)\n------------------\n\n- Added support for Python 3.10, dropped 3.8.\n\n- Upgraded requirements.txt to latest versions.\n\n * `yearfrac==0.4.4` was incompatible with latest setuptools.\n\n * `openpyxl` had API changes that were addressed and tests fixed.\n\n\n0.4.2 (2021-05-17)\n------------------\n\n- Make sure that decimal rounding is only set in context and not system wide.\n\n0.4.1 (2021-05-14)\n------------------\n\n- Fixed cross-sheet references.\n\n\n0.4.0 (2021-05-13)\n------------------\n\n- Pass ``ignore_hidden`` from ``read_and_parse_archive()`` to\n ``parse_archive()``\n\n- Add Excel tests for ``IF()``.\n\n- Add ``NOT()`` function.\n\n- Implemented ``BIN2OCT()``, ``BIN2DEC()``, ``BIN2HEX()``, ``OCT2BIN()``,\n ``OCT2DEC()``, ``OCT2HEX()``, ``DEC2BIN()``, ``DEC2OCT()``, ``DEC2HEX()``,\n ``HEX2BIN()``, ``HEX2OCT()``, ``HEX2DEC()``.\n\n- Drop Python 3.7 support.\n\n\n0.3.0 (2021-05-13)\n------------------\n\n- Add support for cross-sheet references.\n\n- Make ``*IF()`` functions case insensitive to properly adhere to Excel specs.\n\n- Support for Python 3.9.\n\n\n0.2.13 (2020-12-02)\n-------------------\n\n- Add functions: ``FALSE()``, ``TRUE()``, ``ATAN2()``, ``ACOS()``,\n ``DEGREES()``, ``ARCCOSH()``, ``ASIN()``, ``ASINH()``, ``ATAN()``,\n ``CEILING()``, ``COS()``, ``RADIANS()``, ``COSH()``, ``EXP()``, ``EVEN()``,\n ``FACT()``, ``FACTDOUBLE()``, ``INT()``, ``LOG()``, ``LOG10()``. ``RAND()``,\n ``RANDBETWRRN()``, ``SIGN()``, ``SIN()``, ``SQRTPI()``, ``TAN()``\n\n\n0.2.12 (2020-11-28)\n-------------------\n\n- Add functions: ``PV()``, ``XIRR()``, ``ISEVEN()``, ``ISODD()``,\n ``ISNUMBER()``, ``ISERROR()``, ``FLOOR()``, ``ISERR()``\n- Bugfix unary operator needed to be right associated to handle cases of\n double use eg; double-negative.. --4 == 4\n\n\n0.2.11 (2020-11-16)\n-------------------\n\n- Add functions: ``DAY()``, ``YEAR()``, ``MONTH()``, ``NOW()``, ``WEEKDAY()``\n ``EDATE()``, ``EOMONTH()``, ``DAYS()``, ``ISOWEEKNUM()``, ``DATEDIF()``\n ``FIND()``, ``LEFT()``, ``LEN()``, ``LOWER()``, ``REPLACE()``, ``TRIM()``\n ``UPPER()``, ``EXACT()``\n\n\n0.2.10 (2020-10-30)\n-------------------\n\n- Support CONCATENATE\n- Update setup.py classifiers, licence and keywords\n\n\n0.2.9 (2020-09-26)\n------------------\n\n- Bugfix ModelCompiler.read_and_parse_dict() where a dict being parsed into a\n Model through ModelCompiler was triggering AttributeError on calling\n xlcalculator.xlfunctions.xl. It's a leftover from moving xlfunctions into\n xlcalculator. There has been a test included.\n\n\n0.2.8 (2020-09-22)\n------------------\n\n- Fix implementation of ``ISNA()`` and ``NA()``.\n\n- Impement ``MATCH()``.\n\n\n0.2.7 (2020-09-22)\n------------------\n\n- Add functions: ``ISBLANK()``, ``ISNA()``, ``ISTEXT()``, ``NA()``\n\n\n0.2.6 (2020-09-21)\n------------------\n\n- Add ``COUNTIIF()`` and ``COUNTIFS()`` function support.\n\n\n0.2.5 (2020-09-21)\n------------------\n\n- Add ``SUMIFS()`` support.\n\n\n0.2.4 (2020-09-09)\n------------------\n\n- Updated README with supported functions.\n\n- Fix bug in ModelCompiler extract method where a defined name cell was being\n overwritten with the cell from one of the terms contained within the formula.\n Added a test for this.\n\n- Move version of yearfrac to 0.4.4. That project has removed a dependency\n on the package six.\n\n\n0.2.3 (2020-08-18)\n------------------\n\n- In-boarded xlfunctions.\n\n- Bugfix COUNTA.\n\n * Now supports 256 arguments.\n\n- Updated README. Includes words on xlfunction.\n\n- Changed licence from GPL-3 style to MIT Style.\n\n\n0.2.2 (2020-05-28)\n------------------\n\n- Make dependency resolution part of the execution.\n\n * AST eval'ing takes care of depedency resolution.\n\n * Provide cycle detection with reporting.\n\n * Implemented a specific evaluation context. That makes cache control,\n namespace customization and data encapsulation much easier.\n\n- Add more tokenizer tests to increase coverage.\n\n\n0.2.1 (2020-05-28)\n------------------\n\n- Use a less intrusive way to patch ``openpyxl``. Instead of permanently\n patching the reader to support cached formula values, ``mock`` is used to\n only patch the reader while reading the workbook.\n\n This way the patches do not interfere with other packages not expecting\n these new classes.\n\n\n0.2.0 (2020-05-28)\n------------------\n\n- Support for delayed node evaluation by wrapping them into expressions. The\n function will eval the expression when needed.\n\n- Support for native Excel data types.\n\n- Enable and update Excel file based function tests that are now working\n properly.\n\n- Flake8 source code.\n\n\n0.1.0 (2020-05-25)\n------------------\n\n- Refactored ``xlcalculator`` types to be more compact.\n\n- Reimplemented evaluation engine to not generate Python code anymore, but\n build a proper AST from the AST nodes. Each AST node supports an `eval()`\n function that knows how to compute a result.\n\n This removes a lot of complexities around trying to determine the evaluation\n context at code creation time and encoding the context as part of the\n generated code.\n\n- Removal of all special function handling.\n\n- Use of new `xlfunctions` implementation.\n\n- Use Openpyxl to load the Excel files. This provides shared formula support\n for free.\n\n\n0.0.1b (2020-05-03)\n-------------------\n\n- Initial release.\n\n\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Converts MS Excel formulas to Python and evaluates them.",
"version": "0.5.0",
"split_keywords": [
"xls",
"excel",
"spreadsheet",
"workbook",
"data analysis",
"analysisreading excel",
"excel formula",
"excel formulas",
"excel equations",
"excel equation",
"formula",
"formulas",
"equation",
"equations",
"timeseries",
"time series",
"research",
"scenario analysis",
"scenario",
"modelling",
"model",
"unit testing",
"testing",
"audit",
"calculation",
"evaluation",
"data science",
"openpyxl"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "de0788e3f269c032c089636176ae97d8915e2390312413a2df8f6809d6ccd784",
"md5": "51a974dcc080edc4bd73ac5236dd2595",
"sha256": "5ea7337c3a86b0efcc1508d96345bf417387181344428be0a745e80a1fe9d1fc"
},
"downloads": -1,
"filename": "xlcalculator-0.5.0.tar.gz",
"has_sig": false,
"md5_digest": "51a974dcc080edc4bd73ac5236dd2595",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.9",
"size": 458397,
"upload_time": "2023-02-06T13:40:37",
"upload_time_iso_8601": "2023-02-06T13:40:37.605213Z",
"url": "https://files.pythonhosted.org/packages/de/07/88e3f269c032c089636176ae97d8915e2390312413a2df8f6809d6ccd784/xlcalculator-0.5.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-02-06 13:40:37",
"github": true,
"gitlab": false,
"bitbucket": false,
"github_user": "bradbase",
"github_project": "xlcalculator",
"travis_ci": true,
"coveralls": true,
"github_actions": false,
"requirements": [
{
"name": null,
"specs": []
},
{
"name": "attrs",
"specs": [
[
"==",
"22.2.0"
]
]
},
{
"name": "build",
"specs": [
[
"==",
"0.10.0"
]
]
},
{
"name": "click",
"specs": [
[
"==",
"8.1.3"
]
]
},
{
"name": "coverage",
"specs": [
[
"==",
"7.1"
]
]
},
{
"name": "et-xmlfile",
"specs": [
[
"==",
"1.1.0"
]
]
},
{
"name": "exceptiongroup",
"specs": [
[
"==",
"1.1.0"
]
]
},
{
"name": "flake8",
"specs": [
[
"==",
"6.0.0"
]
]
},
{
"name": "iniconfig",
"specs": [
[
"==",
"2.0.0"
]
]
},
{
"name": "jsonpickle",
"specs": [
[
"==",
"3.0.1"
]
]
},
{
"name": "mccabe",
"specs": [
[
"==",
"0.7.0"
]
]
},
{
"name": "mock",
"specs": [
[
"==",
"5.0.1"
]
]
},
{
"name": "numpy",
"specs": [
[
"==",
"1.24.2"
]
]
},
{
"name": "numpy-financial",
"specs": [
[
"==",
"1.0.0"
]
]
},
{
"name": "openpyxl",
"specs": [
[
"==",
"3.1.0"
]
]
},
{
"name": "packaging",
"specs": [
[
"==",
"23.0"
]
]
},
{
"name": "pandas",
"specs": [
[
"==",
"1.5.3"
]
]
},
{
"name": "pip-tools",
"specs": [
[
"==",
"6.12.2"
]
]
},
{
"name": "pluggy",
"specs": [
[
"==",
"1.0.0"
]
]
},
{
"name": "pycodestyle",
"specs": [
[
"==",
"2.10.0"
]
]
},
{
"name": "pyflakes",
"specs": [
[
"==",
"3.0.1"
]
]
},
{
"name": "pyproject-hooks",
"specs": [
[
"==",
"1.0.0"
]
]
},
{
"name": "pytest",
"specs": [
[
"==",
"7.2.1"
]
]
},
{
"name": "pytest-cov",
"specs": [
[
"==",
"4.0.0"
]
]
},
{
"name": "python-dateutil",
"specs": [
[
"==",
"2.8.2"
]
]
},
{
"name": "pytz",
"specs": [
[
"==",
"2022.7.1"
]
]
},
{
"name": "scipy",
"specs": [
[
"==",
"1.10.0"
]
]
},
{
"name": "six",
"specs": [
[
"==",
"1.16.0"
]
]
},
{
"name": "tomli",
"specs": [
[
"==",
"2.0.1"
]
]
},
{
"name": "wheel",
"specs": [
[
"==",
"0.38.4"
]
]
},
{
"name": "yearfrac",
"specs": [
[
"==",
"0.4.8"
]
]
}
],
"tox": true,
"lcname": "xlcalculator"
}
JSON
