How to use Excel to file

How to use Excel to file C115‟s via OCD Online, E-Permitting Revised: 8/18/2006 Table of Contents INTRODUCTION............................................................................................. 1 GETTING STARTED WITH C115 ELECTRONIC FILING ......................................... 1 1. DOWNLOAD THE C115 ADD-IN MACRO…………………………………..1 2. DOWNLOAD THE C115 SPREADSHEET….. ……………………………….3 CONFIRMING THE INITIAL FILE INSTALLATION ..................................................... 4 FILE CONFIGURATION .............................................................................. 4 C115 SPREADSHEET….……….……….……………………………………… 4 “ADD-IN” MACRO ............................................................................................. 5 USING OCD’S C115 SPREADSHEET FILE ................................................ 8 ENTERING DATA TO YOUR C115 SPREADSHEET ............................................... 8 REMAINING COLUMNS .................................................................................... 12 GENERAL C115 INFORMATION ........................................................................ 12 CREATING AND SUBMITTING YOUR C115 REPORT.......................................... 13 SUMMARY OF THE MONTHLY C115 FILING PROCESS..……………………… 16 C115 SPREADSHEET TROUBLESHOOTING GUIDE ............................................. 17 F I L E A C Q U I S I T I O N A N D I N S T A L L A T I O N Section 1 Introduction T S O F T W A R E R E Q U I R M E N T S his software solution includes two files; 1.) A simple Microsoft Excel spreadsheet (C115Spreadsheet.xls) that looks like the paper version of the Oil Conservation Division‟s (OCD‟s) C115 Monthly Production Report. 2.) The “Add-in” macro file (C115.xla) that processes data from the spreadsheet and creates a text file (“Flat File”), which the operator may then submit to OCD as an attached file via the OCD website at OCD Online, E-Permitting. For instructions on accessing OCD Online and submitting a C115 via E-Permitting, go to the OCD website: www.emnrd.state.nm.us/ocd and select „Frequently Asked Questions‟ from the options on the left. Scroll down to Monthly Well Production Reports (C-115s) and click the „User Manual for C-115 Web‟ link. The User Manual is available in PDF or PowerPoint formats. NOTE: The “Add-in” Macro will only work in conjunction with     MS Windows 95/98 or NT 4.0 MS Excel 97/98 or newer Internet e-mail software (for contact information, approvals, rejections) Internet access software (for Internet submissions) If you are running a non-supported version of Excel, you will need to upgrade to a more recent version. Microsoft Excel 97 or newer versions. Getting Started with C-115 Electronic Filing Downloading the Excel files from the OCD website Open Internet Explorer or any web browser and go to emnrd.state.nm.us/ocd Select „Frequently Asked Questions‟, scroll down to “Monthly Well Production Reports (C-115s) “ and select: How can I file my C-115 (Monthly Production Report) electronically? 1. Download the C115 “Add-in” Macro File (Add-in) NOTE: For Internet Downloads, use a right click of your mouse button to save the files to disk. Right click on Excel Piece 1 and select „Save Target As.‟ Note directions next to the link. Three changes are required in the „Save As‟ box; 1.) Save to C:\Program Files\Microsoft Office\Office or Office11\XLStart 2.) The file name must be changed from c115_000.xls to c115.xla 3.) The file type must be changed from Microsoft Excel Worksheet to All Files prior to saving. 1 F I L E A C Q U I S I T I O N A N D I N S T A L L A T I O N 1.) 2.) 3.) If you do not have an XLStart folder, save the macro file (c115.xla) to Local Disk (C:). Open Excel, go to „Tools‟ and select „Add-Ins.‟ Select „Browse‟ and double click on c115.xla. 2 F I L E A C Q U I S I T I O N A N D I N S T A L L A T I O N Check the box next to NM ONGARD C-115 to activate the macro. Copy “C115.xla” to Excel‟s “XlStart” folder Test the installation by running Excel and noting whether “C-115 Electronic Reporting” has been added to the Tools pull-down menu. 2. Download the C115 Spreadsheet Right click on Excel Piece 2 and select „Save Target As.‟ The spreadsheet file „C115Spreadsheet.xls‟ can be saved to any desired drive or folder on a PC. The file name and type do not need to be changed. However, selecting a file name that includes the C115 Report month and year is recommended (ex: C115062006.xls). Updating the month and year in the file name using the „Save As‟ File option will create a new file for each report month. Confirming the Initial File Installation If Excel was open, close the program and reopen it, otherwise open Excel. To test that the Add-in is available, select „Tools‟ from the pull-down menu. A new menu item „C115 Electronic Reporting‟ will appear near or at the bottom of the menu. If you don‟t see this, try shutting down and re-booting your computer. Once installed in the „XlStart‟ Folder or through „Add-ins,‟ this new option will appear in every Excel spreadsheet. Note: If you are running Windows 95, be certain to go to the "Tools" pulldown menu, select "Add-ins,” and check the “NM ONGARD C-115” Add-in from the list to activate it. 3 F I L E C O N F I G U R A T I O N Section 2 C115 Spreadsheet & Add-in Macro Configuration Configuring the Report Add-in Macro is fairly simple if you use the spreadsheet provided by OCD. If you decide to use your own existing spreadsheet, the setup will be somewhat cumbersome and it will require that you pay very close attention to various settings. T he C-115 spreadsheet provided by OCD is designed to look like a standard C115 paper form. The „C-115 Electronic Report‟ Add-in (c115.xla) is configured to work with the layout provided in the spreadsheet format below. To configure this Add-in requires that you have several pieces of information at hand (see requirements box at left on next page). Most of the examples in this section assume that you will be using the OCD C115 spreadsheet. If this is not the case, you will have to identify the location of the required reporting information in your existing spreadsheet and reconfigure the Add-in‟s „Column Settings‟ screen. 4 F I L E C O N F I G U R A T I O N Configuring the Add-in Macro The Add-in is not difficult to configure if you follow the procedure outlined below after opening Excel. 1. Select „File‟ and then „Open‟ from the pull-down menu 2. Browse for your „C115Spreadsheet.xls‟ file (or new file name if changed after downloading) and double-click to open it 3. Select „Tools‟ from Excel‟s pull-down menu 4. R E Q U I R E D I N F O R M A T I O N Select „C-115 Electronic Report‟ from the menu. When selected, the Add-in launches a dialog box, like the one illustrated below. Information requirements for each of the three tabs in the Add-in are outlined on the following pages.     OGRID number Contact name Contact phone Name of output file Note: If using the OCD spreadsheet, fill in the required information on first the „C1115 Report Settings‟ tab and then the „Output File‟ tab before clicking the OK button. ON THE C-115 REPORT SETTINGS TAB: 1. 2. 3. 4. Enter your OGRID number (six digits, add leading digits ex: 000149) Enter the upcoming report month (one or two digit report month, 1 – 12) Enter the upcoming report year (use four digits) Enter the correct contact name and phone number 5 F I L E C O N F I G U R A T I O N ON THE COLUMN SETTINGS TAB: NOTE: You can avoid column reconfiguration if you use the OCD spreadsheet No changes are required if you are using the OCD spreadsheet file. ON THE OUTPUT FILE TAB: 1. The default „File Path Name‟ is A:\C115DATA.txt. Edit the „File Path Name‟ to reflect the location (drive) where text files will be saved and for the desired file name. A recommended example is: „„C:\C115DATA0606.TXT” (saving to the C:\ drive and adding the month and year to the file name). 2. Uncheck the Confirmation Box, it isn‟t necessary. 6 F I L E C O N F I G U R A T I O N 3. Select OK and the „C-115 Processing‟ box will appear Note: The next time you use the Add-in; it will remember the OGRID number and contact information. The „Report Month‟ and „Report Year‟ in the „C-115 Report Settings‟ tab and the month/year in your file name in the „Output File‟ tab MUST BE UPDATED EACH MONTH The next section describes in detail how to use the spreadsheet and includes a list of common problems and their solutions. 7 U S I N G O C D ‟ S C 1 1 5 S P R E A D S H E E T Section 3 Using OCD‟s C115 Spreadsheet File The C115 spreadsheet is a simple Excel file, but you do need to stay consistent in how you enter the data. (WARNING: It is important to keep your report data for each month in a different file – only one report month per file, because the Add-in takes data from ALL of the Worksheets in the current file.) T he spreadsheet used for C115 reporting is reasonably straightforward; the only requirement is that the correct data is entered in the appropriate column. The preferred source of well information (Pool number, Property number, API and POD number) for the initial setup of the spreadsheet is the „Well List‟ on the OCD website. Go to emnrd.state.nm.us/ocd, select „OCD Online‟ and then „E-Permitting.‟ On the left under Operator Data, select „Well List‟. Enter your Operator name or OGRID number and click the „Get Report‟ button. Entering Data to Your C115 Spreadsheet Each of the columns processed by the Add-in are briefly described below. For the Add-in to work correctly you must follow the reporting conventions described below. Column A This column should remain blank. No data is required in column A. Column B Well Information (Box 7) 1. Enter the 5-digit pool number followed by the pool name (in the same cell - add leading zeros if necessary, ex: 07227). 2. In the next row enter the 6-digit producing property number followed by the property name (in the same cell - add leading zeros if necessary, ex: 026481). -8- U S I N G O C D ‟ S C 1 1 5 S P R E A D S H E E T 3. In the next row enter the well number followed by the unit lettersection-township-range (U-L-S-T-R) location for the well. This entire line is optional depending on the operator‟s preference. 4. In the next row enter the well‟s API number (dashes are important). Well Number & ULSTR are optional Repeat steps 3-4 for each well within that property number. Repeat the entire procedure for each different Pool and Property number. Column C – “Code 1” (Well Status) (Box 8) Well status as of the last day of the reported month, use one of the following codes (must be in CAPS): 1. 2. 3. 4. Injection/ Disposal Information (F) Flowing (P) Pumping (G) Gas Lift (S) Shut-in 5. 6. 7. 8. (T) Temporarily Abandoned (I) Injection Well (D) Salt Water Disposal Well (A) Plugged and Abandoned INJECTION/DISPOSAL These wells can only be reported with Code 1 as “D” or “I”. If the well is “Shut in” or “Temporarily abandoned” please still use a “D” or an “I” whichever is appropriate and in the volume and pressure columns use zeros. Code 2 should be whatever product is normally disposed of or injected. Column D – Volume AMOUNTS – NO DECIMALS) (Box 9) Enter the number of barrels of water or MCF of gas injected. (WHOLE Column E (Box 10) Enter the injection pressure (PSIG). Please do not report vacuum. -9- U S I N G O C D ‟ S C 1 1 5 S P R E A D S H E E T Column F - “Code 2” (Type of Injected Fluid) (Box 11) Type of injected fluid code, use one of the following codes (must be in CAPS): 1. 2. 3. 4. (W) Water (G) Gas (C) CO2 (O) Other Production Information Column G (Box 12) Enter the number of barrels of crude oil or condensate produced for the month. (WHOLE AMOUNT, NO DECIMALS) Column H (Box 13) Enter the number of barrels of water produced for the month. (WHOLE AMOUNT, NO DECIMALS) Column I (Box 14) Enter the MCF of gas (includes CO2) produced for the month. (WHOLE AMOUNT, NO DECIMALS) Column J (Box 15) Enter the number of days that the well produced or injected during the month. Column K - “Code 3” (Type of Product) Disposition Information (Box 16) Enter one of the following acceptable entry codes (must be in CAPS): 1. (G) Gas (includes CO2) 2. (O) Crude Oil, Condensate, Drip 3. (W) Water - 10 - U S I N G O C D ‟ S C 1 1 5 S P R E A D S H E E T Column L (Box 17) Enter the point-of-disposition (POD) number (7-digits) for each product transported from the property or enter the non-transported volumes and oil storage balance. An Oil POD number is required for all reported Oil disposition. The assigned POD number for each well completion can be found on the OCD „Well List.‟ Column M (Box 18) Enter the gas BTU (Whole number) in effect for the gas volume transported (adjusted to 15.025 PSIA at 60o F) or enter the weighted average API oil gravity (include one decimal space format: 00.0) of oil transported. Column N (Box 19) Enter the beginning oil storage volume for the current reporting month. (WHOLE AMOUNT, NO DECIMALS) Column O DECIMALS) (Box 20) Enter the volume of oil, gas, or water transported or otherwise dispositioned (lost, vented, spilled, used on property, etc). (WHOLE AMOUNT, NO Column P NOTE: There must (Box 21) be a transporter OGRID number (Box 21) or a “Code 4” (Box 22) to correspond with each volume amount entered in Box 20. Enter a transporter OGRID for oil and gas volumes transported from the POD. A list of current transporters is available on OCD‟s website under „Statistics‟, „Transporters.‟ Do not enter a transporter OGRID, if the volume in Box 20 corresponds to non-transported disposition and a “Code 4” is enter in Box 22. Column Q - “Code 4” (Non-transported Disposition) (Box 22) Enter one of the following acceptable entry codes (must be in CAPS): For Oil: 1. (C) Circulating Oil 2. (S) Sedimentation 3. (L) Lost 4. (T) Theft 5. (O) Other - 11 - U S I N G O C D ‟ S C 1 1 5 S P R E A D S H E E T For Gas: 1. (G) Gas Lift 2. (R) Repressurizing 3. (V) Vented 4. (U) Gas used on property 5. (L) Lost 6. (O) Other For Water: 1. (I) Water re-injected on property 2. (P) Pitted 3. (A) Evaporated 4. (O) Other Column R (Box 23) Enter the ending oil storage volume. (WHOLE AMOUNT, NO DECIMALS) Remaining Columns No data is required in the remaining columns. General C115 Information     Report all gas volumes at 15.025 PSIA at 60º F. Report all oil volumes to the nearest whole barrel. In general, produced volumes should equal disposition volumes. Please keep this in mind when rounding. All production amounts must be entered on the same row as their corresponding API‟s. There must be a transporter OGRID number or a “Code-4” for each disposition volume amount entered – one or the other. Enter an OGRID number if the product was transported; enter a “Code-4” if the product was not transported. OCD cannot accept an initial or amended C115 file that does not include all well completions that should be reported for each Operator OGRID. PROPERTY NUMBERS ALWAYS CHANGE WHEN THE OPERATOR CHANGES.   - 12 - U S I N G O C D ‟ S C 1 1 5 S P R E A D S H E E T Creating and Submitting Your C-115 Report When you have completed entering data into your spreadsheet, use the following procedure to create the text file (“Flat File”) that will be attached via the website in OCD Online, E-Permitting. 1. With your final spreadsheet open, go to the „Tools‟ pull-down menu and select „C115 Electronic Report.‟ The dialog box opens to the default „C-115 Report Settings‟ tab. The fields will display with the information from the last time a text file was created. Confirm the OGRID is correct, update the Report Month to reflect the current month being reported, and confirm the Report Year, Contact Name and Phone are correct. 2. If you are filing an amended report, check the „Amended Report?‟ box above OGRID and confirm the spreadsheet currently open on the screen is the entire report - all wells must be reported on all reports. - 13 - U S I N G O C D ‟ S C 1 1 5 S P R E A D S H E E T 3. Move to the Output File Tab and update the file name with the current month and year if you intend to save a separate text file for each production month. Otherwise, you can simply overwrite the file by leaving the file name unchanged (not recommended). NOTE: Uncheck the Confirmation Sheet – it is not required. 4. Click the „OK‟ button and the „C-115 Processing‟ screen displays. The „C-115 Rows Processed‟ number reflects all the rows in the spreadsheet read by the Add-in (including headers, footers and miscellaneous information). The „E-Report Rows Generated‟ number is the total rows of C115 information extracted from the spreadsheet and copied to the text file. 5. Click the Exit button. You may want to locate your Output data file (saved either to your hard drive or to diskette) before going to the OCD - 14 - U S I N G O C D ‟ S C 1 1 5 S P R E A D S H E E T website. See “Introduction” on page one for information on uploading your text file to OCD Online, E-Permitting. A sample Output file 6. If you are creating a new text file for a text file that already exists (filing an amended report or correcting errors for a report previously validated with errors in OCD Online, E-Permitting), the following screen will display: 7. Select „Yes‟ to delete the previous file and replace it with the current file. If you selected „OK‟ from the „C-115 Electronic Reporting‟ dialog box in error, select „Cancel.‟ Warning: selecting „No‟ results in a duplication of data as the current data is appended to the previous file. - 15 - U S I N G O C D ‟ S C 1 1 5 S P R E A D S H E E T Summary of the Monthly C115 Filing Process (based on recommended options) 1. Open the previous month‟s spreadsheet 2. Update well information to reflect changes from the prior month 3. Enter new injection, production and disposition numbers 4. Using the „Save As‟ File option, change the file name to reflect the current month being reported 5. Go to „Tools‟ and select „C-115 Electronic Reporting.‟ 6. In the „C-115 Report Settings‟ tab, update the Report Month and Year to reflect the current month being reported 7. In the „Output File‟ tab, update the file name to reflect the current month being reported 8. Click the „OK‟ button to create a new text file 9. Go to the OCD website and login 10. Create a new C-115, browse for the current text file, attach the file, validate the file and when “NO ERRORS EXIST,” submit the C115 to OCD For instructions on accessing OCD Online and submitting a C115 via EPermitting, go to the OCD website: www.emnrd.state.nm.us/ocd and select „Frequently Asked Questions‟ from the options on the left. Scroll down to Monthly Well Production Reports (C-115s) and click the „User Manual for C-115 Web‟ link. The User Manual is available in PDF or PowerPoint formats. - 16 - U S I N G O C D ‟ S C 1 1 5 S P R E A D S H E E T Spreadsheet Troubleshooting Guide C O M M O N P R O B L E M S Problem Solution Nothing happens when I press the OK button One of the data fields in the Report Settings on the Add-in. screen is not filled out correctly.  Check that you are using 4 digits for the year. Verify that the column numbers specified for each of the data items on the Column Settings tab match those in the spreadsheet.  Why does the Add-in find so many rows to process? Can I use other columns in the worksheet for other purposes? The Add-in reads every row in each worksheet contained in the currently open file, including headers and footers. Yes, but don‟t use the columns specified in the Column Settings of the Add-in for any other type of data. This error message is generated by several I get a Windows “Run Time error 91… “ after different problems: selecting „OK‟ in the C-115 Electronic Reporting dialog box when creating a text file.  Check your worksheet to confirm that you have entered the data correctly (see data entry instructions found in this section).  Confirm your C115 Excel Spreadsheet is open and there is only one month in the file. If saving your .txt to A:\, confirm your floppy disk is formatted, has available space and is completely inserted into the A: \ drive. The file path name cannot contain special characters or spaces, should be limited to 12-15 characters and must end with .txt.   If you need assistance please contact: Crystal Padilla (505) 476-3482 E-mail: crystal.padilla@state.nm.us. If you encounter other problems or find solutions to problems that are not included in this list, please notify us. We welcome your input in helping us better serve current and future users of this software. - 17 -