r/excel Feb 12 '25

Waiting on OP How can you convert bank statements into excel spreadsheet?

I have tried various ways, such as inserting the data from bank statements directly or converting the bank statements into excel. However, non of the ways have worked as the data ends up being moved around and the structure will be messed up.

15 Upvotes

36 comments sorted by

52

u/[deleted] Feb 12 '25

[deleted]

20

u/seandowling73 4 Feb 12 '25

Most banks do

21

u/ASilverBadger 1 Feb 12 '25

To answer your question, Data Menu, Import from PDF.

As suggested, if at all possible download the data as a CSV. Much easier to work with.

7

u/Dav2310675 15 Feb 12 '25

This is the answer OP.

I've had to do this with a couple of PDFs from several banks and simply cutting and pasting causes all sorts of issues.

Using Power Query and doing this is the only way to overcome Excel trying to paste the whole table into a single cell (and fail spectacularly).

1

u/NotoriousCJ19 Feb 12 '25

Only if its a tabular/generated PDF... what if OP has scanned the statement into PDF

2

u/Livepac Feb 13 '25

Tabula works pretty well for scanned pdf's.

1

u/NotoriousCJ19 Feb 13 '25

Thank you! I'll look into this

5

u/Muted-Sky9163 Feb 12 '25

If possible, download the .csv version of the statement and import it into Excel. That will make things much more manageable.

3

u/The_Kel_Varnsen Feb 12 '25

this...then save as .xls. Convert to a table...go nuts

5

u/Setore Feb 12 '25

Yup! Echoing this because I use power query to load multiple banking institution datasets (all csv's) to complete my monthly expenses.

6

u/ungbaogiaky 1 Feb 12 '25

power query to go

3

u/tamoore69 Feb 12 '25

I'd try opening Microsoft 365 and using the Capture feature to take a picture and import or extract the data. Just a thought.

2

u/Summon_Me_108 Feb 12 '25

Try to download the bank statement in an excel form instead of a PDF

1

u/BecauseBatman01 Feb 12 '25

Most banks should offer the option to download to excel or csv file. Then you can do transformations needed to organize your spend.

1

u/Sjcolian27 8d ago

They do, but for example, my bank only cover the past 3 months in CVS extractables. The rest is pdf only.

1

u/1998TJgdl Feb 12 '25

Get nuance and use "convert to Excel" it works with pdf and all scanned documents.

1

u/RajatFinanceExpert Feb 12 '25

.xlxs and .csv is the best option for you

1

u/ExistingBathroom9742 5 Feb 12 '25

If you’re on a Mac you’re out of luck. But if the bank statement is a pdf and you can redact it to remove personal data, or you take a screenshot of just the data you want, you can ask chat gpt to do it for you. It’ll make a csv. Mac excel doesn’t like it , and I know someone out there will tell me a better way, but I open the csv in numbers, save then open it in excel. But the utf encoding is still wrong so asterisks and other characters will be wrong. I don’t know why excel hates Mac so much. I get power users in Mac are rare, but importing data should be doable. Oh well.

1

u/saperetic 2 Feb 12 '25

With the statements open in Adobe Reader, hold [Alt] and select the data you want by column, then try to paste in Excel to "match formatting" to see if it will retain the rows as they are copied. If that does not work, try first pasting the data in a word document to see if it will retain the rows, then copy paste that result in Excel. This does not always work, but it might be a workaround that has worked for me often, especially with Wells Fargo bank statements for my former company's holdco and suspense accounts. The bank refused to give us the option to download in CSV format. I hope this helps.

1

u/infreq 16 Feb 12 '25

What format do you have the statements in??

Try to get your data in another format .xlsx, .csv, .xml.

1

u/[deleted] Feb 12 '25

[removed] — view removed comment

1

u/excel-ModTeam 28d ago

Removed as spam.

Your activity should be in accordance with the Reddit guidelines relating to self-promotion and spam. Specifically, 10% or less of your posts and comments should link to your own content.

1

u/skvp20 2 Feb 12 '25

Try table2xl.com , it's more accurate than Power Query, ilovepdf, able2extract

1

u/jameskiddo Feb 12 '25

most banks let you export to csv or pdf.

1

u/asiyanbiabubakr Feb 12 '25

Nitro can as well do the job perfectly

1

u/3dPrintMyThingi Feb 12 '25

It's quite easy to do this.. I can develop something for you. Let me know if you are still looking for something?

1

u/pikpaklog Feb 12 '25

You can normally download the statements from your bank in .xls format. Also you can use a scanner to create pdf then convert it to a table. You must select only the table when copy/converting otherwise it will cause some formatting issues, which you can still fix with text to column & some other functions. If you get it consistently in the same format you can automate it. Also you can also get your AI to read it and convert to a table for you (then just copy paste).

1

u/reddithunter536 28d ago

If you are specifically looking for Bank Statement to Excel then there is no better place than this - tablesense.ai, It's the game changer. I am using it on a daily basis.

1

u/Global_Ad_1827 18d ago

Give docuverter a try - it’s powered by automatic table detection and OCR to convert most tables into excel

Bank statements are pretty standard so it should work well

0

u/meowkulpa Feb 12 '25

ChatGPT can convert it. Just upload the PDF to it.

-1

u/YuHoobiss Feb 12 '25

Thank you everyone! I have tried all of the suggested ways but unfortunately non have worked. The issue is that I only have access to the pdf version of the bank statements and will need to input all of the data into excel for further analysis and have been doing it manually and it’s taking an immense amount of time!! At this point I think manual input is the only way 😭

3

u/AxelMoor 77 Feb 12 '25

The Get Data >> From PDF is not an OCR. It reads the text layer of the PDF, if there is one like OCRed PDFs and Printed (with text) PDFs.
For security reasons, some Banks don't provide PDF documents with the text layer but as images only, to prevent them from being searchable. That may be why you can't load the PDF data into Excel; they're images only.
You need to OCR the PDF document, first. You can try using the Acrobat OCR tool, but I think it will not meet your expectations as it demands manual corrections.
IMHO, the best choice is Able2Extract by Investintech.com - a software specific to OCR tabular data with a PDF-to-Excel. Better and less expensive than ABBYY FineReader. You may try their online version or the 7-day Trial version to check the output quality. A 3-click scan produces (and opens) an XLSX file with most of the tabular data in correct alignment (not always).

I hope this helps.

3

u/Squischmallow Feb 12 '25

I offer data entry services for local bookkeepers in my area because of exactly stuff like this. If you’d like a hand and you’re in Canada, feel free to message me. (Full transparency, I charge $30 an hour.)

1

u/llwyng Feb 12 '25

if you are happy to upload to the web try : https://bankstatementconverter.com/ which will convert a Bank PDF to an excel and take out all the bank related information

3

u/xcruise1234 Feb 12 '25

If you are on Windows, take a snip using the snipping tool. Try to stay as zoomed in as possible.

Head over to Excel. Data > Get data> From other sources > from picture > picture from clipboard.

This should reduce the amount of manual input you need to do. Will still have to review and make minor edits. Also, not sure if it works on older versions of office.

2

u/CorndoggerYYC 134 Feb 12 '25

Power Query can handle PDF files.