Data and Analysis Template Files

Important notice: AnalysisTemplatev3 (01/15/08) has been replaced with AnalysisMonthlyv5 (7/28/2011)

AnalysisMonthlyv5 is compatible with Office 2007 Excel and Office 2010 Excel but not with Office 1997-2003 Excel.

New features of AnalysisMonthlyv5 include:

  • Accesses four additional river data files (Blanchard River, Tiffin River, Chickasaw Creek and an unnamed tributary to Lost Creek);
  • Allows selection of three additional chemical parameters (sulfate, silica and conductivity);
  • Provides two new graphing options (loading rate versus time and loading rate plus cumulative load versus time);
  • Provides one new tabular summary (monthly load, discharge, flow-weighted concentration, time-weighted concentration and cumulative time for a selected river, parameter and time interval);
  • Includes a feature allowing selection of months to be included in various graphs and summaries.

As with previous versions of our data analysis templates, do not change the name of the file (AnalysisMonthlyv5) since it is referred to internally in the Excel macros that drive the template.

Update Status of RiverData files.

To use AnalysisMonthlyv5, you need to upload new RiverData files for each river of interest. The new RiverData files are also in Office 2007/Office 2010 Excel worksheets. The worksheets include additional columns for the new chemical parameters.

The RiverData files listed below include period-of-record data for each station. For currently operating stations, the data extend through the end of the 2011Water Year (September 30, 2011). It is our current practice to update each river data file on a quarterly basis.

Update status of Directions for Use of the Data Analysis Templates.

On December 16, 2005 the Directions for use of the AnalyticalTemplatev3 were revised to reflect the 10/12/2005 version of the AnalysisTemplatev3. Those directions are still applicable to the operation of AnalysisMonthlyv5.

Platform and Software Requirements

The AnalysisMonthyv5 was developed for use on Windows operating systems containing Microsoft Office 2007 or Microsoft Office 2010 Excel software. The program will not operate on Office 1997/2003 software.

Data Security

The RiverData files, as downloaded from this website, are protected. Changes cannot be made in these original Excel files. However, once the Excel files are copied, the copies are no longer protected. Copied files could be modified in ways that would alter the graphs and summary reports and/or prevent operation of AnalysisMonthlyv5. Consequently, we urge users to use RiverData files as directly downloaded from this website, rather than RiverData files shared from other users.

Data quality control and data screening.

The data provided in the RiverData files have all been screened by NCWQR staff. The purpose of the screening is to remove outliers that staff deem likely to reflect sampling or analytical errors rather than outliers that reflect the real variability in stream chemistry. Often, in the screening process, the causes of the outlier values can be determined and appropriate corrective actions taken. These may involve correction of sample concentrations or deletion of those data points.

This micro-site contains data for approximately 126,000 water samples collected beginning in 1974. We cannot guarantee that each data point is free from sampling bias/error, analytical errors, or transcription errors. However, since its beginnings, the NCWQR has operated a substantial internal quality control program and has participated in numerous external quality control reviews and sample exchange programs. These programs have consistently demonstrated that data produced by the NCWQR is of high quality.

A note on detection limits and zero and negative concentrations

It is routine practice in analytical chemistry to determine method detection limits and/or limits of quantitation, below which analytical results are considered less reliable or unreliable. This is something that we also do as part of our standard procedures. Many laboratories, especially those associated with agencies such as the U.S. EPA, do not report individual values that are less than the detection limit, even if the analytical equipment returns such values. This is in part because as individual measurements they may not be considered valid under litigation.

The measured concentration consists of the true but unknown concentration plus random instrument error, which is usually small compared to the range of expected environmental values. In a sample for which the true concentration is very small, perhaps even essentially zero, it is possible to obtain an analytical result of 0 or even a small negative concentration. Results of this sort are often “censored” and replaced with the statement “<DL” or “<2”, where DL is the detection limit, in this case 2. Some agencies now follow the unfortunate convention of writing “-2” rather than “<2”.

Censoring these low values creates a number of problems for data analysis. How do you take an average? If you leave out these numbers, you get a biased result because you did not toss out any other (higher) values. Even if you replace negative concentrations with 0, a bias ensues, because you’ve chopped off some portion of the lower end of the distribution of random instrument error.

For these reasons, we do not censor our data. Values of -9 and -1 are used as missing value codes, but all other negative and zero concentrations are actual, valid results. Negative concentrations make no physical sense, but they make analytical and statistical sense. Users should be aware of this, and if necessary make their own decisions about how to use these values. Particularly if log transformations are to be used, some decision on the part of the user will be required.

How to cite these data

When you are using these data in publications or presentations, please cite the data as follows: Heidelberg University, National Center for Water Quality Research, Tributary Loading Website (http://www.heidelberg.edu/academiclife/distinctive/ncwqr/data)

File Downloads

  1. Downloading Excel files

    Recommendation: Each river file is updated on a quarterly basis. In addition to the latest data being added, corrections to historic data may also have been made. Therefore, it is recommended the most recent river file be used. Though most corrections are to single dates, some may cover a greater period of record (e.g., data in the SciotoData file between May 18th, 2004 and February 10th, 2005 was removed as it was found to be from a different river, and the station was offline during this timeframe.)

    Warning: Do not change the names of any of the RiverData (e.g., CuyahogaData) files or the AnalysisMonthlyv5 file. If you change the file names, the macros that run the file will not operate. Also, the RiverData files and the AnalysisMonthlyv5 file should be placed in the same folder.

    Warning: You will have to use the Enable Macros setting on your computer to use the AnalysisMonthlyv5 program. You may have to adjust the Macro setting on your computer to be able to download the AnalyisMonthlyv5 program.

  2. Directions for use of Analysis Template v3 (also applicable for AnalysisMonthlyv5)
  3. Download RiverData files and AnalysisMonthlyv5.
    1. AnalysisMonthlyv5 - updated 05/27/2014
    2. BlanchardData – Updated 04/04/2014
    3. ChickasawData - Updated 04/04/2014
    4. CuyahogaData - Updated 04/04/2014
    5. GrandData
    6. GreatMiamiData - Updated 04/04/2014
    7. HoneyCreekData - Updated 04/04/2014
    8. LostCreekData - Updated 04/04/2014
    9. MaumeeData - Updated 05/12/2014
    10. MuskingumData - Updated 04/04/2014
    11. PortageData - Updated 04/04/2014
    12. RaisinData - Updated 04/04/2014
    13. RockCreekData - Updated 04/04/2014
    14. SanduskyData - Updated 01/04/2014
    15. SciotoData - Updated 04/04/2014
    16. TiffinData - Updated 04/04/2014
    17. VermilionData