Implemented HSBC Positive Pay integration across six companies, eliminating $45 per unverified check fees (≈$3,000/week risk). Automated .CSV generation via SQL scripts and developed a custom Excel/VBA application to ensure correct encoding, column order, and account validation. Trained accounting staff, reducing manual errors, ensuring compliance, and safeguarding against check fraud at just $200.
HSBC Positive Pay is a fraud-prevention service for businesses that verifies checks presented for payment against a list of issued checks provided by the company. If there is a mismatch in details such as the check number or amount, the bank flags the check, preventing it from being processed until the business reviews and authorizes the transaction. This safeguards against counterfeit or altered checks.
In early 2025, I was tasked with ensuring the company complied with the deadline provided by HSBC Bank to begin using this fraud-prevention feature. Otherwise, the company would have been required to pay forty-five dollars per unverified check—an expense that would quickly add up since, across all the owner’s companies, about seventy checks are issued per week.
HSBC provided a portal for customers to import a .CSV file, but the file required a specific encoding, a strict column order, and the account number present on each transaction line. This was a highly customized .CSV export that our ERP/Check System could not generate on its own, and manually editing .CSV files for six companies was not practical. Fortunately, the ERP ran on a SQL Server database, making SQL scripts and automation a viable option. This approach enhanced efficiency, reduced manual errors, and streamlined database management. By sourcing a SQL developer on Upwork, we were able to configure the ERP to email .CSV files directly. However, since there was still a risk of unverified checks (and the emails did eventually fail), we needed a fail-safe solution.
To address this, we developed an Excel application that allowed us to easily export a file from the ERP system in a format close to HSBC’s requirements. The Excel app then handled the remaining adjustments, including the correct encoding of the .CSV file. Eventually, when I was notified that the emailed .CSV files were being rejected by HSBC’s system, I quickly sourced a VBA developer, created and tested the Excel application, and trained the accounting team on how to use it.