Step-by-Step Guide to Statistical Catch-at-Age Models in Excel

A comprehensive guide by Einar Hjӕrleifsson on building statistical catch-at-age models in Excel. The tutorial covers setting up the model, disentangling mathematical formulations, and utilizing Solver for optimization. Excel's graphical display and integration with Solver make it an ideal tool for learning and testing such models. The approach focuses on understanding through practice, enabling users to create separable models with fixed selection patterns. Various elements including fishing mortality, stock size, and survey indices are incorporated to minimize differences between observed and predicted values.

• Statistical modeling
• Excel guide
• Fishery management
• Solver optimization
• Separable models

kduha Follow

Uploaded on Sep 27, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. UALG Statistical catch at age models: Step by step guide in Excel Einar Hj rleifsson

2. A short comment first 2 einar For line code people Excel may be cumbersome, inefficient and (for some) confusing. However wide availability, almost daily usage, integrated instant graphical display and the existence of Solver make it an ideal teaching and testing medium. The model coding we do here is probably the only time most of us will ever go in this field. The intent with doing it at least once is to disentangle the mathematical formulations that are the basis for statistical catch at age models.

3. The model in math 3 einar ( ) 2 a a full for e a a L = F s F = full s ay a y ( ) a 2 a a full for e a a R full R = = , a y 1 or a = 1 a y + ( ) F M = N N e 1, 1 1, 1 a y a y 1 a , 1, 1 a y a y plus = + + ( ) ( ) F M F M + N e N e 11 a a 1, 1 1, 1 , 1 , 1 a y a y a y a y 1, 1 , 1 a y a y plus ( 1 ) F C + ( ) F M , a y M = e N , a y ay ay ay + F , a y ay ( ) 1 S + p F M = = 1 1 1 1 S ay S a S ay S ay where U q N N N e ay ay ay ( ) 2 S + p F M = = 2 2 2 2 S ay S a S ay S ay where U q N N N e ay ay ay ( ) ( ) ( ) 2 2 2 = + + 1 1 1 2 2 2 2 S a S ay S ay S S a S ay S ay ln ln ln ln ln ln SSE C C U U U U min 1 C a ay ay S y a y a y a

4. Can you disentangle this? 4 einar

5. The approach 5 einar I hear - I forget I see - I remember I do - I understand

6. The model in words 6 einar Make a separable model having: A fixed (constant through time) selection pattern (sa) for each age, assume selection pattern follows double-half Gaussian Fixed selectivity with time is commonly referred to as a separable model. Fishing mortality (for some reference age) for each year (Fy) Numbers of fish that enter the stock each year (year class size, recruitment, N1,y) and in the first year (Na,1) A plus group: Catches of the oldest age groups are summed - Needs to be taken into account in the model Calculate: The number of fish caught each year and age by the fishermen (Cay-hat). This is the modeled Cay number. The number of fish caught each year and age by the scientist (Uay-hat). This is the modeled Uay number. Assume the relationship between stock size and survey indices as: Uay = qN Set up an objective function: Constrain the model such that we minimize the squared difference between observed values (Cay and Uay) and predicted values (Cay-hat and Uay-hat)

7. The model as a map 7 einar Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V Population and observation model 2. Selection pattern 3. Fishing mortality Natural mortality 9. Solver Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V 4a. Population numbers 4b. Nay at survey 1 time 4c. Nay at survey 2 time Year Age -----> | | V Year Age -----> | | V indices Year Age -----> | | V indices 5. Predicted catch 6a. Predicted survey 1 6b. Predicted survey 2 1. Parameters Measurements Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V Observed catch at age Observed survey 1 indices Observed survey 2 indices Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V 7. Catch residuals 8a. Survey 1 residuals 8b. Survey 2 residuals Objective functions Color code Population model Observation model Measurements | Objectives Parameters Year Age -----> 7. Catch residuals squared Year Age -----> | | V squared Year Age -----> | | V squared 8a. Survey 1 residuals 8b. Survey 2 residuals | V

8. The separable part 8 einar Selectivity describes the relative fishing mortality within each age group. In this simplest model setup we assume that the selectivity is the same in all years. Fishing mortality by age and year can thus be described by: = F s F ay a y Fay: Fishing mortality of age a year y sa: Selectivity of age a Fy: Fishing mortality (of some reference age) in year y Note: The separability assumption reduces the number fishing mortality parameters from: n = (#age groups x #years) to n = (#age groups + #years)

9. Assume double half-Gaussian 9 einar Lets make a further assumption here by letting selectivity follow: ( ) 2 a a full for e a a L = full s ( ) a 2 a a full for e a a R full afull: age at full selectivity R: Shape factor (standard deviation) for right hand curve L: Shape factor (standard deviation) for left side of curve Note: by using this selection function we reduce the number of parameters from whatever number of age groups we have, to only 3 parameters. But could just as well just estimate each Sa without resorting to a particular function. The R, L and Afull are parameters that we estimate

10. Selectivity - double half-Gaussian 10 einar Note asymmetry 1.0 L=5, 10, 15 0.9 0.8 0.7 Selectivity afull = 8 0.6 0.5 0.4 0.3 0.2 R=10000, 100, 15 0.1 0.0 0 5 10 Age

11. The map 11 einar Year Age -----> | | V Calculate sa here Year Age -----> | | V Year Age -----> | | V 2. Selection pattern 3. Fishing mortality Natural mortality 9. Solver Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V 4a. Population numbers 4b. Nay at survey 1 time 4c. Nay at survey 2 time Store Afull, L and R here Year Age -----> | | V Year Age -----> | | V indices Year Age -----> | | V indices 5. Predicted catch 6a. Predicted survey 1 6b. Predicted survey 2 1. Parameters Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V Observed catch at age Observed survey 1 indices Observed survey 2 indices Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V 7. Catch residuals 8a. Survey 1 residuals 8b. Survey 2 residuals Color code Population model Observation model Measurements | Objectives Parameters Year Age -----> 7. Catch residuals squared Year Age -----> | | V squared Year Age -----> | | V squared 8a. Survey 1 residuals 8b. Survey 2 residuals | V

12. The map: say details 12 einar Estimated parameters Param. L R afull Fy .. .. Fy+8 Nay Nay+1 Nay+2 Nay+3 Nay+4 ... y/a y y+1 y+2 y+3 y+4 y+5 y+6 y+7 y+8 a Sa Sa Sa Sa Sa Sa Sa Sa Sa a+1 Sa+1 Sa+1 Sa+1 Sa+1 Sa+1 Sa+1 Sa+1 Sa+1 Sa+1 a+2 Sa+2 Sa+2 Sa+2 Sa+2 Sa+2 Sa+2 Sa+2 Sa+2 Sa+2 a+3 Sa+3 Sa+3 Sa+3 Sa+3 Sa+3 Sa+3 Sa+3 Sa+3 Sa+3 a+4 Sa+4 Sa+4 Sa+4 Sa+4 Sa+4 Sa+4 Sa+4 Sa+4 Sa+4 a+5 Sa+5 Sa+5 Sa+5 Sa+5 Sa+5 Sa+5 Sa+5 Sa+5 Sa+5 ( ) 2 a a full for e a a L = full s ( ) a 2 a a full for e a a R full Excel speak: =exp(-((a-afull)^2/if(a=<afull;sL;sR)))

13. Side step A word on nomenclature 13 einar Often make the following distinction: Selectivity: The probability of catching an individual of a given age scaled to the maximum probability over all ages, given that all animals are available to be caught by a certain gear in a certain plaice. This is what gear technologist study at lengths! when they are studying the properties of various gears. Availability: The relative probability, as a function of age, of being in the area in which catching occurs. Vulnerability: The combination of selectivity and availability. Thus should really refer to vulnerability but lets stick with the more ambiguous word selectivity, the reason being its wide usage.

14. Setting up Fy and calculating Fay 14 einar The fishing mortality each year (Fy) are parameters of the model that we want to estimate. Since we already calculated sa we can calculate fishing mortality by age and year from: Fay = saFy

15. The map 15 einar Year Age -----> | | V Year Age -----> | | V Calculate Fay here Year Age -----> | | V 2. Selection pattern 3. Fishing mortality Natural mortality 9. Solver Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V 4a. Population numbers 4b. Nay at survey 1 time 4c. Nay at survey 2 time Year Age -----> | | V Year Age -----> | | V indices Year Age -----> | | V indices 5. Predicted catch 6a. Predicted survey 1 6b. Predicted survey 2 Store Fy here 1. Parameters Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V Observed catch at age Observed survey 1 indices Observed survey 2 indices Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V 7. Catch residuals 8a. Survey 1 residuals 8b. Survey 2 residuals Color code Population model Observation model Measurements | Objectives Parameters Year Age -----> 7. Catch residuals squared Year Age -----> | | V squared Year Age -----> | | V squared 8a. Survey 1 residuals 8b. Survey 2 residuals | V

16. Setting up Ninit and calculating Nay 16 einar The number of fish entering the system in first year and in the first age (Ninit) are parameters of the model that we want to estimate. Need: The number of fish in each age group in the first year (Na,1) The number of recruits entering each year (N1,y) Given the above we can then fill in the abundance matrix by the conventional stock equation + ( ) F M += N N e ay ay + 1, 1 a y ay

17. The map 17 einar Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V 2. Selection pattern 3. Fishing mortality Natural mortality 9. Solver Year Age -----> | | V Calculate Nay here Year Age -----> | | V Year Age -----> | | V 4a. Population numbers 4b. Nay at survey 1 time 4c. Nay at survey 2 time Year Age -----> | | V Year Age -----> | | V indices Year Age -----> | | V indices 5. Predicted catch 6a. Predicted survey 1 6b. Predicted survey 2 Store Ninit here 1. Parameters Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V Observed catch at age Observed survey 1 indices Observed survey 2 indices Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V 7. Catch residuals 8a. Survey 1 residuals 8b. Survey 2 residuals Color code Population model Observation model Measurements | Objectives Parameters Year Age -----> 7. Catch residuals squared Year Age -----> | | V squared Year Age -----> | | V squared 8a. Survey 1 residuals 8b. Survey 2 residuals | V

18. The map: Nay details 18 einar Green area: Estimated parameters Param. L R afull Fy .. .. Fy+8 Nay Nay+1 Nay+2 Nay+3 Nay+4 ... y/a y y+1 y+2 y+3 y+4 y+5 y+6 y+7 y+8 a a+1 Na+1,y Na+1,y+1 Na+1,y+2 Na+1,y+3 Na+1,y+4 Na+1,y+5 Na+1,y+6 Na+1,y+7 Na+1,y+8 a+2 Na+2,y Na+2,y+1 Na+2,y+2 Na+2,y+3 Na+2,y+4 Na+2,y+5 Na+2,y+6 Na+2,y+7 Na+2,y+8 a+3 Na+3,y Na+3,y+1 Na+3,y+2 Na+3,y+3 Na+3,y+4 Na+3,y+5 Na+3,y+6 Na+3,y+7 Na+3,y+8 a+4 Na+4,y Na+4,y+1 Na+4,y+2 Na+4,y+3 Na+4,y+4 Na+4,y+5 Na+4,y+6 Na+4,y+7 Na+4,y+8 a+5 Na+5,y Na+5,y+1 Na+5,y+2 Na+5,y+3 Na+5,y+4 Na+5,y+5 Na+5,y+6 Na+5,y+7 Na+5,y+8 Na,y Na,y+1 Na,y+2 Na,y+3 Na,y+4 Na,y+5 Na,y+6 Na,y+7 Na,y+8 + ( ) F M += N N e ay ay + 1, 1 a y ay

19. The map: N-plus group details 19 einar Param. L R a50 Fy Fy+1 .. Fy+8 Nay Nay+1 Nay+2 Nay+3 Nay+4 ... y/a y y+1 y+2 y+3 y+4 y+5 y+6 y+7 y+8 a a+1 Na+1,y Na+1,y+1 Na+1,y+2 Na+1,y+3 Na+1,y+4 Na+1,y+5 Na+1,y+6 Na+1,y+7 Na+1,y+8 a+2 Na+2,y Na+2,y+1 Na+2,y+2 Na+2,y+3 Na+2,y+4 Na+2,y+5 Na+2,y+6 Na+2,y+7 Na+2,y+8 .. a+10 Na+10,y Na+10,y+1 Na+10,y+2 Na+10,y+3 Na+10,y+4 Na+10,y+5 Na+10,y+6 Na+10,y+7 Na+10,y+8 a-plus Na+,y Na+,y+1 Na+,y+2 Na+,y+3 Na+,y+4 Na+,y+5 Na+,y+6 Na+,y+7 Na+,y+8 Na,y Na,y+1 Na,y+2 Na,y+3 Na,y+4 Na,y+5 Na,y+6 Na,y+7 Na,y+8 + + ( ) ( ) F M F M = + N N e N e 1, 1 1, 1 , 1 , 1 a y a y a y a y + + , 1, 1 , 1 a y a y a y

20. Side step Note on parameters 20 einar What is Na+5,y+8? + ( ) s F M + + 4 7 = a y N N e , 5 + + , 4 + + 8 7 a y a y + + ( ) ( ) s F M s F M + + + + 3 6 4 7 = a y a y N e e , 3 + + 6 a y + + + ( ) ( ) ( ) s F M s F M s F M + + + + + + 2 5 3 6 4 7 = a y a y a y N e e e , 2 + + 5 a y + + + + ( ) ( ) ( ) ( ) s F M s F M s F M s F M + + + + + + + + 1 4 2 5 3 6 4 7 = a y a y a y a y N e e e e , 1 + + 4 a y + + + + + ( ) ( ) ( ) ( ) ( ) s F M s F M s F M s F M s F M + + + + + + + + + 3 1 4 2 5 3 6 4 7 = a y a y a y a y a y N e e e e e + , 3 a y Estimated parameters I.e.: Stock size (Nay) for each year and age is only a function of recruitment and cumulative mortality. Thus Na+5,y+8 is NOT a parameters. One could actually say that the model never sees this value, only the catches: Ca+5,y+8

21. Estimating catch Cay-hat 21 einar Once the population matrix is calculated it is simple to calculate the predicted catch (Cay-hat) according to the catch equation: ( ay y a M F , + ) F C + ( ) F M , a y = 1 e N , a y ay ay ay The C-hats are values that we will later confront with the measurements that we have.

22. The map 22 einar Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V 2. Selection pattern 3. Fishing mortality Natural mortality 9. Solver Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V 4a. Population numbers 4b. Nay at survey 1 time 4c. Nay at survey 2 time Year Age -----> | | V Calculate Cay-hat Year Age -----> | | V indices Year Age -----> | | V indices 5. Predicted catch 6a. Predicted survey 1 6b. Predicted survey 2 1. Parameters Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V Observed catch at age Observed survey 1 indices Observed survey 2 indices Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V 7. Catch residuals 8a. Survey 1 residuals 8b. Survey 2 residuals Color code Population model Observation model Measurements | Objectives Parameters Year Age -----> 7. Catch residuals squared Year Age -----> | | V squared Year Age -----> | | V squared 8a. Survey 1 residuals 8b. Survey 2 residuals | V

23. Confronting the model with data 23 einar Until now we have only set up equations that follow the progression of each year class and calculated catch. This is more or less a population simulator. If we let recruitment be a function of SSB and we add some noise to recruitment we have a closed system and thus almost a medium/long term simulator This is also more or less the same thing as we do when we do a short term projection. Or for that matter in a yield per recruit analysis, except that there we focus only on one cohort (here one diagonal line). At present we are only interested in fitting the model to observations (measurements). Need thus some kind of objective function.

24. The objective function in words 24 einar Find the value of the parameters: fishing pattern by age (controlled by L, R and Afull) yearly fishing mortality (Fy) population number in the first year (Na,1) recruitment (N1,y) in each year that minimize the squared deviation of estimated catch (Cay-hat) and measured catch (Cay). = y a ( ) 2 ln ln SSE C C min C ay ay Note that here we assume a log-normal error distribution. Could easily be replace with other type of error structure.

25. The map 25 einar Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V 2. Selection pattern 3. Fishing mortality Natural mortality 9. Solver The sum Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V 4a. Population numbers 4b. Nay at survey 1 time 4c. Nay at survey 2 time Year Age -----> | | V Year Age -----> | | V indices Year Age -----> | | V indices 5. Predicted catch 6a. Predicted survey 1 6b. Predicted survey 2 1. Parameters Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V Observed catch at age Observed survey 1 indices Observed survey 2 indices Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V 7. Catch residuals The residuals 8a. Survey 1 residuals 8b. Survey 2 residuals Color code Population model Observation model Measurements | Objectives Parameters Year Age -----> 7. Catch residuals squared The residuals squared Year Age -----> | | V squared Year Age -----> | | V squared 8a. Survey 1 residuals 8b. Survey 2 residuals | V

26. Different weights by age 26 einar The catch of different age groups are often measured with different accuracy. Thus often set different weights to the residuals, so that the information from age groups that are measured with the most accuracy weigh more in the objective function: ( ) 2 y = ln ln SSE C C min C a ay ay a More on later: it is inversely related to the variance

27. If we only have Cay 27 einar If there are no other available data for a stock than catch at age one could attempt to fit the model to catches alone. May need a extra stabilizer : The brave one may assume that fishing mortality does not change much between consecutive years: = + SSE SSE SSE min C F ( ) y = + 2 ln ln SSE F F 1 C y y Use with extreme caution!

28. Tuning with survey indices 28 einar If additional information are available it is relatively easy to add them to the model. If age- based survey indices are available one may use: U = q N ay a ay where qa is a parameter (catchability). The minimization is by (again assuming log-normal errors): ( min = y a ) 2 ln ln SSE U U U a ay ay

29. Population numbers at survey time 29 einar If survey time is not in the beginning of the year we need to take that into account by: + = ' ( ) p Fay May N N e ay ay = ' U q N ay a ay Where N is the population size at survey time p is the fraction of the year when the survey takes place.

30. The map 30 einar Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V 2. Selection pattern 3. Fishing mortality Natural mortality 9. Solver The sum Year Age -----> | | V Year Age -----> | | V Calculate N ay Year Age -----> | | V 4a. Population numbers 4b. Nay at survey 1 time 4c. Nay at survey 2 time Year Age -----> | | V Year Age -----> | | V indices Calculate Uay-hat Year Age -----> | | V indices 5. Predicted catch 6a. Predicted survey 1 6b. Predicted survey 2 Store qa here 1. Parameters Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V Observed catch at age Observed survey 1 indices Observed survey 2 indices Year Age -----> | | V Year Age -----> | | V Year Age -----> | | V 7. Catch residuals 8a. Survey 1 residuals The residuals 8b. Survey 2 residuals Color code Population model Observation model Measurements | Objectives Parameters Year Age -----> 7. Catch residuals squared Year Age -----> | | V squared The squared stuff Year Age -----> | | V squared 8a. Survey 1 residuals 8b. Survey 2 residuals | V

31. Objective function I 31 einar Simple to combine the two objective functions: = + SSE SSE SSE min C U ( ) 2 y = ln ln C C a ay ay a ( ) 2 y + ln ln U U a ay ay a Lets not worry about weighing for now

32. Objective function II 32 einar Often put different weights to the data: = + SSE SSE SSE min C U ( ) 2 y = ln ln C C C a ay ay a ( ) 2 y + ln ln U U U a ay ay a weights are most often set externally At minimum should check the sensitivity to

33. Getting it all together 33 einar THE MINIMIZATION STUFF The heart of the setup lies in the left side of the spread- sheet. There we have the the objective functions, Penalties and weighing factors and parameters in one place. The only thing left is to setup the solver such that it minimizes the total SSE by changing the para- meters. Sum of squares C@A U@A - Survey 1 U@A - Survey 2 Lambda 48.419 53.900 49.084 1 1 1 SSE total 151.4023 PARAMETERS Name Ln Afull Ln L Ln R Ln(parameter) Switches Parameter 2.3900 1.4481 5.0000 10.91 4.26 148.41

34. A trick to get things working 34 einar PARAMETERS Estimate logarithms for most parameters because: 1) Precludes searching nonsensical negative space 2) In logarithmic form parameters become scaled. Name Ln Afull Ln L Ln R LnF 0 LnF 1 ...... LnN 1,0 LnN 2,0 .... LnN 0,1 LnN 0,1 .. Ln q 0 Ln q 1 ..... Ln(parameter) Switches Parameter 2.3900 1.4481 5.0000 -1.6871 -2.3877 -1.9916 6.9367 6.2462 10.91 4.26 148.41 0.19 0.09 0.14 1029.39 516.05 Leave space for switches, used later on when modify the model 1771.57 1767.62 7.4796 7.4774 5.7345 -4.0738 -2.6097 0.0170 =EXP(D17) Values that are used in the model coding What Solver sees

35. Setup of xModel 35 einar The following steps may be followed in order to setup the model in Excel: 1. set up some sensible initial values for the parameters. 2. calculate selectivity (sa) 3. calculate fishing mortality (Fay) 4. calculate abundance (Nay) 5. calculate estimated catch (Cay-hat) 6. calculate estimated survey indices (Uay-hat) 7. calculate residuals in catch (lnCay-lnCay-hat)2 8. calculate residuals in survey (lnUay-lnUay-hat)2 9. use solver to minimize 7. and 8. Population model: 1-4 Observation model: 5-6 Objective function: 7-9

36. The approach 36 einar I hear - I forget I see - I remember I do - I understand

37. Lets start the work ..... 37 einar Excel translation of Shakespeare!