Colorado Data Entry


Updating the Colorado data for a year involves preparing two spreadsheets. The first spreadsheet consists of crop, acreage and climate data that culminates in estimates of pumping, groundwater return flow fractions and groundwater acreage for each of eight counties. In essence, updating the model for Colorado groundwater pumping boils down to generating these twenty four values.

The second spreadsheet is used to prepare monthly surface water return flows for three ditches. In essence, updating the model for Colorado surface water returns boils down to generating these thirty-six values.

Groundwater Pumping Spreadsheet

The groundwater pumping spreadsheet is named co/yyyy Republican-CO Pump.xls where yyyy is the year. This file consists of a number of sheets, but the key sheet is named Annual Pump. This sheet contains twelve tables which summarize the calculations. In particular, the following tables contain the results required by the groundwater model.
Table 3 - Acres Irrigated by Groundwater Pumping
This table contains the county totals of groundwater irrigation for the eight counties. These values are appended to the file data/co/gwacres.dat.
Table 10 - Irrigation Groundwater Pumping (acre-feet)
This table contains the county totals of groundwater pumping for the eight counties. These values are appended to the file data/co/pumping.dat.
Table 12 - Recharge Fraction for Groundwater Pumping in Colorado
This table contains the county average recharge fraction for the eight counties. These values are appended to the file data/co/returns.dat.
The three sets of eight county values are transferred to the individual files by opening the spreadsheet in an appropriate program, copying the values from the three spreadsheets and pasting the values at the end of the text files.

All the groundwater related calculations are contained in this spreadsheet. The User Interface contains step by step instructions for obtaining the raw data and performing the calculations. In short, the steps are:

  1. Set the beginning and ending date for the year;
  2. Download and enter HPCC climate data for each county;
  3. Download and enter County Assessor irrigated acreage for each county;
  4. Download and enter NASS crop statistics for each county;
  5. Calculate CIR, pumping, returns and acreage.
The exact details are shown in the spreadsheet. A number of Excel macros in the spreadsheet aid setting up and performing the calculations. The following table summarizes the sheets and their purpose. Sheets shown with a yellow background are used to input raw data. The orange sheet is the final results. Sheets shown in blue contain parameters that do not change from year to year or intermediate calculations.
SheetDescription
User Interface Enter beginning and end of year; Navigate data entry process.
Annual Pump Final results; Copy and paste selected values to text files.
Climate Data Enter daily precipitation and minimum and maximum temperature for Akron 4E (050109), Burlington (051121), Cheyenne Wells (051564), Holyoke (054082) and Wray 2E (059243) for the year.
Assessors Irrigated Acreage Enter county total sprinkler and flood irrigated acreage for Cheyenne, Kit Carson, Lincoln, Logan, Phillips, Sedgwick, Washington and Yuma counties.
NASS Crop Data Enter total acreage of Corn-Grain, Corn-Silage, Wheat, Other Hay, Alfalfa, Sorghum, Dry Beans, Oats, Barley and Sugar Beets for each county.
Crop Control Defines beginning and end of growing season, growth coefficients by month, effective precipitation coefficient and threshold temperature for each crop type.
Climate Control Defines climate station location and Hargreaves ETo to Penman-Monteith ETr ratios by month for each county.
CWR Crop Water Requirement; Monthly total of values in Daily sheet by crop for each county
Eff-Prec Effective Precipitation; Monthly total calculated per TR 21 by crop for each county
CIR Crop Irrigation Requirement; Monthly total defined as CWR minus effective precipitation by crop for each county
Cheyenne - REF ET Daily reference ET calculation for Cheyenne county. Daily reference ET for each county ETo is calculated using the Hargreaves 1985 method based on the daily minimum and maximum temperature (sheet Climate Data), date and station latitude (sheet Climate Control). ETo is converted to a calibrated Penman-Monteith ETr using monthly factors (sheet Climate Control) for each county.
Kit Carson - REF ET Daily reference ET calculation for Kit Carson county.
Lincoln - REF ET Daily reference ET calculation for Lincoln county.
Logan - REF ET Daily reference ET calculation for Logan county.
Phillips - REF ET Daily reference ET calculation for Cheyenne county.
Sedgwick - REF ET Daily reference ET calculation for Cheyenne county.
Washington - REF ET Daily reference ET calculation for Cheyenne county.
Yuma - REF ET Daily reference ET calculation for Cheyenne county.
Daily Daily Crop Water Requirement ETc by crop for each county; Calculated from county reference ET sheets using crop coefficients in Crop Control sheet and growing-degree-days (GDD).
Kc Modified Penman Crop Coefficients by stage and crop used in CWR calculations.

Surface Water Spreadsheet

The surface water spreadsheet is named CO Surface Water Diversions yyyy.xls where yyyy is the year. For 2001 and 2002, the calculations were done in a single spreadsheet named CO Surface Water Diversions for 2001 and 2002.xls.

The calculations in the spreadsheet are used to determine the amount of surface water diverted and applied in Colorado. The surface water return flow is simply 40 percent of the surface water diversions.

There are three ditches represented in the model named the Hale, Laird and Pioneer. The Hale and Laird are entirely inside Colorado. The Pioneer crosses the state line and becomes the Haigler. The net Pioneer diversions defined as the diversion at the river minus the state line flow is used to calculate Colorado surface water returns on the Pioneer.

Surface water recharge by month defined as 40 percent of the diverted amount is appended to the file ../data/co/surf.dat. This file contains one line for every month with the Laird, Pioneer and Hale values listed in order. The values are simply cut and paste from the table Table 1: Colorado Surface Water Return Flows

Home | Previous | Next

Model Units
Directory Structure
Programs
Static Files
Precipitation Data Entry
ET Data Entry
Stream Data Entry
Kansas Data Entry
Nebraska Data Entry
Running the Model