14.03 Lending Monthly Reports
Last updated
Last updated
The Lending Monthly Reports script connects to the PostgreSQL database, retrieves lead data for mortgage brokers, generates monthly reports, and uploads them to Digital Ocean Spaces. This process ensures mortgage brokers have timely and accurate reports of their lead data.
The script performs the following key functions:
Loads Environment Variables: Reads configuration values from a .env file.
Connects to PostgreSQL Database: Establishes a connection to retrieve lead data.
Queries Lead Data: Executes SQL queries to fetch lead metrics for construction and professional mortgage leads.
Generates CSV Reports: Creates CSV files for each broker's monthly leads.
Uploads to Digital Ocean Spaces: Uploads the generated CSV files to a specific folder in Digital Ocean Spaces.
Handles environment variables, logging, database connection, CSV processing, and Digital Ocean Spaces interaction.
Uses dotenv to load database credentials and Digital Ocean Spaces credentials from a .env file.
Connect to PostgreSQL Database
Connects using credentials from environment variables.
Fetch Loan Officers
Retrieves the list of mortgage brokers from the database.
Get Prior Month and Year
Calculates the previous month and year to determine the reporting period.
Query Lead Data
For each broker, constructs and executes SQL queries to fetch:
Construction Leads: Lead data related to construction mortgages.
Professional Leads: Lead data related to professional mortgages.
Generate CSV Reports
Writes the fetched lead data into CSV files for each broker, following a specific naming convention.
Upload to Digital Ocean Spaces
Uploads the generated CSV files to a designated folder in Digital Ocean Spaces using the boto3 library.
Error Handling
Logs errors encountered during the process and provides detailed error messages.
Construction Leads Query: Fetches lead data related to construction mortgages.
Professional Leads Query: Fetches lead data related to professional mortgages.
Running the Script: The script can be executed locally or scheduled to run periodically using a cron job.
Environment Setup: Requires a .env file with database and Digital Ocean Spaces credentials.
Uses psycopg2 for database connection.
Uses csv for CSV file handling.
Uses os for operating system operations.
Uses datetime for date calculations.
Uses boto3 for Digital Ocean Spaces interaction.
Uses dotenv for environment variable management.
Loads DO_SPACES_KEY, DO_SPACES_SECRET, DO_SPACES_ENDPOINT, and DO_SPACES_REGION for Digital Ocean Spaces.
Loads database connection parameters.
Connect to Database: Establishes connection using psycopg2.
Fetch Loan Officers: Retrieves broker data.
Determine Reporting Period: Calculates prior month and year.
Query Data and Generate Reports: Executes SQL queries and generates CSV reports.
Upload Reports: Uses boto3 to upload reports to Digital Ocean Spaces.
Error Handling: Catches and logs errors.
Construction Leads Query: Retrieves construction mortgage leads.
Professional Leads Query: Retrieves professional mortgage leads.
Creates CSV files with headers and lead data for each broker.
Uploads CSV files to a specified folder, creating a hierarchical structure based on year and month.
Can be scheduled using a cron job to run on the 1st of every month at 10 PM UTC.
The script ensures that monthly lead reports are automatically generated and securely uploaded, providing mortgage brokers with up-to-date and organized lead data.
