[RESOLVED] Export NatWest Bank Statement to Excel

Introduction

A recent task I had to work on was fixing up a conversion for bank statement from NatWest.

Now being from Australia, I am not too familiar with NatWest Bank. A quick google turned out its one of the largest banks in the UK. D’oh!

So my task was to make sure that the NatWest bank statement converts accurately to Excel.

In this post, I will go over my steps for this.

Download NatWest bank statements

• Visit www.onlinebanking.natwest.com to access Online Banking (note: the link will open in a new window).
• Navigate to ‘Statements & transactions’ from the primary menu, followed by choosing ‘Statements’ from the ‘Your statements’ subsection.
• The system will display the statements available for the highlighted account and the specified year, as determined by your selection in the time period dropdown menu.
• If you hold multiple accounts, feel free to switch between them and alter the year of interest through the time period dropdown menu.
• Locate and click on the pertinent statement cycle link visible on the webpage to initiate a PDF download, in accordance with your browser preferences.
• You have the option to either print or save the downloaded PDF file for your records.

Analysis of NatWest Bank Statement

To get a accurate extraction of the data, we first need to understand the bank statement.

Below is a sample with fake data and redacted personal info:

Observations

• The NatWest bank statement is pretty straight forward with six columns: “Date”, “Type”, “Description”, “Paid In”, “Paid Out”, “Balance”
• The “Paid In” represents your credit and “Paid Out” are your debit transactions
• Dates are formatted with the “year” so there is less confusion if the statement goes over various years.
• Each page has your details first and then the bank transactions. This will make it troublesome if you want to copy and paste manually.
• Highlighting the table of transactions manually is not adequate - some columns and rows are missed

👉 Using PdfDodo to extract data to Excel

This option is created by yours truly. Just sign up with an account and you try it out for free.

To do start the CSV 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 we have the results in Excel, we can use this to analyze our data or import it into a accounting software package like Xero or Quickbooks.

Alternative 1: Use Microsoft Word to convert PDF to Word and then copy/paste to Excel

Now if you do not want to use PdfDodo and already have the Microsoft Office products installed on your machine, you can use Microsoft Word!

To do this, we do the following steps:

Opening the PDF in Word:

1. Launch Microsoft Word.
2. Go to File > Open.
3. Navigate to the location of your PDF file.
4. Select the PDF and click Open. Note: When you open a PDF in Word, it will notify you that it’s going to convert the PDF to an editable Word document. The conversion might not be perfect, especially if the PDF has complex layouts or graphics.

Some issues:

• Copying and pasting the data to Excel seems ok, but the tables are all over the place.
• As seen in the image above, the conversion does not respect the paging. There are some sections that are broken up by page.

Advantages and Disadvantages of Utilizing Word for File Conversion

• Utilizes existing software for those with Microsoft Office, avoiding the need for extra downloads.
• Allows for selective data transfer, facilitating control over the portions of the document you migrate to Excel.
• Can be less streamlined and potentially time-consuming, particularly for intricate or extended files.
• May give rise to formatting inconsistencies or lost information, necessitating additional manual tweaks.
• Incompatible with password-protected PDFs."

Alternative 2: Use Adobe Pro

• Step 1: Open the PDF in Adobe Acrobat - Launch Adobe Acrobat and open the PDF file you wish to convert.
• Step 2. Export the PDF - Navigate to File in the top-left corner, then select Export To from the dropdown menu.
• Step 3. Choose Spreadsheet Option - From the sub-menu of the export options, select Spreadsheet and then choose Microsoft Excel Workbook. Note: Adobe Acrobat doesn’t provide a direct “export to CSV” option, so we first convert to Excel.
• Step 4. Save the File - Choose a location on your computer to save the Excel file and click Save.
• Step 5. Open the Excel File - After the conversion is completed, open the resulting Excel file in Microsoft Excel.
• Step 6. Save the File as CSV - In Excel, go to File -> Save As.

Choose the location where you want to save your CSV file. From the “Save as type” dropdown, select CSV (Comma delimited) (*.csv) and then click Save.

Now, you should have your PDF content in a CSV format.

Limitations:

• I found that the accuracy of this good for some instances, but can be hit and miss.
• You will have to pay for Adobe Pro - which can be quite steep.

Clean data in Excel

Fortunately the table of bank transactions that NatWest provides is quite detailed so after our extraction using PdfDodo (or one of the alternative options), we do not need to do too much data cleansing.

Some minor things you may need to consider:

• Updating the “Paid In”, “Paid Out”, “Balance” columns with the currency format
• Normalize the “Type” column to something that would be more meaningful for your needs.

You may need to do a bit more work if you want to convert it to a CSV that is compatible with accounting software like Xero or Quickbooks.

Tips for efficient and accurate results

• Reaching a complete 100% conversion cannot be achieved without a hands-on approach. With PdfDodo’s assistance, you can easily complete over 90% of the work.
• Prioritize security and confidentiality, given the sensitive nature of financial documents. Rest assured, PdfDodo upholds stringent data protection standards through encryption and full adherence to GDPR regulations.

Summary

Overall, I think the NatWest bank statement is a lot better than other bank statements that I have tried to convert to Excel.

It contains detailed dates (including the year) of the transactions, the balances are “rolling” and manual copy/pasting seems not be too bad.

You can use PdfDodo to do the conversion to Excel and would require minimal data cleansing.

If you decide to opt to use Microsoft Word or Acrobat Pro, there might be more extra manual steps to clean up the data.