Technical Assignment: Tool for Reconciling Accounts with Bank, Cards, and Accountant
General Goal
A local tool (script/small application in Python) is needed, which is manually run once every 1-2 months on my computer and performs reconciliation between:
- Invoices I issued to clients and received from suppliers (PDF files and photos of receipts stored in Gmail)
- Bank statement (Excel)
- Credit card expenses (Excel, may have several files — multiple cards)
- Excel file from the accountant, in which he enters invoices for reporting to tax and VAT
What the Tool Should Do
1. Collect Invoices from Email (Gmail)
- Connection via Gmail API (OAuth)
- Search for emails over a specified period (e.g., the last 2 months) with attachments
- Download attachments: PDF files and images (photos of receipts/invoices)
- Recognize data from each invoice: date, amount, name of supplier/client, invoice number (if available)
- For PDF — extract text directly
- For images — text recognition (OCR), including text in Hebrew
2. Read Excel Files
- Bank statement (income and expenses)
- One or more files of credit card expenses
- Excel from the accountant with a list of entered invoices (it contains only the amount + date + name of the counterparty, there is no unique invoice number for matching)
3. Matching Logic
Match records between sources based on the combination: amount + date (with an acceptable deviation) + name of the counterparty (fuzzy text comparison, as names may differ slightly).
4. Checks to Perform
A. Are invoices issued for all income For each incoming money in the bank — check if there is a corresponding invoice that I issued. If not — mark as "income without issued invoice".
B. Are invoices saved for all recognized expenses For each expense in the bank/on cards that relates to the category of recognized expenses — check if there is a saved invoice. If not — mark as "expense without saved invoice".
C. Reconciliation with the accountant's list Compare the list of invoices I collected (from email) with the list that the accountant entered and sent to tax/VAT. Mark:
- Invoices that I collected but are not in the accountant's file (possibly not transferred or not entered)
- Records in the accountant's file for which no corresponding invoice was found in my collection
D. Payroll Check Check that the amount stated in the payroll matches the amount that was actually debited/credited in the bank.
5. Final Report
The result — one Excel file with several sheets:
- Income without issued invoice
- Expenses without saved invoice
- Invoices missing from the accountant
- Accountant's records without corresponding invoice
- Payroll reconciliation with the bank
Discrepancies/problematic lines should be visually highlighted (in color).
Technical Details
- Language: Python
- Excel: pandas + openpyxl
- PDF: text extraction (pdfplumber/PyMuPDF)
- OCR for images: with support for Hebrew (preferably use Claude API with image support instead of classic Tesseract, for better accuracy with Hebrew)
- Gmail API: google-api-python-client, OAuth (a project will need to be created in Google Cloud Console — this will be done by the client/performer together)
- Result is saved as an Excel file locally on the computer
- Interface: command line (CLI) is sufficient, no need for a web interface
Submission Format
- Source code of the project
- Brief instruction on how to run (including how to connect your Gmail and where to place the input Excel files)
- Instructions for creating credentials in Google Cloud Console, if not done in advance
Additional
I am ready to provide examples of the structure of Excel files (bank, cards, accountant's file) and examples of invoices (PDF and photos) for testing.