Spaces:
Running
Running
File size: 4,816 Bytes
5d47dd0 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 | """Module for parsing Zerodha CSV exports."""
import pandas as pd
import io
def clean_numeric_series(series):
"""Removes commas and % signs from a pandas Series, then converts to float."""
return series.astype(str).str.replace(',', '', regex=False).str.replace('%', '', regex=False).astype(float)
def load_portfolio(file_path_or_string):
"""
Reads a Zerodha portfolio CSV and returns a cleaned pandas DataFrame.
Accepts either a file path ending in .csv or a raw CSV text string.
"""
try:
if file_path_or_string.strip().endswith('.csv') and '\n' not in file_path_or_string:
df = pd.read_csv(file_path_or_string.strip())
else:
df = pd.read_csv(io.StringIO(file_path_or_string.strip()))
# Strip whitespace from column names
df.columns = df.columns.str.strip()
# Rename columns. Using a flexible mapping in case of slight variations (like "Qty." vs "Qty")
col_mapping = {
"Instrument": "symbol",
"Qty.": "qty",
"Qty": "qty",
"Avg. cost": "avg_cost",
"LTP": "ltp",
"Invested": "invested",
"Cur. val": "cur_val",
"P&L": "pnl",
"Net chg.": "net_chg",
"Day chg.": "day_chg"
}
df = df.rename(columns=col_mapping)
# Drop rows where symbol is empty or NaN
if 'symbol' in df.columns:
df = df.dropna(subset=['symbol'])
df = df[df['symbol'].astype(str).str.strip() != '']
# Convert numeric columns
numeric_cols = ['qty', 'avg_cost', 'ltp', 'invested', 'cur_val', 'pnl', 'net_chg', 'day_chg']
for col in numeric_cols:
if col in df.columns:
df[col] = clean_numeric_series(df[col])
return df
except Exception as e:
raise ValueError(f"Failed to load or parse portfolio: {e}")
def validate_portfolio(df):
"""
Validates the parsed portfolio DataFrame.
Returns (True, None) if valid.
Returns (False, error_message) if invalid.
"""
try:
if df.empty:
return False, "Portfolio DataFrame is empty (no rows)."
required_cols = ['symbol', 'qty', 'avg_cost', 'ltp', 'invested', 'cur_val', 'pnl', 'net_chg', 'day_chg']
missing_cols = [col for col in required_cols if col not in df.columns]
if missing_cols:
found_cols = list(df.columns)
return False, f"Missing required columns: {', '.join(missing_cols)}\nFound columns: {', '.join(found_cols)}"
if df['symbol'].isnull().any() or (df['symbol'].astype(str).str.strip() == '').any():
return False, "Found rows with null or empty symbols."
return True, None
except Exception as e:
return False, f"Validation failed with error: {e}"
def portfolio_to_text(df):
"""
Converts the cleaned DataFrame into a formatted text summary for AI prompts.
"""
lines = []
for _, row in df.iterrows():
# SYMBOL | Qty: X | Avg: ₹Y | LTP: ₹Z | P&L: ₹A (B%) | Day: C%
sym = row['symbol']
qty = row['qty']
avg = row['avg_cost']
ltp = row['ltp']
pnl = row['pnl']
net = row['net_chg']
day = row['day_chg']
line = f"{sym} | Qty: {qty:g} | Avg: ₹{avg:.2f} | LTP: ₹{ltp:.2f} | P&L: ₹{pnl:.2f} ({net:+.2f}%) | Day: {day:+.2f}%"
lines.append(line)
lines.append("-" * 40)
total_invested = df['invested'].sum() if 'invested' in df.columns else 0.0
total_cur_val = df['cur_val'].sum() if 'cur_val' in df.columns else 0.0
lines.append(f"Total Invested: ₹{total_invested:.2f} | Total Current Value: ₹{total_cur_val:.2f}")
return "\n".join(lines)
if __name__ == "__main__":
import sys
if sys.platform == 'win32':
sys.stdout.reconfigure(encoding='utf-8')
# Simple test with sample data matching Zerodha format
sample_csv = '''Instrument,Qty.,Avg. cost,LTP,Invested,Cur. val,P&L,Net chg.,Day chg.
RELIANCE,10,"2,450.50","2,500.00","24,505.00","25,000.00","495.00",2.02%,0.50%
TCS,5,"3,200.00","3,150.00","16,000.00","15,750.00","-250.00",-1.56%,-0.20%
,10,100,100,1000,1000,0,0%,0%
'''
print("Loading sample portfolio...")
df = load_portfolio(sample_csv)
print("\nValidating DataFrame...")
is_valid, err = validate_portfolio(df)
print(f"Is valid? {is_valid}")
if not is_valid:
print(f"Error: {err}")
print("\nDataFrame Output:")
print(df.to_string())
print("\nFormatted Text:")
print(portfolio_to_text(df))
|