Batch Processing Using SigmaPlot Automation


A common task is to perform a set of identical operations on data sets in a group of files. This is batch processing. For example, you might want to read multiple Excel files from a plate reader, determine concentrations from a standard curve, fit a four parameter logistic function to replicate data in each file, create a graph of the data and fit, and export certain fit parameters to an Excel results file.

Since the automation language in SigmaPlot is written for Visual Basic then implementing a batch processing task is simply a matter of writing a SigmaPlot macro or a Visual Basic program.

An excellent example of a batch processing program is the Batch Process Excel Files macro in SigmaPlot. The dialog from this macro is shown in Figure 1. It is intended as a basic design around which you can build your own macro to perform a specific task.

It lets you select a group of Excel files by clicking on the Add File button that invokes the file open dialog, fit an equation to a data region in the Excel file that you specify and produce a graph and report of the results. In this case the results for each file are placed in a separate section of a SigmaPlot notebook. This can be modified to place the results in an Excel file if you wish.

Figure 1. The dialog for the Batch Process Excel Files macro.

Each of the Excel files from a well plate reader looks like the one shown in Figure 2. Five replicate measurements of specific binding are shown in columns C through G. For tutorial purposes, the free radioligand concentration has been added in column B. The macro has been written to fit an equation to two columns of data so for this example we will ignore the replicates. It is easy to modify the macro to include the rowwise replicate format in the curve fit.

Figure 2. One of the Excel files to analyze.

You can then select the appropriate region of the Excel file containing the data to fit. This is shown in Figure 3 for the data in Figure 2.

Figure 3. Region selection for the block of data to fit.

The hyperbolic function is selected to fit each data set and a simple scatter plot is used to display the results. Note that every equation in the SigmaPlot curve fit library is listed in the dropdown box in Figure 4. It is easy to do this since SigmaPlot Automation allows you to search a notebook (in this case the standard.jfl notebook containing all the curve fit equations) for fit objects (or objects of any type) and create a list of them.

If you want you can substitute in this macro a different notebook with another collection of fit equations. The new equations will then appear in the dropdown list. If a user-defined equation is added to standard.jfl then it will appear in the list.

Figure 4. The hyperbolic function “Single Rectangular, 2 Parameter” is selected to fit each data set. The results will be displayed with a scatter plot.

The batch process results are then saved in a notebook. You may Browse to select the appropriate file.

Figure 5. Specify the notebook to save the results.

For the five files shown in Figure 1, the notebook contains five sections each with worksheets with individual data sets, scatter plots of the data, fit results and detailed curve fit reports (Figure 6).

Figure 6. The notebook with results of the batch processing. Each section contains the analysis results of an Excel file.

The graph for the first data set is shown in Figure 7. The axis ranges and labels were modified

Figure 7. Results of the saturation binding curve fit for data in the first Excel file.

Possible Macro Modifications

This macro consists of multiple statements, of course, but getting the Excel files that will be batch processed is just one Basic call


and you can see that files of other types, ASCII for example, could also be opened. Open the macro editor for this macro to see the programming details. The macro is part of the Toolbox in SigmaPlot.

This macro interactively opens Excel files and adds them to a list. You could just as easily read all files with an XLS extension from a particular folder and avoid the need for user interaction. To do this you might use the import statement

\WELLPLATE DATA.XLS", 0, 0, 0, 0, 0, 0, ".XLS")

Try SigmaPlot FREE for 30 Days!