File size: 6,429 Bytes
b54df69 54e2f2a 0b8b44d 73327e8 0b8b44d e8ce164 0b8b44d 73327e8 f83ff36 73327e8 646e5e3 e8ce164 73327e8 e8ce164 73327e8 6e66646 73327e8 e8ce164 73327e8 54e2f2a e8ce164 43ecdc4 e8ce164 646e5e3 e8ce164 54e2f2a 635a76a 4aa29f9 e8ce164 4aa29f9 0b8b44d e8ce164 0b8b44d e8ce164 0b8b44d e8ce164 0b8b44d d6e934d 54e2f2a |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 |
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()
|