This script parses Canadian Tire Financial Services (ctfs.com) PDF statement files and extracts the relevant transaction information which is then categorized and appended to an Excel file. This information can then be used to create pivot tables and charts to track and understand trends in your personal spending habits. It uses pdfplumber for PDF extraction, pandas for data manipulation, and logging for debug information.

• Python 3.x
• pdfplumber
• pandas
• openpyxl
• xlsxwriter

First, ensure you have Python 3.x installed. Then, install the required libraries using pip:

pip install pdfplumber pandas openpyxl xlsxwriter

1. Place your PDF files: Place all the PDF files you want to process in the specified folder. PDFs will be processed one-by-one and transactions will be appended to the end of the Excel file.
2. Run the script: Execute the script to process the PDFs and save the transactions to an Excel file.

1. Extract Transactions: Extract text from PDF files using pdfplumber.
2. Parse Transactions: Identify and parse transaction lines from the extracted text.
3. Clean Transactions: Format and categorize transaction data.
4. Save to Excel: Save the cleaned transactions to an Excel file.

• Folder Path: Update the folder_path variable with the path to your PDFs folder.
• Excel Path: Update the excel_path variable with the path where you want to save the Excel file.

Run the script using the following command:

python ImportTransactions.py

The script logs debug and warning information to help track the processing steps and identify any issues.

Extracts text from each page of the PDF file.

Extracts the year from the text.

Identifies if a line contains a transaction using regular expressions.

Parses a line to extract transaction details such as date, description, and amount.

Splits the text into lines and processes each line to identify and extract transactions.

Categorizes the transaction based on keywords in the description. The script uses a category_mapping.py file to map keywords to specific categories.

Formats and cleans the transaction data.

Formats dates into a standard format.

Saves the cleaned transactions to an Excel file with correct formatting.

Processes all PDF files in the specified folder and saves the transactions to an Excel file.

Main function to set folder and Excel paths and initiate processing.

def main():
    folder_path = '/path/to/your/pdfs/'
    excel_path = '/path/to/save/transactions.xlsx'
    
    process_pdfs(folder_path, excel_path)

if __name__ == "__main__":
    main()

Update the folder_path and excel_path with your specific paths and run the script. The transactions from the PDFs will be extracted and saved to the Excel file.

This script uses a separate category_mapping.py file to categorize transactions. The category_mapping.py file should define a dictionary where the keys are keywords found in transaction descriptions, and the values are tuples containing the sub-category and category.

Example of category_mapping.py:

category_mapping = {
    "GROCERY STORE": ("Groceries", "Food"),
    "GAS STATION": ("Gasoline", "Transport"),
    "RESTAURANT": ("Dining Out", "Food"),
    # Add more mappings as needed
}

This project is licensed under the MIT License - see the LICENSE file for details.

This script was generated with the assistance of ChatGPT, a language model created by OpenAI.

Feel free to modify the script according to your requirements. For any issues or improvements, please open an issue or submit a pull request.

Happy Tracking!