|
|
import streamlit as st |
|
|
import pandas as pd |
|
|
from datetime import datetime |
|
|
import requests |
|
|
from transformers import AutoTokenizer, AutoModelForTableQuestionAnswering |
|
|
|
|
|
class InterestCalculatorApp: |
|
|
def __init__(self): |
|
|
self.invoices_df = None |
|
|
self.base_rates_df = None |
|
|
self.late_payment_interest = st.sidebar.slider("Late Payment Interest Rate (%)", min_value=0.0, max_value=10.0, value=4.0) |
|
|
self.compounding_method = st.sidebar.selectbox("Compounding Method", ["x% above Base Rate (daily)", |
|
|
"x% above Base Rate (annually)", |
|
|
"Quarterly compounding (25 Mar, 24 Jun, 29 Sep, 25 Dec)", |
|
|
"Quarterly compounding (1 Mar, 1 Jun, 1 Sep, 1 Dec)"]) |
|
|
|
|
|
|
|
|
self.tokenizer = AutoTokenizer.from_pretrained("google/tapas-large-finetuned-wtq") |
|
|
self.model = AutoModelForTableQuestionAnswering.from_pretrained("google/tapas-large-finetuned-wtq") |
|
|
|
|
|
def load_invoices(self, file_path): |
|
|
try: |
|
|
self.invoices_df = pd.read_excel(file_path, parse_dates=['Due Date']) |
|
|
st.success("Invoices loaded successfully.") |
|
|
except Exception as e: |
|
|
st.error(f"Failed to load invoices: {e}") |
|
|
|
|
|
def calculate_interest(self): |
|
|
if self.invoices_df is not None and self.base_rates_df is not None: |
|
|
st.write("Calculating interest...") |
|
|
today = datetime.today() |
|
|
interests = [] |
|
|
for index, invoice in self.invoices_df.iterrows(): |
|
|
due_date = invoice['Due Date'] |
|
|
amount = invoice['Amount'] |
|
|
base_rate = self.get_base_rate(due_date) |
|
|
effective_rate = base_rate + self.late_payment_interest |
|
|
if due_date > today: |
|
|
interests.append(0) |
|
|
continue |
|
|
interest = self.calculate_compound_interest(due_date, amount, effective_rate, self.compounding_method, today) |
|
|
interests.append(interest) |
|
|
total_amount_owed = amount + interest |
|
|
self.invoices_df.loc[index, 'Interest'] = interest |
|
|
self.invoices_df.loc[index, 'Total Amount Owed'] = total_amount_owed |
|
|
total_interest = sum(interests) |
|
|
st.success(f"Total Interest Calculated: £{total_interest:.2f}") |
|
|
st.write(self.invoices_df) |
|
|
else: |
|
|
st.error("Please load both invoices and base rates files.") |
|
|
|
|
|
def get_base_rate(self, due_date): |
|
|
self.base_rates_df['Date Changed'] = pd.to_datetime(self.base_rates_df['Date Changed']) |
|
|
rate_rows = self.base_rates_df[self.base_rates_df['Date Changed'] <= due_date].sort_values(by='Date Changed', ascending=False) |
|
|
return rate_rows.iloc[0]['Rate'] if not rate_rows.empty else 0 |
|
|
|
|
|
def calculate_compound_interest(self, due_date, amount, effective_rate, method, today): |
|
|
days = (today - due_date).days |
|
|
if 'daily' in method: |
|
|
daily_rate = (effective_rate / 100) / 365 |
|
|
return amount * daily_rate * days |
|
|
elif 'annually' in method: |
|
|
annual_rate = effective_rate / 100 |
|
|
return amount * annual_rate * (days / 365) |
|
|
elif 'Quarterly compounding' in method: |
|
|
return self.calculate_quarterly_interest(due_date, amount, effective_rate, method, today) |
|
|
|
|
|
def calculate_quarterly_interest(self, due_date, amount, effective_rate, method, today): |
|
|
quarterly_dates = { |
|
|
"Quarterly compounding (25 Mar, 24 Jun, 29 Sep, 25 Dec)": [(3, 25), (6, 24), (9, 29), (12, 25)], |
|
|
"Quarterly compounding (1 Mar, 1 Jun, 1 Sep, 1 Dec)": [(3, 1), (6, 1), (9, 1), (12, 1)] |
|
|
}[method] |
|
|
interest = 0 |
|
|
compounded_amount = amount |
|
|
for month, day in quarterly_dates: |
|
|
compounding_date = datetime(today.year, month, day) |
|
|
if compounding_date > today: |
|
|
break |
|
|
if compounding_date > due_date: |
|
|
days_since_last_compounding = (today - compounding_date).days |
|
|
period_rate = effective_rate / 4 |
|
|
compounded_interest = compounded_amount * ((1 + period_rate) ** (days_since_last_compounding / 365.25) - 1) |
|
|
compounded_amount += compounded_interest |
|
|
interest += compounded_interest |
|
|
due_date = compounding_date |
|
|
return interest |
|
|
|
|
|
def download_boe_rates(self): |
|
|
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) |
|
|
self.base_rates_df = df |
|
|
self.base_rates_df['Date Changed'] = pd.to_datetime(self.base_rates_df['Date Changed'], format='%d %b %y') |
|
|
st.success("Bank of England rates downloaded successfully.") |
|
|
else: |
|
|
st.error("Failed to retrieve data from the Bank of England website.") |
|
|
except requests.RequestException as e: |
|
|
st.error(f"Failed to download rates: {e}") |
|
|
|
|
|
def ask_tapas(self, query, table): |
|
|
inputs = self.tokenizer(table, query, return_tensors="pt", padding=True) |
|
|
outputs = self.model(**inputs) |
|
|
predicted_answer = self.tokenizer.decode(outputs.logits.argmax(dim=-1)) |
|
|
return predicted_answer |
|
|
|
|
|
def main(): |
|
|
st.title("Interest Calculation App") |
|
|
|
|
|
app = InterestCalculatorApp() |
|
|
|
|
|
file_path = st.file_uploader("Upload Invoices File", type=["xlsx"]) |
|
|
|
|
|
if file_path is not None: |
|
|
app.load_invoices(file_path) |
|
|
|
|
|
if st.button("Calculate Interest"): |
|
|
app.calculate_interest() |
|
|
|
|
|
app.download_boe_rates() |
|
|
|
|
|
if __name__ == "__main__": |
|
|
main() |
|
|
|
