Thursday, 28 March 2013

Evaluation of non-linear expressions using Excel

NAG provides VB headers for all the routines in the library allowing them to be called easily from Microsoft Excel given a little knowledge of Visual Basic. Along with the headers, some Excel examples are distributed with the product. You may also find demo worksheets for a variety of routines on the NAG web site.

Combining the ease of use of Excel and the power of NAG routines is a great way of creating simple, flexible interfaces for solving difficult problems. Typically you write VBA code to handle the reading and writing of data to the Excel worksheet and the passing of parameters to the NAG routines. Once this VBA code has been written the problem parameters can be easily changed and tweaked from the worksheet without having to alter any code again.

Routines which take only simple parameters such as scalar values, vectors and matrices can have all their arguments input via the Excel worksheet however for routines that require non-linear expression such as those routines with callback functions it isn’t so straightforward. One method is to simply write the callback in VBA however you lose the interactivity gained from using Excel as every time you want to change the problem you would have to edit the VBA code. This isn’t a big deal but there is another way!

The VBA function evaluate() can be used for just this purpose and, along with a few tricks, allows  natural input of non-linear expressions via the worksheet. The demo for the NAG optimization routine e04jcf takes advantage of this method. As you can see the objective function is expressed naturally on the sheet along with the simple input parameters required for the routine e04jcf:


Those with a keen eye will have picked up on the fact that the objective function is described in terms of X1…X4 but the initial estimation is given in cells B11…B14. The trick is that once the initial estimate has been read from the worksheet and stored in a VBA array, it gets copied back to the sheet in cells X1…X4 before the NAG routine e04jcf is called. Of course you could describe the objective function in terms of which-ever cells you like. It should be noted that this is not the most efficient method due to the extra copying of data that is required so it may not be suitable if your problem is large.

This technique can be exploited with many other routines in the NAG Library such as root finding, quadrature, systems of non-linear equations and many more. Once a worksheet has been created, it can be used by anyone who is familiar with Excel even if they do not know VBA.

Tip: You can hide column X by right-clicking the column header and selecting hide or set the font colour to white if you don’t want the intermediate values displayed.

The demo for e04jcf can be downloaded from our Excel page.

Friday, 15 March 2013

3 Years Later

Do you remember those episodes of Friends that consisted entirely of flashbacks - presumably the cast were too busy with other duties that week so couldn't film, or the writers too busy to think up a real story ...

The NAG Blog has been running for just over 3 years now. In that time we have had 118 posts on a wide variety of numerical computing topics including HPC, .NET, customer engagement, GPUs, optimization, and much more.

The most popular posts (pageviews) have often been the most technical ones - including:

On the HPC theme, there have been posts discussing factors of 1000x in performance, describing supercomputing to friends, family and politicians, and the relationship between supercomputers and ice cubes.

I hope you have found our blog to be a nice mix of useful content, occasional fun, and topical thoughts. Do you have a favourite NAG Blog post - or more importantly - is there a topic you would like one of our numerical computing experts to write about? From talking to customers we know that you’d like to see more technical articles which we are always working on, but we also would like to see more contributors to the blog. So if you have something to say about numerical algorithms or HPC that relates to NAG and the work that we do, get in touch and maybe we can collaborate.

Our other social networks – the NAG Linkedin Group and @NAGTalk on Twitter have grown to be fairly engaged spaces. Our aim with all our groups is to create a friendly space where users and staff can readily exchange ideas, innovate and share relevant stories. The Linkedin Group is a vetted group which means that we keep it free from spam and recruiters. Do join if you want to connect with other NAG users, collaborators and developers.