I implemented my own Newton-Raphson solver to calculate effective APY for my investment. Attached is an example worksheet.
The push button “Calculate APY” is linked to macro “SolveAPYNR”.
When I click the button, I can see the numbers change, my mouse cursor goes back and forth between “busy” and normal, and in the bottom status bar, “calculating” flashes multiple times. It takes about a second to finish.
However, if I open “Basic” code editor from menu “Tools → Macros → Edit Macros” and launch the macro there by hitting F5 key, it runs much faster. At least 2x faster.
Why the difference in speed?
Version: 7.3.7.2 (x64) / LibreOffice Community
Build ID: e114eadc50a9ff8d8c8a0567d6da8f454beeb84f
CPU threads: 8; OS: Windows 10.0 Build 19045; UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL
contribution-example.ods (35.9 KB)
export basic:
REM ***** BASIC *****
Sub Main
End Sub
Sub SolveAPYNR()
Dim oSheet As Object
Dim oRange As Object
Dim oCell As Object
Dim bal_actual As Double
Dim bal1 As Double
Dim bal2 As Double
Dim apy1 As Double
Dim apy2 As Double
Dim da As Double
Dim db As Double
Dim db_da As Double
Dim e As Double
Dim c As Integer
Dim s As boolean
oSheet = ThisComponent.CurrentController.ActiveSheet
oCell_apy = oSheet.getCellRangebyName( "APY_TARGET_" & oSheet.Name ).getCellByPosition(0,0)
oCell_bal = oSheet.getCellRangebyName( "BAL_TARGET_" & oSheet.Name ).getCellByPosition(0,0)
oCell_bal_actual = oSheet.getCellRangebyName( "BAL_ACTUAL_" & oSheet.Name ).getCellByPosition(0,0)
oCell_loopnum = oSheet.getCellRangebyName( "LOOPNUM_" & oSheet.Name ).getCellByPosition(0,0)
oCell_error = oSheet.getCellRangebyName( "ERROR_" & oSheet.Name ).getCellByPosition(0,0).value
oCell_dapy = oSheet.getCellRangebyName( "DAPY_" & oSheet.Name ).getCellByPosition(0,0).value
bal_actual = oCell_bal_actual.value
apy1 = oCell_apy.value
da = apy1 * 0.001
bal2 = oCell_bal.value
c = 0
Do
bal1 = bal2
oCell_apy.value = oCell_apy.value + da
bal2 = oCell_bal.value
db = bal2 - bal1
db_da = db/da
e = bal_actual - bal2
da = SmoothLimit(e / db_da, oCell_apy.value)
c = c + 1
oCell_loopnum.value = c
oCell_error = e
oCell_dapy = da
s = Abs(e) < 0.0001 Or c > 50
Loop Until s
End Sub
Function SmoothLimit(x As Double, y As Double)
SmoothLimit = x * (0.55-0.45*Tanh((Abs(x/y)/.1)-1))
End Function
Function Tanh(x As Double)
Dim e1 As Double
Dim e2 As Double
Dim t As Double
e1 = Exp(x)
e2 = Exp(-1*x)
Tanh = (e1-e2)/(e1+e2)
End Function
