import streamlit as st import pandas as pd import plotly.express as px import plotly.graph_objects as go # Sample Data Format st.subheader('Sample Data Format:') st.write(""" The data should be in the following format with the listed columns: | Customer Name | Date | City | Country | State | Product Name | Product Attribute 1 | Product Attribute 2 | Product Attribute 3 | Product Attribute 4 | Net Sales Value | Margin Amount | Cost | |----------------|------------|--------------|-------------|-------------|-----------------|---------------------|---------------------|---------------------|---------------------|------------------|---------------|-------| | John Doe | 2024-01-01 | New York | USA | NY | Product A | Attribute 1A | Attribute 2A | Attribute 3A | Attribute 4A | 1000 | 300 | 700 | | Jane Smith | 2024-01-02 | Los Angeles | USA | CA | Product B | Attribute 1B | Attribute 2B | Attribute 3B | Attribute 4B | 1500 | 400 | 1100 | | Bob Johnson | 2024-02-15 | Chicago | USA | IL | Product A | Attribute 1A | Attribute 2A | Attribute 3A | Attribute 4A | 1200 | 350 | 850 | | Alice Williams | 2024-03-10 | Miami | USA | FL | Product C | Attribute 1C | Attribute 2C | Attribute 3C | Attribute 4C | 2000 | 500 | 1500 | | Charlie Brown | 2024-04-05 | Houston | USA | TX | Product B | Attribute 1B | Attribute 2B | Attribute 3B | Attribute 4B | 1800 | 450 | 1350 | """) # File upload functionality st.sidebar.header("Upload Data") uploaded_file = st.sidebar.file_uploader("Choose a CSV file", type=["csv"]) # Check if file is uploaded if uploaded_file is not None: # Load the data df = pd.read_csv(uploaded_file) # Convert Date to datetime format if it's in string format if 'Date' in df.columns: df['Date'] = pd.to_datetime(df['Date']) # Add 'Year' and 'Month' columns for easy filtering and analysis df['Year'] = df['Date'].dt.year df['Month'] = df['Date'].dt.month # Sidebar Filters st.sidebar.header("Filter Options") # Text Input for Customer and Product customer_query = st.sidebar.text_input('Enter Customer Name (partial or full):').strip().lower() product_query = st.sidebar.text_input('Enter Product Name (partial or full):').strip().lower() city_query = st.sidebar.text_input('Enter City (partial or full):').strip().lower() state_query = st.sidebar.text_input('Enter State (partial or full):').strip().lower() country_query = st.sidebar.text_input('Enter Country (partial or full):').strip().lower() # Date Range Selection start_date = st.sidebar.date_input('Start Date:', df['Date'].min()) end_date = st.sidebar.date_input('End Date:', df['Date'].max()) # Filter Data by Date Range filtered_df = df[ (df['Date'] >= pd.to_datetime(start_date)) & (df['Date'] <= pd.to_datetime(end_date)) ] # Filter Data by Customer Name if customer_query: filtered_df = filtered_df[filtered_df['Customer Name'].str.contains(customer_query, case=False, na=False)] # Filter Data by Product Name if product_query: filtered_df = filtered_df[filtered_df['Product Name'].str.contains(product_query, case=False, na=False)] # Filter Data by City if city_query: filtered_df = filtered_df[filtered_df['City'].str.contains(city_query, case=False, na=False)] # Filter Data by State if state_query: filtered_df = filtered_df[filtered_df['State'].str.contains(state_query, case=False, na=False)] # Filter Data by Country if country_query: filtered_df = filtered_df[filtered_df['Country'].str.contains(country_query, case=False, na=False)] # Add 'Net Sales Value - Cost' as a new column filtered_df['Net Sales Value - Cost'] = filtered_df['Net Sales Value'] - filtered_df['Cost'] # Display Filtered Data st.write(f"Filtered Data: {len(filtered_df)} records found.") st.dataframe(filtered_df) if not filtered_df.empty: # KPI Metrics st.subheader("Key Financial Metrics") # Profit for the Year (Calculated as Net Sales Value - Cost) profit_for_the_year = filtered_df['Net Sales Value'] - filtered_df['Cost'] st.metric("Profit for the Year", f"${profit_for_the_year.sum():,.2f}") # Gross Margin (Net Sales Value - Cost) gross_margin = filtered_df['Net Sales Value'] - filtered_df['Cost'] st.metric("Gross Margin", f"${gross_margin.sum():,.2f}") # Total Sales total_sales = filtered_df['Net Sales Value'].sum() st.metric("Total Sales", f"${total_sales:,.2f}") # Visualization 1: Profit and Loss Overview (Table) st.subheader("Profit and Loss Overview") pnl_data = filtered_df[['Customer Name', 'Product Name', 'Net Sales Value', 'Cost', 'Net Sales Value - Cost']] pnl_data.columns = ['Customer', 'Product', 'Sales', 'Cost', 'Profit'] st.dataframe(pnl_data) # Visualization 2: Matrix View (like Power BI Matrix) st.subheader("Matrix View of Financial Data") matrix_data = filtered_df.pivot_table( values='Net Sales Value', index=['Year', 'Customer Name'], columns=['Product Name'], aggfunc='sum', fill_value=0 ) st.dataframe(matrix_data) # Visualization 3: Sales Trend over Time st.subheader("Sales Trend over Time") sales_trend = filtered_df.groupby(['Year', 'Month'])['Net Sales Value'].sum().reset_index() fig = px.line(sales_trend, x='Month', y='Net Sales Value', color='Year', title="Sales Trend over Time") st.plotly_chart(fig) # Visualization 4: Profit Margin Visualization (Bar Chart) st.subheader("Profit Margin per Product") profit_margin_data = filtered_df.groupby('Product Name').apply( lambda x: (x['Net Sales Value'] - x['Cost']).sum() / x['Net Sales Value'].sum() ).reset_index(name="Profit Margin") fig = px.bar(profit_margin_data, x='Product Name', y='Profit Margin', title="Profit Margin per Product") st.plotly_chart(fig) else: st.write("No data available for the selected filters.") else: st.write("Please upload a CSV file to get started.")