Python for Excel Users in Finance

Transition from Excel to Python for financial analysis. VLOOKUP, pivot tables, and charts — all in pandas.

Read Excel Files

import pandas as pd

# Read an Excel file
df = pd.read_excel("portfolio.xlsx", sheet_name="Holdings")
print(df.head())

# This replaces: opening Excel, selecting cells, scrolling

VLOOKUP Equivalent

# Excel VLOOKUP = pandas merge
prices = pd.read_excel("portfolio.xlsx", sheet_name="Prices")
holdings = pd.read_excel("portfolio.xlsx", sheet_name="Holdings")

# Merge on ticker column (like VLOOKUP)
combined = holdings.merge(prices, on="Ticker", how="left")
combined["Value"] = combined["Shares"] * combined["Price"]
print(combined)

Pivot Tables

# Excel Pivot Table = pandas pivot_table
pivot = pd.pivot_table(
    combined,
    values="Value",
    index="Sector",
    aggfunc=["sum", "count", "mean"]
)
print(pivot)

Charts

import matplotlib.pyplot as plt

# Pie chart of sector allocation
sector_vals = combined.groupby("Sector")["Value"].sum()
sector_vals.plot.pie(autopct="%1.1f%%", figsize=(8, 8))
plt.title("Portfolio Allocation by Sector")
plt.ylabel("")
plt.savefig("allocation.png")
plt.show()