United States	Office of Air Quality Planning and Standards
Environmental	Outreach and Information Division
Protection	National Air Data Group
Agency	Research Triangle Park, NC 27711
AQSP&A Transaction Generator
Users' Guide
Version 8.4

Air Quality System
September 2007

-------
AOSP&A Transaction Generator	Users' Guide
2
June 2007

-------
AOSP&A Transaction Generator
Users' Guide
Table of Contents
1.	About AQSP&A
2.	Setting up the application
3.	Entering data
4.	Using lookups
5.	Adding a new monitor or method to reference worksheets
6.	Generating a transaction file
7.	Creating a report
8.	Tips for using Excel
9.	Help
3
June 2007

-------
AOSP&A Transaction Generator
Users' Guide
1. About AOSP&A
AQSP&A is a stand-alone PC tool to build precision and accuracy transactions for
uploading P&A data into the Air Quality System (AQS). The intent of this tool is to
provide you with a simple way to create the 2 AQS data input transactions used for
uploading accuracy data (transaction type RA) and precision data (transaction type
RP). The transactions are created in the "new" AQS format with pipe delimiters
(vertical bars), or in XML format to comply with the AQS XML Schema version 2.
The scope of this tool is identical to the Precision and Accuracy Reporting System
(PARS) tools that have been provided in the past. This additional tool is now
available because the older PARS software does not execute on some of the newer PC
operating systems (e.g. Windows 2000).
AQSP&A is an Excel workbook. You must have Excel software to use AQSP&A.
The minimum version of Excel required is Excel 97.
Note: An Excel file is also called a Workbook. A workbook can include many worksheets.
Each worksheet has a tab name. In the AQSP&A workbook, there are 7 worksheets, named
Help, TransactionRA, TransactionRP, Valid Methods, Code Values, Audit Protocols, and
Coding Manual.
We have created 10 workbooks, one for each USEPA region. Each workbook
contains reference tables for active criteria pollutant monitors for every reporting
organization in the USEPA region. You need only one of the workbooks; the one
appropriate to your USEPA region. More information about this can be found in
Chapter 2 of this guide.
To fulfill the intent of providing you with a simple way to create precision and
accuracy transactions, we have included the following capabilities:
•	Lists of values for data entry are provided whenever practical. The lists of
values provided for data entry are taken from the reference worksheets, which
have been loaded with AQS data. The lists of values are presented in drop
down menus, and are available for most fields, including Method, Units, and
Sampling Duration.
•	Descriptions of codes and monitors are provided by "right-clicking" the field
in question.
•	The P&A data you enter in the worksheet are validated prior to creation of
transaction. The use of valid monitors, methods, durations, units, various
code values, and acceptable observation values are checked. Feedback is
provided to you, enabling error corrections. Note: The existence of
corresponding raw data values is not checked, however.
•	Help is provided for each field on each transaction by including the
appropriate information from the AQS Data Coding Manual.
4
June 2007

-------
AOSP&A Transaction Generator
Users' Guide
•	A report can be generated for each transaction (as a Microsoft Word
document). The generated report includes the % difference calculated value
for each transaction, as well as a subset of the transaction fields.
•	The ability to "toggle" between Tribal and non-tribal modes is included. In
Tribal mode, monitors are identified using the Tribal code in place of the State
+ County code.
•	The use of Excel is considered to provide a user-friendly aspect to this
application. Because this application is an Excel file, you can create multiple
copies of the file, move it to different directories, and also use the many built-
in capabilities provided by the Excel program.
5
June 2007

-------
AOSP&A Transaction Generator
Users' Guide
2. Set up
Setting up this application consists of copying the appropriate file to your hard drive.
Following this, you may also choose to run the Set Up Screening Group utility from the
Help worksheet to include only the monitors in one screening group.
Copy the file
Log onto USEPA's Technology Transfer Network (TTN), go to the AQS page, and then
go to the "Precision and Accuracy Data" page. The URL is:
http://www.epa.gov/ttn/airs/airsaqs/padata/padata.htm
Ten similar files have been "posted" on the TTN... one for each USEPA Region. Choose
the file for the Region in which your agency resides. For example, if you are an agency
in Region 1, then you would download the file named "AQSP&A R01 v8.2.zip".
You must decide on a target drive and folder (directory) to download the file into. As a
default suggestion, you could create a folder named "AQSP&A" on your hard drive.
Then download the file from the TTN into this directory. You must then "unzip" the file.
Be sure to unzip it, and then open the "unzipped" file. If you run the file from within
WinZip or some other decompression utility program, the spreadsheet macros may not
run properly.
Enable Macros
After unzipping the file, double click it to open it with Excel, or start Excel and then open
the file from Excel. Because the file contains "macros', you must be sure that the
security setting in Excel is set to let you run macros. If you do not get a prompt window
to "Enable Macros" when the file is opening, then change the security setting in Excel.
To change the security setting in Excel, first close the AQSP&A file if open. Start Excel,
and then from the Excel menu, choose Tools / Macro / Security. There are 3 settings (4
in Excel 2003), Low, Medium, and High. If security is set to High, you will not be able
to run macros. In this case, change the level to "Medium". With this setting, Excel will
prompt you every time you open a file; asking whether you want to enable macros, or
not. You can still choose to disable macros when opening other Excel files.
Once you have opened the file from Excel and "Enabled Macros", you will see the Help
worksheet.
Set Up Screening Group
• The Help worksheet contains a green button labeled "Set Up Screening Group".
This button runs a procedure that prompts you for your screening group, and then
deletes all of the extraneous data for other screening groups from the look up
6
June 2007

-------
AOSP&A Transaction Generator
Users' Guide
worksheets (Valid Methods and Code Values) in the workbook. This helps
ensure that you build transactions only for the monitors belonging to your
screening group. You only run this utility one time.
•	The "Set Up Screening Group" button does not have to be used. If you want all
of the lookup data available for all agencies in the EPA Region, then ignore the
"Set Up Screening Group" button.
Note about multiple screening groups
If you submit precision and accuracy data to AQS for more than one screening group, then you can
create copies of the AQSP&A file with a different name for each screening group that you use.
The AQSP&A files must have different names, of course, or else reside in different directories.
Then run the Set Up Screening Group utility for each copy, and choose a different screening group
for each copy.
Different versions of Excel
With AQSP&A versions 4.3 and later, version incompatibilities of Excel no longer create
compile errors when first running the spreadsheet.
•	However, if you run into problems, please give NADG a call so that we can
address the problem in future releases. Our contact numbers are included in the
back of this Users' Guide.
7
June 2007

-------
AOSP&A Transaction Generator
Users' Guide
3. Entering Data
Creating input data transactions consists of entering values into the columns of two
worksheets; TrnasactionRA or TransactionRP. For accuracy transactions (type RA), use
the TransactionRA worksheet. For precision transactions, (type RP), use the
TransactionRP worksheet.
Each transaction worksheet has a column header area that contains the names of the fields
in the order in which they appear in the transaction format for that transaction type. To
create a transaction, populate a row with appropriate values in each column. If you want
to create multiple transactions, populate multiple rows with the required data. Mandatory
fields have black column headings; optional fields have blue headings. Right clicking the
heading will jump you to the field definition in the coding manual.
Drop down lists are available for most fields. For fields that have a set of allowable
codes or values, drop down lists will appear in each cell containing the allowable choices.
The left mouse button is used to select the appropriate value from the list. In all cases,
warnings are given if values not on the list are typed in; however you can proceed with
those values if so desired. But if you generate a transaction with non-valid values, you
will receive an error when attempting to load the transaction into AQS.
In cases where values are dependent upon other values, such as duration, units, and
method, the drop down list of values will contain only the appropriate choices for the
specific monitor. The most recently used method code will appear as the first value (at
the top) of the method code drop down list.
To populate the monitor id fields, you can type the appropriate values into the monitor id
columns (State - County - Site ID - Parameter - POC;
or Tribal Code-Site ID-Parameter-POC when you are in Tribal mode)
Another way to populate the monitor id fields is to use the "Look Up Monitor" button on
the Excel menu. To add a monitor in this way, first select (left click) a cell in a blank row
under one of the monitor id columns. Then click the "Look Up Monitor" button on the
Excel menu. A procedure will run that asks you to first select a county code, or Tribal
code in Tribal mode.. When you select the code, a list of monitors is displayed in the
dialogue window. You can then click on a monitor, and the monitor id will be filled in
on the worksheet.
8
June 2007

-------
AOSP&A Transaction Generator
Users' Guide
4. Using Lookups
In addition to the drop down list of values for selection, look ups are available for
validating monitors and for displaying code descriptions. Look ups are performed by
using the right mouse button.
After entering data, you can right-click the values for duration, units, or method to see a
description of the code value. For example, right clicking on a unit code of "001" will
display a message box with "UG/CU METER (25)".
If you type values for the monitor id fields directly into the spreadsheet, you can check
the validity of the monitor id by right clicking any one of the monitor id fields you just
populated. A message window will be displayed informing you whether or not the
monitor exists on the reference worksheet (Valid Methods). The validation routines that
run during transaction generation will also check for valid monitors as well.
Note about using the mouse
Because the right mouse button is used for look ups on the two transaction worksheets, the
default right-click function menu will not appear. To perform those functions (e.g.
cut/copy/paste), you will have to use the menu at the top of the worksheet.
Important Note: If you use cut/copy/paste, always use paste special and paste only the
values to prevent overwriting the specific cell validation formulas.
For the three date fields, a right click will open a pop-up calendar from which you can
graphically choose the date.
For more information about validation, see the Help Worksheet in AQSP&A.
9
June 2007

-------
AOSP&A Transaction Generator
Users' Guide
5.	Adding a new monitor or method to lookup worksheets
You may need to build a transaction for a monitor, or use a method, that is not included
in the lookup worksheets. To provide for this possibility, a menu button labeled "Add
New Monitor / Method" is provided. This procedure will present a form requiring you to
enter the monitor, method, duration, and units for a new entry. After filling in the form,
the appropriate lookup worksheets are updated with the new monitor or method. From
then on the new values will be available in the drop down lists of values and the look ups
in the transaction worksheets.
You may choose to use this button rather than downloading a newer version of AQSP&A
if the lookup worksheets start to get "out of sync" with AQS.
Check our website for a newer version of AQSP&A, which will always include refreshed
lookup tables. If a newer version is not yet available, then you may contact NADG, and
we will send you a refreshed file. Additional pollutants can also be included.
6.	Assigning and Revising Tribal Codes
Tribal codes are now included as part of the monitor id when you are in Tribal mode
(similar to AQS). These codes are included in the look up tables for monitors that have
had raw, criteria data reported.
Tribal codes can be added or deleted using the Look Up Monitor menu item. When in
Tribal mode, a couple of new buttons appear on the Look Up Monitor form that allow
you to add or delete a Tribal code for a specific monitor.
10
June 2007

-------
AOSP&A Transaction Generator
Users' Guide
7. Generating a transaction file
The Generate Text File of Transactions button is used to create transactions for uploading
via CDX to AQS for loading into the database. The button has 2 submenus... one choice
creates a text file of transactions in the new vertical bar delimited format, and the other
choice will generate an XML file of input transactions formed to comply with the AQS
schema document version 2.
Select one or more rows on the transaction worksheet for generating transactions and
then choose the transaction type to be generated by pressing the button "Generate
Transactions", and choosing either AQS format or XML format from the submenu.
When selecting rows to create input transactions, you do not have to be precise
regarding which columns you have selected. All of the appropriate columns will be used
by the transaction generation procedure. The procedure will reselect all columns in rows
with highlighted cells.
You will then be prompted for the path and file name for saving the text file, or xml file.
To enable your PC to open the file in the application you normally use for text editing,
you may want to add the appropriate file extension to the name (e.g. ".txt" or ".xml").
Edit checks are also performed for mandatory fields, values within ranges, max values,
and percent differences. For certain fields, the errors are highlighted with red text.
Message boxes are used to inform you of the condition generating the error. When an
error is encountered, you can choose to continue file generation, or to stop file generation
and return to the field in error to fix the value. If you choose to generate the file with
errors, you can also choose to receive an error report. After file generation, the rows for
which you generated the text file are set to a gray background as a reminder to you. The
error report will refer to transaction lines produced in the generated AQS format
transaction text file - not the xml format file. You can generate both transaction formats
if you want to use the error report when submitting xml format.
Tribal codes will be used when in Tribal mode, or State-County codes if not in Tribal
mode.
Separate files must be generated for each transaction type. You can append them
together later after they have been created, but this application will not generate both
transaction types in one file.
You may want to develop a file naming convention, and use certain directories, to stay
organized and provide some audit trail capabilities.
11
June 2007

-------
AOSP&A Transaction Generator
Users' Guide
8. Creating a Report
Some limited reporting capability has been added to the workbook. A "Create Report"
menu choice allows you to create a report as a Microsoft Word document for the rows
selected on either of the 2 transaction worksheets. The main benefit of running the
reports is that you will be able to see the calculated percent differences for each
transaction. Errors will be flagged with red text. So although the complete statistical
summarization is not performed, one of the key indicators (the percent difference) is
provided as a tool. (Note that % difference checks are also included in the validation
routines when you generate a text transaction file).
Reports can be generated for one transaction type at a time. Not all fields are included on
the reports in order to make the report easier to print (and read).
12
June 2007

-------
AOSP&A Transaction Generator
Users' Guide
9. Tips for using Excel
Various tips to help with using this workbook:
•	You can hide look up sheets, and unhide them, as desired. The coding manual
worksheet must not be hidden if you want to access it via right clicking the field
names.
•	You should always use Paste - Special when copying and pasting. Choose Paste
Values only; this will keep the cells' data validation formulae in place for the
selected cells.
•	Deleting rows on the transaction spreadsheets is OK. The data validations are still
in place.
•	You can use Excel filters and sorting if helpful.
•	The default "right-click" menu of commands is not active on the worksheets (cut,
copy, paste, insert, delete, clear, format, etc.), because the right click event is used
for providing look ups. You can still perform these functions by using the Excel
menu, or shortcut keys (Cntl A, Cntl C, Cntl X, Cntl V).
•	If you do not use the "Set Up Screening Group" button, all of the active criteria
monitors in the USEPA Region will remain on the look up worksheets. You can
use the application in this way, but you must remember which monitors are
appropriate for the screening group you will use to submit data input transactions.
13
June 2007

-------
AOSP&A Transaction Generator
Users' Guide
10. Help
About Application Help
Application Help is included in the workbook as a worksheet. The help menu item and
F1 key will provide Excel help only.
Data Questions
Right click the column headings on the TransactionRA and TransactionRP worksheets to
see the coding manual information for each field. You can also get a copy of the data
input formats from NADG's AQS website
(http://www.epa.gov/ttn/airs/airsaqs/index.htm). Look under Manuals and Guides for
Input Transaction Formats, Data Coding Manual, and Data Dictionary. Also, the AQS
Helpline is available to help you get answers to your questions.
Contact:
Bill Frietsche
(919)541-5451
frietsche.bill@epa.gov
AQS Help Line
1-866-411-4EPA (1-866-411-4372)
Thank you for providing data to AQS!
14
June 2007

-------