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