Load Duration Curves: Spreadsheet and PowerPoint Tutorial

1
Load Duration CurvesSpreadsheet and PowerPoint
Tutorial
Office of Water Quality Indiana Department of
Environmental Management November 2003
By Ernest L. Johnson III1 and Bruce
Cleland2 1Assessment Branch, Office of Water
Quality Indiana Department of Environmental
Management 2525 North Shadeland Avenue, Box
6015 Indianapolis, IN 46206-6015 2Americas
Clean Water Foundation 750 First Street N.E.
Suite1030 Washington, D.C. 20002 IDEM
32/02/084/2003
2
Assumptions for Tutorial

• Process of the tutorial was done using the
  following programs
• Internet Explorer 6.0
• Excel 97 SR-2
• PowerPoint 97 SR-2
• This is a draft copy.
• Issues regarding the mechanics of the spreadsheet
  or tutorial contact ejohnson_at_dem.state.in.us
• Issues regarding Flow Duration concepts contact
  b.cleland_at_acwf.org

• Problems encountered
• Netscape Navigator does not save U.S.G.S.
  discharge data in .txt form
• All workbooks in Excel 97 need to be open in the
  same session of Excel
• If you print this please do not do so in color.
  You will need to uncheck the Black White
  check box before printing or you will not get any
  backgrounds.

3
Go to http//waterdata.usgs.gov/nwis/sw This is
where you will get your flow data. Click the
Streamflow button.
4
Click the State box under Site Location, then
click Submit.
5
Select Your State and click Submit
6
Find the gauging station nearest to your sampling
area and click on the Site Number.
7
For example purposes, Ive chosen Eel River near
Logansport. The data set is current. Note if
doing a Load Duration Curve the flow data must
include the dates your samples were collected.
8
When you get to this screen, drop down the
Available data for this site and Select
Station Home Page.
9
Here is where you will copy the Station
Description information into the Duration Curve
Spreadsheet. 1. Click and Drag your cursor from
Location to the end of the email address
h20team_at_usgs.gov. 2. Select Edit and then Copy
from the menu bar, or press Ctrl C.
10
This is what the screen will look like when you
select the data.
11
Notice the cells with the red triangle in the
upper right corner? Place your cursor over
these for helpful information.
Open your !WQ Tool(Template).xls file. Select
the cell you see here A4 and click the Paste
button or type CtrlV. You may need to adjust the
column width if your data show a bunch of
. In this workbook, anyplace you see
Neon Green is a place where you will need to
enter data. Also, when copying data from a web
page it may not post just like this. You may
need to delete out the original data and paste in
as you see fit.
12
You can switch between the USGS site page and
here to either copy or retype the information for
Station Description in A1 and the information
for Station ID in H2, Station Name H3, and
Drainage area G4. This information is linked
throughout the workbook and you wont need to
retype them again. If you Copy, Paste the above
information from the USGS Website, you should use
Paste Special, Values.
13
Notice that the Station ID and Name have copied
over automatically here for you.
Click on the Raw_Data tab at the bottom and
then go back to the USGS site to finish your flow
data retrieval. We wont do anything here just
yet.
14
To get here we clicked in the Available data for
this site and Selected Surface-water Daily
streamflow.
If you do not want/need all the data, you can
select your own dates by typing in the range of
interest.
Select the Tab-separated data button and Click
Submit
15
You will get the File Download screen. Click
Save. The default name is discharge. Leave
the default name as it appears. The name must
be the same or the copy program will not work
later in the spreadsheet.
16
Ive created a folder in My Documents as a
standard place to put the discharge file. Notice
that there is only one file. We do not need to
keep the raw data once we copy it into our
spreadsheet, so I will just overwrite the
previous file.
17
The computer will tell you the file exists and
Do you want to replace it?. Click Yes.
18
Now we want to open the file. Click Open.
If you do not get the Download Complete window,
Do Not Panic. You will need to manually open the
file. Go to your Excel session and select File,
Open and select the discharge file from the
folder it was placed.
19
Sometimes Windows wont know which program to
open the file in and you will get the Open With
window. Simply select Microsoft Excel and click
OK.
20
You should now be looking at the raw data from
the USGS. Now, go back to the Flow Duration
spreadsheet. Click Window and select the !WQ
Tool(Template).xls workbook. You may have
noticed that we have yet to save your working
file. Do not worry, this will happen shortly.
Well save it when you have moved your raw data
(and sample data if doing a Load Duration Curve).
21
Now that you are back to the !Flow Duration
workbook, be sure you are in the Raw_Data
worksheet. The data will not show until you do
the next step. Click on the Click here to Get
Data from USGS discharge file
buttonandvoilayour data should appear. If
not, seek some assistance.
22
Go to the Sample_Data worksheet. Load Duration
Curves have some additional base assumptions The
primary base assumption for Load Duration Curves
is that you have already obtained the raw data
from your database and it is in an Excel
spreadsheet. Clear out any remaining data that
may be left over. There shouldnt be since
youve opened the template that is read only, but
you never know.
23
This is the column that you will eventually
Copy, Paste, Values into your sample date
column. You will have to select them all.
Select the first date and then press and hold
Ctrl Shift Down Arrow then Ctrl C to
copy the data. Next go to the Sample_Data
worksheet in the !WQ Tool(Template).xls
Workbook.
You will need to use this spreadsheet
(AIMS_2_LDC_Format) if dates from your database
include a time stamp with the date. This will
tease apart the date/time data for use in the
Load Duration Spreadsheet. Copy your raw data
here by using Edit, Paste Special, Values.
24
Select the first green cell A2 then Select
Edit, Paste Special, Values, then click OK.
25
Now click the Flow_Data tab at the bottom. You
should see your flow data from the USGS Raw Data
already here. Also, your Drainage area should be
the same as on the first worksheet where you
typed it in. If its not correct, recheck the
Site Info worksheet. You dont need to do
anything else here.
26
Click back to the Sample_Data worksheet, Select
Tools, Macro, Visual Basic Editor, or press Alt
F11.
Note Your Sample Data needs to be in ascending
chronological order or the program will not work.
You can also have multiple same day data.
27
There are several Macros listed here, but we are
only interested in this one. Click your mouse
someplace in this area below End Sub near the
top. Click the Run button, and your sample data
dates will be matched with your flow dates data
and the corresponding flow for your sample date
will be matched. If all goes well you will see
the next screen. If not, then something is wrong
with your dates, e.g. not in ascending order,
sample date within two weeks of raw flow data
dates.
28
If all went well, you will see this screen.
Click OK.
29
After you Click OK from the previous screen,
you will come back here. Close this window out
by clicking on the X in the upper right corner.
30
Now you have all the basic information you will
need to generate a Load Duration Curve. Now is a
good time for you to save your file.
31
Now you will need to copy over the rest of your
data from the AIMS_2_LDC_FORMAT workbook. This
will include sample time, TSS, E. coli, NO2,
Phos. Any or all, depends on what you are
graphing. Note you will be able to look at any
WQ species, this is just a template. You will
need to change the names, but as long as you know
the WQ criteria and it can be measured (lbs/day,
ton/day, lbs/sec, tons/hr etc) you will be able
to modify this spreadsheet to meet your own ends.
32
Remember, to easily select all data, click in the
first cell and then hold down your shift key and
then tap your right arrow key to select across.
When you have done that, continue holding down
the shift key, then press the Ctrl Down Arrow
unit you get to the bottom of your data. If you
go past and end up at the bottom of the
spreadsheet, just tap the Up Arrow Key once while
still holding down the CTRL key and it will move
up to your last data entry. Next, Ctrl C to
copy, switch back the Load Duration spreadsheet
and paste your newly copied data there.
33
Once again Select Edit, Paste Special, Values,
then click OK.
34
You are just about ready to put your data into a
PowerPoint Graph which will show your Load
Duration Curve and plot your sample data.
35
Open your !WQ Analysis(Template) PowerPoint
file. This looks like a lot of data, and it is,
but for starters we are only going to look at
total data and not seasonality, or percentiles,
etc.
36
Bring up your datasheet by double clicking on the
graph or click the graph and select View,
Datasheet.
37
This is where you will copy your Load Data.
38
We now have our WQ Data and were ready to set
our criteria. For the tutorial we are looking at
NO2NO3
39
Youll want to copy the data from this column
into your PowerPoint.
To get the data youll need to change the Season
and Year to include the dates you are interested
in graphing.
40
Notice we dont have any data here to copy. We
need to change the equation in this cell to
point to the cells that have the data we are
interested in e.g. (NO2NO3).
This cell is pointing to cell J8 for data and
we want to look at cell L8, so change the J
to an L. Then Select all the cells in this
column and click Edit, Fill, Down.
41
Now that the cells are being referenced properly,
we can now move the data into PowerPoint. For
this example, were only interested in All
Data, so we will copy that column.
42
We will also need to copy the Flow Rank (), this
is what matches our Sample Load with a Flow. The
Flow Rank () is the X-values, and our All
Data is the Y-values forming the scatter plot
data on our Load Duration Curve.
43
Having copied Flow Rank () data , copy the
All Data to the All Data Column in
PowerPoint. Next well go and get our Load
Duration data from Load Duration Target
worksheet.
44
Here is where we get out Load Duration Curve
data. Some things that you need to be aware
of 1. WQ Criteria is correct for the species
you are looking at. 2. The equation in the
Load column is correct. When this is correct,
select the data in the Load Column and copy it.
45
Paste your Load Duration Curve data in this area.
46
For this example, Im not going to worry about
seasonal, storm flow, 90th, or median, so I will
delete them out.
47
Dont forget to save often. It is always a good
idea after youve moved data.
48
Since were dealing with larger numbers, we can
adjust the Y-axis. Click on the graph, and
double click on the Y-axis to bring up this
screen. You can now adjust the scale
accordingly. This will mess up your vertical
lines, which we will change on the next screen.
49
Notice how our dividing lines are now short. To
fix this, double click the graph or select View,
Datasheet again.
50
Change all the 10000 or 100000 (in this example)
or to what ever your max scale is. This will
move the lines to the proper height.
51
Now, modify all the text like you did for a Flow
Duration Curve and you have a proper Load
Duration Curve ready for presentation.
52
Suggested Reading

• Cleland, Bruce, 2003, TMDL Development
  from the Bottom Up -- Part III Duration Curves
  and Wet-weather Assessments, http//www.tmdls.net/
  tipstools/docs/TMDLsCleland.pdf

53
The End