EPA420-F-01-007 February 2001 Gasoline Sulfur Compliance Reports: Guidance for Preparing Spreadsheet or Comma Delimited Text Files for Electronic Reporting Transportation and Regional Programs Division Office of Transportation and Air Quality U.S. Environmental Protection Agency ------- The following guidance applies to periodic reporting forms and data collections related under the Tier n Gasoline Sulfur Rule, which includes reporting related to various sulfur standards and the generation, transfer and use of sulfur allotments and credits. The statutory provisions and EPA regulations described in this document contain legally binding requirements. This document does not substitute for those provisions or regulations, nor is it a regulation itself. Thus, it does not impose legally binding requirements on EPA or the regulated community, and may not apply to a particular situation based upon the circumstances. EPA decisionmakers retain the discretion to adopt approaches on a case-by-case basis that differ from this guidance where appropriate. Any decisions regarding a particular facility will be made based on the statute and regulations. Therefore, interested parties are free to raise questions and objections about the substance of this guidance and the appropriateness of the application of this guidance to a particular situation. EPA will consider whether or not the recommendations or interpretations in the guidance are appropriate in that situation. This guidance is a living document and may be revised periodically without public notice. EPA welcomes public comments on this document at any time and will consider those comments in any future revision of this guidance document. Information submitted with regard to baseline determinations is covered by the guidance document, "Guidance to Parties Submitting Gasoline Sulfur Baseline Applications," EPA 420-S- 00-001 (March 2000) The guidance document related to baseline applications is available on EPA's Website at: http://www.epa.gov/otaq Information required to be submitted for annual reporting and data collection purposes and covered by this guidance include the following types information required under 40 CFR § 80.370: Beginning in 2000-2003 - data related to early credits (2000-2003) and early allotments generated by refiners (2003) Beginning in 2004 - data related to corporate pool average standards (2004-2005), per- gallon cap standards (beginning in 2004), geographic phase-in (GPA) gasoline standard (2004-2006), small refiner standards (2004-2007)1, and credit and allotment activity (beginning in 2004). Beginning in 20052 - refinery and importer annual average standards (2005 and beyond) This guidance does not apply to attest engagements, as required under § 80.370(f). In order to make submission of the data as easy as possible for both reporting parties and EPA, we request that the information be provided in electronic format. EPA will accept reporting 1 Under a hardship exemption, the small refiner standards may be in effect for 2008-2009 as well. 2 Note that in 2005 only, both the corporate pool annual average standard and the refinery/importer annual average standard apply. 1 ------- information as either a comma delimited text file or a spreadsheet file from any of the major spreadsheet software applications available. We consider the major spreadsheet software applications to include Lotus 1-2-3, Microsoft Excel, and Corel Quattro Pro. If you need other arrangements, please contact us. (See Contact and Address Information found later in this Guidance.) This guidance should be used in conjunction with the individual EPA form instructions established for Gasoline Sulfur Reporting. The form instructions provide detailed information on field order, codes, data type, length, and format. In addition to the instructions, spreadsheet template files are available. The templates allow the data to be entered directly into a given report, providing a column header row of all necessary fields. Concept - Whether reporting via comma delimited text file or spreadsheet file, the underlying reporting concept is: one line (or one row in the case of a spreadsheet) is equal to one complete report. Each report will contain the information necessary to identify the report type, the owner of the data and the data itself. This is, in fact, flat file reporting. Ground Rules - These are a few items to keep in mind before getting started: • Use upper case when filling text data fields. You may use both upper and lower case in comment lines since they do not contain report data. • Some numeric fields may be positive or negative and are identified by the "±" symbol in the form instructions. Negative numbers must be identified using the "-" sign. Positive numbers should be unsigned, i.e. do not use "+" sign. Do not use any punctuation in text data fields. You may use punctuation in comment lines since they do not contain report data. Some numeric fields such as Company ID, Facility ID and Batch ID are actually text fields. Please include all preceding zeros when applicable. • For those submitting comma delimited files, we can process text fields with or without surrounding "quotation marks." • Always use the latest version forms. The EPA website contains the latest form instructions and templates. Additionally, the site contains an archive area with prior form versions that may be used for resubmissions. Please visit frequently to get the latest updates and changes. Originals vs. Resubmissions. Once an Original report has been submitted to EPA, all changes or updates to that report must be submitted as a Resubmission. Each line/row in a submitted file/spreadsheet must start with either a comment symbol (##) or a valid Report Form ID. Or the line/row must be completely blank. A complete, single line/row, report is the combination of the overhead (identifying information) and the report detail (the actual data). If you have any questions, comments, or suggestions regarding reporting, please e-mail or call. ------- Report Forms - Reporting forms have been separated into two types, or families — those pertaining to company information are one and those pertaining to facility (individual refinery or import facility) information are the other. Gasoline Sulfur Company forms are identified by the prefix 'GSC' and deal with reporting of allotments and corporate pool averages. Gasoline Sulfur Facility forms are identified by the prefix 'GSF' and deal with reporting of credits, individual gasoline batches, and general facility information. Each family of forms is made up of one overhead form and several report forms. When complete, the forms of both families contain fields that are necessary to identify the form, the form's owner, and the reporting year of the data. This information is called "overhead." The overhead is common information that must be reported regardless the type of report being submitted. In other words, all forms in a family will contain the same first half dozen or so fields. The detailed instructions for the report forms contain the data part of the reports only. The overhead information has been omitted to save on space. But, no form is complete without the overhead and detail parts combined together. The EPA website maintains a listing of current forms, instructions, and spreadsheet templates for online reference as well as download. Form Examples - The following two examples are fictitious Sulfur Batch Report submissions. Figures 1.1 and 1.2 represent the same report information in different formats. The first version is comma delimited and the second is a spreadsheet representation of the same data. Fig. 1.1 Sample of comma delimited Sulfur Batch Report ## Capital City Refinery 1999 Sulfur Batch Reports GSF0400,O,02/21/2000,1999,,1234,12345,000001,RG,01/02/1999,10000,140 GSF0400,O,02/21/2000,1999,,1234,12345,000002, ... ------- Fig. 1.2 Sample of spreadsheet Sulfur Batch Report ## Capital City Refinery 1999 Sulfur Batch Reports Q o LL •c O Q. CD o: at 1 GSF0400 GSF0400 CD Q. O Q. CD o: 0 o CD -H- < CO Q O Q. CD o; 02/21/2000 02/21/2000 j_ CO p •c o Q. CD Di 1999 1999 O 0. O Q cc E o O 1234 1234 Q >, 'o CO 12345 12345 CD 5 Z .c CO 00 1 2 CD Q. H B •o 2 Q_ RG CD CO Q o B •o 2 Q_ 01/02/1999 CD "5 B CO 00 10000 a 3 w 140 The examples above show that a Sulfur Batch Report is made up of twelve fields. The first seven fields of this report are the overhead fields (Report Form ID, Report Type, Report Date, Report Year, GPA Gas, Company ID, and Facility ID). Fields 8 - 12 are the data fields (Batch Number, Product Type, Production Date, Batch Volume and Sulfur). The Report Form ID, the first field in the sample reports shown above, is the driving force behind this reporting system. This ID defines the remaining information in the report. For Form GSF0400, the field order, data formats and instructions will never change; the sixth field will always be the Company ID and the twelfth field will always be the Sulfur value, etc. If a form is updated or changed, it will be given a new Report Form ID number. The old form will be archived and its use will be limited to submitting resubmissions only. Comments - Comments may be added to either of the report formats so long as they are on a single line and are not combined with any data. Use two pound signs (##) to indicate the start of a comment line. Since comment lines do not contain data, you may use any capitalization and punctuation you need, provided you start the line with the ## symbol. The examples above (Figures 1.1 and 1.2) contain the comment line '## Capital City Refinery 1999 Sulfur Batch Reports.' Note that in the above spreadsheet example (Fig. 1.2), the first two rows are comment lines. The second row, begining with '## Report Form ID,' functions as a header for the data in the columns beneath. Since the column headers are not actual data, the row is marked in this manner. Use as many or as few comment lines as you need. Column Headers - Column Headers are unique to the spreadsheet file only and are not necessary for reporting purposes. Note that there is no header row in the comma delimited format. Column Headers provide a guide for entering data in the proper position. The entire row may be deleted if you like. ------- RFG & Anti-Dumping Batch Reports and Sulfur Batch Reporting - To avoid placing additional burden on the reporting community, EPA has taken several steps which satisfy Sulfur Batch reporting requirements through the existing RFG & Anti-Dumping Batch reporting process. A new field has been added to the Batch Report forms which allows the batch to be identified as GPA Gasoline (Gasoline produced in a Geographic Phase-In Area by registered GPA refiners and importers.) This change has been implemented in existing paper bubble reports as well as the EDI mapping. Additionally, we have chosen to release the comma delimited and spreadsheet versions of the "RFG & Anti-Dumping Batch Report" and the "RFG & Anti-Dumping Report for Batches Containing Previously Certified Gasoline." These reports follow the same conventions outlined in this guidance for GSC and GSF reports, but are identified as the RFG family of reports. Combining Reports - A single comma delimited file or spreadsheet file may contain all the reports of your submission. In fact, EPA encourages the use of a single file as it will be easier for us to track and process. With respect to spreadsheets, there is no need to have more than one worksheet (or page). This is possible because a single line is a complete and independent report; and there is no need to worry about the order, or mixing of lines within a file. Within a file submission, reports should be in a logical order with thoughtfully placed comment lines dividing different report types. Naming the file - Give your file a meaningful name. We suggest the file name should include the Company ID or Facility ID, and the year the averaging reports cover. For example, reports from Company 1234 for the 2001 reporting year may be ' 1234_01 .txt" for a comma delimited text file. Or, for facility 67890 with resubmissions for year 2000 reports, '67890_OOR.xls' may be a good choice. Long file names are acceptable. Use native file extension when naming your files. Text files will normally have a .txt, or .csv extension while spreadsheets should have their application default extension; .xls for Microsoft Excel, .123 or .wk4, .wk3, etc. for Lotus 1-2-3, and .qpw for Corel Quattro Pro. Confidential Business Information, Encryption and Submitting ~ Information covered by a claim of business confidentiality should be clearly marked. Submit a cover letter, a floppy diskette containing the report file(s) and a paper printout of the file you are submitting. Beginning May 2001, all report files submitted to this office must be encrypted. Instructions for encrypting and submitting report files beginning May 2001 will be posted on EPA's Website at: http://www.epa.gov/otaq or will be available through John Weihrauch (see contact information below). EPA will treat submissions covered by a claim of business confidentiality in accordance with established safeguards and with the requirements of 40 CFR Part 2. Spreadsheets and comma delimited text files should be submitted as electronic files on 1.4 megabyte floppy disks. A hard copy cover letter signed by an appropriate corporate officer ------- certifying that the contents of the submission meet the requirements described in § 80.370(d)(2) of the regulations, must accompany the floppy disk and printout of the data. The letter should reference the included file name(s) containing the reports, the company name and EPA company ID, all facility names and EPA facility IDs covered by the report(s), and include the other information required in § 80.370, except for § 80.370(f) dealing with attest engagements and as discussed above. Additionally, the letter should include the name, address, phone number, facsimile number, and E-mail address of a corporate contact person. EPA Contact and Address Information - EPA Contact: John Weihrauch (202)343-9477 Fax:(202)343-2802 E-mail: weihrauch.john@epa.gov To receive paper copies of all related forms and instructions, please contact: Angie Young (202)343-9038 Fax:(202)343-2802 E-mail: young.angie@epa.gov If sent by U.S. mail, send to: U.S. EPA Attn: Sulfur Program (6406 J) 1200 Pennsylvania Ave. NW Washington, DC 20460 If sent by an overnight or courier service, send to: U.S. EPA Attn: Sulfur Program - John Weihrauch 1310 L Street, NW Washington, DC 20005 Phone: (202) 343-9477 Executive Order 13132, entitled "Federalism" (64 FR 43255, August 10, 1999), requires EPA to develop an accountable process to ensure "meaningful and timely input by State and local ------- officials in the development of regulatory policies that have federalism implications." "Policies that have federalism implications" is defined in the Executive Order to include regulations and regulatory policies that have "substantial direct effects on the States, on the relationship between the national government and the States, or on the distribution of power and responsibilities among the various levels of government." This guidance document does not have federalism implications. It will not have substantial direct effects on the States, on the relationship between the national government and the States, or on the distribution of power and responsibilities among the various levels of government, as specified in Executive Order 13132. It covers the procedures to follow in submitting data on the sulfur content of gasoline. Thus, the requirements of section 6 of the Executive Order do not apply to this guidance document. ------- |