MissRF : A Visual Basic Application in MS Excel to Find out Missing Rainfall Data and Related Analysis

Hydrological analyses are often encountered with many missing periods of rainfall while designing developmental action plans for inaccessible and disadvantageous area. Visual basic based application software (to run in MS Excel) was developed to calculate and autofilled the missing rainfall data using widely followed Normal Ratio Method. The operational details of the software are described in the paper.


Introduction
Continuous meteorological data of contiguous stations are extremely important for forecasting and planning developmental activities in agriculture.Meteorological parameters change erratically at short intervals both spatially and temporally.It's a huge task to accumulate data at decentralised locations, particularly for the developing and under developed countries because of inaccessibility and difficult field situations.Rainfalls being one of the most important meteorological parameters, numbers of statistical methods are in use for filling of the missing rainfall data with varied logical and technical considerations.Dynamics of short term rainfall has a significant role in hydrological planning [1] and the missing periods need therefore be filled up for better analysis, prediction and efficient rainfall-runoff modeling [2].Simple (simple arithmetic average, normal ratio or NR, and NR weighted with correlations) as well as complex type neural network, and multiple imputation strategy adopted by Monte Carlo Markov Chain based on expectation-maximization or EM-MCMC) methods are used for calculation of missing rainfall values [3].[2], [4] and [5] have also mentioned about different logical concepts and mathematical applications, which requires intensive calculations and computations.
Temporal as well as spatial correlation is required for near accurate calculation of missing values of rainfall in a particular station.The more decentralised stations we go, the less difference of rainfall value we may get.Normal ratio method (Equation (1)) is one of the existing conventional methods, suitable for gap filling of the rainfall records particularly when the normal annual precipitation at any index station differs from that of the interpolation station by more than 10% [6] [7].[8] have used this method at various places of the world.The method is one of the simpler ways of predicting missing values [3], but repeating calculations makes it hectic if the number of records is very large. Where

Methods and Materials
An attempt was therefore made to develop a software code using Microsoft-Excel Visual Basic Application for gap filling of the missing rainfall data based on the "Normal Ratio" method.The rainfall of the 12 community development blocks (CDBs) of Kandhamal district in Odisha state of India were used as an example for this.The district is one of the most undeveloped regions of the country and the world as well and therefore has a large number of missing values in terms of rainfall and other metereological parameters even on daily, weekly and monthly basis.

The Software Code
The software comprises 389 lines of code in Visual Basic Application, which is to be copied in the code window of the Excel object (Microsoft Excel 3.1 or later version) in a separate module so that it can run in all open workbooks.It can also be used with creation of a macro button in the Menu bar to run the program.It will not affect the original data file; rather the output file after gap filling was stored in the root C:\ directory with a new file name.The message box at appropriate stages of the program kept the user alert for any apprehension.
The advantage of this code is that, unlike earlier coding procedures like FORTRAN etc.It does not require any particular data format.The rainfall data are to be kept in Excel sheets (separate sheet for individual stations) and the message box at appropriate stages, while running the program, kept the user alert for any apprehension.
The flow chart of the running of the programme is shown in Figure 1.With installation of the software, a new icon will be added to the "Quick Access Toolbar" of the MS Excel sheet at the top of the screen as in Figure 2. The first row and the first column of the excel sheet are for the name of the months and the name of the years, respectively.The actual rainfall data may therefore entered from the "B2" cell (Figure 3).Figures 4, 5 shows the initial message box that appeared just after clicking the "MissRF" icon on toolbar.It displays a set of instructions with the default option of "No".Once "Yes" button was clicked, the program starts executing.Then the program fills the gaps existing in the respective stations in different sheets as per the equation of Normal Ratio Method.The filled in data are then reformatted in a different colour and saved in a different file name not affecting the original file.Soon after the program execution is complete, another message box (Figure 5) appears to indicate the end of program execution.After gap filling by the program, the new Excel file was stored in the root directory C:\ with a new file name having "-Norm" extension to the original file name as shown in Figure 6.The program, not only fills the gaps, but also calculates; year wise total value, monsoon total and yearly average values to be used for further analysis.

Conclusion
The 389 lines code Visual Basic Application software to be used as a Microsoft Excel object is observed to be a

Open Excel File containing the original
Rainfall records with missing data.
Organize the file so that each sheet contains Rainfall records of one station only.
Change the sheet name according to the station name.
Arrange the Rainfall records, so that Column-A contains name of years and Row-1 contains name of months.
Press the Macro-button that has been created on the Toolbar (Else: Developer\macros\MissRF) to run the program.vantage of this code is that it does not affect the original data file; rather the output file after gap filling is stored in the root C:\ directory with a new file name.The message box at appropriate stages of the program keeps the user alert for any apprehension.The above codes are operative for finding out the missing monthly data.But for the missing daily data, the program needs to be redesigned.

Figure 1 .
Figure 1.Flow chart of running of the programme (MissRF) to calculate missing rainfall.

Figure 4 .
Figure 4. Message box before program execution.

Figure 5 .
Figure 5. Message box after end of program execution.

Figure 6 .
Figure 6.Rainfall data series after gap filling.
, x P = Missed rainfall of Station-x to be filled; x N = Annual Average rainfall of Station-x ; 1 P ,  , respectively; n = number of stations for which rainfall data are available.