Pandas for Traders

# A DataFrame is a table; a Series is one of its columns. Meet both.
import os
from datetime import datetime, timedelta

from openalgo import api

client = api(
    api_key=os.getenv("OPENALGO_API_KEY", "your_api_key_here"),
    host=os.getenv("OPENALGO_HOST", "http://127.0.0.1:5000"),
)

end = datetime.now().strftime("%Y-%m-%d")
start = (datetime.now() - timedelta(days=40)).strftime("%Y-%m-%d")

df = client.history(symbol="RELIANCE", exchange="NSE", interval="D", start_date=start, end_date=end)

print("Type      :", type(df).__name__)        # DataFrame -- the whole table
print("Shape     :", df.shape)                  # (rows, columns)
print("Columns   :", df.columns.tolist())       # the column labels
print("Index name:", df.index.name)             # the timestamp index
print("\nFirst 3 rows:")
print(df.head(3))

# Pull single columns (Series) and several columns (a smaller DataFrame).
import os
from datetime import datetime, timedelta

from openalgo import api

client = api(
    api_key=os.getenv("OPENALGO_API_KEY", "your_api_key_here"),
    host=os.getenv("OPENALGO_HOST", "http://127.0.0.1:5000"),
)

end = datetime.now().strftime("%Y-%m-%d")
start = (datetime.now() - timedelta(days=40)).strftime("%Y-%m-%d")
df = client.history(symbol="TCS", exchange="NSE", interval="D", start_date=start, end_date=end)

close = df["close"]                  # one column -> a Series
print("close is a", type(close).__name__)
print("Latest close :", close.iloc[-1])      # iloc[-1] = last value by position
print("Highest close:", close.max())
print("Average close:", round(close.mean(), 2))

hl = df[["high", "low"]]             # two columns -> a DataFrame
print("\nHigh/Low table (last 3 rows):")
print(hl.tail(3))

# Slice rows by date with .loc -- the timestamp index makes this effortless.
import os
from datetime import datetime, timedelta

from openalgo import api

client = api(
    api_key=os.getenv("OPENALGO_API_KEY", "your_api_key_here"),
    host=os.getenv("OPENALGO_HOST", "http://127.0.0.1:5000"),
)

end = datetime.now()
start = end - timedelta(days=90)
df = client.history(symbol="INFY", exchange="NSE", interval="D",
                    start_date=start.strftime("%Y-%m-%d"), end_date=end.strftime("%Y-%m-%d"))

# Last 30 calendar days as a date string -- .loc selects every row from there on.
last_month = (end - timedelta(days=30)).strftime("%Y-%m-%d")
recent = df.loc[last_month:]
print(f"Rows since {last_month}:", len(recent))
print("Close range in window:", recent["close"].min(), "to", recent["close"].max())

# .loc also reads a single row by its exact index label.
one_row = df.loc[df.index[-1]]
print("\nMost recent session:")
print(one_row)

# .rolling() slides a window across the data -- the basis of every moving average.
import os
from datetime import datetime, timedelta

from openalgo import api

client = api(
    api_key=os.getenv("OPENALGO_API_KEY", "your_api_key_here"),
    host=os.getenv("OPENALGO_HOST", "http://127.0.0.1:5000"),
)

end = datetime.now().strftime("%Y-%m-%d")
start = (datetime.now() - timedelta(days=120)).strftime("%Y-%m-%d")
df = client.history(symbol="HDFCBANK", exchange="NSE", interval="D", start_date=start, end_date=end)

df["sma20"] = df["close"].rolling(window=20).mean()   # 20-day simple moving average
df["sma50"] = df["close"].rolling(window=50).mean()   # 50-day simple moving average

print(df[["close", "sma20", "sma50"]].tail(4))

# A classic read: is price trading above its 20-day average?
last = df.iloc[-1]
trend = "above" if last["close"] > last["sma20"] else "below"
print(f"\nClose {last['close']} is {trend} the 20-day average {round(last['sma20'], 2)}")

# .pct_change() turns a price column into daily percentage returns.
import os
from datetime import datetime, timedelta

from openalgo import api

client = api(
    api_key=os.getenv("OPENALGO_API_KEY", "your_api_key_here"),
    host=os.getenv("OPENALGO_HOST", "http://127.0.0.1:5000"),
)

end = datetime.now().strftime("%Y-%m-%d")
start = (datetime.now() - timedelta(days=90)).strftime("%Y-%m-%d")
df = client.history(symbol="ICICIBANK", exchange="NSE", interval="D", start_date=start, end_date=end)

df["ret"] = df["close"].pct_change()        # today/yesterday - 1, as a fraction
df["ret_pct"] = (df["ret"] * 100).round(2)  # same thing as a readable percentage

print(df[["close", "ret_pct"]].tail(5))

up_days = (df["ret"] > 0).sum()             # boolean True counts as 1
print("\nUp days     :", int(up_days), "of", df["ret"].notna().sum())
print("Best day    :", round(df["ret"].max() * 100, 2), "%")
print("Worst day   :", round(df["ret"].min() * 100, 2), "%")
print("Average day :", round(df["ret"].mean() * 100, 3), "%")

# .resample() rolls daily candles up to weekly -- here on an MCX commodity.
import os
from datetime import datetime, timedelta

from openalgo import api

client = api(
    api_key=os.getenv("OPENALGO_API_KEY", "your_api_key_here"),
    host=os.getenv("OPENALGO_HOST", "http://127.0.0.1:5000"),
)

end = datetime.now().strftime("%Y-%m-%d")
start = (datetime.now() - timedelta(days=120)).strftime("%Y-%m-%d")
df = client.history(symbol="GOLDM03JUL26FUT", exchange="MCX", interval="D", start_date=start, end_date=end)

# Each weekly candle: first open, highest high, lowest low, last close, summed volume.
rules = {"open": "first", "high": "max", "low": "min", "close": "last", "volume": "sum"}
weekly = df.resample("W").agg(rules).dropna()

print("Daily rows:", len(df), "-> weekly rows:", len(weekly))
print(weekly.tail(4))

# .groupby() answers "which weekday has the best average return?"
import os
from datetime import datetime, timedelta

from openalgo import api

client = api(
    api_key=os.getenv("OPENALGO_API_KEY", "your_api_key_here"),
    host=os.getenv("OPENALGO_HOST", "http://127.0.0.1:5000"),
)

end = datetime.now().strftime("%Y-%m-%d")
start = (datetime.now() - timedelta(days=200)).strftime("%Y-%m-%d")
df = client.history(symbol="SBIN", exchange="NSE", interval="D", start_date=start, end_date=end)

df["ret"] = df["close"].pct_change()
df["weekday"] = df.index.day_name()        # Monday, Tuesday, ... from the timestamp

# Group the returns by weekday and average each group.
by_day = df.groupby("weekday")["ret"].mean() * 100
order = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"]
by_day = by_day.reindex(order)             # keep the natural week order

print("Average daily return by weekday (%):")
print(by_day.round(3))
print("\nBest weekday on average:", by_day.idxmax())

# Chain daily returns into a growth curve with .cumprod().
import os
from datetime import datetime, timedelta

from openalgo import api

client = api(
    api_key=os.getenv("OPENALGO_API_KEY", "your_api_key_here"),
    host=os.getenv("OPENALGO_HOST", "http://127.0.0.1:5000"),
)

end = datetime.now().strftime("%Y-%m-%d")
start = (datetime.now() - timedelta(days=180)).strftime("%Y-%m-%d")
df = client.history(symbol="RELIANCE", exchange="NSE", interval="D", start_date=start, end_date=end)

df["ret"] = df["close"].pct_change().fillna(0)

# Each day multiplies the running balance by (1 + that day's return).
df["growth"] = (1 + df["ret"]).cumprod()       # 1.0 = starting value
df["equity"] = 100000 * df["growth"]           # what 1,00,000 would have become

print(df[["close", "growth", "equity"]].tail(4).round(2))

total = (df["growth"].iloc[-1] - 1) * 100
print(f"\nBuy-and-hold over {len(df)} sessions: {round(total, 2)}%")

# Running max with .cummax() gives you drawdown -- the pain of every dip.
import os
from datetime import datetime, timedelta

from openalgo import api

client = api(
    api_key=os.getenv("OPENALGO_API_KEY", "your_api_key_here"),
    host=os.getenv("OPENALGO_HOST", "http://127.0.0.1:5000"),
)

end = datetime.now().strftime("%Y-%m-%d")
start = (datetime.now() - timedelta(days=180)).strftime("%Y-%m-%d")
df = client.history(symbol="GOLDM03JUL26FUT", exchange="MCX", interval="D", start_date=start, end_date=end)

df["peak"] = df["close"].cummax()                       # highest close seen so far
df["drawdown"] = (df["close"] / df["peak"] - 1) * 100   # % below that peak today

print(df[["close", "peak", "drawdown"]].tail(4).round(2))
print("\nDeepest drawdown:", round(df["drawdown"].min(), 2), "%")
print("On date         :", df["drawdown"].idxmin().date())

# Combine two symbols' closes into one aligned DataFrame, then compare them.
import os
from datetime import datetime, timedelta

import pandas as pd
from openalgo import api

client = api(
    api_key=os.getenv("OPENALGO_API_KEY", "your_api_key_here"),
    host=os.getenv("OPENALGO_HOST", "http://127.0.0.1:5000"),
)

end = datetime.now().strftime("%Y-%m-%d")
start = (datetime.now() - timedelta(days=90)).strftime("%Y-%m-%d")


def close_of(sym):
    d = client.history(symbol=sym, exchange="NSE", interval="D", start_date=start, end_date=end)
    return d["close"]


# pd.concat lines the two Series up on their shared timestamp index.
prices = pd.concat({"TCS": close_of("TCS"), "INFY": close_of("INFY")}, axis=1).dropna()
print(prices.tail(3))

# Correlation of daily returns -- do these two IT names move together?
rets = prices.pct_change().dropna()
print("\nReturn correlation:", round(rets["TCS"].corr(rets["INFY"]), 3))

# .describe() and friends summarise a whole column in one line.
import os
from datetime import datetime, timedelta

from openalgo import api

client = api(
    api_key=os.getenv("OPENALGO_API_KEY", "your_api_key_here"),
    host=os.getenv("OPENALGO_HOST", "http://127.0.0.1:5000"),
)

end = datetime.now().strftime("%Y-%m-%d")
start = (datetime.now() - timedelta(days=120)).strftime("%Y-%m-%d")
df = client.history(symbol="HDFCBANK", exchange="NSE", interval="D", start_date=start, end_date=end)

df["ret_pct"] = df["close"].pct_change() * 100      # daily returns in percent

# describe() gives count, mean, std (volatility), min, quartiles and max at once.
print("Daily return summary (%):")
print(df["ret_pct"].describe().round(3))

# Annualised volatility: daily std (in %) scaled by the square root of ~252 trading days.
ann_vol = df["ret_pct"].std() * (252 ** 0.5)
print("\nAnnualised volatility:", round(ann_vol, 1), "%")