I have reviewed and analyzed USGS mean daily flow data for several years and I have settled into a bit of routine once I save the text file from the NWIS website for a given gage. This file (https://www.box.com/s/9x7y9rpi6lpnhis3kny6) shows the steps I take to generate a flow duration curve for the Connecticut River at Montague City (#01170500). This gage happens to have a very nice 108+ year record; clearly this is on the high end for most gages. One of the first steps I take at this stage is to obtain the data based on water year (WY). As such, I will enter 1904-10-01 for the begin date and 2012-09-30 for the end date. I always save the data as ‘Tab-separated’ and make sure that the file has a .txt extension.
Once I have the text file, I import it into Excel and the tab separated format makes the import process a breeze. The next step is to simply copy the date and discharge data and paste the data into a separate tab. You should be able to run the macro called: Year_Month_Date and this will calculate the year, month and date for each flow value. Once that is done, I can then generate a pivot table using Year for Columns, and Month followed by Date for the rows. Since I’m using 24hr mean daily data, I can use the sum function for the flow data, but if I had 15 minute or hourly data, I would use the average of discharge. Once this table is made, I then copy and paste it into yet another tab. If the data are pasted into cell A1, then the delete month macro should quickly delete the twelve extra rows that were generated in the pivot table. It is at this stage that I think the fun analysis can begin. The PercentileCharts tab shows two different flow duration curves, one for the April 1 to May 30 flows and another one for the June 1 to July 31 flows. I use the ‘Percentile’ function to generate the data. When using this function, remember that for the percent of time a flow is equalled or exceeded, you have to use 1-the percent of interest for the data to be plotted correctly. These dates are somewhat arbitrary but if you are engaged in perhaps a fish study, you can tailor your dates to a specific life stage (e.g juveniles, adults, migration seasons). If you know that certain decades or time periods were wetter or drier than others, you can modify your array selection for certain years.
While I don’t generate a hydrograph based on percentiles very often, I thought I would include how the percentile function can be used to create an annual hydrograph based on percent exceedance of interest. It’s simply another way to explore and think about the data. I plotted the data on a log scale simply to spread out the low flow data, it’s a bit difficult to discern on a linear scale.
All of the steps mentioned above can be done in a matter of minutes. The internet and Excel make these analyses rather quick and straightforward. Comments on these techniques or your own methodology are welcome!