Most finance work still runs on Excel.
When I was a derivatives trader, most the $7 billion operation was based on Excel.
The process was the same:
You build the model, drag the formulas down and refresh it every Monday.
Eventually, the sheets got too big and wouldn't even open. The analysis was basic and not that interesting.
An actuary in one of my courses said most of his analysis lived in Excel, and "it was hard to get projects started because of a lack of framework."
Today, we'll talk about how you can take the existing work you've done on Excel and quickly move it to Python.
Why Python is the new Excel:
- Microsoft caps an Excel worksheet at 1,048,576 rows by 16,384 columns. Useful, until it's not.
- In October 2020, Public Health England left 15,841 positive COVID-19 cases out of its daily count because an old Excel file format which capped each sheet at 65,536 rows.
Excel works until your data outgrows it.
The solution: Replace Excel with Pandas
Each step takes about one minute. Your workbook keeps running the whole time.
Step 1: Load the workbook you already refresh
Start with the workbook you refresh.
Here, it’s an NVDA tracker with three formula columns: a 20-day moving average, 20-day annualized volatility and a one-year percentile rank.
Load the raw prices with one line: pd.read_excel().
You end up with your data in a pandas DataFrame that looks just like Excel. Here's a super simple way to think about it:
And here's what the data actually looks like in the DataFrame.
Step 2: Translate one column at a time
I call this the drag-down rule: any formula you drag down a column in Excel is one line of pandas. =AVERAGE(B2:B21) dragged down 2,000 rows becomes prices.rolling(20).mean().
In this code example, you'll see the equivalent Excel functions for each line of pandas.
If it looks simple, it's because it is. Now instead of being limited by Excel's constraints, you are limited by the amount of RAM on your computer.