Convert ABSA Bank Statement to Excel

Introduction

One problem that I had recently is to convert ABSA bank statements to Excel format.

You would think this might be a easy problem to solve but can be complex.

A few issues that arises:

• the ASBA PDF bank statement is password protected
• we need to clean up the data for things like locale/region specific
• transactions are spread across multiple pages so will need to be combined correctly without duplicates or error.

Now imagine that we are dealing with more than just one but a dozen statements. You can see that our problems will compound!

Note

Now if you live outside of South Africa, you may never have heard of this bank. ABSA Bank, also known as Amalgamated Banks of South Africa, is a South African-based financial services group.

It offers a range of banking and financial services including retail, business, corporate, investment banking, as well as wealth management products and services.

It is headquartered in Johannesburg and is one of the largest banks in South Africa.

In this post, I will go over my process of converting a ASBA bank statement to Excel accurately and efficiently.

Step 1: Download the bank statement from ASBA bank

Sign up to receive eStatements through ABSA Online Banking, utilizing Cellphone Banking, or by stopping by your nearest branch.

• To access your eStatements, download and set up either the Striata Reader or the Adobe Reader. You’ll find the links to these tools in the eStatement cover email.
• Your eStatement will arrive in your email inbox, attached as a PDF to the email.
• For enhanced security, opening the file will require a password, which will be prompted once you attempt to open it.
• To find out which password to use, refer to the instructions in the cover email accompanying your statement.

Installing the Striata Reader

1. Ensure you have an active internet connection.
2. Visit the Striata website to obtain the Striata Reader. Adhere to the guidelines outlined for your specific cellphone or computer OS.
3. Complete the installation process to have the Striata Reader set up on your device.
4. Check your inbox for a welcome email, which will detail the necessary steps to access and view your eStatement

Remove the password protection

ABSA bank statements are password protected. So we need to remove this first. We can do this using Google Chrome (or Edge)

1. Open Google Chrome.
2. Drag and drop the PDF file into the Chrome browser or open it via “Ctrl + O” (Windows) or “Cmd + O” (Mac).
3. Enter the password to open the file (if it is password-protected).
4. Go to the “Print” option (you can press “Ctrl + P” on Windows or “Cmd + P” on Mac).
5. Change the destination to “Save as PDF.”
6. Click “Save” and choose a location to save the new PDF.

The new PDF file will be saved without password protection.

Step 2: Extract the data from PDF to excel using PdfDodo

We can use PdfDodo to extract the bank transactions from your ASBA bank statement to Excel.

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.
• Step 5: Find the table that you are interested in and hit “CSV”. This will give you the CSV format.

Option 2: Use Microsoft Excel

One alternative option is to use Excel itself.

Now if you already have Microsoft Office 365 then this is a good option. You can use it to pull data from your text PDF bank statements. Just a heads up, it won’t work on scanned docs.

Note

This option only works with Microsoft 365 (Home, Personal, Business, Business Premium, Enterprise)

If you have the following versions, then you will not see this:

Office 2016 (Home, Student, Professional, Business Standard), 2019 (Home, Student, Professional, Business Standard)

Steps to Export Data from PDF to Excel:

To initiate the process, first choose the spreadsheet you intend to use in Excel and navigate to the “Data” tab. Then, click on the “Get Data” option situated at the left corner of the ribbon and hover over “From File” to select “From PDF.”

Click on “Get Info,” followed by “From File” and then “From PDF” Identify and choose your document in the pop-up window before hitting “Import.” Choose your file and press “Import”

Following this, the Navigator window will become visible. It displays a list of tables and pages from your PDF on the left side. Utilize the search bar at the top to find a specific item or choose one from the list to preview it on the right.

Identify the object you wish to transfer and press “Load” situated at the lower section of the window. Execute the data loading process

Once the transfer is successful, the data will appear on your spreadsheet, accompanied by the “Queries & Connections” sidebar. This feature facilitates the easy modification of the linked data as per your requirements.

Limitations:

• Requires you to have the latest Excel installed. Older versions of Excel such as 2019, 2016 does not work.
• I found its not 100% accurate.
• Does not work with scanned PDFs

Option 3: Use 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.
• Step 3: You can then find your table of transactions and copy and paste all the row and columns into Excel.
• Step 4: We can then save the Excel file as XLSX using File -> Save As -> Save As Type -> XLSX

Limitations:

• Requires you to have Word installed.
• I found its not 100% accurate.

Step 3: Clean up the data

Overall the data in the bank transactions is quite clean so there is not much clean up needed. A few things we need to be aware of:

• The format of the dates is <day>/<month>/<year>. So if you are in the United States and working with this, you may need to change the format.
• The dollar amount is displayed with a “period” (unlike banks in europe where they use commas). For example “300.12” instead of “300,12”.

Final thoughts

Over the ABSA bank statements are not too complex to convert to Excel once we have the right tools.

We can convert the PDF bank statements using PdfDodo. First we need to download the statement using Striata Reader, remove the password.

After that we can import it into PdfDodo for it to extract the data.

Alternative options to extract the data includes using Microsoft Excel or Word (although you will need Microsoft 365).

After that we just need to review and do minor data cleansing.