Fully bi-directional linking software provides powerful assist

click to enlarge  Figure 1: Mathematica’s powerful programming language allows visualization of data and equations in ways not possible in Excel alone.

Mathematica Link for Excel 3 is a much-needed link between the two programs that serves the user well in both directions of export. For those of us who use Microsoft Excel on a regular basis, we appreciate the data manipulative characteristics and ease-of-use features, but cringe at the thought of doing anything but routine statistical and mathematical analysis in that software. It’s not that Excel does not have extensive data analysis features, but the more advanced features have many nuances of their own, and the novice user needs to be constantly vigilant. Also, the scope of these analyses is dwarfed by what Mathematica can do.

From the other side, less-experienced Mathematica users sometimes yearn for the ease of data entry and manipulation features of Excel and, for years, have mightily wished for an easy way to exchange data between the two platforms. Wolfram solved the problem some years ago with this fully bidirectional linking software that quickly and easily integrates the two programs. This utility is therefore popular with analysts in a wide variety of disciplines.

The advantages here are obvious and many:
• an instant connection for Excel users to the thousands of very powerful mathematical functions in Mathematica to perform symbolic computations
• plotting of complex equations
In addition, there are a number of intelligent features, such as read-and-write routines that will automatically resize to fit the data set and smart commenting technology that streamlines notebook sharing. Key new features of this version are listed in Table 1, and a longer list of improved features is available from the Web site. This product requires Mathematica 5.0 or higher, and Excel 2002/XP or higher, and runs on Windows. A 138-page PDF manual is available online.Let’s now delve into the mechanics of import/export, producing a few items for the spreadsheet, and importing Excel data into Mathematica. We start by loading the ExcelLink package in Mathematica by merely typing + , as is customary for all Mathematica commands. Then by typing ? ExcelLink`* , we can quickly see a list of functions provided by the package (I counted 57).

To specify a location in Excel and then assign or retrieve data from it, the manual offers the following three easy steps/examples:
• Needs[“ExcelLink`”] — calls the link)
• Excel[“A1”] = “hello” — assigns the word hello to cell A1 in the active spreadsheet)
• Excel[“A1”] — calls the contents of A1 to the Mathematica worksheet)

By using standard Mathematica format with curly braces and commas, rows, columns and arrays may be filled in Excel. Once graphics are produced in Mathematica, they

click to enlarge  Figure 2: Data can be exchanged easily between Mathematica and Excel.

are easily moved to Excel with a single assignment command. As always though, watch the case • Mathematica is case sensitive (I spent a lot of time years ago learning this the hard way!).

Functions follow a general convention that is easily typed through shorthand notation that greatly alleviates the burden of keystrokes. Many of the names are very intuitive and allow for simple data import of large arrays of data from Excel. Just as valuable are Mathematica routines to validate the imported data ranges. All of this has been done in a new Excel sheet that appears when we call Excel from Mathematica. To specify a specific file from which to import data, it is only necessary to use the ‘ToFileName’ command and specify the ‘ExcelDirectory’ link. Data can then be read from a single, or multiple tabs within a single file. Needed data ranges are then specified for use in calculations. A graphic of the data is easily produced in Mathematica and appears where directed in the Excel spreadsheet.

Special macros for Excel can be created in Mathematica and repetitively used in Excel. These macros can use Mathematica functions to quickly produce intricate graphics and to allow for annotation. There are relatively simple ways to check and trap errors while testing these macros, and error messages can be specified by the programmer to convey maximal information to an end user. Feedback may be provided on how the analysis is proceeding just as easily with the ‘ExcelStaus’ command. Now, let’s take a look at the other side and see how to use the power of Mathematica from within Excel.

By using the Start>Programs menu in Windows, the Mathematica Link Add-in is quickly installed. Once installed, the Mathematica toolbar will appear in Excel and may be docked in any convenient location. After this initial start-up, the Tools/Add-ins menu is used to load and unload the link.

The simplest introduction to using Mathematica from within Excel is to type the ‘Eval’ function and use this with various Mathematica expressions for mathematics or graphics.

click to enlarge  Figure 3: Histograms, labeled scatter plots, geographic maps and 3-D parametric plots can be created in Excel with the bi-directional linking software.

Four other worksheet functions (EXPR, DATA, RULE and CALC) also are available and, by combining these expressions, powerful analyses may be performed in very flexible ways. By clicking the Functions button on the new toolbar, the Mathematica Function Wizard is launched to assist in selecting the proper function from among the thousands offered. The user can then use this Wizard to specify arguments and options to the function. Macros formulated in Mathematica may be called and run by special toolbar buttons created within Excel. The special Mathematica clipboard is called to facilitate evaluation of expressions in much the same way as is done in the notebooks. Help is always accessible, as the Link manual is now installed with the new toolbar. This manual contains reference sections for both programs and is a very useful ‘quick review’ guide for infrequently used functions and commands.

The above features are always available, as the link is automatically loaded each time Excel is started. This feature can be defeated by un-checking the appropriate box in the Excel Add-ins Manager.
br>I have found this package almost intuitive in many aspects. However, for the user who is new to Mathematica, there may be a maddening learning curve to the braces/brackets and case-sensitive nature of the code. It also takes a while to assimilate those commands most useful for the particular needs of any individual. However, once the initial hump is over, the curve flattens and progress is fairly quick. It is unfortunate that the applications developed in Mathematica cannot be deployed as stand-alone functions to those users of Excel who are not mathematically inclined and, therefore, do not have Mathematica on their computers, but I’ll leave that for another version.

As a long-time Mathematica user (at the coder level only), I know this linking software will be especially useful, as I now have a tool to quickly move data from Excel — where it is usually collected, to Mathematica — where I can play with many of the mathematical statistical functions that greatly extend the information one gets from the menu-driven, applied statistical programs. For Mathematica users, this is definitely worth purchasing.

Table 1: New Features in Mathematica Link for Excel 3
• Display of typesetting and formatted output in Excel
• Ability to create Mathematica-based macros
• Suite of Mathematica functions to interact with and automate Excel

Availability

• $249
• Upgrade and educational pricing available

Wolfram Research
100 Trade Center Drive, Champaign, IL 61820-7237
1-800-965-3726; info@wolfram.com; www.wolfram.com

John Wass is a statistician with GPRD Pharmacogenetics, Abbott Laboratories. He may be reached at editor@ScientificComputing.com.