Spectroscopy Add-In Instructions CFN, 10/16/98

Tools for Use in Analyzing High-Resolution IR Spectra
Provided in the Excel Add-In, “Spectroscopy Add-In.xla”

Installing “Spectroscopy Add-In.xla”

To use the Spectroscopy Add-In, the file, “Spectroscopy Add-In.xla”, must first be loaded into Excel as an Add-In. If a Spectra menu is visible in the Excel menu bar, the Add-In is already loaded and ready to use. If the Add-In is not loaded, it can be by selecting the Tools | Add-Ins… menu item. This will bring up a dialog box with a list of Add-Ins. Currently loaded Add-Ins have a check by them. Simply check “Spectroscopy Add-In” and then click OK. If for some reason “Spectroscopy Add-In” does not appear on this list, then click the Browse… button. Point the resulting dialog box to the file, “Spectroscopy AddIn.xla”.

Using the Spectroscopy Add-In

The Spectroscopy Add-In is designed to integrate seamlessly into Excel. Therefore most of its tools are designed to be used by selecting an item from the Spectra menu. Note that the contents of the Spectra menu changes, depending on whether the current view is an Excel worksheet or an Excel chart.

Open Bruker Spectral File…

This command is used to import a Bruker spectral file into the current workbook as a new worksheet. The spectral file is loaded into column pairs. The first column of a column pair is the frequency column, and the second column of a column pair is the transmittance column. Each column pair is limited to 32,000 rows, a limit imposed by Excel’s charting routines. If more than 32,000 points are to be imported, the remaining points will overflow into additional column pairs. In this situation, the last point of a given column pair is repeated as the first point in the following column pair. The repetition prevents small gaps when the spectrum is plotted, and is used by other commands provided in the Spectroscopy Add-In to distinguish between multi-column pair spectra and multiple spectra.

Selecting the Open Bruker Spectral File… menu item opens a dialog box. The name of the spectral file to be opened should be typed in the Spectral Filename text box, or the Browse… button can be used to search for the file. Clicking the OK button opens the file.

The Start At and End At number boxes allow input of starting and ending frequencies. These number boxes can be used to import a small portion of the entire spectrum, preventing the current workbook from becoming unnecessarily large. If the spectral file begins at a higher frequency than the value of the Start At number box, the file will be imported from its beginning. Similarly, if the value of the End At number box is greater than the last frequency of the spectral file, the file will be imported to its end.

Spectral Files can be quite large, especially when displayed in user-friendly form. Essentially, an imported spectrum in Excel will be at least twice as large as its Bruker spectral file source. The size of the imported data can be limited by use of the Start At and End At dialog boxes and/or by selecting some form of Compression. When the spectral file is imported using a compression algorithm, only those points crucial to representing the spectrum are loaded. It is important to note that large spectra not only take up disk space and system resources, but they also take longer to render for display and printing.

The Compression frame currently contains two choices, None and Inflection Points Only. If None is chosen, the entire spectral file between the Start At and End At frequencies will be loaded. If Inflection Points Only is chosen, only points that are local minima or maxima will be imported. Using Inflection Points Only typically reduces the number of points imported by a factor of 4 to 10, thus saving space. If the spectrum is only going to be used at survey scales, this space savings is ideal. However, for detail-level scales, Compression should probably be set to None. However, the difference in spectral presentation by the two methods is often negligible, even at expanded scales.

Open ASYM3000 Data File…

This command is used to import a data file for the ASYM3000 program as a new worksheet in the active workbook. Selection of this menu item brings up a standard Open dialog box that is used to select the desired ASYM3000 data file. Opening an ASYM3000 Data File in Excel is useful, because it allows editing features not available in a text editor, such as sorting and column editing.

Below is a typical ASYM3000 data file shown as ASCII text. The file format consists of three parts, a header containing initial upper and ground state constants, followed by a list of data, in this example, GSCDs, to be fit using ASYM3000, followed by entries that are ignored by ASYM3000. (The last section usually consists of assigned lines that the user does not want to fit, but wishes to retain in the data set for later use.) These three sections are separated by “9”s in the first column.

1.07316 1 1 1 1.07316

0.039094 1 1 1 0.039094

0.037720 1 1 1 0.037720

0.0 1 1 0 0.0

0.0 1 1 0 0.0

0.0 1 1 0 0.0

0.0 1 1 0 0.0

0.0 1 1 0 0.0

0.0 0 2 1 0.0

0.0 1 1 1 0.0

0.0 2 0 1 0.0

0.0 0 3 0 0.0

0.0 2 1 0 0.0

9 0.0 3 0 0 0.0

21.088368 6 6 0 4 4 0

21.242067* 7 6 1 5 4 1

21.698948 10 6 4 8 4 4

22.006181 12 6 6 10 4 6

22.159165 13 6 7 11 4 7

22.311930 14 6 8 12 4 8

22.618495 16 6 10 14 4 10

22.770993 17 6 11 15 4 11

22.924551 18 6 12 16 4 12

23.229931 20 6 14 18 4 14

23.382397* 21 6 15 19 4 15

…etc…

* 92 4 89 90 2 89

* 93 4 90 91 2 90

* 94 4 91 92 2 91

* 95 4 92 93 2 92

* 96 4 93 94 2 93

* 97 4 94 95 2 94

9

108.807284 27 27 0 25 25 0

108.960316 28 27 1 26 25 1

109.113434 29 27 2 27 25 2

109.267054 30 27 3 28 25 3

109.420776 31 27 4 29 25 4

…etc…

The Open ASYM3000 Data File… menu item creates a new worksheet then loads each line of the ASYM3000 Data File into a row of this worksheet. The command recognizes data lines, which are parsed into columns B through I. Non-data lines are placed into column B in their entirety. If a line is parsed, the command places a “#” into column A, and a transition label (Ex:PP8lowKc) into column J. (The transition label doesn’t have any physical meaning for GSCDs). The imported data is then formatted for aesthetics. This formatting does not change the data however. The imported file can be edited, then exported using the Save As ASYM3000 File… command.

Open ASYM3000 Results File…

This command is similar to the Open ASYM3000 Data File… command, but instead loads an ASYM3000 results file into Excel as a new worksheet in the active workbook. Importing an ASYM3000 results file is useful primarily to take advantage of Excel’s enhanced display capabilities to format data into a more readable form.

Below is a typical ASYM3000 results file. An ASYM3000 results file consists of a header containing the results of each iteration of the fit, followed by a data section in which each processed line and its residual is given.

1

ttDFBD GS(RR4-9,RR11-14,RR16-22,RR24-27;corr. PP)

THE INITIAL CONSTANTS ARE

A= 0.1073160000D+01 DELTAK= 0.0000000000D+00

B= 0.3909000000D-01 SH(J)= 0.0000000000D+00

C= 0.3772000000D-01 SH(JK)= 0.0000000000D+00

DELTAJ= 0.0000000000D+00 SH(K)= 0.0000000000D+00

DIAGONAL CONST. (02) = 0.0000000000D+00

DIAGONAL CONST. (11) = 0.0000000000D+00

DIAGONAL CONST. (20) = 0.0000000000D+00

DIAGONAL CONST. (03) = 0.0000000000D+00

DIAGONAL CONST. (21) = 0.0000000000D+00

DIAGONAL CONST. (30) = 0.0000000000D+00

KAPPA= -0.997353782D+00

***THIS CALCULATION WAS DONE IN THE 1R REPRESENTATION ***

****** THIS IS AN 'A'-REDUCTION CALCULATION ******

AT THE END OF ITERATION NO. 1

THE CONSTANTS BEING FIT ARE

A= 0.1050750309D+01

+ +/-0.10875E-05

B= 0.3897247957D-01

+ +/-0.12762E-05

C= 0.3757947603D-01

+ +/-0.98293E-06

DELTAJ= 0.0000000000D+00

DELTAK= 0.0000000000D+00

SH(J)= 0.0000000000D+00

SH(JK)= 0.0000000000D+00

SH(K)= 0.0000000000D+00

DIAGONAL CONST. (02) = 0.3920824838D-05

+ +/-0.11687E-08

DIAGONAL CONST. (11) = -.3601160969D-07

+ +/-0.34669E-09

DIAGONAL CONST. (20) = 0.1213616652D-08

+ +/-0.45370E-10

DIAGONAL CONST. (03) = 0.0000000000D+00

DIAGONAL CONST. (21) = 0.0000000000D+00

DIAGONAL CONST. (30) = 0.0000000000D+00

KAPPA= -0.997250210D+00

STD. DEV.= 0.000494

AT THE END OF ITERATION NO. 2

…etc…

STD. DEV.= 0.000494

KMAX= 50

THE CONVERGENCE CRITERION HAS BEEN SATISFIED -- NO MORE ITERATIONS ARE NEEDED.

THE CORRELATION COEFFICIENTS ARE:

1 2 3 2 11 20

1 1.0000

2-0.5022 1.0000

3-0.0434-0.7043 1.0000

2-0.7602 0.6514-0.3442 1.0000

11 0.0758-0.6174 0.7204-0.6362 1.0000

20 0.1940 0.3125-0.7672 0.3374-0.8363 1.0000

THE NUMBER OF NON-ZERO DATA POINTS IS: 911

1

ttDFBD GS(RR4-9,RR11-14,RR16-22,RR24-27;corr. PP)

J' KA' KC' J'' KA'' KC'' OBSERVED DEV. WT.

6 6 0 - 4 4 0 21.088368 0.000846 0.100E+01

7 6 1 - 5 4 1 21.242067 0.001429 0.000E+00

10 6 4 - 8 4 4 21.698948 -0.001042 0.100E+01

12 6 6 - 10 4 6 22.006181 -0.000042 0.100E+01

13 6 7 - 11 4 7 22.159165 -0.000173 0.100E+01

14 6 8 - 12 4 8 22.311930 -0.000521 0.100E+01

16 6 10 - 14 4 10 22.618495 -0.000174 0.100E+01

17 6 11 - 15 4 11 22.770993 -0.000780 0.100E+01

18 6 12 - 16 4 12 22.924551 -0.000321 0.100E+01

20 6 14 - 18 4 14 23.229931 -0.001120 0.100E+01

…etc…

The Open ASYM3000 Results File… menu item creates a new worksheet and then loads each line of the ASYM3000 results into a row of this worksheet. The command recognizes data lines, which are parsed into columns B through J. Non-data lines are placed into column B in their entirety. If a line is parsed, the command places a “#” into column A, and a transition label (Ex:PP8lowKc) into column K. (The transition label doesn’t have any physical meaning for GSCDs). The imported data is then formatted for aesthetics.

Open ASYMBD7Y Predictions File…

This command is similar to the Open ASYM3000 Data File… command, but instead loads an ASYMBD7Y results file into Excel as a new worksheet in the active workbook. Importing a predictions file into Excel is very useful not only because the data can be sorted and displayed in a more user friendly data, but the data can be converted into a plotable spectrum using the Convert Peaklist to Transmittance Spectrum… or Convert Peaklist to Absorbance Spectrum… commands.

Below is a typical ASYMBD7Y predictions file. The file consists of a header containing the constants used for the predictions, followed by a data section containing predicted lines.

1

ttDFBD Predictions

UPPER STATE CONSTANTS LOWER STATE CONSTANTS

A= 0.104657035D+01 A= 0.105075031D+01

B= 0.389544450D-01 B= 0.389719567D-01

C= 0.375906062D-01 C= 0.375800046D-01

DELTAJ= -0.718175516D-10 DELTAJ= 0.000000000D+00

DELTAK= 0.000000000D+00 DELTAK= 0.000000000D+00

SH(J)= 0.000000000D+00 SH(J)= 0.000000000D+00

SH(JK)= 0.000000000D+00 SH(JK)= 0.000000000D+00

SH(K)= 0.000000000D+00 SH(K)= 0.000000000D+00

DIAGONAL CONST.(02)= 0.306794347D-04 DIAGONAL CONST.(02)= 0.392081998D-05

DIAGONAL CONST.(11)=-0.128344758D-06 DIAGONAL CONST.(11)=-0.360199671D-07

DIAGONAL CONST.(20)= 0.121298876D-08 DIAGONAL CONST.(20)= 0.121441814D-08

DIAGONAL CONST.(00)= 0.934138919D+03 DIAGONAL CONST.(00)= 0.000000000D+00

KAPPA = -0.99725

***THIS CALCULATION WAS DONE IN THE 1R REPRESENTATION ***

****** THIS IS AN 'A'-REDUCTION CALCULATION ******

JMIN= 0 JMAX= 300 KMAX= 50

HIGHEST WAVENUMBER = 970.0000 LOWEST WAVENUMBER = 900.0000

THE A-DIPOLE COMPONENT IS.00000E+00 -- THE B-DIPOLE COMPONENT IS.00000E+00 -- THE C-DIPOLE COMPONENT IS.10000E+01

S(T) WAS CALCULATED FOR A TEMPERATURE OF 298.00 K.

THE SMALLEST VALUE FOR S(T) WAS SET AT 0.10000E-01

33760 LINES HAVE BEEN CALCULATED WITH SUFFICIENT INTENSITY TO BE OUTPUT

THE SUM OVER ALL THE CALCULATED LINE INTENSITIES IS

0.86425E+04 CM(-2)*ATM(-1)

1

ttDFBD Predictions

J' KA' KC' -J'' KA''KC'' FREQ. S S(T) LOWER E

1 1 0 0 0 0 935.224426 0.10000E+01 0.70405E-01 0.000000

1 1 1 1 0 1 935.146484 0.15000E+01 0.10556E+00 0.076552

2 1 1 1 0 1 935.302307 0.15000E+01 0.10558E+00 0.076552

0 0 0 1 1 0 933.049194 0.10000E+01 0.69865E-01 1.089718

2 0 2 1 1 0 933.278809 0.49899E+00 0.34870E-01 1.089718

2 2 0 1 1 0 937.311523 0.15010E+01 0.10537E+00 1.089718

1 0 1 1 1 1 933.127136 0.15000E+01 0.10481E+00 1.088326

2 2 1 1 1 1 937.312927 0.15000E+01 0.10530E+00 1.088326

…etc…

The Open ASYMBD7Y Predictions File… menu item creates a new worksheet then loads each line of the ASYMBD7Y predictions file into a row of this worksheet. The command recognizes data lines, which are parsed into columns B through K. Non-data lines are placed into column B in their entirety. If a line is parsed, the command places a “#” into column A, and a transition label (Ex:PP8lowKc) into column L. The imported data is then formatted for aesthetics.

It is important to note that this command switches the S and S(T) columns of the ASYMBD7Y predictions file. This is done because S(T) (signal strength with temperature dependence) is the more useful number and it is better to have this number beside the frequency column for use with the Convert Peaklist to Transmittance Spectrum… or Convert Peaklist to Absorbance Spectrum… commands.

This command does not work with prediction files from the normal ASYMBD7 program, but only the Y variant.

Save As ASYM3000 Data File…

This command is the inverse of the Open ASYM3000 Data File… command. Selecting this menu item opens a standard Save As dialog box, in which the user should give a name for the new file. The command the saves the active worksheet as a text file in the format of an AYM3000 data file. If the active worksheet is not in the format used by the Open ASYM3000 File… command, the resulting file will be meaningless. Thus, while it is, in theory, possible to create an ASYM3000 data file from scratch in Excel and then use this command, it is generally only useful to use this command after editing an existing ASYM3000 data file.

Convert Peaklist to Transmittance Spectrum…

This command will create a new worksheet in the active workbook containing a spectrum calculated from a peaklist. The new worksheet will be formatted in the same manner as the results of the Open Bruker Spectral File… command.