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))