Hydro Excel 116
Hydro Excel 116
Hydro Excel 116
6
Introduction
Begin with the Data Source worksheet. There, you specify the Web Service you want to work with, and you can ask for what ca
the Web Service has. A list of sample Web Services is provided.
Once you've specified a web service, you can use the following worksheets to get information from the web service. It doesn't
which worksheet you access first, although a typical workflow is to download the Series Catalog and then look for time series
download.
The Sites worksheet lists sites available for the selected Web Service. Use this to get a quick list of sites.
The Variables worksheet lists all variables available in the selected Web Service.
The Site Info worksheet lists basic site information and variables measured at a given site. Use this when you are interested in
information for a particular site.
The Series Catalog worksheet lists the variables available for all sites in the selected Web Service. You get A LOT of informat
this one, so it can take a while. Use this when you basically want a detailed inventory of all sites available in the Web Service.
The Site Summary worksheet uses a pivot table to organize and filter site information.
The Time Series worksheet retrieves time series data for a given site and variable. Use this once you've examined the availab
and variables on the previous worksheets, and have decided on a particular site and variable for which you want to retrieve tim
data.
The Statistics and Charts worksheet uses a Pivot table to summarize and chart results from the Time Series worksheet. This
The Series Catalog worksheet lists the variables available for all sites in the selected Web Service. You get A LOT of informat
this one, so it can take a while. Use this when you basically want a detailed inventory of all sites available in the Web Service.
The Site Summary worksheet uses a pivot table to organize and filter site information.
The Time Series worksheet retrieves time series data for a given site and variable. Use this once you've examined the availab
and variables on the previous worksheets, and have decided on a particular site and variable for which you want to retrieve tim
data.
The Statistics and Charts worksheet uses a Pivot table to summarize and chart results from the Time Series worksheet. This
handy way of spotting trends and learning more about the time series that you have downloaded.
Excel allows a finite number of rows, so be careful when asking for a lot of data. For Office 2003, the limit is 65536 rows. Fo
2007, the limit is 1,048,576 rows. You might get "Run-time error '1004'" if the macros in this spreadsheet attempt to write to row
the row limits of Excel.
Time zone information is ignored when parsing datetimes. In other words, the datetime returned from the Web Service is us
without considering any time zone information returned from the Web Service.
Spreadsheet dependencies
This spreadsheet requires an Internet connection in order to access Web Services.
This spreadsheet can only work with WaterOneFlow Web Services that return data in WaterML format.
This spreadsheet relies on the HydroObjects object library in order to communicate with WaterOneFlow. The HydroObjects ins
be downloaded from http://his.cuahsi.org/hydroobjects.html.
Note
When running the macros in this worksheet, if you get a "License information for this component not found" or "Active-X can't c
object" or "User-defined type not defined" error, then try resetting the reference to HydroObjects in VBA. Alt+F11 is a shortcut
opening the VBA environment.
----------------------------------------------------------
License
Copyright (c) 2012, The University of Texas at Austin
All rights reserved.
The macros in this spreadsheet are distributed under the Berkeley Software Distribution license. Redistribution and use in sour
binary forms, with or without modification, are permitted provided that the following conditions are met:
* Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in
documentation and/or other materials provided with the distribution.
* Neither the name of The University of Texas at Austin nor the names of its contributors may be used to endorse or promote pr
derived from this software without specific prior written permission.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR I
WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS F
PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIA
FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINE
INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY
TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN
ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINE
INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY
TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN
ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
ee KML viewers:
ites.
at.
ow. The HydroObjects installer can
ollowing disclaimer.
he following disclaimer in the
to endorse or promote products
R PROFITS; OR BUSINESS
RACT, STRICT LIABILITY, OR
THIS SOFTWARE, EVEN IF
Data Source
In the box next to WSDL Location, input (hint: copy and
paste) the WSDL location for the WaterOneFlow Web
Service you want to access.
This Web Service will be used in all other worksheets.
Some services may not support all the methods that this
spreadsheet utilizes. For example, consider a service that
provides access to a gridded dataset. Since the data are
based on a grid, and not on discrete sensor locations or
"sites", the service will not support a GetSites or GetSiteInfo
method, and so you will not be able to use the Sites, Site
Info, or Series Catalog worksheets with that service.
Click Open Service Web Page to open a web page that may
have more information about the Web Service.
Click Get Sites to jump to the Sites worksheet and download
a list of sites from the Web Service.
Click Get Variables to jump to the Variables worksheet and
download a list of variables from the Web Service.
Click Get Series Catalog to jump to the Series Catalog
worksheet and download the series catalog for all sites
available in the Web Service.
Click Get All to download a list of sites, variables and series
catalog for all sites in the Web Service. The time series
parameters will also be populated in the Time Series
worksheet.
HydroExcel Add-Ins Menu
Select the HydroExcel Menu from the Add-Ins panel. This
menu gives you the option to save downloaded WaterML
files to disk and also to test your Internet connection. If you
choose to download the WaterML files, they will be saved to
a folder named after the spreadsheet in the same directory
as the spreadsheet.
HydroExcel Right-click Menu
On this worksheet, right-click anywhere in the row containing
a data source and select the Set Active Web Service option
from the HydroExcel menu to set that service as the active
data service.
Organization
AmeriFlux Network
Baltimore Ecosystem Study
Baltimore Ecosystem Study
Baltimore Waters Test Bed
Beacon Institute of River and Estuary
Boise State University, Hydrologic Sciences De
Chesapeake Bay Environmental Observatory
Chesapeake Bay Information Management Sys
Crown of the Continent Observatory (Montana St
EPA
Florida Dept of Enviro Protection, STORET dat
Florida Dept of Environmental Protection
Hermine Flood Database by CRWR
Hydro_NEXRAD Data Services at the University
Idaho State University
IIHR - The University of Iowa
IIHR - The University of Iowa
Lawrence Berkeley National Laboratory
Multicultural students At Sea Together (MAST)
National Atmospheric Deposition Program
National Climatic Data Center
Niagara Peninsula Conservation Authority
Niagara Peninsula Conservation Authority
NOAA National Weather Service Advanced Hydro
NOAA's National Weather Service West Gulf Ri
NOAA's National Weather Service West Gulf Ri
NOAA's National Weather Service West Gulf Ri
NOAA's National Weather Service West Gulf Ri
NOAA's National Weather Service West Gulf Ri
Northeastern University
Northwest Watershed Research Center, Agricultu
Pennsylvania State University
San Diego River Park Foundation
Southwest Florida Water Management District
Stroud Research Center / University of Delewar
Get Sites
Get Variables
Get All
s indicate which worksheets should work with the selected Web Service.
1
1
1
1
1
w Web Services
http://river.sdsc.edu/renewsd/cuahsi_1_0.asmx
http://cbe.cae.drexel.edu/SRBHOS/cuahsi_1_1.asmx?WSDL
http://ees-his06.ad.ufl.edu/SantaFe-SRGWL/cuahsi_1_0.asmx?WSDL
http://ees-his06.ad.ufl.edu/SantaFe-RainDlySRWMD/cuahsi_1_0.asmx?WSDL
http://ees-his06.ad.ufl.edu/SFe_SRWMD_SurfWater/cuahsi_1_0.asmx?WSDL
http://his.crwr.utexas.edu/TRACS/cuahsi_1_0.asmx?WSDL
http://his.crwr.utexas.edu/SabineBio/cuahsi_1_0.asmx?WSDL
http://his.crwr.utexas.edu/SanAntonioBio/cuahsi_1_0.asmx?WSDL
http://his.crwr.utexas.edu/TWDB_Sondes/cuahsi_1_0.asmx?WSDL
http://his.crwr.utexas.edu/TWDB_Wind/cuahsi_1_0.asmx?WSDL
http://river.sdsc.edu/wateroneflow/corps/cuahsi_1_0.asmx?WSDL
http://icewater.boisestate.edu/pmrw2dataservices/cuahsi_1_1.asmx
http://ees-his06.ad.ufl.edu/SantaFe-MICROWAVECITRA/cuahsi_1_0.asmx?WSD
http://ees-his06.ad.ufl.edu/SFe_CTDSondes/cuahsi_1_0.asmx?WSDL
http://water.sdsc.edu/czo_arizona/cuahsi_1_0.asmx?WSDL
http://water.sdsc.edu/czo_catalina/cuahsi_1_0.asmx?WSDL
http://192.31.21.100/czo_merced/cuahsi_1_0.asmx?WSDL
http://192.31.21.100/czo_boulder/cuahsi_1_0.asmx?WSDL
http://icewater.civil.uidaho.edu/ParadiseCreek/cuahsi_1_0.asmx?WSDL
http://nmhis.unm.edu/RGET/cuahsi_1_0.asmx?WSDL
http://192.31.21.100/czo_luquillo/cuahsi_1_0.asmx?WSDL
http://river.sdsc.edu/wateroneflow/NWIS/DailyValues.asmx?WSDL
http://river.sdsc.edu/wateroneflow/NWIS/Data.asmx?WSDL
http://river.sdsc.edu/wateroneflow/NWIS/UnitValues.asmx?WSDL
http://river.sdsc.edu/wateroneflow/NWIS/Groundwater.asmx?WSDL
http://ees-his06.ad.ufl.edu/SantaFe-GWLUSGS/cuahsi_1_0.asmx?WSDL
http://cbe.cae.drexel.edu/RIMP/cuahsi_1_0.asmx?WSDL
http://icewater.usu.edu/MudLake/cuahsi_1_0.asmx?WSDL
http://icewater.usu.edu/littlebearriver/cuahsi_1_0.asmx?WSDL
http://his.crwr.utexas.edu/GW_Edwards/cuahsi_1_0.asmx?WSDL
http://icewater.usu.edu/loganriver/cuahsi_1_0.asmx?WSDL
http://his09.umbc.edu/BaltPrecip/cuahsi_1_1.asmx?WSDL
Get All
Clear All
Title
Site Count
Freeman Ranch Mesquite Juniper Flux Tower
1
Baltimore Ecosystem Study Stream Chemistry Da
22
Baltimore Ecosystem Study Soils Data
19
Baltimore Waters Test Bed Ground Water Level D
14
Beacon Institute for River and Esturay
6
Dry Creek Experimental Watershed, SW Idaho
68
Benthic Data in Chesapeake Bay
4367
Chesapeake Bay Information Management Syste
894
Crown of the Continent Observatory Snow
2
EPA STORET
362645
Storet Phosph and Nitr in Surf water
17
Santa Fe, STORET
50
Hermine Flood
448
HydroNEXRAD
215359
Portneuf Watershed Observations, Idaho
2
IIHR Tipping Bucket
20
IIHR Water Quality
8
Grasslands Ecological Area of the San Joaquin Bas
48
MAST
44
National Atmospheric Deposition Program
19
NCDC Hourly Data
24770
Niagara Peninsula Conservation Authority Water Q
24
Niagara Peninsula Conservation Authority Water Q
71
Multi-sensor Precipitation Estimates
9308
NWS-WGRFC Daily Multi-sensor Precipitation Es
138986
NWS-WGRFC Weather Forecast Office_FWD Ho
5911
NWS-WGRFC USGS Water Region 12 Hourly M
31110
NWS-WGRFC Weather Forecast Office_EWX Ho
6396
NWS-WGRFC Daily Multi-sensor Precipitation Est
69830
Muddy River Water Quality Monitoring Project
14
Reynolds Creek Experimental Watershed, SW Id
136
Shale Hills Susquehanna CZO
1
San Diego River Park Foundation
18
Santa Fe, Southwest Florida Water Management Di
118
Christina River Basin Critical Zone Observatory
31
32
5
2178
19
4
5566
171
162
220
127
2206
7
2
26
14
5
8
1
19
6
8
29894
375831
11185
827200
190
9
12
14
14489
8
10
32.69
40.06486
29.02692
29.74135
29.84611
25.87639
30.14717
28.67102
26.05028
26.07333
-91.62195
33.62761
29.40967
29.84611
35.8546
32.43347
37.06104
40.05479
46.72261
33.79113
18.27971
-77.52639
-14.26267
13.35936
-14.33639
28.37917
37.67083
42.12035
41.49541
29.00028
41.77034
39.25436
-117.0381
-77.51886
-82.04565
-82.04983
-82.21972
-93.51
-93.51886
-97.5397
-93.76
-93.71667
122.583
-84.17204
-82.17762
-82.21972
-106.4958
-110.7404
-119.1824
-105.6165
-116.9524
-106.6828
-65.73111
179.2467
179.9098
144.7057
174.0936
-81.49703
-76.17445
-111.2884
-111.7993
-97.40028
-111.5647
-76.64867
32.80592
40.66582
30.65575
30.21551
29.9325
36.47
31.06658
29.32763
30.00417
30.095
49.32806
33.63176
29.40967
29.99806
35.9092
32.55995
37.06104
40.05479
46.80556
34.95877
18.33111
71.29403
71.38737
70.48843
68.87263
30.65131
39.65861
42.25035
41.71847
31.19972
41.93315
39.49658
hsarabia@ucsd.edu
michael.piasecki@drexel.edu
katmckee@ufl.edu
katmckee@ufl.edu
katmckee@ufl.edu
ehersh@mail.utexas.edu
ehersh@mail.utexas.edu
Benslein@gmail.com
Benslein@gmail.com
CEMVRRiverGages@usace.army.mil
nepeters@usgs.gov
katmckee@ufl.edu
katmckee@ufl.edu
chorover@cals.arizona.edu
patroch@hwr.arizona.edu
markw@snobear.Colorado.edu
hage0608@vandals.uidaho.edu
stephenb@unm.edu
leonmi@sas.upenn.edu
valentin@sdsc.edu
katmckee@ufl.edu
kenhyer@usgs.gov
jeff.horsburgh@usu.edu
jeff.horsburgh@usu.edu
milada_majerova@yahoo.com
Sites
Click Get Sites to return locations of all sites available
in the Web Service.
Create and open KML file after download
If you choose to create and open a KML file after
download, a KML file with the downloaded sites will be
written to a folder with the same name as this
spreadsheet.
The KML file will include basic site information, but not
variables recorded at each site. Use the Series Catalog
worksheet to get information about variables measured
at each site.
Restrict search to Latitude-Longitude box
Check the "Use Lat/Lon" check box and enter bounding
latitudes and longitudes to restrict the list of sites
returned by the service to those that fit in that latitudelongitude box. Some of the older web services may not
support this option.
Click Create Series Catalog for These Sites to jump to
the Series Catalog worksheet and download site info for
each site visible in the Sites worksheet.
HydroExcel Right-click Menu
Right-click anywhere in a row containing a site and
select the Get Series Catalog option from the
HydroExcel menu to get the series catalog for that
particular site.
Canceling Long Operations
If you find that the spreadsheet is taking too long to
process, you can interrupt code execution by pressing
CTRL+BREAK on your keyboard. A dialog box will tell
you that code execution was interrupted.
Get Sites
Site Code
0
0
-111.946402
41.495409
-111.799324
41.718473
State
County
None
Latitude
Longitude
Variables
Returns all variables available in the Web Service.
t Variables
Variable Name
Units
Speciation
NoData Value
Value Type
Sample Medium
Site Info
The site codes from the Sites worksheet can be used
on this worksheet to retrieve more detailed site
information.
Site Name
State
County
Latitude
Longitude
Time Series:
Variable Code
Variable Name
Value Count
Start Date
End Date
Units
Speciation
Value Type
Sample Medium
Method
Organization
Source Description
Series Catalog
Returns site locations and series catalog for sites available from the Web Service.
Limiting the number of sites returned
This process can take a long time. You can limit the number if sites returned by inputting
a whole number greater than zero next to the green cell labeled Max sites to get.
Create and open KML file after download
If you choose to create and open a KML file after download, a KML file with the
downloaded sites will be written to a folder with the same name as this spreadsheet.
The KML file will include basic site information as well as a list of variables measured at
each site (Sites by Variable).
A time stamp based on the period of record of a given variable at a given site will be
included in the site placemark. This means that if you have enabled time in your KML
viewer, you can adjust the currently displayed datetime to see which sites have a given
variable for the selected date. This only affects placemarks in the Sites by Variable
folder in the KML output.
HydroExcel Right-click Menu
Right-click anywhere in a row for a site and variable and select Download Time Series
or Populate Time Series Parameters from the HydroExcel menu to jump to the Time
Series worksheet and download the time series or populate the time series parameters
for that site and variable.
Sorting
Once site information is retrieved, you may want to sort by variable, count or date to see
which sites you are interested in exploring further. Select only column names and data
fields before sorting. Make sure you select all columns so they don't get mismatched!
Canceling Long Operations
If you find that the spreadsheet is taking too long to process, you can interrupt code
execution by pressing CTRL+BREAK on your keyboard. A dialog box will tell you that
code execution was interrupted.
Site Name
Variable Code
Variable Name
Value Count
Start Date
End Date
Units
Speciation
NoData Value
Value Type
Data Type
General Category
Sample Medium
Method
Organization
Source Description
State
County
Latitude
Longitude
Site Summary
This worksheet contains a pivot table that summarizes information in the Series Catalog. All the headings in Series Catalog ca
Count" is recommended to be used in the Values box to give the number of data values present for the given pivot table filters.
To Change Row Labels: Click the pivot table to bring up the pivot table field list. In the actual pivot table, click on the existing
that label from the table. Then drag the desired field from the field selection area to where the previous row label was in the tab
To Change Column Labels: Follow the "Change Row Labels" procedure but drag to and from the columns in the pivot table.
To Change Filter: Follow the "Change Row Labels" procedure but drag to and from the Filter box.
To Change Filtered items: Click the drop-down box above the table and select the desired filter items. Only those rows that m
Note: The same fields cannot be used in two areas at once. E.g., Variable Name cannot be a filter and a row label.
Click Refresh Site Summary if the pivot table does not reflect the current data in the Series Catalog worksheet.
Filter
Variable Name
Sum of Value Count
Site Name
East Fork Little Bear River at Paradise Canal Diversion near Avon, Utah
East Fork Little Bear River Radio Repeater near Avon, Utah
Little Bear River at McMurdy Hollow near Paradise, Utah
Little Bear River at Mendon Road near Mendon, Utah
Little Bear River at Paradise, Utah
Little Bear River below Confluence of South and East Forks near Avon, Utah
Little Bear River near Wellsville, Utah
Little Bear River Upper Weather Station near Avon, Utah
Radio Repeater near Paradise, Utah
South Fork Little Bear River above Davenport Creek near Avon, Utah
South Fork Little Bear River below Davenport Creek near Avon, Utah
Utah State University Experimental Farm near Wellsville, Utah
Grand Total
http://his02.usu.edu/littlebearriver/cuahsi_1_0
6/12/2009 7:35
(All)
Sample Medium
Air
101784
106260
208044
All the headings in Series Catalog can be chosen as either a filter, row label or column label. "Sum of
esent for the given pivot table filters.
tual pivot table, click on the existing row label (e.g., Site Name) and drag it onto the field list to remove
the previous row label was in the table.
ter box.
d filter items. Only those rows that match the filter will be displayed.
s Catalog worksheet.
02.usu.edu/littlebearriver/cuahsi_1_0.asmx?WSDL
Precipitation
Surface Water
(blank)
478200
812900
1024453
155149
402201
425579
16965
422801
496429
17844
34809
4217712
32620
57376
66505
66403
27237
27691
33930
57376
28299
32583
35519
465539
Grand Total
510820
57376
879405
1090856
155149
429438
453270
152679
57376
451100
529012
159623
4926104
Time Series
In this worksheet, you can download time series for a given location, variable code,
and date range. To download data:
1. Fill in the required parameters next to the green cells.
2. Check "Ignore NoDataValue" if you don't want NoDataValues (aka Null values)
written to the spreadsheet. These values may occur when the sensor recording the
measurement was down.
3. Click the "Get Values" button.
Tip: Use GetSiteInfo on the Site Info worksheet to see a list of variable codes for
DateTime
a given site.
---------------------------------------------------------------For Services Without Sites
Some Web Services provide access to gridded data, instead of data at discrete
sampling stations. For these services, you may provide a latitude-longitude point, or a
latitude-longitude box, depending on the service. Data are interpolated automatically
to the point, or spatially averaged over the box.
Querying for Points (works with DAYMET)
Specify the Location in decimal degrees as:
GEOM:POINT(Longitude Latitude)
For example, to get data for Longitude -106.5, and Latitude 43.2 (central Wyoming),
you would input:
GEOM:POINT(-106.5 43.2)
Querying for Boxes (works with MODIS)
Specify the Location in decimal degrees as:
GEOM:BOX(west south,east north)
For example, to get data for roughtly all of Wyoming, you would input:
GEOM:BOX(-111 41,-104 45)
---------------------------------------------------------------Canceling Long Operations
If you find that the spreadsheet is taking too long to process, you can interrupt code
execution by pressing CTRL+BREAK on your keyboard. A dialog box will tell you that
code execution was interrupted.
Get Values
Get Values
Value
Qualifier
Offset
Offset Units
Offset Description
Day
Month
Year
Day of Year
Data
Hour of Day
Average of Value Max of Value Min of Value
0:00 25.0741666667
36.43
11.72
1:00
26.2275
39.04
13.88
2:00 24.0345454545
36.02
15.69
3:00
20.605
33.51
10.89
4:00 24.9890909091
37.19
11.17
5:00 24.6522727273
78.45
11.92
6:00 28.9231818182
111.36
12.49
7:00 21.5977272727
37.82
10.06
8:00 21.7472727273
39.48
9.51
9:00 21.0740909091
33.22
9.14
10:00 19.7554545455
36.63
11.03
11:00
22.71
41.53
10.67
12:00 24.2631818182
38.81
8.84
13:00 20.9795454545
37.73
11.14
14:00 20.8016666667
37.92
14.23
15:00 21.0908333333
34.69
11.8
16:00 23.6095833333
38.3
11.71
17:00
22.8475
36.46
11.89
18:00 22.5708333333
34.77
9.95
19:00
26.545
43.75
8.84
20:00 27.3145833333
55.85
10.64
21:00 24.7704166667
39.39
9.98
22:00 24.4458333333
41.05
11.2
23:00
Grand Total
27.3679166667
23.6979528986
40.53
111.36
12.53
8.84
e Viewing
ML:USU1
Turbidity
nephelometric turbidity units
Surface Water
MudLake:USU-ML-Causeway
Mud Lake outflow to Bear Lake at the causeway.
42.12042
-111.29907
5/31/2009 14:00
6/12/09 1:30
0
6/12/09 12:02
http://icewater.usu.edu/mudlake/cuahsi_1_0.asmx?WSDL