# -*- coding: utf-8 -*- import asyncio from pymongo import MongoClient import aiohttp import pandas as pd import requests import json import urllib.parse import gradio as gr import datetime import os wallets = [ "J7QAjhEGTAx71RoS1Nnuz4aKqK866xEnRXky4zhPa2WG", "9huTEYifjBMJVhPWG3dX84S1Pr6pgXBH9i7274dpPeMr", "CoA4vLyykjYobxEsDWHEx2Rna9WribGQMxSxVkH5NwN6", "BpaD3QF9Z2YtUqPRxpTn7dgC2sBzpzxro5zrEdwhwNd9", "FWmhs5vdUSMHUE5sHPQmd1hBEQVWD8wyCPe9op3KwTNg", "HW1qacccywvtEmUK6mDWfaJfxcM1R64T8BmhfjmxA6Nx", "3QpWHc77Vze5uiqm6WDfxYawKUj1bVbKEegS3YYBLGvL", "5FyNV778E1SZ4ZBwUXCgbx43zrJFdMy4UgUysSw3yocU", "4L3wPZc8smLYQbnpMSxHCiRB5cojbtuc33NkTNsrtdug", "MruHB1owAkBQwtSkzTqTDpqZEDPmz4iUWz3oHCYHKWV" ] #SCRAPE_DO_TOKEN = os.getenv('SCRAPE_DO_KEY') #client = MongoClient(os.getenv('MONGO_DB_URI')) SCRAPE_DO_TOKEN = "c3cb4da35304433483052fb6ba0c7011fef2ff42d62" client = MongoClient("mongodb+srv://djamaal:FHmV2N733lzrLkWf@test-cluster.mys4n.mongodb.net/") db = client["billy_balances"] token_collection = db["turnstile-tokens"] turnstile_token = token_collection.find_one(sort=[("timestamp", -1)])['x-turnstile-token'] common_headers = { "accept": "application/json", "accept-encoding": "identity", "accept-language": "en-GB,en-US;q=0.9,en;q=0.8", "authorization": "Bearer CGtF4EdvDbBpwUXmZSKW3HsYkajy7e", "content-type": "application/json", "origin": "https://portfolio.jup.ag", "referer": "https://portfolio.jup.ag/", "user-agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.0.0 Safari/537.36", "x-turnstile-token": turnstile_token # keep this updated } async def fetch_portfolio(session, wallet_address, results): base_url = f"https://portfolio-api-jup.sonar.watch/v1/portfolio/fetch?address={wallet_address}&addressSystem=solana" encoded_url = urllib.parse.quote(base_url) proxy_url = f"http://api.scrape.do/?token={SCRAPE_DO_TOKEN}&url={encoded_url}&forwardHeaders=True&super=True" try: async with session.get(proxy_url, headers=common_headers) as resp: text = await resp.json() if resp.status == 200: print(f"✅ Wallet {wallet_address[:6]}...: success") results.append({"wallet": wallet_address,"data": text}) else: print(f"❌ Wallet {wallet_address[:6]}...: HTTP {resp.status}") results.append({"wallet": wallet_address, "data": None}) except Exception as e: print(f"⚠️ Error fetching {wallet_address}: {e}") results.append({"wallet": wallet_address, "status": "error", "error": str(e)}) async def get_data(wallet_addresses): results = [] async with aiohttp.ClientSession() as session: tasks = [fetch_portfolio(session, address, results) for address in wallet_addresses] await asyncio.gather(*tasks) return results #Have a button function which does all this async def process_data(): portfolios = await get_data(wallets) df = pd.DataFrame(portfolios) token_rows = [] for index, row in df.iterrows(): data = row['data'] # Token info mapping token_info = data.get('tokenInfo', {}).get('solana', {}) # Navigate to assets list elements = data.get('elements', []) for element in elements: if element.get('type') == 'multiple': assets = element.get('data', {}).get('assets', []) for asset in assets: asset_data = asset.get('data', {}) address = asset_data.get('address') amount = asset_data.get('amount') value = asset.get('value') # Get symbol using address symbol = token_info.get(address, {}).get('symbol', 'UNKNOWN') token_rows.append({ 'original_row': index, 'address': address, 'symbol': symbol, 'amount': amount, 'value': value }) # Final DataFrame tokens_df = pd.DataFrame(token_rows) tokens_df['original_row'] = tokens_df['original_row'].apply(lambda x: df.loc[x, 'wallet']) tokens_df = tokens_df.dropna() # Pivot for values value_pivot = tokens_df.pivot(index='original_row', columns='symbol', values='value') value_pivot.columns = [f'{col}_value' for col in value_pivot.columns] # Pivot for amounts amount_pivot = tokens_df.pivot(index='original_row', columns='symbol', values='amount') amount_pivot.columns = [f'{col}_amount' for col in amount_pivot.columns] # Combine both final_df = pd.concat([value_pivot, amount_pivot], axis=1).reset_index() # Optional: Fill NaNs with 0 final_df = final_df.fillna(0) rows = [] for idx, row in df.iterrows(): data = row['data'] original_row = row['wallet'] # or whatever uniquely identifies the row for element in data.get("elements", []): if element.get("platformId") == "jupiter-exchange": input_token = element.get("data", {}).get("assets", {}).get("input", {}) if input_token: token_data = input_token.get("data", {}) address = token_data.get("address") value = input_token.get("value") amount = token_data.get("amount") symbol = token_info.get(address, {}).get("symbol", address) rows.append({ "original_row": original_row, "address": address, "symbol": symbol, "amount": amount, "value": value }) # Convert to DataFrame jup_tokens_df = pd.DataFrame(rows) jup_tokens_df['symbol'] = jup_tokens_df['symbol'].apply(lambda x: "SOL" if x == "So11111111111111111111111111111111111111112" else x) # Pivot # Group by original_row and symbol, sum value and amount tokens_grouped = jup_tokens_df.groupby(['original_row', 'symbol']).agg({ 'value': 'sum', 'amount': 'sum' }).reset_index() # Pivot value_pivot = tokens_grouped.pivot(index='original_row', columns='symbol', values='value') value_pivot.columns = [f'{col}_value' for col in value_pivot.columns] amount_pivot = tokens_grouped.pivot(index='original_row', columns='symbol', values='amount') amount_pivot.columns = [f'{col}_amount' for col in amount_pivot.columns] jup_df = pd.concat([value_pivot, amount_pivot], axis=1).reset_index().fillna(0) if "9Rhbn9G5poLvgnFzuYBtJgbzmiipNra35QpnUek9virt_value" in jup_df.columns: jup_df = jup_df.rename(columns={"9Rhbn9G5poLvgnFzuYBtJgbzmiipNra35QpnUek9virt_value":"BILLY_value"}) if "9Rhbn9G5poLvgnFzuYBtJgbzmiipNra35QpnUek9virt_amount" in jup_df.columns: jup_df = jup_df.rename(columns={"9Rhbn9G5poLvgnFzuYBtJgbzmiipNra35QpnUek9virt_amount":"BILLY_amount"}) final_df1 = final_df.set_index('original_row') final_df2 = jup_df.set_index('original_row') # Combine the two DataFrames, adding values where they overlap combined_df = final_df1.add(final_df2, fill_value=0) # Reset index if you want original_row back as a column combined_df = combined_df.reset_index() # Optional: fill NaNs (if any) with 0 just in case combined_df = combined_df.fillna(0) # Sum all numeric columns except the original_row which is non-numeric totals = combined_df.select_dtypes(include='number').sum() # Add a row with these totals at the bottom of the dataframe totals_row = pd.DataFrame(totals).T totals_row['original_row'] = 'Total' # Append the totals row to the original df df_with_totals = pd.concat([combined_df, totals_row], ignore_index=True) totals = final_df.select_dtypes(include='number').sum() # Add a row with these totals at the bottom of the dataframe totals_row = pd.DataFrame(totals).T totals_row['original_row'] = 'Total' # Append the totals row to the original df final_df = pd.concat([final_df, totals_row], ignore_index=True) totals = jup_df.select_dtypes(include='number').sum() # Add a row with these totals at the bottom of the dataframe totals_row = pd.DataFrame(totals).T totals_row['original_row'] = 'Total' # Append the totals row to the original df jup_df = pd.concat([jup_df, totals_row], ignore_index=True) final_df = final_df.rename(columns={'original_row': ' '}) jup_df = jup_df.rename(columns={'original_row': ' '}) df_with_totals = df_with_totals.rename(columns={'original_row': ' '}) sol_price = 170 billy_price = 0.001845 # Starting balances starting_balances = { " ": ["Total"], "SOL_amount": [344], "SOL_value": [344 * sol_price], "BILLY_amount": [75_000_000], "BILLY_value": [75_000_000 * billy_price] } # Create the DataFrame starting_df = pd.DataFrame(starting_balances) return(starting_df,final_df.tail(1),jup_df.tail(1),df_with_totals.tail(1)) collection = db["balance_data"] def df_to_mongo_safe_dict(df): return {str(k): v for k, v in df.to_dict(orient="index").items()} async def display_data(): df0, df1, df2, df3 = await process_data() def summarize(df): value_cols = [col for col in df.columns if "value" in col.lower()] total_value = df[value_cols].sum(axis=1).values[0] if value_cols else 0 return df, total_value df0, total0 = summarize(df0) df1, total1 = summarize(df1) df2, total2 = summarize(df2) df3, total3 = summarize(df3) pnl = float(total3) - float(total0) # Save to MongoDB record = { "timestamp": datetime.datetime.utcnow(), "df0": df_to_mongo_safe_dict(df0), "total0": float(total0), "df1": df_to_mongo_safe_dict(df1), "total1": float(total1), "df2": df_to_mongo_safe_dict(df2), "total2": float(total2), "df3": df_to_mongo_safe_dict(df3), "total3": float(total3), "pnl": float(pnl) } collection.insert_one(record) return df0, total0, df1, total1, df2, total2, df3, total3, f"PNL : {pnl:,.2f}" def load_from_mongo(): latest = collection.find_one(sort=[("timestamp", -1)]) if latest: df0 = pd.DataFrame(latest["df0"]).T df1 = pd.DataFrame(latest["df1"]).T df2 = pd.DataFrame(latest["df2"]).T df3 = pd.DataFrame(latest["df3"]).T return ( df0, latest["total0"], df1, latest["total1"], df2, latest["total2"], df3, latest["total3"], f"PNL : {latest['pnl']:,.2f}" ) else: empty_df = pd.DataFrame() return empty_df, 0, empty_df, 0, empty_df, 0, empty_df, 0, "PNL : 0.00" with gr.Blocks() as demo: gr.Markdown("## BILLY Balances and PnL") df0_out = gr.Dataframe(label="Starting Balances") txt0_out = gr.Textbox(label="Starting Value Total") df1_out = gr.Dataframe(label="Wallet Balances") txt1_out = gr.Textbox(label="Wallet Value Total") df2_out = gr.Dataframe(label="JUP Limit Balances") txt2_out = gr.Textbox(label="JUP Limit Value Total") df3_out = gr.Dataframe(label="Total Balances") txt3_out = gr.Textbox(label="Current Value Total") pnl_out = gr.Textbox(label="PNL (AUM Model)") # Load from MongoDB on app load (sync function) demo.load( fn=load_from_mongo, inputs=[], outputs=[ df0_out, txt0_out, df1_out, txt1_out, df2_out, txt2_out, df3_out, txt3_out, pnl_out ] ) demo.launch(debug=True, share=True)