Quantcast

Newsletters

  • Contact sales

Creating Deployable Spreadsheet Applications with MATLAB Excel Builder

By Jim Stewart, MathWorks and Chris Garvin, MathWorks

MATLAB Excel Builder automates the process of creating independent Excel add-ins from M-code. This article walks you through a simple example that illustrates how to use Excel Builder and Visual Basic for Applications (VBA) to create a custom GUI-based application that plugs right into Microsoft Excel.

Portfolio Analysis

In our example, we will develop a deployable financial application from our MATLAB code. We will build a component to plot the price history, moving averages, and efficient frontier for our portfolio, using three M-files to implement these tasks. The price histories of the funds will indicate each security's performance during the given time period. Using the same historical data set, we can make trading decisions by applying leading and lagging moving averages to closing price histories. In this example, we will display 5-day leading and 20-day lagging moving averages with the price histories. The efficient set of portfolios is identified from the available universe of portfolios and displayed as the portfolio with the lowest risk (aggregate variance).

Creating a COM Object from MATLAB M-files

The development of our portfolio optimization spreadsheet begins with the Excel Builder mxltool GUI. We simply name our component, add our M-files, and build.

At this point, you may want to download the finished component, supporting libraries, and Excel spreadsheets from MATLAB Central.

matlabxl_fig1_w.gif

 

Figure 1. mxltool GUI with our three M-files added and the necessary settings made. Click on image to see enlarged view.

The remainder of this article focuses on integrating our component into Excel, and refers to the Visual Basic code and forms contained in the portopt.xls workbook included in the download. Follow the included instructions to register the necessary components on your system, then start Excel, load the portopt.xls workbook, and follow these steps:

  1. Select Tools->Macro->Visual Basic Editor.
  2. Press F2 for the Object Browser.
  3. Select Portfolio in the Libraries drop-down.

The object browser will now display our opt401k class with the three methods corresponding to the original M-files, and one class property for guiding the data conversion process between Excel and MATLAB types.

matlabxl_fig2_w.gif

 

Figure 2. Run-time type information that is bound into the COM object during compilation enables the Visual Basic Object Browser to display our object's attributes. Click on image to see enlarged view.

Integrating the Component with Microsoft Excel

Our spreadsheet application will have a main form for managing the selected data and a subform for selecting data from the worksheet. The main form will need a list view control to manage worksheet selections using buttons to add, edit, and remove selected data. Buttons will also be needed to invoke the class methods for plotting price history, moving average, and efficient frontier. A second child form will enable the selection of worksheet data, setting the fund name, and initializing the allocation value. Figure 3 shows the basic layout of the two forms.

matlabxl_fig3_w.gif

 

Figure 3. Forms and controls are created with the Excel Visual Basic editor. Click on image to see enlarged view.

matlabxl_fig4_w.gif

 

Figure 4. The Visual Basic code that calls the pricehistory method when the corresponding button is pressed. Click on image to see enlarged view.

Once the forms are drawn and the controls are all in place, we can implement the code to load an instance of our opt401k class, call the class's methods when asked to, and handle other user-initiated events in the application. When the main form is shown, we can add fund data to the list and call one of the three class methods by clicking one of the buttons to the right of the list. Each button fires a button_click event whenever it is pressed. Figure 4 shows the click event handler for the Price History button. Similar handlers are implemented for the Moving Average and Efficient Frontier buttons.

matlabxl_fig5_w.gif

 

Figure 5. The completed application. Click on image to see enlarged view.

Deploying the Stand-alone Spreadsheet Application

Now that we have completed our application, we can create an Excel Add-In, and deploy our application. Before saving the Add-In we add code to the Workbook_AddinInstall and Workbook_AddinUninstall event handlers to allow the user to invoke the tool by selecting Portfolio from the Excel Tools menu. Selecting Save As from Excel's File menu and selecting "Microsoft Excel AddIn" as file type creates the finished product. As a final step, use the mxltool packager to create a self-extracting executable that can be used to deploy our Add-In onto other computers.

Testing the Application

To test our application, we need to open the MyPortfolio.xls workbook that came with the download. This workbook contains historical data from nine typical mutual funds. To load and use the add-in follow these steps:

  1. Open the MyPortfolio.xls workbook.
  2. Select Add-Ins from the tools menu and check off the "PortOpt" Add-In.
  3. Return to the tools menu and select Portfolio to invoke the tool.
  4. For each fund, enter the fund data by clicking Add on the main form. Type in the fund name in the first edit box, select the worksheet range for the fund, and enter an allocation percentage.

Figure 5 above shows the three plots for this worksheet.

This application illustrates how you can use MATLAB Excel Builder to quickly convert complex MATLAB algorithms and graphics into Excel Add-Ins. Once created, your Add-Ins can be distributed free of charge.

Published 2002

Receive the latest MATLAB and Simulink technical articles.

Related Resources

Latest Blogs