Convert Kotak Bank Statement to Excel

Introduction

One problem that I recently came across is converting the Kotak bank statements PDF to Excel.

You would think this is a easy thing to do - but seems like taking data out of PDFs is pain in ****

Theres things that we need to consider. Some things on the top of my mind:

• correct date and currency formats
• making sure the data is extracted from multiple pages - for example if your bank transactions span two or more pages.

In this post I will go over the steps to convert your Kotak bank statement from PDF to Excel using PdfDodo.

Generally this involves:

1. Download the bank statement from Kotak Bank
2. Convert the bank statement to Excel
3. Clean up the data in Excel

A note on Kotak Bank

Kotak Mahindra Bank is one of the leading private sector banks in India. Established in 1985 as a non-banking financial company, it later received its banking license from the Reserve Bank of India (RBI) in 2003, transitioning to a full-fledged bank.

It offers a wide array of banking services and financial products to both individual and corporate customers, including personal banking, wealth management, life insurance, and investment banking.

Step 1: Download the bank statement from Kotak Bank

Checking your Kotak bank account’s transaction history no longer necessitates a physical passbook, thanks to the e-statement service provided by the bank. This service allows you to review and download your account statements at your convenience.

Here’s how you can access a PDF version of your Kotak bank account statement through various online channels:

Via Net Banking:

• Sign in to your Kotak Mahindra Bank internet banking account.
• Navigate to “Banking” followed by “Saving/Current Account” to find and view your account statements.
• Select your account number and specify the statement period - this can be a predefined length or a custom date range. Choose PDF as your preferred format. = Hit the download button to get a PDF file of your statement, which compiles all transactions for the selected period.

Via Mobile Banking:

If you prefer using a mobile device, it’s just as simple:

• Make sure your email address is registered with your bank account to enable receipt of statements via email.
• Open the Kotak Mobile Banking app and select “Banking”, followed by “Account Statement” to request your statement.
• Choose your account number and the length for the statement, be it 1, 3, or 6 months, or specify a custom date range.
• Input your email ID and submit your request.
• You will promptly receive your account statement in PDF format at the specified email address.

Step 2: Convert the bank statement to Excel

We can use PdfDodo to extract the bank transactions from your Kotak bank statement to Excel.

To do start the CSV 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.
• Step 5: Find the table that you are interested in and hit “CSV”. This will give you the CSV format.

Option 2: Use Microsoft Excel

One alternative option is to use Excel itself.

Now if you already have Microsoft Office 365 then this is a good option. You can use it to pull data from your text PDF bank statements. Just a heads up, it won’t work on scanned docs.

Note

This option only works with Microsoft 365 (Home, Personal, Business, Business Premium, Enterprise)

If you have the following versions, then you will not see this:

Office 2016 (Home, Student, Professional, Business Standard), 2019 (Home, Student, Professional, Business Standard)

Steps to Export Data from PDF to Excel:

• Step 1: Open the Microsoft Excel application available through your Office 365 subscription.
• Step 2: Navigate to the “Data” tab present at the top menu.
• Step 3: In the “Data” tab, click on “Get Data”. Hover over “From File” and choose “From PDF.”
• Step 4: Identify and select the PDF file containing the data you wish to import.
• Step 5: You will be redirected to the “Navigator” window where different sections of the PDF are recognized as separate tables. Browse and click on different tables to preview the data and determine the one you wish to import.
• Step 6: Based on your preference, select one of the two options:
  • To import all data: Click “Load” to import all the data as a comprehensive table. You can remove unnecessary data later.
  • To transform data before importing: Click “Transform Data” to open the “Power Query Editor.” Here you can manage data by:

You can then have the option to remove unwanted columns by right-clicking and deleting it.

Alternatively, if the column type is wrong (eg should be date instead of text), we can right click and change the type.

• Step 7: After making the necessary adjustments in the “Power Query Editor”, click “Close and Load” to finalize the import process.

Limitations:

Now this option is not ideal for all scenarios. There are a few caveats.

• The import with PDF does not exist on all versions of Excel. It requires you to have the latest Excel installed - this is Office 365. Older versions of Excel such as 2019, 2016 does not work.
• I found its not 100% accurate.
• Will not work with scanned PDFs. Only works with text based PDFs.
• You can not do bulk imports.

Option 3: Use Microsoft Word

One option that not a lot of people have thought about is to first convert your PDF file to Word.

Now if you already have Microsoft Word this is easy.

• Step 1: Open up Microsoft Word
• Step 2: Select a PDF file. If all goes well, Word converted the PDF file to a Word document.
• Step 3: You can then find your table of transactions and copy and paste all the row and columns into Excel.
• Step 4: We can then save the Excel file as XLSX using File -> Save As -> Save As Type -> XLSX

Limitations:

• This needs you to have Microsoft Word installed.
• I found its not 100% accurate and need to adjust some of the text and dates.

Step 3: Clean up the data in Excel

• Kotak bank contains two “DR/CR” columns. This represents the debits or credits. These seem to be repeating the same info, we can remove one of those columns.
• The transaction date follows the format: <day>/<month>/<year>. Keep this in mind if you are processing this from another region such as United States where the day and month are flipped.

Final thoughts

Overall the Kotak bank statement is easy to convert if we use the right tools.

Using PdfDodo takes you 99% of the way there with little data clean up afterwards.

If you already own Microsoft Office 365, then you can use the feature import from PDF feature. Keep in mind that this works with text PDFs and not scanned ones.

Additionally it does not support password protected or scanned PDFs.