HOW TO - Convert Wells Fargo Bank Statement to Excel

Introduction

A few of our users use Wells Fargo for their banking. The problem is that the statements come in PDF.

In this post I will go over the steps to take your Wells Fargo bank statement (PDF) and convert it to Excel or CSV.

We can then use the result to do data analysis or import it to a accounting software like Xero, Quickbooks, etc.

Generally the steps are as follows:

1. Download the statement from Wells Fargo website
2. Use PdfDodo to extract the transactions
3. Clean up the data

Downloading the statement

• Step 1: Click Small Business. Enter your username and password and click Go
• Step 2: Click on Statements & Documents
• Step 3: Click Statements and Disclosures
• Step 4: Choose the correct account from the dropdown and Click on the statement you want to download*
• Step 5: Click the download icon in the top right corner and save

Analysis of Wells Fargo bank statement

To accurately convert our bank statement from Wells Fargo, we first need to do a bit of analysis on what it looks like.

The first page seems to be just for personal info (obviously I have redacted the personal information):

The second page and onwards is where it gets more interesting. This is where all of your bank transactions will be:

Observations

• The dates do not include the year. Just under the format of <Month>/<Day>. This seems to be similar to other banks. Now if you have a statement that goes across years - in your data cleansing step, you will need to add it in.
• The “Ending Balance” column spans multiple rows. It will only display the daily balance. So lets say you have multiple transactions within one day, the “Ending Balance” value will only appear on the last row for that day!

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

With using Wells Fargo bank, if you have PDF statements from them, then consider using PdfDodo to extract the data and convert it to Excel.

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

Did you know that starting with Word 2013, Microsoft Word can handle PDFs? It’s really useful if you’re looking to edit a PDF in Word or incorporate it into other Office tools.

Here’s how to get it done:

• 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.
• Word will try to mirror the PDF’s format. Some differences might pop up depending on the original PDF’s design. Once converted, you can edit as you would any Word document.

One issue from the above that I see is that for some transactions, if Word cannot convert - it will keep it as a image (as in the above image).

When you finally copy all of this to Excel, you will also copy the image. So you will need to manually enter the data.

Pros

• If you’re already a Microsoft Word user, there’s no need to download or purchase separate software just for PDF conversion. Additionally, if you already familiar with Word, then its pretty easy to use.
• Works with Word (2013 and later), you can convert PDFs without incurring additional charges, unlike some dedicated PDF conversion tools.
• You own the data - the conversion happens offline on your computer, there’s a reduced risk associated with uploading sensitive documents to online converters.

Cons:

• Word might not always reproduce the exact formatting of the original PDF, especially if the PDF is complex with lots of graphics, columns, or special formatting.
• Pictures, charts, or other graphics might be misplaced, have reduced quality, or sometimes not be converted at all.
• The file size of the PDF might be even bigger than the original Word document
• If the PDF uses fonts that aren’t available on your computer, Word might substitute them, causing weird appearances.
• Does not work with Scanned PDFs or password protected PDFs.

Alternative 2: 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.

Issues

The obvious issues with this is that its a laborious process and prone to mistakes.

Additionally it will not work with scanned PDF bank statements.

Data clean up after conversion

After you have extracted your Wells Fargo bank statement into Excel, the next step is to clean the data.

Check the date format

Make sure that the dates are correct. This follows the American format of <Month>/<Day>. If you are using a machine with UK local, then make sure to double check the date format.

Adding the Year

The “Date” column in the transactions does not include year. 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.

Fix for Rolling Balance

Another issue is that the transaction list only displays the balance within the day. It does not display the balance after each transaction.

To update for a “Rolling Balance” we can do the following:

• Take the initial balance and place it into some cell - in this case “D2”
• In the “Rolling Balance” column

=D2+B3-C3

This formula takes the previous balance (D2) and adds the debit amount while subtracting the credit amount. The result will be your new balance.

• After inputting the formula in D3, you can drag down the small square at the bottom right corner of the cell (this is the fill handle) down the column to copy the formula to subsequent rows. This will calculate the rolling balance for each transaction in the list.

Final thoughts

Overall the Wells Fargo bank statement is fairly simple to convert to Excel or CSV.

We can use PdfDodo to do the conversion or alternative options using Microsoft Word or manually copying and pasting.

The alternative options are great if you already own the Microsoft Office suite.

After our conversion, we still need to do a bit of cleaning the data - particularly for the dates and rolling balances.