#!/usr/bin/env python3
"""Make an offline, invented exchange-rate sensitivity exercise."""
from pathlib import Path

from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.workbook.properties import CalcProperties

P=Path(__file__).resolve().parent
w=Workbook();s=w.active;s.title='Invented rates'
s['A1']='Fictional AUD–USD rate switch · never a live quote · no personal data'
s.merge_cells('A1:G1')
s['A1'].font=Font(size=14,bold=True,color='FFFFFF');s['A1'].fill=PatternFill('solid',fgColor='204F70')
heads=['Model','USD per AUD $1','AUD input','USD output','USD input','AUD output','Rate status']
for col,head in enumerate(heads,1):
 c=s.cell(2,col,head);c.font=Font(bold=True,color='FFFFFF');c.fill=PatternFill('solid',fgColor='11665B');c.alignment=Alignment(wrap_text=True,vertical='center')
for r,(label,rate,aud,usd) in enumerate([('Card M',0.64,250,96),('Try changed rate',0.80,250,96)],3):
 s.cell(r,1,label)
 for col,val in [(2,rate),(3,aud),(5,usd)]:
  c=s.cell(r,col,val);c.fill=PatternFill('solid',fgColor='FFF0B8');c.number_format='0.00' if col==2 else '$#,##0.00'
 s.cell(r,4,f'=C{r}*B{r}').number_format='$#,##0.00'
 s.cell(r,6,f'=E{r}/B{r}').number_format='$#,##0.00'
 s.cell(r,7,'INVENTED').alignment=Alignment(horizontal='center')
 for col in range(1,8):s.cell(r,col).border=Border(bottom=Side(style='hair',color='B7CBC7'))
s['A6']='Direction';s['B6']='AUD to USD: multiply by USD per AUD. USD to AUD: divide by USD per AUD.';s.merge_cells('B6:G6')
s['A7']='Try';s['B7']='Change only B4. Predict both output directions before reading D4 and F4.';s.merge_cells('B7:G7')
s['A8']='Check';s['B8']='For Card M, D3 ÷ B3 should recover C3; F3 × B3 should recover E3.';s.merge_cells('B8:G8')
s['A9']='Limit';s['B9']='Stipulated rates omit live quotes, dates, fees, spread and rounding rules.';s.merge_cells('B9:G9')
s['A10']='Access';s['B10']='Card M and exchange-directions.pdf provide the paper/text equivalent.';s.merge_cells('B10:G10')
s['A11']='Privacy';s['B11']='Enter invented amounts only; do not record transactions or personal money.';s.merge_cells('B11:G11')
for r in range(6,12):s.cell(r,1).font=Font(bold=True);s.cell(r,2).alignment=Alignment(wrap_text=True,vertical='center');s.row_dimensions[r].height=30
for col,width in {'A':23,'B':18,'C':18,'D':18,'E':18,'F':18,'G':19}.items():s.column_dimensions[col].width=width
s.row_dimensions[2].height=40;s.freeze_panes='B3'
s.sheet_properties.pageSetUpPr.fitToPage=True;s.page_setup.fitToWidth=1;s.page_setup.fitToHeight=1
s.page_setup.paperSize=s.PAPERSIZE_A4;s.page_setup.orientation=s.ORIENTATION_LANDSCAPE
s.page_margins.left=0.3;s.page_margins.right=0.3;s.print_area='A1:G11'
w.calculation=CalcProperties(calcMode='auto',fullCalcOnLoad=True)
out=P/'rate-switch.xlsx';w.save(out);print(out)