Numerical Integration; Tanh-Sinh Quadrature v. 4.3

A new version (4.3) of the numerical integration spreadsheet by Graeme Dennes is now available for download from Tanh_Sinh Quadrature.

The Tanh-Sinh Quadrature Version 4.3 workbook now provides a feature for the integration of integrands contained in independent (external) UDFs. By using this feature, changes to the workbook VBA programs are not required, providing a simplification of the use of the workbook. Although the performance shown by this method is not as great as that shown by the VBA-stored method, it is faster than the cell-stored method, and adds to the utility and functionality of the workbook without requiring VBA changes to be made by the user. All 13 quadrature programs can utilise this new feature. Added examples and documentation for using this new feature have been included on the T-S EXAMPLES worksheet. This feature was proposed by Doug Jenkins.

Also included in this release is the ability to evaluate text as a formula, further adding to the functionality of the workbook. Also proposed by Doug.

My thanks to Doug for raising these changes to enhance the workbook.

Graeme Dennes

In addition to the main workbook, containing full documentation and numerous examples, two other workbooks are now included in the download zip file, to simplify the process of using these functions in other applications:

  • Tanh-Sinh Quadrature v4.3 by Graeme Dennes-Code Only.xlsm: includes full open-source VBA code, but documentation is reduced to 2 simple examples of each function.
  • Tanh-Sinh Quadrature v4.3 by Graeme Dennes-Minimal Code.xlsm: includes code for the Tanh-Sinh function only, with two examples of using this function to evaluate integrals required by other routines.

Either of the cut-down workbooks may be incorporated in other applications by either inserting new worksheets and VBA code as required, or by copying the VBA code modules to another application.  VBA modules may be copied between projects by opening both workbooks, then simply selecting the desired modules in the VBE Project Explorer window, and dragging them to the new project “Modules” folder.

Alternatively the workbooks may be saved as an add-in (open the Save-as window, then select “Excel add-in” (*.xlam)), so that all the functions will be available from any open workbook.

Posted in Excel, Maths, Newton, Numerical integration, UDFs, VBA | Tagged , , , , , , , , | 6 Comments

More from Martin Simpson

“Never any Good”

A beautiful song by Martin Simpson about his father, who he clearly did not see in the way the title might suggest:

Martin Simpson – Never Any Good Lyrics

You were never any good with money.
You couldn’t even hold a job,
Not steady enough for the office,
Not hard enough for the hod.
You’d rather be riding your Norton
Or going fishing with your split cane rod.
You were never any good with money.
You couldn’t even hold a job.

When your grammar school days were over,
It was nineteen-seventeen,
And you did the right and proper thing.
You were just eighteen.
You were never mentioned in dispatches.
You never mentioned what you did or saw.
You were just another keen young man
In the mud and stink of war.

You were never any good with money.
You couldn’t even hold a job,
Not steady enough for the office,
Not hard enough for the hod.
You’d rather be singing the Pirate King,
Or going fishing with your split cane rod.
You were never any good with money.
You couldn’t even hold a job.

You came home from the Great War
With the pips of a captain’s rank.
A German officer’s Luger,
And no money in the bank.
Your family sent you down in the coal mine
To learn to be captain there,
But you didn’t stand it very long.
You needed the light and the air.

You were never any good with money.
You couldn’t even hold a job,
Not steady enough for the office,
Not hard enough for the hod.
You’d rather be watching performers fly
Or going fishing with your split cane rod.
You were never any good with money.
You couldn’t even hold a job.

When the second war came along,
You knew what should be done.
You would re-enlist to teach young men
The booby trap and the gun;
And they sent you home to Yorkshire,
With a crew and a Lewis gun,
So you could save your seaside town
From the bombers of the Hun.

You were never any good with money.
You couldn’t even hold a job,
Not steady enough for the office,
Not hard enough for the hod.
You’d rather be finding the nightjar’s nest,
Going fishing with your split cane rod.
You were never any good with money.
You couldn’t even hold a job.

And when my mother came to your door,
With a baby in her arm,
Her big hurt boy only nine years old,
Trying to keep her from harm,
If you had been a practical man,
You would have been forewarned.
You would have seen that it never could work,
And I would have never been born.

There’s no proper work in your seaside town,
So you come here looking for a job.
You were storeman at the power station
Just before I came along.
Nobody talked about how you quit,
But I know that’s what you did.
My mother said you were a selfish man,
And I was your selfish kid.

You were never any good with money.
You couldn’t even hold a job,
Not steady enough for the office,
Not hard enough for the hod;
And your Norton it was soon gone
Along with your split cane rod.
You were never any good with money.
You couldn’t even hold a job.

You showed me eyebright in the hedgerow,
Speedwell and travellers joy.
You showed me how to use my eyes When I was just a boy;
And you taught me how to love a song
And all you knew of nature’s ways:
The greatest gifts I have ever known,
And I use them every day.

You were never any good with money.
You couldn’t even hold a job,
Not steady enough for the office, maybe,
Not hard enough for the hod.
You’d rather be riding your Norton
Or going fishing with your split cane rod.
You were never any good with money.
You couldn’t even hold a job.

Posted in Bach | Tagged , | Leave a comment

Three spoonfuls of Martin Simpson

According to Wikipedia, “Martin Simpson is regarded by many as one of the most skilled folk guitarists currently playing on the British and American folk scene”, with which my only disagreement would be that to characterise his music purely as “folk” really does not do it justice:

 

Posted in Bach | Tagged , | Leave a comment

Numpy and Scipy for Excel

The Python Numpy and Scipy libraries contain a huge number of maths and science related functions.  Most of these are not directly available from Excel, but using the free ExcelPython package it is fairly easy to make the link, vastly increasing the functionality of Excel for maths, science and engineering applications.

I have collected interface functions to many of the (to me) most useful Scipy and Numpy functions in a Python module called xlScipy.  These may be accessed from a free and open source spreadsheet: xlSciPy.zip.

The spreadsheet requires a copy of Python to be installed (including Numpy and Scipy), all other files, including ExcelPython, are included in the download package.  Just unzip to a convenient folder, enable macros when you start up, and it should work.  For those who have downloaded earlier versions of ExcelPython, this spreadsheet uses version 2.08, which is not compatible with earlier versions, so install in a different folder to the earlier versions.

The functions included in xlSciPy are listed on the first sheet (index):

xlScipy1
There are ten functions for interpolation and fitting:
xlScipy2
17 linear algebra functions:
xlScipy3
12 functions for solving, creating and evaluating polynomials of any order:
xlScipy4
All of the Scipy special functions (219 in all):
xlScipy5
and 26 statistical functions:
xlScipy6

Additional functions will be added as time permits.

The spreadsheet includes examples and brief documentation of each function; more detailed documentation can be found at Numpy Reference and Scipy Reference.

Posted in Excel, Link to Python, Maths, NumPy and SciPy, UDFs, VBA | Tagged , , , , , , | 11 Comments

Working with array functions

Regular visitors will know that I make frequent use of User Defined Functions that return an array of data, and need to be entered as an array function.

This has the advantages that the functions are very flexible in the layout of input and output data, and brief documentation can be added easily, but they do have a couple of disadvantages:

  • There is no simple built-in way to reduce the size of an array functions output range.
  • For applications where simplicity and speed of operation is more important than flexibility it is often better to use sub-routines with pre-defined ranges and user-input forms and buttons.

To get the best of both worlds I have modified the RegExpres spreadsheet with the following features:

  • Added two sub-routines that will adjust an array function range to the selected range (press Ctrl-Shift-R), or adjust the size to display the full output array (press Ctrl-Shift-S).
  • Added a simple demonstration of how a function can be wrapped in a sub-routine, that will read the input data from defined ranges, and write the output array as values to a range of adjustable size.

The spreadsheet can be downloaded from RegExpres.xlsb, including full open-source code.

The array re-sizing functions were taken from the Technicana site, where they are made freely available, along with other VBA utilities.

The screen-shots below show an array output being reduced down to the selected area with Ctrl-Shift-R, then re-sized to display the full array with Ctrl-Shift-S:

Array-resize1

Array-resize2

The code for the subroutine to return the array function output to a defined range is shown below:


Sub Reg_Expres_Sub()
Dim SubIn As Variant, SubArg As Variant, FuncResA As Variant
Dim Pattern As String, Operation As String, RepString As String, Glob As Boolean, IgnoreCs As Boolean
Dim NumRows As Long, NumCols As Long

' Read data
SubIn = Range("subin").Value2
SubArg = Range("subarg").Value2

' Create function arguments
Pattern = (SubArg(1, 1))
Operation = SubArg(2, 1)
RepString = SubArg(3, 1)
Glob = SubArg(4, 1)
IgnoreCs = SubArg(5, 1)

'Call Function
FuncResA = Reg_Exp(SubIn, Pattern, Operation, RepString, Glob, IgnoreCs)

'Check size
NumRows = UBound(FuncResA)
NumCols = UBound(FuncResA, 2)

' Clear output range and rezize
With Range("subout")
.ClearContents
.Resize(NumRows, NumCols).Name = "subout"
End With
' Write results to spreadsheet
Range("subout").Value2 = FuncResA

End Sub

This code is linked to a button allowing the displayed results to be updated as required:

Array-resize3

Posted in Arrays, Excel, UDFs, VBA | Tagged , , , , | Leave a comment