#!/usr/bin/env python3 """ DeFi Collateral Simulation Excel Generator Generates DeFi_Collateral_Simulation.xlsx with: - Assets sheet: Asset inputs, toggles, LTVs - Summary sheet: Portfolio totals, LTV, HF, status - Simulation sheet: Multi-round modeling with per-round recomputation - Optional Redeploy sheet: Advanced asset-level redeploy grid """ import xlsxwriter from typing import List, Dict, Tuple # Configuration OUTPUT_FILE = 'DeFi_Collateral_Simulation.xlsx' MAX_ROUNDS = 10 DEFAULT_ASSETS = [ {'name': 'ETH', 'amount': 10, 'price': 2000, 'ltv': 0.80, 'liq_th': 0.825, 'is_stable': False}, {'name': 'wBTC', 'amount': 1, 'price': 60000, 'ltv': 0.70, 'liq_th': 0.75, 'is_stable': False}, {'name': 'stETH', 'amount': 0, 'price': 2000, 'ltv': 0.75, 'liq_th': 0.80, 'is_stable': False}, {'name': 'USDC', 'amount': 5000, 'price': 1, 'ltv': 0.90, 'liq_th': 0.92, 'is_stable': True}, ] # Named range prefixes ASSETS_PREFIX = 'Assets_' SUMMARY_PREFIX = 'Summary_' SIM_PREFIX = 'Sim_' def create_workbook(filename: str) -> xlsxwriter.Workbook: """Create workbook with formatting options.""" workbook = xlsxwriter.Workbook(filename, {'remove_timezone': True}) return workbook def add_assets_sheet(workbook: xlsxwriter.Workbook, assets: List[Dict]) -> None: """Create Assets sheet with asset inputs, toggles, and calculations.""" worksheet = workbook.add_worksheet('Assets') # Header row headers = ['Asset', 'Amount', 'Price (USD)', 'Value (USD)', 'Collateral ON/OFF', 'LTV', 'Liquidation Threshold', 'Collateral Value', 'Max Borrowable'] header_format = workbook.add_format({ 'bold': True, 'bg_color': '#366092', 'font_color': 'white', 'align': 'center', 'valign': 'vcenter', 'border': 1 }) for col, header in enumerate(headers): worksheet.write(0, col, header, header_format) # Column widths worksheet.set_column('A:A', 12) worksheet.set_column('B:B', 12) worksheet.set_column('C:C', 12) worksheet.set_column('D:D', 12) worksheet.set_column('E:E', 18) worksheet.set_column('F:F', 10) worksheet.set_column('G:G', 22) worksheet.set_column('H:H', 18) worksheet.set_column('I:I', 15) # Data validation for ✅/❌ dropdown checkbox_format = workbook.add_format({'align': 'center'}) # Write asset data num_assets = len(assets) for row_idx, asset in enumerate(assets, start=1): excel_row = row_idx + 1 # Excel is 1-indexed, row 1 is header # Asset name worksheet.write(excel_row, 0, asset['name']) # Amount (user input) worksheet.write(excel_row, 1, asset['amount']) # Price (user input) worksheet.write(excel_row, 2, asset['price']) # Value (USD) = Amount * Price worksheet.write_formula(excel_row, 3, f'=B{excel_row}*C{excel_row}') # Collateral ON/OFF (dropdown: ✅, ❌) worksheet.data_validation(excel_row, 4, excel_row, 4, { 'validate': 'list', 'source': ['✅', '❌'], 'error_type': 'stop', 'error_title': 'Invalid Value', 'error_message': 'Must be ✅ or ❌' }) # Default to ✅ worksheet.write(excel_row, 4, '✅', checkbox_format) # LTV (default value) worksheet.write(excel_row, 5, asset['ltv']) # Liquidation Threshold (for display only) worksheet.write(excel_row, 6, asset['liq_th']) # Collateral Value = IF(E{row}="✅", D{row}, 0) worksheet.write_formula(excel_row, 7, f'=IF(E{excel_row}="✅",D{excel_row},0)') # Max Borrowable = IF(E{row}="✅", D{row}*F{row}, 0) worksheet.write_formula(excel_row, 8, f'=IF(E{excel_row}="✅",D{excel_row}*F{excel_row},0)') # Create named ranges for Assets sheet last_row = num_assets + 1 workbook.define_name(f'{ASSETS_PREFIX}Amount', f'Assets!$B$2:$B${last_row}') workbook.define_name(f'{ASSETS_PREFIX}Price', f'Assets!$C$2:$C${last_row}') workbook.define_name(f'{ASSETS_PREFIX}Value', f'Assets!$D$2:$D${last_row}') workbook.define_name(f'{ASSETS_PREFIX}CollateralOn', f'Assets!$E$2:$E${last_row}') workbook.define_name(f'{ASSETS_PREFIX}LTV', f'Assets!$F$2:$F${last_row}') workbook.define_name(f'{ASSETS_PREFIX}LiqTh', f'Assets!$G$2:$G${last_row}') workbook.define_name(f'{ASSETS_PREFIX}CollateralValue', f'Assets!$H$2:$H${last_row}') workbook.define_name(f'{ASSETS_PREFIX}MaxBorrowable', f'Assets!$I$2:$I${last_row}') workbook.define_name(f'{ASSETS_PREFIX}AssetNames', f'Assets!$A$2:$A${last_row}') # Store asset metadata in a hidden column (J) for Python reference # Column J: is_stable flag (1 for stable, 0 for volatile) for row_idx, asset in enumerate(assets, start=1): excel_row = row_idx + 1 worksheet.write(excel_row, 9, 1 if asset['is_stable'] else 0) workbook.define_name(f'{ASSETS_PREFIX}IsStable', f'Assets!$J$2:$J${last_row}') def add_summary_sheet(workbook: xlsxwriter.Workbook, num_assets: int) -> None: """Create Summary sheet with portfolio totals, LTV, HF, and status.""" worksheet = workbook.add_worksheet('Summary') # Header header_format = workbook.add_format({ 'bold': True, 'bg_color': '#366092', 'font_color': 'white', 'align': 'center', 'valign': 'vcenter', 'border': 1 }) worksheet.write(0, 0, 'Metric', header_format) worksheet.write(0, 1, 'Value', header_format) worksheet.set_column('A:A', 25) worksheet.set_column('B:B', 20) # Labels and formulas label_format = workbook.add_format({'bold': True, 'align': 'right'}) value_format = workbook.add_format({'num_format': '#,##0.00', 'align': 'right'}) hf_format = workbook.add_format({'num_format': '0.0000', 'align': 'right'}) row = 1 # Total Collateral Value worksheet.write(row, 0, 'Total Collateral Value', label_format) worksheet.write_formula(row, 1, f'=SUM(Assets!H2:H{num_assets+1})', value_format) workbook.define_name(f'{SUMMARY_PREFIX}TotalCollateral', f'Summary!$B${row+1}') row += 1 # Total Max Borrowable worksheet.write(row, 0, 'Total Max Borrowable', label_format) worksheet.write_formula(row, 1, f'=SUM(Assets!I2:I{num_assets+1})', value_format) workbook.define_name(f'{SUMMARY_PREFIX}TotalMaxBorrow', f'Summary!$B${row+1}') row += 1 # Borrowed (user input) worksheet.write(row, 0, 'Borrowed (input)', label_format) worksheet.write(row, 1, 25000, value_format) # Default test value workbook.define_name(f'{SUMMARY_PREFIX}BorrowedInput', f'Summary!$B${row+1}') row += 1 # Portfolio LTV = IFERROR(Borrowed / TotalCollateral, 0) worksheet.write(row, 0, 'Portfolio LTV', label_format) worksheet.write_formula(row, 1, f'=IFERROR({SUMMARY_PREFIX}BorrowedInput/{SUMMARY_PREFIX}TotalCollateral,0)', value_format) row += 1 # Health Factor (HF) = IFERROR(TotalMaxBorrow / Borrowed, 0) worksheet.write(row, 0, 'Health Factor (HF)', label_format) hf_cell = f'B{row+1}' worksheet.write_formula(row, 1, f'=IFERROR({SUMMARY_PREFIX}TotalMaxBorrow/{SUMMARY_PREFIX}BorrowedInput,0)', hf_format) workbook.define_name(f'{SUMMARY_PREFIX}HF_Portfolio', f'Summary!${hf_cell}') row += 1 # Status = IF(HF>=2,"✅ Safe","⚠ Risky") worksheet.write(row, 0, 'Status', label_format) worksheet.write_formula(row, 1, f'=IF({SUMMARY_PREFIX}HF_Portfolio>=2,"✅ Safe","⚠ Risky")') row += 1 # Conditional formatting for HF worksheet.conditional_format(f'B{row-1}', { 'type': 'cell', 'criteria': '>=', 'value': 2, 'format': workbook.add_format({'bg_color': '#C6EFCE', 'font_color': '#006100'}) }) worksheet.conditional_format(f'B{row-1}', { 'type': 'cell', 'criteria': '<', 'value': 2, 'format': workbook.add_format({'bg_color': '#FFC7CE', 'font_color': '#9C0006'}) }) def add_simulation_sheet(workbook: xlsxwriter.Workbook, assets: List[Dict], max_rounds: int) -> None: """Create Simulation sheet with multi-round modeling and per-round recomputation.""" worksheet = workbook.add_worksheet('Simulation') num_assets = len(assets) # Header section for optimization controls header_format = workbook.add_format({ 'bold': True, 'bg_color': '#366092', 'font_color': 'white', 'align': 'center', 'valign': 'vcenter', 'border': 1 }) worksheet.write(0, 0, 'Optimization Controls', header_format) worksheet.merge_range(0, 0, 0, 3, 'Optimization Controls', header_format) worksheet.write(1, 0, 'Max Repay per Round:', workbook.add_format({'bold': True})) worksheet.write(1, 1, 5000) # Default workbook.define_name(f'{SIM_PREFIX}MaxRepayPerRound', 'Simulation!$B$2') worksheet.write(2, 0, 'Max Swap per Round:', workbook.add_format({'bold': True})) worksheet.write(2, 1, 10000) # Default workbook.define_name(f'{SIM_PREFIX}MaxSwapPerRound', 'Simulation!$B$3') worksheet.write(3, 0, 'Optimization On/Off:', workbook.add_format({'bold': True})) worksheet.data_validation(3, 1, 3, 1, { 'validate': 'list', 'source': ['✅', '❌'], 'error_type': 'stop' }) worksheet.write(3, 1, '❌') # Default off workbook.define_name(f'{SIM_PREFIX}OptimizationOn', 'Simulation!$B$4') # Spacer row = 5 # Main simulation table headers headers = ['Round', 'Borrowed', 'Repay Amount', 'Swap Volatile → Stable', 'New Collateral Value', 'Max Borrow', 'HF', 'LTV', 'Status', 'Suggested Repay', 'Suggested Swap'] header_row = row for col, header in enumerate(headers): worksheet.write(header_row, col, header, header_format) worksheet.set_column('A:A', 8) worksheet.set_column('B:B', 15) worksheet.set_column('C:C', 15) worksheet.set_column('D:D', 25) worksheet.set_column('E:E', 22) worksheet.set_column('F:F', 15) worksheet.set_column('G:G', 12) worksheet.set_column('H:H', 12) worksheet.set_column('I:I', 12) worksheet.set_column('J:J', 18) worksheet.set_column('K:K', 18) row += 1 # Helper block: Per-asset, per-round calculations (columns L onwards, hidden) # Structure: For each round, for each asset: Base Value, Adjusted Value, Collateral Value, Max Borrowable helper_start_col = 11 # Column L (after main table ends at K) helper_header_row = header_row # Write helper column headers (will be hidden) helper_col = helper_start_col for asset in assets: asset_name = asset['name'] # Base Value, Adjusted Value, Collateral Value, Max Borrowable worksheet.write(helper_header_row, helper_col, f'{asset_name}_Base', workbook.add_format({'hidden': True})) helper_col += 1 worksheet.write(helper_header_row, helper_col, f'{asset_name}_Adj', workbook.add_format({'hidden': True})) helper_col += 1 worksheet.write(helper_header_row, helper_col, f'{asset_name}_Coll', workbook.add_format({'hidden': True})) helper_col += 1 worksheet.write(helper_header_row, helper_col, f'{asset_name}_MaxB', workbook.add_format({'hidden': True})) helper_col += 1 # Round 0 (initial state) round_0_row = row # Round number worksheet.write(round_0_row, 0, 0) # Borrowed_0 = Summary!BorrowedInput worksheet.write_formula(round_0_row, 1, f'={SUMMARY_PREFIX}BorrowedInput') # Repay Amount (user input, 0 for round 0) worksheet.write(round_0_row, 2, 0) # Swap Volatile → Stable (user input, 0 for round 0) worksheet.write(round_0_row, 3, 0) # Helper function to convert column index to letter def col_to_letter(col_idx): """Convert 0-based column index to Excel column letter (A, B, ..., Z, AA, AB, ...)""" result = '' col_idx += 1 # Excel is 1-indexed while col_idx > 0: col_idx -= 1 result = chr(65 + (col_idx % 26)) + result col_idx //= 26 return result # Helper block for Round 0: Initialize from Assets sheet helper_col = helper_start_col helper_col_refs = [] # Store column letters for each asset's helper columns for asset_idx, asset in enumerate(assets): asset_row = asset_idx + 2 # Assets sheet row (1-indexed, row 1 is header) base_col_letter = col_to_letter(helper_col) adj_col_letter = col_to_letter(helper_col + 1) coll_col_letter = col_to_letter(helper_col + 2) maxb_col_letter = col_to_letter(helper_col + 3) helper_col_refs.append({ 'base': base_col_letter, 'adj': adj_col_letter, 'coll': coll_col_letter, 'maxb': maxb_col_letter }) # Base Value = Assets!D{row} worksheet.write_formula(round_0_row, helper_col, f'=Assets!D{asset_row}') helper_col += 1 # Adjusted Value = Base Value (no swap in round 0) worksheet.write_formula(round_0_row, helper_col, f'={base_col_letter}{round_0_row+1}') # Reference base column helper_col += 1 # Collateral Value = IF(Assets!E{row}="✅", Adjusted Value, 0) worksheet.write_formula(round_0_row, helper_col, f'=IF(Assets!E{asset_row}="✅",{adj_col_letter}{round_0_row+1},0)') helper_col += 1 # Max Borrowable = IF(Assets!E{row}="✅", Adjusted Value * Assets!F{row}, 0) worksheet.write_formula(round_0_row, helper_col, f'=IF(Assets!E{asset_row}="✅",{adj_col_letter}{round_0_row+1}*Assets!F{asset_row},0)') helper_col += 1 # New Collateral Value_0 = SUM of helper Collateral Value columns coll_value_formula = '+'.join([ f'{refs["coll"]}{round_0_row + 1}' for refs in helper_col_refs ]) worksheet.write_formula(round_0_row, 4, f'={coll_value_formula}') # Max Borrow_0 = SUM of helper Max Borrowable columns max_borrow_formula = '+'.join([ f'{refs["maxb"]}{round_0_row + 1}' for refs in helper_col_refs ]) worksheet.write_formula(round_0_row, 5, f'={max_borrow_formula}') # HF_0 = IFERROR(MaxBorrow_0 / Borrowed_0, 0) # If Borrowed = 0, set HF to large number (999) but Status remains ✅ worksheet.write_formula(round_0_row, 6, f'=IF(B{round_0_row+1}=0,999,IFERROR(F{round_0_row+1}/B{round_0_row+1},0))') # LTV_0 = IFERROR(Borrowed_0 / NewCollateralValue_0, 0) worksheet.write_formula(round_0_row, 7, f'=IFERROR(B{round_0_row+1}/E{round_0_row+1},0)') # Status_0 = IF(HF_0>=2 OR Borrowed=0,"✅","⚠") worksheet.write_formula(round_0_row, 8, f'=IF(OR(G{round_0_row+1}>=2,B{round_0_row+1}=0),"✅","⚠")') # Suggested Repay and Swap (optimizer output, initially empty) worksheet.write(round_0_row, 9, '') worksheet.write(round_0_row, 10, '') row += 1 # Rounds 1 to max_rounds for round_num in range(1, max_rounds + 1): round_row = row prev_round_row = round_row - 1 # Round number worksheet.write(round_row, 0, round_num) # Borrowed_t = MAX(Borrowed_{t-1} - Repay_t, 0) worksheet.write_formula(round_row, 1, f'=MAX(B{prev_round_row+1}-C{round_row+1},0)') # Repay Amount (user input) worksheet.write(round_row, 2, 0) # Swap Volatile → Stable (user input) worksheet.write(round_row, 3, 0) # Helper block: Per-asset calculations with swap applied helper_col = helper_start_col # First, compute sum of volatile collateral values for pro-rata calculation # This will be used to distribute swap reduction across volatile assets volatile_sum_col = helper_start_col + num_assets * 4 # Column after all asset helpers volatile_sum_formula_parts = [] for asset_idx, asset in enumerate(assets): asset_row = asset_idx + 2 refs = helper_col_refs[asset_idx] base_col_letter = refs['base'] adj_col_letter = refs['adj'] coll_col_letter = refs['coll'] maxb_col_letter = refs['maxb'] # Base Value = Previous round's Adjusted Value worksheet.write_formula(round_row, helper_col, f'={adj_col_letter}{prev_round_row+1}') helper_col += 1 # Adjusted Value = Base Value - (pro-rata swap reduction if volatile) + (swap increase if USDC) if asset['is_stable']: # USDC: Add swap amount worksheet.write_formula(round_row, helper_col, f'={base_col_letter}{round_row+1}+D{round_row+1}') else: # Volatile: Subtract pro-rata share of swap # Pro-rata = (This asset's collateral value / Sum of all volatile collateral) * Swap amount # First, we need to compute the sum of volatile collateral values from previous round volatile_coll_refs = [] for v_idx, v_asset in enumerate(assets): if not v_asset['is_stable']: v_refs = helper_col_refs[v_idx] volatile_coll_refs.append(f'{v_refs["coll"]}{prev_round_row+1}') if volatile_coll_refs: volatile_sum = '+'.join(volatile_coll_refs) # Pro-rata reduction this_asset_coll = f'{coll_col_letter}{prev_round_row+1}' pro_rata_reduction = f'IF({volatile_sum}>0,({this_asset_coll}/{volatile_sum})*D{round_row+1},0)' worksheet.write_formula(round_row, helper_col, f'=MAX({base_col_letter}{round_row+1}-{pro_rata_reduction},0)') else: # No volatile assets, no reduction worksheet.write_formula(round_row, helper_col, f'={base_col_letter}{round_row+1}') helper_col += 1 # Collateral Value = IF(Assets!E{row}="✅", Adjusted Value, 0) worksheet.write_formula(round_row, helper_col, f'=IF(Assets!E{asset_row}="✅",{adj_col_letter}{round_row+1},0)') # Add to volatile sum if volatile if not asset['is_stable']: volatile_sum_formula_parts.append(f'{coll_col_letter}{round_row+1}') helper_col += 1 # Max Borrowable = IF(Assets!E{row}="✅", Adjusted Value * Assets!F{row}, 0) worksheet.write_formula(round_row, helper_col, f'=IF(Assets!E{asset_row}="✅",{adj_col_letter}{round_row+1}*Assets!F{asset_row},0)') helper_col += 1 # New Collateral Value_t = SUM of helper Collateral Value columns coll_value_formula = '+'.join([ f'{refs["coll"]}{round_row + 1}' for refs in helper_col_refs ]) worksheet.write_formula(round_row, 4, f'={coll_value_formula}') # Max Borrow_t = SUM of helper Max Borrowable columns max_borrow_formula = '+'.join([ f'{refs["maxb"]}{round_row + 1}' for refs in helper_col_refs ]) worksheet.write_formula(round_row, 5, f'={max_borrow_formula}') # HF_t = IFERROR(MaxBorrow_t / Borrowed_t, 0) # If Borrowed = 0, set HF to large number (999) but Status remains ✅ worksheet.write_formula(round_row, 6, f'=IF(B{round_row+1}=0,999,IFERROR(F{round_row+1}/B{round_row+1},0))') # LTV_t = IFERROR(Borrowed_t / NewCollateralValue_t, 0) worksheet.write_formula(round_row, 7, f'=IFERROR(B{round_row+1}/E{round_row+1},0)') # Status_t = IF(HF_t>=2 OR Borrowed=0,"✅","⚠") worksheet.write_formula(round_row, 8, f'=IF(OR(G{round_row+1}>=2,B{round_row+1}=0),"✅","⚠")') # Suggested Repay and Swap (heuristic optimizer) # Heuristic: If HF < 2, suggest actions to bring it to 2.0 # Repay suggestion: Amount needed to make HF = 2 after repay # HF_target = 2 = MaxBorrow / (Borrowed_prev - Repay) => Repay = Borrowed_prev - MaxBorrow/2 # Use previous round's borrowed (before repay) for calculation suggested_repay_formula = ( f'IF(AND({SIM_PREFIX}OptimizationOn="✅",G{round_row+1}<2,B{prev_round_row+1}>0),' f'MIN(B{prev_round_row+1},' f'MAX(0,B{prev_round_row+1}-F{round_row+1}/2),' f'{SIM_PREFIX}MaxRepayPerRound),"")' ) worksheet.write_formula(round_row, 9, suggested_repay_formula) # Swap suggestion: Amount needed to increase MaxBorrow enough to make HF = 2 # HF_target = 2 = (MaxBorrow + Swap*LTV_stable) / Borrowed_current # => Swap = (2*Borrowed_current - MaxBorrow) / LTV_stable # For simplicity, assume LTV_stable = 0.90 (USDC default) # Note: This uses current round's borrowed (after repay) for accuracy suggested_swap_formula = ( f'IF(AND({SIM_PREFIX}OptimizationOn="✅",G{round_row+1}<2,B{round_row+1}>0),' f'MIN({SIM_PREFIX}MaxSwapPerRound,' f'MAX(0,(2*B{round_row+1}-F{round_row+1})/0.90)),"")' ) worksheet.write_formula(round_row, 10, suggested_swap_formula) row += 1 # Hide helper columns for col in range(helper_start_col, helper_start_col + num_assets * 4): worksheet.set_column(col, col, None, None, {'hidden': True}) # Conditional formatting for HF column hf_col = 'G' hf_start_row = round_0_row + 1 hf_end_row = row worksheet.conditional_format(f'{hf_col}{hf_start_row}:{hf_col}{hf_end_row}', { 'type': 'cell', 'criteria': '>=', 'value': 2, 'format': workbook.add_format({'bg_color': '#C6EFCE', 'font_color': '#006100'}) }) worksheet.conditional_format(f'{hf_col}{hf_start_row}:{hf_col}{hf_end_row}', { 'type': 'cell', 'criteria': '<', 'value': 2, 'format': workbook.add_format({'bg_color': '#FFC7CE', 'font_color': '#9C0006'}) }) def add_redeploy_sheet(workbook: xlsxwriter.Workbook, assets: List[Dict], max_rounds: int) -> None: """Create optional Redeploy sheet for advanced asset-level redeploy grid.""" worksheet = workbook.add_worksheet('Redeploy (optional)') header_format = workbook.add_format({ 'bold': True, 'bg_color': '#366092', 'font_color': 'white', 'align': 'center', 'valign': 'vcenter', 'border': 1 }) # Instructions worksheet.write(0, 0, 'Advanced Redeploy Grid (Optional)', header_format) worksheet.merge_range(0, 0, 0, max_rounds + 1, 'Advanced Redeploy Grid (Optional)', header_format) worksheet.write(1, 0, 'If used, this overrides aggregate swap for that round.') worksheet.write(2, 0, 'Enter USD deltas: positive = move TO stable, negative = move FROM stable.') # Headers: Asset | Round 0 | Round 1 | ... | Round N row = 4 worksheet.write(row, 0, 'Asset', header_format) for round_num in range(max_rounds + 1): worksheet.write(row, round_num + 1, f'Round {round_num}', header_format) row += 1 # Per-asset rows for asset in assets: if asset['is_stable']: continue # Skip stable assets in this grid (they're the target) worksheet.write(row, 0, f"Delta {asset['name']} To Stable (USD)") for round_num in range(max_rounds + 1): worksheet.write(row, round_num + 1, 0) # Default: no swap row += 1 worksheet.set_column('A:A', 30) for col in range(1, max_rounds + 2): worksheet.set_column(col, col, 15) def add_help_sheet(workbook: xlsxwriter.Workbook) -> None: """Create Help sheet with test cases and documentation.""" worksheet = workbook.add_worksheet('Help') header_format = workbook.add_format({ 'bold': True, 'bg_color': '#366092', 'font_color': 'white', 'align': 'center', 'valign': 'vcenter', 'border': 1 }) row = 0 worksheet.write(row, 0, 'DeFi Collateral Simulation - Help & Test Cases', header_format) worksheet.merge_range(row, 0, row, 1, 'DeFi Collateral Simulation - Help & Test Cases', header_format) row += 2 # Test Cases section worksheet.write(row, 0, 'Test Cases', workbook.add_format({'bold': True, 'font_size': 14})) row += 1 test_cases = [ ('Test 1: Baseline', 'ETH: 10 @ $2,000; wBTC: 1 @ $60,000; stETH: 0; USDC: 5,000 @ $1; all ✅; Borrowed=25,000', 'Round 0 should match: TotalCollateral = SUM(H), TotalMaxBorrow = SUM(I), ' 'HF_0 = TotalMaxBorrow / 25,000, LTV_0 = 25,000 / TotalCollateral'), ('Test 2: Swap only', 'Round 1, Swap=$4,000 (no repay)', 'Volatile H values reduce pro-rata by 4,000; USDC H increases by 4,000. ' 'MaxBorrow_1 recomputed as SUM of per-asset Value * LTV after swap. HF must increase.'), ('Test 3: Repay only', 'Round 1, Repay=$3,000 (no swap)', 'Borrowed_1 = 22,000; MaxBorrow_1 stays driven by unchanged collateral; HF_1 increases.'), ('Test 4: Combined', 'Round 1, Repay=$2,000, Swap=$3,000', 'Both effects apply; HF_1 rises more; LTV_1 drops.'), ('Test 5: Optimizer check', 'With caps MaxRepayPerRound=3,000, MaxSwapPerRound=4,000, and HF_0 < 2', 'Suggestions bring HF_1 to just ≥ 2 without large overshoot.'), ] for test_name, setup, expected in test_cases: worksheet.write(row, 0, test_name, workbook.add_format({'bold': True})) row += 1 worksheet.write(row, 0, 'Setup:', workbook.add_format({'italic': True})) worksheet.write(row, 1, setup) row += 1 worksheet.write(row, 0, 'Expected:', workbook.add_format({'italic': True})) worksheet.write(row, 1, expected) row += 2 # Formulas section row += 1 worksheet.write(row, 0, 'Key Formulas', workbook.add_format({'bold': True, 'font_size': 14})) row += 1 formulas = [ ('Health Factor (HF)', 'HF = TotalMaxBorrow / Borrowed'), ('Loan-to-Value (LTV)', 'LTV = Borrowed / TotalCollateral'), ('Collateral Value', 'IF(CollateralOn="✅", Value, 0)'), ('Max Borrowable', 'IF(CollateralOn="✅", Value * LTV, 0)'), ] for name, formula in formulas: worksheet.write(row, 0, name, workbook.add_format({'bold': True})) worksheet.write(row, 1, formula) row += 1 def main(): """Generate the DeFi Collateral Simulation workbook.""" print(f"Generating {OUTPUT_FILE}...") workbook = create_workbook(OUTPUT_FILE) # Create sheets add_assets_sheet(workbook, DEFAULT_ASSETS) add_summary_sheet(workbook, len(DEFAULT_ASSETS)) add_simulation_sheet(workbook, DEFAULT_ASSETS, MAX_ROUNDS) add_redeploy_sheet(workbook, DEFAULT_ASSETS, MAX_ROUNDS) add_help_sheet(workbook) workbook.close() print(f"✅ Successfully generated {OUTPUT_FILE}") print(f" - Assets sheet: {len(DEFAULT_ASSETS)} assets") print(f" - Summary sheet: Portfolio totals and HF") print(f" - Simulation sheet: {MAX_ROUNDS + 1} rounds (0-{MAX_ROUNDS})") print(f" - Redeploy sheet: Advanced asset-level redeploy") print(f" - Help sheet: Test cases and documentation") if __name__ == '__main__': main()