import xlsxwriter
from google.colab import files
# Create workbook
wb = xlsxwriter.Workbook("Master_Finance_Planner.xlsx")
# Create sheets
sheet_names = ['Income', 'Expenses', 'Budget Planner', 'Goals', 'Savings',
'Investments',
'Dashboard', 'Forecast', 'Recommendations', 'User Profiles',
'Settings',
'Net Worth']
sheets = {name: wb.add_worksheet(name) for name in sheet_names}
# Format styles
header = wb.add_format({'bold': True, 'bg_color': '#C6EFCE', 'border': 1})
currency = wb.add_format({'num_format': '₨#,##0.00'})
percent = wb.add_format({'num_format': '0.00%'})
bold = wb.add_format({'bold': True})
highlight = wb.add_format({'bg_color': '#FFC7CE', 'font_color': '#9C0006'})
# === Settings ===
set_ws = sheets['Settings']
set_ws.write_column('A1', ['Categories', 'Frequency'])
set_ws.write_column('A2', ['Essentials', 'Lifestyle', 'Financial', 'Savings',
'Investment'])
set_ws.write_column('B2', ['Monthly', 'Quarterly', 'Annually'])
set_ws.write_row('D1', ['Currency', 'Region', 'Inflation Rate', 'Fiscal Start'])
set_ws.write_row('D2', ['₨', 'Pakistan', '6%', 'January'])
# === Income ===
inc = sheets['Income']
inc.write_row('A1', ['User', 'Source', 'Amount', 'Frequency', 'Yield %', 'Monthly
Income'], header)
for r in range(2, 30):
inc.data_validation(f'D{r}', {'validate': 'list', 'source': '=Settings!
$B$2:$B$4'})
inc.write_formula(f'F{r}', f'=C{r}*(E{r}/12)', currency)
# === Expenses ===
exp = sheets['Expenses']
exp.write_row('A1', ['User', 'Date', 'Month', 'Category', 'Description', 'Amount',
'Fixed/Variable'], header)
for r in range(2, 50):
exp.data_validation(f'D{r}', {'validate': 'list', 'source': '=Settings!
$A$2:$A$6'})
exp.conditional_format(f'F{r}', {'type': 'cell', 'criteria': '>', 'value':
20000, 'format': highlight})
# === Budget Planner ===
bp = sheets['Budget Planner']
bp.write_row('A1', ['Month', 'Category', 'Budgeted ₨', 'Actual ₨', 'Variance ₨',
'Accuracy %'], header)
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']
for i, m in enumerate(months):
r = i + 2
bp.write(f'A{r}', m)
bp.data_validation(f'B{r}', {'validate': 'list', 'source': '=Settings!
$A$2:$A$6'})
bp.write_formula(f'D{r}', f'=SUMIFS(Expenses!F2:F50, Expenses!D2:D50, B{r},
Expenses!C2:C50, A{r})', currency)
bp.write_formula(f'E{r}', f'=C{r}-D{r}', currency)
bp.write_formula(f'F{r}', f'=IF(C{r}=0,0,D{r}/C{r})', percent)
bp.conditional_format(f'E{r}', {'type': 'cell', 'criteria': '<', 'value': 0,
'format': highlight})
# === Goals ===
goals = sheets['Goals']
goals.write_row('A1', ['User', 'Goal', 'Target ₨', 'Current ₨', 'Deadline',
'Progress %'], header)
for r in range(2, 12):
goals.write_formula(f'F{r}', f'=D{r}/C{r}', percent)
# === Savings ===
sav = sheets['Savings']
sav.write_row('A1', ['Fund Type', 'Target ₨', 'Current ₨', 'Completion %'], header)
for r in range(2, 12):
sav.write_formula(f'D{r}', f'=C{r}/B{r}', percent)
# === Investments ===
inv = sheets['Investments']
inv.write_row('A1', ['Fund Name', 'Type', 'Initial ₨', 'Monthly ₨', 'Yield %',
'Projected Value'], header)
for r in range(2, 22):
inv.write_formula(f'F{r}', f'=C{r}*(1+E{r})+D{r}', currency)
# === Net Worth ===
nw = sheets['Net Worth']
nw.write_row('A1', ['Asset', 'Value ₨', '', 'Liability', 'Amount ₨'], header)
nw.write('A20', 'Net Worth')
nw.write_formula('B20', '=SUM(B2:B19)-SUM(E2:E19)', currency)
# === Forecast ===
fc = sheets['Forecast']
fc.write_row('A1', ['Month', 'Projected Income', 'Projected Expenses', 'Projected
Savings'], header)
# === Recommendations ===
reco = sheets['Recommendations']
reco.write_row('A1', ['Month', 'Category', 'Advice', 'Priority'], header)
# === User Profiles ===
profile = sheets['User Profiles']
profile.write_row('A1', ['User', 'Email', 'Age', 'Income Tier', 'Spending Style'],
header)
# === Dashboard ===
dash = sheets['Dashboard']
dash.write('A1', '📊 Monthly Overview', bold)
dash.write('A3', 'Total Income')
dash.write_formula('B3', '=SUM(Income!F2:F29)', currency)
dash.write('A4', 'Total Expenses')
dash.write_formula('B4', '=SUM(Expenses!F2:F49)', currency)
dash.write('A5', 'Surplus')
dash.write_formula('B5', '=B3-B4', currency)
# Chart: Income vs Expense
chart1 = wb.add_chart({'type': 'column'})
chart1.add_series({
'categories': '=Dashboard!$A$3:$A$4',
'values': '=Dashboard!$B$3:$B$4',
})
chart1.set_title({'name': 'Monthly Income vs Expense'})
dash.insert_chart('D3', chart1)
# Chart: Budget Acc
'name': 'Budget Accuracy'
})
chart2.set_title({'name': 'Budget Performance'})
dash.insert_chart('D20', chart2)
# Finalize
wb.close()
files.download("Master_Finance_Planner.xlsx")
