Introduction:
WalletWise Analytics is a smart personal expense tracking application that combines the power of Microsoft Fabric and Power BI to create an intelligent expense tracking system.
What It Does:
- Track Expenses in Real-time: Add your daily expenses directly from a Power BI dashboard and watch your balance update instantly
- Monitor Account Balance: See your current balance on a live card that updates every time you spend money
- Email Notifications: Get email alerts sent to your inbox when your balance gets dangerously low
- Monthly Salary Management: Automatically adds $8,000 monthly salary to keep your account funded
- Expense Control: View all your expenses in a table and visualize the spending
- Write-back Functionality: Enter expense data through Power BI that immediately saves to your database
Full Demo Video:
Steps Used:
Step 1: Database Setup
- Created two tables in Fabric SQL Database: Expense table and Bank table
- Added sample data to test the system
Step 2: User Data Functions
- Built smart functions that can add expenses, add salary, and send email alerts
- Connected these functions to the database to read and write data
Step 3: Power BI Dashboard
- Created a user-friendly dashboard with cards showing balance and alerts
- Added input controls where users can type expense details
- Built a table showing all expenses
Step 4: Write-back Buttons
- Added "Add Expense" button that saves data to the database when clicked
- Added "Add Salary" button that adds $8,000 monthly credit
- Added "Send Email" button for email alerts
Step 5: Smart Notifications
- Set up email notifications for critical balance levels
- Made the system update in real-time when changes happen
Why I Came Up With This:
I created this project to solve a common problem many people face: Losing track of their spending and running out of money unexpectedly. This project showcases the power of translytical flows - where analytical tools like Power BI can write data back to databases, creating a seamless experience between viewing data and updating it.
UDF:
Adding Expense
import fabric.functions as fn
from datetime import datetime
udf = fn.UserDataFunctions()
@udf.connection(argName="sqlDB", alias="YOUR_CONNECTION_ALIAS")
@udf.function()
def write_expense(sqlDB: fn.FabricSqlConnection, expenseDate: str, description: str, amount: float, category: str) -> str:
# Error handling
if amount <= 0:
raise fn.UserThrownError("Amount must be greater than 0", {"Amount": amount})
if len(description) > 255:
raise fn.UserThrownError("Description too long (max 255 characters)", {"Description": description})
# Establish connection
connection = sqlDB.connect()
cursor = connection.cursor()
try:
# Insert into Expense table
expense_query = "INSERT INTO Expense (ExpenseDate, Description, Amount, Category) VALUES (?, ?, ?, ?)"
cursor.execute(expense_query, expenseDate, description, amount, category)
# Insert corresponding debit into Bank table
bank_query = "INSERT INTO Bank (TransactionDate, Description, Amount, TransactionType) VALUES (?, ?, ?, ?)"
bank_description = f"Expense: {description}"
cursor.execute(bank_query, expenseDate, bank_description, -amount, 'Debit')
# Commit transaction
connection.commit()
return f"Expense ${amount} for '{description}' added successfully"
except Exception as e:
connection.rollback()
raise fn.UserThrownError(f"Failed to add expense: {str(e)}")
finally:
cursor.close()
connection.close()
Add $8000 Monthly Salary
@udf.connection(argName="sqlDB", alias="YOUR_CONNECTION_ALIAS")
@udf.function()
def add_salary(sqlDB: fn.FabricSqlConnection) -> str:
connection = sqlDB.connect()
cursor = connection.cursor()
try:
# Add fixed $8000 salary to Bank table with current date
current_date = datetime.now().strftime('%Y-%m-%d')
salary_query = "INSERT INTO Bank (TransactionDate, Description, Amount, TransactionType) VALUES (?, ?, ?, ?)"
cursor.execute(salary_query, current_date, 'Monthly Salary', 8000.00, 'Credit')
connection.commit()
return "Monthly salary $8000 added successfully"
except Exception as e:
connection.rollback()
raise fn.UserThrownError(f"Failed to add salary: {str(e)}")
finally:
cursor.close()
connection.close()
Send Email when balance drops below $100
Configuration Setup (Detailed Steps)
Part A: Get SendGrid API Key
Create SendGrid Account:
- Go to https://sendgrid.com/
- Click "Start for Free" or "Sign Up"
- Fill out registration form with your details
- Verify your email address
Access SendGrid Dashboard:
- Log into your SendGrid account
- You'll see the main dashboard
Create API Key:
- In the left sidebar, click "Settings"
- Click "API Keys"
- Click "Create API Key" button
- Choose "Restricted Access" (recommended)
- Give it a name like "Fabric-Expense-Tracker"
- Under "Mail Send", select "Full Access"
- Click "Create & View"
- IMPORTANT: Copy the API key immediately (it won't be shown again)
- Store it securely (you'll need this for the code)
Part B: Update Your Function Code
import fabric.functions as fn
import logging
from flask import Flask
from flask_mail import Mail, Message
from datetime import datetime
udf = fn.UserDataFunctions()
# Flask Mail Configuration
app = Flask(__name__)
app.config['MAIL_SERVER'] = 'smtp.sendgrid.net'
app.config['MAIL_PORT'] = 587
app.config['MAIL_USERNAME'] = 'apikey'
app.config['MAIL_PASSWORD'] = 'YOUR_SENDGRID_API_KEY_HERE' # Replace with your actual API key
app.config['MAIL_USE_TLS'] = True
app.config['MAIL_DEFAULT_SENDER'] = 'your-email@domain.com' # Replace with your email
mail = Mail(app)
@udf.connection(argName="sqlDB", alias="YOUR_CONNECTION_ALIAS") # Replace with your alias
@udf.function()
def check_balance_and_notify(sqlDB: fn.FabricSqlConnection, recipientemail: str) -> str:
"""Function to manually check balance and send notification if needed"""
connection = sqlDB.connect()
cursor = connection.cursor()
try:
# Get current balance
balance_query = "SELECT SUM(Amount) FROM Bank"
cursor.execute(balance_query)
current_balance = cursor.fetchone()[0] or 0
if current_balance < 100:
send_low_balance_email(current_balance, recipientemail)
return f"🚨 CRITICAL: Balance is ${current_balance:.2f}. Email notification sent to {recipientemail}"
else:
return f"✅ Balance OK: ${current_balance:.2f}. No notification needed."
except Exception as e:
raise fn.UserThrownError(f"Failed to check balance: {str(e)}")
finally:
cursor.close()
connection.close()
def send_low_balance_email(balance: float, recipient_email: str):
"""Send low balance alert email - SIMPLIFIED VERSION"""
try:
current_time = datetime.now()
# Simple text email instead of complex HTML
email_text = f"""
CRITICAL: Low Balance Alert!
Your account balance has dropped below $100!
Current Balance: ${balance:.2f}
Date: {current_time.strftime('%Y-%m-%d')}
Time: {current_time.strftime('%H:%M:%S')}
Please add funds to your account immediately to avoid any issues.
"""
with app.app_context():
msg = Message(
subject="🚨 CRITICAL: Account Balance Alert - Action Required",
recipients=[recipient_email],
body=email_text
)
mail.send(msg)
logging.info(f"Low balance email sent to {recipient_email}. Balance: ${balance:.2f}")
except Exception as e:
logging.error(f"Failed to send email: {str(e)}")
raise fn.UserThrownError(f"Failed to send notification email: {str(e)}")
Other Use Cases That Can Be Implemented:
- Student Budget Manager: Track tuition, books, food, and living expenses for students
- Travel Expense Tracker: Monitor trip expenses, daily spending limits, and travel budgets
- Investment Portfolio Monitor: Track investments, gains, losses, and portfolio alerts
- Healthcare Expense Tracker: Track medical bills, insurance claims, and health-related spending
