Apr 28,
2015

In the previous instalment on linear systems we saw that Excel can easily invert a modest-sized matrix and find solutions to a set of simultaneous linear equations. In the real world, a place I allude to frequently and try to avoid as much as possible, it is sometimes necessary to bring more powerful guns to bear on a particular problem.

We shall now take look at how Excel can enlist the aid of MATLAB, a high-end mathematical program, in the solution not only of systems of linear equations but a broad spectrum of mathematical problems in science, engineering, economics, and business.

The technique we will describe here will only work for Excel running on Windows. If you are not interested in economics and just want to start using MATLAB, you can skip to The Hear of the Matter.

We’ll start by choosing a small system of linear equations from economics research, a set used to model a small portion of the US economy. This type of analysis, called input-output analysis won Russian-American Vasily Leontief the Nobel Prize in 1974. Unfortunately, repeating his analysis in Excel will not qualify us for a Nobel Prize.

When a company makes, for example, steel, some of the steel ends up in consumer products, but some is consumed in the manufacturing process of other products. Indeed, the steel industry itself uses some steel.

Below are actual production figures for the automotive and steel industries in the United States in 1958 (in millions of 1958 dollars).

Well, that’s fine, but what about next year? Let’s assume that we expect an increase of $200M in the demand for steel to $17589M, but a decrease of $25M in demand for cars to $21243M. Can’t we just increase production by $200M? It’s not that simple, because an increase in steel production will consume some steel itself, and will also consume some automobiles (which will also consume some steel). We start be taking a pocket calculator, doing some division, and creating what economists call the * technology matrix*. This is nothing more than the chart above represented in ratios.

used by steel | used by auto | |

value of steel |
0.212000943 |
0.087787517 |

value of auto |
0.001886199 |
0.297568049 |

In other words, about 21% of steel production is used by the steel industry itself and almost 30% of the automobile production is consumed by the automotive industry. We want to calculate the total steel and auto production predicted to meet the demand in the coming year. Writing this out as a pair of simultaneous linear equations:

0.2120 * steel production + 0.0878 * auto production + 17589 = steel production

0.0019 * steel production + 0.2976 * auto production + 21243 = auto production

This example can be found in the companion workbook. This workbook also contains a more recent example based on the 2007 economy of the Netherlands.

Notice that, unlike the previous example, the right-hand side of these equations is a variable, not a constant. This changes the algebra a bit. If A is the matrix constructed from the coefficients on the left-hand side, the matrix we need to invert is I-A, where I is the identity matrix, the matrix with ones along the diagonal and zeros elsewhere.

In the earlier instalment, we solved a system of linear equations by asking Excel to calculate an inverse matrix. This time, however, we will let MATLAB do the work. This can be valuable, since ultimately we are concerned with more than just a numerical answer. For example, if we were building an economic model, we might wish to know how sensitive out model was to small errors in the parameter values.

When installed on Windows, MATLAB registers itself as a COM automation server. This means that using VBA, we will be able to create a MATLAB server to do our mathematical bidding.

When using COM automation, it is generally preferable to use early binding, which means that the compiler must be given information about MATLAB before the VBA code is run in Excel. To accomplish this, we go to the tools menu in the Visual Basic editor and select the MATLAB type library.

To cut down on clutter, only the most important features of the code will be discussed here. The complete code is available in an Excel workbook. This workbook also contains some additional illustrations, such as the creation of a MATLAB plot from a VBA macro.

Classic automation used “late binding”, which means that VBA really doesn’t have any idea what objects are going to be doing until runtime when the objects are actually created. Here is example code for creating a MATLAB COM server object using late binding:

Private mMatlab as Object

Sub StartServer()

If mMatlab Is Nothing Then

Set mMatlab = CreateObject(“matlab.application”) ‘ late binding

End If

End Sub

As mentioned, early binding is generally preferable when feasible. In early binding, we declare a variable of a type defined in the COM library, in this case, MLApp.MLApp.

Private mMatlab As MLApp.MLApp

Sub StartServer()

If mMatlab Is Nothing Then

Set mMatlab = New MLApp.MLApp ‘ early binding

End If

End Sub

MATLAB is a very large application and, depending on your system’s resources, may load slowly. Excel’s COM automation may time out and assume there is a problem even though MATLAB is loading normally. If this happens, a second try often does the trick. In this code, we try once more if the first attempt fails.

Sub StartServer()

Dim TryAgain As Integer

On Error GoTo Retry

If mMatlab Is Nothing Then

Set mMatlab = New MLApp.MLApp ‘ early binding

End If

Exit Sub

Retry:

If TryAgain = 0 Then Resume

MsgBox “Unable to start Matlab automation server”

End Sub

When the MATLAB automation server starts, the MATLAB console appears. In a production environment, you might wish to set the visible property of the server to false. However, in a development environment, it is a good idea to keep the visible property set to true, even if you intend to set it false for deployment. That way, you have the console available for debugging and testing. Any variables created in MATLAB by Excel will be directly accessible in the console. You can even use this to perform valuable operations ad hoc.

The basic tasks are very simple. We send data to MATLAB, we tell MATLAB what to do, we retrieve our results.

There are three fundamental functions for passing data from Excel into MATLAB: PutWorkspaceData, PutFullMatrix, and PutCharArray. We will not discuss PutCharArray here. PutFullMatrix will probably not be of great value to analysts using Excel, since it assumes that every matrix is complex. This doubles the workload; we must always supply a matrix of the imaginary parts of the complex matrix, even if they are all zero.

This leaves us with PutWorkspaceData. This function takes as arguments the name of the new variable for the matrix, the scope, which will usually be “base”, and the matrix itself. The matrix must be an array of double data type. Variants will not work.

mMatlab.PutWorkspaceData VariableName, Scope, dblMatrixVariable.

The “flip-sdie” of PutWorkspaceData is GetWorkspaceData. In contrast with PutWorkspaceData, variants work fine here, and makes our job easy.

This code invokes the inv [inverse] function on a matrix L that has been previously been sent to MATLAB. Once we have obtained the output from MATLAB, it can be directly assigned to a range in a worksheet. To be useful, the range in the worksheet must match the dimensions of the matrix.

Dim v As Variant

mMatlab.Execute “invL=inv(L)”

mMatlab.GetWorkspaceData “invL”, “base”, v

ActiveSheet.Range(“InverseMatrix”).Value = v ‘target range is smaller than array, data will be truncated

MATLAB also provides a “wrapper” function called GetVariable, which has the same functionality as GetWorkspaceData, but returns the result in a manner comparable to that normally found in VBA functions.

vHilbert = mMatlab.GetVariable(“H”, “base”)

There are two distinct ways to tell MATLAB what to do. One is by sending MATLAB a command line from Excel, just as if a human were typing a command line at the MATLAB console. This is done using the Execute method. The second method is simply asking MATLAB to evaluate a function for us. This is accomplished with the Feval method.

*Execute* allows Excel to send commands exactly as if they were being typed at the MATLAB command prompt. While this is extremely powerful, it is often overused. Constructing large strings and executing them to create data within MATLAB generally leads to code that is difficult to read and difficult to maintain. It is better to send data to MATLAB using PutWorkspaceData and keeping the command strings send with Execute short and sweet.

*Feval* provides an easy way to evaluate a MATLAB function. While you could always evaluate a function with the appropriate commandline using Execute, Feval has the advantage that you can invoke the function and retrieve the result in a single operation.

Dim Out As Variant

Dim notQuitePi As Double

notQuitePi = 3.1415926

mMatlab.Feval “cos”, 1, Out, notQuitePi

Debug.Print CStr(Out(0)) ‘ notice that the Out value is an array

The first argument to Feval is the name of the function to invoke. The Second argument is the number of arguments expected by the function we just named. In this example, the “cos” function expects one argument. The third argument is a variant provided to contain the results of the function. Since many MATLAB functions return more than one output value, the Out variant here is always an array, even if the function only returns a single value. In other words, we access the result of a single-valued function by checking Out(0), an array with one element.

In OLE automation in general, the transfer of data between client and server is one of the slowest steps. Therefore it is unwise to send data to MATLAB, process it and retrieve the result, then send the result to MATLAB, then process that and get the result, and so forth. It is far better to send MATLAB whatever it needs, send whatever commands are required, and then retrieve the result. The less data transfer between MATLAB and Excel, the faster the performance you will achieve.

In our demonstration, we have calculated the inverse of a matrix defined in Excel using MATLAB. In general, if that is all you wish to do, using Excel’s own MINVERSE function is far easier. However, MATLAB’s toolbox is far deeper. Perhaps you wish to obtain a condition number, or perform a principal components analysis (PCA). In such cases the built-in functions of Excel fall short and MATLAB can come to the rescue.

For more, have a look at Learning Tree’s complete curriculum of Excel courses including 3 new 1-day courses you can take online from anywhere.