Thank you FJCC & hubert lambert:
I have been hard at work since your excellent pointers.
I have a working function which I call from basic. It works once the calc is opened but I get two Basic error messages when I open calc before the spreadsheet appears. The first message comes up approximately thirty times (ie once I press OK to remove the dialog box it immediately reappears for about thirty presses of the OK button). I think this happens once for each instance of the function in my worksheet. This message appears to relate to NamedRanges not available in the document. I think this has to do with the location of the python script in my /home/user/.config/libreoffice/4/user/Scripts/python directory. This location works once the file is open. I am using location=user.
Code: Select all
sURL = "vnd.sun.star.script:my-python-macros.py$AveCostPerShare?language=Python&location=user"
I have tried location=share, location=document and location=application. All without success. I think location=document is the location that should work since my Basic and python scripts are in the document. I have to copy the py script to the user directory separately after I have saved it. I am using APSO to write my python scripts.
My Basic function to call the python function is:
Code: Select all
Function AveCostPerShare(p1,p2)
Dim sURL as String
Dim oMSP as Object
Dim oScript as Object
sURL = "vnd.sun.star.script:my-python-macros.py$AveCostPerShare?language=Python&location=user"
oMSP = getMasterScriptProvider()
oScript = oMSP.getScript(sURL)
AveCostPerShare = oScript.invoke(Array(p1,p2), Array(), Array())
End Function
Function getMasterScriptProvider()
' Dim g_MasterScriptProvider as Object
Dim oMasterScriptProviderFactory as Object
if isNull(g_MasterScriptProvider) then
oMasterScriptProviderFactory = createUnoService("com.sun.star.script.provider.MasterScriptProviderFactory")
g_MasterScriptProvider = oMasterScriptProviderFactory.createScriptProvider("")
endif
getMasterScriptProvider = g_MasterScriptProvider
End Function
Part of my python script is:
Code: Select all
# -*- coding: utf-8 -*-
from __future__ import unicode_literals
import uno
import unohelper
#from operator import itemgetter
def AveCostPerShare(share = "NEI SELECT GROWTH PORTFOLIO", ldate = 43404):
oDoc = XSCRIPTCONTEXT.getDocument()
oNamedRngs = oDoc.NamedRanges
oRng = oNamedRngs.getByName("transactions_detail")
oRefCells = oRng.getReferredCells()
oDataArray = oRefCells.getDataArray()
cnt = 0
i = 0
units = 0
Reduction = 0
TotalCost = 0
AveCost = 0
oFilteredArray = [[ 0 for j in range(4)] for i in range(100)] #max 100 transactions
for i in range(0, len(oDataArray) - 1):
if ((oDataArray[i][7] == share) and ((oDataArray[i][5] == "Purchase") or (oDataArray[i][5] == "Disposition") or (oDataArray[i][6] == "Dividend Reinvestment") or (oDataArray[i][6] == "Return of Capital")) and (oDataArray[i][10] <= ldate)):
oFilteredArray[cnt][0] = oDataArray[i][10]
oFilteredArray[cnt][1] = oDataArray[i][9]
oFilteredArray[cnt][2] = oDataArray[i][6]
if isinstance(oDataArray[i][8], str):
oFilteredArray[cnt][3] = 0
else:
oFilteredArray[cnt][3] = (oDataArray[i][8])
cnt = cnt + 1
if cnt == 0: # if no eligible transactions
return 0
del oFilteredArray[cnt:100] #delete the empty list items
oFilteredArray.sort(key=lambda x: x[0]) #sort in place
for i in range(0, cnt):
if (oFilteredArray[i][3] > 0):
units = units + oFilteredArray[i][3]
TotalCost = TotalCost + oFilteredArray[i][1]
if units == 0:
AveCost = 0
else:
AveCost = TotalCost/units
if (oFilteredArray[i][2] == "Return of Capital"):
TotalCost = TotalCost - oFilteredArray[i][1]
if units == 0:
AveCost = 0
else:
AveCost = TotalCost / units
if (oFilteredArray[i][3] < 0):
units = units + oFilteredArray[i][3]
Reduction = oFilteredArray[i][3] * AveCost
TotalCost = TotalCost + Reduction
if units == 0:
AveCost = 0
else:
AveCost = TotalCost / units
if TotalCost < .00001 or units < .00001:
AveCost = 0
AveCostPerShare = AveCost
return AveCostPerShare
# mri(oFilteredArray)
The error messages I'm getting are :
the first error message which appears approximately thirty times is
the secord error which I have to dismiss approximately six or seven times is
Any help would be appreciated, since the python function works accurately and is much faster than the Basic function that I was using.
If could get a trouble free start up I would be all set.