d-bis/27-combi
4
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
27-combi/generate_defi_simulation.py

692 lines
28 KiB
Python
Raw Permalink Normal View History

Initial commit: add .gitignore and README
2026-02-09 21:51:30 -08:00
#!/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()
Reference in New Issue Copy Permalink