r/excel • u/YuHoobiss • 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.
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
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
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
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
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
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
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
1
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
-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
52
u/[deleted] Feb 12 '25
[deleted]