[HOW TO] Convert ANZ Bank Statement to Excel

Introduction

When it comes to tax time, I usually need to take my bank statement and convert it to Excel. This way I can hand it off for my accountant to process.

The problem is that getting data out of PDFs can be a pain in A**!!

I recently had to take a ANZ bank statement to convert to Excel and it was not fun. Some problems that comes up:

• If doing manual copy/paste - the text is not easily selectable.
• The transactions can span multiple pages so had to double check the errors. There are also random “Summary rows” to show the totals for each page.
• Dates do not have years - so if your statement spans across years you will need to be careful.

In this post, I will show examples of how to efficiently convert your ANZ bank statement to Excel!

Our steps are as follows:

• Step 1: Download the statement from ANZ
• Step 2: Convert the PDF to Excel
• Step 3: Clean the data

Step 1: Download the statement from ANZ

Download statement using Internet Banking

To access your statements, proceed as follows:

1. Sign into your Internet Banking account
2. From the main page, choose the account whose statement you want to see
3. Find and click on the “View statements” option located in the left sidebar
4. You will find your statements organized in the “Statement issue date” area

(If necessary, use the “Filter by statement issue date” feature to narrow down your search)

To view a specific statement, either click on the respective date or the download symbol. The statement will open in a new browser window or tab."

Download statement using the ANZ app

If you have the ANZ app you can download your statement on your phone.

1. Open the ANZ App and log in with your credentials.
2. Navigate to ‘More,’ followed by selecting the ‘View statements’ option.
3. You will be presented with a list of your accounts – this includes both your active and previously closed accounts. Select the one for which you wish to view statements.
4. From here, you can choose to view your most recent statement or find older statements sorted by year. Simply tap on the statement you wish to view to see all the detailed information.

Analysis of the statement

• We can see that it has 5 columns: “Date”, “Transaction Details”, “Withdrawals”, “Deposits” and “Balance”.
• The date column only has the format of <day>/<month>. We will need to add in the year.
• The transaction details can have multiple rows.
• There is no currency symbol - and the format uses commas - eg “1,100.10”
• Transactions can span multiple pages and there is a row that contains the totals for each page “Totals at end of Page”. This column will contain currency symbol (unlike the other rows)

Step 2: Convert the PDF to Excel

There are several ways we can convert the ANZ bank statement to Excel. This ranges from just manual copy and paste, using tools like PdfDodo and Adobe Pro.

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

Now you can extract the Standard bank statements by using PdfDodo.

Just sign up with an account and you try it out for free.

To do start the Excel conversion:

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

Option 2: Using Adobe Acrobat

First, you will need to convert your PDF to an Excel file format, as Excel files can easily be converted to CSV.

There are several online tools and software applications that can help you convert a PDF file to Excel. Here are a couple of methods:

• Open the PDF file in Adobe Acrobat (not the free Reader).
• Go to “File” > “Save As Other” > “Spreadsheet” > “Microsoft Excel Workbook.”
• Save the file to your desired location.

Problems with using Adobe Acrobat

• Struggles to seamlessly transfer data spanning across multiple pages into a single Excel sheet. When attempting to convert a bank transaction table extending over several pages, it instead recognizes it as separate tables.
• The cost might feel excessive, especially if your primary use-case is straightforward PDF data extraction.
• Raises concerns over data storage transparency; reaching out to customer support for clarification proves to be challenging.

Option 3: 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

The pro of this method is that there is no additional software needed. Additionally, you have full control over which data is transferred, enabling a selective data transfer process.

Obviously the disadvantage is that it takes time. So do not consider this if you are time poor and have a PDFs with a large amount of data or complex structures.

If your PDF contains a lot of different styling and formatting this could be troublesome.

I have found in the past that when copying manually, the formatting and styles gets stripped out. This especially happens when dealing with nested tables and rows.

Step 3: Clean the data

The only thing that I need to clean is the date column (adding the year) and removing the “Totals at end of page” row.

Removing the “Totals at end of page” row should be simple. The hard part is fixing up the dates.

Adding the Year

We can see that our “Date” only has the day and year. For example “21 FEB”. This can cause confusion when the statement spans multiple years.

So we need to use the DATE function to add the year to those dates.

In Excel add a new column with the formula:

=DATE(2023, LEFT(A2, FIND("/", A2) - 1), MID(A2, FIND("/", A2) + 1, 2))

This formula takes the year 2023 and the value from our “Date” column and adds the year.

Explanation of the formula

• LEFT(A2, FIND("/", A2) - 1) extracts the month from the text. In this case, we find the first "/" and take take the left section.
• MID(A2, FIND("/", A2) + 1, 2) extracts the day from the text. In our case, we extract 2 characters from the middle of a text string. This assumes that our text has 2 characters after the slash ("/")
• FIND("/", A2) returns the position of the first “/” character in the text in cell A2.
• DATE(2023, …, …) constructs a full date using the year 2023.

• We can then drag the formula down in Excel to fill out other columns.

Final thoughts

Overall, using PdfDodo to convert our ANZ bank statement to Excel is the most effective way to extract the data.

It is quick and accurate - compared to getting someone to do it manually - this can take days if you have multiple statements.

Another option is to use Adobe Pro - but this has the disadvantage that it is not focused for bank statements. So it will not combine tables across multiple pages, will not recognize the statement period and that does not work with password protected Pdfs.