User's manual for the commercial Salmon Catch Spreadsheet
Contents
1. e 5 WN OOND i bh BR The The The The The The The The REFERENCES APPENDIX APPENDIX B APPENDIX C APPENDIX D APPENDIX E iv LIST w RETRIEVE SAVE s e o VIEW s SHEET menu item TITLE DELETE AR SPEC menu item PR SPEC menu item G RESTR menu item i Menu selection edit G RESTR menu COL menu item ROW menu item OUTPUT menu item DISPLAY PRINTED e CGP osasu SPREADSHEE a x EXEC menu item Consistency Checks QUIT menu item ACKNOWLEDGEMENTS o o X 9 e e o Qs se w d o w J Qu 9 COs e ewe w w wv Vw 9 v E e 33 35 39 41 44 FIGURE FIGURE FIGURE FIGURE FIGURE FIGURE FIGURE FIGURE FIGURE FIGURE FIGURE FIGURE FIGURE FIGURE FIGURE FIGURE TABLE OF FIGURES Logical Structure of Catch System Catch Spreadsheet Identification The main menu The FILE menu The SHEET menu The AREA menu Time Periods The global restriction menu Column Data selection The ROW menu ete Example of column data amp2. Canadian Technical Report of Fisheries and Aquatic Sciences 1807 1991 USER S MANUAL FOR THE COMMERCIAL SALMON CATCH SPREADSHEET PROGRAM by M A Holmes and D W A Whitfield Biological Sciences Branch Department of Fisheries and Oceans Pacific Biological Station Nanaimo British Columbia Canada V9R 5K6 jj c Minister of Supply and Services Canada 1991 Cat No Fs 97 6 1807E ISSN 0706 6457 Correct citation for this publication Holmes M A and D W A Whitfield 1991 User s Manual for the Commercial Salmon Catch Spreadsheet Program Can Tech Rep Fish Aquat Sci 1807 44 p ABSTRACT e o e e F ORWARD o e e 9 INTRODUCTION a o SECTION I GENERAL OVERVIEW e Historical Perspective Sales slips SECTION II DATABASE STRUCTURE TABLE Catch Data System Structure of the Catch Data Stratum fields Effort fields Species field Catch fields SECTION III PROGRAM USER S MANUAL System Access The main menu The status line USER INTERFACE Overview El OF CONTENTS Working with the menu The CDSS menu G_RESTR COL e ROW AR SPEC PR SPEC DB STAT FILE SHEET OUTPUT EXEC QUIT The DB STAT PATH FILENAME menu item The FILE menu item 0 167 9 0 e e ee o e 9 9 wm 9 ww o e 9 SS et
3. If no restrictions are made for a label category all the information for that label will be included Thus no restrictions on years would result in summing the data selected over the entire range of years contained in the database Therefore the process of restricting the label categories to select the information relevant to the user is the key to using the program to create meaningful tables As an example of the combination of restriction and label selection consider the table below Pieces STWK 061 2091 STWK 062 4013 STWK 063 16570 STWK 064 24421 To produce this table the same restrictions on year period area gear and species were selected as for the two previous tables What was not selected was two levels of row and column label categories Therefore instead of reporting areas separately they have been pooled The same is true for years and gear types Comparison of this table with the previous one shows that the pieces reported here match that portion of the previous table for which totals have been made over areas years and gear types 16 Note that by not segregating areas years and gear types the table is not well documented Just by looking at this table there is no way to tell that the catch from only areas 10 and 12 years 1974 and 1975 and gear types troll and gill net are being shown To help with this problem CDSS also prints a summary of the selections made by the user for every report
4. AREAS STATS 25 27 STATS 21 23 24 Historical STATS 13 18 29A B C STATS 1 5 Historical STATS 29A B C D E STATS 1 5 STATS 14 18 STATS 12 13 STATS 6 11 STATAREA 20 STATAREA 20 STATS 25 27 STATS 21 24 STATS 6 9 30 STATS 10 12 Opening at the mouth 39 APPENDIX C MICRO COMPUTER SYSTEM REQUIREMENTS The Catch Data Summary or Spreadsheet System CDSS will run on any IBM PC or AT compatible with 640 KiloBytes Kb of memory and a hard disk large enough to accommodate the data files Program files software to run the spreadsheet require 1 2 MegaBytes Mb of disk space the index and data files require approximately 350 000 bytes per year The years 1952 to 1989 total approximately 11Mb of disk space Programs and data files for micro computers are available from the Salmon Information Group Yearly updates for current data files will be required as the data goes from a preliminary status to a permanent status no more editing is done Any preliminary data years will require yearly updated files to be obtained from the Salmon Group Obtain program and data files from the SSA group A copy of the spreadsheet programs and the catch data and index files for all available years must be loaded into the microcomputer To run the program on the micro go to the directory where program files are kept i e c catch code and enter CD88 C NCATCHNCODENCDSS Gene
5. Save sets the files in which spreadsheet specifications are stored have extension set If LIST is selected all of the filenames from the current directory are listed Selecting USER FILES results in a prompt for the desired filename extension i e to list all of your com files erase the and enter com RETRIEVB choosing this will show a menu of all file names with the extension 8ET from the current directory a file can be selected from the list by moving over to it via arrows and hitting RETURN 8AVB when saving a file the user is prompted for a filename If a name has already been entered under FILENAMB then moving to 8AVE will save it under that filename VIEW displays the same list of 8ET filenames as does RETRIEVE Lets the user see the files but not retrieve them The SHBET menu item The SHEET sub menu provides control over the sheets in a set During an interactive session more than one spreadsheet can be specified with NEW A single spreadsheet specification is called a sheet and the collection of sheets from one session is a set DB STAT FILE SHEET FIGURE 5 The SHEET menu TITLE prompts for character string to form title of current sheet Up to 50 characters can be chosen 8HOW displays information on the current sheet NEW adds a new empty sheet to the set Note sheet number in the bottom left hand corner of the status screen The maximum number of sheets is 10 GOTO
6. Statistics Canadian Department of Fisheries and Oceans DFO 1951 British Columbia Catch Statistics by area and type of gear Chumak Tony and D W A Whitfield 1989 PC screen and keyboard I O system Report produced under this contract Kuhn B R 1988 The MRP Reporter program A data extraction and reporting tool for the Mark recovery Program database Can Tech Rep Fish Aquat Sci 1625 145 p Kuhn Brian R D W A Whitfield and Tony Chumak 1988 The Custom Graphics Package a Pascal programmer s graphics toolbox and data presentation systen Canadian Technical Report of Fisheries and Aquatic Sciences No 1659 Whitehead Valerie 1990 Commercial Catch Statistics Systen Workflow Procedures and Data Dept of Fisheries amp Oceans in house document Wong F C 1983 Historical salmon commercial catch data system of the Fisheries Research Branch Department of Fisheries and Oceans Pacific Region Canadian Technical Report of Fisheries and Aquatic Sciences No 1156 33 APPENDIX Statweeks are labelled by a three digit code The first two digits delineate the month and the last one specifies the week within the month Each statweek in the catch database has 7 days and each month has 4 statweeks except that months 04 07 and 10 each have 5 periods Statweek 120 in CDSS is defined as all periods from December through February inclusive The table below for 1975 gives the week ending day and the
7. Troll Seine CORR WGT Gillnet Tot Net Tot Gear Unknown Move Quit T Top B Bottom C Clear all Select all Use ARROWS to move cursor lt Return gt Finished selection S Select U UnSelect PF1 Toggle Move S or UJ FIGURE 8 The global restriction menu The following section explains the small editor available within the menu selection box Menu selection editing This box contains information for manipulating the data choices Quit makes no choices T Top to goto the top of the screen B Bottom move to bottom of screen C Clear all clears all previous chosen selections Select all to choose all data choices 8 Select Hit 8 to begin selection 26 U UnSelect Hit U to unselect data PF1 Toggle S or 0 can use this to move among data selecting and unselecting i e choosing chinook and coho in SPECIES lt Return gt Finished selection selects data chosen with 8 Use ARROWS to move cursor to data NOTE Quit is the backslash and no restrictions would be chosen If a restriction is made then lt RETURN gt should be used G_RESTR menu below Year Period Area Gear Species Wgt_unit Corr_wgt The individual menu items are described Data from 1952 available choose the week s or month s which will define the report choices can be either catch region statareas subareas or districts the g
8. form round dressed head on etc number of fish pieces weight and price The sales slips are issued when the catch is first sold usually at the point of landing The fish processing companies collate the data and then forward the slips to DFO for keypunching Some companies now enter the data themselves through an electronic system The companies manually check the slips then enter them at their work site Extensive electronic checks are made at the time of keypunching The Remote Saleslip Entry System RSE gathers sales slip data directly via phone lines DFO will add the electronic data to their sale slip and average weight files Errors identified by DFO s audit must be corrected by the fish company within three working days Errors are minimized by validating such things as the vessel CFV species coding weight ranges etc The data are required to reach DFO within seven days of landing the catch Keypunching of the sales slips is done by contract staff who sort the information by gear area and time period and manually inspect and correct obvious errors such as incorrect species codes month day transpositions etc The biological sample data length weight age etc are used to estimate the average weight for each salmon species For a sales slip lacking information on the numbers of fish but with weight information the number is estimated by dividing the weight with the average weight of the particular species area week and gear
9. generated The summary for the above table is Sheet lof 1 Created 14 Mar 1991 11 26am Title Area spec STATS AREA SUBS Period spec STATS PERIOD Selected years 74 75 Selected areas 10 12 Selected periods STWK 061 STWK 062 STWK 063 STWK 064 Selected gear Troll Gillnet No species restriction Selected data Pieces Weight unit Kg Weight not corrected for dressed Cols l none N 2 none N Rows 1 PERIOD N 2 none 3 none N Output PRINTED The combination of the table and the summary provide sufficient information to properly interpret the output from CDSS Working with the menu The user makes choices through horizontal and vertical menus The general procedures for moving through these menus is described before discussing the formation of the spreadsheet n entry can be chosen by typing the first letter of the entry i e Pile or Screen or by using the arrow keys to move to the item of choice and then hitting Enter or Return Where menu items begin with the same letter the cursor goes to the first occurrence of that letter The main menu is displayed in one line across the screen top Sub menus are arranged one item above the other in boxes near the top left corner of the screen 17 Here are some quick tips for working with text within the menus PF2 is the help key PF4 refreshes the screen if it becomes corrupted lt gt backslash key steps user back out of submenus No
10. hit U and this will unselect 6 sula Hit Return if you hit then no choices will be made If you want to check your choices are okay hit Return again and the highlighted areas should be there If not select them again and make sure to hit Return Hit Return to get back to sub menu 7 Go to Gear Hit Return Hit S to select TROLL and then hit the down arrow to select GILLNET Hit PF1 F1 and then arrow over to 8BINE and hit S Hit Return Checking selections can be done as in 6 8 Go to Species Hit Return and then select CHINOOK Hit Return 9 Return to main menu line and choose COL Select DATA and choose Pieces Total Wgt Dressed and Avg Weight by using the arrow keys and following the directions in the help box When DATA is complete hit Enter and then return to main menu lt gt 10 In ROW choose 18T LEVEL as AREA and 2ND LEVEL as GEAR 11 Return to main menu and choose Output Printed is the default This report will be running interactively so we will choose Display also to see the report on the screen Hit S 12 If you aren t sure what you have you can check it out by going back to the main menu using lt gt and choosing SHEET Hit S in this case it means SHOW Figure 16 is what you should see The title was added by being in SHEET and choosing TITLE You can enter up to 50 characters to make a title SET DESCRIPTION Sheet 10f 1 Created 27 Aug 1990 8 28a
11. labelling is not a label at all in the terms of CDSS rather it is the name of the data type reported in the column pieces Notice that the middle level of labelling years is nested within the upper level statistical areas Therefore since two areas have been selected each year label is repeated twice once for each area All of the numbers in the first column satisfy both levels of label categories that is they represent numbers of salmon caught in area 10 in 1974 Likewise all of the numbers in the last column represent numbers of salmon caught in area 12 in 1975 14 The rows in this table have two levels of labels gear and statistical week Like the columns the row labels are also nested Thus the first four rows report on separate statistical weeks for troll caught fish while the next four rows do the same for gill net caught fish The combination of row and column gives the individual entries in the table their unique meaning For example in area 10 in 1975 the troll fleet caught 1087 salmon during statistical week 064 In addition to reporting the data selected by the user the CDSS program can also provide row or column totals This is often a useful feature However be warned that selection of totals can lead to a substantial increase in the size of the table produced For example if we select the option to produce totals for both rows and columns for the above table the resulting table is 10 12 TOTAL 76
12. the year the time period the fishing area and the fishing gear While the year and fishing gear have conventional definitions the time period and area use specialized terms The basic unit used to delineate the time period is the statistical fishing week or stat week which is explained by example in Appendix A The unit used to delineate a basic geographic area is the statistical fishing area stat area These areas are described and shown in Appendix B as part of the catch region list The use of the word statistical in these terms comes from their association with the catch statistics group within DFO The sales slip data are summarized by catch stratum i e year statarea statweek and gear in the database used by the spreadsheet program Information about individual vessels or individual landings is available on the detailed annual sales slip magnet tapes but the data are totalled for an individual catch stratum in the database Information about larger units is generated by combining the catch strata into groupings representing wider boundaries of space time gear etc Some of these groupings can be done automatically by selection of the appropriate spreadsheet menu item For example groupings into catch region catch regions usually incorporate several stat areas is a potential menu choice Thus the program generates all information about units larger than a single stratum by summing over the appropriate strata and related
13. this section briefly to get an idea of the choices to be made and then go to Appendix D to work through an example This will produce a simple report so that the documentation that follows will be more meaningful Overview A spreadsheet program creates a table of numbers arranged in rows and columns The columns are the vertical lines of numbers while the rows are the horizontal lines Thus in the table 1 2 3 4 5 6 the first row contains the three numbers 1 2 and 3 while the third column contains the numbers 3 and 6 Such a table is often a useful way to organize complex data especially when different rows and columns represent different facets of the data For example if in the above table we wanted to show the number of children enroled in grades 1 3 by the sex of the child we could write Numbers of children enroled in school Grade 1 Grade 2 Grade 3 Girls 1 2 3 Boys 4 5 6 Now our table is much more meaningful In this trivial example we have constructed a table containing numeric data which was organized in a logical way and becomes easy to interpret by the appropriate use of row and column labels The CDSS spreadsheet program allows the user to construct tables whose entries report on the commercial salmon catch in B C To organize a table in a meaningful way the user must decide what information is wanted in the rows and columns of the table and what labels are associated with rows and columns Therefore th
14. to form larger fisheries by DFO areas that define catch by a gear aspects of data for catch region statarea divisions The list below shows four The first row is the catch region number i e catch region 04 is Georgia Strait Troll The second column lists the abbreviation of the catch region that is used in the spreadsheet program when you are defining area by catch region The third column gives a description of the catch region while the fourth column tells you which of the statareas belong to that catch region For some years some statistical areas were divided into subareas i e statareas 2 5 and data is reported by subarea Care should be used when using subareas since when available they will not necessarily total to statarea Historical data catch region WOT amp AKN data were collected prior to 1979 when Canadian fishermen were permitted to fish off the American coast CATCH REGION 01 NWTR 02 SWTR 03 WOT 04 GSTR 06 NTR 07 ATR 08 FGN 09 NN 10 GSN 11 JSN 12 CN 13 JFN 14 JFTR 20 NWVN 21 SWVN 56 NCTR 57 SCTR 58 FSN DESCRIPTION NW Van Is Troll SW Van Is Troll Wash Oreg Troll Georgia Strait Troll Northern Troll Alaska Troll Fraser River Gill Net Northern Net Georgia Strait Net Johnstone Strait Net Central Net Juan De Fuca Net Juan De Fuca Troll Northwest Van Is Southwest Van Is North Central Troll South Central Troll Fraser Seine Net Net STAT
15. 75 TOTAL 75 75 TOTAL 75 75 Pieces Pieces Pieces Pieces Pieces Pieces Pieces Pieces Pieces Troll STWK 061 0 126 126 991 941 1932 991 1067 062 116 656 772 1738 1111 2849 1854 1767 STK 063 703 375 1078 10626 1672 12298 11329 2057 13376 064 2627 1087 3716 6791 2626 9217 9418 3513 12931 TOTAL 3446 2264 5690 20146 6150 26296 23592 8394 31986 Gillnet STUK 061 0 0 0 0 33 33 0 33 062 0 0 0 95 297 392 95 297 STUK 063 0 0 0 1000 21 3194 1000 2196 STK 064 0 0 0 5926 5564 11490 5926 5564 11490 TOTAL 0 0 0 7021 8088 15109 7021 8088 15109 TOTAL STK 061 0 126 126 991 976 1965 991 1100 STK 062 116 656 772 1833 1408 3241 1949 STK 063 703 375 1078 11626 3866 15492 12329 4241 16570 STUK 064 2627 1087 3716 12717 7990 20707 15344 9077 26421 TOTAL i 3446 2264 5690 27167 14238 41605 30613 16482 47095 The table has increased from 4 columns to 9 and from 8 rows to 15 Totals are shown for both levels of row and column labels For example 2849 fish were caught by troll in area 12 during statistical week 062 for the years 74 and 75 combined while 3241 fish were caught for the same week and same area when troll and gill net gear categories are combined The final feature of the CDSS program to discuss in this overview is the concept of restriction The tables for salmon data shown above were created by restricting the range of the row and column categories For example the years were restricted to just 1974 and 1975
16. ECES DF SHOW WEIGHT DF NUM BOATS DAYS FISHD AVG PRICE LANDED VAL Move Quit T Top B Bottom C Clear all Select all Use ARROWS to move cursor Return Finished selection S Select U UnSelect PF1 Toggle Move S or UJ FIGURE 9 Column Data selection in the example above COL DATA has been selected and the DATA selection box shows choices available for report selection DATA defines the numbers shown in the column s of the spreadsheet as PIECES the actual number of fish caught TOTAL WT weight undressed head on DRESSED WT of fish out of the total landed that were dressed AVG WT weight pieces PIECES DF days fished pieces WEIGHT DF weight days fished NUM BOATS of CFV landings by area by statweek DAYS FISHD Days fished for troll landings for net AVG PRICE Average price by weight LANDED VAL gt Total landed value 28 Weights for troll are dressed with head on and for net are round weight Collection of average weights information from the MRP commercial sampling program enables the estimation of species and gear specific average weights The MRP data is provided in a file by area gear period and species and each record contains the number of fish sampled and average weight per fish This information is supplied by the MRP data entry contractor to Catch Statistics in Vancouver because these values are more accurate than those found in th
17. ENDIX B for catch region summary DISTRICTS District 1 includes statareas 28 29AB 29C 29D 29E District 2 includes areas 1 10 30 and Alaska District 3 includes 11 27 C District 4 includes Nass and Stikine Rivers The PR SPEC menu item The period specification menu allows a choice of reporting data by statistical week statweek or by statistical month DB STAT FILE SHEET AR SPEC PR SPEC MONTH d FIGURE 7 Time Periods The default for time period is statweek because commercial data are reported this way Statweek defines the week as starting on Sunday and ending on Saturday except in 1981 when it began on Monday Months are defined as being four weeks long except for April July and October which have five weeks Please see Appendix A for more information NOTE The program will sum all the data if no restrictions are made i e to get an annual rollup choose year but make no period choices 25 The G_RESTR menu iten In Figure 8 the global restriction menu is shown and gear has been chosen to be defined from the sub menu The global restrictions menu defines the contents of a spreadsheet report Salmon catch by year period area gear and species along with units of weight and weight corrections may each be limited to the user selected values DB_STAT FILE SHEET AR SPEC PR_SPEC G_RESTR COL ROW OUT YEAR PERIOD AREA GEAR SPECIES EAR RESTRICTION UNIT Fr Troll Tot
18. TOTAL will add the totals of a particular level in another row but was not chosen in this example CLEAR removes previous choices and leaves the level unchosen The column choices were PIECES for data and the lST LEVEL was YEAR Sheet 1 84 85 86 87 Pieces Pieces Pieces Pieces Chinook NTR 179665 186723 152999 177457 SWTR 327853 279769 261103 265787 NN 50767 70667 42716 41245 SWVN 48313 21263 4115 612 Chum NTR 61559 146935 50750 62135 SWTR 1349 44929 52510 3123 NN 857189 1019126 626966 371389 SKVN 187117 1616890 387679 395446 FIGURE 11 Example column data amp nested row levels The OUTPUT menu iten The output from CDSS is available in several forms Interactive output of the spreadsheet will appear as a table of rows and columns on the computer screen larger spreadsheet can be viewed by scrolling The cursor control arrow keys will cause movement by one row or column in the direction of the arrow The keys lt u gt lt d gt lt 1 gt and lt r gt will cause the spreadsheet to move by one screen in the up down left and right directions respectively SELECT OUTPUTS DISPLAY CGP SPREADSHEET PRINTED sf Love M Quit T Top B Bottom C Clear all Select all Use ARROWS to move cursor lt Return gt Finished selection S Select U UnSelect PF1 Toggle 5 or UJ FIGURE 12 The OUTPUT menu DISPLAY the spreadsheet will appear on the screen where it may be scr
19. aved reports COM batch file i to be submitted by the user for the VAX When batch jobs are chosen the program submits the report to the chosen queue and these file extensions are created SET setup parameters to re create saved reports LOG when batch jobs are submitted to queue logs detail problems etc C01 custom graphics file S01 spreadsheet file for importing to other programs PRT puts data into simple report format for printing When interactive mode is chosen data will appear on your screen as well as in these file extensions SET setup parameters to re create saved reports 01 custom graphics file 501 spreadsheet file for importing to other programs PRT puts data into simple report format for printing
20. concerned SECTION II DATABASE STRUCTURE Catch Data Systen Records of each sales slip reported in a year are stored on magnetic tape by the Statistics Unit of Fisheries Branch in Vancouver These data are then processed by a series of database programs at PBS The data may be accessed via the menu driven spreadsheet report writer or by user written FORTRAN programs Library routines exist to handle data access for the user written programs Summary catch information by year area time period gear and species number of days open and amount of effort are maintained on the PBS computer The summary catch database and spreadsheet report writer may also be used on an IBM compatible micro computer Annual catch data are usually finalized by October of the year following the reported catch Structure of the Catch Data In order to understand the catch data it is necessary to know something about the salmon fishery Salmon are caught near the coast by commercial fishermen The fishery can be broken into sub units which expedites management of the many separate salmon stocks An individual fishery encompasses both a restricted geographic area and time period Thus to describe the catch requires the definition of this spatial temporal unit which we refer to as the catch stratum This stratum represents the highest resolution of spatial temporal information obtainable from the spreadsheet progran The catch stratum has four components
21. d level in the figure includes the effort box therefore the effort fields contain information appropriate for the selected catch stratum Finally the lowest level contains the catch fields These fields report information that applies to the combined catch stratum and species selections but does not need the effort data to be interpreted properly Stratum fields These fields delineate when and where the fishery occurred Year The year the landings were made The catch spreadsheet program starts with 1952 and includes data up to the most current year of available information Period The time period is a subset of the total group of statistical weeks plus one additional period which includes several statweeks Appendix A Statweeks are written as a three digit number with the first two digits delineating the month and the third digit giving the week within the month Statweeks 031 through 114 are all included individually within the catch database The additional period statweek 120 is defined as all statweeks not included individually above i e statweeks 011 through 024 plus 121 through 124 Prior to 1969 the months of March and April were reported by statistical month Area Gear Data are stored by statarea see Appendix B Note that other area types such as catch region are available Catch regions are defined as a combination of statarea and gear type For some years some statistical areas were divi
22. ded into subareas and data is reported by subarea when available Care should be used when using subareas since they will not necessarily total to statarea Four different gear types are specified troll freezer troll gill net and seine net Day troll boats and ice troll boats are combined in the troll category while freezer troll is segregated into a category by itself Effort fields These fields describe the fishing effort Days Days associated with a catch stratum Open The number of days the fishery was open for fishing This information is not currently available at this time however space for the data has been reserved Fished This field and the next report information related to fishing effort The interpretation of this field is conditional on the gear type Specifically for troll boats the field reports the total number of boat days fished by strata For net gears the field represents the total number of landings by strata These data are only available from 1963 Number of Boats This field represents the number of boats making at least one landing within a statarea statweek and gear stratum Thus it has two possible interpretations one of which is meaningful only for a single catch stratum These interpretations are 1 For troll as a measure of fishing effort the number estimates boat trips This definition is still valid when several strata are combined 2 This field estimates the numbe
23. dressed weight rather than round weight The program compensates for troll landed head off with an algorithm than adjusts to dressed head on This data is available in 1973 and from 1982 on Avg Price The average price paid for the fish Price can be reported in either price per pound or per kilogran at the user s discretion This data is available from 1972 on SECTION III PROGRAM USER S MANUAL A spreadsheet is produced by working interactively with the program and making choices from menus An interpreter accesses the catch database and then generates the output either interactively or in a batch process In one session several spreadsheets may be specified and these specifications may be saved to and restored from disk System Access CD88 can be run either on the VAX at PBS or on a micro computer with sufficient disk space and memory While many micro computers are self sufficient DFO uses them to also emulate a terminal on the VAX where software enables the user to logon directly into the mini computers while still being on the micro computer Details on how to log onto the VAX can be obtained from the Computing Division at PBS To begin the interactive spreadsheet program on the VAX enter 88A C for Salmon Stock Assessment SSA menu Salmon Catch Database being selected C DAFfy gt SSA C 14 Oct 1990 9 53am user Salmon Sunday CATCH DATABASE SPREADSHEET SYSTEM Version 3 3 Copyright 1989 Go
24. e average weight table generally used for editing purposes Independent variables sort levels are year period area gear and species Column contents depend on the data items Sort levels are used in columns to help specify categories of data of interest to the user For example the user may select pieces as the data item and year as a sort level This choice would result in one column per year for the years specified using G_RESTR Each column would have the pieces caught in the corresponding year If no sort levels are chosen i e from 18T_LEVEL or 2ND_LEVEL the spreadsheet columns will become the data variables Each sort level chosen after data variables are chosen multiplies the number of values taken by that independent variable The ROW menu iten Spreadsheet rows are determined by one two or three nested sort levels A spreadsheet without at least one row sort level has no meaning and this will generate an error message when the report is run The number of rows in a spreadsheet are the product of the number of values taken by each sort level variable plus one if summation TOG TOTAL is specified 29 DB STAT FILE SHEET AR SPEC PR SPEC G RESTR COL ROW FIGURE 10 The ROW menu In the example below 18T LEVEL chosen was area while 2ND LBVEL chosen was species Note that the row sorts from the finest detail of data area to the least detail species which is the reverse of most spreadsheet layouts
25. e first distinction we want to make is between the data reported in the table the numbers themselves and the column or row labels a category of information In the Troll 13 example above the data are the actual numbers of boys girls in a specific grade The categories are grade and sex The position of a number in the table indicates that it belongs to the respective row and column categories Thus of the five students in grade 1 four are boys A more complex but more relevant table generated from the CDSS program is shown below 10 12 74 75 74 75 Pieces Pieces Pieces Pieces STWK 061 0 126 991 941 STWK 062 116 656 1738 1111 STWK 063 703 375 10626 1672 STWK 064 2627 1087 6791 2426 Gillnet STWK 061 0 0 0 33 STWK 062 0 0 95 297 STWK 063 0 0 1000 2194 STWK 064 0 0 5926 5564 This table reports the numbers of salmon pieces caught within each week for a four week period over two years in two statistical areas by two types of fishing gear Only one item in this list represents the data displayed in the table the pieces All other items in the list are category labels for either rows or columns Let us first examine the column labels There are three levels of labels for the columns of the table The upper level represents the statistical areas selected by the user for the report areas 10 and 12 The next level represents the years selected by the user 1974 and 1975 The final level of
26. ear choices are troll or freezer troll seine and gillnet Chinook includes the totals of the other chinook grades Kilograms default or pounds For troll the standard weight is dressed head on This option will convert troll to round weight which is handy for comparing the total tonnage between troll and net Default is weight not converted to round weight for troll N There is a connection between gear and area when area is chosen as CATCH REGION Catch region by its definition describes a geographical area and gear Catch regions are used by MRP for sampling purposes If catch region Northern Net NN were chosen as the area and troll as the gear a spreadsheet with no data would be produced as NN is by definition an area for net gears 27 The COL menu item COL or column defines the vertical aspect of the spreadsheet Colum divides its information between dependent and independent variables Dependent variables are data and independent variables are sort levels Data dependent variables are pieces total weight percent dressed weight average weight pieces per day fished weight per day fished number of boats days fished average price and landed value as you can see from Figure 9 Some of the dependent quantities listed i e average weights are calculated DB STAT FILE SHEET AR SPEC PR SPEC G_RESTR COL ROW OUTPUT EXEC DATA lst LEVE DATA SELECTION 2nd LEVE PIECES TOTAL WT SDRESS WT AVG WT PI
27. for details BE_USER for use by SSA group only Consistency Checks and Limitations When the GO sub menu item is selected under EXECUTE then a set of consistency checks is done on the spreadsheet specifications which the user has established This is done sheet by sheet and the user is prompted for instructions to continue after each sheet has been checked If a problem is found i e no DATA selection was made or the same sort level was made in both COL and ROW a warning or error condition is generated When all sheets in the set have been checked the program checks for warning or error conditions and will display a prompt on whether or not to proceed with processing Usually the user would choose not to proceed until the problem has been corrected Users who discover combinations of specifications which result in erroneous spreadsheets but for which the program issued no warning or error messages should report them to the system maintenance person so the program can be modified appropriately The QUIT menu iten To exit the spreadsheet select QUIT and reply yes 32 ACKNOWLEDGEMENTS I would like to thank Dr T Mulligan for providing very helpful editorial advice along with perspectives from Brian Moore Don Radford Gail Hudson Terry Calvin and Sue Lehman REFERENCES Burton G L 1949 Fishery statistics of the Pacific coast Canada a report to the Dominion Department of Fisheries and the Dominion Bureau of
28. hese are the file extensions created when the job is submitted to batch SET setup parameters to re create saved reports FIL created when batch file is chosen BAT command file to be submitted to batch micro APPENDIX D The report below can be replicated by following the directions The directions will follow the procedures used on the VAX 1987 CHINOOK DATA FOR AREAS 1 5 Sheet 1 Pieces Total Wgt Dressed Avg Weight Troll 1 57989 478141 100 0 8 2 2E 7942 54575 100 0 6 9 2W 38168 329823 100 0 8 6 3 3847 26950 100 0 7 0 4 2768 16179 100 0 5 8 5 3366 22134 100 0 6 6 Seine 1 7528 49285 62 1 6 5 2E 5 20 26 7 4 1 2W 1400 5808 100 0 4 1 3 18153 73686 32 4 el 4 2148 11894 42 1 5 5 5 976 5098 19 3 Boz Gillnet 1 428 4464 99 5 10 4 2E 8 43 18 1 5 3 2W 0 0 3 1370 6337 57 2 4 6 4 9055 59129 66 3 6 5 5 174 738 46 0 4 2 FIGURE 15 Report Example 1 SSAC 2 The introductory screen comes up and hit lt Enter gt 3 The AR SPEC is set at STATS AREA SUBS default 4 The PR SPEC is set at STATS PERIOD default 5 Goto G RESTR menu and hit Enter Cursor goes to Year Hit Enter Year sub menu comes up Choose 1987 using the arrow keys On 1987 hit S Select Hit Enter 6 Choose Areas by hitting A or by using the arrow keys down to Area Hit S Use arrows over to 5 Note highlighted selection which indicate the choices If you go over too far i e to 6
29. includes a choice of screen display a printable report a standard spreadsheet file and a file formatted for input to the PBS Custom Graphics Package Kuhn Whitfield and Chumak 1988 Since most users will use the program through the Salmon Stock Assessment SSA menu on the computer at the Pacific Biological Station PBS procedures for using the program will be focused on this computer VAX When procedure differs between the VAX and micro computer versions of the program these differences will be noted in Appendix C Section I covers background details on the history of sales slip data and the compilation of the PBS catch database Section II explains the structure of the data in the database while Section III describes the program with various menus used to format a report SECTION I GENERAL OVERVIEW Historical Perspective As the B C fishing industry developed so did fisheries regulations and management problems It was recognized in the late 1940 s that a more accurate and detailed accounting of fishing catch statistics was needed Consequently after consultation between government officials industry representatives and biologists a report was issued on a multiple sales slip system Burton 1948 This system was acknowledged to be the best method of providing complete and detailed information on the catch A trial sales slip system was instigated for the Nass and Skeena River areas and its success led to the extension of
30. information STRATUM 1 Year 2 Area 3 Time 4 Gear Period EFFORT SPECIES 5 Days Open 6 Days 7 Number 8 Species Fished of Boats grade CATCH 9 Pieces 10 Total 11 Dressed 12 Average Weight Price FIGURE 1 Logical Structure of Catch System The logical relationships of the data fields in the database are graphically shown in Fig 1 In the figure the 12 separate data fields have been combined into four groups of related information For example the top box in the figure contains the four data fields that together define the catch stratum Fields which are numbered and listed within a box are independent of one another Thus the year and area fields both help define the catch stratum and are independent of each other catch stratum is defined by choosing a single value for year area period and gear i e 1984 statarea 1 statweek 061 and seine net If more than one value is chosen for any of these four fields for example the statareas 1 amp 2 then there is no longer a single catch stratum but in this example two catch strata which could be summed together or reported individually depending on the report choices The two boxes on the second level rely on the additional information above them to be interpreted properly This hierarchial relationship is depicted graphically by the vertical lines connecting the boxes For example the secon
31. m Title 1987 CHINOOK DATA FOR AREAS 1 5 Area spec STATS AREA SUBS Period spec STATS_PERIOD Selected years 87 Selected areas 1 2E 2W 3 4 5 No period restriction Selected gear Troll Seine Gillnet Selected species Chinook Selected data Pieces Total Wgt Dressed Avg Weight Weight unit Kg Weight not corrected for dressed Cols 1 YEAR N 2 none N Rows 1 AREA 2 GEAR 3 none Output DISPLAY PRINTED FIGURE 16 Sample of SHOW for Report Example 13 14 T Om Before running the program save the file into a unique filename Go back to the main menu and choose FILE Choose FILENAME and delete setfile with the backspace key Enter a unique name Go to SAVE and hit Enter Save will have your unique name already installed Enter saves it under that name Back to the main menu and choose EXEC To use the DISPLAY feature of the OUTPUT menu INTERACTIVE is the choice as well as being the default Choose GO d ue APPENDIX E The following extensions are used on filenames generated by the system under the default setfile unless the user saves the report as a unique filename Multiple report extensions of the same filename with the exception of SET will be saved on the VAX The spreadsheet program keeps only one copy of yourfile SET On the VAX when all output options are chosen and a create job file option is chosen the following extensions are saved SET setup parameters to re create s
32. nested The OUTPUT menu woe ed Northern British Columbia Statarea Map Southern British Columbia Statarea Map Report Example Screen Sample of SHOW for Report Example levels a o ww dw W x Vi ABSTRACT Holmes M A and D W A Whitfield 1991 User s Manual for the Commercial Salmon Catch Spreadsheet Program Can Tech Rep Fish Aquat Sci 1807 44 p Summary information from the commercial salmon catch sales slip data are stored for the years 1952 to the present on the VAX computer at the Pacific Biological Station in Nanaimo B C The summary data contains information on the salmon catch by year area time period gear type and species Additional information on fishing effort is also included A spreadsheet program has been written to allow users to generate a wide variety of report tables for their own use both on the VAX or on a personal computer This manual describes how to use the spreadsheet program to obtain the information needed Keywords commercial salmon catch fishing effort sales slip salmon database salmonid spreadsheet program R SUM Holmes M A and D W A Whitfield 1991 User s Manual for the Commercial Salmon Catch Spreadsheet Program Can Tech Rep Fish Aquat Sci 1807 44 p Des renseignements sommaires fond s sur les donn es provenant des bordereaux de vente des prises de saumon commerciales sont stock s pour la p
33. olled for viewing PRINTED This choice is the default output method The spreadsheet will be written to a file for subsequent printing on a 132 column printer Spreadsheets too large for one printed page will be split over as many pages as necessary CGP creates a data set that can be imported into the Custom Graphics Package by B Kuhn Can Tech Report No 1659 The program contains a stand alone program specifically designed to interface the the MRP Reporter program 8PRBADSHBET will generate files which are formatted for input to a standard spreadsheet The EXEC menu item The execute menu offers a choice of interactive or batch processing The default is interactive which processes the 31 spreadsheet as you wait Jobs can also be sent to the batch queue on the VAX which will free up your terminal to do other work GO submits job to batch queue on the VAX or to interactive screen depending on which choice has been made in DEVICE DEVICE defines how the job will be run Short CPU time of 3 minutes or less batch queue 8QB long batch queue more than 3 minustes CPU time to run the interactive screen will run immediately from the screen at the GO command or CREATE_JOB_FILE which creates a batch file COM extension to be submitted to any queve at another time Several different file extensions are created depending on what output files are chosen and also on how the job is run See Appendix E
34. orts on the current status of information in the database File specification Lets the user name save retrieve and view CDSS files Spreadsheet name Allows more than one spreadsheet to be constructed during a single session of program use Program output Lets the user decide what type of output he wants Execute Instructs the program to execute the commands built up by the user to produce the spreadsheet Quit the program Returns control back to the operating system We shall now go through each of these menu items in detail and describe all of the relevant features The DB_STAT menu item For the entire database the status is 1 the latest year of data available 2 the number of years of preliminary data 3 the years for which days open data are available 99 not available 4 descriptive text on data The FILE menu item This submenu provides information on the files in the current directory and allows the retrieval or storage of spreadsheet specifications files with the extension of 8ET DB STAT FILE SHEET SPEC FIGURE 4 The FILE menu PATH this allows a path or directory to be changed from the default directory in which the user is working to other directories FILENAME the default file name is SETFILE and extension choices are described in Appendix E A unique filename can be chosen here LIST allows listing of files in the current directory defined by PATH
35. prompts for the number of a sheet which then becomes the current sheet This facilitates moving around a report with many sheets COPY prompts for the sheet number where contents are to be copied to the current sheet i e If the user has made the first sheet report then creates a new spreadsheet with NEW COPY allows information already entered for one sheet to be copied to another sheet This is very useful when many aspects of each sheet will contain similar information DELETB removes a sheet from the set The AR_SPEC menu item 23 The data are stored in the database in terms of The other area definitions statareas including subareas represent combinations of statareas definitions are summarized below 1 8 16 24 Alaska Taku Dp 16 29E DB STAT FILE SHEET 8 STATS AREA SUBS STATS AREA SUBS DISTRICT DIST STATS SUBS DIST STATS SUBS CATCH REGION CR STATS SUBS CR STATS SUBS FIGURE 6 The STATS AREA 8UB the default 2E 2W 9 10 17 18 25 26 STATS AREA SUBS 2AE 2BE 5 6 5 1 9 10 17 18 25 26 30 c 3 11 19 27 Stikine Unknown 2AW 5 2 11 19 27 Alaska 4 12 20 28 BC 2BW 5 3 12 20 28 Taku AREA menu 5 6 13 14 21 22 29 30 3X 3Y 5 4 6 13 14 21 22 29AB 29C The different area 32 7 15 23 29 Stikine Unknown 24 CATCH REGIONS those geographical areas combing with gear Please see APP
36. r of boats participating in the fishery represented by the stratum This definition may not be valid when several strata are combined because double counting of boats can occur There is no number of boats data from 1952 1967 in the database Species field This field selects the species within the catch stratum Species The six species of Pacific salmon chinook coho chum pink steelhead and sockeye are all included In addition chinook grade types are included as though they also represented separate species These grade types are large red gt 12 lbs medium red 8 12 lbs small red 1 7 lbs 2 red lt 7 lbs whites and jacks lt 5 lbs Selecting large red results ina summary of all red chinook over 12 pounds The choice of chinook gives the totals for all chinook grades Note that the grade types apply only to chinook Catch fields These fields describe the catch within the selected catch stratum and species composition Pieces The number of fish landed Total Weight The total weight of landed fish Total weight can be reported in either pounds or kilograms at the user s discretion This field is conditional on gear type since the different gear types use different weight standards Troll fish weight is reported for fish dressed with head on whereas for net caught fish the standard assumed is for round weight head on undressed Dressed The percentage of landings landed as
37. rally the procedures for running the catch spreadsheet program on the VAX will work also for the micro computer Some of the differences are as follows The program limits the total number of columns and the integrity checking mechanism enforces that limit On the micro the user is allowed 50 columns and 106 rows The user may interrupt program execution by using the Ctrl C and lt Ctrl Y gt keys For the former to be effective the MS DOS command BREAK ON should be issued before starting the program Then lt Ctrl C gt will return the user to the DOS command level and Ctrl Y will result in a choice to quit or continue execution c M s NOTE Unlike the VAX micro computers will not accumulate more that one version of a filename It is good policy to assign unique filenames for each of the spreadsheet reports created On the micro each subsequent report created without changing and saving filename as a unique name will write the current report into that same filename i e SETFILE On the micro processing is limited to interactive jobs which also produce a printable report However the user can create batch files for use to import to the VAX GO submits job to run on the interactive screen or creates a batch file to run later It initiates data processing and integrity checking followed by calculation of the appropriate data DEVICE a batch file is created to run after exiting the interactive program T
38. riode allant de 1952 aujourd hui dans l ordinateur VAX de la Station biologique du Pacifique Nanaimo en Colombie Britannique Ces donn es sommaires r partissent les captures de saumon par ann e secteur g ographique p riode de l ann e genre d engin de p che et esp ce On y trouve aussi d autres informations sur l effort de p che Un logiciel tableur a t con u pour permettre aux utilisateurs de produire un large ventail de tableaux qu ils peuvent utiliser sur le syst me VAX ou sur un ordinateur personnel Le guide indique comment utiliser le tableur pour obtenir les renseignements souhait s Mots cl s prises de saumon commerciales effort de p che borderau de vente base de donn es sur le saumon salmonid s et tableur FORWARD This document introduces the new user to the salmon catch data spreadsheet program developed by Doug Whitfield and Tony Chumak for the Dept of Fisheries and Oceans at the Pacific Biological Station under the Scientific Authority of L Lapi The commercial salmon catch data system described by Fred Wong Wong 1983 contains detailed information on the history of the catch data and the intricacies of its structure The current report repeats some material because of procedural updates and in the interest of background continuity INTRODUCTION The system described here accesses the catch database Wong 1983 and produces flexible reports in a generalized spreadsheet form Output
39. statweek MMW A table for any year can be generated by running run ssa CWTSYS TABLES MMWTAB Running this program will produce statweeks for all periods from January but CDSS uses dates from March lst through Nov 30th ENDING DAY MON YR STATWEEK 08 03 75 031 15 03 75 032 22 03 75 033 29 03 75 034 05 04 75 041 12 04 75 042 19 04 75 043 26 04 75 044 03 05 75 045 10 05 75 051 17 05 75 052 24 05 75 053 31 05 75 054 07 06 75 061 14 06 75 062 21 06 75 063 28 06 75 064 05 07 75 071 12 07 75 072 19 07 75 073 26 07 75 074 02 08 75 075 09 08 16 08 23 08 30 08 06 09 13 09 20 09 27 09 04 10 11 10 18 10 25 10 01 11 08 11 15 11 22 11 29 11 DEC JAN FEB 34 APPENDIX B LULA 5 Canada STATISTICAL AREA MAP SHOWING AREAS OF CATCH FOR BRITISH COLUMBIA WATERS NORTHERN HALF B cr ae Ro 7 EE 2 CAPE 9COTT V c3 127 e rd VANCOUVER ISLAND 22 SIXTH EDITION 1985 FIGURE 13 Northern British Columbia Statarea Map B Fisheries P ches and Oceans et Oc ans STATISTICAL AREA MAP SHOWING AREAS OF CATCH FOR BRITISH COLUMBIA WATERS SOUTHERN HALF ame INSERT MAP FRASER RIVER SIXTH EDITION FEB 1985 Southern British Columbia Statarea Map FIGURE 14 The statistical area maps for British Columbia shown the preceding pages shows how the areas are divided for various Statistical areas are combined
40. te the arrows move along a line but not down unless you press the down arrow gt lt move within string lt Ctrl B gt move to beginning of string lt Ctrl E gt move to end of string lt Ctrl G gt delete character to right of the cursor lt Ctrl H gt or delete character to the left of lt Delete gt the cursor lt Ctrl D gt delete to end of line ctrl I toggle insert overstrike mode ctrl Y abort will work at a logical point in the program i e between sheets while Ctrl C will return to the VAX prompt immediately Note is used to step back out of the sub menu However Return or Enter are used when a choice has been selected If these are not used the program cannot handle the unexpected response and it will abort The CD88 menu 18 First let us take a look at the types of operations the user performs by using the CDSS menu The following three menu items determine the contents and layout of a spreadsheet report G_RESTR ROW Global restrictions This menu item lets the user choose the appropriate subset of information in which he is interested The data fields that can be restricted are all four stratum fields year area period and gear and the species field For example the user might be interested only in the years 1964 and 1982 He would then select only these years using the restriction procedures described below Column definition There are
41. this system in 1951 to the remainder of the province In 1967 the Block Brothers Computing Centre in Vancouver BB began computer processing and storing the catch data for DFO Two reports were published annually but much of the data was still unavailable for users who could not access the data in a timely flexible or inexpensive manner In 1981 PBS purchased its first multi user computer and the present salmon catch database was designed using the BB catch tapes Fred Wong developed the programs and subroutines necessary for the efficient storage and retrieval of summary sales slip information Wong 1983 Catch information on tapes was sent from the Catch Statistics Division of Fisheries Branch to the Salmon Information unit of the Biological Science Branch where the information was assembled and stored on the PBS computer The original reports that users could access on the VAX have now been replaced by the spreadsheet program which allows greater variety in the content of the reports Sales slips The data collection mechanism is via the sales slip Each time a commercial catch is sold a sales slips is completed and a copy sent to Department of Fisheries and Oceans DFO Information on the sales slip which becomes the basis of the salmon catch database is sales slip number gear used to catch the fish date of sale or landing company purchasing the fish number of days fished and statistical area of catch species
42. two types of choices for definition of the column entries The first is the type of data to be shown in the columns of the report The data items are all from the catch fields of the database The second choice is referred to as sort level and determines the label categories for the data For example in the overview the data selection was pieces while the label categories were years and areas Label categories all come from the stratum and species fields Row definition The row definition is similar to the column definition with the exception that no data selection is involved Therefore different rows of the table correspond to different label categories In the overview example the row label categories were gear and period Row labels also come from either the stratum or species fields of the database 19 The next two menu items let the user specify the units of measurement defining the area and time period AR SPBC PR SPBC Area specification The user chooses from the various types of areas the program supports For example he may choose statistical areas or catch regions Period specification The user chooses either statistical weeks or statistical months as the time period units The last menu items select I O options report on the current status of the database execute the program commands or return to the operating system DB STAT FILE 8HEET OUTPUT EXEC QUIT Database status Rep
43. vernment of Canada Department of Fisheries and Oceans Press any key to continue FIGURE 2 Catch Spreadsheet Identification Screen The introductory screen provides information on copyright date and time of report production Figure 2 The main menu For the rest of the interactive session the main display window contains the main menu at the top and a status line at the bottom Figure 3 Each menu choice will be explained in detail 11 DB STAT FILE SHEET AR SPEC PR SPEC G RESTR COL ROW OUTPUT EXEC QUIT F2 Help F4 Redraw Screen Sheet 1 of 1 Path CATCH CODE File setfile FIGURE 3 The main menu Note The order of menu items is not the order in which a report is created i e DB STAT is the first item on the menu but it only gives information on the database while G RESTR is used to format the contents of the report The status line The status line at the bottom of the main menu screen gives information on help keys directory or path filename and spreadsheet number It provides information on help PF2 and on refreshing the screen PF4 as well as the name of the report default file name is setfile where the report is being created Path USERNAME and which report is currently being worked on Sheet 1 of 1 All of these items will be discussed in the following sections 12 USER INTERFACE This section describes the interactive program and how to produce a report Read through
Download Pdf Manuals
Related Search