
Help for GetFlowRecord.xls  (http://GetMyRealtime.com (c)Carson)

** Create a copy of GetFlowRecord.xls and **
** rename it for each gage record and calendar year such as LVKids2009.xls **


OVERVIEW:

GetFlowRecord uses methods for the computation of open channel flow records of the US Geological Survey as described in the online water supply paper: http://pubs.usgs.gov/wsp/wsp2175/

1) Create rating. Sheet4.Measurements.
2) Enter shifts to apply. Sheet4.Measurements.
3) Load field GH's to HDB (hydrologic database). Sheet0.LoadHDB.
4) Enter GH corrections. Sheet6.GageCorrections.
5) Retrieve field GH's from HDB and apply corrections. Sheet6.GageCorrections.
6) Run primary computations on GH's. Sheet7.PrimaryComps.
7) Write final GH and Flow to HDB. Sheet6.GageCorrections.
8) Document, document, document...


GETTING STARTED:

The computation of flow records begins once the field gage height records are loaded into the Hydrologic Data Base (HDB) named GetFlowHDB.mdb or if you wish, the GetRealTime database GetAccessHDB.mdb.  Both database files are identical.  GetRealtime allows downloading online values from various websites.  These database files are MS Office Access files.  Any other ODBC database can be used if the database tables are adapted to that database.

The program GetAccess.exe can be used to access and maintain either HDB.  Running Setup.exe will install the GetAccess.exe program and its supporting DLL system files (VB6 DLL's) used to view the HDB database.  It will also install the GetAccessHDB.mdb database.  The default location for these 2 files are in C:\Program Files\Access.

Loading data from the field data logger can be accomplished on Sheet0 as explained below.


ALL SHEETS:

The Rust Colored cells are used by macros or are referenced by other worksheets and indicate data to be supplied by the user.  These cells cannot be moved without adjusting the VBA macro codes.

Other colors are just for highlighting certain info.


SHEET0.LoadHDB:

The HDB connection string is entered in rust colored cell A1.  In this example the connection string is:  Driver={Microsoft AccessDriver (*.mdb)}; DBQ=C:\Program Files\GetAccess\GetAccessHDB.mdb;Uid=:Admin;Pwd=Me; 

The HDB station data id, DSID (datatype_site_id), is entered in cell A2.  An explanation on how to create your own DSID's is located at the end of this help page.

Following the instructions on SHEET0, the Date_Time and GageHeights are loaded anywhere on the sheet.  Highlight the values to upload and press the button Upload Selection.  The date_time and GH values will be written to HDB table Rhour or table Runit depending on the detected time step of the data.


SHEET1.CompChecklist:

Fill in the rust colored cells Calendar Year, Station Site ID, and Station Name.  The yellow cells can be filled out as task are accomplished through out the year.  These yellow cells can be moved and expanded as needed.


SHEET2.StationDescription:

An example of the type of info the USGS list on their station descriptions.


SHEET3.LevelSummary:

Example of how gage height datum references are maintained.  Datum Corrections to the gage height record when needed are applied on SHEET6.GageCorrections.


SHEET4.Measurements:

Discharge measurements are entered in the yellow area.  The shift and percent differences can be computed by copying the formulas to new data lines.  This data is not referenced so Remarks can be entered as needed or below each measurement data line.

Columns R,S,T rust colored areas are filled with the shifts to be applied to the Primary Computations.  The shifts are prorated with time.  Shifting with event must be done by including addidional shifts as needed during the time of the event.

Columns W,X yellow colored areas are filled with the GH and Flow of the measurements to be used in developing a new rating.  The XLS worksheet GetRegression.xls is included with your download and is used to compute the rating coefficients using the Y=a(X-e)^b option.  Instructions for GetRegression are located on the first sheet of its workbook.  GetRegression will add a graph page as it is being used which can be deleted once the final regression values for a,b, and e are determined.  The Graph SHEET RatingNo.1 is used to display the final ratings and references the SHEET4 yellow areas and the SHEET5 rating table.


SHEET5.Rating:

The coefficients a, b, and e (PZF or point of zero flow) are entered in the rust colored area.  Historical rating coefficients can be included for reference.  The last rating listed will be used to develop the rating table to the right and and all ratings will be considered if applicable for primary computations.  Two sets of a,b, and PZF's can be used to describe the rating if needed.

The rating table can be created by entering the minimum and maximum GH in the rust colored column O and pressing the Generate Rating Table button.  The rating table is not referenced by code but is used in the Graph SHEET RatingNo.1.


SHEET6.GageCorrections:

If any GH corrections are needed to the field record, then these corrections are entered in the rust colored area of columns A,B,C.  These corrections will be applied with time.

Enter the BeginDateTime and EndDateTime rust colored area of column E and the gage height record DSID in column H for the uploaded GH's.  Select whether the field GH values are in HDB table Rhour or table Runit by selecting option in rust colored area of column I.

Press the Yellow Get GH's button to retrieve the field GH's and apply corrections.  Corrected GH's will be displayed in column H.  At this point you may fill in missing or bad GH values in column H and then write them to HDB by pressing the blue Put GH's.  The DSID for the corrected GH's should probably be different than the field GH values so you dont keep correcting values that have already been corrected... but you can develop a method to get around this somehow using GH Source Field and adjusting the VBA code somehow.  A graph of the GH's is located on the right of SHEET6 for evaluation.

At this point Primary Calculations can be made on the corrected GH's of column H and having entered the shifts to apply on SHEET4.Measurements.  After performing Primary Computation on SHEET7 we will return to SHEET6 to process the computed flows.

....Retruning from Sheet7.PrimaryComps, when the blue Put GH's button is pressed the following data is written to HDB:  GH unit values, computed mean hourly GH values if unit values used, mean daily GH, max daily GH, and min daily GH.

Edit any bad flows computed in column J and write them to HDB by pressing the blue Put Flows button.  When the blue Put Flows button is pressed the following data is written to HDB:  Q unit values, computed mean hourly Q values if unit values used, mean daily Q, max daily Q, min daily Q, and applied unit shifts.  A graph of the flows is located on the right of SHEET6 for evalution.

At this point all computations are completed and final data has been written to SHEETS 8-15.  If corrections are needed on a sub period of record, then start the above process over.


SHEET7.PrimaryComps:

There is nothing to enter.  Press the Green Go button and the primary computations will be displayed and shift and flow values will be written to SHEET6, columns I and J.

The primary computation sheets can be printed out and measurements/shifts/corrections penciled in as needed to document computations.

Return to SHEET6 to finish correcting the final flows to be written to HDB.


SHEETS8-15:

... are updated automatically when the blue Put buttons are pressed on SHEET6.

You may wish to rearrange the order of the sheets to suite your frequency of use.



HDB SETUP AND DATABASE ID's:

The online help page at http://GetMyRealtime.com/Help has applicable sections with more detail and has example figures for using GetAccess.exe than that which follows here.

To add your new station name and data type to the table Rsite, first you will need to determine the stations Site_id and Datatype_id.  These 2 values will then be used to create a unique Datatype_Site_ID (DSID) that is stored with each retrieved value.

Site_Id's= 0^00 where 0=area or reach, 00=count in reach.  Site_Id's are 0 to 999.

Datatype_id 1= flow, cfs
Datatype_id 2= gage height, ft
Datatype_id 10= rainfall, inches, etc, (See table of Datatype_ids below)

Datatype_Site_ID (DSID) = 00^000 where 00=datatype_id and 000=Site_id.
Valid DSIDs are integers in the range -32,768 to 32,767

Example: DSID 10212 = rainfall, area 2, 12th station
Example: DSID 1212 = flow, area 2, 12th station
Example: DSID -1212 = Computed flow, area 2, 12th station

You may wish to consider using negative DSID's for values that are computed to keep them separated from the web source reported values and also to note it has been computed.

You may use another method for creating the Site_id and associated unique DSID but remember the valid DSID integer range -32,768 to 32,767.

Datatype_ids must be used as listed because of their associated formatting and averaging methods used by GetAccess.exe.  If  more than 32 datatypes are needed, then rename one of the unused listed ids that do not have a unit name of feet or inches.  These are handled as totals or rounded differently.

Table of datatype_ids

datatype_id	datatype_name	unit_name
1	flow	cfs
2	gage height	ft
3	elevation	ft
4	contents	kaf
5	contents	% capacity
6	specific conductance	umhos
7	water temperature	f
8	ph	std units
9	ytd precipitation	inches
10	rainfall	inches
11	total precip	inches
12	turbidity	fnu
13	dissolved oxygen	% saturation
14	tds	mg/l
15	pressure	in hg
16	dew point	f
17	air temperature	f
18	humidity	% saturation
19	wind	direction
20	wind gust	mph
21	snow depth	inches
22	absolute humidity	g/m3
23	snow water content	inches
24	inflow	cfs
25	snow water content	% of avg
26	ytd precipitation	% of avg
27	ET	inches
28	wind speed	mph
29	solar radiation	Langleys/day
30	runoff	cfs


Edit the HDB database for accepting your Gage Height data using GetAccess.exe, open the database file GetFlowRecordHDB.mdb and follow these steps:

1) Open GetAccess.exe and click DB Tables.

2) Select rsite from the list of database tables presentend, check the Allow Edit check box, and then click GO.  The blue * indicates the row is available for adding new data by typing in fields on that row.

3) Following the example 2 rows that you see enter your station information.  Fields to the right of Unit Name are optional.  When you move from the line being edited the values will be updated in the HDB database.

4) Exit GetAccess.exe and your HDB database is ready for use.

END