SPARTAN 5.1 User's Guide: Chapter 11

Spartan 5.1 User's Guide

Chapter 11: Spreadsheets

This section reviews the features available in Spartan's spreadsheet for handling data from a series of calculations. These include plot construction and data fitting to a variety of simple functional forms.

Spartan allows grouping of molecules into lists. Some of these lists are very closely related and are automatically formed by Spartan. Examples include lists which result from conformation searching (see Section 7.2), as well as sequences of molecules which follow a particular vibrational mode (see Section 7.4) or, more generally, any user-defined set of coordinates (see Section 7.5). Lists may also result from single or multiple substitutions on a "parent" molecule (see Section 7.6), or simply follow from grouping molecules as convenient (see Group As, Section 4.7). In all of these situations, lists may be treated either as single molecules or as an entire group insofar as job setup (specification of kind and level of calculation and request for properties and/or graphics), and need to be treated as a group insofar as job submission.

Creation (or selection) of a list leads to display of a spreadsheet dialog (or
simply spreadsheet).

Initially, the first member of the list will be selected, and only its structure
model will be displayed on screen. A different member of the list may be selected by clicking on the box to the left of the name of the member (far left in
the spreadsheet).

Controls at the bottom of the spreadsheet provide a variety of display options:

Multiple
Controls whether a single member of the list, or several members of the list, are simultaneously displayed on screen. Multiple "off" indicates only a single member (that selected in the spreadsheet) is to be displayed, while Multiple "on" indicates that all selected members of the list are to be displayed.
The result of selecting individual members of the list depends whether Multiple is "on" or "off". With Multiple "off", selecting a new member deselects the previously selected member. With Multiple "on", selecting a new member leaves the original member(s) selected, while selecting the "selected" entry deselects it. The only exception is that the "last" member cannot be "deselected".
Animate
Animates (rapid stepping through) the molecules in a list. It is particularly useful for visualizing the results of a normal-mode analysis (see descriptions in Sections 7.4 and 9.6), or the driving of coordinates from one structure to another (see description in Section 7.5). Note that animation is not restricted to structure; any graphic associated with a particular molecule may also be animated. Animation may only be turned "on" if Multiple is "off".
keys
Manually steps forward ( ) or backward ( ) through the list of molecules. Stepping is allowed only if Multiple is "off".
Expert
Extends the spreadsheet.

An extended text field appears at the top of the dialog, the contents of which indicate the "generating formula" of the selected header for a column or of an individual cell in the spreadsheet. Information, in the form of a header name or a formula, entered into this text field will also be entered into the header (to be propagated throughout the column) or into an individual cell.

Additional rows appear at the bottom of the spreadsheet. These display information relating to the entire set of column entries, for example, the sum of the entries or their average. Further information is provided under Spreadsheet Operations.

Section 11.1: Spreadsheet Operations

Molecules contained within a spreadsheet may either be acted on as individuals or be treated as part of a group. As individuals, each member has access to the full functionality of Spartan's graphical user interface, except for job submission, which needs to be handled for the entire group. Optionally, job setup (selection of level of calculation, request for properties, graphical surfaces, etc.) may either be done on an individual basis or for the collection as a whole.

Spartan's spreadsheet operations are typical of those of general purpose spreadsheets, although they are more limited.

Numerical Data
Numerical data may be directly entered into the spreadsheet. First, it is necessary to define a column header, by clicking above the next available column, entering a name and pressing Enter. Data are entered into the individual cells by clicking inside the appropriate cell, entering a numerical value and pressing Enter. Existing data (in any spreadsheet column) may be edited by clicking inside the appropriate cell, altering or replacing the existing number and pressing Enter.

Expressions

Column headers may involve algebraic expressions: name = expression. Expressions may contain numerical data, previously defined column headers, the following mathematical and support functions,

ABS(x)	absolute value	LN(x)	natural logarithm
ACOS(x)	inverse cosine	LOG(x)	log (base 10)
ASIN(x)	inverse sine	SIN(x)	sine
ATAN(x)	inverse tangent	SQRT(x)	square root
COS(x)	cosine	TAN(x)	tangent
EXP(x)	exponential

AVG(x)	average	NUM(x)	number of entries
MAX(x)	maximum	STDEV(x)	standard deviation
MIN(x)	minimum	SUM(x)	sum

the following quantities derived from molecular mechanics and quantum chemical calculations,

AM1AQ	AM1aq aqueous solvation energy (kcal/mol)
AM1HD	AM1hd hexadecane solvation energy (kcal/mol)
AM1OCT	AM1oct octanol solvation energy (kcal/mol)
AM1SM2	AM1-SM2 aqueous solvation energy (kcal/mol)
ANGLE(i,j,k)	angle involving atoms i, j, k (degrees)
CPKAREA	surface area of a space-filling model(Å2)
CPKVOLUME	volume of a space-filling model (Å3)
DIHEDRAL(i,j,k,l)	dihedral angle involving atoms i, j, k, l (degrees)
DIPOLE	dipole moment (debyes)
DISTANCE(i,j)	distance involving atoms i, j (Å)
ELECTROSTATIC(i)	electrostatic charge on atom i (electrons)
ENERGY	energy (hartrees)
ENERGYAM1AQ	energy including AM1aq aqueous solvent correction (hartrees)
ENERGYAM1HD	energy including AM1hd hexadecane solvent correction (hartrees)
ENERGYAM1SM2	energy including AM1-SM2 aqueous solvent correction (hartrees)
ENTROPY	entropy (cal/mol.degree)
FREEENERGY	free energy (kcal/mol)
HOMO(-N)	HOMO energy (hartrees)
LOGPC	LogP according to Crippen model
LOGPH	LogP according to Dixon-Hehre model
LOGPV	LogP according to Villar model
LUMO(+N)	LUMO energy (hartrees)
MULLIKEN(i)	Mulliken charge on atom i (electrons)
NATURAL(i)	natural charge on atom i (electrons)
ZEROPOINT	zero-point energy (kcal/mol)

and/or the following specialty functions.

ROW	the number of the row in the spreadsheet
ROW(molecule name)	the number of the row for molecule
REF(i, x)	the value of the x referenced to row i

Mathematical, "Spartan", and specialty functions need to be preceeded by "@". For example, @DIPOLE accesses the dipole moment. Arguments for mathematical functions may either be numbers, previously defined header labels, or other mathematical or "Spartan" functions (preceeded by an "@" sign), while arguments (i) for "Spartan" functions are atom labels, e.g., @DISTANCE (C1,C2) gives the distance between atom "C1" and atom "C2". Arguments (N) for HOMO and LUMO are decrement and increment values, respectively, and are optional.

The usual arithmetic operations are supported.

+	addition
-	subtraction
*	multiplication
/	division
^	raise to a power

It is necessary to press Enter following entry of the expression into the column header.

Examples of expressions include:

energy.area = @Energy/@AREA	energy divided by surface area
rel.energy = @Energy-@REF(6,@ENERGY)	energy relative to energy of molecule in row 6
equil = @EXP (-@Energy/592.1)	equilibrium constant at room temperature

Data filtering (see Section 11.2.4) requires that Boolean operations be carried out inside the spreadsheet. These are of the form: name = expression, where the expression may now include Boolean operators from among the following.

>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to
==	equal to
!=	not equal to
\|	or
&	and

Examples of expressions incorporating Boolean operators include:

energyfilter = @Energy < -99.43	"true" (0) for all energies < -99.43
rowfilter = @ROW> 10	"true" (0) all entries past row 10

The extended form of the spreadsheet (Expert "on") permits calculation and display of quantities which relate to the entire collection of molecules (rows in the spreadsheet). The most important are sum of the rows (SUM), the average (AVG), the standard deviation (STDEV) and the minimum and maximum values (MIN and MAX, respectively). Expressions involving these functions and other quantities are constructed in the same way as previously described, and entered into the header (left most) column at the bottom of the (extended) spreadsheet. For example, the expression "average = @AVG" provides the average value of the rows for each and every column of the spreadsheet.

Section 11.2: Menus in the Spreadsheet

Other editing/display functions associated with Spartan's spreadsheet are accessed by way of menus, from a menu bar.

Section 11.2.1: Sheet

Selection results in display of the following menu:

Calculator

Export

Print

Show Formulas

Calculator

Opens a "calculator", the workings of which have already been described in Section 3.7. The calculator and open spreadsheet are linked insofar that clicking inside a cell of the spreadsheet enters the numerical value into the calculator. This allows reaction energies to be computed directly from the tabulated data.

Export

Reformats the entire contents of the spreadsheet as "tab delimited" for export to Microsoft Excel or other general purpose spreadsheet programs. The user is presented with a file browser and must supply a name. An attempt to overwrite an existing file will be preceded by a warning message. Clicking on Export writes the spreadsheet to the file.

Print

Prints the spreadsheet (according to the specifications made in the last access to Print Setup; see Section 3.4).

Show Formulas/Show Values

Replaces the numerical values in the spreadsheet cells by their generating formulas. Upon selection, the menu entry is changed to Show Values, selection of which replaces the formulas by numerical values.

Section 11.2.2: Molecule

Selection results in the following menu:

Insert

Extract As

Delete

Delete to End

Decouple Coordinates

Align Coordinates

Insert

Inserts a molecule or list of molecules into the spreadsheet. Selection leads to the usual file browser. Double clicking on a name, or clicking on a name followed by clicking on Insert, inserts the molecule or list of molecules following the last entry of the spreadsheet. No sorting is done on the entries in the spreadsheet.

Extract As

Extracts one or more members from a list and saves them. Extract As functions with Multiple either "on" or "off". With Multiple "off", Extract As saves the selected member as a single molecule. With Multiple "on", Extract As saves all selected members of the list as a new list, unless only one member is selected, in which case this member is saved as a single molecule. Selection of Extract As results in a file browser, and request for a name. If the name supplied already appears in the working directory, this leads to a warning that pre-existing information is to be overwritten. Once a file name has been supplied, clicking on Extract saves the information and exits the dialog. The original spreadsheet is closed, but all molecules in the spreadsheet which were selected prior to Extract As remain on screen and constitute a new spreadsheet. If only a single molecule was extracted, it remains on screen but the spreadsheet disappears. Clicking on Cancel exits the dialog with no further action.

Delete

Eliminates from the spreadsheet whatever member is presently selected. A warning that the actions to be taken will result in deletion of all files associated with the particular molecule appears on screen.

Assuming that this is what is intended, deletion occurs by clicking on Delete. Clicking on Cancel removes the dialog but does not result in molecule deletion. Delete is not available with Multiple "on".

Delete to End

Eliminates from the spreadsheet whatever member is presently selected, and all members following it in the spreadsheet. A warning that the actions to be taken will result in deletion of all files associated with the particular molecule or molecules appears on screen.

Decouple Coordinates

Controls whether or not the motions of molecules in the spreadsheet which are simultaneously displayed (see discussion under Multiple) are coupled. It is important to couple the motions for visual examination and construction and display of difference plots for molecules which have previously been aligned (see Align Coordinates below). The initial setting is for molecular motions to be coupled. Selection of Decouple Coordinates allows them to be moved (translated and rotated) independently, and changes the menu label to Couple Coordinates. Selection of Couple Coordinates again locks the motions in step and returns the menu entry to Decouple Coordinates. Decouple Coordinates is accessible only if Multiple is "on".

Align Coordinates

Aligns all molecules in the spreadsheet based on highest coincidence of designated centers. The selected molecule acts as the template for alignment. Common substructures are automatically identified wherever possible. Relabeling is possible and will override the automatic assignments. Operation is as follows: First, select a "parent molecule" (the molecule to which other members of the list are to be aligned), then select Align coordinates from the molecule menu in the spreadsheet. Selection of Align results in a message in the menu bar.

Align: Select three or more atoms to align.

Atoms may be selected for alignment by clicking on them in turn, and may be deselected by clicking again. Selected atoms will be visibly marked as such. Note that at least three (non-colinear) atoms need to be selected for alignment to be meaningful, and it is recommended that no more than ten atoms be selected. It is up to the user to ensure that atoms are labelled in a consistent manner for different members in the list. Relabeling, if it is necessary, can be done using Edit Labels under the Model menu (see Section 5.9). Following selection of the appropriate atoms, clicking on Align, aligns the molecules in the list. Clicking instead on Cancel exits without alignment being performed.

Section 11.2.3: Column

Selection results in the following menu:

Add Energy

Add Rel. Energy

Add

Delete

Sort

Copy

Paste

Add Energy

Selection leads to display of a sub-menu:

Energy in hartrees

Energy in kcal/mol

Energy in kJ/mol

Enters the total energy (or heat of formation for a semi-empirical calculation or strain energy for a molecular mechanics calculation) in hartrees, kcal/mol or kJ/mol, depending on the selection, for all members of the list into the next available spreadsheet column. For calculation methods which produce multiple energies, e.g., MP2, only the final energy, e.g., the MP2 energy, is displayed. Other energies, e.g., the Hartree-Fock energy in the case of an MP2 calculation, needs to be entered by way of Post in the Energy dialog (see Sections 9.2.1, 9.2.2 and 9.2.3).

Add Rel. Energy

Selection leads to display of a sub-menu:

Energy in hartrees

Energy in kcal/mol

Energy in kJ/mol

Enters the total energy (or heat of formation for a semi-empirical calculation or strain energy for a molecular mechanics calculation) in hartrees, kcal/mol or kJ/mol, depending on the selection, for all members of the list, relative to the corresponding total energy (heat of formation, strain energy) of the selected molecule, into the next available spreadsheet column.

Add

Selection results in display of a sub-menu:

Dipole

E(HOMO)

E(LOMO)

Molecular Weight

Surface Area

Volume

Dipole

Enters the dipole moment (in debyes) for an ab initio, density functional or semi-empirical calculation, or the dipole moment resulting from calculated atomic charges in a MMFF molecular mechanics calculation for all members of the list, into the next available spreadsheet column.

E(HOMO)

Enters the energy of the highest-occupied molecular orbital (in hartrees for an ab initio or density functional calculation and in eV for a semi-empirical calculation) for all members of the list into the next available spreadsheet column.

E(LUMO)

Enters the energy of the lowest-unoccupied molecular orbital (in hartrees for an ab initio or density functional calculation and in eV for a semi-empirical calculation) for all members of the list into the next available spreadsheet column.

Molecular Weight

Enters the molecular weight (in atomic mass units) for all members of the list into the next available spreadsheet column.

Surface Area

Enters the surface area of a space-filling model (in Å2) for all members of the list into the next available spreadsheet column.

Volume

Enters the volume of a space-filling model (in Å3) for all members of the list into the next available spreadsheet column.

Delete

Deletes all entries from the selected column. No warnings are provided.

Sort

Sorts the entries in the selected column from smallest to largest values.

Copy

Copies all entries in the selected column to the clipboard.

Paste

Pastes the contents of the clipboard into the next available spreadsheet column. If there are fewer entries than rows in the spreadsheet, entries for the extra rows are left blank. If there are more entries than rows in the spreadsheet, extra entries are not pasted.

Section 11.2.4: Plot

Selection results in a sub-menu:

Create

Edit

Delete

Create

Selection leads to an on-screen dialog.

This contains two (identical) lists of the columns in the spreadsheet (labeled "X Axis" and "Y Axis", respectively). The first entry in both lists, "Molecule", is simply the number of the molecule in the list, and the other entries are named according to the column headers. One or more plots are constructed by selecting (clicking on) one entry from X Axis and one or more entries from Y Axis, followed by clicking on Create. In response, the dialog is closed and a plot appears.

The plot behaves as any other graphical object insofar as it can be translated or scaled (but not rotated). It first needs to be selected by clicking on any portion of the plot, i.e., on any of the individual graphs or on an axes. A yellow frame appears, indicating the plot as the selected. A molecule may be selected (and the plot deselected) by clicking on it.

Colors for the plot axes and/or for the individual graphs which make up the overall plot may be adjusted using Colors from the Logo menu (see Section 3.2).

The initial data representation is in terms of points connected by a so-called cubic spline. This is a smooth function which actually passes through all the data points. The data representation may be altered using functions under the Edit menu (described below).

With Multiple "off" (see previous discussion), the selected molecule (designated by a gold sphere on each of the individual graphs in the plot) may be stepped using the and keys or animated using Animate. The molecule changes in response. Step and animate functions are not available with Multiple "on", although in this mode several different molecules may be displayed at once. (Note that the molecules may either be moved together or as individuals; see Decouple Coordinates, below.) Even with Multiple "on", only the molecule actually selected is designated by a gold sphere on the graph (or graphs).

Selection of Expert (in the top left hand corner) extends the Create dialog to include a third list "Filters".

These act in a Boolean manner to "filter" (include or exclude) specific data points (rows) in the plot. A "0" entry in the filter column designates that the data point is to be excluded while a non-zero entry designates that it is to be included.

Formation of Boolean data to use in filtering has already been described under Spreadsheet Operations (Section 11.1).

Edit

This allows either individual graphs or the X axis of the plot to be altered from their initial values (following Create). Selection requires that one of the graphs on the overall plot or the plot axes be the "active" graphical object (by clicking on them). Clicking on one of the graphs, followed by selection of Edit leads to an on-screen dialog.

The name of the quantity being graphed (the Y axis) is provided at the top of the dialog. Controls are available for setting up various aspects of the overall display.

Fit
Lists the available display/fit options:

cubic spline
skyline
point-to-point
linear least squares
quadratic least squares
cubic least squares
Fourier least squares

The first three do not involve data fitting but simply representation of the data. "Cubic spline" (previously described) provides a smooth line which passes through all the points, "skyline" presents the data in terms of histogram, and "point-to-point" connects the individual data points by straight lines. The remaining selections involve data fitting in a least-squares sense, and the resulting fits are provided on the plot. "Linear", "quadratic" and "cubic least squares" fit the data to simple linear (two-parameter), quadratic (three-parameter) and cubic (four-parameter) forms, respectively.
```
                      Y = a + bX   linear
                Y = a + bX + cX2   quadratic
           Y = a + bX +cX2 + dX3   cubic
```
"Fourier least squares" fits to a truncated Fourier series involving seven independent parameters.
```
 	    Y = a(1-cosX) + b(1-cos2X) + c(1-cos3X) + 
                d sinX + e sin2X + f sin3X + g
```
Range
Sets the lower (From) and upper (To) values for the Y axis. The default settings (the minimum and maximum values of the data, respectively) may be changed, and can be restored by clicking on Reset.
Axis Labels
Turns labels for the Y axis "on" and "off".
Markers
Turns data point markers "on" and "off", Type of marker is selected from Marker Type below.
Marker Labels
Turns labels (molecule names) associated with the markers on the data points "on" and "off".
Sphere
Turns the gold sphere demarking of the selected molecule "on" and "off".
Line Width
Selections in the menu control the width of the graph lines. 1 is thinnest, 5 is thickest.
Marker Type
Selections in the menu designates the type of marker: square, circle, diamond, triangle and cross.

Clicking on Done exits the dialog with all edits; clicking on Cancel exits the dialog but any edits are lost.

Clicking on the plot axes (instead of on an individual graph) prior to selecting Edit leads to a different dialog.

This allows setting the lower (From) and upper (To) limits of the X axis. The default settings (the minimum and maximum values of the data, respectively) may be changed, and can be restored by clicking on Reset.

Clicking on Done exits the dialog with all edits; clicking on Cancel exits the dialog but edits are lost.

Selection of Expert (in the top left hand corner) extends the Edit dialog to address error handling.

Selection of an entry under Error uses the data in the associated column to assign error bars to the plot.

Error data is also used to construct least squares fits (linear, quadratic, cubic and Fourier).

Conventional least squares in one dimension involves minimizing the square of the difference between actual data values, y(xi), and those approximated by some fitting function, f(xi, cj), where the cj are adjustable parameters.

This assumes equal weighting of the individual data points, that is to say, that the uncertainties (errors) in the data are uniform. If, on the other hand, the errors are not uniform, a different expression to that given above may be preferrable.