[RESOLVED] Export CitiBank Bank statements in Excel

Introduction

One common problem is converting old bank statement from PDF to Excel.

I recently had this problem with a client since this client just dumped bank statements from CitiBank for the last 7 years.

Instead of manually copy and pasting from the PDF (which could take hours or days) we can utilize a automated process.

In this post I will show you a quick and accurate way to convert CitiBank bank statements to Excel with little manual steps.

Quick summary of the steps are as follows:

• Step 1: Download the statement online or via the app
• Step 2: Convert the Bank statement to Excel
• Step 3: Data cleansing in Excel

Step 1: Download the statement online or via the app

Citi Online

• Visit the Citi Online page and log in to your account.
• Once logged in, find and click on “Services” in the top menu.
• Click on the “My Profile” option.
• Be prepared to enter the One-Time PIN (OTP) that will be sent to your registered mobile number when prompted.
• From the left-hand menu, select “Statements and Notices”.
• Click on “View/Download Statement”.
• Choose the account and time period for which you would like to see the statement.
• To download the statement, click “Download”.

For quick access in the future, click here to go directly to your statements page after logging in.

Citi Mobile App

• Tap the Citi Mobile® App icon to open it.
• Tap on the “Profile and Settings” icon located at the top left of your screen.
• Find and tap “Statements” under the “Financial Settings” section.
• Select the account and define the time period to view the respective statement.
• Your account statement will be displayed in a PDF format within the app.

The result bank statement from CitiBank might look like the following (I have redacted personal identifying information):

Some things to note:

• The dates does not have years - so we need to clean this up in our Step 3
• The “Debits” is named “Amounts Subtracted” and “Credits” as “Amounts Added”
• The balance only displays on the last row of the day. For example if you have multiple transactions in a day, the balance will display on the last row.

Step 2: Convert the Bank statement to Excel

👉 Use PdfDodo to extract the data to Excel

Now you can extract the Standard bank statements by using PdfDodo.

Just sign up with an account and you try it out for free.

To do start the Excel conversion:

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

You can then open up the Excel file and inspect your transactions so that it matches with the Standard Bank statement.

Alternative - Convert Pdf to Microsoft Word

Converting a PDF to an Excel file using Microsoft Word is a bit of a roundabout process, but it’s doable. Here’s a relaxed step-by-step guide to help you do just that:

1. Open Microsoft Word on your computer.
2. Click on “File” in the top left corner.
3. Choose “Open” and find the PDF file you want to convert. Select it and click “Open”.

📝 Note: Word will give you a message saying it will convert the PDF into an editable Word document. That’s what we want, so go ahead and click “OK”.

4. You can then copy and paste the bank transactions that you are after. Press Ctrl+A on your keyboard (or Cmd+A on a Mac) to select everything, or just use your mouse to select the bits you want.

This results in the following Word document:

• Surprisingly the conversion looks ok. However when I try to take copy the bank transactions section and paste it into Excel, the columns are out of whack:

As we can see above, the date column has shifted to the “Description” column. So we will need manual intervention and split that back out.

Drawbacks of using Microsoft Word for conversion

• Can result in weird formatting and random arrangement of columns
• Does not work with Scanned PDFs. The result will be one massive image so you cannot copy and paste from it.
• Does not work with 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.

Step 3: Data cleansing in Excel

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.

Final thoughts

Overall using PdfDodo to extract the data from your CitiBank bank statement and then dump into Excel is not too laborious.

The app will get you there 99% of the way, although you will need to do a bit of data cleansing.

If you decide to just manually copy/paste from the PDF or use Microsoft Word - it could take more time and risky higher chance of mistakes.