import os
import logging
from datetime import datetime, time as dt_time
from dotenv import load_dotenv
from Dhan_Tradehull import Tradehull
import pandas as pd
import time
import xlwings as xw
import ssl
Bypass SSL certificate verification (not recommended for production)
ssl._create_default_https_context = ssl._create_unverified_context
Logging setup
logging.basicConfig(level=logging.INFO)
Load environment variables
load_dotenv()
API_KEY = os.getenv(“API_KEY”, “1106293837”)
ACCESS_TOKEN = os.getenv(“ACCESS_TOKEN”,“eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzUxMiJ9.eyJpc3MiOiJkaGFuIiwicGFydG5lcklkIjoiIiwiZXhwIjoxNzU2MzU5MjY4LCJ0b2tlbkNvbnN1bWVyVHlwZSI6IlNFTEYiLCJ3ZWJob29rVXJsIjoiIiwiZGhhbkNsaWVudElkIjoiMTEwNjI5MzgzNyJ9.GdAiqn1kjBGRt-Va8WoyGcaBQtrpWJwR3uNAn0YloZqITW1Y3mJfbIVKOyf-elVgW9InroFkozhy_PnyU-Dgag”)
Initialize Tradehull
tsl = Tradehull(API_KEY, ACCESS_TOKEN)
Enrichment Function
def enrich_data(df):
required_cols = {‘open’,‘high’, ‘low’, ‘close’, ‘volume’}
if not required_cols.issubset(df.columns):
logging.warning(f"Missing columns: {required_cols - set(df.columns)}")
return pd.DataFrame()
if 'timestamp' not in df.columns:
df['timestamp'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
df['Avg Price'] = (df['open'] + df['close']) / 2
df['Money Flow'] = df['Avg Price'] * df['volume']
df['ROC'] = df['close'] - df['open']
df['NT_direction'] = df['close'].diff().apply(lambda x: '+value' if x > 0 else '-value' if x < 0 else '0')
df['NT_ratio'] = df['Avg Price'] / df['volume']
net_mf = []
prev_avg = None
for _, row in df.iterrows():
avg, mf = row['Avg Price'], row['Money Flow']
if pd.isna(avg) or prev_avg is None:
net_mf.append(None)
else:
if avg > prev_avg:
net_mf.append(abs(mf))
elif avg < prev_avg:
net_mf.append(-abs(mf))
else:
net_mf.append(mf if mf != 0 else 0)
prev_avg = avg
df['Net MF'] = net_mf
return df[['timestamp', 'open', 'high', 'low', 'close', 'volume', 'ROC', 'Avg Price', 'Money Flow', 'Net MF', 'NT_direction', 'NT_ratio']]
Excel formatting
def apply_net_mf_formatting(sheet, start_row, end_row, col_name):
header = sheet.range(f"A2{start_row}:Z2{start_row}").value
if col_name in header:
col_index = header.index(col_name) + 1
for row in range(start_row + 1, end_row + 1):
cell = sheet.range((row, col_index))
try:
val = float(cell.value)
if val > 0:
cell.color = (0, 255, 0)
cell.font.color = (0, 0, 0)
elif val < 0:
cell.color = (255, 0, 0)
cell.font.color = (255, 255, 255)
else:
cell.color = (255, 192, 203)
cell.font.color = (0, 0, 0)
except:
cell.color = (255, 255, 0)
cell.font.color = (0, 0, 0)
def apply_conditional_formatting(sheet, start_cell, end_cell):
for cell in sheet.range(f"{start_cell}:{end_cell}"):
if isinstance(cell.value, str):
if “+value” in cell.value:
cell.color = (0, 255, 0)
cell.font.color = (0, 0, 0)
elif “-value” in cell.value:
cell.color = (255, 0, 0)
cell.font.color = (255, 255, 255)
Main Loop
while True:
now = datetime.now().time()
if now < dt_time(9, 15) or now > dt_time(15, 30):
logging.info(“Market closed. Waiting…”)
time.sleep(300)
continue
try:
# Fetch data
NIFTY_FUTURE = pd.DataFrame(tsl.get_intraday_data('NIFTY JUL FUT', exchange='NFO', timeframe=5))
nifty_cash_data = pd.DataFrame(tsl.get_intraday_data('NIFTY', exchange='INDEX', timeframe=5))
call_24 = pd.DataFrame(tsl.get_intraday_data('NIFTY 31 JUL 25300 CALL', exchange='NFO', timeframe=5))
put_24 = pd.DataFrame(tsl.get_intraday_data('NIFTY 31 JUL 25000 PUT', exchange='NFO', timeframe=5))
call_31 = pd.DataFrame(tsl.get_intraday_data('NIFTY 31 JUL 25200 CALL', exchange='NFO', timeframe=5))
put_31 = pd.DataFrame(tsl.get_intraday_data('NIFTY 31 JUL 25100 PUT', exchange='NFO', timeframe=5))
call_24800 = pd.DataFrame(tsl.get_intraday_data('NIFTY 31 JUL 24800 CALL', exchange='NFO', timeframe=5))
put_24800 = pd.DataFrame(tsl.get_intraday_data('NIFTY 31 JUL 24800 PUT', exchange='NFO', timeframe=5))
option_chain = tsl.get_option_chain(Underlying="NIFTY", exchange="INDEX", expiry=0, num_strikes=2)
if option_chain is not None:
logging.info("Option chain fetched successfully")
option_chain = pd.DataFrame(option_chain)
else:
logging.warning("Option chain returned None")
option_chain =()
except Exception as e:
logging.error(f"Data fetch error: {e}")
time.sleep(250)
continue
# Clean headers
for df in [NIFTY_FUTURE, nifty_cash_data, call_24, put_24, call_31, put_31, call_24800, put_24800, option_chain]:
if isinstance(df, pd.DataFrame):
df.columns = [str(col).strip().lower().replace(' ', '_') for col in df.columns]
# Tail
data_sets = [NIFTY_FUTURE, nifty_cash_data, call_24, put_24, call_31, put_31, call_24800, put_24800]
data_sets = [df.tail(6) for df in data_sets]
# Enrich
enriched = [enrich_data(df) for df in data_sets]
enriched_future, enriched_cash, e_call24, e_put24, e_call31, e_put31, e_call24800, e_put24800 = enriched
enriched_chain = enrich_data(option_chain) if not option_chain.empty else pd.DataFrame()
# Excel
wb = xw.Book('nifty_data_ACC.xlsx')
stock = wb.sheets['STOCK']
chain_sheet = wb.sheets['option_chain']
# option_chain.range('A1').options(index=False).value = option_chain
stock.range('M2').options(index=False).value = enriched_future
stock.range('M11').options(index=False).value = enriched_cash
stock.range('A2').options(index=False).value = e_call24
stock.range('Y2').options(index=False).value = e_put24
stock.range('A11').options(index=False).value = e_call31
stock.range('Y11').options(index=False).value = e_put31
stock.range('A19').options(index=False).value = e_call24800
stock.range('Y19').options(index=False).value = e_put24800
if not enriched_chain.empty:
chain_sheet.range('A1').options(index=False).value = enriched_chain
else:
logging.warning("Option chain DataFrame is empty, not writing to Excel.")
# Headers
stock.range('Q1').value = 'NIFTY FUTURE'
stock.range('Q10').value = 'NIFTY CASH'
stock.range('B10').value = 'NIFTY CALL_25200 31 JUL'
stock.range('B1').value = 'NIFTY CALL_25300 31 JUL'
stock.range('Z1').value = 'NIFTY PUT_25000 31 JUL'
stock.range('Z10').value = 'NIFTY PUT_25100 31 JUL'
stock.range('B18').value = 'NIFTY CALL_24800 31 JUL'
stock.range('Z18').value = 'NIFTY PUT_22800 31 JUL'
# Format
stock.range('A1:AJ1').color = (0, 0, 0)
stock.range('A1:AJ1').font.color = (255, 255, 255)
stock.range('A1:AJ1').font.bold = True
stock.range('A1:AJ1').font.size = 14
stock.range('A10:AJ10').color = (255, 255, 0)
stock.range('A10:AJ10').font.color = (0, 0, 0)
stock.range('A10:AJ10').font.bold = True
stock.range('A10:AJ10').font.size = 14
stock.range('A18:AJ18').color = (255, 255, 0)
stock.range('A18:AJ18').font.color = (0, 0, 0)
stock.range('A18:AJ18').font.bold = True
stock.range('A18:AJ18').font.size = 14
stock.range('M1:M41').color = (0, 0, 0)
stock.range('Y1:Y41').color = (0, 0, 0)
# Correlation
stock.range('G1').formula = "=CORREL(P3:P5,E3:E5)"
stock.range('F1').formula = "=CORREL(P6:P8,E6:E8)"
stock.range('AD1').formula = "=CORREL(P3:P5,AA3:AA5)"
stock.range('AE1').formula = "=CORREL(P6:P8,AA6:AA8)"
stock.range('F10').formula = "=CORREL(P3:P5,E12:E14)"
stock.range('G10').formula = "=CORREL(P6:P8,E15:E17)"
stock.range('Z10').formula = "=CORREL(P3:P5,AA12:AA14)"
stock.range('AA10').formula = "=CORREL(P6:P8,AA15:AA17)"
# Formatting
apply_net_mf_formatting(stock, 1, len(enriched_future)+1, 'Net MF')
apply_conditional_formatting(stock, "J2", f"J{len(enriched_future)+1}")
apply_net_mf_formatting(stock, 1, len(enriched_cash)+1, 'Net MF')
apply_conditional_formatting(stock, "S2", f"S{len(enriched_cash)+1}")
logging.info("Cycle complete. Waiting for next fetch...")
time.sleep(300)
nifty_50_symbols = [
"ETERNAL", "DRREDDY", "TATAMOTORS", "TATACONSUM", "CIPLA", "GRASIM", "JIOFIN", "EICHERMOT",
"SUNPHARMA", "TATASTEEL", "HINDALCO", "SBILIFE", "WIPRO", "JSWSTEEL", "TITAN", "HEROMOTOCO",
"APOLLOHOSP", "ONGC", "LT", "POWERGRID", "ADANIENT", "BHARTIARTL", "INDUSINDBK", "BEL", "MARUTI",
"SBIN", "ICICIBANK", "ADANIPORTS", "HDFCLIFE", "HDFCBANK", "HINDUNILVR", "M&M", "AXISBANK",
"ULTRACEMCO", "ASIANPAINT", "TCS", "ITC", "NTPC", "BAJFINANCE", "KOTAKBANK", "HCLTECH",
"BAJAJ-AUTO", "COALINDIA", "INFY", "BAJAJFINSV", "RELIANCE", "SHRIRAMFIN", "TECHM", "TRENT", "NESTLEIND"
]
# Fetch, enrich, and write Nifty 50 data
nifty_data_list = []
for symbol in nifty_50_symbols:
try:
df = pd.DataFrame(tsl.get_intraday_data(symbol, exchange='NSE', timeframe=5))
df.columns = [str(col).strip().lower().replace(' ', '_') for col in df.columns]
enriched_df = enrich_data(df.tail(6))
enriched_df.insert(0, 'symbol', symbol)
nifty_data_list.append(enriched_df)
except Exception as e:
logging.warning(f"Error fetching/enriching {symbol}: {e}")
if nifty_data_list:
all_nifty_df = pd.concat(nifty_data_list, ignore_index=True)
nifty_sheet = wb.sheets['nifty']
nifty_sheet.range('A1').options(index=False).value = all_nifty_df
# Fill each stock with a different color
unique_symbols = all_nifty_df['symbol'].unique()
color_palette = [
(255, 235, 205), (220, 220, 220), (255, 228, 225), (240, 255, 255), (255, 250, 205),
(224, 255, 255), (255, 240, 245), (245, 255, 250), (255, 228, 181), (230, 230, 250),
(255, 222, 173), (255, 239, 213), (240, 248, 255), (255, 245, 238), (255, 248, 220),
(250, 240, 230), (255, 250, 240), (240, 255, 240), (255, 228, 196), (255, 255, 224),
(255, 255, 240), (245, 245, 220), (255, 228, 225), (255, 240, 245), (255, 228, 196),
(255, 250, 205), (255, 239, 213), (255, 245, 238), (255, 248, 220), (250, 240, 230),
(255, 250, 240), (240, 255, 240), (255, 228, 196), (255, 255, 224), (255, 255, 240),
(245, 245, 220), (255, 228, 225), (255, 240, 245), (255, 228, 196), (255, 250, 205),
(255, 239, 213), (255, 245, 238), (255, 248, 220), (250, 240, 230), (255, 250, 240),
(240, 255, 240), (255, 228, 196), (255, 255, 224), (255, 255, 240)
]
row_start = 2 # Assuming header is at row 1
for idx, symbol in enumerate(unique_symbols):
color = color_palette[idx % len(color_palette)]
symbol_rows = all_nifty_df[all_nifty_df['symbol'] == symbol].index
for r in symbol_rows:
nifty_sheet.range(f"A{row_start + r}:L{row_start + r}").color = color
else:
logging.warning("No Nifty 50 data to write.")
i am noy getting option _chain data
strong text