translated from Stata code 2/24/2026 Owner: Samantha Werner
Usage
get_commercial_data(
ora_id,
oraprod_pw,
spp_name,
nespp3_codes,
START.YEAR,
END.YEAR,
deflate_yr
)Arguments
- ora_id
username for Oracle connection (in quotation marks)
- oraprod_pw
password for Oracle connection (in quotation marks)
- spp_name
the name of the species you want to pull (e.g., "LONGFINSQUID")
- nespp3_codes
the NESPP3 codes for the species you want to pull (e.g., "('801')") - note the single quotes inside the string for SQL
- START.YEAR
the first year you want to pull (e.g., 1996)
- END.YEAR
the last year you want to pull (e.g., 2025)
- deflate_yr
the year you want to deflate to (e.g, 2025)
Details
overview– This code creates six economic commercial fishing indicators used in ESP work. The code below pulls data, deflates any monetary values and formats data to what is needed for the time series plots. Must have access to the 'NEFSC_GARFO' schema in Oracle to run this code.
Indicators created: Commercial Landings (LBS): The total weight of the species landed (e.g., Commercial_LONGFINSQUID_Landings_LBS). Number of Commercial Vessels: The count of unique permits landing that species (e.g., N_Commercial_Vessels_Landing_LONGFINSQUID). Average Price per Pound: The average annual price, winsorized to handle outliers and adjusted for inflation (e.g., AVGPRICE_LONGFINSQUID_2025_DOLlb). Total Annual Revenue: The total value of all landings for that species, adjusted for inflation (e.g., TOTALANNUALREV_LONGFINSQUID_2025Dols). Average Revenue per Vessel: The average revenue earned per permit per year, adjusted for inflation (e.g., AVGVESREVperYr_LONGFINSQUID_2025_DOLlb). Average Annual Diesel Price: The price of Ultra-Low-Sulfur No. 2 Diesel (from FRED), adjusted for inflation (e.g., AVGANNUAL_DIESEL_PRICE2025dols).
This uses CFDERS data but may need to be updated to CAMs
Table with species names and nespp3 codes here: https://nefsc.github.io/NEFSC-dataserve/table-of-commercial-species.html
before running!!! make sure you are connected to VPN ensure all packages are installed you have a folder in your directory called "data" NEVER COMMIT OR PUSH TO GH WITH YOUR ORACLE USERNAME AND PASSWORD IN THIS SCRIPT
Returns a csv file in 'data/intermediate/' with the queried data