http://www.stanford.edu/~wfsharpe/mia/mat/mia_mat4.htm#operations
Introduction
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. The numerical accuracy
is also questionable.
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.
We 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.
Named Ranges
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.
Matrix Operations in Excel
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.
Matrix Addition
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.
Matrix Subtraction
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
Using Matrices with Scalars
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
Matrix Multiplication
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)
Transposition
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.
Matrix Inversion
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.
Combining Matrix Operations
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.