[HOW TO] Convert Ally Bank Statement as CSV

Introduction

One recent task that I came across for a client was to convert a Ally Bank statement to CSV.

Ok - so I was thinking to myself - surely Ally bank as an online bank should have a CSV version of your transactions.

Unfortunately, this only goes back for a certain period. So the client was sending me PDFs .. sigh!

In this post, I will go over how to convert a PDF Ally bank statement to CSV.

Note: About Ally Bank

Now if you are outside of the United States, you may not have heard of Ally Bank before. It is an online-only bank and started as GMAC under General Motors. They started in 2009 with a focus on customers who are interested in competitive interest rates, high-yield savings accounts, checking accounts, Certificates of Deposit (CDs), mortgage and auto loans. They also offer investing services with Ally Invest.

Step 1: Download the statement from Ally bank

The first step to download the credit card statement PDF file from your bank.

Accessing and downloading your bank statements from Ally Bank is a straightforward process.

• Step 1: Enroll and Log In If you haven’t already, enroll in Ally Bank’s online services. Visit ally.com and click on the “Log In” button to access your account.
• Step 2: Access Your Profile Once logged in, click on your name or profile icon located on the main navigation bar to access your profile menu.
• Step 3: Navigate to Statements In the expanded menu under your profile icon, find and click on “Statements and Tax Forms” which is listed under the Bank Accounts section.
• Step 4: Select and Download Statement Browse through the list of available statements and select the one you need. Upon selection, the statement will automatically download to your device.
• Step 5: View and Print Statement Open the downloaded statement using your preferred web browser: Internet Explorer, Google Chrome, or Safari. Click anywhere on the page to reveal a submenu, then navigate to and click on the printer icon within the submenu to print your statement.

Now that we have the bank statement, we can move on to analyzing the statement itself.

Analysis of the Ally bank statement

To be accurate and efficient with our bank statement to CSV conversion, we should start to have a look at the statement:

• We can see that they have 5 columns of “Date”, “Description”, “Credits”, “Debits”, “Balance”.
• The transactions seem to be relatively easy to copy - this can help when you just want to manual copy and paste methods.
• Transaction “Date” follows the format: day/month/year - we need to keep this in mind when importing into accounting software like Xero, QuickBooks
• Bank transactions can have rows with multiple lines.

Step 2: Convert the Bank statement

Now to convert your bank statement to CSV format, we can use PdfDodo to do this. It has generous free tier and can get you 99% of the way there.

Option 1: Use PdfDodo to convert PDF to CSV

One option is to use PdfDodo to convert your PDF bank statements to Excel format.

To do this 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 “Excel”

Alternative 1: Use Microsoft Word

If you do not like to use online tooling such as PdfDodo, you can use existing Microsoft Word.

We can use the PDF to Word conversion first, and then copy the data from the Word document to Excel.

Consider this option if you want something that is offline and already have Microsoft Word installed.

(Keep in mind that it will only have this PDF to Word conversion for versions 2013 or above)

To do this we following the below:

• Open Microsoft Word.
• Click on “File” at the top-left.
• Select “Open”.
• Browse to your PDF’s location.
• Choose the PDF and click “Open”. A quick note: Word will make a conversion copy of your PDF. Don’t worry; your original PDF stays untouched.
• Press “OK” to start the conversion.

After Word finished up the conversion - it looks ok, but when I tried to copy it seems like its a massive image.

So this ends up being incredibly frustrating to copy and paste to Excel

Overall rating for Microsoft Word

Using Microsoft Word for PDF conversion is a handy feature if you already have it installed. This feature also ensures secure offline conversion, which is great for handling sensitive data.

However it is only available for free from Word 2013 onwards.

However, there are some downsides. You might face formatting misalignments from the original PDFs, and graphics could be misplaced, lose quality, or be omitted. The converted PDF may have a larger file size than the Word document, which could be a concern.

Font substitutions can also occur, leading to unexpected appearances in the document. Lastly, this feature doesn’t work with scanned or password-protected PDFs, which limits its usefulness in certain scenarios

Alternative 2: Use Adobe Acrobat

Another alternative is to use Adobe Acrobat itself. One would think this is a obvious pick - since they created the PDF format!!

But unfortunately, I found out its not 100% and stuffs up on a few scenarios.

If you want to go down this path, then follow the below steps.

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

Step 3: Clean up the data

The final step is to clean up the data so that it can be reconciled or imported into your accounting software like Xero or QuickBooks.

We need to consider:

• Check that the date format will suit your accounting software package - Ally Bank uses the format of day/month/year
• Remove rows that are not needed - eg the first or last row that contains the “Beginning Balance” or “Ending Balance”
• Consider combining the “Credits/Debits” column. The Ally Bank statement seems to place a “$0.00” when its credit or debits. For example when it is a “credit” transaction, they have a $0.00 for the “debits” column. This seems a bit redundant.
• Consider removing the currency symbol for our credits, debits and balance. Accounting software like Xero or Quickbooks does not like this!

Final thoughts

Converting the PDF Ally Bank statement to a CSV seems relatively simple if you use the right tools.

Using PdfDodo, gets you 99% there. It has a free tier and the conversion is accurate. Other options include using Microsoft Word or Adobe Acrobat.

Microsoft Word comes with a built in PDF converter - but its not great. As with Adobe Acrobat - you will have to pay!