May 16, 2016

Using Approximation Models With Excel

One of key features of pSeven, as a powerful design space exploration platform, is the capability to train approximation models using data samples or simulation results. Trained models become a fast alternative to the original time-consuming computational code. In particular, an approximation model in pSeven provides the following methods:

Evaluation – calculates model output values at a given input point.
Gradient evaluation – calculates gradients, first order partial derivatives of model output functions.
Accuracy evaluation – calculates accuracy estimates for the output and gradient values (these functions are available if the model was trained with the GTApprox/AccuracyEvaluation option).

Often there is a task to export a model trained with pSeven in order to use it with another engineering platform. To enable this, pSeven provides functions exporting a model to various common formats, such as MATLAB-compatible code or C source. A recent improvement in model export is the function that generates C code highly compatible with Microsoft Excel – which remains one of the most popular platforms for engineering calculations, data analysis and visualization.

Since version 6.7, pSeven provides an option to export a special version of model C code intended for compiling a DLL that works natively with Microsoft Excel. Importing such a DLL to your spreadsheet allows to call model methods as user-defined Excel functions. This note explains the general workflow of importing and using pSeven models in Excel.

Workflow

General steps to make a pSeven approximation model available in Excel are:

From pSeven, export the model to C code.
Compile a model DLL.
Import the model DLL into Excel.
Define Excel functions that call model methods from the DLL.

The Excel-specific export format added in pSeven 6.7 allows to greatly simplify the import process. In addition to the model C code, pSeven now automatically generates the VBA code for Excel which requires only a minimal edit to make model methods available in your spreadsheets, as described further.

Step 1. Exporting Model Code

In pSeven, a model can be trained with Model Builder or loaded from an existing file. Model training is out of this article's scope; we assume that the model has already been created and added to a report in Analyze.

Models contained in a report are listed on the Models pane in the report database. From here you can export model code, doing the following:

Open the Report database pane.
Select the model to export on the Models pane.
Select "Export to file..." from the context menu on the Models pane or click the model export button on the pane's quick access toolbar. This action will open the Export model dialog.

In the Export model dialog:

Select “C source for Microsoft Excel VBA” as the export format.
Specify the export file name in the “File” input field (pseven_model.c in the example screenshot).
Specify the function name in the “Function name” field (model in the example).
Click the Export button. Generated code will be saved to the file you specified (located in the current project directory by default).

The resulting file contains model C code that should be compiled to a DLL and the VBA code for Microsoft Excel. VBA code is found in the comment block on top. Since this code is commented out, there is no need to remove it when you compile the model. The VBA code is required when importing the compiled DLL to Excel.

Note that the function name you specified in the Export model dialog is used as a base name for model methods. Most models provide several functions (such as evaluating model ooutputs or gradients), and since all methods must have unique names, pSeven will add a specific suffix to the name of each method. This suffix also indicates the purpose of the function as explained further.

Step 2. Compiling a Model DLL

After the export, model code has to be compiled to a DLL to allow importing it into Excel. pSeven generates code for the Microsoft Visual C++ compiler (MSVC). If using MSVC, no changes are required to the C code. Using another compiler may require certain code edits depending on the compiler.

Note that when importing a DLL to Excel, you will need to specify the full path to the DLL file. It is recommended to store the compiled DLL at some convenient location; further it is assumed that the DLL is saved to C:\Users\tmp\pseven_model.dll, for example.

If you are using Microsoft Excel 2010 or later, it is also advised to pay attention to the compatibility between the 32-bit and 64-bit versions. A detailed description of this topic is available at MSDN: see Compatibility Between the 32-bit and 64-bit Versions of Office 2010.

Step 3. Importing a Model DLL to Excel

The C source file exported from pSeven contains everything required to provide access to the DLL from Excel through a VBA module. Such module declares new functions that make exported model methods available in Excel. The VBA module code is found in the comment on top of the model source. You only need to copy the code from the comment to Excel and specify the path to DLL in the Declare statements (since Excel requires to specify the absolute path to an imported DLL).

To copy the VBA code:

Open the Developer pane in Excel and add a new module.
Open the model C source and copy the VBA code found in the beginning comment. Note that you have to copy the code only, without the lines that start and end a C-style comment (“/* … */”).
Paste the copied VBA code to the new module you have added.

The Declare statements that have to be corrected manually are found in first lines of the copied VBA code. You are required to replace the placeholders in these lines with an exact path to the DLL. Declare statements in the VBA code generated by pSeven look like this:

Declare PtrSafe Function model_impl Lib "<put DLL path here>" Alias "model" (x As Variant, ByVal idx_f As Integer, out As Variant) As Integer

There are several declarations, one for each exported model method. In each statement, the "<put DLL path here>" placeholder should be replaced with the actual path to the compiled DLL. For example, if the DLL is found at C:\Users\tmp\pseven_model.dll, the edited declaration will look like this:

Declare PtrSafe Function model_impl Lib "C:\Users\tmp\pseven_model.dll" Alias "model" (x As Variant, ByVal idx_f As Integer, out As Variant) As Integer

Step 4. Calling a Model From Excel

After you edit the DLL path in the VBA code copied to Excel, you can use the functions it declares to perform spreadsheet calculations. Generally, functions available in Excel are:

model(point, index) - evaluates model output component with the given index at the given input point.
modelGrad(point, output_index, input_index) - evaluates the gradient of the output_index output component with respect to the input_index input variable at the given point.
modelAE(point, index) - calculates the accuracy evaluation estimate for an output component with the given index at the given input point.
modelGradAE(point, output_index, input_index) - calculates the accuracy evaluation estimate for model gradients.
modelInfo(info) - gets model information, where the info argument is one of the following strings:

"size_x" – get model input dimension.
"size_f" – get model output dimension.
"has_ae" – check whether the model supports accuracy evaluation.

Note that all functions from the same model have the same basename – the one you have specified in the “Function name” field in the Export model dialog in pSeven (here for example it is “model”).

Let us consider the basic rules of passing arguments to these functions, using the model function as an example (this function calculates model output values).

In general, model input and output can be multidimensional. The model used here as an example has input dimension 3 and output dimension 2. In this case, the point argument to model should be a range including 3 columns, and the index argument can be 1 or 2 (Excel uses 1-based indexing).

We will use the imported model to plot a slice along one of the input axes. To do this, two input values are fixed (columns “x2” and “x3” in the example), and values in the “x1” column are varied. Columns “y1” and “y2” in the example spreadsheet contain formulas that calculate output values:

y1 = model(A3:C3; 1)
y2 = model(A3:C3; 2)

Resulting data from columns “y1” and “y2” can then be used for further analysis, plotting and so on. For example, the plot below shows a slice of model outputs along the x1 axis.