3 Ways to Convert pdf bank statement to CSV FreshBooks
Best 3 ways to convert your scanned bank statement PDFs to Excel and import into FreshBooks
Sep 21, 2023 | Read time 8 minutes![](/img/blog-img-excel.png)
π Table of contents
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
Date | Amount |
---|---|
Option 2
Date | Amount Spent | Amount Earned |
---|---|---|
- 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.
- Navigate to the Accounting section.
- 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.
- Under the Unreconciled Bank Transactions, click Add Bank Transaction.
- Then, click the Import from File option.
- Browse and select your desired CSV file.
- 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).
- 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.
- After the match-ups and setting the Date Format, hit the Import Transactions button.
- 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
ormonth-day-year
- Should not have currency symbols (
$
) - Should not be over 5MB or 10000 rows!
π About the Author
G'day! I am Ken Ito a software engineer based in Australia. I have worked as a software engineer for more than 10 years ranging from roles such as tech lead, data analyst to individual contributor. I have experience in creating code (python, javascript) to extract data from PDFs for ETL and financial statement analysis for accounting purposes.
During those years, I have worked for Government departments, companies like NTT (Nippon Telegraph and Telephone), Bupa and not-for-profit organizations.
![Step by Step to convert your scanned PDFs to Excel without losing formatting.](/img/blog-img-excel.png)
3 Ways to Convert Pdf to Excel Without Losing Formatting
Sep 21, 2023Step by Step to convert your scanned PDFs to Excel without losing formatting.
![Tutorial on how to convert your MayBank Bank Statement PDFs to Excel format.](/img/blog-img-excel.png)
[HOW TO] Convert MayBank Statement to Excel
Sep 19, 2023Tutorial on how to convert your MayBank Bank Statement PDFs to Excel format.
![Best ways to convert your scanned PDFs to Excel format.](/img/blog-img-excel.png)
Convert ABSA Bank Statement to Excel
Sep 17, 2023Best ways to convert your scanned PDFs to Excel format.