Transcript No Slide Title

Load Duration Curves:
Spreadsheet 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
2America’s
Clean Water Foundation
750 First Street N.E. Suite#1030
Washington, D.C. 20002
IDEM 32/02/084/2003
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:
[email protected]
– Issues regarding Flow Duration
concepts contact:
[email protected]
• 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.
2
Go to http://waterdata.usgs.gov/nwis/sw This is where you will
get your flow data. Click the Streamflow button.
3
Click the State box under Site Location, then click Submit.
4
Select Your State and click Submit
5
Find the gauging station
nearest to your sampling area
and click on the Site Number.
6
For example purposes, I’ve 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.
7
When you get to this screen, drop down
the “Available data for this site” and
Select “Station Home Page”.
8
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 [email protected].
2. Select Edit and then Copy from the
menu bar, or press “Ctrl + C”.
9
This is what the screen will look like
when you select the data.
10
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 Ctrl+V.
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.
11
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
won’t need to retype them again.
If you Copy, Paste the above
information from the USGS
Website, you should use Paste
Special, Values.
12
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 won’t do
anything here just yet.
13
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”
14
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.
15
I’ve 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.
16
The computer will tell you the
file exists and “Do you want
to replace it?”. Click “Yes”.
17
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.
18
Sometimes Windows
won’t know which
program to open the file
in and you will get the
“Open With” window.
Simply select Microsoft
Excel and click “OK”.
19
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.
We’ll save it when you have moved
your raw data (and sample data if
doing a Load Duration Curve).
20
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” button…and…voila…your data should
appear. If not, seek some assistance.
21
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 shouldn’t be since you’ve
opened the template that is read only, but
you never know.
22
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.
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”.
Next go to the
“Sample_Data”
worksheet in the “!WQ
Tool(Template).xls”
Workbook.
23
Select the first
green cell “A2”
then Select Edit,
Paste Special,
“Values”, then
click “OK”.
24
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 it’s not correct, recheck the
“Site Info” worksheet.
You don’t need to do anything else here.
25
*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.
Click back to the
Sample_Data
worksheet, Select
Tools, Macro,
Visual Basic
Editor, or press
“Alt + F11”.
26
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.
27
If all went well, you
will see this screen.
Click “OK”.
28
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.
29
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.
30
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.
31
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.
32
Once again Select
Edit, Paste Special,
Values, then click
“OK”.
33
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.
34
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.
35
Bring up your datasheet
by double clicking on
the graph or click the
graph and select View,
Datasheet.
36
This is where
you will copy
your Load
Data.
37
We now have our WQ
Data and we’re ready to
set our criteria. For the
tutorial we are looking
at NO2+NO3
38
You’ll want
to copy the
data from
this column
into your
PowerPoint.
To get the data you’ll
need to change the
Season and Year to
include the dates you
are interested in
graphing.
39
Notice we don’t 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. (NO2+NO3).
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.
40
Now that the cells are
being referenced
properly, we can now
move the data into
PowerPoint. For this
example, we’re only
interested in “All Data”,
so we will copy that
column.
41
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.
42
Having copied “Flow Rank
(%)” data , copy the “All
Data” to the “All Data”
Column in PowerPoint.
Next we’ll go and get our
Load Duration data from
“Load Duration Target”
worksheet.
43
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.
44
Paste your Load
Duration Curve
data in this
area.
45
For this example, I’m
not going to worry
about seasonal, storm
flow, 90th, or median, so
I will delete them out.
46
Don’t forget to save
often. It is always a
good idea after you’ve
moved data.
47
Since we’re dealing with
larger numbers, we can
adjust the Y-axis. Click
on the graph, and
double click on the Yaxis 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.
48
Notice how our dividing
lines are now short. To
fix this, double click the
graph or select View,
Datasheet again.
49
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.
50
Now, modify all the text
like you did for a Flow
Duration Curve and you
have a proper Load
Duration Curve ready for
presentation.
51
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
52
The End
53