| Related sites for http://www.stanford.edu/~wfsharpe/mia/mat/mia_mat4.htm |
| RFC_1546 Host Anycasting Service. C. Partridge, T. Mendez, W. Milliken. November 1993. | | IT-Factories Provides implementation and consulting service in China. | | Linux_Sampler_Project Audio sampler with features similar to hardware samplers. Offers speed, high audio quality rates, supports common sample library formats. | | Digital_Recovery Uses data encryption for safe transmissions. Backup and restoration are always available by either shipped CD or internet download. | | JokeWallpaper_com Images that makes fun of computers and popular culture. | | Using_Windows_Forms_Controls_in_ActiveX_Containers During the early phase of the .NET Frameworks rollout, there is a high probability that controls authored with Windows Forms will need to be consumed in ActiveX containers such as IE and VB6. This art | | Al\'s_Review_of_Fujitsu\'s_Poqet_PC_Plus Geared towards the Plus model of the Poqet, Al wong gives some hard to find information. Includes technical information for the serial port and setup help. | | Computing_Machinery_and_Intelligence Turing's original 1950 article on machine intelligence, where he introduces the famous Turing Test, and started this profound multi-decade debate. | | Trendy_Outfitters Retailer of brand name laptop bags, designer bags, backpacks and luggage. | | RFC_1959 An LDAP URL Format. T. Howes, M. Smith. June 1996. | | The_Executive_Computer;_A_Web_of_Networks,_an_Abundance_of_Services An early New York Times article on the Internet. (February 28, 1993) | | The_C++_Resources_Network Includes references, tutorials, and a forum section where users can share their problems and ideas. | | Earth_Computer_Technologies LCD distributor/remarketer: surplus LCDs, LCD controllers, LCD monitors, touch monitors, flat panels, POS and notebook screens. | | Shasta_Marketing_Consultants Offers design, marketing, and hosting. Located in Mount Shasta, California, United States. | | Arcdesign Web design and logo design for small businesses and individuals. | | MCII_Web_Development Website design, e-commerce, and database development. | | Net_Trend Offers design and hosting. Located in Victoria, Australia. | | Network_Cybernetics_Corporation Design, graphics, search engine optimization, Linux-based hosting, and custom application development using non-Microsoft solutions. Located in Dallas, Texas, United States. | | Hex_Mixer HTML color charts for browser-safe color selection. Provides both hex codes and RGB values. | | Ant-contrib_project SourceForge project for potential ant contributions. Currently consists of a C/C++ compilation task and various logical tasks. |
|
Excel
Excel
Contents:
Introduction
Named Ranges
Matrix Operations in Excel
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. 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.
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.
|
|
