The Excel Addin consists of a set of VBA functions that can be called from Microsoft Excel, either as a function directly from a worksheet, or as part of a macro. The Mercury Addin for Excel makes it simple to send complete data sets from Excel to GAUSS for analysis, and to retrieve the results back in to your Excel worksheet. From the user's point of view, it appears that Excel has suddenly gained an entire set of new functionality - that of GAUSS - but with an additional twist. You no longer wait while Excel computes. Rather, all the power, speed, depth and accuracy of GAUSS is a macro away.
The following code provides a simple example of how one would write a VBA macro in Excel to do something trivial - raise each element of a spreadsheet array to some power.
- Sub gausstest()
- Sheets("sheet1").Select
- mercury.load
- ge.WorkspaceCreate
- ge.RangePut "a3:c6", "x"
- ge.ExecCode "z = x^2; "
- ge.RangeGet "e3","z"
- End Sub
The macro consists of a sub called gausstest. The worksheet (sheet1) contains the input data, and this sheet is made the active sheet in line 2. An instance of the Mercury class (ge) is created in line 3. The array in a3:c6, which is the data that we are interested in using, is copied to GAUSS and stored with the name x using the RangePut command in line 5. The operation
z=x^2;
is carried out using GAUSS in the command ExecCode shown in line 6. This creates the matrix z, which is retrieved into the worksheet using the RangeGet command in line 7, with the (1,1) element occurring at e3.
This example portrays one of the simplest ways of running GAUSS commands; that is, by embedding them as string arguments to the ExecCode command within an Excel macro. The string can contain a number of GAUSS commands, or the name of a GAUSS command file, or even a worksheet range containing GAUSS commands. GAUSS library functions are available as usual. The addin can be used as a macro - as shown above, or as an Excel function.
Hence all the power of GAUSS is available for mathematical computation and analysis, while the spreadsheet provides the advantages of data presentation and charting. The net effect is to have the functionality of GAUSS within Excel. The Mercury Addin also provides a link to the Mercury reference file on the Excel Tools menu.
Click here for a demonstration of the Black & Scholes Model for evaluating future call and put options using Excel and the Mercury Addin.