import pandas as pd import streamlit as st import gspread from google.oauth2.service_account import Credentials import ast import requests from datetime import datetime import json # Define the scope start = False starting_position = [] tradeHistory_positions = [] scopes = ["https://www.googleapis.com/auth/spreadsheets"] # Service account credentials as a dictionary service_account_info = { "type": "service_account", "project_id": "primetrade-433011", "private_key_id": "8bdab2f373343c045c8712c27e34f858132675df", "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQCtmNCg9Jkku2+W\nRXWqnOzoLQmXrn4BJC3yk7aaSGNh254/zMrWgyejTGpWxqklv0Hnxx1qn8nb3QoP\nKmRDbJnkt4doKupXFgfxPlebelLXgRT1JDbmVCTfCp8TcG8I1/9FpFNoqvpyeMZx\nd747UfP+bqym1pdhMr6rxCUEYVcKhc/4t+04k3i0IGGWW293CXGWGD54CIeFqWQX\n+SHo20pYfh8FKamytY8LHfwk1XbX1dMjnsxsQ/xZ8IjHZ//+m3bAG7n9QPe3a724\nG3L7iTZ15VET48j55aiSi4tJvHuy/I2kzOXrm/OuHRqJ+bH5+Ze8FVbmbBQlBjn8\nI8boxVSHAgMBAAECggEAMTAHEUwtJmjLpecZf5XGVMUKHkXtYxJmyICNMWsIad5q\nGQbEhIKWFSGeUecpX04xdOSI08Dh19/qLUDkNuyLMHDGN8BNNQ7DgloZRa8j0Pc8\nwncX7SxzZBVk3IOzmmxlYsy8a4BixVOuWtFEgBdpDLM8TWupafuQZigGGxcfrBWl\njgUoga05ybjpsdxW9c9+DoXXaOPHu/QQCEbv1X3dAJHJ0My2rBaO0s+0qoDJime1\nqNms6d36TnnoD6c0qhwD/E0eZfuaijcGxarq5BBnk9qsyxud2dmZd3M8jtVV/Env\n4o1rBV9Hao/z7DKbFdqOPNSMJRtY3e+hRjgm1/feaQKBgQDhKtsMRV+Ovkvu/JdA\nHG6We0nJ/kt8czEmbbW61rvUmJbI8hAK+0TERv/mwXaQqmo6JNhCREcx17vIE7Qy\nEzThGv7hYKotrrEXZq9Dje76KmAtk2zeJPXRriRu1rixRNPRwx9F2I+B3+iXaoqx\nsenzNMSy545P0YvssJYQLnKMTwKBgQDFXi3ZxtKCUwqdOvBEsVHeE00mUbqNm+fV\nDUgxFesQ8KkwuFib29NglnbxG3hgCVpA/4BoCsM2EyuZKap3gtoMW/EZqqhb9Hu+\nfwDoiJy3DmHivq6kHeEo6V6uTDxybqgPN+Yc08X+bqflDMYXLkBuJOnE+8O38TtE\n7BROW+EOSQKBgFiXHPH6BXvLAWM4/GVcCmKohUK1C4weYlMlTSACxooBsynCm29G\npyq2aI6oxXZrpjnUL0X7SSuiHp68qeQdzGtYzLlt5+brWX/EheaFXGYO8CJeY7IP\nRqxF4M2/K5GLa++W3qIDb4sAxql0YLdDMbHfrBhbpJFg97WbUJ9zNtxfAoGAdV23\n7lUpQY6YNT+jOXYotOLNcggP473ecvdfArGCA6TZN7uoFab3X+yZ9m7bemCVZymI\n9lXQGAv2VTJNyJvrhoX2LckqLOSJ4ZIsvBrg9op68xdpSvbpuiZsw0FagMIE9mfL\nU0Er8E1lUfPyqD482kLhMN52WJ//GtE4khBZGOECgYEAwD6mhwYdgQq1rujDZF8g\nzr4Ze3hiwoKGsEvybSYjqmsJMqwLWLCe9Wsj2bPWiMJmkpYdiCC+j3Wo6A1bdWy2\nFn/2T9dO35veJwM/HjP7/jMicyVr6S86vhMfzWuqvnQtuB/HAwctH+N4lJ5z0k8w\nn6WFbBEenJv8p5vZQi0NhHg=\n-----END PRIVATE KEY-----\n", "client_email": "myapi-994@primetrade-433011.iam.gserviceaccount.com", "client_id": "104595139129046465243", "auth_uri": "https://accounts.google.com/o/oauth2/auth", "token_uri": "https://oauth2.googleapis.com/token", "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs", "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/myapi-994%40primetrade-433011.iam.gserviceaccount.com", "universe_domain": "googleapis.com" # Add your service account credentials here } cookie_str = "p20t=web.740861259.532251DB15AFA4E2C9D5A7A4AA7EB97E" #cookie_str get by logging into binance csrft = "4f341a1a0b78bfb7ddb0bfc9b093ec06" trade_type= "PERPETUAL" # perpetual or delivery # Authenticate using the service account info creds = Credentials.from_service_account_info(service_account_info, scopes=scopes) client = gspread.authorize(creds) # The ID of the Google Sheet (found in the URL of the sheet) sheet_id = "1I_PuAeWTaRC4OhS5BA5gv0XQCA17VlIjpA1MvOlzVA8" # Open the Google Sheet by sheet ID workbook = client.open_by_key(sheet_id) # Select the specific sheet in the workbook sheet = workbook.worksheet("okxHistory") sheet2 = workbook.worksheet("okxLeaderBoard") sheet3 = workbook.worksheet("Performance") # Extract the data from the Google Sheet into a pandas DataFrame data = sheet.get_all_values() data2 = sheet2.get_all_values() headers = data.pop(0) headers2 = data2.pop(0) df = pd.DataFrame(data, columns=headers) df2 = pd.DataFrame(data2,columns=headers2) def combine_chunks(df): combined_rows = [] # Group by U_IDs grouped = df.groupby('U_IDs') for uid, group in grouped: # Combine chunks for each UID trade_history_combined = ''.join(group['trade_history'].tolist()) # Create a DataFrame for the combined row combined_row = pd.DataFrame({ 'U_IDs': [uid], 'trade_history': [trade_history_combined] }) combined_rows.append(combined_row) # Concatenate all combined rows into a single DataFrame combined_df = pd.concat(combined_rows, ignore_index=True) return combined_df df = df.fillna(value=pd.NA) df = df.where(pd.notnull(df), None) df2 = df2.fillna(value=pd.NA) df2 = df2.where(pd.notnull(df2), None) df = combine_chunks(df) # df2 = combine_chunks(df2) def convert_str_to_list_or_keep(value): if isinstance(value, str): try: return ast.literal_eval(value) except (SyntaxError, ValueError): return value else: return value df = df.apply(lambda col: col.map(convert_str_to_list_or_keep)) df2 = df2.apply(lambda col: col.map(convert_str_to_list_or_keep)) df['positionClosed'] = False uid_input = st.text_input("Enter U_IDs to filter") option = st.radio("Choose an option:", ["Show Position History", "Show Live Positions"]) if df is not None and uid_input: if option == "Show Position History": st.title("Position History Viewer") # Display starting positions with clickable rows st.header("Starting Positions") filtered_df = df[df['U_IDs'] == uid_input].copy() if not filtered_df.empty: trade_list = filtered_df['trade_history'].iloc[0] else: st.write("No data found for the provided U_ID.") unique_lists = [] def get_amounts_from_positions_and_closed_trades(data): # Check if 'Modified' key exists and extract amounts if 'Modified' in data: modified_positions = data['Modified'] # modified_positions = modified_positions[0] if isinstance(modified_positions, dict) and 'amount' in modified_positions: amount = modified_positions.get('amount') if isinstance(amount, (int, float)): # Check if amount is a number amounts =amount # Check if 'ClosedTrades' key exists and extract amounts if 'ClosedTrades' in data: closed_trades = data['ClosedTrades'] closed_trades =closed_trades[0] if isinstance(closed_trades, dict) and 'amount' in closed_trades: amount = closed_trades.get('amount') if isinstance(amount, (int, float)): # Check if amount is a number amounts = amount return amounts def get_symbols_from_positions_and_closed_trades(data): # Check if 'Modified' key exists and extract symbols if 'Modified' in data: modified_positions = data['Modified'] # modified_positions =modified_positions if isinstance(modified_positions, dict) and 'symbol' in modified_positions: symbol = modified_positions['symbol'] # Check if 'ClosedTrades' key exists and extract symbols if 'ClosedTrades' in data: closed_trades = data['ClosedTrades'] closed_trades =closed_trades[0] if isinstance(closed_trades, dict) and 'symbol' in closed_trades: symbol = closed_trades['symbol'] return symbol for i in range(len(trade_list)): if trade_list[i]=="none": continue if not trade_list: # Check if the trade_list is empty st.header("No data found, this may not be in the leaderboard") if start ==False: st.subheader(f"Data is from {datetime.now()}") start =True foundCLosed = False changeInAmount = 0 if 'symbol' in trade_list[i]: symbol = trade_list[i]['symbol'] side ="buy" if trade_list[i]['amount']>0 else "sell" amount = trade_list[i]['usdAmount'] symbol = trade_list[i]['symbol'] trade_list[i]['side'] =side trade_list[i]['changeInAmount'] = changeInAmount trade_list[i]['i'] = i unique_lists.append({"position":trade_list[i]}) trade_list[i] = "none" else: if 'positions' in trade_list[i]: reached = False # Collect necessary data first before modifying the dictionary for k, v in list(trade_list[i].items()): # Convert to a list to avoid modifying during iteration for entry in v: if 'NewPosition' in entry: new_position = entry.get('NewPosition', {}) # Extract symbol and amount symbol = new_position.get('symbol') amount = new_position.get('usdAmount') if start==False: start_time = new_position.get('updateTime') year = start_time[0] month = start_time[1] day = start_time[2] hour =start_time[3] minute =start_time[4] seconds = start_time[5] dt = datetime(year, month, day, hour, minute, seconds) human_readable_format = dt.strftime('%B %d, %Y, %I:%M:%S %p') st.subheader(f"Data from {human_readable_format}") start=True # if start==False: # # start =True side = "buy" if amount > 0 else "sell" new_position['side'] = side new_position['changeInAmount'] = changeInAmount new_position['i'] = i # Update the entry with the modified 'NewPosition' entry['NewPosition'] = new_position # Append the updated trade_list[i] to unique_lists unique_lists.append(trade_list[i]) reached = True # Now safely modify the dictionary after iteration is complete if reached: trade_list[i] = "none" # Now safely modify the dictionary after iteration is complete for j in range(i+1, len(trade_list)): if trade_list[j] == "none": continue if 'positions' in trade_list[j] and isinstance(trade_list[j]['positions'], list): for position in trade_list[j]['positions']: # Check if 'Modified' is in the position and is a dict if 'Modified' in position and isinstance(position['Modified'], dict): if start==False: for k,v in position.items(): start_time = v['updateTime'] year = start_time[0] month = start_time[1] day = start_time[2] hour =start_time[3] minute =start_time[4] seconds = start_time[5] dt = datetime(year, month, day, hour, minute, seconds) human_readable_format = dt.strftime('%d-%m-%Y %H:%M:%S') st.subheader(f"Data from {human_readable_format}") start=True modified_amount = get_amounts_from_positions_and_closed_trades(position) modified_symbol = get_symbols_from_positions_and_closed_trades(position) if modified_amount > 0: modified_side = "buy" else: modified_side = "sell" if symbol == modified_symbol and side == modified_side: if start ==False: st.header(f"Data is from {datetime.now}") start =True position['Modified']['side'] = modified_side position['Modified']['changeInAmount'] = amount - modified_amount if modified_amount < 0 else modified_amount - amount position['Modified']['i'] = i amount = modified_amount unique_lists.append(trade_list[j]) trade_list[j] = "none" # Check if 'ClosedTrades' is in the position and is a tuple if 'ClosedTrades' in position and isinstance(position['ClosedTrades'], tuple): if start ==False: st.header(f"Data is from {datetime.now}") start =True foundCLosed = False closed_trades_tuple = position['ClosedTrades'] closed_trades_dict = { 'trade_info': closed_trades_tuple[0], 'side': closed_trades_tuple[1] } closed_amount = get_amounts_from_positions_and_closed_trades(position) closed_symbol = get_symbols_from_positions_and_closed_trades(position) if closed_amount > 0: closed_side = "buy" else: closed_side = "sell" if symbol == closed_symbol and side == closed_side: if start==False: for k,v in position.items(): start_time = v['updateTime'] start =True closed_trades_dict['side'] = closed_side trade_info = closed_trades_dict['trade_info'] trade_info['changeInAmount'] = amount - closed_amount if closed_amount < 0 else closed_amount - amount amount = closed_amount closed_trades_dict['trade_info']['i'] = i # Store index 'i' inside 'ClosedTrades' closed_trades_dict['trade_info']['closed'] = True # Append the updated trade_list[j] to unique_lists unique_lists.append(trade_list[j]) trade_list[j] = "none" foundCLosed = True break # Break the inner loop if a closed trade was found if foundCLosed: break for k in range(len(unique_lists)): data = unique_lists[k] if k ==0: if 'positions' in data: if isinstance(data['positions'], list): for a in data['positions']: if 'NewPosition' in a: position_data = a['NewPosition'] starting_position.append(position_data) tradeHistory_positions.append(position_data) else: if 'position' in data: position_data =data['position'] starting_position.append(position_data) tradeHistory_positions.append(position_data) if 'positions' in data: if isinstance(data['positions'],list): for a in data['positions']: if 'ClosedTrades' in a: position_data = a['ClosedTrades'][0] tradeHistory_positions.append(position_data) if 'positions' in data: if isinstance(data['positions'],list): for a in data['positions']: if 'Modified' in a: position_data = a['Modified'] tradeHistory_positions.append(position_data) unique_lists =[] elif option == "Show Live Positions": filtered_df2 = df2[df2['U_IDs'] == uid_input] if not filtered_df2.empty: positions_list = filtered_df2['Positions'].iloc[0] # Extract the first match # Convert the list of dictionaries to a DataFrame if isinstance(positions_list, list) and positions_list: positions_df = pd.DataFrame(positions_list) st.subheader("Live Positions") st.dataframe(positions_df) else: st.write("No live positions data available for the given U_ID.") data3 = sheet3.get_all_values() headers3 = data3.pop(0) df3 = pd.DataFrame(data3, columns=headers3) filtered_df3 = df3[df3['U_IDs'] == uid_input] st.subheader("Performace") st.dataframe(filtered_df3) def show_position_history(selected_position): st.header(f"History for {selected_position}") # Filter trade history for the selected position position_history = [pos for pos in tradeHistory_positions if pos['i'] == selected_position] if position_history: df_history = pd.DataFrame(position_history) # Update the global timestamp with the last update from history # Create a transformed DataFrame for display df_transformed = pd.DataFrame({ 'Pair/Asset': df_history['symbol'], 'is long': df_history['side'], 'Current size after change': df_history['usdAmount'], 'Change in size in Asset': df_history['changeInAmount'], 'Change in size in USDT': df_history['changeInAmount'] * -(df_history['markPrice']), 'Entry price': df_history['entryPrice'], 'Exit price': df_history['markPrice'], 'pnl in usdt': df_history['pnl'], 'pnl in %': df_history['roe'], 'Leverage': df_history['leverage'], 'updatedTime': df_history['updateTime'] }) if 'closed' in df_history.columns: df_transformed['Position closed'] = df_history['closed'] st.dataframe(df_transformed) # Add the update timestamp to the transformed DataFrame else: st.write("No history found for this position.") def lastUpdated(selected_position): position_history = [pos for pos in tradeHistory_positions if pos['i'] == selected_position] return position_history[-1]['updateTime'] def isClosed(selected_position): # Filter trade history for the selected position position_history = [pos for pos in tradeHistory_positions if pos['i'] == selected_position] # Check if there are any records for the selected position if not position_history: return False # Get the most recent entry for the selected position last_entry = position_history[-1] # Check if the 'closed' key exists and if it indicates the position is closed return last_entry.get('closed', False) def main(): df_starting = pd.DataFrame(starting_position) for index, row in df_starting.iterrows(): side = True if row['usdAmount'] > 0 else False is_closed = isClosed(row['i']) # Generate a unique key for the button button_key = f"position_{row['i']}" # Display a button for each trade position if st.button( f"{row['symbol']} : Long: {side}, Entry Price: {row['entryPrice']}, " f"Market Price: {row['markPrice']}, Amount: {row['usdAmount']}, " f"Leverage: {row['leverage']}, TradeTakenAt: {row['updateTime']}, " f"lastUpdated: {lastUpdated(row['i'])}, isClosed: {is_closed}", key=button_key ): show_position_history(row['i']) if __name__ == "__main__": main()