Automated Validation of Insurance Applications against Calculation Specifications

Fig. 2 shows the schema corresponding to the example CS sheet in Fig. 1. Schema is a representation of the metadata of CS sheet, and it contains the input and output details required for validation of PAS against the corresponding CS sheet. In order to devise a robust schema generation technique, we analyzed various CS sheets and generalized the characteristics of input and output cells. This was done to ensure that BVCS generates schema for CS sheets that do not strictly adhere to their formatting guidelines. Based on our observations, we define input and output cells as follows.

• •

  Input Cell: A cell that is not dependent[6] on any other cell for it’s value, but is referred by other cell(s). Examples, cells B3, B5, C5 etc. in Fig. 1.

• •

  Output Cell: A cell that is not referred by any other cell, but it’s value is calculated based contents of other cell(s). Examples, cells H2 and H3 in Fig. 1.

The algorithm used by BVCS for schema generation is shown in Algorithm 1. To generate schema for a given CS sheet, BVCS firstly reads the contents of all the cells, that aren’t empty, to identify referred cells. These are identified by parsing the formula in each nonempty cell. For example, in Fig. 1 cell H3 has a formula, “=H10-B3-H11”, and hence H10, B3 and H11 are identified as referred cells for H3. In case a cell of the input CS sheet has referred cells belonging to a different CS sheet, then that sheet is tagged as referred sheet. It is worth noting that the example CS sheet in Fig. 1 doesn’t have any referred sheets. The identified referred cells and referred sheets drive the entire process to find input and output cells, which is described as follows.

BVCS maintains the relationship between a cell and it’s referred cell(s) in the form of a directed graph. Each cell (e.g. H3 in Fig. 1) is tagged as ‘destination node’ and an edge is drawn from each of it’s referred cells (H10, B3, H11 in Fig. 1) by tagging them as ‘source nodes’. Next, the edges between each cell and corresponding referred cells are traversed transitively to identify input and output cells as per their aforementioned definition. Input cells are the ones that are never tagged as a ‘destination node’ but are tagged as ‘source node’ at least once. Output cells are the ones that are never tagged as a ‘source node’ but are tagged as ‘destination node’ at least once.

Once all input and output cells are identified, the BVCS captures their respective a) data sources mentioned in the CS sheet (cells C3, E5 etc. in Fig. 1), and b) value format. Lastly, BVCS dumps all collected information in a Comma-Separated Value (CSV) file as shown in 2. The resulting schema file is a lightweight alternative to the input CS sheet and acts as a foundation for gathering data for validating PAS against the input CS sheet. The contents of each row of the schema are described below.

• •

  CS Sheet is the spreadsheet file name and tab name, separated by $, that contains calculation specifications.

• •

  Field Type indicates whether given cell is input cell or output cell.

• •

  Cell ID in which input values are to be entered or output values need to be read from, depending on whether the cell is an input or output cell. Notice that cell IDs for cells that are part of a table (Premium Date and Premium Amount in Fig. 1) are appended with RowWise and ColumnWise depending on the direction in which the respective table expands.

• •

  Data Source from where data for input and output values is to be retrieved. Note that output values are always retrieved from the user interface (UI) of PAS.

• •

  Format of the values to be entered/read, as per the formats supported in Microsoft Excel. Square brackets indicate the decimal precision of numeric formats.

Once the schema is ready, BVCS collects the input and output values for the policy under test from the PAS, based on the sources mentioned in the Data Source row of the schema. To retrieve data from App UI of PAS, we use Selenium scripts[7], and to retrieve data from Database and Config Table we use Python scripts. These data collection scripts are designed to collect data for any given CS sheet based on parameters like UI screen name, field name, database queries etc. These CS-specific parameters need to be provided by domain experts for each CS sheet under test.

For example, to collect value(s) for ‘Policy Value’ (cell B10 in Fig. 1), the domain expert is expected to provide the respective parameterized SQL query[8]. Similarly, the domain expert needs to provide the name of UI screen/page that contains the value of ‘Withdrawal Charge’ (cell H2 in Fig. 1). Based on the provided information, respective Selenium and Python scripts interact with the database and application UI to retrieve input and output values for the policy under test. Collection and execution of input and output values, based on such parameterized information, makes BVCS a low code/no code test automation technique. Therefore, to validate a different input CS sheet, BVCS does not require change in its code. It only needs the parameterized information for collecting data for the different CS sheet.

Once all the input-output values for the policy under test have been retrieved from PAS, BVCS validates the collected data against corresponding CS sheet and it’s referred sheets’ data points, and reports the results. BVCS performs validation by using the CS sheet as a black-box for entering inputs and checking outputs. First, all input values are entered in respective cell IDs, which triggers the intermediate steps that generate expected output values in the respective cell IDs of the CS sheet. In case referred sheets have been identified for the input CS sheet, there is no restriction on the order for entering inputs in the input CS sheet and referred sheets. As long as data is entered in all involved CS sheets, the generated output will always be considered valid.

Next, BVCS compares the expected output generated in CS sheet with the PAS output collected in Step II. The results of comparison are reported to the user via a dashboard that indicates whether the validation for the policy under test has PASSED or FAILED. Additionally, a copy of the filled CS sheet, that contains values for the policy under test, is preserved as an evidence of validation. In this evidence CS sheet, BVCS also highlights the cell(s) for which the value in CS sheet was found to be different from the value in PAS. This helps the BVCS user to quickly identify the data points where validation failed.

The schema is a lightweight alternative to the CS sheet as it contains textual facts about the CS sheet, and omits complex spreadsheet features and formulae. In fact, generation of schema and using it for validating PAS is the key innovative idea in BVCS. Schema generalizes the structure of calculation specifications and as a result domain experts could write parameterized data collection scripts that gave BVCS the flexibility to validate multiple CS sheets through multiple policies. The schema and these parameterized scripts make BVCS a low-code/no-code technique as they enable BVCS to validate different CS sheets without requiring any changes in BVCS code. This fact is exploited in the batch-wise execution mode of BVCS. In this mode, BVCS validates PAS against multiple input CS sheets through corresponding policies in bulk.

In order to properly validate PAS implementation against CS sheets, QA team needs to ensure that all statements, branches and possible execution paths in the PAS implementation are tested. In real-world scenario, this is achieved by using data of policies, relevant to the CS sheet, as test cases for QA. This ensures QA is done on actual test data, instead of randomly generated test data, and also ensures coverage of all data points for validation. Moreover, validating the data of all policies on all relevant CS sheets is necessary for ensuring test coverage and regulatory compliance of PAS. To achieve this, QA team can run BVCS in the batch-wise execution mode to validate multiple policies on multiple CS sheets by providing the list of policies and CS sheets as additional inputs. BVCS batch-wise execution is extremely useful in real-world scenarios where PAS needs to be validated against large number of CS sheets through bulk actual customer policies, as described in detail in Section III.

To evaluate the efforts saved through BVCS, the efforts spent by a tester for end-to-end manual validation of a single insurance policy were compared against the time taken by BVCS to do the same. End-to-end validation comprises of collecting input & output values from respective data source(s) of PAS, filling input values in the CS sheet, and comparing the output values generated by CS sheet with output values collected from PAS.

Table II shows the results of one-to-one comparison of BVCS against manual validation approach. In this experiment, BVCS outperformed manual approach with an average effort savings of approximately 92%. It is worth noting that the efforts shown in Table II do not include the efforts spent by domain experts to provide CS-specific parameters, such as screen name, database query etc., for data collection scripts (refer Step II). This is because both manual and BVCS-based approach depend on the said CS-specific input parameters for end-to-end validation.

To ensure that BVCS is robust and scales on bulk real-world data, 254 insurance policies relevant to CS sheets Easy-3 and Med-3 were validated end-to-end using BVCS. These two CS sheets were picked because they were representative of the common types of CS sheets and also had the maximum number of actual policies readily available.

Table III shows observations from BVCS batch-mode execution and the comparison of BVCS machine efforts with projected efforts in case a tester manually validates the same number of policies. In this experiment, BVCS was able to scale-up on the real-world policies with effort savings of approximately 91%. It is worth noting that,

• •

  BVCS execution time (column ‘BVCS Effort’) shows the time taken for data collection, validation and report generation only, as schema generation was effectively a one-time activity for execution of each CS sheet in batch mode.

• •

  The indicated manual effort is a ball-park estimate. It has been extrapolated by multiplying number of policies with the average manual effort spent by a tester to validate a single policy for respective CS sheet category.

As evident from Table II and III, BVCS is very effective in automatically validating implementation of calculations against their specifications through bulk real-world data. BVCS is also very efficient and, on an average, results in approximately 92% effort savings when compared to existing manual validation approach. Furthermore, the inconsistencies reported by BVCS, for the 12 CS sheets under test, matched the ones reported through manual validation. This makes BVCS a robust and reliable approach for QA of PAS.

The success of BVCS allows TCS QA team to plan acceptance testing of the PAS of a large insurance company on it’s entire book of business. Use of BVCS will enable validating the company’s PAS against thousands of CS sheets using millions of policies. Based on the results of conducted experiment, the insurance company estimates that use of BVCS in all their projects will lead to effort savings of 100 person years and cost savings of US $10 million.