Converting Commonwealth Bank statement to Excel

Introduction

As a customer of Commonwealth Bank in Australia, I found that they do not provide a way to download bank transactions into a Excel or CSV file.

The web interface is a bit clunky - you have to keep scrolling to load transactions and there is not download or export button.

If you wanted to see all transactions - you will have troll through all the statements… sigh…

Recently I was tasked to extract data out of these PDF bank statements from Commonwealth Bank - so I decided to use PdfDodo to do the heavy lifting for me.

In this post, I will go over my process of exporting the PDF bank statement to Excel. I will also go over a few other alternative options if you do not prefer to use PdfDodo.

Generally, my process involves:

• Downloading all the bank statements to PDF from NetBank
• Upload those documents into PdfDodo and let it “Analyze”
• Download the extracted transactions into Excel

Alternative methods would involve using Excel itself or first convert the PDF to Word and copy/paste the result into Excel.

How to download the CBA bank statements

Via NetBank (Online Banking):

Login to NetBank:

• Go to the Commonwealth Bank website.
• Click on ‘Log on’ then select ‘NetBank’.
• Enter your client number and password to log in.
• Navigate to Statements - Once you’re logged in, look for a section labeled “View Statements” or something similar.
• Select the Account - Choose the account for which you want to download the statement.
• Choose the Statement Period - You should see a list of available statements, typically sorted by date.
• Click on the relevant date or period for which you need the statement and download!

Via the CommBank App:

Open the App:

• Locate and open the CommBank mobile app on your device.
• Log In - Sign in using your pin/credentials or biometrics.
• Navigate to Accounts tab
• Navigate to the Manage tab
• Tap on the “View Statements” section. This is where you will see all of your statements.
• You can then download the statement period that you are after.

Analysis of the bank statement

Now after you have downloaded the statement, it may look like something below.

I have redacted the personal info and highlighted in the interesting bits:

Some observations:

• Area A - This is just the bank statement header - not interesting for our purposes.
• Area B - The personal info and address - we can skip this.
• Area C - The account info and the statement period.
• Area D - this lists all your transactions in the specific period. This is the information that we want to be exported to Excel. Luckily, I have found that with Commonwealth bank statements, they have been following the same format and number of columns for the last decade or so.
• The transaction date does not include the year - so when we export to Excel, we need a manual step to set the year.
• The “Debit” and “Credit” columns do not have currency symbol - so you will need to manually add it in Excel.
• The “Transaction” column contains the description of the transaction and can be multi-line. This is of note because when copying to Excel - you might end up copying two or more rows instead of one!

👉 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:

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 option 1: Convert PDF to Word and then copy/paste to Excel

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.

Copy Data to Excel:

1. Highlight the data or tables you want to transfer to Excel.
2. Copy the selected data (using Ctrl+C or right-click and select Copy).
3. Open Microsoft Excel.
4. Paste the data into the Excel worksheet (using Ctrl+V or right-click and select Paste). Adjusting in Excel:

Once the data is pasted into Excel, you might need to make further adjustments:

• Check for merged cells.
• Ensure columns are aligned correctly.
• Make sure data types (like dates or numbers) are correctly formatted.

Pros of Using Word for Conversion

• No need for additional software if you already have Microsoft Office.
• Direct control over which parts of the document you transfer to Excel.

Cons of using Word for conversion

• Indirect and might not be the most efficient method, especially for long or complex documents.
• Potential for formatting issues or missed data during the conversion. Might require more manual adjustments.
• Will not work with password protected PDFs

Alternative option 2: Adobe Online Conversion

We can utilize Adobe Online for our bank statement conversion to Excel.

1. Press the ‘Select a file’ button displayed above or simply drag and drop a PDF into the designated zone.
2. Choose the PDF you’d like to transform into the XLSX format.

Limitations to Consider

While this method may initially seem like the best choice, there are some drawbacks to be aware of:

• Analysis of even small files can be time-consuming.
• Data security is uncertain – there’s potential for data resale to interested parties.
• Lack of immediate customer support accessibility.
• Incompatibility with encrypted or password-protected PDF bank statements.

Tips

• No single method will get you 100% conversion without manual input. PdfDodo will definitely get you over 90% of the way there.
• Be careful with dates since the year is not displayed - so you will have to input that in. You will have to be extra cautious when your statement goes over multiple years.
• The “Debit” and “Credit” columns do not seem to include the currency symbol - so you will have to include them in your Excel sheet.
• Keep in mind of security and privacy since we are dealing with Financial statements. PdfDodo takes this seriously and your data will be encrypted. PdfDodo also have a comprehensive GDPR compliance.

Final thoughts

Overall the Commonwealth Bank statement is fairly simple compared with statements from other banks.

We can use PdfDodo to convert to the Pdf bank statement to Excel or CSV and it will get us 99% of the way there.

However there are still issues that you will need to fix up:

• The dates do not come with year
• Debit and Credit column do not currency - so when in Excel you will have to set these columns to currency.

Other options to get your bank statement transaction data is to use Microsoft Word or Adobe’s online PDF converter.

Although these options are less reliable, will not work with scanned PDFs and therefore you will have to spend more time cleaning up the data.