User manual MATLAB SPREADSHEET LINK EX 3

[. . . ] Spreadsheet LinkTM EX 3 User's Guide How to Contact The MathWorks Web Newsgroup www. mathworks. com/contact_TS. html Technical Support www. mathworks. com comp. soft-sys. matlab suggest@mathworks. com bugs@mathworks. com doc@mathworks. com service@mathworks. com info@mathworks. com Product enhancement suggestions Bug reports Documentation error reports Order status, license renewals, passcodes Sales, pricing, and general information 508-647-7000 (Phone) 508-647-7001 (Fax) The MathWorks, Inc. 3 Apple Hill Drive Natick, MA 01760-2098 For contact information about worldwide offices, see the MathWorks Web site. Spreadsheet LinkTM EX User's Guide © COPYRIGHT 1996­2010 by The MathWorks, Inc. The software described in this document is furnished under a license agreement. The software may be used or copied only under the terms of the license agreement. [. . . ] The option value is zero for prices significantly 2-17 2 Solving Problems with the Spreadsheet LinkTM EX Software above the exercise price. Ignore the zeros that correspond to a zero in the price tree. 5 Try changing the data in B4:B10 and reexecuting the Spreadsheet Link EX functions. Note If you increase the time to maturity (B7) or change the time increment (B8), you may need to enlarge the output tree areas. 6 When you finish the example, close the figure window. 2-18 Calculating and Plotting the Efficient Frontier of Financial Portfolios Calculating and Plotting the Efficient Frontier of Financial Portfolios MATLAB and Financial Toolbox functions compute and plot risks, variances, rates of return, and the efficient frontier of portfolios. Efficient portfolios have the lowest aggregate variance, or risk, for a given return. Microsoft Excel and the Spreadsheet Link EX software let you set up data, execute financial functions and MATLAB graphics, and display numeric results. This example analyzes three portfolios, using rates of return for six time periods. In actual practice, these functions can analyze many portfolios over many time periods, limited only by the amount of computer memory available. Note This example requires the Financial Toolbox software. 1 Click the Sheet5 tab on ExliSamp. xls. The worksheet for this example appears. 2-19 2 Solving Problems with the Spreadsheet LinkTM EX Software 2 Make A15 the active cell. The Spreadsheet Link EX function transfers the labels that describe the output that the MATLAB software computes. 3 Make A16 the active cell to copy the portfolio return data to the MATLAB workspace. 4 Execute the functions in A19 and A20 to compute the Financial Toolbox efficient frontier function for 20 points along the frontier. 5 Execute the Spreadsheet Link EX functions in A23, A24, and A25 to copy the output data to the Excel worksheet. The worksheet looks as follows. 2-20 Calculating and Plotting the Efficient Frontier of Financial Portfolios The data describes the efficient frontier for these three portfolios: that set of points representing the highest rate of return (ROR) for a given risk. For each of the 20 points along the frontier, the weighted investment in each portfolio (Weights) would achieve that rate of return. 6 Now move to A28 and press F2; then press Enter to execute the Financial Toolbox function that plots the efficient frontier for the same portfolio data. The following figure appears. 2-21 2 Solving Problems with the Spreadsheet LinkTM EX Software The light blue line shows the efficient frontier. Note the change in slope above a 6. 8% return because the Corporate Bond portfolio no longer contributes to the efficient frontier. 7 To try running this example using different data, close the figure window and change the data in cells B4:D9. Then reexecute all the Spreadsheet Link EX functions. The worksheet then shows the new frontier data, and the MATLAB software displays a new efficient frontier graph. When you finish this example, close the figure window. 2-22 Mapping Time and Bond Cash Flows Mapping Time and Bond Cash Flows This example shows how to use the Financial Toolbox and Spreadsheet Link EX software to compute a set of cash flow amounts and dates, given a portfolio of five bonds with known maturity dates and coupon rates. 1 Click the Sheet6 tab on ExliSamp. xls. The worksheet for this example appears. 2 Make A18 the active cell. Press F2, then Enter to execute the Spreadsheet Link EX function that transfers the column vector Maturity to the MATLAB workspace. 2-23 2 Solving Problems with the Spreadsheet LinkTM EX Software 3 Make A19 the active cell to transfer the column vector Coupon Rate to the MATLAB workspace. 4 Make A20 the active cell to transfer the settlement date to the MATLAB workspace. 5 Execute the functions in cells A23 and A24 to enable the Financial Toolbox software to compute cash flow amounts and dates. 6 Now execute the functions in cells A27 through A29 to transform the dates into string form contained in a cell array. 7 Execute the functions in cells A32 through A34 to transfer the data to the Excel worksheet. 2-24 Mapping Time and Bond Cash Flows 8 Finally, execute the function in cell A37 to display a plot of the cash flows for each portfolio item. 2-25 2 Solving Problems with the Spreadsheet LinkTM EX Software 9 When you finish the example, close the figure window. 2-26 3 Function Reference Link Management (p. 3-3) Work with link management functions Work with data management functions 3 Function Reference Link Management matlabinit MLAutoStart MLClose MLOpen MLUseCellArray Initialize Spreadsheet Link EX software and start MATLAB process Automatically start MATLAB process End MATLAB process Start MATLAB process Toggle MLPutMatrix to use MATLAB cell arrays 3-2 Data Management Data Management matlabfcn matlabsub Evaluate MATLAB command given Microsoft Excel data Evaluate MATLAB command given Microsoft Excel data and designate output location Create or append MATLAB matrix with data from Microsoft Excel worksheet Delete MATLAB matrix Evaluate command in MATLAB software Import current MATLAB figure into Microsoft Excel spreadsheet Write contents of MATLAB matrix to Microsoft Excel worksheet Write contents of MATLAB matrix in Microsoft Excel VBA variable Set empty cells to NaN or 0 Create or overwrite MATLAB matrix with data from Microsoft Excel worksheet Create or overwrite MATLAB matrix with data from Microsoft Excel VBA variable Return standard Spreadsheet Link EX errors or full MATLAB errors using MLEvalString MLAppendMatrix MLDeleteMatrix MLEvalString MLGetFigure MLGetMatrix MLGetVar MLMissingDataAsNaN MLPutMatrix MLPutVar MLShowMatlabErrors 3-3 3 Function Reference MLStartDir MLUseFullDesktop Specify MATLAB current working folder after startup Specify whether to use full MATLAB desktop or MATLAB Command Window 3-4 4 Functions -- Alphabetical List matlabfcn Purpose Syntax Evaluate MATLAB command given Microsoft Excel data Worksheet: command inputs matlabfcn(command, inputs) MATLAB command to evaluate. Embed the command in double quotation marks; for example, "command". Variable length input argument list passed to a MATLAB command. The argument list may contain a range of worksheet cells that contain input data. Description Passes the command to the MATLAB workspace for evaluation, given the function input data. [. . . ] MATLAB matrix is a 1-by-1 zero matrix. Cause Corresponding Excel worksheet cells are empty. You used quotation marks around the data-location argument in MLPutMatrix or MLAppendMatrix. Solution Excel worksheet cells must contain only numeric or string data. Correct the syntax to remove quotation marks. MATLAB matrix is empty ([ ]). VBA matrix is empty. You referenced a nonexistent VBA variable in MLPutVar. [. . . ]