Automating Bank Statement and Invoice Reconciliation Using GPT and Google Sheets

When you are panic-stricken due to the volume of invoices not matching what you see in a bank, confusion arises, and you have a lot of work on your hands. There are many reasons for discrepancies between client payments and invoices: incorrect invoice numbers, lower amounts than what was invoiced, or no reason given for the payment! The time spent reconciling bank statements with clients' invoices can take days (at a cost to your financial records) rather than the few minutes required by an automated approach.

Through automation, it is possible to have a computer program (algorithm) that can evaluate the data for both the bank and clients and find/compare matches, as well as flag discrepancies to be reviewed manually. The integration of GPT allows the algorithm to understand context, identify different ways to write company names, disregard typos, and link partial payments to their appropriate invoices. Automated reconciliation creates three key advantages to the financial community: speed, accuracy, and scalability.

Using historical data, it takes approximately 15 hours to reconcile 300 payments manually; the same number of payments with automation takes 3-5 minutes. A company was able to decrease its month-end closing from five days to four hours by implementing automation. Accuracy: Humans are prone to inaccuracies due to fatigue; however, GPT never tires and conducts all operations based upon a single algorithm. In test scenarios where 1,200 transactions were used, the GPT achieved 97% accuracy. The remaining 3% of transactions that fall into this category require additional review due to complexity. Scalability: The automation platform continues to grow with a business, providing the same response time as demand increases, while the demand for manual resources increases proportionately to the workload. The overall costs associated with implementing this system are roughly the same. The automation platform also allows users to monitor discrepancies in "real-time" and sends notifications via Telegram for unmatched payments longer than 48 hours, enabling users to quickly resolve issues before they delay payment.

Benefits of Automation

Let's break down the math on this: An accountant works 60 hours a week at a monthly compensation of $30/hour for a total monthly cost of $1,800. The subscription for the ASCN.AI automated processing platform is $29. As a result, the estimated monthly savings from the implementation of this system would be around $1,800, which equals an annual savings of approximately $21,000! Even factoring in several hours for setup assistance, the ROI is already evident in the first month. The risks associated with the human factor are greatly reduced with the implementation of automated processing. For example, should the chief accountant leave unexpectedly, a new employee can take over very quickly with the same system already established, thereby eliminating the potential for delays in operations. Furthermore, logging all transactions in the system promotes an environment of integrity and transparency. Auditors are provided with complete records of each transaction without requiring an explanation of every single transaction's movement. Accountants will have the opportunity to use their time more effectively as they can devote all of their time to performing analytics instead of performing routine tasks. Doing so will help them reduce debt, predict finances more accurately, and act as true strategy advisors to the organisation.

Understanding GPT and how it can be used in Google Sheets for reconciliation automation.

What is GPT and how does it process text data?

GPT (generative pre-trained transformer) is a sophisticated type of language model trained using a large volume of text data. As a result of this training, GPT understands the meaning of words within the context of a sentence and is able to identify patterns in the data it has been trained on as well as change unstructured text data into structured data. Within a reconciliation setting, GPT serves as an intelligent "matcher" to help identify key data points from payment transactions and match those data points against a company's historical invoice data.

In comparison with other traditional automated scripting methods, GPT is able to identify patterns within context as well as understand and take into account different spelling variations. For example, in the case where a company's name appears in varying forms within a company's historical invoice data, GPT's ability to process information based on patterns will allow it to identify those names as being from the same company, whereas another automated script may not be able to distinguish between those variations. GPT is also capable of discarding extraneous information, such as prepositions, spaces, and differences in format, from the data it has collected so that it can match payments with invoice data more accurately.

In addition to its ability to process raw data, GPT also has the capability to resolve minor discrepancies in payment amounts; for instance, if a bank statement indicates a payment amount that is slightly less than the invoice amount, it can automatically classify the difference as either a commission fee or discount, depending on the circumstances. GPT can also be programmed to ignore discrepancies that are less than 1% of the invoice amount as being commissions; therefore, a user will not receive a notification regarding these types of discrepancies. As an added feature, GPT can also process unstructured data such as PDF files and extract the appropriate document number(s) from those unstructured files and insert them into Google Sheets.

Google Sheets is an online software that serves as a database for small and mid-sized businesses. The process of automating the reconciliation between customer payments via their bank statements with the actual invoices sent or paid by customers includes the following three components: (1) Data storage: Each customer's payment and existing invoice(s) will be stored on separate no-code Google Sheets for real-time access by multiple users, without requiring any development work by an accountant. Therefore, changes to either the invoice(s) or payment(s) can be seen instantly. (2) Data processing: A combination of formulas and script-based solutions (e.g., Google Apps Script) will allow users to assess not only when there are matches between payments and invoices but also which invoices were not matched against the customer payment. These will automatically be identified as "disputed" or "problematic." (3) Control interface: All discrepancies will be located within a workspace for quick identification by an accountant, saving considerable time in checking and verifying disputed invoices before taking action on them.

In addition to integrating Google Sheets and GPT, the use of APIs allows for the creation of automated workflows that can move payment information across multiple platforms with no code needed.

Process Overview for How GPT and Google Sheets Work Together to Automate Reconciliation

The reconciliation process begins when bank statements and invoices are uploaded to Google Sheets using a single template. At the point of entry, Google Sheets reads the newly entered bank statement and invoice data and initiates a search for matching records. The GPT (Generative Pre-trained Transformer) identifies which row in the spreadsheet corresponds to which payment based on analysis of the invoice(s) paid and creates a request for each payment, detailing who paid, what invoice was paid, how much and when. The search for matches uses a confidence scale and the invoice number of the payment matching the bank statement is noted in the Google Sheet. The results will be returned back to Google Sheets where potentially disputed payments will be flagged for further scrutiny. The results of the search will be sent to the Telegram app with a summary of any charts related to the disputed invoices.

Overview of Key Components and Estimation Methods for Automating the Reconciliation Process

1. Identifying and Organizing Bank Statements and Invoices in Google Sheets

The first key step in automating customer payment reconciliation is to put all the relevant bank statement and invoice records into a single database format so they can be processed quickly and easily. Your Statement should have the date listed, the counterparties that were used and for what purpose, the status of the reconciliation, and the amount and currency associated with the entry. You should set up an Invoices sheet that lists the following: the invoice number, date of the invoice, name of the counterparty on the invoice, the amount of the invoice, and whether the invoice has been paid. If the invoice number is included in the payment purpose, that makes the search easier. When the invoice number cannot be directly retrieved from the payment purpose, you have to rely on GPT to extract the information for you. For companies that have more than one account (e.g., local and foreign currency accounts), it is best practice to use separate sheets or create a column to categorize the transaction, thus limiting the chance of mistakes. It is also recommended that Banks provide an API for exportation of their Statements automatically, instead of manually having to download the statements.

Using GPT to Recognize Data and Classify it

Using the same example provided previously, GPT is able to match and classify the different misspellings of a counterparty's name that were used to make the payment, as well as classify the payment as either a payment, a refund, or a commission. The system is able to read through the text and find all the numbers within the text, identify all the dates, and also pull instances of partial payments under one invoice number; therefore, marking all instances of permissible discrepancies, i.e., bank fees.

Algorithms Used for Data Comparison and Discrepancy Identification

Typically the process of Reconciliation occurs within the following arrangement of priorities: A perfect match is when the Date, Counterparty Name, and Amount all match. A Near Match is when the Counterparty Name is relatively similar, but has several variations. Complex cases that require human involvement will be categorized by GPT based on the Analysis that it provides and the Confidence level it has.

When a Match Cannot be Made

On occasions, No Match(s) is found for an Entry; therefore, the Entry will be sent for a Manual Review. The System will evaluate the presence of Duplicate Payments and Update Invoice Status for Partial Payments. Automating payment processes is a standard practice that helps businesses operate efficiently. There are several steps involved in automating payments, including preparing bank statements and invoices, creating templates and scripts in Google Sheets, configuring the GPT model for analysis and reconciliation, and automating notifications and reporting.

When preparing to automate payments, the first step is to gather and organize bank statements and invoices using a CSV or XLSX export from the bank. Once the information is gathered, it must be cleaned by removing any unnecessary rows and formatting the dates and numbers to a uniform style.

The next step is to create a template in Google Sheets using VLOOKUP formulas to match the invoice number and bank statement number. Important columns should be protected from deletion to prevent data loss. Once completed, the completed templates should be used to monitor and provide key statistics regarding payment statuses.

The final steps are to configure the GPT model (e.g., ASCN.AI) by creating a workflow and generating triggers based on user-defined events or schedules. Also include the logic for handling confidence levels and setting statuses within the model. Finally, test the results using sample data and adjust the prompts as needed.

To automate the notification and reporting processes, create a Telegram Bot that will send users notifications about the status of their payments, as well as final results and key statistics with warnings about large discrepancies. PDF reports are created weekly or monthly in accordance with your business's requirements, integrating with your chosen CRM to keep your deal status updated automatically. PDF reports are archived so they can be easily reviewed if necessary.

Company Examples and Case Studies

Automating a Small Business

Example: A small online store sells between 3 and 5 million rubles in products each month and has around 200 to 300 online orders. The previous reconciliation took 12 hours for an accountant to complete, and around 30% of the clients did not include the order number.

Solution: Integrate ASCN.AI and automate the loading of orders and statements into your CRM. GPT is used to interpret payment descriptions and link them back to the original order with an accuracy of 92%.

Results: The time it takes to reconcile is reduced to approximately 40 minutes per month; a dramatic reduction in error rates; the business saves 3,000 dollars on salary per year.

Business Automation in Medium and Large Businesses

Example: An electronics distributor has sales of over 200 million rubles, with over 2,000 invoices every month and two accountants who spend about 80 hours each month reconciling payments received from 300 vendors.

Solution: Setup a complex workflow to track the variations of vendor names and automatically analyze contracts. 85% of payments are now processed automatically using GPT.

Results: Manual checking has reduced from 80 hours per month to 15 hours; month-end closings are now completed in 3 days instead of 7; and accounts receivable delays are now down to 18%. Yearly savings are more than 23,000 dollars.

Case Study: Automating with Stripe and QuickBooks

Example: A SaaS company has over 5,000 current subscriptions and thousands of micro-payments that had previously been manually entered into QuickBooks.

Solution: Automatic export of payments from Stripe and exporting to QuickBooks for transaction analysis, along with generating invoice status updates through GPT.

Results: Nearly 100% automation with 60 hours a month of saved time, allowing growth without adding accounting staff. A very low level of errors (almost none).

Best Practices and Recommendations:

Data Security for Financial Data

Since financial data is very sensitive, it is important to protect it properly. Always include two-factor authentication on any account where sensitive financial information is being stored (Google Accounts, ASCN.AI, Bank Account Access, etc.). Limit access to Google Sheets and other systems to only those who truly need it. Store API tokens in encrypted environments (for example, ASCN.AI's SecretKey feature). Update passwords and API keys regularly, document all actions in Auditing Logs, and only use HTTPS for data transmission. Always backup your data and workflow.

Optimizing Accuracy and Automation

Use GPT training on your own data for improved recognition. Make a list of variations for counterparty names for reference. Set confidence threshold values to find the optimum point between automation and manual review. Regularly renew prompt templates based on previous errors and introduce new rules. Divide data into functional business divisions to avoid confusion, track processing speed, and perform A/B testing on prompts to determine which are the most effective.

Common Mistakes When Automating

Do not completely eliminate manual review; maintain it as a fallback for disputes. Always conduct automation testing on older data sets where outcomes are known. Remain current on how Bank export formats are changing and modify your system quickly. Write down the logic and parameters of your automation so future maintainers have an easy time making any necessary adjustments. Divide up the work into small parts, and do not give the model complicated commands or overload it with information. Also, train employees to be able to use the system as well as utilize modern models such as GPT-4 or GPT-4 Turbo, which are more accurate than older models.

Frequently Asked Questions (FAQ)

How do I use GPT and Google Sheets for financial data safely?

Security is the main priority, so use two-factor authentication, restrict access to only those who need it, and store your keys in encrypted vaults. Always transfer your files using HTTPS. You can also anonymize client data by replacing their name with a code. Additionally, you may use local models or APIs with private configurations to do this.

Can the system handle multiple bank accounts and currencies?

Yes, easily. You can create a new sheet for each bank account or create a new column to identify the currency type. Within the prompts, you can filter the data based on currency using GPT. Additionally, you can automate currency conversions based on the current exchange rate using integrated Currency Exchange APIs. All workflow processes run in parallel.

What technical skills do I need to set up this system?

You need to have basic Excel or Google Sheets skills, as well as an understanding of how to work with APIs and the ability to follow directions. You do not need coding experience to set up the system; ASCN.AI offers a visual no-code builder to assist with the setup process. Simple setups usually take about 30 minutes to complete; complex processes may take a few hours. Detailed instructions and technical assistance are provided.

How do I fix common reconciliation errors while automating?

If GPT is unable to locate a match, check your prompt and standardize your data. If your workflow is moving too slowly, break your data into smaller batches. Reducing the confidence threshold will lower the number of manual checks required, but be careful and monitor for errors. Track duplicate payments through the payment records. There should be a delay between each request to stay within the limits of the API. If necessary, include PDF parsing using OCR to extract data from attachments.