import streamlit as st import pandas as pd import openai import requests # Streamlit App def main(): st.title("Invoice Interest Calculator and Conversation") # Prompt user for OpenAI API key api_key = st.text_input("Enter your OpenAI API key:") if api_key: # Download BOE rates boe_rates_df = download_boe_rates() # Allow user to upload Excel sheet uploaded_file = st.file_uploader("Upload Excel file", type=["xlsx", "xls"]) if uploaded_file is not None: df = pd.read_excel(uploaded_file) # Display uploaded data st.write("Uploaded Data:") st.write(df) # Analyze Excel sheet due_dates, payment_dates, amounts = analyze_excel(df) # Allow user to specify late interest rate late_interest_rate = st.number_input("Enter Late Interest Rate (%):", min_value=0.0, max_value=100.0, step=0.1) # Calculate late interest if due dates and payment dates are available if due_dates and payment_dates: # Create DataFrame with extracted due dates, payment dates, and placeholder amount df_calculate = pd.DataFrame({ 'due_date': due_dates, 'payment_date': payment_dates, 'amount': amounts }) # Calculate late interest df_with_interest = calculate_late_interest(df_calculate, late_interest_rate, boe_rates_df) # Display calculated late interest total_late_interest = df_with_interest['late_interest'].sum() st.write("Calculated Late Interest:") st.write(total_late_interest) # Generate conversation prompt prompt = generate_conversation_prompt(df, boe_rates_df) # Allow user to engage in conversation user_input = st.text_input("Start a conversation:") if st.button("Send"): openai.api_key = api_key # Set user-provided OpenAI API key completion = openai.ChatCompletion.create( model="gpt-4-turbo", messages=[ {"role": "system", "content": prompt}, {"role": "user", "content": user_input} ], max_tokens=1800 # Adjust this value to allow longer responses ) response = completion.choices[0].message['content'] st.write("AI's Response:") st.write(response) else: st.warning("Please enter your OpenAI API key.") # Function to generate conversation prompt def generate_conversation_prompt(df, boe_rates_df): prompt = "I have analyzed the provided Excel sheet. " # Include due dates, payment dates, and amounts from the Excel sheet due_dates = df['due_date'].tolist() payment_dates = df['payment_date'].tolist() amounts = df['amount'].tolist() prompt += f"The due dates in the sheet are: {', '.join(str(date) for date in due_dates)}. " prompt += f"The payment dates in the sheet are: {', '.join(str(date) for date in payment_dates)}. " prompt += f"The amounts in the sheet are: {', '.join(str(amount) for amount in amounts)}. " # Include Bank of England base rates if boe_rates_df is not None: prompt += "The Bank of England base rates are as follows: \n" for index, row in boe_rates_df.iterrows(): prompt += f"On {row['Date Changed']}, the base rate was {row['Current Bank Rate']}. \n" prompt += "Based on this information, what would you like to discuss?" return prompt # Function to calculate late interest def calculate_late_interest(data, late_interest_rate, boe_rates_df): # Convert due_date column to Timestamp objects data['due_date'] = pd.to_datetime(data['due_date']) data['payment_date'] = pd.to_datetime(data['payment_date']) # Calculate late days and late interest data['late_days'] = (data['payment_date'] - data['due_date']).dt.days.clip(lower=0) data['late_interest'] = data['late_days'] * data['amount'] * (late_interest_rate / 100) # Consider additional factors like Bank of England base rate if boe_rates_df is not None: data['boe_base_rate'] = data['due_date'].map(lambda x: get_boe_base_rate(x, boe_rates_df)) data['late_interest'] += data['amount'] * (data['boe_base_rate'] / 100) return data # Function to analyze Excel sheet and extract relevant information def analyze_excel(df): # Extract due dates and payment dates due_dates = pd.to_datetime(df.iloc[:, 0], errors='coerce').dropna() # Convert to datetime and drop NaT values payment_dates = pd.to_datetime(df.iloc[:, 1], errors='coerce').dropna() # Convert to datetime and drop NaT values amounts = [] # Extract and clean amounts from third column for amount in df.iloc[:, 2]: if isinstance(amount, str): amount = amount.replace('"', '').replace(',', '') amounts.append(float(amount)) return due_dates, payment_dates, amounts # Function to download Bank of England rates def download_boe_rates(): try: headers = { 'accept-language': 'en-US,en;q=0.9', 'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/109.0.0.0 Safari/537.36' } url = 'https://www.bankofengland.co.uk/boeapps/database/Bank-Rate.asp' response = requests.get(url, headers=headers) if response.status_code == 200: df = pd.read_html(response.text)[0] df.to_csv('boe_rates.csv', index=False) st.success("Bank of England rates downloaded successfully.") return df # Return the downloaded data else: st.error("Failed to retrieve data from the Bank of England website.") return None except requests.RequestException as e: st.error(f"Failed to download rates: {e}") return None def get_boe_base_rate(date, boe_rates_df): closest_date_index = (boe_rates_df['Date Changed'] - pd.Timestamp(date)).abs().argsort()[0] closest_date = boe_rates_df['Date Changed'].iloc[closest_date_index] return boe_rates_df.loc[closest_date_index, 'Current Bank Rate'] if __name__ == "__main__": main()