How to Extract Data from Bank Statements to Excel with MS Power Automate - part 1

Introduction

In this guide, we will walk you through the process of extracting data from bank statements and exporting it to an Excel file using Microsoft Power Automate. This can be a helpful tool for managing your finances and keeping track of transactions with ease. In part 2 we will describe how to extract transaction data from your Bank Statement to your Excel.

Video Instruction

Step 1: Use Bank Statement Template

Visit Bitskout's template library and locate the "Bank Statements" template. This template will help you automatically extract data from bank statements.

Step 2: Create a Plugin

Click on "Use Template". The plugin will be immediately created. As we are using MS Power Automate, press Close.

Step 3: Set up Microsoft Power Automate

Go to Microsoft Power Automate and create a new cloud flow. - In this example, we will use a trigger for flagging a new email with a bank statement attached. It is easier for demo purposes to flag an email. You can always replace a trigger.

Search for "Outlook" and set up the trigger accordingly.

Also Read: How to extract data from a CV (resume) to Excel with Zapier

Step 4: Configure the Get Attachment Action

Add the "Get Attachment" action and specify the message ID and attachment ID.

Step 5: Run Bitskout Plugin

Add a new action called "Run Bitskout" and choose "Run Plugin for a File."

Select the "Bank Statement" plugin. To input a direct link, click on "Expressions" and type "string()" with "content" inside the brackets. This allows you to work directly with the file.

string(outputs('Get_Attachment_(V2)')?['body/contentBytes'])

Step 6: Save and Test Your Setup

Save your cloud flow and run a test to trigger one run of the Bitskout plugin. This will allow you to see the output.

Step 7: Copy the JSON Output

Once the test is complete, copy the "outputs" by clicking on it and pressing Ctrl+C.

Step 8: Parse the JSON Output

Click on Edit in the top right corner to exit the test and add a "Parse JSON" action. Microsoft Power Automate needs to know what kind of information it will receive from the Bitskout plugin. Then click on "Generate from Sample" and paste the output there:

Step 9: Export Data to Excel

Add a "Add a Row into a Table" action and select your OneDrive file and table. As the JSON module has provided us with parsed information, you just need to map the fields to the correct columns.

Conclusion

As you can see the setup of bank statement data extraction is pretty straightforward. There are some steps that are somewhat complex, however, we a few tries you'll get a grasp of MS Power Automate quirks.

In the next part, we will show how you can process transactions. We will be updating the same flow and you'll find the exported flow attached to that article.

Start Free Trial