A Visual Basic Spreadsheet Macro for Recession Curve Analysis

ComputerNote/AVisualBasicSpreadsheetMacroforRecessionCurveAnalysisˇani2,andZoranNakic´2byKristijanPosavec1,AndreaBacAbstractAVisualBasicprogramforanExcelspreadsheetwaswrittentoconstructamasterrecessioncurve(MRC),usingtheadaptedmatchingstripmethod,gramusesfivedifferentlinear/nonlinearregressionmodelstoagramcanalsobeusedtoanalyzetherecessionsegmentsofothertimeseries,amplesoffielductionRecessionanalysiscanbetracedbacktoBoussinesq(1877)andMaillet(1905)whopresentedquantitativeanalysisofhydrographreces-pleexponentialequationhasalsobeenusedinmanyempiricalstudiesandistodaythemostwidelyusedequa-tionforrecessionanalysis(Tallaksen1995).TherecessioncurveanalysisextractsvaluableinertandLopez(1998)presentedarecessionslopeanalysisoflow-streamflowhydrogrsen(1995)ionanalysishasprovenusefulinmanyareasofwaterresourceplanningandmanagement,suchaslow-flowforecastingforthemanagementofirrigation,watersupply,hydroelectricpowerplants,:FacultyofMining,GeologyandPetroleumEngineering,DepartmentofGeologyandGeologicalEngineering,UniversityofZagreb,Pierottijeva6,Zagreb,10000;kposavec@2FacultyofMining,GeologyandPetroleumEngineering,DepartmentofGeologyandGeologicalEngineering,UniversityofZagreb,Pierottijeva6,Zagreb,tedOctober2005;ghtª2006TheAuthor(s)Journalcompilationª:10.1111/j.1745-6584.2006.00226.x1CorrespondingInanygivensystem,suchasanaquiferorstream,individualrecessionsegmentsrecordedatdifferenttimesmayhavedifferentslopesdependingonthevariabilityinstorage,evaporationloss,cases,however,itispossibletocompileindividualreces-sionsintoasinglerecessioncurvethatprovidesanaveragecharacterizationofhead,flow,orotherobservedvariableresponse:theresultingconstructionistermedamasterrecessioncurve(MRC).Recessionratesarestronglyin-fluencedbytheantecedentconditionsofthesystem,andthustheMRCrepresentsthemostprobablerecessionscenariounderagivensituation(NathanandMcMahon1990).Traditionally,graphicalmethodshavebeenusedtoconstructanMRC(Toebesetal.1969).Thetwomostcommonlyusedmethodsarethematchingstripmethod(Snyder1939)andthecorrelationmethod(Langbein1938).Inthematchingstripmethod,whichisbaseduponthesimpleexponentialmodel,individualrecessionseg-mentsareplottedandadjustedhorizontallyuntiltheyoverlapinthemainparts(ToebesandStrang1964).TheMRCisconstructedbyvisuallyfittingamodelfandMcMahon(1990)presentedapro-cedureadaptedforsemiautomatedprocessingonacom-puterthatextractsvariable-lengthrecessionperiodsfromthestrearatortheninteractivelyshiftsindividualrecessionsalongtheordinateaxisuntilallthebaseflowrecessionsoverlapinthedesiredfashion.764Vol.44,No.5—GROUNDWATER—September–October2006(pages764–767)

Arnoldetal.(1995)cedurewasdesignedtoautomateamethodtopredicttheslopeofthebaseflowrecessionsusinglinearreptedmatchingstripprocedurepresentedinthispaperusesfivedifferentlinear/nonlinearrtoricalacceptanceandawideuseofasingleexponentialrecessionfunctioninrecessionanaly-siscanperhapsbepartlyattributedtotheeaseofcon-structionandinterpretationofsemilogarithmicplots,sincetherecessionsthatobeytheexponentialdecayfunctionplotasastraightlineonsemilogarithmicgraphpaper(NathanandMcMahon1990).Asthesimpleexponentialequationgenerallydoesnotsatisfactorilyrepresentreces-sionoverawiderangeofconditions,aVisualBasic(VB)spreadsheetmacrowasco,linear,loga-rithmic,second-orderpolynomial,andpoweriacro,withtheautomaticandobjec-tivemethods,,MSExcel)iswidelyaccessible,anmDesignTheVBmacrosimulatesagraphicalmethodofsuperimposingrecessionsegmefiningthedata-processingperiodandstoringthedatasetinanewspreadsheet,thefirstcomputationalstepistimeseriessegmentation(Figure1),inwhichthecon-tinuoustimeseriesisdiionsegmentsarethenranksortedfromthehighets’sortingisfollowedbydateconversionthatconvertsabsessionsegmentwiththehighestinitialvalue,hereafterreferredtoasthefirstrecessionsegment,istestedwithfivedifferentregressionmodels(trendlines)availableinExcel(‘‘Cal-culateA’’inFigure1),i.e.,linear(y¼ax1b),loga-rithmic(y¼alnx1b),second-orderpolynomial(x¼ay21by1c),power(y¼bxa),andexponential(y¼beax).Notethatydesignatesgroundwaterlevel,ond-orderpolynomialregressionmodeltakestheformx¼f(y)becausey¼f(x)hasaten-dencytobendupwardatthelowestvaluesofrecessionsegments,elthatbestfitsthefirstrrionusedbycomputerprogramstoselectthemostappropriatemodeliscoefficientofdeterminationR2(Kirkup2002;MontgomeryandRunger2003).Someauthorsrefertothiscoefficientas‘‘goodnessoffit’’(Davis2002).efiningthefirstregressioncurve,thereces-sionsegmentthathasthesecondhighestinitialvalue(thesecondrecessionsegment)istranslatedtoitsproperposi-tioninthefirstregressioncurve(‘‘SegmentTranslation’’inFigure1)inthefollowingmanner:(1)programcalcu-latesthetimeshiftrequiredtoplacetheinitialpointofthesecondrecessionsegmentonthefirstregressioncurve;forexample,ifthefirstregressioncurveisalogarithmicfunction(y¼alnx1b),thetimeshift(xÞ=a2)isgivenbyx2¼eðy22b,wherey2istheinitialvalueofthesecondrecessionsegment;(2)thematchingrelativetimesfortherestofthesecondrecessionseextstep(‘‘CalculateB’’inFigure1),theregressionanalysisisappliedtothecompositeofthefirstandsecondsegmentsusingallfivemodels,andthemostappropriateregressionmodel(thesecondregres-sioncurve)rdrecessionsegmentisadjustedtoitsproperpositioninthesecondregressioncurveinthesamemannerasdescribedpreviouslWATER44,no.5:764–767765

dsheetexampleoftheMRCofthegroundwaterhydrographforanobservationwelllocatedinanuncon-finedalluvialaquifer,Zagreb,tepsarerepeated(‘‘SegmentsLoop’’inFigure1)y,thecom-positeofallrecessionsegmentsisdescribedbythemostappropriateregressionmodel,gramproducesagraphwithalloverlappingrecessionsegmentsandanMRCattheendofthepro-cessing(‘‘Chart’’inFigure1).Suchagraphenablesavisualcheckastowheaneffec-tivewaytoexaminethegoodnessoffitbecausethecoef-ficientofdeterminationR2canbelargeevenwhenthelinear/nonlinearapproximationispoor(MontgomeryandRunger2003).ExamplesThefirstexample(Figure2)illustratesanMRCofanobservationwelllocatedinanalluvialaquiferintheareaofZagreb,,sD,E,andFareconstantsofˇspring,Istria,WATER44,no.5:764–767

theregressionequation,thecoefficientofdetermination,R2,wasobtainedbyprocessinga10-year(1994to2003)ondexample(Figure3)illustratesanMRCoftheBulazˇkarstspring,es-sioncurvewasobtainedbyprocessingan8-yeartimeseries(1994to2001)sionsAVBprogramstexampleshowstheMRCofanobservationwelllocatedinanunconfinedalluvialaquifer,tiontoitsuseforprocessingthetimeseriesofindividualobjects,afterminormodificationstheprogramcanalsobeusedforautomatedprocessingoftimeseriesmeasuredatasetofnobjects,ptedmatchingstripmethodpresentedinthispaperusesfivedifferentlinear/nonlinearregressiohodisfast,reliable,sureAvailabilityTheExcelspreadsheetwithitsopen-accessVBmaledgmentsTheauthorsthankSeanFleming,JohnGuswaandespeciallyoneanonymousreviewerformakinganumberofveryhelpfulsuggestions,ch-Miletic,c,rkhasbeensupportedthroughtheprojectRecordingofDataancesArnold,J.G.,,h,Water33,no.6:1010–nesq,urlathe´´moirespre´sente´spardiverssavantsal’AcademiedesSciencesdel’III,no.1:1-680.(CitedbyHall1968)Brutsaert,W.,-scalegeohydro-logesourcesResearch34,no.2:233–,ticsandDataAnalysisinGeology,k:JohnWiley&,owrecessions—esourcesResearch4,no.5:973–,alysiswithExcelÒ.dge,U.K.:in,annelst,Transactions,AmericanGeophysicalUnion19,435–t,’,France:LibrarieSci.,.(CitedbyHall1968)Montgomery,C.M.,dStatisticsandProbabilityforEngineers,k:JohnWiley&,R.J.,esourcesResearch26,no.7:1465–,,Transactions,AmericanGeophysicalUnion20,725–sen,lofHydrology165,no.1–4:349–,C.,sey,r,gton,NewZealand:MinistryofWorks.(CitedbyTallaksen1995)Toebes,C.,ssioncurves,1—lofHydrology(NewZealand)3,no.2:2–15.(CitedbyTallaksen1995)WATER44,no.5:764–767767