A visual basic spreadsheet macro for recession curve analysis

A Visual Basic program for an Excel spreadsheet was written to construct a master recession curve (MRC), using the adapted matching strip method, for recession analysis of ground water level time series. The program uses five different linear/nonlinear regression models to adjust individual recession segments to their proper positions in the MRC. The program can also be used to analyze the recession segments of other time series, such as daily stream discharge or stage. Some examples of field data from Croatia are used to illustrate the usefulness of its application.     

Method and Excel VBA Algorithm for Modeling Master Recession Curve Using Trigonometry Approach

A new method was developed and implemented into an Excel Visual Basic for Applications (VBAs) algorithm utilizing trigonometry laws in an innovative way to overlap recession segments of time series and create master recession curves (MRCs). Based on a trigonometry approach, the algorithm horizontally translates succeeding recession segments of time series, placing their vertex, that is, the highest recorded value of each recession segment, directly onto the appropriate connection line defined by measurement points of a preceding recession segment. The new method and algorithm continues the development of methods and algorithms for the generation of MRC, where the first published method was based on a multiple linear/nonlinear regression model approach (Posavec et al. 2006). The newly developed trigonometry‐based method was tested on real case study examples and compared with the previously published multiple linear/nonlinear regression model‐based method. The results show that in some cases, that is, for some time series, the trigonometry‐based method creates narrower overlaps of the recession segments, resulting in higher coefficients of determination R², while in other cases the multiple linear/nonlinear regression model‐based method remains superior. The Excel VBA algorithm for modeling MRC using the trigonometry approach is implemented into a spreadsheet tool (MRCTools v3.0 written by and available from Kristijan Posavec, Zagreb, Croatia) containing the previously published VBA algorithms for MRC generation and separation. All algorithms within the MRCTools v3.0 are open access and available free of charge, supporting the idea of running science on available, open, and free of charge software.     

A new approach to master recession curve analysis

Abstract

The analysis of drought discharge is of utmost relevance in the design of water intake structures, management of water resources, and in coping with environmental issues. In this context, the master recession curve represents a tool in hydrological analysis, giving integrated information on long period drought flow rates. In this paper, a technique is developed for deriving a master recession curve directly from daily discharge series that takes into account the high variability in the behaviour of individual recession segments. The statistical framework developed allows us to explicitly represent uncertainty, and hence a novel interpretation of the master recession curve is derived. The method is successfully applied to three important Italian basins draining the southern slopes of the eastern Alps.

Citation Fiorotto, V. and Caroni, E., 2013. A new approach to master recession curve analysis. Hydrological Sciences Journal, 58 (5), 966–975.     

Quantifying flows along hydrological pathways by applying a new filtering algorithm in conjunction with master recession curve analysis

Quantifying the proportion of the river hydrograph derived from the different hydrological pathways is essential for understanding the behaviour of a catchment. This paper describes a new approach using the output from master recession curve analysis to inform a new algorithm based on the Lyne and Hollick ‘one‐parameter’ signal analysis filtering algorithm. This approach was applied to six catchments (including two subcatchments of these) in Ireland. The conceptual model for each catchment consists of four main flow pathways: overland flow, interflow, shallow groundwater and deep groundwater. The results were compared with those of the master recession curve analysis, a recharge coefficient approach developed in Ireland and the semi‐distributed, lumped and deterministic hydrological model Nedbør‐Afstrømings‐Model. The new algorithm removes the ‘free variable’ aspect that is typically associated with filtering algorithms and provides a means of estimating the contribution of each pathway that is consistent with the results of hydrograph separation in catchments that are dominated by quick response pathways. These types of catchments are underlain by poorly productive aquifers that are not capable of providing large baseflows in the river. Such aquifers underlie over 73% of Ireland, ensuring that this new algorithm is applicable in the majority of catchments in Ireland and potentially in those catchments internationally that are strongly influenced by the quick‐responding hydrological pathways. Copyright © 2013 John Wiley & Sons, Ltd.     

A spreadsheet method of estimating best-fit hydraulic gradients using head data from multiple wells

Hydraulic gradients from planar water tables, or piezometric surfaces, and horizontal flow regimes can be quickly and conveniently calculated from data sets involving numerous wells. The matrix-solving functions of a modem spreadsheet program (Excel) were used to determine the equation of a water-table plane, Ax + By + Cz - D = 0, and the equation coefficients were then used to determine the magnitude of the hydraulic gradient, according to gradient = square root of A2 + B2/C2, and its direction, according to alpha = arctan B/A, where alpha is the angle measured from the x-axis. A pre-prepared Excel file constructed to handle data from up to 20 wells at once is available for free downloading at www.geo.ku.edu/hydro/KUHydro.html.     

Excel VBA数据处理软件开发

ExcelVBA编程通过对Excel自身功能的集成和扩展可以较为快捷地形成一个完整的数据处理软件。本文以地下流体数据处理软件开发为实例,简要论述了ExcelVBA开发数据处理软件的思路、方法和技术要点,同时还列举了一些通用代码供大家参考和使用。     

Variable water‐saturated areas and streamflow generation in the small Ringelbach catchment (Vosges Mountains,France): the master recession curve as an equilibrium curve for interactions between atmosphere,surface and ground waters

In the small Ringelbach research catchment, where studies on the water cycle components in a granitic mountainous environment have been conducted since 1976, the water‐saturated areas that are hydraulically connected to the outlet play a major role in the streamflow generation, as it is here that complex interactions between atmosphere, surface and ground waters take place. During baseflow recession periods, which may last several months between two groundwater recharge events, the atmospheric inputs of water and energy on these contributing areas only explain the streamflow fluctuations observed around the master recession curve, which defines the groundwater contribution: fluctuating above it in the case of precipitation input on these areas, below it in the case of evaporation output from these areas. Streamflow may therefore largely deviate from the master recession curve in the case of long, hot, dry spells. Detailed mapping has shown that their variable extent is well related to baseflow by a loglinear curve. On the other hand, a synthetic master recession curve, well fitted by a second‐order hyperbolic function, has been obtained from numerous pure recession periods. Both based on these two curves, a simple procedure and a simple model have been used to (i) validate the hypothesis that the connected saturated areas are the only permanent variable contributing areas and (ii) simulate the daily streamflow volumes over long baseflow recession periods by a water balance of the aquifer below these areas only. The storm runoff ratio for small to moderate rainfall events is indeed corresponding to the catchment saturated fraction at that time. The volume of daily streamflow oscillations is indeed corresponding to the evaporation at the potential rate from the saturated areas only. In both cases, streamflow naturally tends towards the master recession curve after the end of any atmospheric perturbation. Introducing these findings into TOPMODEL led to significantly improved simulation results during baseflow recession periods. The master recession curve may therefore be considered as a dynamic equilibrium curve. Together with the relationship between saturated extent and baseflow, it provides the main characteristics necessary to understand and model the interactions at this complex interface and the resulting daily streamflow variations during baseflow recession periods in this type of catchment. Copyright © 2016 John Wiley & Sons, Ltd.     

A spreadsheet program for two-well tracer test data analysis

Two-well tracer tests are often conducted to investigate subsurface solute transport in the field. Analyzing breakthrough curves in extraction and monitoring wells using numerical methods is nontrivial due to highly nonuniform flow conditions. We extended approximate analytical solutions for the advection-dispersion equation for an injection-extraction well doublet in a homogeneous confined aquifer under steady-state flow conditions for equal injection and extraction rates with no transverse dispersion and negligible ambient flow, and implemented the solutions in Microsoft Excel using Visual Basic for Application (VBA). Functions were implemented to calculate concentrations in extraction and monitoring wells at any location due to a step or pulse injection. Type curves for a step injection were compared with those calculated by numerically integrating the solution for a pulse injection. The results from the two approaches are similar when the dispersivity is small. As the dispersivity increases, the latter was found to be more accurate but requires more computing time. The code was verified by comparing the results with published-type curves and applied to analyze data from the literature. The method can be used as a first approximation for two-well tracer test design and data analysis, and to check accuracy of numerical solutions. The code and example files are publicly available.     

A User Interface for the Kansas Geological Survey Slug Test Model

The Kansas Geological Survey (KGS) developed a semianalytical solution for slug tests that incorporates the effects of partial penetration, anisotropy, and the presence of variable conductivity well skins. The solution can simulate either confined or unconfined conditions. The original model, written in FORTRAN, has a text-based interface with rigid input requirements and limited output options. We re-created the main routine for the KGS model as a Visual Basic macro that runs in most versions of Microsoft Excel and built a simple-to-use Excel spreadsheet interface that automatically displays the graphical results of the test. A comparison of the output from the original FORTRAN code to that of the new Excel spreadsheet version for three cases produced identical results.     

A spreadsheet-based technique (Lotus 1-2-3) for separating tropical forest storm hydrographs using Hewlett and Hibbert's slope

The inclined line separation technique of Hewlett and Hibbert has been widely adopted to separate delayed flow from the total stream storm runoff. Presented here is the application of the technique to highly responsive storm hydrographs using a personal computer method based on a Lotus 1-2-3 spreadsheet. Using discharge measurements (in m³ s⁻¹), catchment area (in km²) and time (in Julian days), the separation slope is adjusted on the monitor screen until the precise time at which the end of quickflow as storm runoff gives way to delayed flow may be established. The application of the inclined line method is compared with other separation techniques applied to the same dataset. The annual stream quickflow runoff for the study catchment was calculated by the four different separating lines — (i) best-fit curve, (ii) N-day after peak, (iii) inclined line and (iv) horizontal line — was 250, 312, 368, and 588 mm, amounting to 33, 31, 51 and 78 per cent respectively of the annual total stream runoff. Separation of flow by computer spreadsheet methods may be consistently applied throughout a dataset and therefore have a comparative advantage over more arbitrary techniques. © 1997 John Wiley & Sons, Ltd.     

基于VBA开发的电磁波观测资料管理软件

本地震前兆电磁波观测数据处理软件（DCB软件）是一个基于Excel数据平台,用VBA编写的管理软件。本软件具有数据自动录入、数据库管理、绘图和打印等功能。以Excel作为数据处理平台的DCB软件具有以下优点：操作简单、数据直观、制作图表方便、便于数据管理、分析统计、传递交流,等等。VBA程序则具有容易学习、理解,方便修改等优点。     

A comparison of techniques for hydrograph recession analysis

A comparison between commonly used techniques for hydrograph recession analysis, namely the semi‐logarithmic plot of a single recession segment, the master recession and a relatively new approach based on wavelet transform was carried out. These methods were applied to a number of flood hydrograph events of two catchments in West Java, Indonesia. The results show that all the methods tested produce reasonable and comparable results. However, problems arise in the semi‐logarithmic plot and the master recession, i.e. determining the recession parameter K is not an easy task especially where the plotted data on a semi‐logarithmic plot is not a linear but a curved line. On a curved line, the end of direct flow or starting point of baseflow is not clear and it is quite difficult to identify. Hence, the best line as a basis for computing the recession parameter K becomes uncertain. The wavelet transform approach, however, produces promising results and minimizes a number of problems associated with hydrograph recession analysis. The end of direct flow and the location of the baseflow component are easily determined through the wavelet maps. Copyright © 2004 John Wiley & Sons, Ltd.     

基于Excel VBA的开发技术在重型动力触探试验中的应用

通过Excel VBA宏语言程序来解决重型动力触探试验的成果分析和自动化出图的问题,在工程实践中得到验证.将Excel强大的电子表格计算能力与Excel VBA增加的自动功能相结合,可方便地将试验结果输出为模版式图表,使试验成果管理得到规范和系统化,有利于开发程序所应用专业的技术推广或前沿拓展.从系统分析、软件工程的开...     

An Excel Macro to Plot the HFE‐Diagram to Identify Sea Water Intrusion Phases

A hydrochemical facies evolution diagram (HFE‐D) is a multirectangular diagram, which is a useful tool in the interpretation of sea water intrusion processes. This method note describes a simple method for generating an HFE‐D plot using the spreadsheet software package, Microsoft Excel. The code was applied to groundwater from the alluvial coastal plain of Grosseto (Tuscany, Italy), which is characterized by a complex salinization process in which sea water mixes with sulfate or bicarbonate recharge water.     

Recession slope curve analysis under human interferences

To study the base flow recession at the watershed scale, the log-scale plot of − dQ/dt ∼ Q proposed by Brutsaert and Nieber [10] has been used to estimate the recession parameters, i.e., the slope and interception of the theoretical recession slope curve. The lower envelope or the best fit in some studies is usually used to determine the recession slope curve for natural watersheds. However, human interferences exist in most watersheds around the world. This paper discusses the impact of human interferences, which include groundwater pumping, water diversion and return flow, on the determination of the recession slope curve and the cloud shape of the data points of − dQ/dt ∼ Q. First, values of − dQ/dt generated for hypothetical watersheds are analyzed. Then real data for three watersheds in Illinois is analyzed to verify the hypothetical analysis. The placement of the recession slope curve depends on the coexistence and relative amount of the evapotranspiration, groundwater pumping or even water diversion if it exists, and the return flow. When the water consumption rate is small, the recession slope curve can even be located at the upper envelope of the cloud of points representing historical data. These results suggest that the use of the lower envelope as a guideline for estimating recession parameters for watersheds subject to human interferences can result in biased estimates.     

Analysis of nonstationarity in low flow in the Loess Plateau of China

An approach for nonstationary low‐flow frequency analysis is developed and demonstrated on a dataset from the rivers on the Loess Plateau of China. Nonstationary low‐flow frequency analysis has drawn significant attention in recent years by establishing relationships between low‐flow series and explanatory variables series, but few studies have tested whether the time‐varying moments of low flow can be fully described by the time‐varying moments of the explanatory variables. In this research, the low‐flow distributions are analytically derived from the 2 basic explanatory variables—the recession duration and the recession coefficient—with the assumption that the recession duration and recession coefficient variables follow exponential and gamma distributions, respectively; the derived low‐flow distributions are applied to test whether the time‐varying moments of explanatory variables can explain the nonstationarities found in the low‐flow variable. The effects of ecosystem construction measures, that is, check dam, terrace, forest, and grassland, on the recession duration and recession coefficient are further discussed. Daily flow series from 11 hydrological stations from the Loess Plateau are used and processed with a moving average technique. Low‐flow data are extracted following the pit under threshold approach. Six of the 11 low‐flow series show significant nonstationarities at the 5% significance level, and the trend curves of the moments of low flow are in close agreement with the curves estimated from the derived distribution with time‐dependent moments of the recession duration and time‐constant moments of the recession coefficient. It is indicated that the nonstationarity in the low‐flow distribution results from the nonstationarity in the recession duration in all 6 cases, and the increase in the recession duration is resulted from large‐scale ecosystem constructions rather than climate change. The large‐scale ecosystem constructions are found to have more influence on the decrease in streamflow than on the increase in watershed storage, thus resulting in the reduction of low flow. A high return period for the initial fixed design value decreases dramatically with an increasing recession duration.     

Calculating basal temperatures in ice sheets: an Excel spreadsheet method

The flow of ice sheets and their geomorphological impact is greatly influenced by their basal thermal regime. Calculations of basal temperatures in ice sheets are therefore fundamental in evaluating glacier dynamics and in determining the spatial distribution of zones of erosion and deposition beneath ice masses. Calculations of basal temperatures are not frequently attempted, however, primarily because of the techniques required to solve the heat conduction equation between the ice surface and the base. This paper describes a new Excel spreadsheet method of solving this equation that can readily be applied to both former and contemporary ice sheets. The application of the spreadsheet is illustrated with two examples. The first provides a calculation of basal thermal regime beneath the north eastern part of the Scottish ice sheet during the last glacial maximum; the second shows how basal ice temperatures can be calculated beneath the modern Antarctic ice sheet. Copyright © 2002 John Wiley & Sons, Ltd.     

A computer program for flow-log analysis of single holes (FLASH)

A new computer program, FLASH (Flow-Log Analysis of Single Holes), is presented for the analysis of borehole vertical flow logs. The code is based on an analytical solution for steady-state multilayer radial flow to a borehole. The code includes options for (1) discrete fractures and (2) multilayer aquifers. Given vertical flow profiles collected under both ambient and stressed (pumping or injection) conditions, the user can estimate fracture (or layer) transmissivities and far-field hydraulic heads. FLASH is coded in Microsoft Excel(5) with Visual Basic for Applications routines. The code supports manual and automated model calibration.     

A multi‐storage groundwater concept for the SWAT model to emphasize nonlinear groundwater dynamics in lowland catchments

Hydrological models are useful tools to analyze present and future conditions of water quantity and quality. The integrated modelling of water and nutrients needs an adequate representation of the different discharge components. In common with many lowlands, groundwater contribution to the discharge in the North German lowlands is a key factor for a reasonable representation of the water balance, especially in low flow periods. Several studies revealed that the widely used Soil and Water Assessment Tool (SWAT) model performs poorly for low flow periods. This paper deals with the extension of the groundwater module of the SWAT model to enhance low flow representation. The current two‐storage concept of SWAT was further developed to a three‐storage concept. This was realized due to modification of the groundwater module by splitting the active groundwater storage into a fast and a slow contributing aquifer. The results of this study show that the groundwater module with three storages leads to a good prediction of the overall discharge especially for the recession limbs and the low flow periods. The improved performance is reflected in the signature measures for the mid‐segment (percent bias ?2.4% vs ?15.9%) and the low segment (percent bias 14.8% vs 46.8%) of the flow duration curve. The three‐storage groundwater module is more process oriented than the original version due to the introduction of a fast and a slow groundwater flow component. The three‐storage version includes a modular approach, because groundwater storages can be activated or deactivated independently for subbasin and hydrological response unit level. Copyright © 2013 John Wiley & Sons, Ltd.     

An Excel macro for generating trilinear plots

This computer note describes a method for creating trilinear plots in Microsoft Excel. Macros have been created in MS Excel's internal language: Visual Basic for Applications (VBA). A simple form has been set up to allow the user to input data from an Excel worksheet. The VBA macro is used to convert the triangular data (which consist of three columns of percentage data) into X-Y data. The macro then generates the axes, labels, and grid for the trilinear plot. The X-Y data are plotted as scatter data in Excel. By providing this macro in Excel, users can create trilinear plots in a quick, inexpensive manner.