In plain English
Tools are how the analysis actually gets done: a spreadsheet for structure and communication, SQL for getting the data, Python or R for anything repeated.
The advanced view
Choose the tool by the failure mode you fear. Spreadsheets fail silently at scale and version control; SQL is declarative and set-based, so performance follows from indexing and join cardinality; scripting gives reproducibility, testing and automation. Production analytics needs all three with clear boundaries between them.
Python is the primary language for data work in finance: pandas for manipulation, numpy for numerics, scipy for distributions and tests, statsmodels for econometrics and GARCH, scikit-learn for machine learning, matplotlib and seaborn for charts. R remains dominant in academic statistics and some quant shops — tidyverse, forecast, tidymodels, rugarch.
SQL is mandatory: SELECT, all JOIN types, GROUP BY and HAVING, subqueries, CTEs, and especially window functions (ROW_NUMBER, RANK, LAG, LEAD, running aggregates) for rolling averages, rankings and year-on-year comparisons. Excel is still the lingua franca of corporate finance: INDEX-MATCH/XLOOKUP, SUMPRODUCT, IF/IFS, pivot tables, data tables for sensitivity, Goal Seek and Solver, named ranges — and keyboard speed, which interviewers do notice.
Stack to demonstrate
pandas / numpy
Data manipulation and numerics. The default working environment.
statsmodels / scikit-learn
Econometrics and ML. OLS, logit, time series, pipelines, cross-validation.
SQL window functions
Rolling aggregates, ranks, lags. The most-tested SQL skill in analyst interviews.
Excel modelling
Three-statement models, sensitivity tables, Goal Seek, clean formula discipline.
Why it works
Reproducibility works because a script is an executable record of every decision. When a number is challenged — and in consulting it always is — a re-runnable pipeline turns a two-day rebuild into two minutes, which is why the discipline pays for itself on the first revision cycle.
Common pitfalls
- ×Hard-coding assumptions inside formulas instead of keeping a labelled input block.
- ×Mixing inputs and calculations in the same cells, making audit impossible.
- ×Reporting a number that no one, including you, can regenerate.
How it is used — a model that survives review
Step 1 of 4
- 1Blue cells = inputs, black =
Deeper
Deeper: an analysis that survives review
The technical standard is reproducibility: someone else runs your file and gets your number. That means raw data untouched, transformations in code or documented formulas, hard-coded inputs in one clearly marked block, and every output traceable to a source.
In Excel specifically: colour-code inputs against formulas, never bury a constant inside a formula, build one row per driver, and add check rows (balance sheet balances, sources = uses, sum of segments = total). Those checks catch most modelling errors before an interviewer does.
Must know cold
- ✓Separate inputs, calculations and outputs.
- ✓Add explicit check rows and let them show red when broken.
- ✓SQL: GROUP BY for aggregation, window functions for running and ranked measures.
- ✓Version and date every file you send.
Exercises
Try each one on paper before revealing the worked solution.
Exercise 1
You inherit a model where WACC is typed inside 14 formulas. What do you do, and what check do you add?
Exercise 2
Write the logic (not the exact syntax) for revenue by segment per quarter, with the share of total.