How To Export HSBC bank statement to Excel

Introduction

Every end of financial year I generally have the task of analyzing my financial info (and my partner as well) of the past year - checking my income and expenses and what we owe the tax man!

This starts with getting a hold of my bank statements and then hacking them together in Excel.

The bane of my existence is getting data out of these PDFs and into Excel - especially when you have bank like HSBC.

HSBC used to be quite good with allowing you to download CSV/Excel but recently they limited that to the past 12 months only.

In this post I will show you my process of getting the data from my HSBC bank statement. Generally it goes as follows:

• Download the PDF bank statement from the HSBC website
• Pump the PDF to Pdfdodo to extract transaction data to Excel

Getting the PDF bank statement from HSBC

Login to HSBC Online Banking:

• Go to the HSBC website for your country.
• Enter your Personal Internet Banking ID, password, and any other necessary security credentials.
• Navigate to Account Information
• Locate the Transaction History or Statement Section - This could be under tabs like ‘Statement’ or ‘Recent Transactions’.
• Select the Date Range - Some platforms allow you to pick specific date ranges for which you’d like to download transactions.
• Choose the File Type - HSBC often offers multiple file formats such as CSV. With their recent change you can only take the last 12 months worth of transactions. Anything older than that will have to be PDF.
• Download - Click on the ‘Download’ or ‘Export’ button to begin the process.

Via HSBC Mobile Banking App:

The HSBC mobile app may have slightly different functionalities depending on updates and region. However, many banking apps may not provide as comprehensive export functionalities as their web counterparts. Here’s a general idea:

Login to the HSBC Mobile App. Select the relevant account. Find the transaction history or statement section. Look for an ‘Export’ or ‘Download’ option.

Example HSBC bank statement

An example of what the HSBC bank statement might look like (I have redacted out the personal identity bits):

Analysis of the bank statement

• Section A: This section will include your personal details - such as full name and postal address
• Section B: This is the barcode that the banks can use as an extra layer to check for fake statements. Generally it contains identifying information if scanned - eg your name and address.
• Section C - The IBAN, branch and account numbers
• Section D - This is the main section containing all of your transactions.

Note on IBAN numbers

An IBAN (International Bank Account Number) is a global standard for bank account formatting that aids in quicker and safer international payments. Paired with a Bank Identifier Code (BIC), it’s essential for certain cross-border transactions.

The IBAN looks like this GB15MIDL40051512345678. The structure is consistent but the actual length, which can be up to 34 characters, depends on the national standards of the country.

We can see that it can be broken down as follows:

Country code	Check Number	Bank code	Sort Code	Account number
GB	15	MIDL	400515	12345678

As you can see the document contains a fair bit of info. We cannot easily copy and paste the data to Excel or even CSV. Theres random things like:

• rows with multiple lines,
• they constantly change the transaction tables - sometimes having 4 columns and recently having 6 columns. Can be a pain to combine!

To accurately extract data from a document like this, we will need a app like Pdfdodo to do this.

👉 Use PdfDodo to extract the 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:

As we can see it correctly matches the columns and rows - even the row with multiple lines!

After we have our Excel file - we can do things like formulas - getting the SUM or even generating a nice Pie Chart of our expenses.

Benefits

• This works well and have high accuracy (99% rate).
• Allows you to work with CSV or Excel if required
• Works with scanned PDFs not just text-based PDFs
• Free for limited use and no need to install heavy software.

Alternative: Convert PDF to Word using Microsoft Word

One option that not a lot of people have thought about is to first convert your PDF file to Word.

Now if you already have Microsoft Word this is easy.

• Step 1: Open up Microsoft Word
• Step 2: Select a PDF file. If all goes well, Word converted the PDF file to a Word document.

Usually there would be a message saying that the operation will take a while - depending on the size of your PDF file.

• Step 3: You can then find your table of transactions and copy and paste all the row and columns into Excel.

In my case, the basic Word document looks like it converted ok - but if we look at the transaction table, its all jumbled up:

• The payment type, payment in, payment out are all out of whack!

I had a closer look at the transaction table to see if I can at least copy and paste it into Excel. Unfortunately not much is salvageable - theres a bunch of hidden spaces and margins.

Copying and pasting it into Excel gives me random columns and values where they should not be - eg Payment Type column is mixed with the Balance column.

So I ended up spending more time fixing and cleaning it up in Excel. Now I had about a dozen statements to go through so this option is not viable in my scenario.

Limitations

• While this option to use Word to convert your PDFs to Word and then to Excel might work - the success rate can be low and random.
• Does not work with PDF bank statements that are scanned!

Tips to make your HSBC bank statement conversion to Excel easier

• No one tool will be 100% perfect and requiring no manual intervention. PdfDodo can take you 99% of the way.
• The “Paid in”, “Paid out”, and “Balance” columns all do not have the currency symbol. So when you export to Excel, make sure to set these columns to “currency” format. I am guessing they did this intentionally to simplify since HSBC is a international bank and currency would be depending on the country - eg pound, dollar, etc.
• The date of transaction does not contain the full year. So you will need to be careful when the statement goes over multi-years.

Final thoughts

Generally, we can use PdfDodo to convert PDF bank statements from HSBC to Excel or CSV format.

This comes up due to the recent change by HSBC allowing you to only download transactions from the past 12 months to CSV (anything older than that needs to be in PDFs).

The HSBC bank statement can be annoying to deal with since it is changing year-on-year with differing columns and how they want to present their transactions.

PdfDodo can take care of this allowing us to export it cleanly in Excel - after that we can do the regular excel formulas such as SUM or adding pie charts.

An alternative option if you already have Microsoft Word is to get Word to convert your PDF. Then we can copy the transaction table to Excel.

In my case I found that the HSBC transaction table gets all jumbled up and success rate of the conversion can be low.