Automating Payment Generation and Validation in QA: A Journey from Version 1.0 to 2.0 with Excel and VBA Introduction

Robot representing excel based automation — Excel Automation

Introduction:

In the world of Quality Assurance (QA), manual data preparation can be a bottleneck—especially when dealing with complex payment workflows that require precision and speed.

My experience with generating payment XML files, managing responses, and tracking the progress of test cases highlighted the need for automation to ensure accuracy and efficiency.

To address this, I developed a custom tool using Excel and VBA, evolving it through different versions to handle data generation, logging, validation, and more. In this post, I’ll share how version 1.0 of the tool started as a basic payment generator, and how version 2.0 became a more advanced solution with XML templates, manual editing capabilities, and direct integration with WinSCP.

Version 1.0 – Payment Generation, Unique IDs, and Logging

The initial version of the tool was designed to tackle the most pressing challenge I faced: the need to generate large volumes of XML files quickly and consistently, avoiding manual errors and ensuring that each payment was uniquely identifiable.

Key Features of Version 1.0:

Payment Generation (Initializing Files): The tool automated the creation of payment XML files based on the specific payment type. This eliminated the need to manually craft each XML file.
Response Files Generation: The tool also generated corresponding response XML files based on the system’s feedback after a payment was injected (e.g., returns, recalls, approvals). These files were saved for later validation.
Unique IDs: Each payment required a unique IDs (MsgID, TransactionId, EndToEndId and more) for tracking purposes. The tool automatically generated these IDs, ensuring that every XML file could be traced throughout the testing process.
Basic Logging: I included a simple log to track payment files and their associated IDs. This log provided a basic structure in the first column, with details about the payment type, timestamp, and other metadata, allowing for manual updates.

Example VBA Snippet for Payment Generation in Version 1.0:

Most basic implementation included excel sheet. Versions from 1.5 used VBAForms

vba```
Sub GeneratePaymentXML_V1()
	Dim IBAN As String, BIC As String, PaymentType As String, MsgID As String 
	IBAN = Range("B1").Value 
	BIC = Range("B2").Value 
	PaymentType = Range("B3").Value 
	' Generate unique MsgID for the payment 
	MsgID = "PAY_" & Format(Now(), "YYYYMMDDHHMMSS") & "_" & Int(Rnd  1000) 
	' Generate payment XML 
	Dim XMLContent As String XMLContent = "<Payment><MsgID>" & MsgID & "</MsgID><IBAN>" & IBAN & "</IBAN><BIC>" & BIC & "</BIC><Type>" & PaymentType & "</Type></Payment>" 
	
	' Save XML file 
	Dim FileName As String FileName = Application.GetSaveAsFilename("Payment_" & PaymentType & ".xml", "XML Files (.xml), *.xml") Open FileName For Output As #1 Print #1, XMLContent Close #1 
	
	' Log the payment generation Sheets("Log").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = MsgID 
	MsgBox "Payment XML generated successfully!" 
End Sub```

Section 2: Version 2.0 – Enhanced Features for Flexibility and Automation

While version 1.x automated the basics, I quickly found the need for more advanced features that could handle greater complexity and give more control over the generated files. Version 2.0 introduced additional features like XML templates, XSD validation, and manual editing to further streamline the process.

Key Features of Version 2.0:

XML Templates for Common Payments: In this version, I introduced predefined XML templates that allowed users to quickly generate files for the most commonly used payment types. For example:
- Valid Payment Type A (Version 1)
- Broken Payment Type A
- Valid Payment Type A (Version 2)
These templates were stored within the tool and could be selected from a dropdown to generate payments without having to redefine common fields every time.
Manual Preview and Editing: One of the most requested features was the ability to preview the generated XML file and make manual adjustments before finalizing it. This was especially useful for edge cases where human oversight was needed to adjust details (or to break the payment before creating- negative cases). The user could load the XML into a preview pane, make changes, and then print the final version.
XSD Validation: To ensure that the generated XML files adhered to the correct format, I added an XSD validation feature. The tool could validate the XML against an XSD schema file if one was provided. If no schema was specified, the validation step would be skipped.
Example of XSD Validation Code:

vba```
Sub ValidateXML_V2() 
	Dim xmlDoc As Object 
	Dim xsdFile As String 
	Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0") 
	
	' Load the XML file 
	xmlDoc.Load "C:\Path\To\Generated\Payment.xml" 
	
	' Check if an XSD file is provided 
	xsdFile = Range("B4").Value 
	' Input for XSD file path 
	If xsdFile <> "" Then xmlDoc.schemas.Add "", xsdFile 
		If xmlDoc.validate.errorCode <> 0 Then MsgBox "XML validation failed: " & xmlDoc.parseError.reason 
		Else MsgBox "XML validated successfully!" 
		End If 
	Else MsgBox "No XSD provided, skipping validation." 
	End If 
End Sub
```

Direct Integration with WinSCP: To further streamline the process, I added a feature that allowed the tool to automatically upload the generated XML files to a server via WinSCP. This eliminated the need for manual file uploads and ensured that the files were injected into the system on time.
Example VBA Code for WinSCP Integration:

vba```
Sub UploadToServer_V2() 
	Dim WinSCPPath As String 
	Dim SessionOptions As String 
	Dim Command As String WinSCPPath = "C:\Program Files (x86)\WinSCP\WinSCP.com" 
	SessionOptions = "/log=C:\winscp.log /command ""open sftp://user:password@server.com/ -hostkey=""your-host-key""" Command = "put C:\Path\To\Generated\Payment.xml /remote/directory/" 
	
	Shell WinSCPPath & " " & SessionOptions & " " & Command & " " & 				
	"""exit""",
	 vbHide MsgBox "File uploaded successfully!" 
End Sub```

Enhanced Logging in Version 2.0: In contrast to the manual logging of version 1.0, version 2.0 introduced advanced logging capabilities directly within the Excel sheet. This new feature automatically recorded:

- Unique payment Message IDs
- The status of each payment (whether injected, returned, or completed) using dropdown menus
- Associated test cases for better traceability
- Timestamps for every action, enhancing the efficiency of test monitoring.

Furthermore, I developed a VBA script that audited the log for payments needing attention (such as returns on designated days) and notified me upon opening the sheet.

Practical Use of Version 1.0:

Even with just the fundamental features of version 1.0, I managed to process a high volume of payments effectively, retaining the ability to make manual modifications when necessary. This functionality streamlined the entire data generation workflow.

For instance:

- I was able to inject 80 files on the first day and another 80 on the second day to support a failover strategy effortlessly.

- I could manually tweak any outliers using the files I had prepared.

- I monitored ongoing progress directly within the logging sheet.

The tool's capacity to automate monotonous tasks, while still providing the flexibility for manual intervention in complex situations, proved to be an essential asset in managing my QA responsibilities.

Conclusion

By evolving the tool from version 1.0 to version 2.0, I was able to streamline payment generation, validation, and tracking in a way that improved accuracy and reduced manual effort. Whether you’re generating payment XMLs, validating files against an XSD schema, or automating file uploads, Excel and VBA offer a powerful and flexible platform for handling large-scale QA tasks.

If you’re a QA professional dealing with repetitive manual processes, consider how tools like Excel and VBA can help you build custom automation solutions that are tailored to your specific needs.

Call to Action

Have you used Excel or VBA to automate aspects of your testing process? Or are you thinking of implementing advanced features like XML templates or XSD validation? Share your thoughts and experiences in the comments—I’d love to hear how others in QA are solving similar challenges!