Basic Excel. Manual entries. No performance visibility.
At Swastika Investmart, the trading team was maintaining basic Excel sheets to log trades — no formulas for performance metrics, no automation, no consistent structure. At the end of each day, entries were made by hand, P&L was calculated manually, and there was no way to quickly see drawdown trends or evaluate whether a strategy was actually performing.
With an active derivatives desk running multiple positions daily, the lack of structured performance tracking meant decisions were being made on gut feel rather than data. I built the MIS framework to fix that.
No drawdown tracking, no Sharpe ratio, no automated reports — performance review was a manual, error-prone process done at end of day.
One workbook. Four modules. Fully automated.
I designed the framework as a single Excel workbook with four dedicated sheets, each handling one aspect of trading performance. VBA macros connected everything — automating calculations, generating daily reports, and keeping the log structured with minimal manual input.
Audit the Current Process
Reviewed how the team was logging trades — what fields were being captured inconsistently, what calculations were missing, and where errors most commonly crept in.
Design the Trade Log Sheet
Built a structured daily trade log with standardised columns: symbol, strike, expiry, buy/sell, quantity, entry price, exit price, P&L, and trade type. VBA macros auto-calculated P&L on entry and applied input validation to prevent logging errors.
Build the P&L Tracker
A dedicated P&L sheet aggregated daily, weekly, and monthly profit/loss from the trade log automatically. Charts updated in real time as new trades were entered — no manual refresh needed.
Drawdown Calculator
Computed rolling drawdown from the equity curve — tracking peak capital, current capital, and maximum drawdown percentage. A conditional formatting heatmap flagged days where drawdown exceeded defined risk thresholds.
Sharpe Ratio Model
Built a rolling Sharpe ratio calculation using daily returns from the P&L tracker — annualised against a risk-free rate. This gave the team a single number to evaluate whether returns justified the risk being taken.
VBA Macro Automation
Macros automated the end-of-day report generation — pulling data from the trade log, updating all sheets, formatting the summary, and producing a printable daily MIS report with one button click.
Four sheets. One source of truth.
Each module in the workbook was built to be self-contained but linked — changes in the trade log flowed automatically into P&L, drawdown, and Sharpe ratio without any manual intervention.
Daily Trade Log
Structured entry sheet with input validation, auto P&L calculation per trade, and VBA-enforced formatting. Every trade captured consistently — symbol, strike, expiry, quantity, entry/exit, P&L.
P&L Tracker
Daily, weekly, and monthly P&L aggregated automatically from the trade log. Live charts updating on each new entry — no manual refresh or formula dragging required.
Drawdown Calculator
Rolling equity curve with peak tracking. Max drawdown percentage calculated and highlighted — conditional formatting flagged breaches of daily/weekly risk limits in red automatically.
Sharpe Ratio Model
Annualised rolling Sharpe ratio computed from daily returns against a risk-free rate. Single KPI to evaluate whether strategy returns justified risk — updated automatically each day.
From manual maintenance to one-click reports.
The framework replaced the team's ad-hoc Excel maintenance entirely. Daily reporting went from a manual, error-prone end-of-day task to a single button click — and for the first time, the desk had consistent, reliable visibility into actual trading performance.
For the first time, the trading desk had a Sharpe ratio and rolling drawdown to evaluate strategy risk — decisions moved from gut feel to data.
What this project reinforced.
Domain knowledge makes better tools
Having traded derivatives myself meant I knew exactly what the team needed — the right fields, the right risk metrics, the right thresholds. Tools built by practitioners fit differently than those built from the outside.
VBA is underrated for rapid internal tooling
No backend, no deployment, no dependencies — just open the file and click a button. For a trading desk that lives in Excel, VBA automation delivered immediate value with zero adoption friction.
Structure beats sophistication
The biggest improvement wasn't a complex formula — it was enforcing consistent trade log structure from day one. Clean input data made everything downstream (P&L, drawdown, Sharpe) reliable and trustworthy.