LLM Treasury: Snowflake Cash Flow Forecasting

import snowflake.connector
import os

# --- Configuration ---
SNOWFLAKE_USER = os.environ.get('SNOWFLAKE_USER', 'YOUR_SNOWFLAKE_USER')
SNOWFLAKE_PASSWORD = os.environ.get('SNOWFLAKE_PASSWORD', 'YOUR_SNOWFLAKE_PASSWORD')
SNOWFLAKE_ACCOUNT = os.environ.get('SNOWFLAKE_ACCOUNT', 'YOUR_SNOWFLAKE_ACCOUNT')
SNOWFLAKE_DATABASE = os.environ.get('SNOWFLAKE_DATABASE', 'YOUR_DATABASE')
SNOWFLAKE_SCHEMA = os.environ.get('SNOWFLAKE_SCHEMA', 'YOUR_SCHEMA')

OPENAI_API_KEY = os.environ.get('OPENAI_API_KEY', 'YOUR_OPENAI_API_KEY')

# --- Snowflake Connection ---
def get_snowflake_connection():
    try:
        conn = snowflake.connector.connect(
            user=SNOWFLAKE_USER,
            password=SNOWFLAKE_PASSWORD,
            account=SNOWFLAKE_ACCOUNT,
            database=SNOWFLAKE_DATABASE,
            schema=SNOWFLAKE_SCHEMA
        )
        return conn
    except Exception as e:
        print(f"Error connecting to Snowflake: {e}")
        return None

# --- Data Query ---
def query_recent_cash_flow_data(conn, days=90):
    try:
        cursor = conn.cursor()
        query = f'''
        SELECT
            DATE_TRUNC('day', transaction_date) as transaction_day,
            SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) as total_inflows,
            SUM(CASE WHEN amount < 0 THEN amount ELSE 0 END) as total_outflows
        FROM
            your_transactions_table  -- REPLACE WITH YOUR ACTUAL TABLE NAME
        WHERE
            transaction_date >= DATEADD(day, -{days}, CURRENT_DATE())
        GROUP BY
            transaction_day
        ORDER BY
            transaction_day;
        '''
        cursor.execute(query)
        results = cursor.fetchall()
        return results
    except Exception as e:
        print(f"Error querying Snowflake: {e}")
        return []

# --- LLM Prompt Generation ---
def generate_llm_prompt(cash_flow_data):
    if not cash_flow_data:
        return "Unable to generate forecast: No data provided."

    # Format data for LLM context
    formatted_data = "Date | Inflows | Outflows\n"
    for row in cash_flow_data:
        formatted_data += f"{row[0].strftime('%Y-%m-%d')} | {row[1]:.2f} | {row[2]:.2f}\n"

    prompt = f"""
    You are an expert financial forecaster for commercial real estate. Based on the following recent cash flow data, predict the net cash flow for the next 30 days. Provide the prediction as a single numerical value (positive for net inflow, negative for net outflow) and a brief (1-2 sentence) justification.

    Recent Cash Flow Data:
    {formatted_data}

    Predict the net cash flow for the next 30 days:
    """
    return prompt

# --- Main Execution ---
if __name__ == "__main__":
    snowflake_conn = get_snowflake_connection()

    if snowflake_conn:
        print("Successfully connected to Snowflake.")
        recent_data = query_recent_cash_flow_data(snowflake_conn)
        
        if recent_data:
            print(f"Retrieved {len(recent_data)} days of cash flow data.")
            llm_prompt = generate_llm_prompt(recent_data)
            print("\n--- LLM Prompt ---")
            print(llm_prompt)
            print("\n------------------")
            
            # --- Placeholder for actual LLM API call ---
            # To make an actual call, you would use the OpenAI Python library:
            # import openai
            # openai.api_key = OPENAI_API_KEY
            # response = openai.ChatCompletion.create(
            #     model="gpt-4", # or "gpt-3.5-turbo"
            #     messages=[
            #         {"role": "system", "content": "You are a financial forecasting assistant."}, # System message can be refined
            #         {"role": "user", "content": llm_prompt}
            #     ]
            # )
            # predicted_cash_flow = response.choices[0].message.content
            # print(f"LLM Prediction: {predicted_cash_flow}")
            # --- End Placeholder ---
            
            print("\nNOTE: This script generates the prompt. Actual LLM API call requires 'openai' library and API key configuration.")
            
        else:
            print("Could not retrieve cash flow data from Snowflake.")
        
        snowflake_conn.close()
    else:
        print("Failed to establish Snowflake connection. Please check credentials and network access.")