portfolio_mgt_pilot.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python Resource Management Project:\n",
    "\n",
    "WORK IN PROGRESS:  This script will (eventually) allow you to automate your financial tracking via email scraping of financial institutions. Data is gathered into a data table and uploaded to your Google Drive as a Sheets file. Visual data to be included down the road (along with the remainder of work). For now, feel free to take what is useful to yourselves.\n",
    "\n",
    "<br>\n",
    "\n",
    "[Overview (link removed)](https://console.cloud.google.com/)\n",
    "\n",
    "GCP Service account:  ```<redacted>```\n",
    "\n",
    "\n",
    "### OAuth Scopes:\n",
    "\n",
    "* Gmail:\n",
    "    * ```https://www.googleapis.com/auth/gmail.modify```\n",
    "    * ```https://www.googleapis.com/auth/gmail.labels```\n",
    "\n",
    "* Sheets:\n",
    "    * ```https://www.googleapis.com/auth/spreadsheets```\n",
    "\n",
    "* Drive:\n",
    "    * ```https://www.googleapis.com/auth/drive.file```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "bat"
    }
   },
   "outputs": [],
   "source": [
    "# requirements.txt\n",
    "\n",
    "google-auth-oauthlib==1.0.0\n",
    "google-auth-httplib2==0.1.0\n",
    "google-api-python-client==2.86.0\n",
    "pandas==2.0.0\n",
    "numpy==1.24.0\n",
    "matplotlib==3.7.0\n",
    "seaborn==0.12.0\n",
    "schedule==1.2.0\n",
    "pytz==2023.3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from google.oauth2 import service_account\n",
    "from googleapiclient.discovery import build\n",
    "from datetime import datetime, timedelta\n",
    "import base64\n",
    "import email\n",
    "import pytz\n",
    "import schedule\n",
    "import time\n",
    "from email.mime.text import MIMEText\n",
    "from email.mime.multipart import MIMEMultipart\n",
    "from email.mime.image import MIMEImage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from google_auth_oauthlib.flow import InstalledAppFlow\n",
    "from google.auth.transport.requests import Request\n",
    "from google.oauth2.credentials import Credentials\n",
    "from googleapiclient.discovery import build\n",
    "from datetime import datetime, timedelta\n",
    "import base64\n",
    "import pytz\n",
    "import schedule\n",
    "import time\n",
    "import pickle\n",
    "\n",
    "SCOPES = [\n",
    "    'https://www.googleapis.com/auth/gmail.modify',\n",
    "    'https://www.googleapis.com/auth/gmail.labels',\n",
    "    'https://www.googleapis.com/auth/spreadsheets',\n",
    "    'https://www.googleapis.com/auth/drive.file'\n",
    "]\n",
    "\n",
    "class FinancialEmailProcessor:\n",
    "    def __init__(self, credentials):\n",
    "        self.gmail_service = build('gmail', 'v1', credentials=credentials)\n",
    "        self.sheets_service = build('sheets', 'v4', credentials=credentials)\n",
    "        self.processed_ids_file = 'processed_email_ids.txt'\n",
    "        self.user_id = 'me'\n",
    "        self.label_id = self.get_or_create_label('Portfolio')\n",
    "\n",
    "    def get_or_create_label(self, label_name):\n",
    "        \"\"\"Get or create Gmail label\"\"\"\n",
    "        try:\n",
    "            results = self.gmail_service.users().labels().list(userId=self.user_id).execute()\n",
    "            labels = results.get('labels', [])\n",
    "            \n",
    "            # Check if label exists\n",
    "            for label in labels:\n",
    "                if label['name'] == label_name:\n",
    "                    return label['id']\n",
    "            \n",
    "            # Create new label if needed\n",
    "            label_object = {\n",
    "                'name': label_name,\n",
    "                'labelListVisibility': 'labelShow',\n",
    "                'messageListVisibility': 'show'\n",
    "            }\n",
    "            created_label = self.gmail_service.users().labels().create(\n",
    "                userId=self.user_id,\n",
    "                body=label_object\n",
    "            ).execute()\n",
    "            return created_label['id']\n",
    "            \n",
    "        except Exception as e:\n",
    "            print(f\"Error with label: {e}\")\n",
    "            return None\n",
    "\n",
    "    def get_financial_emails(self):\n",
    "        \"\"\"Get financial notification emails\"\"\"\n",
    "        processed_ids = self.load_processed_ids()\n",
    "        # Customize senders list as needed\n",
    "        senders = ['etrade.com', 'chase.com', 'schwab.com']  \n",
    "        query = f\"from:({' OR '.join(senders)})\"\n",
    "        \n",
    "        results = self.gmail_service.users().messages().list(\n",
    "            userId=self.user_id, q=query).execute()\n",
    "        messages = results.get('messages', [])\n",
    "        \n",
    "        new_transactions = []\n",
    "        \n",
    "        for message in messages:\n",
    "            if message['id'] not in processed_ids:\n",
    "                msg = self.gmail_service.users().messages().get(\n",
    "                    userId=self.user_id, id=message['id'], format='full').execute()\n",
    "                \n",
    "                # Add Portfolio label\n",
    "                if self.label_id:\n",
    "                    self.gmail_service.users().messages().modify(\n",
    "                        userId=self.user_id,\n",
    "                        id=message['id'],\n",
    "                        body={'addLabelIds': [self.label_id]}\n",
    "                    ).execute()\n",
    "                \n",
    "                # Extract transaction details\n",
    "                transaction = self.parse_email(msg)\n",
    "                if transaction:\n",
    "                    new_transactions.append(transaction)\n",
    "                \n",
    "                self.save_processed_id(message['id'])\n",
    "        \n",
    "        return pd.DataFrame(new_transactions) if new_transactions else pd.DataFrame()\n",
    "\n",
    "def get_credentials():\n",
    "    \"\"\"Get and refresh OAuth credentials\"\"\"\n",
    "    creds = None\n",
    "    \n",
    "    # Load saved credentials if they exist\n",
    "    if os.path.exists('token.pickle'):\n",
    "        with open('token.pickle', 'rb') as token:\n",
    "            creds = pickle.load(token)\n",
    "    \n",
    "    # Refresh if expired\n",
    "    if creds and creds.expired and creds.refresh_token:\n",
    "        creds.refresh(Request())\n",
    "    # Otherwise, let user log in\n",
    "    else:\n",
    "        flow = InstalledAppFlow.from_client_secrets_file(\n",
    "            'client_secret.json',  # OAuth client secrets file\n",
    "            SCOPES,\n",
    "            redirect_uri='http://localhost:8080'  # modify this for WSL hostname...\n",
    "        )\n",
    "        creds = flow.run_local_server(port=8080)\n",
    "        \n",
    "        # Save credentials for future use\n",
    "        with open('token.pickle', 'wb') as token:\n",
    "            pickle.dump(creds, token)\n",
    "    \n",
    "    return creds\n",
    "\n",
    "def main():\n",
    "    credentials = get_credentials()\n",
    "    processor = FinancialEmailProcessor(credentials)\n",
    "    SPREADSHEET_ID = 'your_spreadsheet_id'\n",
    "    \n",
    "    # to be continued...\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    main()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "nassau",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}