Excel Link

Using Excel Link

Example 1: Regression and Curve Fitting	Data regression and curve fitting.
Example 2: Interpolating Data	Uses an Excel worksheet to organize and display the original data and the interpolated output data.
Example 3: Pricing a Stock Option with the Binomial Model	Uses the binomial model to price an option.
Example 4: Calculating and Plotting the Efficient Frontier of Financial Portfolios	Analyzes three portfolios, using rates of return for six time periods.
Example 5: Bond Cash Flow and Time Mapping	Computes a set of cash flow amounts and dates given a portfolio of five bonds.

This section shows how Microsoft Excel, Excel Link, and MATLAB work together to solve real-world problems.

These examples ship with Excel Link in the file ExliSamp.xls, which is installed in <matlab>/toolbox/exlink/. Start Excel, Excel Link, and MATLAB. Open and try executing the examples.

Note Examples 1 and 2 use only basic MATLAB functions. Examples 3, 4, and 5 use functions in the optional MATLAB Financial Toolbox. The Financial Toolbox in turn requires the Statistics and Optimization Toolboxes.

Example 1: Regression and Curve Fitting

Regression techniques and curve fitting attempt to find functions that describe the relationship among variables. In effect, they attempt to build mathematical models of a data set. MATLAB provides many powerful yet easy-to-use matrix operators and functions to simplify the task.

This example does both data regression and curve fitting. It also executes the same example in a worksheet version and a macro version. The example uses Excel worksheets to organize and display the data. Excel Link functions copy the data to MATLAB and execute MATLAB computational and graphic functions. The macro version also returns output data to an Excel worksheet.

Worksheet Version

To try the worksheet-only version of this example, click the Sheet1 tab on ExliSamp.xls.

The worksheet contains one named range: A4:C28 is named DATA and contains the sample data set:

Make E5 the active cell. Press F2, then Enter to execute the Excel Link function that copies the sample data set to MATLAB. The data set contains 25 observations of three variables. There is a strong linear dependence among the observations; in fact, they are close to being scalar multiples of each other.
Move to cell E8 and press F2, then Enter. Repeat with cells E9 and E10. These Excel Link functions tell MATLAB to regress the third column of data on the other two columns. They create a single vector y containing the third-column data, and a new three-column matrix A consisting of a column of ones followed by the rest of the data.
Execute the function in cell E13. This function computes the regression coefficients by using the MATLAB backslash operation to solve the (overdetermined) system of linear equations, A*beta = y.
Execute the function in cell E16. MATLAB matrix-vector multiplication produces the regressed result (fit).
Execute the functions in cells E19, E20, and E21. These functions compare the original data with fit; sort the data in increasing order and apply the same permutation to fit; and create a scalar for the number of observations.
Execute the functions in cells E24 and E25. Often it is useful to fit a polynomial equation to data. To do so, you would ordinarily have to set up a system of simultaneous linear equations and solve for the coefficients. The MATLAB polyfit function automates this procedure, in this case for a fifth-degree polynomial. The polyval function then evaluates the resulting polynomial at each data point to check the goodness of fit (newfit).
Finally, execute the function in cell E28. The MATLAB plot function graphs the original data (blue circles), the regressed result fit (dashed red line), and the polynomial result (solid green line); and adds a legend.
.

Since the data is closely correlated but not exactly linearly dependent, the fit curve (dashed line) shows a close, but not an exact, fit. The fifth-degree polynomial curve, newfit, represents a more accurate mathematical model for the data.

When you have finished this version of the example, close the figure window.

Macro Version

To try the macro-and-worksheet version of this example, click the Sheet2 tab on ExliSamp.xls.

Make cell A4 the active cell, but do not execute it yet.

Cell A4 calls the macro CurveFit, which you can examine from the Visual Basic environment.

While this module is open, pull down the Tools menu and select References. In the References window, make sure there is a check in the box for excllink.xla. If not, check the box and click OK. You may have to use Browse... to find the excllink.xla file.

Back in cell A4 of Sheet2, press F2, then Enter to execute the CurveFit macro. The macro executes the same functions as in Step 1 through Step 7 of the worksheet version (in a slightly different order), including plotting the graph. Plus, it copies the original data y (sorted), the corresponding regressed data fit, and the polynomial data newfit, to the worksheet. (The last three MLGetMatrix functions in the CurveFit macro copy data to the Excel worksheet.)

When you have finished the example, close the figure window.

Saved Worksheets Example 2: Interpolating Data