How to Convert Bank Statements to Excel and CSV

Importing financial data from your organization’s bank statements into accounting software or financial databases used to be a hassle. The old copy-and-paste trick worked just fine in the case of already digitized documents. However, the real problem arises when you need to process older transactions, PDFs or scanned images of bank statements. 

Nowadays, this process is completely automated, thanks to Intelligent Document Processing. An IDP platform uses Optical Character Recognition technology to digitally convert bank statements, by extracting financial information in an instant. The real benefit? It also converts it to Excel or CSV formats, given the financial nature of the data.

In this blog, we will guide you on a step-by-step journey on how to convert bank statements to Excel and CSV formats. Thanks to Klippa’s IDP platform, this process only takes 5 minutes to set up. Let’s start!

How to Convert Bank Statements to Excel

We will demonstrate how you can easily automate the process of extracting data from your bank statements into an Excel spreadsheet or convert it into a CSV format, ensuring that you will never have to handle documents manually again.

Klippa DocHorizon is a powerful Intelligent Document Processing (IDP) platform that automates document workflows and offers flexibility for various use cases by supporting more than 100 document types and formats.

Let’s walk you through a step-by-step process of extracting data from a bank statement to an Excel sheet or CSV format using Klippa DocHorizon. For both examples, we will process a PDF bank statement from Google Drive as our input source.

Step 1: Sign up on the platform

Start by signing up for free on the DocHorizon Platform. Enter your email address and password, then provide details such as your full name, company name, use case, and document volume. After that, you’ll receive a free credit of €25 to explore all the platform’s features and capabilities.

After logging in, create an organization and set up a project to access our services. For our goal – converting bank statements to Excel – enable the following:

• Go to Project Settings → Services
• Enable the Document Capturing – Bank Statements and Flow Builder 

This setup ensures you have everything you need right from the start!

Step 2: Create a preset

The next step in converting your bank statements to Excel or CSV foramt is to set up a document-capturing preset. This preset is a custom configuration that specifies which data fields should be extracted, ensuring it meets your unique requirements.

Start by clicking on the Bank Statement Model on the left menu field. There, you can create a new preset and assign it a name. Let’s call it “Bank Statements to Excel”.

Next, select the components you want to include in the preset and specify the data fields to be extracted. We’ll keep the default options for this example and simply press “Save.”

Here’s a tip: Besides the default option, you can further customize the preset depending on your use case by adding extra components like raw OCR data.

Step 3: Select your input source

Now that you have enabled the Flow Builder and created a preset, it’s time to build your flow. A flow is a sequence of steps that define how your Bank Statement data is extracted into Excel.

Click New Flow → + From scratch and assign your flow a name. We’ll name the flow “Convert Bank Statements to Excel”. For this example, you’ll create a folder named “Input” in Google Drive and upload your invoices there.

Next, choose your input source by selecting “Google Drive” and “New File” as your trigger. This is going to start your flow. On the right side, fill out the following sections:

• Connection: Assign any name to your connection (e.g. “google-drive”) and authenticate with Google
• Parent Folder: Input
• Include File Content: Check this box to ensure file content is processed

Here’s a tip: You have several options for selecting your input source: you can upload files directly from your device or connect to over 100 external sources, including Google Drive, Dropbox, Outlook, Box, Salesforce, Zapier, OneDrive, and your company’s database.

Test this step by clicking on Load Sample Data: remember to have at least one sample document in your input folder while setting up your flow.

Step 4: Capture and extract data

Now, it’s time to extract the necessary data by using the previously created preset to process all the selected data fields from the bank statement in the input folder.

In the Flow Builder, press the + button and choose Document Capture: Bank Statement.

To proceed, configure the following:

• Connection: Default DocHorizon Platform
• Preset: The name of your preset (in our case, “Bank Statements to Excel”)
• File or URL: New file → Content

Then, test the step to ensure everything is working correctly. Once the test is successful, you’re ready to move on to the next step: saving your results!

Step 5: Save your Excel or CSV file

Now let’s set up an output destination for our extracted data. In this case, we want to show you 2 methods: how to simply convert bank statements into desired formats (such as CSV) and upload them to your desired output and how to automatically compile data into the Microsoft Excel 365 sheet.

Convert Bank Statement to CSV format

First, let’s convert our bank statement into a CSV file. For our example, we’ll choose a Google Drive folder for our output. To convert your bank statement into CSV, follow these steps in the Platform:

Press the + button and select Create new file → Google Drive

Then, set up the following configurations:

• Connection: google-drive
• File Name: Document Capture: Bank Statement → components → bank → name (string). Next to it, type .csv 
  • Here’s a tip: Instead of using “.csv” for the file name, you can choose other formats such as PDF, JSON, XML, and more, depending on your preference.
• Text: Document Capture: Bank Statement → components
  • Here’s a tip: Select the text you want to include in the new document. By selecting “components”, you choose all the extracted elements.
• Content Type: CSV
• Parent Folder: The name of your output folder

Test this step by clicking the button at the bottom right, and you’re all set! 

Convert Bank Statement to Excel

To compile extracted data directly into Microsoft Excel 365 sheet, follow these steps in the Platform:

Press the + button and select Append Row to Worksheet → Excel

On the right side, fill in the following fields:

• Connection: Connect to your Microsoft account (Microsoft Excel 365)
• Workbook: The name of the workbook you created for this workflow
• Worksheet: Name of the sheet

Here’s a tip: If your worksheet contains headers, → toggle the “Does the first row contain headers?” button. This option will inform the platform that row 1 is occupied.

If you’ve followed this tip, you’ll see another section with the names of the headers in your output file. In our case, the names are: Account Number, Opening Balance, and Date Range.

Let’s try it out with the Account Number: in the Data Selector, open Document Capture: Bank Statement → components → financial → account_number. Follow the same steps for the other two header sections. 

Test this step by clicking the button at the bottom right, and you’re all set! 

Congratulations! All the bank statement data is now available in your Microsoft Excel 365 Sheet. With this setup, you can publish the flow, and any new invoices added to the folder will be processed automatically. That’s how you can save time while ensuring accuracy in your workflows.

Use Klippa to Convert Bank Statements to Excel

Looking to save your bank statement data to Excel or convert it into a CSV format? We’ve got you covered! With Klippa DocHorizon, an advanced intelligent document processing platform, you can easily automate any of your workflows. By leveraging Klippa’s advanced module, you can set up a seamless workflow tailored to your needs:

• Data extraction OCR: Automatically extract data from any type of document.
• Human-in-the-loop: Ensure almost 100% accuracy with our human-in-the-loop feature, allowing internal verification or support from Klippa’s data annotation team. 
• Document conversion: Convert documents in any format – such as PDF, scanned images, or Word documents – into various business-ready data formats, including JSON, XLSX, CSV, TXT, XML, and more.
• Data anonymization: Protect sensitive information and ensure regulatory compliance by anonymizing privacy-sensitive data, such as personal information or contact details.
• Document verification: Authenticate documents automatically and identify fraudulent activity to reduce the risk of fraud.

At Klippa, we value privacy – all of our document workflows are HIPAA, GDPR, and ISO compliant, ensuring secure data processing. With peace of mind about data safety, take the next step and streamline your document workflows.

If you want to automate your workflow with Klippa’s intelligent document processing solution, don’t hesitate to contact our experts for additional information or book a free demo!

FAQ