Supporting Information
ExcelAutomat – A Tool for Systematic Processing of Files as Applied to Quantum Chemical Calculations
Jalal Z. A. Laloo1, Nassirah Laloo2, Lydia Rhyman1,3,4 and Ponnadurai Ramasami*1,3
1Computational Chemistry Group, Department of Chemistry, Faculty of Science, University of Mauritius, Réduit 80837, Mauritius
2Department of Industrial Systems Engineering, School of Innovative Technologies & Engineering, University of Technology, Mauritius, La Tour Koenig, Pointe-aux-Sables 11129, Mauritius
3Department of Applied Chemistry, University of Johannesburg, Doornfontein Campus, Johannesburg 2028, South Africa
4Department of Chemistry, University of Johannesburg, PO Box 524, Auckland Park, Johannesburg, 2006, South Africa
*E-mail:
2
Contents
Figure S1. Task processing in ExcelAutomat. 4
Worksheets 4
Figure S2. “ParseFile” worksheet. 4
Figure S3. “GetTemplate” worksheet. 5
Figure S4. “Routes” worksheet. 5
Figure S5. “FileID” worksheet. 6
Figure S7. Workflow to handle output files. 8
Figure S8. Worksheet “ProcessFiles” showing optimised and error lists. 9
Figure S9. “WinSCP” worksheet. 9
Examples 10
Join files directly 10
Join files and add filenames to title section 10
Join files for batch processing 10
Split files as per last word 10
Split files with numbered jobname 10
Split files as per title section 11
Generate file for intrinsic reaction coordinates computation from a transition state file using the worksheet “GetTemplate” 11
Parse files 11
Generate FileID 11
Process files with workflow 11
Table S1. List of selected extracted parameters with their descriptions, data type and routines. The units are according to the output files of the software package. 12
Figure S10. Sample VBA script to extract and display electronic energy. 13
Figure S11. Result of running sample VBA code. 14
Figure S12. ExcelAutomat add-in (a) in MS Excel tab and (b) with numbered parts in “(a)” magnified. 14
Complete reference to Gaussian software package [reference (22)] 15
Complete reference to Gaussian software package [reference (56)] 16
Complete reference to Gaussian software package [reference (57)] 17
Complete reference to Amsterdam density functional program package [reference (23)] 18
Figure S1. Task processing in ExcelAutomat.
Worksheets
Figure S2. “ParseFile” worksheet.
Figure S3. “GetTemplate” worksheet.
Figure S4. “Routes” worksheet.
Figure S5. “FileID” worksheet.
Newfilecode = 0Get initials from “FileID” worksheet
For each folder
For each file with the initial and “.out” as
Extension. ‘Loop through the output files
Get filename
Get filecode from the filename
If filecode > newfilecode Then
filecode = newfilecode
End If
End For
End For
newfilecode = newfilecode + 1
Add initials to filecode for new fileID
Close file and display new fileID
Figure S6. Pseudocode for generating the fileID
Figure S7. Workflow to handle output files.
Figure S8. Worksheet “ProcessFiles” showing optimised and error lists.
Figure S9. “WinSCP” worksheet.
Examples
Samples files are provided in the download folder of ExcelAutomat.
Join files directly
1. Select worksheet “JoinSplit”.
2. Click on button “Direct”.
3. Enter “mergedfile” as filename in input box and click the button “Okay”.
4. Select all files in the folder “Join input” and click the button “Okay”..
The merged file is generated in the folder “Join input”.
Join files and add filenames to title section
1. Select worksheet “JoinSplit”.
2. Click on button “Add filename to title section”.
3. Enter “mergedfile2” as filename in input box and click the button “Okay”.
4. Select all files in the folder “Join input” and click the button “Okay”..
The merged file is generated in the folder “Join input” with the filename appended to the title section.
Join files for batch processing
1. Select worksheet “JoinSplit”.
2. Click on button “Batch”.
3. Enter “mergedfilebatch” as filename in input box and click the button “Okay”.
4. Select all files in the folder “Join input” and click the button “Okay”..
The batch file is generated in the folder “Join input”.
Split files as per last word
1. Select worksheet “JoinSplit”.
2. Click on button “As per last word in title section”.
3. Select files in folder “Split” and click the button “Okay”..
The split files are generated in the folder “Split”.
Split files with numbered jobname
1. Select worksheet “JoinSplit”.
2. Click on button “With numbered jobname”.
3. Select files in folder “Split” and click the button “Okay”..
The split files are generated with a sequential jobname in the folder “Split”.
Split files as per title section
1. Select worksheet “JoinSplit”.
2. Click on button “As per title section”.
3. Select files in folder “Split” and click the button “Okay”..
The split files are generated as per the title section in the folder “Split”.
Generate file for intrinsic reaction coordinates computation from a transition state file using the worksheet “GetTemplate”
1. Select worksheet “GetTemplate”
2. Click on button “Get new template from output file”.
3. Select file “TS” in folder “Gaussian|” of folder “Get template” and click okay.
4. Select a checkbox for route containing the keyword “IRC” in worksheet “Routes”.
5. In worksheet “GetTemplate”, click on button “Cycle through checked routes” until the route containing the keyword “IRC” is reached.
6. Amend the third line by writing “IRC” in the cell.
7. Click on button “Print to Custom Folder”.
8. Enter “IRC” as filename as click okay.
9. Select a folder and click the button “Okay”..
Parse files
1. Select the worksheet “ParseFile”.
2. Clear all checkboxes.
3. Select the checkboxes for the parameters “Routes”, “Title” and “Atom coordinates”.
4. Click on “Extract selected parameters” button.
5. Select files in folder “ParseFile” and click the button “Okay”..
The selected parameters are displayed.
Generate FileID
1. Select the worksheet “FileID”
2. Update the folder location with path to real folders.
3. Click on “Get new fileID”.
Process files with workflow
1. Select the worksheet “ProcessFiles”
2. Click on process files.
3. Select all files in folder “ProcessFiles” and click the button “Okay”..
The files are processed and listed.
Table S1. List of selected extracted parameters with their descriptions, data type and routines. The units are according to the output files of the software package.Parameter name / Description / Data type / Routine / Default parser
Atomcoordinate / Atomic numbers and coordinates / 1D Array / Getatomlistandcoordinates / Gaussian, Gamess
Atomlist / List of atoms / 1D Array / Getatomlistandcoordinates / Gaussian, Gamess
Charge / Charge of structure / Integer / Getchargeandmultiplicity / Gaussian
Errorcode / Error description upon error termination / String / Getjobterminationstatus / Gaussian
Fouryestatus / Check for 4 consecutive yes (value=1 if true) / Integer / Get4yestatus / Gaussian
Freqnum / Vibrational frequencies / 1D Array / GetnimagfreqIR / Gaussian, Gamess
Ircpointotal / Total number of points in IRC computation / Integer / GetIRCpointotal / Gaussian,
Irnum / Infrared intensities / ID Array / GetnimagfreqIR / Gaussian, Gamess
Jobtype / Type of job. Value = “Opt+freq” when route section includes optimisation and a frequency computation, “Optimisation” when no frequency computation included, “Freq” when no optimisation included, “NBO” or “IRC” / String / Getjobtype / Gaussian
Modetotal / Total number of modes of frequencies or IR intensities / Integer / GetnimagfreqIR / Gaussian
Multiplicity / Multiplicity of structure / Integer / Getchargeandmultiplicity / Gaussian
Natom / Number of atoms / Integer / Getnumberatoms / Gaussian, Gamess
Nimag / Number of imaginary frequencies / Integer / GetnimagfreqIR / Gaussian
Njob / Number of jobs in file / Integer / jobtermination
Pointgroup / Point group of structure / String / Getpointgroup / Gaussian, Gamess
Revision / Revision of Gaussian / String / Getrevision / Gaussian, Gamess
Route / Route section / String / Getroute / Gaussian, Gamess
Tenergy / Sum of electronic and zero-point energies / String / Gethermodata / Gaussian
Tenthalpy / Sum of electronic and thermal enthalpies / String / Gethermodata / Gaussian
Tentropy / Entropy / String / Gethermodata / Gaussian
Terminationstatus / Check for normal and error termination. Value = 1 for normal termination, 2 for error termination and 3 if job not completed / Integer / Getjobterminationstatus / Gaussian
Tgibbs / Sum of electronic and thermal free energies / String / Gethermodata / Gaussian
Title / Title section / String / Getitle / Gaussian, Gamess
Scfenergy / Last SCF energy / String / Getscfenergy / Gaussian, Gamess
1: / Option vbasupport 1 'Do not include this line in MS Excel
2: / Sub customscript()
3: / 'The variables are declared.
4: / Dim mainsheet,filepath,filepathlist,extractionfile As Variant
5: / 'The user is prompted to select the output file
6: / filepathlist = selectfilesfd
7: / For Each filepath in filepathlist
8: / If filepath > "" 'check if no files are selected
9:
10:
11:
12:
13:
14:
15:
16:
17:
18: / 'The file is opened in MS Excel
Call openwrkbk(filepath)
extractionfile = ActiveWorkbook.Name
'The thermodata is extracted
Call gethermodata
'The file is closed
Workbooks(extractionfile).Close Savechanges:=False
'The values are displayed in excel worksheet
Cells(1, 1).Value = "Sum of electronic and zero-point energies"
Cells(1, 2).Value = Tenergy
19: / End if
20: / Next
21: / End Sub
Figure S10. Sample VBA script to extract and display electronic energy.
Figure S11. Result of running sample VBA code.
(a) /(b)
Figure S12. ExcelAutomat add-in (a) in MS Excel tab and (b) with numbered parts in “(a)” magnified.
Complete reference to Gaussian software package [reference (22)]
Frisch, M. J.; Trucks, G. W.; Schlegel, H. B.; Scuseria, G. E.; Robb, M. A.; Cheeseman, J. R.; Scalmani, G.; Barone, V.; Mennucci, B.; Petersson, G. A.; Nakatsuji, H.; Caricato, M.; Li, X.; Hratchian, H. P.; Izmaylov, A. F.; Bloino, J.; Zheng, G.; Sonnenberg, D. J.; Hada, M.; Ehara, M.; Toyota, K.; Fukuda, R.; Hasegawa, J.; Ishida, M.; Nakajima, T.; Honda, Y.; Kitao, O.; Nakai, H.; Vreven, T.; Montgomery, J. A., Jr.; Peralta, E.; Ogliaro, F.; Bearpark, M.; Heyd, J.; Brothers, E.; Kudin, N.; Staroverov, N.; Keith, T.; Kobayashi, R.; Normand, J.; Raghavachari, K.; Rendell, A.; Burant, C.; Iyengar, S.; Tomasi, J.; Cossi, M.; Rega, N.; Millam, M.; Klene, M.; Knox, E.; Cross, B.; Bakken, V.; Adamo, C.; Jaramillo, J.; Gomperts, R.; Stratmann, E.; Yazyev, O.; Austin, J.; Cammi, R.; Pomelli, C.; Ochterski, W.; Martin, L.; Morokuma, K.; Zakrzewski, G.; Voth, A.; Salvador, P.; Dannenberg, J.; Dapprich, S.; Daniels, D.; Farkas, Ö.; Foresman, B.; Ortiz, V.; Cioslowski, J.; Fox, J. D. Gaussian 09, Revision D.01, Gaussian, Inc., Wallingford CT, 2009.
Complete reference to Gaussian software package [reference (56)]
Frisch, M. J.; Trucks, G. W.; Schlegel, H. B.; Scuseria, G. E.; Robb, M. A.; Cheeseman, J. R.; Scalmani, G.; Barone, V.; Mennucci, B.; Petersson, G. A.; Nakatsuji, H.; Caricato, M.; Li, X.; Hratchian, H. P.; Izmaylov, A. F.; Bloino, J.; Zheng, G.; Sonnenberg, D. J.; Hada, M.; Ehara, M.; Toyota, K.; Fukuda, R.; Hasegawa, J.; Ishida, M.; Nakajima, T.; Honda, Y.; Kitao, O.; Nakai, H.; Vreven, T.; Montgomery, J. A., Jr.; Peralta, E.; Ogliaro, F.; Bearpark, M.; Heyd, J.; Brothers, E.; Kudin, N.; Staroverov, N.; Keith, T.; Kobayashi, R.; Normand, J.; Raghavachari, K.; Rendell, A.; Burant, C.; Iyengar, S.; Tomasi, J.; Cossi, M.; Rega, N.; Millam, M.; Klene, M.; Knox, E.; Cross, B.; Bakken, V.; Adamo, C.; Jaramillo, J.; Gomperts, R.; Stratmann, E.; Yazyev, O.; Austin, J.; Cammi, R.; Pomelli, C.; Ochterski, W.; Martin, L.; Morokuma, K.; Zakrzewski, G.; Voth, A.; Salvador, P.; Dannenberg, J.; Dapprich, S.; Daniels, D.; Farkas, Ö.; Foresman, B.; Ortiz, V.; Cioslowski, J.; Fox, J. D. Gaussian 09, Revision C.01, Gaussian, Inc., Wallingford CT, 2009.
Complete reference to Gaussian software package [reference (57)]
Frisch, M. J.; Trucks, G. W.; Schlegel, H. B.; Scuseria, G. E.; Robb, M. A.; Cheeseman, J. R.; Scalmani, G.; Barone, V.; Petersson, G. A.; Nakatsuji, H.; Li, X.; Caricato, M.; Marenich, A. V.; Bloino, J.; Janesko, B. G.; Gomperts, R.; Mennucci, B.; Hratchian, H. P.; Ortiz, J. V.; Izmaylov, A. F.; Sonnenberg, J. L.; Williams-Young, D.; Ding, F.; Lipparini, F.; Egidi, F.; Goings, J.; Peng, B.; Petrone, A.; Henderson, T.; Ranasinghe, D.; Zakrzewski, V. G.; Gao, J.; Rega, N.; Zheng, G.; Liang, W.; Hada, M.; Ehara, M.; Toyota, K.; Fukuda, R.; Hasegawa, J.; Ishida, M.; Nakajima, T.; Honda, Y.; Kitao, O.; Nakai, H.; Vreven, T.; Throssell, K.; Montgomery, J. A., Jr.; Peralta, J. E.; Ogliaro, F.; Bearpark, M. J.; Heyd, J. J.; Brothers, E. N.; Kudin, K. N.; Staroverov, V. N.; Keith, T. A.; Kobayashi, R.; Normand, J.; Raghavachari, K.; Rendell, A. P.; Burant, J. C.; Iyengar, S. S.; Tomasi, J.; Cossi, M.; Millam, J. M.; Klene, M.; Adamo, C.; Cammi, R.; Ochterski, J. W.; Martin, R. L.; Morokuma, K.; Farkas, O.; Foresman, J. B.; Fox, D. J. Gaussian 16, RevisionA.03, Gaussian, Inc., Wallingford CT, 2016.
Complete reference to Amsterdam density functional program package [reference (23)]
Baerends, E. J.; Ziegler, T.; Autschbach, J.; Bashford, D.; Bérces, A.; Bickelhaupt, F. M.; Bo, C.; Boerrigter, P.M.; Cavallo, L.; Chong, D. P.; Deng, L.; Dickson, R. M.; Ellis, D. E.; van Faassen, M.; Fan, L.; Fischer, T. H.; Fonseca Guerra, C.; Ghysels, A.; Giammona, A.; van Gisbergen, S. J. A.; Götz, A.W.; Groeneveld, J. A.; Gritsenko, O. V.; Grüning, M.; Gusarov, S.; Harris, F. E.; van den Hoek, P.; Jacob, C. R.; Jacobsen, H.; Jensen, L.; Kaminski, J. W.; van Kessel, G.; Kootstra, F.; Kovalenko, A.; Krykunov, M. V.; van Lenthe, E.; McCormack, D. A.; Michalak, A.; Mitoraj, M.; Neugebauer, J.; Nicu, V. P.; Noodleman, L.; Osinga, V. P.; Patchkovskii, S.; Philipsen, P. H. T.; Post, D.; Pye, C. C.; Ravenek, W.; Rodríguez, J. I.; Ros, P.; Schipper, P. R. T.; Schreckenbach, G.; Seldenthuis, J. S.; Seth, M.; Snijders, J. G.; Solà, M.; Swart, M.; Swer- hone, D.; te Velde, G.; Vernooijs, P.; Versluis, L.; Visscher, L.; Visser, O.;Wang, F.;Wesolowski, T. A.; vanWezenbeek, E. M.;Wiesenekker, G.;Wolff, S. K.;Woo, T. K.; Yakovlev, A. L. ADF, Amsterdam density functional program, 2010. Available at: http://www.scm.com.
10