Call Python Macro using Array as Calc Function

English
, ,
#1

Basic and python treat multi-dimensional arrays differently so multi-dimensional arrays are tricky to return. I have installed xirr/math.py at master · tarioch/xirr · GitHub in C:\Users\User\AppData\Roaming\LibreOffice\4\user\Scripts\python\MyPythonLibrary\Develop\math.py

I’m trying to use the macro bridge at the bottom of https://stackoverflow.com/a/8978435/4539999 which I have in C:\Users\User\AppData\Roaming\LibreOffice\4\user\basic\Standard\Module1.xba and I’m confused at the end of the code.

Exactly what do I need at the end for xnpv(valuesPerDate, rate): using the test data:

const filename = "skaiciuokle"

Function pyFunc(func as String, args as Array)
    pyFunc = invokePyFunc(filename, func, args, Array(), Array())
End Function
#3

humties of financial functions are already implemented in calc, thereis a good chance to find an equivalent.
Anyway, if you want to serious dig into this domain you should start writing AddIns:
http://www.biochemfusion.com/doc/Calc_addin_howto.html
https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=56916

#5

I want to report XIRR for each change of year without inserting and removing eofy values. There’s no function for that.

Packing these up to test every iteration looks a bit complicated. I figured it would be easier to test with a basic call and move to an addin when it firms up.

#18

Existing xirr

Date       Credit     xirr       
 31/12/2019      80.00           
 31/12/2020    -100.00       0.25
 31/12/2020     100.00           
 30/06/2021      -5.00           
 31/12/2021    -104.50       0.10
                             0.17

Proposed UDF xirr

Date       Credit     Balance    xirr       
 31/12/2019      80.00           
 31/12/2020                100.00       0.25
 30/06/2021      -5.00           
 31/12/2021                104.50       0.10
                                        0.17
#4

Here is how you could call xnpv() from Basic.

Sub callXnpv
	valuesPerDate = Array( _
		Array(Array(2019, 12, 31), -100), _
		Array(Array(2020, 12, 31), 110))
	rate = -0.10
    scriptPro = ThisComponent.getScriptProvider()
    myScript = scriptPro.getScript( _
        "vnd.sun.star.script:math.py$xnpv_by_array?language=Python&location=user")
    result = myScript.invoke(Array(valuesPerDate, rate), Array(), Array() )
    MsgBox "Result='" & result & "'"
End Sub

xnpv() expects datetime python objects, so write a wrapper function in python. Here is a modified version of math.py located in Scripts/python:

from __future__ import unicode_literals
from datetime import date

DAYS_PER_YEAR = 365.0

def xnpv_by_array(valuesPerDate, rate):
    valuesDict = {date(*ti): vi for ti, vi in valuesPerDate}
    return xnpv(valuesDict, rate)
    
def xnpv(valuesPerDate, rate):
    '''Calculate the irregular net present value.'''
    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()])

This displays the expected result described in the example:

Result='22.2575078527013'
#7

Putting the macro bridge to the side, what is the correct code for this function wrapper?

Basic code:

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, Array(values), Array(excel_date)), Array(), Array() )
End Function

I’ve reworked the python function to:

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

Using spreadsheet data:

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

Bump. Any advice welcome. I really don’t understand the basic/python interface and I can’t find any relevant examples for arrays. Maybe the data is going through to the python script in an unexpected format.

#9

Extensions » Extensions is an add-in with one function taking a 9x9 array and returning a 9x9 array. It solves Sudou puzzles.

2 Likes
#10

And then there is Struct ArrayWrapper

#11

@Villeroy thanks for replying. Above the post I linked at the top of this post about the basic-python bridge is https://stackoverflow.com/a/7592767/4539999:

On the old OO.org forums, (super)user Villeroy posted an illustration of how to call Python functions from OO.org Basic [broken link]

The issue is two single-dimension arrays not multidimensional arrays which I understand are more complicated. (There could still be an issue with my implementation of the single array format though.)

I really need examples to work through for those sort of docs.

eg there’s heaps of arrays at: Mapping of Sequences and Arrays - Apache OpenOffice Wiki

It doesn;t help much using in functions passing to.from python.

#12

Seems python is getting two lists of single-element lists rather than two lists:

APSO python console [LibreOffice]
3.8.10 (default, Aug 10 2021, 19:39:20) [MSC v.1928 64 bit (AMD64)]
>>> -0.1 ((100.0,), (-110.0,)) ((43830.0,), (44196.0,))
#13

Unfortunatly you did not post the code which produced that Output.
But it seems the representation of the single float -0.1 , followed by 2 nested tuples each of them would fit into cellrange of 2rows * 1Column the second tuple as calc-dates:

2019-12-31
2020-12-31
#14

As you say, clearly the data is there it’s just a parsing issue. I removed all references to array from the basic code and it parses integer ranges (other than a demonstration it’s a red herring).

Full python code reworked from @jimk - last line shows what basic needs to parse: xnpv_helper(-0.10, [-100, 110], [43830.0, 44196.0]):

# 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])
#15

Did not see any code which produces output, so far…
This excel2date is something sophistic, date zero in calc is (default) on 1899-12-30
so my simplified version:

from datetime import date, timedelta as delta
def calc2date(calc_date):
    return date( 1899, 12, 30) + delta( days=int(calc_date ))
#16

Well, that was me.

Too sad. First of all, you need the MRI extension. No programming without MRI.
More spoon feeding:

Function FOO(arg())
oWrapper = createUnoStruct("com.sun.star.script.ArrayWrapper")
oWrapper.Array = arg()
oWrapper.IsZeroIndex = False
End Function

Now pass oWrapper to the Python function. I don’t know what happens but I believe this is how that ArrayWrapper thing is supposed to work.

#17

OK. Changed Basic call lines are commented out and xnpv_helper rewritten for debug output.

Parse issue I can’t resolve is shown in Console output:

  • line 1 - what basic parses
  • line 2 - what xnpv_helper wants from basic
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

Spreadsheet data:

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

'Function xnpv_helper(rate as Double, values() as Variant, excel_date() as Variant) as Double
Function xnpv_helper(rate as Double, values, excel_date) 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, Array(values), Array(excel_date)), Array(), Array() )
    xnpv_helper = myScript.invoke(Array(rate, values, excel_date), Array(), Array() )
end 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):
    print()
    print("1:", excel_date, values, rate)
    # fix array parsed by basic
    excel_date0 = [43830.0, 44196.0]
    values0 = [-100, 110]
    print("2:", excel_date0, values0, rate)
    dates = [excel2date(i) for i in excel_date0]
    valuesPerDate = dict(zip(dates, values0))
    print("3:", valuesPerDate, rate)
    print()
    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])
ScriptForge resources
Direct links to collapsed comments don't work
#19

Useful post but strange to provide a Sub that doesn’t work when the question asks about a function.

Both use UNO IDL (Interface Definition Language) which is an alternate and much more complex approach to Basic.

#20

The Basic array comes to python as a tuple and needs converting to a list (best match for an array in python). Detailed answer.

#21

The solution would be a LibreOffice Add-In. An add-in is an extension which provides additional spreadsheet functions. Then you call the Python function directly on sheet or from the FunctionAccess service:

srv = createUnoService("com.sun.star.sheet.FunctionAccesss")
sResult = srv.callFunction("org.openoffice.pyfunctions.PyString.pystring",Array(sName,arg))

The example refers to the excellent demo by Hubert Lambert [Calc][oxt] A function for all python string methods (View topic) • Apache OpenOffice Community Forum

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.