Basic Wrapper for LibreOffice Calc Python UDF

Question

How is the Basic wrapper call formed to run a LibreOffice Calc Python User Defined Function using non-integer spreadsheet ranges?

I'm not clear on how to declare the function properly for the values and excel_date arrays which are both arrays of real numbers, type Variant in Basic terminology I understand.

I've followed Calling a python function from within LibreCalc and ranges can be passed as integer arguments without defining them but these are real values. Both arrays are single-dimension so the multi-dimensional array issues do not apply as discussed in How can I call a Python macro in a cell formula in OpenOffice.Org Calc?

Basic code is:

Function xnpv_helper(rate as Double, values() as Variant, excel_date() as Variant) as Double
    Dim scriptPro As Object, myScript As Object
    scriptPro = ThisComponent.getScriptProvider()
    myScript = scriptPro.getScript("vnd.sun.star.script:MyPythonLibrary/Develop/math.py$xnpv_helper?language=Python&location=user")
    xnpv_helper = myScript.invoke(Array(rate, values, excel_date), Array(), Array() )
end function

Spreadsheet data:

Date    Amount  Rate    xnpv
31/12/19    100 -0.1    
31/12/20    -110

xnpv_helper at the bottom. Actual python script is based on https://github.com/tarioch/xirr from financial python library that has xirr and xnpv function?

# Demo will run in python REPL - uncomment last line
import scipy.optimize

DAYS_PER_YEAR = 365.0

def xnpv(valuesPerDate, rate):
    '''Calculate the irregular net present value.

    >>> from datetime import date
    >>> valuesPerDate = {date(2019, 12, 31): -100, date(2020, 12, 31): 110}
    >>> xnpv(valuesPerDate, -0.10)
    22.257507852701295
    '''
    if rate == -1.0:
        return float('inf')
    t0 = min(valuesPerDate.keys())
    if rate <= -1.0:
        return sum([-abs(vi) / (-1.0 - rate)**((ti - t0).days / DAYS_PER_YEAR) for ti, vi in valuesPerDate.items()])
    return sum([vi / (1.0 + rate)**((ti - t0).days / DAYS_PER_YEAR) for ti, vi in valuesPerDate.items()])


from datetime import date
def excel2date(excel_date):
    return date.fromordinal(date(1900, 1, 1).toordinal() + int(excel_date) - 2)

def xnpv_helper(rate, values, excel_date):
    dates = [excel2date(i) for i in excel_date]
    valuesPerDate = dict(zip(dates, values))
    return xnpv(valuesPerDate, rate)

# valuesPerDate = {date(2019, 12, 31): -100, date(2020, 12, 31): 110}
# xnpv_helper(-0.10, [-100, 110], [43830.0, 44196.0])

If all you want to do is use the XNPV function, why not just use Calc's [built-in function](https://help.libreoffice.org/7.2/en-GB/text/scalc/01/04060118.html?DbPAR=CALC#bm_id3149198) of that name? There is also a built-in [XIRR](https://help.libreoffice.org/7.2/en-GB/text/scalc/01/04060118.html?DbPAR=CALC#bm_id3147485). — Howard Rudd, Oct 08 '21 at 12:59
How does the behaviour of the Python XIRR function in [https://github.com/tarioch/xirr/blob/master/src/xirr/math.py](https://github.com/tarioch/xirr/blob/master/src/xirr/math.py) differ from that of Calc's built-in function? As far as I can see they are exactly the same. They both take in an array of dates and an array of values and output a value. The fact that in the Python version the two arrays are combined into a single two-dimensional array does not change what the function does. — Howard Rudd, Oct 13 '21 at 21:15
Yes, the mathematical calculations are the same but you can't do multiple calculations with normal data layouts using the built-in functions as demonstrated in https://ask.libreoffice.org/t/call-python-macro-using-array-as-calc-function/67034/18. — flywire, Oct 28 '21 at 00:08

score 0 · Accepted Answer · answered Dec 05 '21 at 11:04

Thank you to @arturaz for explaining the Basic array comes to python as a tuple and needs converting to a list (best match for an array in python). Further details are given in his answer at https://stackoverflow.com/a/8978435/4539999 which builds on the question and other answer.

The APSO python console shows:

what is parsed from Basic to python
what is required
required data with dates formatted

APSO python console [LibreOffice]
3.8.10 (default, Aug 10 2021, 19:39:20) [MSC v.1928 64 bit (AMD64)]
>>> 
1: ((43830.0,), (44196.0,)) ((100.0,), (-110.0,)) -0.1
2: [43830.0, 44196.0] [-100, 110] -0.1
3: {datetime.date(2019, 12, 31): -100, datetime.date(2020, 12, 31): 110} -0.1

The two arrays are reformatted before they are used in the python helper function:

def xnpv_helper(rate, values, excel_date):
    excel_date0 = [a for b in excel_date for a in b]
    values0 = [a for b in values for a in b]
    dates = [excel2date(i) for i in excel_date0]
    valuesPerDate = dict(zip(dates, values0))
    return xnpv(valuesPerDate, rate)

Basic Wrapper for LibreOffice Calc Python UDF

1 Answers1

Linked