Build an Automated Portfolio Dashboard with DuckDB: 30 Lines of Code for Daily P&L Reports

pip install duckdb yfinance pandas matplotlib

pip install duckdb yfinance pandas matplotlib -i https://pypi.tuna.tsinghua.edu.cn/simple

import duckdb
import yfinance as yf
import pandas as pd
from datetime import datetime, timedelta

# ── Portfolio definition (your actual holdings) ──────────
portfolio = pd.DataFrame({
 'ticker': ['AAPL', 'MSFT', 'GOOGL', 'NVDA', 'TSLA'],
 'shares': [100, 50, 20, 30, 40],
 'cost_basis': [145.50, 320.00, 138.20, 850.00, 245.00], # Average buy price
})

# ── Fetch real-time quotes ───────────────────────────────
tickers = portfolio['ticker'].tolist()
today = datetime.now().strftime('%Y-%m-%d')
current_prices = {}

for t in tickers:
 try:
 stock = yf.Ticker(t)
 hist = stock.history(period='1d')
 if not hist.empty:
 current_prices[t] = hist['Close'].iloc[-1]
 except Exception as e:
 print(f"⚠ {t} fetch failed: {e}")
 current_prices[t] = None

print(f"✅ Fetched {len([p for p in current_prices.values() if p])}/{len(tickers)} stock quotes")

# ── Write to DuckDB in-memory database (zero file writes) ──
con = duckdb.connect(':memory:')

# Portfolio table
con.execute("CREATE TABLE portfolio AS SELECT * FROM portfolio")

# Price table
price_rows = [
 (t, p, today) for t, p in current_prices.items() if p
]
con.execute("CREATE TABLE prices AS SELECT * FROM (VALUES ?)", [price_rows])

# Verify data
print(con.execute("SELECT * FROM portfolio").fetchdf())
print(con.execute("SELECT * FROM prices").fetchdf())

# ── Core query: one SQL query does all calculations ─────
profit_query = """
SELECT
 p.ticker,
 p.shares,
 p.cost_basis,
 pr.close AS current_price,
 (pr.close - p.cost_basis) * p.shares AS profit_loss,
 ROUND(((pr.close - p.cost_basis) / p.cost_basis) * 100, 2) AS pct_change,
 pr.close * p.shares AS market_value
FROM portfolio p
JOIN prices pr ON p.ticker = pr.ticker
ORDER BY profit_loss DESC
"""

result = con.execute(profit_query).fetchdf()

print("=" * 50)
print(f"📊 Portfolio Daily Report | {today}")
print("=" * 50)
for _, row in result.iterrows():
 emoji = "📈" if row['profit_loss'] > 0 else "📉"
 sign = "+" if row['profit_loss'] > 0 else ""
 print(f"{emoji} {row['ticker']:6s} | "
 f"P&L: {sign}{row['profit_loss']:,.2f} | "
 f"Change: {sign}{row['pct_change']}% | "
 f"Value: ${row['market_value']:,.2f}")

total_pl = result['profit_loss'].sum()
total_mv = result['market_value'].sum()
print("-" * 50)
print(f"💰 Total P&L: {total_pl:+,.2f} | Total Value: ${total_mv:,.2f}")
print("=" * 50)

con.close()

import matplotlib.pyplot as plt

fig, axes = plt.subplots(1, 2, figsize=(12, 5))

# Left: P&L bar chart
colors = ['#2ecc71' if x > 0 else '#e74c3c' for x in result['profit_loss']]
axes[0].bar(result['ticker'], result['profit_loss'], color=colors)
axes[0].set_title('Daily P&L by Stock', fontsize=12)
axes[0].axhline(y=0, color='black', linewidth=0.5)
axes[0].tick_params(axis='x', rotation=45)

# Right: Portfolio allocation pie chart
axes[1].pie(result['market_value'], labels=result['ticker'], autopct='%1.1f%%')
axes[1].set_title('Portfolio Allocation', fontsize=12)

plt.tight_layout()
plt.savefig('daily_report.png', dpi=150, bbox_inches='tight')
plt.close()
print("✅ Chart saved: daily_report.png")

import schedule
import time
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.image import MIMEImage

def send_report():
 """Send daily report via email"""
 smtp_server = "smtp.gmail.com"
 smtp_port = 587
 sender = "your_email@gmail.com"
 password = "your_app_password"
 receivers = ["your_email@gmail.com"]
 
 msg = MIMEMultipart()
 msg['From'] = sender
 msg['To'] = ", ".join(receivers)
 msg['Subject'] = f"📊 Portfolio Daily Report {today}"
 
 body = f"Portfolio Daily Report {today}\n{'='*40}\n"
 for _, row in result.iterrows():
 sign = "+" if row['profit_loss'] > 0 else ""
 body += f"{row['ticker']}: {sign}{row['profit_loss']:,.2f} ({sign}{row['pct_change']}%)\n"
 body += f"\nTotal P&L: {total_pl:+,.2f}\nTotal Value: ${total_mv:,.2f}"
 
 msg.attach(MIMEText(body, 'plain', 'utf-8'))
 
 # Attach chart
 with open('daily_report.png', 'rb') as f:
 img = MIMEImage(f.read())
 img.add_header('Content-Disposition', 'attachment', filename='daily_report.png')
 msg.attach(img)
 
 server = smtplib.SMTP(smtp_server, smtp_port)
 server.starttls()
 server.login(sender, password)
 server.sendmail(sender, receivers, msg.as_string())
 server.quit()
 
 print(f"✅ Report sent to {len(receivers)} recipient(s)")

# ── Scheduled task: run daily at 22:00 ─────────────────
schedule.every().day.at("22:00").do(send_report)

print("🤖 Auto-delivery service started, waiting for 22:00...")
while True:
 schedule.run_pending()
 time.sleep(60)

# Edit crontab
crontab -e

# Run automatically at 22:00 every day
0 22 * * * cd /path/to/duckdb-portfolio && python3 reporter.py >> /var/log/portfolio.log 2>&1

# 1. Support multiple portfolio configurations
portfolios = {
 'Growth': {'AAPL': 100, 'NVDA': 30, 'TSLA': 40},
 'Conservative': {'MSFT': 50, 'GOOGL': 20, 'JNJ': 60},
}

# 2. Support price change alerts
ALERT_THRESHOLD = 5.0 # Alert when change exceeds 5%
if abs(row['pct_change']) > ALERT_THRESHOLD:
 send_alert(f"⚠️ {row['ticker']} change exceeded {ALERT_THRESHOLD}%!")

# 3. Support Telegram/WeChat delivery (alternative to email)
import requests
TELEGRAM_BOT_TOKEN = "your_bot_token"
TELEGRAM_CHAT_ID = "your_chat_id"

def send_telegram(msg):
 url = f"https://api.telegram.org/bot{TELEGRAM_BOT_TOKEN}/sendMessage"
 requests.post(url, json={
 'chat_id': TELEGRAM_CHAT_ID,
 'text': msg,
 'parse_mode': 'HTML'
 })