HOW TO Download Barclays Bank Statement to Excel

Introduction

Part of working with bank statement PDFs is that you see a lot of different formats to present data such as transactions, customer info and the bank info.

One recent bank statement I was working with is the Barclays bank’s PDF statements.

There are a few nuances when trying to extract data from their bank statement transactions section.

In this post I will go over my analysis of the Barclays bank statement and options on how we can convert their PDFs to Excel format.

Retrieving the statement

Firstly we need to get the bank statement from Barclays. To do this, follow the below steps.

Login to Barclays Online Banking:

• Visit the Barclays website.
• Enter your login credentials to access your online banking account.
• Navigate to ‘Statements’:

Once logged in, you’ll typically find a ‘Statements’ or ‘Documents’ option in the main navigation menu or dashboard. Click on this.

If you have multiple accounts with Barclays, choose the one for which you wish to download the statement.

You may be presented with a list of available statements, typically sorted by date. Choose the statement you wish to download. If there’s an option to select a custom date range, specify your desired start and end dates.

• Download the Statement

Barclays Bank Statement Analysis

Below is a example of one of the Barclay’s bank statements. I have redacted out the personal info.

Some observations

The main area that we are interested in is the transaction area. When I was extracting the data, there are a few manual things I needed to consider to make it as accurate as possible:

• The transactions are grouped by day and can be multiline. So we only have one balance value at the end, but multiple transactions. For example, in our statement above, we can see that on the 01 Jul, there are multiple “Money In” and “Money out” transactions. The balance is displayed at the last transaction row.
• Columns are labeled as “Money in” and “Money out”. This is a bit different to other banks where they label them as “Debit” or “Credits”.
• The dates do not have the year - eg “01 Jul”. So this means that we will need to add the year when cleaning up the data in Excel.
• The rows have icons - so when we extract to Excel, the icons can be some random unicode character - so make sure to check and remove those.

To extract the data, we can use any of the following options:

👉Option 1: Use PdfDodo to extract the data to Excel

If your bank only provide statements in PDFs, then consider using PdfDodo to extract the data and convert it to Excel.

To do start the Excel conversion, we go through the following steps:

• Step 1: Get an account with PdfDodo
• Step 2: Go to the home page and click “Upload Document”

• Step 3: After the document has uploaded - click “Start Analysis”. This will usually take a few seconds.

• Step 4: Click on the uploaded document link to see the results.

After PdfDodo has completed analysis, it will list out all the data that it thinks is in a table format.

Now we can see our result and download the Excel or CSV file:

After that, can open up the Excel file and start doing our analysis.

Benefits

• Can accurately convert scanned and text PDFs
• Works with password protected PDFs
• No need to install software and comes with free option!
• Works for any PDF type not just bank statements. Can also convert documents that are handwritten, financial statements, invoices, receipts, etc

Option 2: Manual Copy and Paste

• Open your PDF bank statement.
• Highlight the data you want to transfer.
• Copy the data (Ctrl + C or Command + C on macOS).
• Open Excel.
• Paste the data (Ctrl + V or Command + V on macOS).
• Adjust columns and rows accordingly.
• This method can be time-consuming and may not be suitable for lengthy statements.

Pros and cons

Option 3: Adobe Acrobat DC

Here’s how you can convert a PDF bank statement to Excel using Adobe Acrobat DC:

• Launch Adobe Acrobat DC on your computer.
• Click on “File” in the upper-left corner.
• Choose “Open” and then navigate to your PDF bank statement file to open it.
• Once your PDF is open, look for the “Export PDF” tool in the right pane. Click on it.
• From the list of available formats, select “Spreadsheet” and then choose “Microsoft Excel Workbook (*.xlsx)”.
• Click the “Export” button.
• Choose a location to save your converted file, name it, and click “Save”.

After conversion, open the generated Excel file - Sometimes the formatting might not be perfect, especially if the original PDF has complex layouts.

Pros and cons

• Works relatively well in most cases - issue is with the pricing of going get a Adobe License.
• Need to be installed and be be a heavy burden on your machine.
• Does not work 100% with scanned PDFs
• Does not work with password protected PDFs

Option 4: Using Microsoft Word

Microsoft Word has the ability to convert and open PDF files, preserving much of its formatting, especially since the introduction of Word 2013. This feature is helpful if you want to edit the content of a PDF in a more familiar environment or if you want to use the content in other Microsoft Office applications.

Here’s a step-by-step guide on how to use Microsoft Word to open a PDF:

• Launch Microsoft Word.
• Click on the “File” tab in the upper-left corner.
• Choose “Open”.
• Navigate to the location of your PDF file.
• Select the PDF and click “Open”.
• Word will warn you that it’s going to make a copy of the PDF and convert its contents into a format that Word can display. The original PDF won’t be changed.
• Click “OK” to proceed.
• Word will attempt to recreate the PDF as closely as possible. Depending on the complexity and formatting of the original PDF, there might be some variations or issues with the layout. Ed Once Word has completed the conversion, you can review and edit the content just like any other Word document.

An example of the converted PDF

Pros and cons

• Benefit of this is that you do not need any additional software
• You own the data - no need to worry about uploading it or internet connection
• Conversion can be hit and miss. For this bank statement, it looks fine - but I found out its incredibly difficult to copy and paste the transaction table.
• Does not work with scanned PDFs

Tips for accurate conversion of the bank statement to Excel

• Check the dates carefully since they do not include the year - eg “01 Jul”. You will have to figure out the year based on the statement period.
• Depending on your method of extracting the data - check that the icons are not copied across. This is because the transaction description contains icons to indicate the type of transaction.
• The balance is grouped by day and not per transaction row. So if you require more fine grained details - then you will need to figure that out in Excel.
• PdfDodo will handle most of your use cases - scanned or text PDFs, password protected PDFs, etc. Other methods will require more manual input.

Summary

Overall converting the Barclays bank statement to a excel format might require a bit of manual process.

If you use PdfDodo it will get you 99% of the way there. It works with scanned or text PDFs and has a free option. Best of all you do not need to install software.

Now if you already have Adobe or Microsoft Office then you can convert using those.

The problem is that they are not always accurate and can generate random results. This will lead to more manual intervention!