Microsoft's Excel spreadsheet program provides an alternative environment for many of the computations required for Macro-Investment Analysis. Its ubiquity and ease of use are among its more attractive features. However, spreadsheets are notoriously dangerous, since the underlying logic of a set of calculations is usually contained in formulas scattered around a sheet (or sheets). Worse yet, the formulas are usually hidden from sight, behind the numbers representing the results of their calculations. These disadvantages loom especially large when an environment is to be chosen primarily as a means of communication. For our purposes, languages such as MATLAB are superior to a spreadsheet environment -- Excel or any other.
The situation is not, however, as bleak as it once was. Since the introduction of version 5.0, Excel has included a full programming language that allows for structured, documented, and readable sets of commands. Formally, it is a version of Microsoft's Visual Basic for Applications, but we will use the simpler form: Visual Basic or to be even more succinct: VB.
In Excel, VB procedures are called Macros , but this is far too humble a term for perfectly respectable programs and we will resist its use except when absolutely necessary.
Will will not cover Visual Basic, since it is a complex programming language that requires an extensive treatise. Suffice it to say that it provides an alternative to MATLAB and other languages for preparing investment application programs.
Here we concentrate on a a discussion of matrix operations in the standard Excel spreadsheet environment. The treatment will be cursory, at best since Excel is far too complex to cover in any detail in this exposition. Our goal is only to suggest ways in which it can be used by the Analyst for matrix operations.
Many Excel formulas require the specification of one or more ranges of cells as arguments. In many cases the easiest way to indicate such a range is to select it using keystrokes and/or a mouse as the formula is typed. For clarity, we adopt an alternative approach, using only named ranges in our formulas and statements. Since names remain with the formulas and statements, it is easy to change the physical range of cells to which a name applies whenever results are desired for a different range of inputs. Perhaps more important, the use of appropriate range names can greatly improve the readability of a set of formulas or statements.
The safest way to assign a name to a range of cells is to first select it, then choose Insert Name Define from the menu, followed by the desired name. Be certain to avoid names that look like cell locations or combinations of them (e.g. A22). In Excel, range names are not case sensitive. Thus Prices, prices and PRICES are considered the same name.
To select a named range, choose Edit Go to (or the equivalent key), followed by the range name. Alternatively, use the drop-down list of names located just above and to the left of the spreadsheet. When a named range is selected, the name will appear in the window for this list. (In fact, you can name ranges by selecting them, then typing the name in this box; however, this sometimes allows conflicts to creep in and should be avoided).
Once you have named a range, you may use it in any formula that allows for a range as an argument. As indicated earlier, we will always choose this alternative.
Unbeknownst to many users, Excel can do matrix operations very efficiently, either directly, or through the use of matrix functions. Microsoft prefers to use the term "Array" to "Matrix", so most references in their manuals and help system can be found under the former term.
Key to understanding the use of matrix operations in Excel is the concept of the Matrix (Array) formula. Such a formula uses matrix operations and returns a result that can be a matrix, a vector, or a scalar, depending on the computations involved. Whatever the result may be, an area on the spreadsheet of precisely the correct size must be selected before the formula is typed in (otherwise you will either lose some of the answer or get added and possibly confusing information).
After typing such a formula, you "enter" it with three keys pressed at once: CTRL, SHIFT and ENTER. This indicates that a matrix (array) result really is desired. It also designates the entire selected range as the desired location for the answer. To modify or delete the formula, select the entire region beforehand.
When matrix computations are performed in this way, the "result areas" will be updated immediately whenever any of the numbers in the "input areas" change (unless automatic recomputation has been turned off). This can be a great help when one wishes to evaluate the effects of changes in assumptions, initial conditions, etc.. This feature, coupled with the ability to see matrices, complete with identification of the rows and columns (i.e. in the form that we have termed tables), will often make the spreadsheet environment the preferred choice for computation, if not for communication.
In Excel, some matrix operations are performed automatically, using standard operators (as in MATLAB). Others require the use of matrix functions. We treat each below.
Assume that Holdings_1 and Holdings_2 are two ranges of the same size (say, {20*1}) containing the holdings of mutual funds in two accounts. To create a vector with the total holdings of both accounts, select an empty {20*1} range on the sheet, type in the formula:
= Holdings_1 + Holdings_2
then press CTRL-SHIFT-ENTER. As a matter of good practice, you might wish to name the resultant range (e.g. Tot_Holdings) for future reference.
Any two matrices of the same size can be added in this manner, with the result placed in a range of the same size.
Not surprisingly, a matrix can be subtracted from one of the same size in a manner analogous to that of addition. For example to find the holdings of account 2, you could use the formula:
= Tot_Holdings - Holdings_1
To add a constant to every element of a matrix, simply include it in a formula, as in:
= Tot_Holdings + 100
You can also subtract a constant from every element or multiply or divide every element by a constant. For example:
= Prices * 1.10
To multiply two matrices, use the MMULT function. Thus, if prices and holdings are compatible for multiplication, you could compute the value of a portfolio with the formula:
= MMULT(prices,holdings)
If a matrix is not turned in the right direction, simply use the TRANSPOSE function. Thus if prices is a {20*1} vector and holdings is also, you could use the formula:
= MMULT(TRANSPOSE(prices),holdings)
to produce the value of the portfolio.
As is often the case, there is another way to do the same thing in Excel. The (non-matrix) function SUMPRODUCT produces the sum of the products of the elements in two vectors of equal dimensions. Thus if prices and holdings are both {20*1}, you could compute the value of the portfolio with the formula:
= SUMPRODUCT(prices,holdings)
Note that to enter this formula, only the ENTER key need be pressed.
The provision of alternative methods for accomplishing a given type of calculation endears Excel to many users, especially those who grew up with prior versions. But it tends to frustrate those who yearn, perhaps quixotically, for a simple, yet powerful computing environment.
To produce the inverse of a matrix, use the MINVERSE function, as in:
= MINVERSE(lhs)
Of course the matrix in the named range must be square and invertable.
In Excel, as in MATLAB, you may combine matrix operations in a single formula. Remember, however, that everything must conform, that the output range should be the correct size for the final result, and that you must press CTRL-SHIFT-ENTER to enter the formula in the output range. As in more mundane formulas, it never hurts to include sufficient parentheses to remove any possible ambiguity concerning your desires.