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.
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).
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.
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:
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.
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.
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.
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.
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:
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.