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 = "[email protected]"
    password = "your_app_password"
    receivers = ["[email protected]"]
    
    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'
    })