3 Ways to Convert pdf bank statement to CSV FreshBooks

Introduction

A common task for users of FreshBooks (and book keepers) is to do Bank Reconciliation.

A recent problem I had with a client is that they gave me a bunch of PDF bank statements and told me to perform the reconciliation.

Now if I did not have any tools at my disposal, I am looking at:

• Manual data entry of the bank transactions to CSV
• Formatting the CSV to a structure that FreshBooks wants

This can be time consuming and I always like to look for efficient ways to complete a task (more time for me to play golf!)

In this post I will go over the steps to do this - so hopefully will help you!

Step 1: Retrieve your bank statement

If you already have your bank statement PDF ready, then just skip this step and move onto step 2! Otherwise just go to your online banking or app and download the relevant statement!

Step 2: Convert your bank statement to Excel

The second step is to select a PDF to excel extraction tool. Now there are multiple options out there, but PdfDodo would be most suitable.

This is because it is optimized for bank and financial statements and free (reasonably) for use.

👉 Use PdfDodo to extract the data to Excel

For this example, I will be HSBC bank.

To do start the Excel 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:

After that, can open up the Excel file and start doing our analysis.

Benefits

Now the good thing about this is that it helps you with:

• Converting PDF bank statements accurately and saves you time!
• Works with scanned documents and text-based PDFs as well.
• Also works with password protected PDFs.

Alternative - Convert Pdf to Microsoft Word

If you do not like to use online tooling such as PdfDodo, you can use existing Microsoft Word. This is great if you already have the Microsoft Office suite installed and prefer a offline option.

(One caveat is that it does not work with Office versions 2013 or below)

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.

The conversion is usually not 100% - you will see layout issues and copying and pasting can be a nightmare if the tables span across multiple pages.

As seen in the below - the HSBC bank transactions table is all jumbled up:

Pros and cons of using Microsoft Word

• No need for separate software if you use Microsoft Word.
• User-friendly for those familiar with Word.
• Compatible with Word (2013 onwards) for free PDF conversion.
• Secure offline conversion, safeguarding sensitive data.
• Potential misalignment in formatting from original PDFs.
• Possible issues with graphics: misplaced, degraded quality, or omitted.
• Converted PDF might have larger file size than Word document.
• Font substitutions can lead to unexpected appearances.
• Incompatible with scanned or password-protected PDFs.

Step 3: Format the Excel result to be compatible with FreshBooks

If you have multiple transactions that you’d like to add to your Bank Reconciliation, you can import them in bulk with a CSV file. When putting together the CSV, ensure the file has these required headers, and then add your data:

Option 1

Option 2

• Date - Use the same date format for all
• Amount (1 Column) - Use positive numbers for money in transactions and negative (-) numbers for money out transactions
• Amount Spent and Amount Earned (2 Columns) – Use positive numbers only for both

When you have completed the CSV preparation, we can then start to import the file.

1. Navigate to the Accounting section.
2. Locate and click on the # left to match link next to the Bank Account you wish to reconcile.

If prompted, choose either Skip or Confirm and Continue if you see Likely Matches.

3. Under the Unreconciled Bank Transactions, click Add Bank Transaction.
4. Then, click the Import from File option.
5. Browse and select your desired CSV file.
6. Now, align the fields with the respective columns in your file using the dropdowns (For instance, the Date field should align with the Date header in your CSV).
7. When you encounter the field for Amount Format, decide between 1 Column or 2 Columns. For those who pick 2 Columns, remember to match both Amount Spent and Amount Earned fields.
8. After the match-ups and setting the Date Format, hit the Import Transactions button.
9. New transactions will now be listed, ready for reconciliation. If any transactions precede the Opening Balance Start Date, adjust the Start Date to encompass these new items for accurate matching.

Some things to keep in mind

• Transactions must be unique within the CSV. For duplicates, follow the steps below to remove them.
• For ease, every transaction imported from the CSV is tagged as Imported via CSV in the description. This helps distinguish between imported and manually entered transactions.
• If headers such as “Amount” (or “Amount Spent” and “Amount Earned”), “Category”, “Date”, and “Description” aren’t present or if one is missing, it may cause issues. Additionally make sure that headers are in the first row!
• Error can pop up if you have mixed formats. For example mixed date formats or having negative numbers in both “Amount Spent” and “Amount Earned” columns within the same row.
• Make sure that the date has the correct format. Dates should be written using dashes (-) or slashes (/) as separators, e.g., 01/17/2020 and 01-17-2020.
• Make sure there are no currency symbols in the “Amount” columns
• You will get an error if your CSV file exceeds 5 MB or contains more than 10,000 rows.

Final thoughts

Overall I found that you can convert your PDF bank statement to CSV to be imported to FreshBooks is relatively easy.

We can use PdfDodo which focuses on financial and bank statements to extract the data to CSV. After that we can open up the CSV in Excel and format it to the structure that is required from FreshBooks.

Generally to get a CSV to be the format of FreshBooks we need to:

• Must have headers of “Date”, “Amount Spent”, “Amount Earned”,
• The dates must be format of month/day/year or month-day-year
• Should not have currency symbols ($)
• Should not be over 5MB or 10000 rows!