""" Monte Carlo Daily Simulation - IMPROVED VERSION - Cash balances included - Historical tracking chart - Fixed factor analysis alignment """ import numpy as np import pandas as pd from google.cloud import storage import requests import smtplib from email.mime.text import MIMEText from email.mime.multipart import MIMEMultipart from email.mime.image import MIMEImage import os import json from datetime import datetime import matplotlib matplotlib.use('Agg') # Non-interactive backend import matplotlib.pyplot as plt import matplotlib.dates as mdates from io import BytesIO import base64 # Configuration BUCKET_NAME = os.environ.get('BUCKET', 'stocks_position') EMAIL_TO = os.environ.get('EMAIL_TO') EMAIL_FROM = os.environ.get('EMAIL_FROM') SMTP_USER = os.environ.get('SMTP_USER') SMTP_PASS = os.environ.get('SMTP_PASS') QUESTRADE_REFRESH_TOKEN = os.environ.get('QUESTRADE_REFRESH_TOKEN') QUESTRADE_ACCOUNT_LIRA = os.environ.get('QUESTRADE_ACCOUNT_LIRA', '********') QUESTRADE_ACCOUNT_RRSP = os.environ.get('QUESTRADE_ACCOUNT_RRSP', '********') ALPHAVANTAGE_API_KEY = os.environ.get('ALPHAVANTAGE_API_KEY') # Simulation Parameters N_PATHS = 5000 TRADING_DAYS_PER_YEAR = 252 # Market Correction Parameters CORRECTION_PROBABILITY_PER_YEAR = 0.15 CORRECTION_DROP_MEAN = -0.35 CORRECTION_DROP_STD = 0.10 CORRECTION_CORRELATION = 0.50 # Currency Risk Parameters USDCAD_DRIFT = -0.005 USDCAD_VOLATILITY = 0.08 # Financial Parameters INFLATION_RATE = 0.025 def get_fresh_refresh_token(): """Get the freshest refresh token - from GCS if available, else env var""" try: client = storage.Client() bucket = client.bucket(BUCKET_NAME) blob = bucket.blob('outputs/questrade_tokens.json') if blob.exists(): data = json.loads(blob.download_as_string()) if 'refresh_token' in data: print("Using refresh token from GCS (auto-updated)") return data['refresh_token'] except Exception as e: print(f"Could not load token from GCS: {e}") print("Using refresh token from environment variable") return QUESTRADE_REFRESH_TOKEN def refresh_questrade_token(refresh_token): """Get new access token from Questrade""" url = "https://login.questrade.com/oauth2/token" params = {'grant_type': 'refresh_token', 'refresh_token': refresh_token} response = requests.get(url, params=params) response.raise_for_status() data = response.json() # Save new refresh token to GCS client = storage.Client() bucket = client.bucket(BUCKET_NAME) blob = bucket.blob('outputs/questrade_tokens.json') blob.upload_from_string(json.dumps(data, indent=2)) return data['access_token'], data['api_server'] def get_account_positions(access_token, api_server, account_number): """Fetch positions for a single Questrade account""" headers = {'Authorization': f'Bearer {access_token}'} url = f"{api_server}v1/accounts/{account_number}/positions" response = requests.get(url, headers=headers) response.raise_for_status() positions = response.json()['positions'] return positions def get_account_balances(access_token, api_server, account_number): """Fetch cash balances for a single Questrade account""" headers = {'Authorization': f'Bearer {access_token}'} url = f"{api_server}v1/accounts/{account_number}/balances" response = requests.get(url, headers=headers) response.raise_for_status() balances = response.json() # Extract total cash (combined currencies converted to CAD, but we'll use USD) combined_balances = balances.get('combinedBalances', []) # Find USD cash balance usd_cash = 0 for balance in combined_balances: if balance.get('currency') == 'USD': usd_cash = balance.get('cash', 0) break return usd_cash def fetch_all_questrade_positions(): """Fetch and combine positions + cash from both LIRA and RRSP accounts""" print("Fetching Questrade positions and balances from both accounts...") access_token, api_server = refresh_questrade_token(get_fresh_refresh_token()) # Fetch both accounts - positions lira_positions = get_account_positions(access_token, api_server, QUESTRADE_ACCOUNT_LIRA) rrsp_positions = get_account_positions(access_token, api_server, QUESTRADE_ACCOUNT_RRSP) # Fetch both accounts - cash balances lira_cash = get_account_balances(access_token, api_server, QUESTRADE_ACCOUNT_LIRA) rrsp_cash = get_account_balances(access_token, api_server, QUESTRADE_ACCOUNT_RRSP) lira_market_value = sum(pos['currentMarketValue'] for pos in lira_positions) if lira_positions else 0 rrsp_market_value = sum(pos['currentMarketValue'] for pos in rrsp_positions) if rrsp_positions else 0 lira_total = lira_market_value + lira_cash rrsp_total = rrsp_market_value + rrsp_cash print(f"LIRA: ${lira_market_value:,.2f} (positions) + ${lira_cash:,.2f} (cash) = ${lira_total:,.2f} USD") print(f"RRSP: ${rrsp_market_value:,.2f} (positions) + ${rrsp_cash:,.2f} (cash) = ${rrsp_total:,.2f} USD") print(f"Total: ${lira_total + rrsp_total:,.2f} USD") return { 'lira': { 'positions': lira_positions, 'market_value': lira_market_value, 'cash': lira_cash, 'value': lira_total }, 'rrsp': { 'positions': rrsp_positions, 'market_value': rrsp_market_value, 'cash': rrsp_cash, 'value': rrsp_total }, 'total_value': lira_total + rrsp_total } def get_usdcad_rate(): """Fetch current USD/CAD exchange rate with fallback""" try: url = "https://www.alphavantage.co/query" params = { 'function': 'CURRENCY_EXCHANGE_RATE', 'from_currency': 'USD', 'to_currency': 'CAD', 'apikey': ALPHAVANTAGE_API_KEY } response = requests.get(url, params=params) response.raise_for_status() data = response.json() # Check if we got valid data if 'Realtime Currency Exchange Rate' in data: rate = float(data['Realtime Currency Exchange Rate']['5. Exchange Rate']) print(f"USD/CAD rate: {rate:.4f} (from API)") return rate else: print(f"⚠️ Alpha Vantage API error: {data}") raise ValueError("Invalid API response") except Exception as e: # Fallback to approximate current rate fallback_rate = 1.39 print(f"⚠️ Could not fetch USD/CAD rate: {e}") print(f"Using fallback rate: {fallback_rate:.4f}") return fallback_rate def load_parameters_from_gcs(): """Load mu, sigma, and correlations from GCS""" client = storage.Client() bucket = client.bucket(BUCKET_NAME) blob = bucket.blob('parameters/mu_sigma.csv') mu_sigma_df = pd.read_csv(blob.open('r')) blob = bucket.blob('parameters/correlations.csv') correlations_df = pd.read_csv(blob.open('r'), index_col=0) return mu_sigma_df, correlations_df def match_portfolio_to_parameters(portfolio_tickers, mu_sigma_df, correlations_df): """Match portfolio tickers to available parameters, filter out mismatches""" # Tickers must be in BOTH mu_sigma AND correlations available_in_mu_sigma = set(mu_sigma_df['ticker'].values) available_in_corr = set(correlations_df.index) available_tickers = available_in_mu_sigma & available_in_corr # Intersection portfolio_tickers_set = set(portfolio_tickers) # Find tickers that are in portfolio but not in parameters missing_tickers = portfolio_tickers_set - available_tickers if missing_tickers: print(f"⚠️ Warning: These tickers in your portfolio don't have parameters: {missing_tickers}") print(f" They will be excluded from simulation") # Use only tickers that have parameters matched_tickers = [t for t in portfolio_tickers if t in available_tickers] if not matched_tickers: raise ValueError("No tickers in portfolio match available parameters!") print(f"Using {len(matched_tickers)} tickers for simulation: {matched_tickers}") # Filter parameters to only matched tickers mu_sigma_filtered = mu_sigma_df[mu_sigma_df['ticker'].isin(matched_tickers)].reset_index(drop=True) correlations_filtered = correlations_df.loc[matched_tickers, matched_tickers] return matched_tickers, mu_sigma_filtered, correlations_filtered def simulate_portfolio(lira_value, rrsp_value, mu, sigma, correlations, usdcad_rate, years, monthly_contrib_cad, n_corrections): """ Run Monte Carlo simulation with dual accounts LIRA: No contributions, just grows RRSP: Receives all contributions """ n_days = years * TRADING_DAYS_PER_YEAR days_per_month = TRADING_DAYS_PER_YEAR // 12 # Initialize arrays lira_vals = np.zeros((N_PATHS, n_days + 1)) rrsp_vals = np.zeros((N_PATHS, n_days + 1)) lira_vals[:, 0] = lira_value rrsp_vals[:, 0] = rrsp_value # Simulate USD/CAD dt = 1 / TRADING_DAYS_PER_YEAR usdcad_paths = np.zeros((N_PATHS, n_days + 1)) usdcad_paths[:, 0] = usdcad_rate # Generate returns using correlation matrix n_assets = len(mu) chol = np.linalg.cholesky(correlations) # Schedule market corrections randomly correction_days = [] if n_corrections > 0: correction_days = np.sort(np.random.choice( range(60, n_days - 60), n_corrections, replace=False )) # Daily simulation for day in range(1, n_days + 1): # Generate correlated random returns z = np.random.normal(0, 1, (N_PATHS, n_assets)) correlated_z = z @ chol.T # Check for market correction if day in correction_days: # Severe correlated drop crash_magnitude = np.random.normal(CORRECTION_DROP_MEAN, CORRECTION_DROP_STD, N_PATHS) crash_magnitude = np.clip(crash_magnitude, -0.60, -0.15) # Between -15% and -60% # Apply to all assets with high correlation crash_component = crash_magnitude[:, np.newaxis] * np.ones((N_PATHS, n_assets)) correlated_z = CORRECTION_CORRELATION * crash_component + (1 - CORRECTION_CORRELATION) * correlated_z # Calculate returns daily_returns = (mu / TRADING_DAYS_PER_YEAR) + (sigma / np.sqrt(TRADING_DAYS_PER_YEAR)) * correlated_z portfolio_returns = daily_returns.mean(axis=1) # Equal weighted for now # Update both accounts with same returns lira_vals[:, day] = lira_vals[:, day - 1] * (1 + portfolio_returns) rrsp_vals[:, day] = rrsp_vals[:, day - 1] * (1 + portfolio_returns) # Add monthly contributions to RRSP only (in USD) if day % days_per_month == 0: usdcad_paths[:, day] = usdcad_paths[:, day - 1] * np.exp( (USDCAD_DRIFT - 0.5 * USDCAD_VOLATILITY**2) * dt + USDCAD_VOLATILITY * np.sqrt(dt) * np.random.normal(0, 1, N_PATHS) ) contrib_usd = monthly_contrib_cad / usdcad_paths[:, day] rrsp_vals[:, day] += contrib_usd else: usdcad_paths[:, day] = usdcad_paths[:, day - 1] # Calculate final values final_lira = lira_vals[:, -1] final_rrsp = rrsp_vals[:, -1] final_total = final_lira + final_rrsp return final_total, final_lira, final_rrsp def analyze_results(final_values, target_usd, inflation_rate, years): """Analyze simulation results""" # Inflation-adjusted target real_target = target_usd / ((1 + inflation_rate) ** years) median = np.median(final_values) p10 = np.percentile(final_values, 10) p90 = np.percentile(final_values, 90) prob_hit_nominal = (final_values >= target_usd).mean() prob_hit_real = (final_values >= real_target).mean() return { 'median': median, 'p10': p10, 'p90': p90, 'prob_nominal': prob_hit_nominal, 'prob_real': prob_hit_real, 'target_nominal': target_usd, 'target_real': real_target } def save_historical_results(portfolio_value, results_5yr): """Save today's results to historical tracking file""" client = storage.Client() bucket = client.bucket(BUCKET_NAME) blob = bucket.blob('outputs/historical_results.json') # Load existing history history = [] if blob.exists(): try: history = json.loads(blob.download_as_string()) except: history = [] # Add today's result today = { 'date': datetime.now().isoformat(), 'portfolio_value': portfolio_value, 'prob_5yr_nominal': results_5yr['no_contrib']['0_corrections']['prob_nominal'], 'prob_5yr_real': results_5yr['no_contrib']['0_corrections']['prob_real'], 'median_5yr': results_5yr['no_contrib']['0_corrections']['median'] } history.append(today) # Keep last 90 days only history = history[-90:] # Save back blob.upload_from_string(json.dumps(history, indent=2)) return history def create_tracking_chart(history): """Create a beautiful tracking chart of portfolio progression""" print("Creating tracking chart...") try: print(f" → Processing {len(history)} data points") if len(history) < 2: print(" → Not enough data points for chart (need at least 2)") return None # Extract data dates = [datetime.fromisoformat(h['date']) for h in history] portfolio_values = [h['portfolio_value'] for h in history] prob_nominal = [h['prob_5yr_nominal'] * 100 for h in history] medians = [h['median_5yr'] / 1000 for h in history] # In thousands print(f" → Creating matplotlib figure...") # Create figure with modern styling fig, (ax1, ax2) = plt.subplots(2, 1, figsize=(12, 8), facecolor='white') fig.subplots_adjust(hspace=0.3) # Chart 1: Portfolio Value ax1.plot(dates, portfolio_values, color='#2563eb', linewidth=2.5, marker='o', markersize=6, markerfacecolor='white', markeredgewidth=2) ax1.fill_between(dates, portfolio_values, alpha=0.1, color='#2563eb') ax1.set_title('Portfolio Value Over Time', fontsize=16, fontweight='bold', pad=15) ax1.set_ylabel('Value (USD)', fontsize=12, fontweight='600') ax1.grid(True, alpha=0.2, linestyle='--') ax1.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f'${x:,.0f}')) ax1.spines['top'].set_visible(False) ax1.spines['right'].set_visible(False) # Chart 2: Dual axis - Probability and Median ax2_twin = ax2.twinx() line1 = ax2.plot(dates, prob_nominal, color='#16a34a', linewidth=2.5, marker='o', markersize=6, markerfacecolor='white', markeredgewidth=2, label='Probability (5yr)') line2 = ax2_twin.plot(dates, medians, color='#dc2626', linewidth=2.5, marker='s', markersize=6, markerfacecolor='white', markeredgewidth=2, label='Median Outcome') ax2.set_title('5-Year Projections (No Contributions, 0 Crashes)', fontsize=16, fontweight='bold', pad=15) ax2.set_xlabel('Date', fontsize=12, fontweight='600') ax2.set_ylabel('Probability of $300k (%)', fontsize=12, fontweight='600', color='#16a34a') ax2_twin.set_ylabel('Median Outcome ($k)', fontsize=12, fontweight='600', color='#dc2626') ax2.tick_params(axis='y', labelcolor='#16a34a') ax2_twin.tick_params(axis='y', labelcolor='#dc2626') ax2.set_ylim(0, 100) # Y-axis from 0% to 100% ax2.set_yticks(range(0, 101, 10)) # Tick marks every 10% ax2.grid(True, alpha=0.2, linestyle='--') ax2.spines['top'].set_visible(False) ax2_twin.spines['top'].set_visible(False) # Format x-axis for ax in [ax1, ax2]: ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %d')) ax.xaxis.set_major_locator(mdates.DayLocator(interval=max(1, len(dates)//7))) plt.setp(ax.xaxis.get_majorticklabels(), rotation=45, ha='right') # Legend lines = line1 + line2 labels = [l.get_label() for l in lines] ax2.legend(lines, labels, loc='upper left', framealpha=0.9, fontsize=10) plt.tight_layout() print(f" → Encoding chart to base64...") # Convert to base64 buffer = BytesIO() plt.savefig(buffer, format='png', dpi=150, bbox_inches='tight') buffer.seek(0) image_base64 = base64.b64encode(buffer.read()).decode() plt.close() print(f" ✅ Chart created successfully ({len(image_base64)} bytes)") return image_base64 except Exception as e: import traceback print(f"❌ Chart generation FAILED!") print(f" Error: {str(e)}") print(f" Traceback:\n{traceback.format_exc()}") return None def send_email_report(portfolio_data, mu_sigma_df, results_5yr, results_10yr, tracking_chart_base64): """Send comprehensive email report with factor analysis and tracking chart""" lira_val = portfolio_data['lira']['value'] lira_market = portfolio_data['lira']['market_value'] lira_cash = portfolio_data['lira']['cash'] rrsp_val = portfolio_data['rrsp']['value'] rrsp_market = portfolio_data['rrsp']['market_value'] rrsp_cash = portfolio_data['rrsp']['cash'] total_val = portfolio_data['total_value'] # Determine status based on 5-year probability (nominal) prob_5yr = results_5yr['no_contrib']['0_corrections']['prob_nominal'] if prob_5yr >= 0.70: status = "✅ On Track" color = "green" elif prob_5yr >= 0.50: status = "⚠️ Watch" color = "orange" else: status = "🔴 Off Track" color = "red" # Load factor analysis from metadata try: client = storage.Client() bucket = client.bucket(BUCKET_NAME) blob = bucket.blob('parameters/metadata.json') metadata = json.loads(blob.download_as_string()) factor_analysis = metadata.get('factor_analysis', None) except: factor_analysis = None # Build HTML email html = f"""

[MC Dashboard] {status} - ${total_val:,.0f} USD

Account Breakdown

LIRA ({QUESTRADE_ACCOUNT_LIRA}): ${lira_market:,.2f} (positions) + ${lira_cash:,.2f} (cash) = ${lira_val:,.2f} USD (no contributions)

RRSP ({QUESTRADE_ACCOUNT_RRSP}): ${rrsp_market:,.2f} (positions) + ${rrsp_cash:,.2f} (cash) = ${rrsp_val:,.2f} USD (receives contributions)

Total Portfolio: ${total_val:,.2f} USD

""" # ADD TRACKING CHART (if available) if tracking_chart_base64: html += f"""

📈 Historical Tracking

Tracking Chart
""" # ADD FACTOR ANALYSIS SECTION (if available) if factor_analysis: concentration = factor_analysis['concentration_risk'] tech_pct = factor_analysis['tech_ai_percentage'] # Determine color based on risk risk_colors = { 'VERY HIGH': '#dc3545', 'HIGH': '#fd7e14', 'MODERATE': '#ffc107', 'LOW': '#28a745' } risk_color = risk_colors.get(concentration, '#6c757d') html += f"""

🎯 Factor Analysis

Concentration Risk: {concentration}

""" # Add other factors for factor_name, factor_data in factor_analysis['factors'].items(): if factor_data['count'] > 0 and factor_name != 'Tech/AI': pct = factor_data['percentage'] tickers = ', '.join(factor_data['tickers']) html += f""" """ html += """
Tech/AI Exposure:
{tech_pct:.1%}
{factor_name}:
{pct:.1%} ({tickers})

What This Means:

""" html += f"""

⚠️ Important Note on Parameters

This simulation uses actual 10-year historical data without artificial caps.

Average Portfolio Return: {mu_sigma_df['mu'].mean():.1%}

Average Portfolio Volatility: {mu_sigma_df['sigma'].mean():.1%}

These reflect your actual portfolio's historical performance. Results include:

5-Year Projections (Target: $300k USD)

""" for corrections in ['0_corrections', '1_corrections', '2_corrections']: label = corrections.replace('_', ' ').replace('corrections', 'crashes') html += f"" for contrib in ['no_contrib', 'contrib_250', 'contrib_500']: r = results_5yr[contrib][corrections] html += f""" """ html += "" html += """
Scenario No Contrib +$250 CAD/mo +$500 CAD/mo
{label} {r['prob_nominal']:.1%} (nominal)
{r['prob_real']:.1%} (real)
Median: ${r['median']:,.0f}

10-Year Projections (Target: $500k USD)

""" for corrections in ['0_corrections', '1_corrections', '2_corrections']: label = corrections.replace('_', ' ').replace('corrections', 'crashes') html += f"" for contrib in ['no_contrib', 'contrib_250', 'contrib_500']: r = results_10yr[contrib][corrections] html += f""" """ html += "" html += """
Scenario No Contrib +$250 CAD/mo +$500 CAD/mo
{label} {r['prob_nominal']:.1%} (nominal)
{r['prob_real']:.1%} (real)
Median: ${r['median']:,.0f}

💡 Key Insights

Generated: {datetime.now().strftime('%Y-%m-%d %H:%M:%S MT')}
Simulation: {N_PATHS:,} paths per scenario
Model: Improved Monte Carlo (no artificial caps, 10-year parameters)

""" # Send email msg = MIMEMultipart('related') msg['Subject'] = f"[MC Dashboard] {status} - ${total_val:,.0f} USD" msg['From'] = EMAIL_FROM msg['To'] = EMAIL_TO msg.attach(MIMEText(html, 'html')) # Attach chart image as separate part (if exists) if tracking_chart_base64: # Decode base64 to bytes image_data = base64.b64decode(tracking_chart_base64) # Create image attachment with Content-ID image = MIMEImage(image_data, name='chart.png') image.add_header('Content-ID', '') image.add_header('Content-Disposition', 'inline', filename='chart.png') msg.attach(image) server = smtplib.SMTP_SSL('smtp.gmail.com', 465) server.login(SMTP_USER, SMTP_PASS.replace(' ', '')) server.send_message(msg) server.quit() print("✅ Email sent successfully") def run(request=None): """Main Cloud Function entry point""" print("=" * 60) print("Monte Carlo Daily Simulation - IMPROVED VERSION") print("=" * 60) # Fetch current portfolio portfolio_data = fetch_all_questrade_positions() lira_value = portfolio_data['lira']['value'] rrsp_value = portfolio_data['rrsp']['value'] total_value = portfolio_data['total_value'] # Get portfolio tickers (only from accounts that have positions) all_positions = portfolio_data['lira']['positions'] + portfolio_data['rrsp']['positions'] if not all_positions: raise ValueError("No positions found in either account!") portfolio_tickers = list(set([pos['symbol'] for pos in all_positions])) print(f"Portfolio tickers: {portfolio_tickers}") # Get USD/CAD rate usdcad_rate = get_usdcad_rate() # Load parameters mu_sigma_df, correlations_df = load_parameters_from_gcs() # Match portfolio to parameters (handles IBIT mismatch) matched_tickers, mu_sigma_filtered, correlations_filtered = match_portfolio_to_parameters( portfolio_tickers, mu_sigma_df, correlations_df ) mu = mu_sigma_filtered['mu'].values sigma = mu_sigma_filtered['sigma'].values correlations = correlations_filtered.values print(f"\nPortfolio μ (avg): {mu.mean():.2%}") print(f"Portfolio σ (avg): {sigma.mean():.2%}") # Run all scenarios print("\n" + "=" * 60) print("Running simulations (this takes ~10 minutes)...") print("=" * 60) results_5yr = {} results_10yr = {} for contrib_label, contrib_cad in [('no_contrib', 0), ('contrib_250', 250), ('contrib_500', 500)]: results_5yr[contrib_label] = {} results_10yr[contrib_label] = {} for n_corrections in [0, 1, 2]: corr_label = f"{n_corrections}_corrections" # 5-year final_5, _, _ = simulate_portfolio( lira_value, rrsp_value, mu, sigma, correlations, usdcad_rate, 5, contrib_cad, n_corrections ) results_5yr[contrib_label][corr_label] = analyze_results( final_5, 300000, INFLATION_RATE, 5 ) # 10-year final_10, _, _ = simulate_portfolio( lira_value, rrsp_value, mu, sigma, correlations, usdcad_rate, 10, contrib_cad, n_corrections ) results_10yr[contrib_label][corr_label] = analyze_results( final_10, 500000, INFLATION_RATE, 10 ) print(f"✓ {contrib_label} / {corr_label}") # Save historical results and create tracking chart print("\nSaving historical results...") history = save_historical_results(total_value, results_5yr) print("Creating tracking chart...") tracking_chart_base64 = create_tracking_chart(history) # Send report print("\n" + "=" * 60) print("Sending email report...") send_email_report(portfolio_data, mu_sigma_df, results_5yr, results_10yr, tracking_chart_base64) print("=" * 60) print("✅ Daily simulation complete!") print("=" * 60) return {'statusCode': 200, 'body': json.dumps({'status': 'success'})} if __name__ == '__main__': run()