Python Trader's Excel Based Trade Terminal For Zerodha And Finvasia [For FREE]
# -*- coding: utf-8 -*-
"""
Created on Fri Oct 14 09:13:23 2022

This tool is to fetch two option chain (of any segment/symbol) data and trade terminal sheet, where all input comes using web socket data which leads to low lag and no api restriction. 

Contact details :
Telegram Channel:  https://t.me/pythontrader
Developer Telegram ID : https://t.me/pythontrader_admin
Gmail ID:   mnkumar2020@gmail.com 
Whatsapp : 9470669031 
Youtube : youtube.com/@pythontraders

Disclaimer: The information provided by the Python Traders channel is for educational purposes only, so please contact your financial adviser before placing your trade. Developer is not responsible for any profit/loss happened due to coding, logical or any type of error.


Toots Feature :
1. Trade terminal where you can take paper/real trade.
2. You can set target/sl together using tool.
3. You can also trail your sl.
4. Tool has voice and telegram notification/alert feature.
5. Tools have orderbook sheet where you can cancel any open orders.
6. Tools have openposition sheet where you can exit any/all running trade.
7. Tools has two option chain and each option chain contains live data with option greeks.
"""

Market_Safety = 1

import warnings
warnings.filterwarnings("ignore")

from NorenRestApiPy.NorenApi import NorenApi
import os
import time
import json
import sys
import platform
from datetime import datetime as dt, timedelta, time, date
from time import sleep
import logging
from threading import Thread

try:
    import pandas as pd
except (ModuleNotFoundError, ImportError):
    print("pandas module not found")
    os.system(f"{sys.executable} -m pip install -U pandas")
finally:
    import pandas as pd
        
try:
    import pyotp
except (ModuleNotFoundError, ImportError):
    print("pyotp module not found")
    os.system(f"{sys.executable} -m pip install -U pyotp")
finally:
    import pyotp
    
try:
    import xlwings as xw
except (ModuleNotFoundError, ImportError):
    print("xlwings module not found")
    os.system(f"{sys.executable} -m pip install -U xlwings")
finally:
    import xlwings as xw

try:
    import pyttsx3
except (ModuleNotFoundError, ImportError):
    #print("pyttsx3 module not found")
    os.system(f"{sys.executable} -m pip install -U pyttsx3")
finally:
    import pyttsx3

try:
    import requests
except (ModuleNotFoundError, ImportError):
    print("requests module not found")
    os.system(f"{sys.executable} -m pip install -U requests")
finally:
    import requests
    
try:
    from GetIVGreeks import DayCountType, ExpType, TryMatchWith, CalcIvGreeks
except ImportError:
    exec(
        __import__("requests")
        .get(
            "https://gist.githubusercontent.com/ShabbirHasan1"
            + "/7695687d87053c7e3df46810ab2e3046"
            + "/raw/60b2baf44b4801dd91ba583c85076f2605783a4b"
            + "/GetIVGreeks.py"
        )
        .text
    )


print("HI welcome")
    
#logging.basicConfig(level=logging.DEBUG)

df_ins_NFO = pd.DataFrame()

OptionChain_template = []
subs_lst = []
subs_pending_lst = []

Indices_To_check_instrument_Sheet = ['NIFTY','BANKNIFTY']
IndexList = ["NIFTY", "BANKNIFTY", "FINNIFTY",'INDIAVIX','MIDCPNIFTY']
Token_list = {'NIFTY':26000,'MIDCPNIFTY':26000,'BANKNIFTY':26009,'FINNIFTY':26037,'INDIAVIX':26017}    

try:
    TerminalSheetName = sys.argv[1]
    if not os.path.exists(TerminalSheetName):
        TerminalSheetName = "Finvasia_Trade_Terminal_v3.xlsm"
except Exception as e:
    TerminalSheetName = "Finvasia_Trade_Terminal_v3.xlsm"
    pass


def Text2Speech(Text):
    #print(Text)
    
    global engine
    try:
        engine = pyttsx3.init()
        voices = engine.getProperty('voices')
        try:
           engine.setProperty('voice', voices[1].id)
        except Exception as e:
            pass
        engine.setProperty('rate', 160)
        engine.say(Text)
        engine.runAndWait()
        del engine
    except Exception as e:
        #print(f"Issue in voice module : {e}")
        pass
    
Text2Speech("Welcome to python trader trade terminal tool, have a nice day. Hope u r enjoying our work")


def Shoonya_login():
    global api
    global TelegramBotCredential, ReceiverTelegramID
    global logger
    global userid, client_name
    client_name = ""
    isConnected = 0
    excel_name = xw.Book(TerminalSheetName)
    Credential_sheet = excel_name.sheets["User_Credential"]
    
    Credential_sheet.range('a1').value = 'Welcome To Python Trader'
    Credential_sheet.range('a1').color = (46,132,198)
    Credential_sheet.range('a14').value = 'Tool developed by PythonTrader, Please follow us on social media site for more tools or freelancing work'
    Credential_sheet.range('a14').color = (46,132,198)
    Credential_sheet.range('b15').value = 'https://www.youtube.com/@pythontraders'
    Credential_sheet.range('b16').value =  'https://www.t.me/pythontrader'
    Credential_sheet.range('b15:b16').color = (220,214,32)
        
    try:
        userid = Credential_sheet.range("B2").value
        
        Timestamp = dt.now().strftime("%d%m%Y_%H%M%S")
        
        subdir = "Logs"

        if not os.path.exists(subdir):
            os.makedirs(subdir)
            
        os_name = platform.system()
        if(os_name == 'Windows'):
            LogFolder = "Logs\\"
        else:
            LogFolder = "Logs/"
            
        LogFile  =  LogFolder + "Finvasia_TT_" + str(userid) + "_" + str(Timestamp)  + str('.log') 

        logging.basicConfig(filename=LogFile, format='%(asctime)s %(message)s', filemode='w')
        logger = logging.getLogger() 
        logger.setLevel(logging.INFO)

        try:
            class ShoonyaApiPy(NorenApi):
                def __init__(self):
                    NorenApi.__init__(self, host='https://api.shoonya.com/NorenWClientTP/', websocket='wss://api.shoonya.com/NorenWSTP/', eodhost='https://api.shoonya.com/chartApi/getdata/')
             
            api = ShoonyaApiPy()
        except Exception as e:
            class ShoonyaApiPy(NorenApi):
                def __init__(self):
                    NorenApi.__init__(self, host='https://api.shoonya.com/NorenWClientTP/', websocket='wss://api.shoonya.com/NorenWSTP/')
            
            api = ShoonyaApiPy()
            pass
                
        
        TelegramBotCredential = str(Credential_sheet.range("b10").value)
        ReceiverTelegramID = str(Credential_sheet.range("b11").value)
        index = ReceiverTelegramID.find(".")
        if index != -1:
            ReceiverTelegramID = ReceiverTelegramID[:len(ReceiverTelegramID)-2]
        
        print(f"TelegramBotCredential= ({TelegramBotCredential}), ReceiverTelegramID=({ReceiverTelegramID})")
        

        password = str(Credential_sheet.range("B3").value)
            
        index = password.find(".")
        if index != -1:
            password = password[:len(password)-2]
        
        LoginMethod = str(Credential_sheet.range("B4").value)
        if (LoginMethod == "New_Session"):
            TotpKey = str(Credential_sheet.range('B5').value)
            index = TotpKey.find(".")
            if index != -1:
                twoFA = int(TotpKey[:6])
            else:
                pin = pyotp.TOTP(TotpKey).now()
                twoFA = f"{int(pin):06d}" if len(pin) <=5 else pin    
                
            vendor_code = Credential_sheet.range("B6").value
            api_secret = Credential_sheet.range("B7").value
            imei = "abcd1234"

            print(
                f"userid={userid},password={password},twoFA={twoFA},vendor_code={vendor_code},api_secret={api_secret}, imei={imei}"
            )
            login_status = api.login(
                userid=userid,
                password=str(password),
                twoFA=str(twoFA),
                vendor_code=vendor_code,
                api_secret=api_secret,
                imei=imei,
            )

            client_name = login_status.get("uname")
            token = login_status.get('susertoken')
            print(login_status)
            Credential_sheet.range("c2").value = "Login Successful, Welcome " + client_name + "\nTool Validity : Demo" + "\nGenerated Token = (" + str(token) + ")"
            isConnected = 1
            Text2Speech("Login Successful, Welcome " + str(client_name) )
            Credential_sheet.range('c2').color = (118,224,280)
        else:
            try:
                ExistingToken = Credential_sheet.range("B8").value
                login_status = api.set_session(userid=userid, password=password,usertoken=ExistingToken)
                get_limits = api.get_limits()
                print(get_limits)
                if(get_limits['stat'] == 'Ok'):
                    Credential_sheet.range("c2").value = "Login Successful \nTool Validity : Demo" + "\nLoggedin using Token = (" + str(ExistingToken) + ")"
                    isConnected = 1
                    Credential_sheet.range('c2').color = (118,224,280)
                    Text2Speech("Login Successful")
                else:
                    Credential_sheet.range("c2").value = "Wrong credential \n" + str(get_limits['emsg']) +"\nPlease login using New_Session"
                    Text2Speech("Login unsuccessful")
                    Credential_sheet.range('c2').color = (255, 0, 0)
            except Exception as e:
                Credential_sheet.range("c2").value = "Wrong credential"
                Text2Speech("Login unsuccessful")
                Credential_sheet.range('c2').color = (255, 0, 0)
                
    except Exception as e:
        print(f"Error : {e}")
        Credential_sheet.range("c2").value = "Wrong credential"
        Text2Speech("Login unsuccessful")
        Credential_sheet.range('c2').color = (255, 0, 0)

    return isConnected

feed_opened = False
SYMBOLDICT = {}
live_data = {}

Telegram_Message = ["Welcome to Python Trader excel based trade terminal","Have a Good Day"]
print(Telegram_Message)
Voice_Message = []
TelegramBotCredential = None
ReceiverTelegramID = None

def SendMessageToTelegram(Message):
    #print(f"SendMessageToTelegram = {Message}")
    global TelegramBotCredential, ReceiverTelegramID
    try:
        Url = "https://api.telegram.org/bot" + str(TelegramBotCredential) +  "/sendMessage?chat_id=" + str(ReceiverTelegramID)
        
        textdata ={ "text":Message}
        response = requests.request("POST",Url,params=textdata)
    except Exception as e:
        #print(f"SendMessageToTelegram exception occur : {e}")
        pass

def event_handler_quote_update(inmessage):
    global live_data

    global SYMBOLDICT
    # e   Exchange
    # tk  Token
    # lp  LTP
    # pc  Percentage change
    # v   volume
    # o   Open price
    # h   High price
    # l   Low price
    # c   Close price
    # ap  Average trade price
    # oi  Open interest

    fields = [
        "ts",
        "lp",
        "pc",
        "c",
        "o",
        "h",
        "l",
        "v",
        "ltq",
        "ltp",
        "bp1",
        "sp1",
        "ap",
        "oi",
        "ap",
        "poi",
        "toi",
    ]

    message = {field: inmessage[field] for field in set(fields) & set(inmessage.keys())}
    # print(message)
    key = inmessage["e"] + "|" + inmessage["tk"]

    if key in SYMBOLDICT:
        symbol_info = SYMBOLDICT[key]
        symbol_info.update(message)
        SYMBOLDICT[key] = symbol_info
        live_data[key] = symbol_info
    else:
        SYMBOLDICT[key] = message
        live_data[key] = message

def event_handler_order_update(tick_data):
    print(f"Order update {tick_data}")

def open_callback():
    global feed_opened
    feed_opened = True
    
    #add below lines if issue facing while websocket distruption
    global subs_lst
    subs_lst = []
    
def event_handler_socket_closed():
    print(f"socket closed, so again trying to reconnect")
    sleep(2)
    
def order_status(orderid):
    #print(f"Checking order_status for ({orderid})")
    AverageExecutedPrice = 0
    status = ''
    try:
        order_book = get_order_book()
        order_book = order_book[order_book['Order No'] == str(orderid)]

        status = order_book.iloc[0]["Status"]
        if status == "COMPLETE":
            AverageExecutedPrice = order_book.iloc[0]["Executed Price"]
    except Exception as e:
        Message = str(e) + " : Exception occur in order_status"
        print(Message)
    return status, AverageExecutedPrice

def place_trade(symbol, quantity, buy_or_sell, order_type = None, price = None):
    global api
    global Product_type
    global logger
    tradingsymbol = symbol[4:]
    exchange = symbol[:3]
    
    if order_type == 'MARKET':
        price = 0
        trigger_price = None
        price_type = "MKT"
    elif order_type == 'LIMIT':
        trigger_price = None
        price_type = "LMT"
    elif order_type == 'SL-M':
        price_type = "SL-LMT"
        trigger_price = price
        
        if buy_or_sell == 'BUY':
            price = price * (100 + Market_Safety) / 100
            if exchange == 'CDS':
                round_to = .0025
                result = ( int(price / round_to) + 1) * round_to
                price = float( "{:.4f}".format(result) )
            else:
                round_to = .1
                result = ( int(price / round_to) + 1) * round_to
                price = float( "{:.1f}".format(result) )
        else:
            price = price * (100 - Market_Safety) / 100
            if exchange == 'CDS':
                round_to = .0025
                result = ( int(price / round_to) ) * round_to
                price = float( "{:.4f}".format(result) )
            else:
                round_to = .1
                result = ( int(price / round_to) ) * round_to
                price = float( "{:.1f}".format(result) )
        
        
    if(Product_type == 'MIS'):
            product = "I"
    else:
    
        if exchange in ["NSE","BSE"]:
            product = "C"
        else:
            product = "M"
    Message = "Order Details : buy_or_sell = " + str(buy_or_sell[0]) + ", product_type = " + str(product)+ ", exchange = " + str(exchange)+ ", tradingsymbol = " + str(tradingsymbol)+ ", quantity = " + str(quantity)+ ", price_type = " + str(price_type)+ ", price = " + str(price)+ ", trigger_price = " + str(trigger_price)
    logger.info(Message)
    
    order_id = api.place_order(
        buy_or_sell=buy_or_sell[0],
        product_type=product,
        exchange=exchange,
        tradingsymbol=tradingsymbol,
        quantity=quantity,
        discloseqty=0,
        price_type=price_type,
        price=price,
        trigger_price=trigger_price,
        retention="DAY",
        remarks="Python_Trader",
    ).get("norenordno")

    Message = "Order placed for " + str (tradingsymbol) + " " + str(quantity) + " quantity " + str(buy_or_sell) + ", ur order id :" + str(order_id)
    print(Message)
    logger.info(Message)
    Telegram_Message.append(Message)
    Voice_Message.append(Message)
    
    print(f"Order id = {order_id}")

    return order_id

def get_order_book():
    try:
        global api
        order_book = api.get_order_book()
        df_order_book = pd.DataFrame(order_book)
        if(len(df_order_book) > 0):
            df_order_book = df_order_book.sort_values(by=["norenordno"]).reset_index(drop=True)
            try:
                df_order_book = df_order_book[['norenordno' ,'status','exch','tsym','prctyp','trantype','qty','fillshares','prc','avgprc','prd','token','ls','remarks','rejreason','trgprc']]
                df_order_book = df_order_book.rename(columns={'norenordno' : 'Order No','exch' : 'Exchange','tsym' : 'Trading Symbol','qty' : 'Quantity','trantype' : 'Transaction Type','prctyp' : 'Product Type','trgprc':'Trigger Price','token' : 'Token','ls' : 'Lot Size','status' : 'Status','remarks' : 'Remarks','rejreason' : 'Order rejection reason','prd':'Order Type','avgprc':'Executed Price','fillshares':'Filled Shares','prc':'Price'})
            except Exception as e:
                try:
                    df_order_book = df_order_book[['norenordno' ,'status','exch','tsym','prctyp','trantype','qty','fillshares','prc','avgprc','prd','token','ls','remarks','trgprc']]
                    df_order_book = df_order_book.rename(columns={'norenordno' : 'Order No','exch' : 'Exchange','tsym' : 'Trading Symbol','qty' : 'Quantity','trantype' : 'Transaction Type','prctyp' : 'Product Type','trgprc':'Trigger Price','token' : 'Token','ls' : 'Lot Size','status' : 'Status','remarks' : 'Remarks','prd':'Order Type','avgprc':'Executed Price','fillshares':'Filled Shares','prc':'Price'})
                except Exception as e:
                    try:
                        df_order_book = df_order_book[['norenordno' ,'status','exch','tsym','prctyp','trantype','qty','prc','prd','token','ls','remarks','avgprc']]
                        df_order_book = df_order_book.rename(columns={'norenordno' : 'Order No','exch' : 'Exchange','tsym' : 'Trading Symbol','qty' : 'Quantity','trantype' : 'Transaction Type','prctyp' : 'Product Type','token' : 'Token','ls' : 'Lot Size','status' : 'Status','remarks' : 'Remarks','prd':'Order Type','prc':'Price','avgprc':'Executed Price'})
                    except Exception as e:
                        df_order_book = df_order_book[['norenordno' ,'status','exch','tsym','prctyp','trantype','qty','prc','prd','token','ls','remarks']]
                        df_order_book = df_order_book.rename(columns={'norenordno' : 'Order No','exch' : 'Exchange','tsym' : 'Trading Symbol','qty' : 'Quantity','trantype' : 'Transaction Type','prctyp' : 'Product Type','token' : 'Token','ls' : 'Lot Size','status' : 'Status','remarks' : 'Remarks','prd':'Order Type','prc':'Price'})
        #print(df_order_book)
    except Exception as e:
        print(f"Exception occur in get_order_book : {e}")

    return df_order_book

def GetToken_UsingSymbol(exchange, tradingsymbol):
    global df_ins_NSE, df_ins_BSE, df_ins_NFO, df_ins_CDS, df_ins_MCX
    global api
    try:
        if exchange == 'NSE':
            df_ins_temp = df_ins_NSE[df_ins_NSE.TradingSymbol == tradingsymbol]
            Token = df_ins_temp.iloc[0]['Token']
        elif exchange == 'BSE':
            df_ins_temp = df_ins_BSE[df_ins_BSE.TradingSymbol == tradingsymbol]
            Token = df_ins_temp.iloc[0]['Token']
        elif exchange == 'NFO':
            df_ins_temp = df_ins_NFO[df_ins_NFO.TradingSymbol == tradingsymbol]
            Token = df_ins_temp.iloc[0]['Token']
        elif exchange == 'CDS':
            df_ins_temp = df_ins_CDS[df_ins_CDS.TradingSymbol == tradingsymbol]
            Token = df_ins_temp.iloc[0]['Token']
        elif exchange == 'MCX':
            df_ins_temp = df_ins_MCX[df_ins_MCX.TradingSymbol == tradingsymbol]
            Token = df_ins_temp.iloc[0]['Token']
        #print(f"Token = {Token}")
    except Exception as e:
        #print(f"Exception : {e}")
        Token = api.searchscrip(exchange=exchange, searchtext=tradingsymbol).get("values")[0].get("token")
        #print(f"latest Token = {Token}")
        pass
    return Token

def subscribe_new_token(exchange, Token):
    global api
    symbol = []
    symbol.append(f"{exchange}|{Token}")
    api.subscribe(symbol)

LimitOrderBook = {}
def start_Trade_Terminal():
    #print("I am inside start_Trade_Terminal")
    global api, live_data
    global SYMBOLDICT
    global Product_type
    global Trade_Mode
    global Telegram_Message, Voice_Message    
    global subs_lst
    excel_TT = xw.Book(TerminalSheetName)
    tt = excel_TT.sheets("Trade_Terminal")
    tt.range("a2:d2").value  = 0
    tt.range("q4:s1000").value  = None
    tt.range("u4:w1000").value  = None
    tt.range("aa4:ac1000").value  = None
    tt.range(f"a3:ac3").value = [ "Symbol", "Open", "High", "Low", "Close", "VWAP", "Best Buy Price",
                                "Best Sell Price","Volume","OI", "LTP","Percentage change", "Qty", "BUY/SELL", "Entry Signal","Entry Limit Price", "Entry Done @","Entry Order ID", "Entry Remarks","Exit Signal","Exit Done @","Exit Order ID","Exit Remarks", "Target","SL" ,"Trail Enable",    "Latest SL","Trade Status","PnL"]
                                
    tt.range('k1').value =  'PYTHON TRADER'
    tt.range('k1').color = (46,132,198)  
    Trade_Mode = str(tt.range('s2').value).upper()
    
    AlertMessage = Trade_Mode + " trade mode enabled"
    Telegram_Message.append(AlertMessage)
    Voice_Message.append(AlertMessage)    
    
    Symbol_Token = {}
    global LimitOrderBook
    #run a parallel thread to update the status
    while True:
        try:
            Product_type = tt.range(f"P2").value
            symbols = tt.range(f"a{4}:a{1000}").value
            trading_info = tt.range(f"m{4}:ac{1000}").value
            main_list = []

            idx = 0
            for i in symbols:
                lst = [None, None, None, None,None, None, None, None, None,None,None]
                if i:
                    if i not in subs_lst:
                        subs_lst.append(i)
                        try:
                            exchange = i[:3]
                            tradingsymbol = i[4:]
                            Token = GetToken_UsingSymbol(exchange, tradingsymbol)
                            Symbol_Token[i] = exchange + "|" + str(Token)
                            subscribe_new_token(exchange, Token)
                            print(f"Symbol = {i}, Token={Token} subscribed")

                        except Exception as e:
                            print(f"Subscribe error {i} : {e}")
                    if i in subs_lst:
                        try:
                            TokenKey = Symbol_Token[i]

                            lst = [
                                live_data[TokenKey].get("o", "-"),
                                live_data[TokenKey].get("h", "-"),
                                live_data[TokenKey].get("l", "-"),
                                live_data[TokenKey].get("c", "-"),
                                live_data[TokenKey].get("ap", "-"),
                                live_data[TokenKey].get("bp1", "-"),
                                live_data[TokenKey].get("sp1", "-"),
                                live_data[TokenKey].get("v", "-"),
                                live_data[TokenKey].get("oi", "-"),
                                live_data[TokenKey].get("lp", "-"),
                                live_data[TokenKey].get("pc", "-"),
                            ]

                            try:
                                trade_info = trading_info[idx]
                                idx_location = idx + 2
                                #print(f" {i} : {trade_info}")
                                if trade_info[0] is not None and trade_info[1] is not None:
                                    if type(trade_info[0]) is float and type(trade_info[1]) is str:
                                        
                                        LTP = float(live_data[TokenKey].get("lp", 0))
                                        
                                        if Trade_Mode == 'REAL':
                                            #Real trade mode handling will handle here
                                            if trade_info[1].upper() == "BUY" and LTP != 0:
                                                if trade_info[2] in ['True_Market' ,'True_Limit_LTP', 'Limit_Below', 'Limit_Above']:
                                                    
                                                    
                                                    #0:Qty    1:BUY/SELL    2:Entry_Signal  3:Entry_Limit_Price  4:Entry_Done@  5:Entry_Order_ID 6: Entry_Remarks  7:Exit_Signal 8:Exit_Done @ 9:Exit_Order_ID  10: Exit_Remarks 11:Target    12:SL  13:Trail Enable 14: Latest_SL 15:Trade_status 16: PnL
                                                    
            
                                                    if trade_info[15] != 'Active' and trade_info[15] != 'Entry_Pending' and trade_info[15] != 'Exit_Pending' and trade_info[15] != 'Closed' and (trade_info[15] is None or trade_info[15] == ''):
                                                        
                                                        
                                                        if trade_info[2] == 'True_Market':
                                                            #Entry buy trade immediately
                                                            Message =  str(i) + " : " +  str(trade_info[0]) + ", " + str(trade_info[1]) + ", " + str(trade_info[2])+ ", " + str(trade_info[3])+ " triggered"
                                                            logger.info(Message)
                                                            order_id = place_trade(i, int(trade_info[0]), "BUY","MARKET")
                                                            
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else:    
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"q{idx_location + 2}").value = None
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                            
                                                        elif trade_info[2] == 'True_Limit_LTP':    
                                                            #Entry buy trade immediately at ltp price
                                                            Message =  str(i) + " : " +  str(trade_info[0]) + ", " + str(trade_info[1]) + ", " + str(trade_info[2])+ ", " + str(trade_info[3])+ " triggered"
                                                            logger.info(Message)
                                                            order_id = place_trade(i, int(trade_info[0]), "BUY","LIMIT",LTP)
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else: 
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"q{idx_location + 2}").value = None
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                            
                                                        elif trade_info[2] == 'Limit_Below' and trade_info[3] is not None:
                                                            #print(f"LTP={LTP}, trade_info[3] ={trade_info[3]}")
                                                            
                                                            #handling real Limit_Below based trade
                                                            Message =  str(i) + " : " +  str(trade_info[0]) + ", " + str(trade_info[1]) + ", " + str(trade_info[2])+ ", " + str(trade_info[3])+ " triggered"
                                                            logger.info(Message)
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "BUY","LIMIT",trade_info[3])
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else: 
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"q{idx_location + 2}").value = None
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                           
                                                            
                                                        elif trade_info[2] == 'Limit_Above' and trade_info[3] is not None:
                                                            #print(f"LTP={LTP}, trade_info[3] ={trade_info[3]}")
                                                            
                                                            #handling real Limit_Above based trade
                                                            Message =  str(i) + " : " +  str(trade_info[0]) + ", " + str(trade_info[1]) + ", " + str(trade_info[2])+ ", " + str(trade_info[3])+ " triggered"
                                                            logger.info(Message)
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "BUY","SL-M",trade_info[3])
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else: 
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"q{idx_location + 2}").value = None
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                            
                                                    elif trade_info[15] == 'Entry_Pending' :
                                                        Order_id = str(int(trade_info[5]))
                                                        #print(f"Order_id={Order_id}")
                                                        status = LimitOrderBook[Order_id]['status']
                                                        if status == 'COMPLETE':
                                                            Executed_price = LimitOrderBook[Order_id]['Executed_price']
                                                            tt.range(f"q{idx_location + 2}").value = Executed_price
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Active'
                                                        
                                                            Message = str(int(trade_info[0])) + " quantity " + str(i[4:]) + " Bought at " + str(Executed_price)
                                                            
                                                            Telegram_Message.append(Message)
                                                            Voice_Message.append(Message)
                        
                        
                                                        tt.range(f"S{idx_location + 2}").value = LimitOrderBook[Order_id]['Remarks']
                                                        
                                                    elif trade_info[15] == 'Active':
                                                        
                                                        
                                                        PnL = (float(LTP) - float(trade_info[4])) * int(trade_info[0])
                                                        tt.range(f"AC{idx_location + 2}").value = PnL
                                                        #print(f"PnL = {PnL}")
                                                        
                                                        TSL = 0
                                                        if type(trade_info[12]) is float:
                                                            if trade_info[13] == True:
                                                                if(LTP >= trade_info[4]):
                                                                    CUR_TSL = LTP - trade_info[4] + trade_info[12]
                                                                else:
                                                                    CUR_TSL = trade_info[12]
                                                                
                                                                OLD_TSL = trade_info[14]
                                                                if OLD_TSL is not None:
                                                                    TSL = max(CUR_TSL,OLD_TSL)
                                                                else:
                                                                    TSL = CUR_TSL 
                                                            else:
                                                                TSL = trade_info[12]
                                                            
                                                            tt.range(f"AA{idx_location + 2}").value = TSL
                                                        
                                                        if trade_info[7] == 'True_Market':
                                                            #exit buy order immediately
                                                            Message =  str(i) + " : " +  str(trade_info[0]) + ", " + str(trade_info[7]) +  " exit triggered"
                                                            logger.info(Message)
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "SELL","MARKET")
                                                            if order_id is None:
                                                                tt.range(f"T{idx_location + 2}").value = None
                                                            else:  
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"v{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Exit_Pending'
                                                                
                                                        elif trade_info[7] == 'True_Limit_LTP':
                                                            #exit buy at ltp limit 
                                                            Message =  str(i) + " : " +  str(trade_info[0]) + ", " + str(trade_info[7]) +  " exit triggered"
                                                            logger.info(Message)
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "SELL","LIMIT",LTP)
                                                            if order_id is None:
                                                                tt.range(f"T{idx_location + 2}").value = None
                                                            else:  
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"v{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Exit_Pending'
                                                            
                                                        elif type(trade_info[11]) is float and trade_info[11] <= LTP:
                                                            #target meets, so exit the buy order
                                                            Message =  str(i) + " : " +  str(trade_info[0]) + ", target meets so exit triggered"
                                                            logger.info(Message)
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "SELL","MARKET")
                                                            
                                                            LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Exit_Pending'
                                                        
                                                        elif TSL >= LTP and type(trade_info[12]) is float:
                                                            #sl meets, so exiting the buy order
                                                            Message =  str(i) + " : " +  str(trade_info[0]) + ", TSL meets so exit triggered"
                                                            logger.info(Message)
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "SELL","MARKET")
                                                            
                                                            LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                            
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Exit_Pending'
                                                            
                                                    
                                                    elif trade_info[15] ==  'Exit_Pending':
                                                        Order_id = str(int(trade_info[9]))
                                                        #print(f"Order_id={Order_id}")
                                                        status = LimitOrderBook[Order_id]['status']
                                                        if status == 'COMPLETE':
                                                            Executed_price = LimitOrderBook[Order_id]['Executed_price']
                                                            tt.range(f"u{idx_location + 2}").value = Executed_price
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Closed'
                                                            
                                                            PnL = (float(Executed_price) - float(trade_info[4])) * int(trade_info[0])
                                                            tt.range(f"AC{idx_location + 2}").value = PnL
                                                            
                                                            Message = str(int(trade_info[0])) + " quantity " + str(i[4:]) + " Sold at " + str(Executed_price) + ", Trade profit or Loss = " + str(round(PnL,2))
                                                            
                                                            Telegram_Message.append(Message)
                                                            Voice_Message.append(Message)
                                                            
                                                        tt.range(f"W{idx_location + 2}").value = LimitOrderBook[Order_id]['Remarks']
                                                        
                                            if trade_info[1].upper() == "SELL" and LTP != 0:
                                                
                                                if trade_info[2] in ['True_Market' , 'True_Limit_LTP' , 'Limit_Below' , 'Limit_Above']:
                                                    #0:Qty    1:BUY/SELL    2:Entry_Signal  3:Entry_Limit_Price  4:Entry_Done@  5:Entry_Order_ID 6: Entry_Remarks  7:Exit_Signal 8:Exit_Done @ 9:Exit_Order_ID   10:Target    11:SL  12:Trail Enable 13: Latest_SL 14:Trade_status 15: PnL
                                                    
                                                    
                                                    if trade_info[15] != 'Active' and trade_info[15] != 'Entry_Pending' and trade_info[15] != 'Exit_Pending' and trade_info[15] != 'Closed' and (trade_info[15] is None or trade_info[15] == '' ):
                                                
                                                        if trade_info[2] == 'True_Market':
                                                            #entry sell order immediately
                                                            
                                                            Message =  str(i) + " : " +  str(trade_info[0]) + ", " + str(trade_info[1]) + ", " + str(trade_info[2])+ ", " + str(trade_info[3])+ " triggered"
                                                            logger.info(Message)
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "SELL","MARKET")
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else: 
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                            
                                                                tt.range(f"q{idx_location + 2}").value = None
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                                #print("b")
                                                            
                                                        elif trade_info[2] == 'True_Limit_LTP':    
                                                            #Entry buy trade immediately at ltp price
                                                            
                                                            Message =  str(i) + " : " +  str(trade_info[0]) + ", " + str(trade_info[1]) + ", " + str(trade_info[2])+ ", " + str(trade_info[3])+ " triggered"
                                                            logger.info(Message)
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "SELL","LIMIT",LTP)
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else: 
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"q{idx_location + 2}").value = None
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                                
                                                        elif trade_info[2] == 'Limit_Above' and trade_info[3] is not None:
                                                            
                                                            #handling real Limit_Above based trade
                                                            Message =  str(i) + " : " +  str(trade_info[0]) + ", " + str(trade_info[1]) + ", " + str(trade_info[2])+ ", " + str(trade_info[3])+ " triggered"
                                                            logger.info(Message)
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "SELL","LIMIT",trade_info[3])
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else: 
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"q{idx_location + 2}").value = None
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                            
                                                                    
                                                        elif trade_info[2] == 'Limit_Below' and trade_info[3] is not None:
                                                           
                                                            #handling real Limit_Above based trade
                                                            Message =  str(i) + " : " +  str(trade_info[0]) + ", " + str(trade_info[1]) + ", " + str(trade_info[2])+ ", " + str(trade_info[3])+ " triggered"
                                                            logger.info(Message)
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "SELL","SL-M",trade_info[3])
                                                            
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else: 
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"q{idx_location + 2}").value = None
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                            
                                                        
                                                    elif trade_info[15] == 'Entry_Pending' :
                                                        Order_id = str(int(trade_info[5]))
                                                        #print(f"Order_id={Order_id}")
                                                        status = LimitOrderBook[Order_id]['status']
                                                        if status == 'COMPLETE':
                                                            Executed_price = LimitOrderBook[Order_id]['Executed_price']
                                                            tt.range(f"q{idx_location + 2}").value = Executed_price
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Active'
                                                            
                                                            Message = str(int(trade_info[0])) + " quantity " + str(i[4:]) + " Sold at " + str(Executed_price) 
                                                            
                                                            Telegram_Message.append(Message)
                                                            Voice_Message.append(Message)
                                                            
                                                        tt.range(f"S{idx_location + 2}").value = LimitOrderBook[Order_id]['Remarks']
                                                        
                                                    elif trade_info[15] == 'Active':
                                                        
                                                        PnL = (float(trade_info[4]) - float(LTP) ) * int(trade_info[0])
                                                        tt.range(f"AC{idx_location + 2}").value = PnL
                                                        
                                                        TSL = 9999999
                                                        
                                                        if type(trade_info[12]) is float:
                                                            if trade_info[13] == True :
                                                                if(LTP <= trade_info[4]):
                                                                    CUR_TSL = trade_info[12] - (trade_info[4] - LTP )
                                                                else:
                                                                    CUR_TSL = trade_info[12]
                                                                
                                                                OLD_TSL = trade_info[14]
                                                                if OLD_TSL is not None:
                                                                    TSL = min(CUR_TSL,OLD_TSL)
                                                                else:
                                                                    TSL = CUR_TSL 
                                                            else:
                                                                TSL = trade_info[12]
                                                            
                                                            tt.range(f"AA{idx_location + 2}").value = TSL
                                                            
                                                        
                                                        if trade_info[7] == 'True_Market':
                                                            #exit sell order immediately
                                                            Message =  str(i) + " : " +  str(trade_info[0]) + ", " + str(trade_info[7]) +  " exit triggered"
                                                            logger.info(Message)
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "BUY","MARKET")
                                                            if order_id is None:
                                                                tt.range(f"T{idx_location + 2}").value = None
                                                            else:  
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                    
                                                                tt.range(f"v{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = "Exit_Pending"
                                                                
                                                        elif trade_info[7] == 'True_Limit_LTP':
                                                            #exit SELL at ltp limit 
                                                            Message =  str(i) + " : " +  str(trade_info[0]) + ", " + str(trade_info[7]) +  " exit triggered"
                                                            logger.info(Message)
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "BUY","LIMIT",LTP)
                                                            
                                                            if order_id is None:
                                                                tt.range(f"T{idx_location + 2}").value = None
                                                            else:  
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"v{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Exit_Pending'    
                                                                
                                                        elif type(trade_info[11]) is float and trade_info[11] >= LTP:
                                                            #target meets, so exiting the sell order
                                                            Message =  str(i) + " : " +  str(trade_info[0]) + ", target meets so exit triggered"
                                                            logger.info(Message)
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "BUY","MARKET")
                                                            
                                                            LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                            
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Exit_Pending'
                                                            
                                                        
                                                        elif TSL <= LTP and type(trade_info[12]) is float:
                                                            #sl hit, so exiting the sell order
                                                            Message =  str(i) + " : " +  str(trade_info[0]) + ", TSL meets so exit triggered"
                                                            logger.info(Message)
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "BUY","MARKET")
                                                            
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else: 
                                                            
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                
                                                                tt.range(f"v{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Exit_Pending'
                                                            
                                                    elif trade_info[15] ==  'Exit_Pending':
                                                        Order_id = str(int(trade_info[9]))
                                                        #print(f"Order_id={Order_id}")
                                                        status = LimitOrderBook[Order_id]['status']
                                                        if status == 'COMPLETE':
                                                            Executed_price = LimitOrderBook[Order_id]['Executed_price']
                                                            tt.range(f"u{idx_location + 2}").value = Executed_price
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Closed'
                                                            
                                                            PnL = (float(trade_info[4]) - float(Executed_price)) * int(trade_info[0])
                                                            tt.range(f"AC{idx_location + 2}").value = PnL
                                                            
                                                            Message = str(int(trade_info[0])) + " quantity " + str(i[4:]) + " buyback at " + str(Executed_price) + ", Trade profit or Loss = " + str(round(PnL,2))
                                                            
                                                            Telegram_Message.append(Message)
                                                            Voice_Message.append(Message)
                                                            
                                                        tt.range(f"W{idx_location + 2}").value = LimitOrderBook[Order_id]['Remarks']
                                        else:
                                            #paper trade mode handling will handle here
                                            if trade_info[1].upper() == "BUY" and LTP != 0:
                                                if trade_info[2]  in ['True_Market' ,'True_Limit_LTP' , 'Limit_Below','Limit_Above']:
                                                    
                                                   
                                                    
                                                    #0:Qty    1:BUY/SELL    2:Entry_Signal  3:Entry_Limit_Price  4:Entry_Done@  5:Entry_Order_ID 6: Entry_Remarks  7:Exit_Signal 8:Exit_Done @ 9:Exit_Order_ID   10:Target    11:SL  12:Trail Enable 13: Latest_SL 14:Trade_status 15: PnL
                                                    
            
                                                    if trade_info[15] != 'Active' and trade_info[15] != 'Exit_Pending' and trade_info[15] != 'Closed' and (trade_info[15] is None or trade_info[15] == ''or trade_info[15] == 'Entry_Pending' ):
                                                        
                                                        
                                                        if trade_info[2] in[ 'True_Market', 'True_Limit_LTP']:
                                                            #Entry buy trade immediately
                                                            order_id = 'PAPER'
                                                            Executed_price = LTP
                                                                
                                                            tt.range(f"q{idx_location + 2}").value = Executed_price
                                                            tt.range(f"r{idx_location + 2}").value = order_id
                                                            tt.range(f"u{idx_location + 2}").value = None
                                                            tt.range(f"v{idx_location + 2}").value = None
                                                            tt.range(f"AA{idx_location + 2}").value = None
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Active'
                                                            tt.range(f"AC{idx_location + 2}").value = None
                                                        
                                                        elif trade_info[2] == 'Limit_Below' and trade_info[3] is not None:
                                                            #print(f"LTP={LTP}, trade_info[3] ={trade_info[3]}")
                                                            
                                                            #handling paper Limit_Below based trade
                                                            if LTP <= trade_info[3]:
                                                                #Entry limit below buy
                                                                
                                                                order_id = 'PAPER'
                                                                Executed_price = LTP
                                                                    
                                                                tt.range(f"q{idx_location + 2}").value = Executed_price
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Active'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None

                                                            elif trade_info[15] != 'Entry_Pending':
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                        
                                                        elif trade_info[2] == 'Limit_Above' and trade_info[3] is not None:
                                                            #print(f"LTP={LTP}, trade_info[3] ={trade_info[3]}")
                                                            
                                                            if LTP >= trade_info[3]:
                                                                #Entry limit above buy
                                                                order_id = 'PAPER'
                                                                Executed_price = LTP
                                                                    
                                                                tt.range(f"q{idx_location + 2}").value = Executed_price
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Active'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None

                                                            elif trade_info[15] != 'Entry_Pending':
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                                                                            
                                                                
                                                    elif trade_info[15] == 'Active' or trade_info[15] == 'Exit_Pending':
                                                        
                                                        
                                                        PnL = (float(LTP) - float(trade_info[4])) * int(trade_info[0])
                                                        tt.range(f"AC{idx_location + 2}").value = PnL
                                                        #print(f"PnL = {PnL}")
                                                        
                                                        TSL = 0
                                                        if type(trade_info[12]) is float:
                                                            if trade_info[13] == True:
                                                                if(LTP >= trade_info[4]):
                                                                    CUR_TSL = LTP - trade_info[4] + trade_info[12]
                                                                else:
                                                                    CUR_TSL = trade_info[12]
                                                                
                                                                OLD_TSL = trade_info[14]
                                                                if OLD_TSL is not None:
                                                                    TSL = max(CUR_TSL,OLD_TSL)
                                                                else:
                                                                    TSL = CUR_TSL 
                                                            else:
                                                                TSL = trade_info[12]
                                                            
                                                            tt.range(f"AA{idx_location + 2}").value = TSL
                                                        
                                                        if trade_info[7] in  ['True_Market','True_Limit_LTP']:
                                                            #exit buy order immediately
                                                            order_id = 'PAPER'
                                                            Executed_price = LTP
                                                                
                                                            tt.range(f"u{idx_location + 2}").value = Executed_price
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Closed'
                                                            
                                                            PnL = (float(Executed_price) - float(trade_info[4])) * int(trade_info[0])
                                                            tt.range(f"AC{idx_location + 2}").value = PnL
                                                        
                                                        
                                                        elif type(trade_info[11]) is float and trade_info[11] <= LTP:
                                                            #target meets, so exit the buy order
                                                            order_id = 'PAPER'
                                                            Executed_price = LTP
                                                                
                                                            tt.range(f"u{idx_location + 2}").value = Executed_price
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Closed'
                                                            
                                                            PnL = (float(Executed_price) - float(trade_info[4])) * int(trade_info[0])
                                                            tt.range(f"AC{idx_location + 2}").value = PnL
                                                        
                                                        elif TSL >= LTP and type(trade_info[12]) is float:
                                                            #sl meets, so exiting the buy order
                                                            order_id = 'PAPER'
                                                            Executed_price = LTP
                                                                
                                                            tt.range(f"u{idx_location + 2}").value = Executed_price
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Closed'
                                                            
                                            if trade_info[1].upper() == "SELL" and LTP != 0:
                                                
                                                if trade_info[2] in ['True_Market','True_Limit_LTP',  'Limit_Below', 'Limit_Above']:
                                                    #0:Qty    1:BUY/SELL    2:Entry_Signal  3:Entry_Limit_Price  4:Entry_Done@  5:Entry_Order_ID 6: Entry_Remarks  7:Exit_Signal 8:Exit_Done @ 9:Exit_Order_ID   10:Target    11:SL  12:Trail Enable 13: Latest_SL 14:Trade_status 15: PnL
                                                    
                                                    
                                                    if trade_info[15] != 'Active' and trade_info[15] != 'Exit_Pending' and trade_info[15] != 'Closed' and (trade_info[15] is None or trade_info[15] == '' or trade_info[15] == 'Entry_Pending'):
                                                
                                                        if trade_info[2] in ['True_Market', 'True_Limit_LTP']:
                                                            #entry sell order immediately
                                                            order_id = 'PAPER'
                                                            Executed_price = LTP
                                                                
                                                            tt.range(f"q{idx_location + 2}").value = Executed_price
                                                            tt.range(f"r{idx_location + 2}").value = order_id
                                                            tt.range(f"u{idx_location + 2}").value = None
                                                            tt.range(f"v{idx_location + 2}").value = None
                                                            tt.range(f"AA{idx_location + 2}").value = None
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Active'
                                                            tt.range(f"AC{idx_location + 2}").value = None
                                                            #print("b")
                                                            
                                                        elif trade_info[2] == 'Limit_Above' and trade_info[3] is not None:
                                                            
                                                            
                                                            if LTP >= trade_info[3]:
                                                                #entry sell order with Limit_Above
                                                                order_id = 'PAPER'
                                                                Executed_price = LTP
                                                                    
                                                                tt.range(f"q{idx_location + 2}").value = Executed_price
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Active'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                                
                                                            elif trade_info[15] != 'Entry_Pending':
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None 
                                                                
                                                        elif trade_info[2] == 'Limit_Below' and trade_info[3] is not None:
                                                            
                                                            
                                                            if LTP <= trade_info[3]:
                                                                #entry sell order with Limit_Below
                                                                
                                                                order_id = 'PAPER'
                                                                Executed_price = LTP
                                                                    
                                                                tt.range(f"q{idx_location + 2}").value = Executed_price
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Active'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                                
                                                            elif trade_info[15] != 'Entry_Pending':
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None    
                                                        
                                                    elif trade_info[15] == 'Active' or trade_info[15] == 'Exit_Pending':
                                                        
                                                        PnL = (float(trade_info[4]) - float(LTP) ) * int(trade_info[0])
                                                        tt.range(f"AC{idx_location + 2}").value = PnL
                                                        
                                                        TSL = 9999999
                                                        
                                                        if type(trade_info[12]) is float:
                                                            if trade_info[13] == True :
                                                                if(LTP <= trade_info[4]):
                                                                    CUR_TSL = trade_info[12] - (trade_info[4] - LTP )
                                                                else:
                                                                    CUR_TSL = trade_info[12]
                                                                
                                                                OLD_TSL = trade_info[14]
                                                                if OLD_TSL is not None:
                                                                    TSL = min(CUR_TSL,OLD_TSL)
                                                                else:
                                                                    TSL = CUR_TSL 
                                                            else:
                                                                TSL = trade_info[12]
                                                            
                                                            tt.range(f"AA{idx_location + 2}").value = TSL
                                                            
                                                        
                                                        if trade_info[7] in  ['True_Market','True_Limit_LTP']:
                                                            #exit sell order immediately
                                                            order_id = 'PAPER'
                                                            Executed_price = LTP
                                                                
                                                            tt.range(f"u{idx_location + 2}").value = Executed_price
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = "Closed"
                                                            
                                                            PnL = (float(trade_info[4]) - float(Executed_price) ) * int(trade_info[0])
                                                            tt.range(f"AC{idx_location + 2}").value = PnL
                                                        
                                                            
                                                        elif type(trade_info[11]) is float and trade_info[11] >= LTP:
                                                            #target meets, so exiting the sell order
                                                            order_id = 'PAPER'
                                                            Executed_price = LTP
                                                                
                                                            tt.range(f"u{idx_location + 2}").value = Executed_price
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Closed'
                                                            
                                                            PnL = (float(trade_info[4]) - float(Executed_price) ) * int(trade_info[0])
                                                            tt.range(f"AC{idx_location + 2}").value = PnL
                                                        
                                                        elif TSL <= LTP and type(trade_info[12]) is float:
                                                            #sl hit, so exiting the sell order
                                                            order_id = 'PAPER'
                                                            Executed_price = LTP    
                                                                
                                                            tt.range(f"u{idx_location + 2}").value = Executed_price
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Closed'
                                                            
                                                            PnL = (float(trade_info[4]) - float(Executed_price) ) * int(trade_info[0])
                                                            tt.range(f"AC{idx_location + 2}").value = PnL
                                
                            except Exception as e:
                                Message = "Exception occur in core order book management:" + str(e)
                                #print(Message)
                                logger.info(Message)
                                pass
                        except Exception as e:
                            #print(f"exception : {str(e)}")
                            pass
                main_list.append(lst)

                idx += 1

            tt.range("b4:l1000").value = main_list
                
        except Exception as e:
            #print(f"Exception : {str(e)}")
            pass

def get_position():
    #print(f"I am inside get_position")
    global api
    try:
        positions = api.get_positions()
        df_positions = pd.DataFrame(positions)
        #df_positions.to_csv("positions.csv")
        #print(df_positions)
        mtm = 0
        pnl = 0
        day_m2m = 0
        if len(df_positions) > 0:
            df_positions = df_positions.sort_values(by=["tsym"]).reset_index(drop=True)
            for i in positions:
                mtm += float(i['urmtom'])
                pnl += float(i['rpnl'])
                day_m2m = mtm + pnl
            #print(f'{day_m2m} is your Daily MTM')
        
            df_positions = df_positions[['exch','tsym','prd','netqty','netavgprc','lp','daybuyqty','daysellqty','daybuyavgprc','daysellavgprc','openbuyqty','opensellqty']]
        
            df_positions = df_positions.rename(columns={'exch':'Exchange' ,'tsym':'Symbol' ,'prd':'Product' ,'netqty':'Net Quantity' ,'netavgprc':'Avg Price' ,'lp':'Last Price' ,'daybuyqty':'Buy Quantity' ,'daysellqty':'Sell Quantity' ,'daybuyavgprc':'Avg Buy Price' ,'daysellavgprc':'Avg. Sell Price' ,'openbuyqty':'Open Buy Quantity' ,'opensellqty':'Open Sell Quantity'})
        
            df_positions['Net Quantity'] = df_positions['Net Quantity'].astype('int')
        
    except Exception as e:
        print(f"Error in get_position: {e}")
    return df_positions,day_m2m 

def LoadInstrument_token(Token_4_Exchange = ['NSE','BSE','NFO','CDS','MCX']):
    global df_ins_NSE, df_ins_BSE, df_ins_NFO, df_ins_CDS, df_ins_MCX
    global api

    try:
        subdir = "Instrument"

        if not os.path.exists(subdir):
            os.makedirs(subdir)
            
        print("Finvasia intrument token download started, may take upto 2-3 minutes ..")
           
        if 'NSE' in Token_4_Exchange:
            #reading nse instrument symbol
            zip_file = "NSE_symbols.txt.zip"
            url = f"https://api.shoonya.com/{zip_file}"
            r = requests.get(f"{url}", allow_redirects=True)
            open(zip_file, "wb").write(r.content)
            df_ins_NSE = pd.read_csv(zip_file)
            os.remove(zip_file)
            try:
                df_ins_NSE.to_csv(os.path.join(subdir,"NSE_symbols.csv"),index = False)
            except:
                pass
                
        if 'BSE' in Token_4_Exchange:        
            #reading BSE instrument symbol
            zip_file = "BSE_symbols.txt.zip"
            url = f"https://api.shoonya.com/{zip_file}"
            r = requests.get(f"{url}", allow_redirects=True)
            open(zip_file, "wb").write(r.content)
            df_ins_BSE = pd.read_csv(zip_file)
            os.remove(zip_file)
            try:
                df_ins_BSE.to_csv(os.path.join(subdir,"BSE_symbols.csv"),index = False)
            except:
                pass
                
        
        if 'NFO' in Token_4_Exchange:
            #reading nfo instrument symbol
            zip_file = "NFO_symbols.txt.zip"
            url = f"https://api.shoonya.com/{zip_file}"
            r = requests.get(f"{url}", allow_redirects=True)
            open(zip_file, "wb").write(r.content)
            df_ins_NFO = pd.read_csv(zip_file)
            df_ins_NFO['Expiry'] = pd.to_datetime(df_ins_NFO['Expiry']).apply(lambda x: x.date())
            df_ins_NFO = df_ins_NFO.sort_values(by=['Expiry',"Symbol",'StrikePrice'], ascending=[True,True,True])
            df_ins_NFO = df_ins_NFO.astype({"StrikePrice": str}) 
            os.remove(zip_file)
            try:
                df_ins_NFO.to_csv(os.path.join(subdir,"NFO_symbols.csv"),index = False)
            except:
                pass
                
        if 'CDS' in Token_4_Exchange:
            #reading CDS instrument symbol
            zip_file = "CDS_symbols.txt.zip"
            url = f"https://api.shoonya.com/{zip_file}"
            r = requests.get(f"{url}", allow_redirects=True)
            open(zip_file, "wb").write(r.content)
            df_ins_CDS = pd.read_csv(zip_file)
            df_ins_CDS['Expiry'] = pd.to_datetime(df_ins_CDS['Expiry']).apply(lambda x: x.date())
            df_ins_CDS = df_ins_CDS.sort_values(by=['Instrument','Expiry',"Symbol",'StrikePrice'], ascending=[False,True,True,True])
            df_ins_CDS = df_ins_CDS.astype({"StrikePrice": str})
            os.remove(zip_file)
            try:
                df_ins_CDS.to_csv(os.path.join(subdir,"CDS_symbols.csv"),index = False)
            except:
                pass
                
        if 'MCX' in Token_4_Exchange:
            #reading MCX instrument symbol
            zip_file = "MCX_symbols.txt.zip"
            url = f"https://api.shoonya.com/{zip_file}"
            r = requests.get(f"{url}", allow_redirects=True)
            open(zip_file, "wb").write(r.content)
            df_ins_MCX = pd.read_csv(zip_file)
            df_ins_MCX['Expiry'] = pd.to_datetime(df_ins_MCX['Expiry']).apply(lambda x: x.date())
            df_ins_MCX = df_ins_MCX.sort_values(by=['Instrument','Expiry',"Symbol",'StrikePrice'], ascending=[False,True,True,True])
            df_ins_MCX = df_ins_MCX.astype({"StrikePrice": str})
            os.remove(zip_file)
            try:
                df_ins_MCX.to_csv(os.path.join(subdir,"MCX_symbols.csv"),index = False)
            except:
                pass
                
        print("Finvasia intrument token download completed")
        
    except Exception as e:
        print(f"Exception in LoadInstrument_token : {e}")
        pass
        
def start_optionchain():
    global subscribe_symbol
    global live_data
    global df_ins_NFO
    global df_ins_NSE, df_ins_BSE, df_ins_NFO, df_ins_CDS, df_ins_MCX
    
    global subs_lst
    excel_name = xw.Book(TerminalSheetName)
    oci = excel_name.sheets("Option_Chain_Input")
    Option_Chain_Output = excel_name.sheets("Option_Chain_Output")
    

    oci.range("F3").value = None
    oci.range("F4").value = None
    oci.range("a2:c500").value = None
    
    Option_Chain_Output.range('a3:ae500').value = None

    IterationSleep = 1
    
    oci.range("d2").value = "Segment==>>"
    oci.range("d3").value, oci.range("d4").value = "Symbol==>>", "Expiry==>>",
    oci.range("d5").value, oci.range("d6").value = "RefreshRate==>>", "NoOfStrike==>>",
    oci.range("d7").value, oci.range("d8").value = "ExpiryType==>>" , "GreekMatch==>>"
    
    pre_selected_segment = pre_selected_symbol = pre_selected_expiry = "" 
    pre_selected_NoOfStrike = 0
    
    while True:
        try:
            selected_segment = oci.range("E2").value
            Exchange = selected_segment[:3]
            if Exchange not in ['NFO','CDS','MCX']:
                Exchange = 'NFO'
            
            oci.range("F2").value = None
            if Exchange == 'NFO':
                df_instrument = df_ins_NFO
            elif Exchange == 'CDS':
                df_instrument = df_ins_CDS
            elif Exchange == 'MCX':
                df_instrument = df_ins_MCX
            else:
                oci.range("F2").value = "Please select correct segment"
            #print(f"pre : pre_selected_segment = {pre_selected_segment}, selected_segment={selected_segment}")
            if pre_selected_segment != selected_segment:
                if Exchange == 'NFO':
                    df_symbol = df_ins_NFO
                if Exchange == 'CDS':
                    df_symbol = df_ins_CDS[df_ins_CDS['Instrument'] == 'UNDCUR']
                if Exchange == 'MCX':
                    df_symbol = df_ins_MCX[df_ins_MCX['Instrument'] == 'OPTFUT']
                    
                df_symbol = df_symbol.drop_duplicates( "Symbol" , keep='first')
                df_symbol =df_symbol[['Symbol']]
                oci.range("a2:c500").value = None
                oci.range("a1").options(index=False, header=True).value = df_symbol
                    
            pre_selected_segment = selected_segment
            #print(f"post : pre_selected_segment = {pre_selected_segment}, selected_segment={selected_segment}")
            
            input_symbol = str(oci.range("E3").value).strip()
            if input_symbol != None:

                df_instrument_temp = df_instrument[(df_instrument.Symbol == input_symbol) & (df_instrument['OptionType'].isin(['CE','PE']))]
                
                if len(df_instrument_temp) > 0:
                    if pre_selected_symbol != input_symbol:
                        df_exp = df_instrument_temp.drop_duplicates( "Expiry" , keep='first')
                        
                        df_exp =df_exp[['Expiry','LotSize']]
                        df_exp.sort_values(by = 'Expiry',inplace = True)
                        oci.range("b2:C100").value = None
                        oci.range("b2").options(index=False, header=False).value = df_exp
                    
                    pre_selected_symbol = input_symbol                    
                    
                    oci.range("F3").value = None
                    
                    expiry_input = oci.range("E4").value
                    
                    expiry_input = expiry_input.date() 

                    #print(f"Option Chain Details: input_symbol={input_symbol},expiry_input={expiry_input}")
                    if expiry_input != None:

                        df_instrument_temp = df_instrument_temp[df_instrument_temp.Expiry == expiry_input]
                        if len(df_instrument_temp) > 0:

                            oci.range("F4").value = None
                            try:
                                IterationSleep = oci.range("E5").value
                                if IterationSleep != None:
                                    IterationSleep = int(IterationSleep)
                                    oci.range("F5").value = None
                            except:
                                IterationSleep = 5
                                Message = "Iteration should be number"
                                print(Message)
                                oci.range("F5").value = Message

                            symbol_in_template = [itr for itr in OptionChain_template if itr["symbol"] ==input_symbol ]
                            if len(symbol_in_template) == 0:
                                #print("Symbol Not found, so append the details")

                                ind_df_specific_symbol = df_instrument[(df_instrument.Symbol == input_symbol) & (df_instrument['OptionType'].isin(['CE','PE']))]
                                #print(ind_df_specific_symbol)

                                List_of_Expiry = ind_df_specific_symbol["Expiry"].unique()
                                #print(List_of_Expiry)

                                Expiry_strikelist_list = []

                                for expiry in List_of_Expiry:
                                    if str( expiry ) != 'NaT' :
                                        #print(expiry)
                                        ind_df_specific_symbol_expiry = ind_df_specific_symbol[(ind_df_specific_symbol.Expiry == expiry) ]
                                        #print(ind_df_specific_symbol_expiry)

                                        List_of_strikes = ind_df_specific_symbol_expiry["StrikePrice"].unique()

                                        LotSize = ind_df_specific_symbol_expiry.iloc[0]["LotSize"]
                                        #print(LotSize)
                                        #print(List_of_strikes)
                                        strike_pe_ce_list = []

                                        for strike in List_of_strikes:
                                            #print(f"\nsymbol:{input_symbol},Expiry:{expiry},strike:{strike}")
                                            ind_df_specific_symbol_expiry_pe = ind_df_specific_symbol_expiry[
                                                (ind_df_specific_symbol_expiry.StrikePrice == str(strike)) & (ind_df_specific_symbol_expiry.OptionType == "PE") ]
                                            if len(ind_df_specific_symbol_expiry_pe) > 0:
                                                pe_token = ind_df_specific_symbol_expiry_pe.iloc[0]["Token"]
                                            else:
                                                pe_token = "NA"

                                            ind_df_specific_symbol_expiry_ce = ind_df_specific_symbol_expiry[(            ind_df_specific_symbol_expiry.StrikePrice== str(strike))& (            ind_df_specific_symbol_expiry.OptionType == "CE")]
                                            if len(ind_df_specific_symbol_expiry_ce) > 0:
                                                ce_token = ind_df_specific_symbol_expiry_ce.iloc[0]["Token"]
                                            else:
                                                ce_token = "NA"

                                            # print(f"symbol:{input_symbol},Expiry:{expiry},strike:{strike},pe_token={pe_token},ce_token={ce_token}")

                                            strike_pe_ce_dictionary = dict(
                                                {
                                                    "strike": strike,
                                                    "PE_Token": pe_token,
                                                    "CE_Token": ce_token,
                                                }
                                            )

                                            strike_pe_ce_list.append(strike_pe_ce_dictionary)

                                        # print(strike_pe_ce_list)

                                        expiry_stikelist_dict = dict(
                                            {
                                                "Expiry": expiry,
                                                "LotSize": LotSize,
                                                "Strike_list": strike_pe_ce_list,
                                            }
                                        )

                                        Expiry_strikelist_list.append(expiry_stikelist_dict)
                                    else:
                                        print(f"Ignoring the wrong date : {expiry}")
                                # print("\n\n")
                                # print(Expiry_strikelist_list)

                                Final_dic = dict(
                                    {
                                        "symbol": input_symbol,
                                        "Expiry_Strike_token": Expiry_strikelist_list,
                                    }
                                )

                                OptionChain_template.append(Final_dic)
                                # print("\n\n")
                                #print(OptionChain_template)
                            else:
                                Message = (
                                    "symbol already available in option chain template"
                                )

                            List_of_Expiry_Strike_token = [
                                itr["Expiry_Strike_token"]
                                for itr in OptionChain_template
                                if itr["symbol"] == input_symbol
                            ][0]
                            #print(f"\n\n******\n\n{List_of_Expiry_Strike_token}")

                            for expiry_strike in List_of_Expiry_Strike_token:
                                
                               
                                if input_symbol not in subs_lst:

                                    List_of_particular_expiry_strike = [
                                        itr["Strike_list"]
                                        for itr in List_of_Expiry_Strike_token
                                        if itr["Expiry"] == expiry_strike["Expiry"]
                                    ][0]

                                    for strike_dict in List_of_particular_expiry_strike:
                                        print(f"Going to subscribe {input_symbol} strike {strike_dict.get('strike')}")
                                        PE_Token = strike_dict.get("PE_Token")
                                        # print(PE_Token)
                                        CE_Token = strike_dict.get("CE_Token")
                                        # print(CE_Token)
                                        if PE_Token != "NA":
                                            subscribe_new_token(Exchange, PE_Token)
                                        if CE_Token != "NA":
                                            subscribe_new_token(Exchange, CE_Token)

                            if input_symbol not in subs_lst:
                                subs_lst.append(input_symbol)
                                print(f"{input_symbol} subscription completed")

                            pd_oc = pd.DataFrame(columns=[ "CE_token", "CE_oi",  "CE_poi", "CE_toi", "CE_lp", "CE_pc", "CE_bq1","CE_bp1", "CE_sq1", "CE_sp1",  "strike", "PE_bq1", "PE_bp1", "PE_sq1", "PE_sp1", "PE_pc",  "PE_lp",  "PE_toi", "PE_poi", "PE_oi", "PE_token","CE_coi","PE_coi","CE_v","PE_v"] )

                            # prepare option chain
                            List_of_Expiry_Strike_token = [
                                itr["Expiry_Strike_token"]
                                for itr in OptionChain_template
                                if itr["symbol"] == input_symbol
                            ][0]
                            #print(f"List_of_Expiry_Strike_token = {List_of_Expiry_Strike_token}")
                            try:
                                Lot_Size = [
                                    itr["LotSize"]
                                    for itr in List_of_Expiry_Strike_token
                                    if itr["Expiry"] == expiry_input
                                ][0]
                                List_of_particular_expiry_strike = [
                                    itr["Strike_list"]
                                    for itr in List_of_Expiry_Strike_token
                                    if itr["Expiry"] == expiry_input
                                ][0]
                                #print(f"****{live_data}")
                                #print(f"List_of_particular_expiry_strike= {List_of_particular_expiry_strike}")
                                
                                isFound, Fut_Token = GetToken(Exchange,input_symbol)
                                #print(f"isFound {isFound} Fut_Token {Fut_Token}")
                                if Exchange == 'NFO':
                                    isFound, Spot_Token = GetToken('NSE',input_symbol)
                                    spot_ltp = float(api.get_quotes("NSE", str(Spot_Token)).get("lp"))
                                else:    
                                    Spot_Token = Fut_Token
                                    spot_ltp = float(api.get_quotes(Exchange, str(Spot_Token)).get("lp"))
                                #print(f"Spot_Token {Spot_Token} spot_ltp {spot_ltp}")
                                future_ltp = float(api.get_quotes(Exchange, str(Fut_Token)).get("lp"))
                                
                                #print(f"{input_symbol} spot ltp = {spot_ltp} future ltp = {future_ltp}")

                                for strike_dict in List_of_particular_expiry_strike:
                                    
                                    Strike = float(strike_dict.get("strike"))
                                    #print(Strike)
                                    PE_Token = strike_dict.get("PE_Token")
                                    PE_Token = str(Exchange)+ "|"  + str(PE_Token)
                                    #print(PE_Token)
                                    CE_Token = strike_dict.get("CE_Token")
                                    CE_Token =  str(Exchange)+ "|" + str(CE_Token)
                                    #print(CE_Token)
                                    
                                    
                                    try:
                                        CE_oi = live_data[str(CE_Token)].get("oi", 0)
                                    except:
                                        CE_oi = 0
                                    try:
                                        CE_poi = live_data[str(CE_Token)].get("poi", 0)
                                    except:
                                        CE_poi = 0
                                    
                                    CE_coi = int(CE_oi) - int(CE_poi)
                                    
                                    try:
                                        CE_toi = live_data[str(CE_Token)].get("toi", "-")
                                    except:
                                        CE_toi = "-"
                                    try:
                                        CE_lp = live_data[str(CE_Token)].get("lp", 0)
                                    except:
                                        CE_lp = 0
                                    try:
                                        CE_pc = live_data[str(CE_Token)].get("pc", "-")
                                    except:
                                        CE_pc = "-"
                                    try:
                                        CE_bq1 = live_data[str(CE_Token)].get("bq1", "-")
                                    except:
                                        CE_bq1 = "-"
                                    try:
                                        CE_bp1 = live_data[str(CE_Token)].get("bp1", "-")
                                    except:
                                        CE_bp1 = "-"
                                    try:
                                        CE_sq1 = live_data[str(CE_Token)].get("sq1", "-")
                                    except:
                                        CE_sq1 = "-"
                                    try:
                                        CE_sp1 = live_data[str(CE_Token)].get("sp1", "-")
                                    except:
                                        CE_sp1 = "-"
                                    
                                    try:
                                        PE_oi = live_data[str(PE_Token)].get("oi", 0)
                                    except:
                                        PE_oi = 0
                                    try:
                                        PE_poi = live_data[str(PE_Token)].get("poi", 0)
                                    except:
                                        PE_poi = 0
                                    
                                    PE_coi = int(PE_oi) - int(PE_poi)
                                    
                                    #print(f"CE COI : {CE_oi} {CE_poi} {CE_coi}")
                                    #print(f"PE COI : {PE_oi} {PE_poi} {PE_coi}")
                                    try:
                                        PE_toi = live_data[str(PE_Token)].get("toi", "-")
                                    except:
                                        PE_toi = "-"
                                    try:
                                        PE_lp = live_data[str(PE_Token)].get("lp", 0)
                                    except:
                                        PE_lp = 0
                                    try:
                                        PE_pc = live_data[str(PE_Token)].get("pc", "-")
                                    except:
                                        PE_pc = "-"
                                    try:
                                        PE_bq1 = live_data[str(PE_Token)].get("bq1", "-")
                                    except:
                                        PE_bq1 = "-"
                                    try:
                                        PE_bp1 = live_data[str(PE_Token)].get("bp1", "-")
                                    except:
                                        PE_bp1 = "-"
                                    try:
                                        PE_sq1 = live_data[str(PE_Token)].get("sq1", "-")
                                    except:
                                        PE_sq1 = "-"
                                    try:
                                        PE_sp1 = live_data[str(PE_Token)].get("sp1", "-")
                                    except:
                                        PE_sp1 = "-"
                                    
                                    try:
                                        CE_v = live_data[str(CE_Token)].get("v", 0)
                                    except:
                                        CE_v = 0
                                        
                                    try:
                                        PE_v = live_data[str(PE_Token)].get("v", 0)
                                    except:
                                        PE_v = 0
                                    
                                    dic_data = {
                                            "CE_token": CE_Token,
                                            "CE_oi": CE_oi,
                                            "CE_poi": CE_poi,
                                            "CE_toi": CE_toi,
                                            "CE_lp": CE_lp,
                                            "CE_pc": CE_pc,
                                            "CE_bq1": CE_bq1,
                                            "CE_bp1": CE_bp1,
                                            "CE_sq1": CE_sq1,
                                            "CE_sp1": CE_sp1,
                                            "strike": Strike,
                                            "PE_bq1": PE_bq1,
                                            "PE_bp1": PE_bp1,
                                            "PE_sq1": PE_sq1,
                                            "PE_sp1": PE_sp1,
                                            "PE_pc": PE_pc,
                                            "PE_lp": PE_lp,
                                            "PE_toi": PE_toi,
                                            "PE_poi": PE_poi,
                                            "PE_oi": PE_oi,
                                            "PE_token": PE_Token,
                                            "CE_coi":CE_coi,
                                            "PE_coi":PE_coi,
                                            "CE_v":CE_v,
                                            "PE_v":PE_v,
                                        }
                                    pd_oc = pd.concat([pd_oc, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                    
                                #pd_oc.to_csv("pd_oc.csv")                            
                                #print(pd_oc)
                                pd_oc = pd_oc.astype({"strike": float})
                                pd_oc = pd_oc.sort_values(by="strike", ascending=True)

                                pd_oc = pd_oc.fillna(0)
                                
                                pd_oc = pd_oc.astype({"CE_oi": int})
                                pd_oc = pd_oc.astype({"PE_oi": int})
                                pd_oc = pd_oc.astype({"CE_poi": int})
                                pd_oc = pd_oc.astype({"PE_poi": int})
                                pd_oc = pd_oc.astype({"CE_coi": int})
                                pd_oc = pd_oc.astype({"PE_coi": int})
                                
                                pd_oc = pd_oc.astype({"CE_v": int})
                                pd_oc = pd_oc.astype({"PE_v": int})
                                
                                pd_oc["CE_v"] = pd_oc["CE_v"] / int(Lot_Size)
                                pd_oc["PE_v"] = pd_oc["PE_v"] / int(Lot_Size)
                                
                                pd_oc["CE_oi"] = pd_oc["CE_oi"] / int(Lot_Size)
                                pd_oc["PE_oi"] = pd_oc["PE_oi"] / int(Lot_Size)
                                pd_oc["CE_poi"] = pd_oc["CE_poi"] / int(Lot_Size)
                                pd_oc["PE_poi"] = pd_oc["PE_poi"] / int(Lot_Size)
                                pd_oc["CE_coi"] = pd_oc["CE_coi"] / int(Lot_Size)
                                pd_oc["PE_coi"] = pd_oc["PE_coi"] / int(Lot_Size)
                                pd_oc["OI_SUM"] = pd_oc["CE_oi"] + pd_oc["PE_oi"]

                                #print(pd_oc)
                                
                                df_oc_pro = pd_oc
                                
                                try:
                                    NoOfStrike = int(oci.range("E6").value)
                                except Exception as e:
                                    NoOfStrike = 100
                                
                                df_oc_pro['strike_diff'] = abs(df_oc_pro['strike'] - spot_ltp)
            
                                df_oc_pro.sort_values(by = 'strike_diff',inplace = True)
                                
                                #print(f"\n\n***\n\n{df_oc_pro}")
                                AtmStrike = float(df_oc_pro.iloc[0]['strike'])
                                AtmStrikeCallPrice = float(df_oc_pro.iloc[0]['CE_lp'])
                                AtmStrikePutPrice = float(df_oc_pro.iloc[0]['PE_lp'])
                                
                                #additional detail related to dump on input page
                                Future_LTP = future_ltp
                                Max_Pain_at_Strike = df_oc_pro[df_oc_pro.OI_SUM == df_oc_pro["OI_SUM"].max()].iloc[0]['strike']
                                Ltp_at_Max_Pain_CE = df_oc_pro[df_oc_pro.OI_SUM == df_oc_pro["OI_SUM"].max()].iloc[0]['CE_lp']
                                Ltp_at_Max_Pain_PE = df_oc_pro[df_oc_pro.OI_SUM == df_oc_pro["OI_SUM"].max()].iloc[0]['PE_lp']
                                #ATM_Strike = 
                                LTP_at_ATM_CE = df_oc_pro[df_oc_pro.strike == AtmStrike].iloc[0]['CE_lp']
                                LTP_at_ATM_PE = df_oc_pro[df_oc_pro.strike == AtmStrike].iloc[0]['PE_lp']
                                Total_OI_CE = df_oc_pro["CE_oi"].sum()
                                Total_OI_PE = df_oc_pro["PE_oi"].sum()
                                
                                Max_OI_CE = df_oc_pro["CE_oi"].max()
                                Max_OI_PE = df_oc_pro["PE_oi"].max()
                                Max_OI_at_Strike_CE = df_oc_pro[df_oc_pro.CE_oi == df_oc_pro["CE_oi"].max()].iloc[0]['strike']
                                Max_OI_at_Strike_PE = df_oc_pro[df_oc_pro.PE_oi == df_oc_pro["PE_oi"].max()].iloc[0]['strike']
                                LTP_of_Max_OI_Strike_CE = df_oc_pro[df_oc_pro.CE_oi == df_oc_pro["CE_oi"].max()].iloc[0]['CE_lp']
                                LTP_of_Max_OI_Strike_PE = df_oc_pro[df_oc_pro.PE_oi == df_oc_pro["PE_oi"].max()].iloc[0]['PE_lp']

                                Total_OI_Change_CE = df_oc_pro["CE_coi"].sum()
                                Total_OI_Change_PE = df_oc_pro["PE_coi"].sum()
                                
                                Max_Change_in_OI_addition_CE = df_oc_pro["CE_coi"].max()
                                Max_Change_in_OI_addition_PE = df_oc_pro["PE_coi"].max()
                                Max_OI_addition_at_Srike_CE = df_oc_pro[df_oc_pro["CE_coi"] == df_oc_pro["CE_coi"].max()].iloc[0]['strike']
                                Max_OI_addition_at_Srike_PE = df_oc_pro[df_oc_pro["PE_coi"] == df_oc_pro["PE_coi"].max()].iloc[0]['strike']
                                LTP_of_Max_OI_addition_Strike_CE = df_oc_pro[df_oc_pro["CE_coi"] == df_oc_pro["CE_coi"].max()].iloc[0]['CE_lp']
                                LTP_of_Max_OI_addition_Strike_PE = df_oc_pro[df_oc_pro["PE_coi"] == df_oc_pro["PE_coi"].max()].iloc[0]['PE_lp']
                                Max_Change_in_OI_unwinding_CE = -1 * int(df_oc_pro["CE_coi"].min())
                                Max_Change_in_OI_unwinding_PE = -1 * int(df_oc_pro["PE_coi"].min())
                                Max_OI_unwinding_at_Srike_CE = df_oc_pro[df_oc_pro["CE_coi"] == df_oc_pro["CE_coi"].min()].iloc[0]['strike']
                                Max_OI_unwinding_at_Srike_PE = df_oc_pro[df_oc_pro["PE_coi"] == df_oc_pro["PE_coi"].min()].iloc[0]['strike']
                                LTP_of_Max_OI_unwinding_Strike_CE = df_oc_pro[df_oc_pro["CE_coi"] == df_oc_pro["CE_coi"].min()].iloc[0]['CE_lp']
                                LTP_of_Max_OI_unwinding_Strike_PE = df_oc_pro[df_oc_pro["PE_coi"] == df_oc_pro["PE_coi"].min()].iloc[0]['PE_lp']
                            
                                df_additional_detail = pd.DataFrame(columns = ['CE','PE'])
                                dic_data = {'CE':Future_LTP}

                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Max_Pain_at_Strike}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Ltp_at_Max_Pain_CE,'PE': Ltp_at_Max_Pain_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':AtmStrike}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':LTP_at_ATM_CE,'PE':LTP_at_ATM_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Total_OI_CE, 'PE':Total_OI_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Total_OI_Change_CE, 'PE':Total_OI_Change_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Max_OI_CE,  'PE':Max_OI_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Max_OI_at_Strike_CE,'PE':Max_OI_at_Strike_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':LTP_of_Max_OI_Strike_CE,'PE':LTP_of_Max_OI_Strike_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Max_Change_in_OI_addition_CE,'PE':Max_Change_in_OI_addition_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Max_OI_addition_at_Srike_CE,'PE':Max_OI_addition_at_Srike_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':LTP_of_Max_OI_addition_Strike_CE, 'PE':LTP_of_Max_OI_addition_Strike_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Max_Change_in_OI_unwinding_CE, 'PE':Max_Change_in_OI_unwinding_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Max_OI_unwinding_at_Srike_CE, 'PE':Max_OI_unwinding_at_Srike_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':LTP_of_Max_OI_unwinding_Strike_CE,'PE':LTP_of_Max_OI_unwinding_Strike_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                

                                oci.range("i3").options(index=False, header=False).value = df_additional_detail
                                
                                
                                
                                df_oc_pro = df_oc_pro[:2*int(NoOfStrike)+1]
                                
                                df_oc_pro.sort_values(by ='strike',inplace = True)
                                
                                df_oc_pro['CE_Delta'] = None
                                df_oc_pro['CE_Gamma'] = None
                                df_oc_pro['CE_Theta'] = None
                                df_oc_pro['CE_Vega'] = None
                                df_oc_pro['CE_Rho'] = None
                                df_oc_pro['CE_IV'] = None
                                
                                df_oc_pro['PE_Delta'] = None
                                df_oc_pro['PE_Gamma'] = None
                                df_oc_pro['PE_Theta'] = None
                                df_oc_pro['PE_Vega'] = None
                                df_oc_pro['PE_Rho'] = None
                                df_oc_pro['PE_IV'] = None
                    
                                df_oc_pro = df_oc_pro.reindex(['CE_Delta','CE_Gamma','CE_Theta','CE_Vega','CE_Rho','CE_oi','CE_coi','CE_v','CE_IV','CE_lp','CE_pc','CE_bq1','CE_bp1','CE_sp1','CE_sq1','strike','PE_bq1','PE_bp1','PE_sp1','PE_sq1','PE_pc','PE_lp','PE_IV','PE_v','PE_coi','PE_oi','PE_Rho','PE_Vega','PE_Theta','PE_Gamma','PE_Delta'], axis=1)
                                
                                SpotPrice = float(spot_ltp)
                                FuturePrice = float(future_ltp)
                                ExpiryDateTime = dt(expiry_input.year, expiry_input.month, expiry_input.day, 0, 0, 0)
                                
                                #print(f"SpotPrice = {SpotPrice}, FuturePrice={FuturePrice}, AtmStrike={AtmStrike}, AtmStrikeCallPrice={AtmStrikeCallPrice}, AtmStrikePutPrice={AtmStrikePutPrice}, ExpiryDateTime={ExpiryDateTime}")
                                
                                ExpiryType = oci.range("F7").value
                                GreekMatch = oci.range("F8").value
                                
                                if ExpiryType == 'WEEKLY':
                                    ExpiryDateType = ExpType.WEEKLY
                                else:
                                    ExpiryDateType = ExpType.MONTHLY
                                
                                FromDateTime = dt.now() 
                                if Exchange == 'NFO':
                                    if dt.now().time() > time(15, 30, 0):
                                        FromDateTime = dt(dt.now().year, dt.now().month,dt.now().day, 15, 30, 0)
                                    
                                    
                                if GreekMatch == "SENSIBULL":
                                    tryMatchWith=TryMatchWith.SENSIBULL
                                else:
                                    tryMatchWith=TryMatchWith.NSE
                                
                                dayCountType = DayCountType.CALENDARDAYS
                                
                                IvGreeks = CalcIvGreeks( SpotPrice = SpotPrice,  FuturePrice = FuturePrice, AtmStrike = AtmStrike, AtmStrikeCallPrice = AtmStrikeCallPrice, AtmStrikePutPrice = AtmStrikePutPrice, ExpiryDateTime = ExpiryDateTime, ExpiryDateType = ExpiryDateType, FromDateTime = FromDateTime, tryMatchWith = tryMatchWith, dayCountType = dayCountType)
                
                                #print(f"SpotPrice={SpotPrice}, FuturePrice={FuturePrice},  AtmStrike={AtmStrike}, AtmStrikeCallPrice={AtmStrikeCallPrice}, AtmStrikePutPrice={AtmStrikePutPrice}, ExpiryDateTime={ExpiryDateTime},  ExpiryDateType={ExpiryDateType}, FromDateTime={FromDateTime}, tryMatchWith={tryMatchWith}")
                                
                                for ind in df_oc_pro.index:
                                    
                                    StrikePrice= float(df_oc_pro['strike'][ind])
                                    StrikeCallPrice= float(df_oc_pro['CE_lp'][ind])
                                    StrikePutPrice= float(df_oc_pro['PE_lp'][ind])
                                    #print(f"StrikePrice={StrikePrice}, StrikeCallPrice={StrikeCallPrice}, StrikePutPrice={StrikePutPrice}")
                                    Greeks = IvGreeks.GetImpVolAndGreeks( StrikePrice = StrikePrice, StrikeCallPrice = StrikeCallPrice, StrikePutPrice = StrikePutPrice)
                                    #print(Greeks)
                                    
                                    df_oc_pro['CE_Delta'][ind] = round(Greeks["CallDelta"],2)
                                    df_oc_pro['CE_Gamma'][ind] = round(Greeks["Gamma"],4)
                                    df_oc_pro['CE_Theta'][ind] = round(Greeks["Theta"],2)
                                    df_oc_pro['CE_Vega'][ind] = round(Greeks["Vega"],2)
                                    df_oc_pro['CE_Rho'][ind] = round(Greeks["RhoCall"],4)
                                    
                                    
                                    df_oc_pro['PE_Delta'][ind] = round(Greeks["PutDelta"],2)
                                    df_oc_pro['PE_Gamma'][ind] = round(Greeks["Gamma"],4)
                                    df_oc_pro['PE_Theta'][ind] = round(Greeks["Theta"],2)
                                    df_oc_pro['PE_Vega'][ind] = round(Greeks["Vega"],2)
                                    df_oc_pro['PE_Rho'][ind] = round(Greeks["RhoPut"],4)
                                    
                                    if GreekMatch == "NSE":
                                        df_oc_pro['CE_IV'][ind] = round(Greeks["CallIV"],2)
                                        df_oc_pro['PE_IV'][ind] = round(Greeks["PutIV"],2)
                                    else:
                                        df_oc_pro['CE_IV'][ind] = round(Greeks["ImplVol"],2)
                                        df_oc_pro['PE_IV'][ind] = round(Greeks["ImplVol"],2)
                                
                                del IvGreeks
                                df_oc_pro.round({"CE_lp":2, 'PE_lp':2})
                                #print(df_oc_pro)
                                
                                if pre_selected_NoOfStrike != NoOfStrike:
                                    pre_selected_NoOfStrike = NoOfStrike
                                    Option_Chain_Output.range('a3:ae500').value = None
                                    Option_Chain_Output.range(f"a3:AE500").color = (255,255,255)
                                    Option_Chain_Output.range(f"p3:p{2 * int(NoOfStrike) + 3}").color = (46,132,198) 
                                
                                df_oc_pro = df_oc_pro.reset_index(drop = True)
                                ATM_pos = df_oc_pro[df_oc_pro.strike == AtmStrike].index.values[0]
                                if NoOfStrike * 2 < len(df_oc_pro):
                                    df_oc_pro = df_oc_pro.iloc[ATM_pos - int(NoOfStrike) : ATM_pos + int(NoOfStrike) + 1]
                                    ATM_Row = int(NoOfStrike) + 3
                                    Option_Chain_Output.range(f"a{ATM_Row}:AE{ATM_Row}").color = (46,132,198)
                        
                                Option_Chain_Output.range('a3').options(index=False,header=False).value = df_oc_pro
                                
                            except Exception as e:
                                Option_Chain_Output.range('a3:ae500').value = None
                                oci.range('I3:J18').value = None
                                Message = "Please check the all provided detail to load option chain:" + str(e)
                                print(Message)
                                oci.range("F4").value = Message
                                
                        else:
                            Option_Chain_Output.range('a3:ae500').value = None
                            oci.range('I3:J18').value = None
                            Message = "Please enter correct expiry in dd-mm-YYYY (date format)"
                            print(Message)
                            oci.range("F4").value = Message
                            
                    else:
                        Option_Chain_Output.range('a3:ae500').value = None
                        oci.range('I3:J18').value = None
                        Message = "Please enter the expiry"
                        print(Message)
                        oci.range("F4").value = Message
                        
                else:
                    Option_Chain_Output.range('a3:ae500').value = None
                    oci.range('I3:J18').value = None
                    Message = "Please enter correct symbol"
                    print(Message)
                    oci.range("F3").value = Message
                    oci.range("F4").value = None
                    oci.range("b2:c100").value = None
                    
            else:
                Option_Chain_Output.range('a3:ae500').value = None
                oci.range('I3:J18').value = None
                Message = "Please enter the symbol"
                print(Message)
                oci.range("F3").value = Message
                oci.range("F4").value = None
                oci.range("b2:c100").value = None
                
        except Exception as e:
            print(f"Excption : {e}")
            pass
        sleep(int(IterationSleep))
        
def GetToken(Exchange,Symbol, Type = 'FUT' ,Expiry=None,Strike = None):
    #print(f"GetToken called with parametrer \nExchange = {Exchange}, Symbol = {Symbol}")
    global df_ins_NSE, df_ins_BSE, df_ins_NFO, df_ins_CDS, df_ins_MCX
    try:
        Token = None
        isTokenFound = False
        Symbol = Symbol.upper()
        if Exchange == 'NSE':
            if Symbol in IndexList:    
                Token = Token_list[Symbol]
                isTokenFound = True
            else:
                df_ins_temp = df_ins_NSE[df_ins_NSE.Symbol == Symbol]
                if len(df_ins_temp) > 0:
                    Token = df_ins_temp.iloc[0]['Token']
                    isTokenFound = True
        
        elif Exchange == 'BSE':
            df_ins_temp = df_ins_BSE[df_ins_BSE.Symbol == Symbol]
            if len(df_ins_temp) > 0:
                Token = df_ins_temp.iloc[0]['Token']
                isTokenFound = True
        elif Exchange == 'NFO':
            df_ins_temp = df_ins_NFO[(df_ins_NFO.Symbol == Symbol) & (df_ins_NFO['Instrument'].isin(['FUTIDX','FUTSTK']))]
            if len(df_ins_temp) > 0:
                df_ins_temp.sort_values(by = 'Expiry',inplace = True)
                Token = df_ins_temp.iloc[0]['Token']
                isTokenFound = True
        elif Exchange == 'CDS':
            df_ins_temp = df_ins_CDS[(df_ins_CDS.Symbol == Symbol) & (df_ins_CDS.Instrument == 'FUTCUR')]
            if len(df_ins_temp) > 0:
                df_ins_temp.sort_values(by = 'Expiry',inplace = True)
                Token = df_ins_temp.iloc[0]['Token']
                isTokenFound = True
        elif Exchange == 'MCX':
            df_ins_temp = df_ins_MCX[(df_ins_MCX.Symbol == Symbol) & (df_ins_MCX.Instrument == 'FUTCOM')]
            if len(df_ins_temp) > 0:
                df_ins_temp.sort_values(by = 'Expiry',inplace = True)
                Token = df_ins_temp.iloc[0]['Token']
                isTokenFound = True
    except Exception as e:
        print(f"Exception occur in GetToken : {e}")
    
    #print(f"Returning value isTokenFound = {isTokenFound}, Token = {Token} ")
    return isTokenFound, Token
    
def start_optionchain_Pro():
    global subscribe_symbol
    global live_data
    global df_ins_NFO
    global df_ins_NSE, df_ins_BSE, df_ins_NFO, df_ins_CDS, df_ins_MCX
    
    global subs_lst
    excel_name = xw.Book(TerminalSheetName)
    oci_pro = excel_name.sheets("Option_Chain_Pro_Input")
    Option_Chain_Pro_Output = excel_name.sheets("Option_Chain_Pro_Output")
    

    oci_pro.range("F3").value = None
    oci_pro.range("F4").value = None
    oci_pro.range("a2:c500").value = None
    
    Option_Chain_Pro_Output.range('a3:ae500').value = None

    IterationSleep = 1
    
    oci_pro.range("d2").value = "Segment==>>"
    oci_pro.range("d3").value, oci_pro.range("d4").value = "Symbol==>>", "Expiry==>>",
    oci_pro.range("d5").value, oci_pro.range("d6").value = "RefreshRate==>>", "NoOfStrike==>>",
    oci_pro.range("d7").value, oci_pro.range("d8").value = "ExpiryType==>>" , "GreekMatch==>>"
    
    pre_selected_segment = pre_selected_symbol = pre_selected_expiry = "" 
    pre_selected_NoOfStrike = 0
    
    while True:
        try:
            selected_segment = oci_pro.range("E2").value
            Exchange = selected_segment[:3]
            if Exchange not in ['NFO','CDS','MCX']:
                Exchange = 'NFO'
            
            oci_pro.range("F2").value = None
            if Exchange == 'NFO':
                df_instrument = df_ins_NFO
            elif Exchange == 'CDS':
                df_instrument = df_ins_CDS
            elif Exchange == 'MCX':
                df_instrument = df_ins_MCX
            else:
                oci_pro.range("F2").value = "Please select correct segment"
            #print(f"pre : pre_selected_segment = {pre_selected_segment}, selected_segment={selected_segment}")
            if pre_selected_segment != selected_segment:
                if Exchange == 'NFO':
                    df_symbol = df_ins_NFO
                if Exchange == 'CDS':
                    df_symbol = df_ins_CDS[df_ins_CDS['Instrument'] == 'UNDCUR']
                if Exchange == 'MCX':
                    df_symbol = df_ins_MCX[df_ins_MCX['Instrument'] == 'OPTFUT']
                    
                df_symbol = df_symbol.drop_duplicates( "Symbol" , keep='first')
                df_symbol =df_symbol[['Symbol']]
                oci_pro.range("a2:c500").value = None
                oci_pro.range("a1").options(index=False, header=True).value = df_symbol
                    
            pre_selected_segment = selected_segment
            #print(f"post : pre_selected_segment = {pre_selected_segment}, selected_segment={selected_segment}")
            
            input_symbol = str(oci_pro.range("E3").value).strip()
            if input_symbol != None:

                df_instrument_temp = df_instrument[(df_instrument.Symbol == input_symbol) & (df_instrument['OptionType'].isin(['CE','PE']))]
                
                if len(df_instrument_temp) > 0:
                    if pre_selected_symbol != input_symbol:
                        df_exp = df_instrument_temp.drop_duplicates( "Expiry" , keep='first')
                        
                        df_exp =df_exp[['Expiry','LotSize']]
                        df_exp.sort_values(by = 'Expiry',inplace = True)
                        oci_pro.range("b2:C100").value = None
                        oci_pro.range("b2").options(index=False, header=False).value = df_exp
                    
                    pre_selected_symbol = input_symbol                    
                    
                    oci_pro.range("F3").value = None
                    
                    expiry_input = oci_pro.range("E4").value
                    
                    expiry_input = expiry_input.date() 

                    #print(f"Option Chain Details: input_symbol={input_symbol},expiry_input={expiry_input}")
                    if expiry_input != None:

                        df_instrument_temp = df_instrument_temp[df_instrument_temp.Expiry == expiry_input]
                        if len(df_instrument_temp) > 0:

                            oci_pro.range("F4").value = None
                            try:
                                IterationSleep = oci_pro.range("E5").value
                                if IterationSleep != None:
                                    IterationSleep = int(IterationSleep)
                                    oci_pro.range("F5").value = None
                            except:
                                IterationSleep = 5
                                Message = "Iteration should be number"
                                print(Message)
                                oci_pro.range("F5").value = Message

                            symbol_in_template = [itr for itr in OptionChain_template if itr["symbol"] ==input_symbol ]
                            if len(symbol_in_template) == 0:
                                #print("Symbol Not found, so append the details")

                                ind_df_specific_symbol = df_instrument[(df_instrument.Symbol == input_symbol) & (df_instrument['OptionType'].isin(['CE','PE']))]
                                #print(ind_df_specific_symbol)

                                List_of_Expiry = ind_df_specific_symbol["Expiry"].unique()
                                #print(List_of_Expiry)

                                Expiry_strikelist_list = []

                                for expiry in List_of_Expiry:
                                    if str( expiry ) != 'NaT' :
                                        #print(expiry)
                                        ind_df_specific_symbol_expiry = ind_df_specific_symbol[(ind_df_specific_symbol.Expiry == expiry) ]
                                        #print(ind_df_specific_symbol_expiry)

                                        List_of_strikes = ind_df_specific_symbol_expiry["StrikePrice"].unique()

                                        LotSize = ind_df_specific_symbol_expiry.iloc[0]["LotSize"]
                                        #print(LotSize)
                                        #print(List_of_strikes)
                                        strike_pe_ce_list = []

                                        for strike in List_of_strikes:
                                            #print(f"\nsymbol:{input_symbol},Expiry:{expiry},strike:{strike}")
                                            ind_df_specific_symbol_expiry_pe = ind_df_specific_symbol_expiry[
                                                (ind_df_specific_symbol_expiry.StrikePrice == str(strike)) & (ind_df_specific_symbol_expiry.OptionType == "PE") ]
                                            if len(ind_df_specific_symbol_expiry_pe) > 0:
                                                pe_token = ind_df_specific_symbol_expiry_pe.iloc[0]["Token"]
                                            else:
                                                pe_token = "NA"

                                            ind_df_specific_symbol_expiry_ce = ind_df_specific_symbol_expiry[(            ind_df_specific_symbol_expiry.StrikePrice== str(strike))& (            ind_df_specific_symbol_expiry.OptionType == "CE")]
                                            if len(ind_df_specific_symbol_expiry_ce) > 0:
                                                ce_token = ind_df_specific_symbol_expiry_ce.iloc[0]["Token"]
                                            else:
                                                ce_token = "NA"

                                            # print(f"symbol:{input_symbol},Expiry:{expiry},strike:{strike},pe_token={pe_token},ce_token={ce_token}")

                                            strike_pe_ce_dictionary = dict(
                                                {
                                                    "strike": strike,
                                                    "PE_Token": pe_token,
                                                    "CE_Token": ce_token,
                                                }
                                            )

                                            strike_pe_ce_list.append(strike_pe_ce_dictionary)

                                        # print(strike_pe_ce_list)

                                        expiry_stikelist_dict = dict(
                                            {
                                                "Expiry": expiry,
                                                "LotSize": LotSize,
                                                "Strike_list": strike_pe_ce_list,
                                            }
                                        )

                                        Expiry_strikelist_list.append(expiry_stikelist_dict)
                                    else:
                                        print(f"Ignoring the wrong date : {expiry}")
                                # print("\n\n")
                                # print(Expiry_strikelist_list)

                                Final_dic = dict(
                                    {
                                        "symbol": input_symbol,
                                        "Expiry_Strike_token": Expiry_strikelist_list,
                                    }
                                )

                                OptionChain_template.append(Final_dic)
                                # print("\n\n")
                                #print(OptionChain_template)
                            else:
                                Message = (
                                    "symbol already available in option chain template"
                                )

                            List_of_Expiry_Strike_token = [
                                itr["Expiry_Strike_token"]
                                for itr in OptionChain_template
                                if itr["symbol"] == input_symbol
                            ][0]
                            #print(f"\n\n******\n\n{List_of_Expiry_Strike_token}")

                            for expiry_strike in List_of_Expiry_Strike_token:
                                
                               
                                if input_symbol not in subs_lst:

                                    List_of_particular_expiry_strike = [
                                        itr["Strike_list"]
                                        for itr in List_of_Expiry_Strike_token
                                        if itr["Expiry"] == expiry_strike["Expiry"]
                                    ][0]

                                    for strike_dict in List_of_particular_expiry_strike:
                                        print(f"Going to subscribe {input_symbol} strike {strike_dict.get('strike')}")
                                        PE_Token = strike_dict.get("PE_Token")
                                        # print(PE_Token)
                                        CE_Token = strike_dict.get("CE_Token")
                                        # print(CE_Token)
                                        if PE_Token != "NA":
                                            subscribe_new_token(Exchange, PE_Token)
                                        if CE_Token != "NA":
                                            subscribe_new_token(Exchange, CE_Token)

                            if input_symbol not in subs_lst:
                                subs_lst.append(input_symbol)
                                print(f"{input_symbol} subscription completed")

                            pd_oc = pd.DataFrame(columns=[ "CE_token", "CE_oi",  "CE_poi", "CE_toi", "CE_lp", "CE_pc", "CE_bq1","CE_bp1", "CE_sq1", "CE_sp1",  "strike", "PE_bq1", "PE_bp1", "PE_sq1", "PE_sp1", "PE_pc",  "PE_lp",  "PE_toi", "PE_poi", "PE_oi", "PE_token","CE_coi","PE_coi","CE_v","PE_v"] )

                            # prepare option chain
                            List_of_Expiry_Strike_token = [
                                itr["Expiry_Strike_token"]
                                for itr in OptionChain_template
                                if itr["symbol"] == input_symbol
                            ][0]
                            #print(f"List_of_Expiry_Strike_token = {List_of_Expiry_Strike_token}")
                            try:
                                Lot_Size = [
                                    itr["LotSize"]
                                    for itr in List_of_Expiry_Strike_token
                                    if itr["Expiry"] == expiry_input
                                ][0]
                                List_of_particular_expiry_strike = [
                                    itr["Strike_list"]
                                    for itr in List_of_Expiry_Strike_token
                                    if itr["Expiry"] == expiry_input
                                ][0]
                                #print(f"****{live_data}")
                                #print(f"List_of_particular_expiry_strike= {List_of_particular_expiry_strike}")
                                
                                isFound, Fut_Token = GetToken(Exchange,input_symbol)
                                #print(f"isFound {isFound} Fut_Token {Fut_Token}")
                                if Exchange == 'NFO':
                                    isFound, Spot_Token = GetToken('NSE',input_symbol)
                                    spot_ltp = float(api.get_quotes("NSE", str(Spot_Token)).get("lp"))
                                else:    
                                    Spot_Token = Fut_Token
                                    spot_ltp = float(api.get_quotes(Exchange, str(Spot_Token)).get("lp"))
                                #print(f"Spot_Token {Spot_Token} spot_ltp {spot_ltp}")
                                future_ltp = float(api.get_quotes(Exchange, str(Fut_Token)).get("lp"))
                                
                                #print(f"{input_symbol} spot ltp = {spot_ltp} future ltp = {future_ltp}")

                                for strike_dict in List_of_particular_expiry_strike:
                                    
                                    Strike = float(strike_dict.get("strike"))
                                    #print(Strike)
                                    PE_Token = strike_dict.get("PE_Token")
                                    PE_Token = str(Exchange)+ "|"  + str(PE_Token)
                                    #print(PE_Token)
                                    CE_Token = strike_dict.get("CE_Token")
                                    CE_Token =  str(Exchange)+ "|" + str(CE_Token)
                                    #print(CE_Token)
                                    
                                    
                                    try:
                                        CE_oi = live_data[str(CE_Token)].get("oi", 0)
                                    except:
                                        CE_oi = 0
                                    try:
                                        CE_poi = live_data[str(CE_Token)].get("poi", 0)
                                    except:
                                        CE_poi = 0
                                    
                                    CE_coi = int(CE_oi) - int(CE_poi)
                                    
                                    try:
                                        CE_toi = live_data[str(CE_Token)].get("toi", "-")
                                    except:
                                        CE_toi = "-"
                                    try:
                                        CE_lp = live_data[str(CE_Token)].get("lp", 0)
                                    except:
                                        CE_lp = 0
                                    try:
                                        CE_pc = live_data[str(CE_Token)].get("pc", "-")
                                    except:
                                        CE_pc = "-"
                                    try:
                                        CE_bq1 = live_data[str(CE_Token)].get("bq1", "-")
                                    except:
                                        CE_bq1 = "-"
                                    try:
                                        CE_bp1 = live_data[str(CE_Token)].get("bp1", "-")
                                    except:
                                        CE_bp1 = "-"
                                    try:
                                        CE_sq1 = live_data[str(CE_Token)].get("sq1", "-")
                                    except:
                                        CE_sq1 = "-"
                                    try:
                                        CE_sp1 = live_data[str(CE_Token)].get("sp1", "-")
                                    except:
                                        CE_sp1 = "-"
                                    
                                    try:
                                        PE_oi = live_data[str(PE_Token)].get("oi", 0)
                                    except:
                                        PE_oi = 0
                                    try:
                                        PE_poi = live_data[str(PE_Token)].get("poi", 0)
                                    except:
                                        PE_poi = 0
                                    
                                    PE_coi = int(PE_oi) - int(PE_poi)
                                    
                                    #print(f"CE COI : {CE_oi} {CE_poi} {CE_coi}")
                                    #print(f"PE COI : {PE_oi} {PE_poi} {PE_coi}")
                                    try:
                                        PE_toi = live_data[str(PE_Token)].get("toi", "-")
                                    except:
                                        PE_toi = "-"
                                    try:
                                        PE_lp = live_data[str(PE_Token)].get("lp", 0)
                                    except:
                                        PE_lp = 0
                                    try:
                                        PE_pc = live_data[str(PE_Token)].get("pc", "-")
                                    except:
                                        PE_pc = "-"
                                    try:
                                        PE_bq1 = live_data[str(PE_Token)].get("bq1", "-")
                                    except:
                                        PE_bq1 = "-"
                                    try:
                                        PE_bp1 = live_data[str(PE_Token)].get("bp1", "-")
                                    except:
                                        PE_bp1 = "-"
                                    try:
                                        PE_sq1 = live_data[str(PE_Token)].get("sq1", "-")
                                    except:
                                        PE_sq1 = "-"
                                    try:
                                        PE_sp1 = live_data[str(PE_Token)].get("sp1", "-")
                                    except:
                                        PE_sp1 = "-"
                                    
                                    try:
                                        CE_v = live_data[str(CE_Token)].get("v", 0)
                                    except:
                                        CE_v = 0
                                        
                                    try:
                                        PE_v = live_data[str(PE_Token)].get("v", 0)
                                    except:
                                        PE_v = 0
                                    
                                    dic_data = {
                                            "CE_token": CE_Token,
                                            "CE_oi": CE_oi,
                                            "CE_poi": CE_poi,
                                            "CE_toi": CE_toi,
                                            "CE_lp": CE_lp,
                                            "CE_pc": CE_pc,
                                            "CE_bq1": CE_bq1,
                                            "CE_bp1": CE_bp1,
                                            "CE_sq1": CE_sq1,
                                            "CE_sp1": CE_sp1,
                                            "strike": Strike,
                                            "PE_bq1": PE_bq1,
                                            "PE_bp1": PE_bp1,
                                            "PE_sq1": PE_sq1,
                                            "PE_sp1": PE_sp1,
                                            "PE_pc": PE_pc,
                                            "PE_lp": PE_lp,
                                            "PE_toi": PE_toi,
                                            "PE_poi": PE_poi,
                                            "PE_oi": PE_oi,
                                            "PE_token": PE_Token,
                                            "CE_coi":CE_coi,
                                            "PE_coi":PE_coi,
                                            "CE_v":CE_v,
                                            "PE_v":PE_v,
                                        }
                                    pd_oc = pd.concat([pd_oc, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                    
                                #pd_oc.to_csv("pd_oc.csv")                            
                                #print(pd_oc)
                                pd_oc = pd_oc.astype({"strike": float})
                                pd_oc = pd_oc.sort_values(by="strike", ascending=True)

                                pd_oc = pd_oc.fillna(0)
                                
                                pd_oc = pd_oc.astype({"CE_oi": int})
                                pd_oc = pd_oc.astype({"PE_oi": int})
                                pd_oc = pd_oc.astype({"CE_poi": int})
                                pd_oc = pd_oc.astype({"PE_poi": int})
                                pd_oc = pd_oc.astype({"CE_coi": int})
                                pd_oc = pd_oc.astype({"PE_coi": int})
                                
                                pd_oc = pd_oc.astype({"CE_v": int})
                                pd_oc = pd_oc.astype({"PE_v": int})
                                
                                pd_oc["CE_v"] = pd_oc["CE_v"] / int(Lot_Size)
                                pd_oc["PE_v"] = pd_oc["PE_v"] / int(Lot_Size)
                                
                                pd_oc["CE_oi"] = pd_oc["CE_oi"] / int(Lot_Size)
                                pd_oc["PE_oi"] = pd_oc["PE_oi"] / int(Lot_Size)
                                pd_oc["CE_poi"] = pd_oc["CE_poi"] / int(Lot_Size)
                                pd_oc["PE_poi"] = pd_oc["PE_poi"] / int(Lot_Size)
                                pd_oc["CE_coi"] = pd_oc["CE_coi"] / int(Lot_Size)
                                pd_oc["PE_coi"] = pd_oc["PE_coi"] / int(Lot_Size)
                                pd_oc["OI_SUM"] = pd_oc["CE_oi"] + pd_oc["PE_oi"]

                                #print(pd_oc)
                                
                                df_oc_pro = pd_oc
                                
                                try:
                                    NoOfStrike = int(oci_pro.range("E6").value)
                                except Exception as e:
                                    NoOfStrike = 100
                                
                                df_oc_pro['strike_diff'] = abs(df_oc_pro['strike'] - spot_ltp)
            
                                df_oc_pro.sort_values(by = 'strike_diff',inplace = True)
                                
                                #print(f"\n\n***\n\n{df_oc_pro}")
                                AtmStrike = float(df_oc_pro.iloc[0]['strike'])
                                AtmStrikeCallPrice = float(df_oc_pro.iloc[0]['CE_lp'])
                                AtmStrikePutPrice = float(df_oc_pro.iloc[0]['PE_lp'])
                                
                                #additional detail related to dump on input page
                                Future_LTP = future_ltp
                                Max_Pain_at_Strike = df_oc_pro[df_oc_pro.OI_SUM == df_oc_pro["OI_SUM"].max()].iloc[0]['strike']
                                Ltp_at_Max_Pain_CE = df_oc_pro[df_oc_pro.OI_SUM == df_oc_pro["OI_SUM"].max()].iloc[0]['CE_lp']
                                Ltp_at_Max_Pain_PE = df_oc_pro[df_oc_pro.OI_SUM == df_oc_pro["OI_SUM"].max()].iloc[0]['PE_lp']
                                #ATM_Strike = 
                                LTP_at_ATM_CE = df_oc_pro[df_oc_pro.strike == AtmStrike].iloc[0]['CE_lp']
                                LTP_at_ATM_PE = df_oc_pro[df_oc_pro.strike == AtmStrike].iloc[0]['PE_lp']
                                Total_OI_CE = df_oc_pro["CE_oi"].sum()
                                Total_OI_PE = df_oc_pro["PE_oi"].sum()
                                
                                Max_OI_CE = df_oc_pro["CE_oi"].max()
                                Max_OI_PE = df_oc_pro["PE_oi"].max()
                                Max_OI_at_Strike_CE = df_oc_pro[df_oc_pro.CE_oi == df_oc_pro["CE_oi"].max()].iloc[0]['strike']
                                Max_OI_at_Strike_PE = df_oc_pro[df_oc_pro.PE_oi == df_oc_pro["PE_oi"].max()].iloc[0]['strike']
                                LTP_of_Max_OI_Strike_CE = df_oc_pro[df_oc_pro.CE_oi == df_oc_pro["CE_oi"].max()].iloc[0]['CE_lp']
                                LTP_of_Max_OI_Strike_PE = df_oc_pro[df_oc_pro.PE_oi == df_oc_pro["PE_oi"].max()].iloc[0]['PE_lp']

                                Total_OI_Change_CE = df_oc_pro["CE_coi"].sum()
                                Total_OI_Change_PE = df_oc_pro["PE_coi"].sum()
                                
                                Max_Change_in_OI_addition_CE = df_oc_pro["CE_coi"].max()
                                Max_Change_in_OI_addition_PE = df_oc_pro["PE_coi"].max()
                                Max_OI_addition_at_Srike_CE = df_oc_pro[df_oc_pro["CE_coi"] == df_oc_pro["CE_coi"].max()].iloc[0]['strike']
                                Max_OI_addition_at_Srike_PE = df_oc_pro[df_oc_pro["PE_coi"] == df_oc_pro["PE_coi"].max()].iloc[0]['strike']
                                LTP_of_Max_OI_addition_Strike_CE = df_oc_pro[df_oc_pro["CE_coi"] == df_oc_pro["CE_coi"].max()].iloc[0]['CE_lp']
                                LTP_of_Max_OI_addition_Strike_PE = df_oc_pro[df_oc_pro["PE_coi"] == df_oc_pro["PE_coi"].max()].iloc[0]['PE_lp']
                                Max_Change_in_OI_unwinding_CE = -1 * int(df_oc_pro["CE_coi"].min())
                                Max_Change_in_OI_unwinding_PE = -1 * int(df_oc_pro["PE_coi"].min())
                                Max_OI_unwinding_at_Srike_CE = df_oc_pro[df_oc_pro["CE_coi"] == df_oc_pro["CE_coi"].min()].iloc[0]['strike']
                                Max_OI_unwinding_at_Srike_PE = df_oc_pro[df_oc_pro["PE_coi"] == df_oc_pro["PE_coi"].min()].iloc[0]['strike']
                                LTP_of_Max_OI_unwinding_Strike_CE = df_oc_pro[df_oc_pro["CE_coi"] == df_oc_pro["CE_coi"].min()].iloc[0]['CE_lp']
                                LTP_of_Max_OI_unwinding_Strike_PE = df_oc_pro[df_oc_pro["PE_coi"] == df_oc_pro["PE_coi"].min()].iloc[0]['PE_lp']
                            
                                df_additional_detail = pd.DataFrame(columns = ['CE','PE'])
                                dic_data = {'CE':Future_LTP}

                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Max_Pain_at_Strike}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Ltp_at_Max_Pain_CE,'PE': Ltp_at_Max_Pain_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':AtmStrike}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':LTP_at_ATM_CE,'PE':LTP_at_ATM_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Total_OI_CE, 'PE':Total_OI_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Total_OI_Change_CE, 'PE':Total_OI_Change_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Max_OI_CE,  'PE':Max_OI_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Max_OI_at_Strike_CE,'PE':Max_OI_at_Strike_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':LTP_of_Max_OI_Strike_CE,'PE':LTP_of_Max_OI_Strike_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Max_Change_in_OI_addition_CE,'PE':Max_Change_in_OI_addition_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Max_OI_addition_at_Srike_CE,'PE':Max_OI_addition_at_Srike_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':LTP_of_Max_OI_addition_Strike_CE, 'PE':LTP_of_Max_OI_addition_Strike_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Max_Change_in_OI_unwinding_CE, 'PE':Max_Change_in_OI_unwinding_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':Max_OI_unwinding_at_Srike_CE, 'PE':Max_OI_unwinding_at_Srike_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                
                                dic_data = {'CE':LTP_of_Max_OI_unwinding_Strike_CE,'PE':LTP_of_Max_OI_unwinding_Strike_PE}
                                df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                                

                                oci_pro.range("i3").options(index=False, header=False).value = df_additional_detail
                                
                                
                                
                                df_oc_pro = df_oc_pro[:2*int(NoOfStrike)+1]
                                
                                df_oc_pro.sort_values(by ='strike',inplace = True)
                                
                                df_oc_pro['CE_Delta'] = None
                                df_oc_pro['CE_Gamma'] = None
                                df_oc_pro['CE_Theta'] = None
                                df_oc_pro['CE_Vega'] = None
                                df_oc_pro['CE_Rho'] = None
                                df_oc_pro['CE_IV'] = None
                                
                                df_oc_pro['PE_Delta'] = None
                                df_oc_pro['PE_Gamma'] = None
                                df_oc_pro['PE_Theta'] = None
                                df_oc_pro['PE_Vega'] = None
                                df_oc_pro['PE_Rho'] = None
                                df_oc_pro['PE_IV'] = None
                    
                                df_oc_pro = df_oc_pro.reindex(['CE_Delta','CE_Gamma','CE_Theta','CE_Vega','CE_Rho','CE_oi','CE_coi','CE_v','CE_IV','CE_lp','CE_pc','CE_bq1','CE_bp1','CE_sp1','CE_sq1','strike','PE_bq1','PE_bp1','PE_sp1','PE_sq1','PE_pc','PE_lp','PE_IV','PE_v','PE_coi','PE_oi','PE_Rho','PE_Vega','PE_Theta','PE_Gamma','PE_Delta'], axis=1)
                                
                                SpotPrice = float(spot_ltp)
                                FuturePrice = float(future_ltp)
                                ExpiryDateTime = dt(expiry_input.year, expiry_input.month, expiry_input.day, 0, 0, 0)
                                
                                #print(f"SpotPrice = {SpotPrice}, FuturePrice={FuturePrice}, AtmStrike={AtmStrike}, AtmStrikeCallPrice={AtmStrikeCallPrice}, AtmStrikePutPrice={AtmStrikePutPrice}, ExpiryDateTime={ExpiryDateTime}")
                                
                                ExpiryType = oci_pro.range("F7").value
                                GreekMatch = oci_pro.range("F8").value
                                
                                if ExpiryType == 'WEEKLY':
                                    ExpiryDateType = ExpType.WEEKLY
                                else:
                                    ExpiryDateType = ExpType.MONTHLY
                                
                                FromDateTime = dt.now() 
                                if Exchange == 'NFO':
                                    if dt.now().time() > time(15, 30, 0):
                                        FromDateTime = dt(dt.now().year, dt.now().month,dt.now().day, 15, 30, 0)
                                    
                                    
                                if GreekMatch == "SENSIBULL":
                                    tryMatchWith=TryMatchWith.SENSIBULL
                                else:
                                    tryMatchWith=TryMatchWith.NSE
                                
                                dayCountType = DayCountType.CALENDARDAYS
                                
                                IvGreeks = CalcIvGreeks( SpotPrice = SpotPrice,  FuturePrice = FuturePrice, AtmStrike = AtmStrike, AtmStrikeCallPrice = AtmStrikeCallPrice, AtmStrikePutPrice = AtmStrikePutPrice, ExpiryDateTime = ExpiryDateTime, ExpiryDateType = ExpiryDateType, FromDateTime = FromDateTime, tryMatchWith = tryMatchWith, dayCountType = dayCountType)
                
                                #print(f"SpotPrice={SpotPrice}, FuturePrice={FuturePrice},  AtmStrike={AtmStrike}, AtmStrikeCallPrice={AtmStrikeCallPrice}, AtmStrikePutPrice={AtmStrikePutPrice}, ExpiryDateTime={ExpiryDateTime},  ExpiryDateType={ExpiryDateType}, FromDateTime={FromDateTime}, tryMatchWith={tryMatchWith}")
                                
                                for ind in df_oc_pro.index:
                                    
                                    StrikePrice= float(df_oc_pro['strike'][ind])
                                    StrikeCallPrice= float(df_oc_pro['CE_lp'][ind])
                                    StrikePutPrice= float(df_oc_pro['PE_lp'][ind])
                                    #print(f"StrikePrice={StrikePrice}, StrikeCallPrice={StrikeCallPrice}, StrikePutPrice={StrikePutPrice}")
                                    Greeks = IvGreeks.GetImpVolAndGreeks( StrikePrice = StrikePrice, StrikeCallPrice = StrikeCallPrice, StrikePutPrice = StrikePutPrice)
                                    #print(Greeks)
                                    
                                    df_oc_pro['CE_Delta'][ind] = round(Greeks["CallDelta"],2)
                                    df_oc_pro['CE_Gamma'][ind] = round(Greeks["Gamma"],4)
                                    df_oc_pro['CE_Theta'][ind] = round(Greeks["Theta"],2)
                                    df_oc_pro['CE_Vega'][ind] = round(Greeks["Vega"],2)
                                    df_oc_pro['CE_Rho'][ind] = round(Greeks["RhoCall"],4)
                                    
                                    
                                    df_oc_pro['PE_Delta'][ind] = round(Greeks["PutDelta"],2)
                                    df_oc_pro['PE_Gamma'][ind] = round(Greeks["Gamma"],4)
                                    df_oc_pro['PE_Theta'][ind] = round(Greeks["Theta"],2)
                                    df_oc_pro['PE_Vega'][ind] = round(Greeks["Vega"],2)
                                    df_oc_pro['PE_Rho'][ind] = round(Greeks["RhoPut"],4)
                                    
                                    if GreekMatch == "NSE":
                                        df_oc_pro['CE_IV'][ind] = round(Greeks["CallIV"],2)
                                        df_oc_pro['PE_IV'][ind] = round(Greeks["PutIV"],2)
                                    else:
                                        df_oc_pro['CE_IV'][ind] = round(Greeks["ImplVol"],2)
                                        df_oc_pro['PE_IV'][ind] = round(Greeks["ImplVol"],2)
                                
                                del IvGreeks
                                df_oc_pro.round({"CE_lp":2, 'PE_lp':2})
                                #print(df_oc_pro)
                                
                                if pre_selected_NoOfStrike != NoOfStrike:
                                    pre_selected_NoOfStrike = NoOfStrike
                                    Option_Chain_Pro_Output.range('a3:ae500').value = None
                                    Option_Chain_Pro_Output.range(f"a3:AE500").color = (255,255,255)
                                    Option_Chain_Pro_Output.range(f"p3:p{2 * int(NoOfStrike) + 3}").color = (46,132,198) 
                                
                                df_oc_pro = df_oc_pro.reset_index(drop = True)
                                ATM_pos = df_oc_pro[df_oc_pro.strike == AtmStrike].index.values[0]
                                if NoOfStrike * 2 < len(df_oc_pro):
                                    df_oc_pro = df_oc_pro.iloc[ATM_pos - int(NoOfStrike) : ATM_pos + int(NoOfStrike) + 1]
                                    ATM_Row = int(NoOfStrike) + 3
                                    Option_Chain_Pro_Output.range(f"a{ATM_Row}:AE{ATM_Row}").color = (46,132,198)
                        
                                Option_Chain_Pro_Output.range('a3').options(index=False,header=False).value = df_oc_pro
                                
                            except Exception as e:
                                Option_Chain_Pro_Output.range('a3:ae500').value = None
                                oci_pro.range('I3:J18').value = None
                                Message = "Please check the all provided detail to load option chain:" + str(e)
                                print(Message)
                                oci_pro.range("F4").value = Message
                                
                        else:
                            Option_Chain_Pro_Output.range('a3:ae500').value = None
                            oci_pro.range('I3:J18').value = None
                            Message = "Please enter correct expiry in dd-mm-YYYY (date format)"
                            print(Message)
                            oci_pro.range("F4").value = Message
                            
                    else:
                        Option_Chain_Pro_Output.range('a3:ae500').value = None
                        oci_pro.range('I3:J18').value = None
                        Message = "Please enter the expiry"
                        print(Message)
                        oci_pro.range("F4").value = Message
                        
                else:
                    Option_Chain_Pro_Output.range('a3:ae500').value = None
                    oci_pro.range('I3:J18').value = None
                    Message = "Please enter correct symbol"
                    print(Message)
                    oci_pro.range("F3").value = Message
                    oci_pro.range("F4").value = None
                    oci_pro.range("b2:c100").value = None
                    
            else:
                Option_Chain_Pro_Output.range('a3:ae500').value = None
                oci_pro.range('I3:J18').value = None
                Message = "Please enter the symbol"
                print(Message)
                oci_pro.range("F3").value = Message
                oci_pro.range("F4").value = None
                oci_pro.range("b2:c100").value = None
                
        except Exception as e:
            print(f"Excption : {e}")
            pass
        sleep(int(IterationSleep))

def CloseTrade():
    global api
    print("I am inside CloseTrade")
    try:
        df_open_position_net,OverAllPnL  = get_position()

        price_type = "MKT"
        price = 0.0
        
        df_open_position_net['Net Quantity'] = df_open_position_net['Net Quantity'].astype('int')
        df_open_position_net = df_open_position_net[df_open_position_net['Net Quantity']!= 0 ] #make it 0 
        
        if(len(df_open_position_net) > 0):
            
            df_position_net_short = df_open_position_net[df_open_position_net['Net Quantity'] < 0]
            
            if(len(df_position_net_short) > 0 ):
                for ind in df_position_net_short.index:

                    try:
                    
                        api.place_order(
                            buy_or_sell='B',
                            product_type=df_position_net_short['Product'][ind],
                            exchange=df_position_net_short['Exchange'][ind],
                            tradingsymbol=df_position_net_short['Symbol'][ind],
                            quantity=abs(df_position_net_short['Net Quantity'][ind]),
                            discloseqty=0,
                            price_type=price_type,
                            price=price,
                            trigger_price=None,
                            retention="DAY",
                            remarks="Python_Trader_UserSelected_squareoff",
                        )
                    
                    except Exception as e:
                        Message =  str(e) + " : Exception occur in Finvasia order placement"
                        print(Message) 
            
            df_position_net_long = df_open_position_net[df_open_position_net['Net Quantity'] > 0]
            
            if(len(df_position_net_long) > 0 ):
                for ind in df_position_net_long.index:
                    
                    try:
                        api.place_order(
                            buy_or_sell='S',
                            product_type=df_position_net_long['Product'][ind],
                            exchange=df_position_net_long['Exchange'][ind],
                            tradingsymbol=df_position_net_long['Symbol'][ind],
                            quantity=abs(df_position_net_long['Net Quantity'][ind]),
                            discloseqty=0,
                            price_type=price_type,
                            price=price,
                            trigger_price=None,
                            retention="DAY",
                            remarks="Python_Trader_UserSelected_squareoff",)
                        
                    except Exception as e:
                        Message =  str(e) + " : Exception occur in finvasia order placement"
                        print(Message)

        else:
            Message = "No Open position found"
            print(Message)
    except Exception as e:
        print(f"Exception occur in CloseTrade : {e}")

df_orderbook = pd.DataFrame()
df_openPosition = pd.DataFrame()

def start_Open_Position():
    
    excel_op = xw.Book(TerminalSheetName)
    op_op = excel_op.sheets("OpenPosition")
    op_tt = excel_op.sheets("Trade_Terminal")
    op_hold = excel_op.sheets("Holdings")
    op_config = excel_op.sheets("Config")
    op_ob = excel_op.sheets("OrderBook")
    
    isTelegramEnable = False
    if op_config.range("b3").value == True:
        isTelegramEnable = True
    
    isVoiceEnable = False
    if op_config.range("b6").value == True:
        isVoiceEnable = True
        
    op_op.range(f"d2").value = False
    op_op.range(f"e2").value = 0
    op_op.range("b3:az1000").value = None
    op_hold.range("a1:w500").value  = None
        
    op_ob.range("b1:az100").value = None
    
    global LimitOrderBook
    global df_orderbook, df_openPosition
    global api
    global Telegram_Message, Voice_Message
    
    while True:
        try:
            get_limits = api.get_limits()
            
            op_tt.range("a2").value = get_limits['cash']
            try:
                op_tt.range("b2").value = get_limits['marginused']
                op_tt.range("c2").value = get_limits['expo']
                op_tt.range("d2").value = get_limits['span']
            except Exception as e:
                op_tt.range("b2").value = 0
                op_tt.range("c2").value = 0
                op_tt.range("d2").value = 0
             
             
            df_openPosition, OverAllPnL  = get_position()
            if(len(df_openPosition) > 0):
                op_op.range(f"a2").value = OverAllPnL
                op_tt.range(f"f2").value = OverAllPnL
                if excel_op.sheets.active.name == "OpenPosition":
                    op_op.range("b3").options(index=False,header=True).value = df_openPosition
                    
                    KillSwitch = op_op.range(f"d2").value
                    Reconfirm = int(op_op.range(f"e2").value)
                    #print(f"KillSwitch={KillSwitch} Reconfirm={Reconfirm}")
                    if(KillSwitch == 'Execute' and Reconfirm == 1):
                        CloseTrade()        
                        op_op.range(f"d2").value = False
                        op_op.range(f"e2").value = 0
                    else:
                        
                        #print("check if any open order needs to be cancel")
                        UserAction = op_op.range(f"a{4}:a{3 + len(df_openPosition)}").value
                        #print(f"UserAction = {UserAction}")
                        if UserAction != None:
                            for i in range (0,len(df_openPosition)):
                                #print(f"{i} : {UserAction[i]}")
                                if UserAction[i] == 'Square_Off' or UserAction[i] == 'S':
                                    #print(f"*{df_openPosition}**")
                                    exchange = df_openPosition['Exchange'][i]
                                    tradingsymbol = df_openPosition['Symbol'][i]
                                    if df_openPosition['Net Quantity'][i] < 0:
                                        buy_or_sell = 'B'
                                    else:
                                        buy_or_sell = 'S'
                                    quantity = abs(df_openPosition['Net Quantity'][i])
                                    product_type = df_openPosition['Product'][i]
                                    tag = "PT individual Exit"
                                    price_type = "MKT"
                                    price = 0.0
                                    #print(f"squareoff {tradingsymbol}  {exchange} {product} {quantity}{transaction_type}")
                                    try:
                                        api.place_order(buy_or_sell=buy_or_sell,product_type=product_type,exchange=exchange,tradingsymbol=tradingsymbol,quantity=quantity,discloseqty=0,price_type=price_type,price=price,trigger_price=None,retention="DAY",remarks=tag)
                                    except Exception as e:
                                        print(f"squareoff can't be done for {df_openPosition['Symbol'][i]} with reason : {e}")
                                        pass
                                    op_op.range(f"a{4+i}").value = None
        except Exception as e:
            print(f"Exception occur in OpenPosition : {e}")
            pass
        
        try:
            if excel_op.sheets.active.name == "Holdings":
                df_holding = getholdings()
                if(len(df_holding)> 0 ):
                    op_hold.range("a1").options(index=False,header=True).value = df_holding
        except Exception as e:
            print(f"Exception occur in Holdings : {e}")
            pass
        
        try:
            if excel_op.sheets.active.name == "OrderBook":
                df_orderbook = get_order_book()
                if (len(df_orderbook) > 0):
                    op_ob.range("b1").options(index=False,header=True).value = df_orderbook
                    
                    
                    #check if any open order needs to be cancel
                    UserAction = op_ob.range(f"a{2}:a{1 + len(df_orderbook)}").value
                    #print(f"UserAction = {UserAction}")
                    if UserAction is not None:
                        for i in range (0,len(df_orderbook)):
                            #print(f"{i} : {UserAction[i]}")
                            if UserAction[i] == 'CANCEL' or UserAction[i] == 'C':
                                #print(f"Cancel order id {df_orderbook['order_id'][i]} variety {df_orderbook['variety'][i]}")
                                try:
                                    api.cancel_order(orderno=df_orderbook['Order No'][i])
                                except Exception as e:
                                    print(f"Order can't be cancelled with reason : {e}")
                                    pass
                                op_ob.range(f"a{2+i}").value = None
                
        except Exception as e:
            print(f"Exception occur in OrderBook : {e}")
            pass
        
        try:
            
            for key, value in LimitOrderBook.items():
                #print(f"check status of {key} , having current status {value['status']}")
                if value['status'] == 'PENDING':
                    status, Executed_price = order_status (key)
                    #print(f"filled_quantity = {filled_quantity},Executed_price={Executed_price}, status={status} ")
                    if status == 'COMPLETE':
                        value['status'] = 'COMPLETE'
                        value['Executed_price'] = Executed_price
                    elif status in ['CANCELLED','CANCELED']:
                        value['status'] = 'CANCELLED'
                    elif status in ['REJECTED']:
                        value['status'] = 'REJECTED'
                    value['Remarks'] = status
                        
                #print(f"current status of {key} , with updated values {value['status']}")        
                
        except Exception as e:
            #print(f"Exception in open position during LimitOrderBook checking : {e}")
            pass
        
        #print(f"Telegram_Message queue = {Telegram_Message}")
        try:
            if isTelegramEnable:
                if len(Telegram_Message) > 0:
                    SendMessageToTelegram(Telegram_Message[0])
                    del Telegram_Message[0]
        
        
        except Exception as e:
            #print(f"Exception in telegram Send Message : {e}")
            pass
            
        try:
            if isVoiceEnable:
                if len(Voice_Message) > 0:
                    Text2Speech(Voice_Message[0])
                    del Voice_Message[0]
        
        
        except Exception as e:
            #print(f"Exception in voice Message : {e}")
            pass
                
def getholdings():  
    #print("I am inside getholdings")
    global api

    df_holding = pd.DataFrame(columns=['Exchange','Name','Holding quantity','Non Poa display quantity','Average price'])
    holdings = api.get_holdings()
    for holding in holdings:
        exch = holding['exch_tsym'][0]['exch']
        tsym = holding['exch_tsym'][0]['tsym']
        holdqty = holding['holdqty']
        try:
            npoadqty = holding['npoadqty']
        except Exception as e:
            npoadqty = 0
        upldprc = holding['upldprc']
        
        #print(f"{exch} {tsym} {holdqty} {npoadqty} {upldprc}")
        dic_data = {'Exchange':exch,'Name':tsym,'Holding quantity':holdqty,'Non Poa display quantity':npoadqty,'Average price':upldprc}
        df_holding = pd.concat([df_holding, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
    return df_holding
    
def StartThread():
    excel_name = xw.Book(TerminalSheetName)
    Config_sheet = excel_name.sheets['Config']
    try:
        
        # Define the threads and put them in an array
        threads = []
        
        if Config_sheet.range("b2").value == True:
            threads.append(Thread(target=start_Trade_Terminal))
            threads.append(Thread(target=start_Open_Position))      
           
        if Config_sheet.range("b4").value == True:
            threads.append(Thread(target=start_optionchain))
        
        if Config_sheet.range("b5").value == True:
            threads.append(Thread(target=start_optionchain_Pro))
            
        if len(threads) != 0 :    
            # Func1 and Func2 run in separate threads
            for thread in threads:
                thread.start()

            # Wait until both Func1 and Func2 have finished
            for thread in threads:
                thread.join()
        else:
            print("Please select atlease one feature in config and restart the algo")
        
    except Exception as e:
        Message = str(e) + " : Exception occur"
        print(Message)

print(f"Python Trader Excel Based Terminal program initialised")
if Shoonya_login() == 1:
    LoadInstrument_token()
    
    
    api.start_websocket(
        order_update_callback=event_handler_order_update,
        subscribe_callback=event_handler_quote_update,
        socket_open_callback=open_callback,
        socket_close_callback=event_handler_socket_closed,
    )

    while feed_opened == False:
        print("Trying to connect WebSocket...")
        pass

    print("Connected to WebSocket...")

    StartThread()
    print("Enjoy the automation...")
else:
    print("\n\nAlgo is not able to login using your given credential. Please follow below steps in matrix wise.\n\n1. Check entered userid/password/apikey/otp is correct or not. Try to login your finvasia account using same credential.\n2. If issue still exist please regenerate your password and api key and update the sheet.\n3. If you are using algo first time, please wait for 24 hours to activate your api.\n4. If issue still exist please contact to finvasia support team.")

Finvasia/Finvasia_Core_V3_002.py
Indian-Algorithmic-Trading-Community/PythonTraderExcelTradeTerminal
# -*- coding: utf-8 -*-
"""
Created on Fri Oct 14 09:13:23 2022

This tool is to fetch two option chain (of any segment/symbol) data and trade terminal sheet, where all input comes using web socket data which leads to low lag and no api restriction. 

Contact details :
Telegram Channel:  https://t.me/pythontrader
Developer Telegram ID : https://t.me/pythontrader_admin
Gmail ID:   mnkumar2020@gmail.com 
Whatsapp : 9470669031 
Youtube : youtube.com/@pythontraders

Disclaimer: The information provided by the Python Traders channel is for educational purposes only, so please contact your financial adviser before placing your trade. Developer is not responsible for any profit/loss happened due to coding, logical or any type of error.


Toots Feature :
1. Trade terminal where you can take paper/real trade.
2. You can set target/sl together using tool.
3. You can also trail your sl.
4. Tool has voice and telegram notification/alert feature.
5. Tools have orderbook sheet where you can cancel any open orders.
6. Tools have openposition sheet where you can exit any/all running trade.
7. Tools has two option chain and each option chain contains live data with option greeks.
"""


Market_Safety = 1

import warnings
warnings.filterwarnings("ignore") 
from kiteext import KiteExt
import os, json, sys
from datetime import datetime as dt, timedelta, time
from time import sleep
import logging
import copy 
import threading
from threading import Thread
import platform

#print(f"001 :{dt.now()}")

try:
    from tzlocal import get_localzone
except (ModuleNotFoundError, ImportError):
    print("tzlocal module not found")
    os.system(f"{sys.executable} -m pip install -U tzlocal")
finally:
    from tzlocal import get_localzone
    
try:
    import numpy as np 
except (ModuleNotFoundError, ImportError):
    print("numpy module not found")
    os.system(f"{sys.executable} -m pip install -U numpy")
finally:
    import numpy as np 
    
try:
    import requests
except (ModuleNotFoundError, ImportError):
    print("requests module not found")
    os.system(f"{sys.executable} -m pip install -U requests")
finally:
    import requests


try:
    from GetIVGreeks import DayCountType, ExpType, TryMatchWith, CalcIvGreeks
except ImportError:
    exec(
        __import__("requests")
        .get(
            "https://gist.githubusercontent.com/ShabbirHasan1"
            + "/7695687d87053c7e3df46810ab2e3046"
            + "/raw/60b2baf44b4801dd91ba583c85076f2605783a4b"
            + "/GetIVGreeks.py"
        )
        .text
    )
    
    
    
try:
    import pyotp
except (ModuleNotFoundError, ImportError):
    print("pyotp module not found")
    os.system(f"{sys.executable} -m pip install -U pyotp")
finally:
    import pyotp
    
try:
    import xlwings as xw
except (ModuleNotFoundError, ImportError):
    print("xlwings module not found")
    os.system(f"{sys.executable} -m pip install -U xlwings")
finally:
    import xlwings as xw

try:
    import pyttsx3
except (ModuleNotFoundError, ImportError):
    #print("pyttsx3 module not found")
    os.system(f"{sys.executable} -m pip install -U pyttsx3")
finally:
    import pyttsx3

try:
    import psutil
except (ModuleNotFoundError, ImportError):
    print("psutil module not found")
    os.system(f"{sys.executable} -m pip install -U psutil")
finally:
    import psutil
    
try:
    import pandas as pd
except (ModuleNotFoundError, ImportError):
    print("pandas module not found")
    os.system(f"{sys.executable} -m pip install -U pandas")
finally:
    import pandas as pd
    

print("HI welcome")

def Text2Speech(Text):
    global engine
    try:
        engine = pyttsx3.init()
        voices = engine.getProperty('voices')
        try:
           engine.setProperty('voice', voices[1].id)
        except Exception as e:
            pass
        engine.setProperty('rate', 160)
        engine.say(Text)
        engine.runAndWait()
        del engine
        
    except Exception as e:
        #print(f"Issue in voice module : {e}")
        pass
    
    
Text2Speech("Welcome to python trader trade terminal tool, have a nice day. Hope u r enjoying our work")

TradeTerminalFileName = 'Zerodha_Trade_Terminal_V3.xlsm'
#logging.basicConfig(level=logging.DEBUG)

def Zerodha_login():
    print(f"I am inside Zerodha_login")
    global kite, excel_master
    global TelegramBotCredential, ReceiverTelegramID
    global UserID, client_name
    
    isConnected = 0
    Credential_sheet = excel_master.sheets['User_Credential']
    try:
        TelegramBotCredential = str(Credential_sheet.range("b8").value)
        ReceiverTelegramID = str(Credential_sheet.range("b9").value)
        index = ReceiverTelegramID.find(".")
        if index != -1:
            ReceiverTelegramID = ReceiverTelegramID[:len(ReceiverTelegramID)-2]
        
        kite = KiteExt()

        Credential_sheet.range('a1').value = 'Welcome To Python Trader'
        Credential_sheet.range('a1').color = (46,132,198)
        Credential_sheet.range('a11').value = 'Tool developed by PythonTrader, Please follow us on social media site for more tools or freelancing work'
        Credential_sheet.range('a11').color = (46,132,198)
        Credential_sheet.range('b12').value = 'https://www.youtube.com/@pythontraders'
        Credential_sheet.range('b13').value =  'https://www.t.me/pythontrader'
        Credential_sheet.range('b12:b13').color = (220,214,32)
        
        UserID = Credential_sheet.range('B2').value.strip()

        LoginMethod = str(Credential_sheet.range("B3").value)
        if (LoginMethod == "New_Session"):
            Password = str(Credential_sheet.range('B4').value)
            
            index = Password.find(".")
            if index != -1:
                Password = Password[:len(Password)-2]

            Totp = str(Credential_sheet.range('B5').value)
            index = Totp.find(".")
            if index != -1:
                twoFA = int(Totp[:6])
            else:
                pin = pyotp.TOTP(Totp).now()
                twoFA = f"{int(pin):06d}" if len(pin) <=5 else pin    
            
            print(f"UserID={UserID},Password={Password},Totp={Totp},twoFA={twoFA}")
            
            enctoken, public_token = kite.login_with_credentials(userid=UserID, password=Password, twofa=twoFA)
            
            #print(f"enctoken = ({enctoken}), public_token = ({public_token}) ")

        else:
        
            enctoken = str(Credential_sheet.range('B6').value)
            #print(f"UserID=({UserID}), enctoken=({enctoken})")
            kite.login_using_enctoken(userid=UserID, enctoken=enctoken, public_token=None) 
        
        Profile = kite.profile()
        print(Profile)
        client_name = Profile.get('user_name')
        Welcome_Message = "Login Successful, Welcome " + client_name + "\nTool Validity : Demo" + "\nToken = (" + str(enctoken) + ")"
        
        UserID = Profile['user_id']
        Text2Speech("Login Successful, Welcome " + str(client_name) )
        
        Credential_sheet.range('c2').value = Welcome_Message
        Credential_sheet.range('c2').color = (118,224,280)
        isConnected = 1
        
    except Exception as e:
        print(f"Error : {e}")
        Credential_sheet.range('c2').value = 'Wrong credential'
        Text2Speech("Login Unsuccessful due to wrong or expired credential/token , kindly check your provided details")
        Credential_sheet.range('c2').color = (255, 0, 0)

    return isConnected
    
SYMBOLDICT = {}
live_data = {}
Token_yet_to_subscribe = []
Telegram_Message = ["Welcome to Python Trader excel based trade terminal","Have a Good Day"]
Voice_Message = []
TelegramBotCredential = None
ReceiverTelegramID = None

def SendMessageToTelegram(Message):
    #print(f"SendMessageToTelegram = {Message}")
    global TelegramBotCredential, ReceiverTelegramID
    try:
        Url = "https://api.telegram.org/bot" + str(TelegramBotCredential) +  "/sendMessage?chat_id=" + str(ReceiverTelegramID)
        
        textdata ={ "text":Message}
        response = requests.request("POST",Url,params=textdata)
    except Exception as e:
        #print(f"SendMessageToTelegram exception occur : {e}")
        pass
            
def on_ticks(ws, ticks):
    # Callback to receive ticks.
    global Token_yet_to_subscribe, live_data
    #print("Ticks data: {}".format(ticks))
    
    for stock in ticks:
        #print(f"inside tick for loop : {stock}")
        try:
            volume = stock["volume_traded"]
        except:
            volume = 0
        try:
            OI = stock["oi"]
        except:
            OI = 0
        try:
            Vwap = stock["average_traded_price"]
        except:
            Vwap = 0
        try:
            bp1 = stock["depth"]["buy"][0]["price"]
        except:
            bp1 = 0
        try:
            sp1 = stock["depth"]["sell"][0]["price"]
        except:
            sp1 = 0        
        live_data[stock['instrument_token']] = {"Open": stock["ohlc"]["open"],
                                                                      "High": stock["ohlc"]["high"],
                                                                      "Low": stock["ohlc"]["low"],
                                                                      "Close": stock["ohlc"]["close"],
                                                                      "LTP": stock["last_price"],
                                                                      "Volume": volume,
                                                                      "OI": OI,
                                                                      "Vwap": Vwap,
                                                                      "change" : stock["change"],
                                                                      "bp1":bp1,
                                                                      "sp1":sp1}
        #print(f"after appending live data became : {live_data}")
    if(len(Token_yet_to_subscribe) > 0):
        try:
            #print(f"New symbol found, so going to subscribe, token= {Token_yet_to_subscribe}")
            kws.subscribe(Token_yet_to_subscribe)
            kws.set_mode(ws.MODE_FULL, Token_yet_to_subscribe)
            Token_yet_to_subscribe = []
        except Exception as e:
            #print(str(e)+":Exception in on_ticks")
            pass
            
def on_connect(ws, response):
    global Initial_Subscribr_TokenList
    # Callback on successful connect.
    #mode available MODE_FULL,MODE_LTP,MODE_QUOTE
    ws.subscribe(Initial_Subscribr_TokenList)
    ws.set_mode(ws.MODE_FULL, Initial_Subscribr_TokenList)

def on_error(ws, code, reason):
    logging.error('Ticker errored out. code = %d, reason = %s', code, reason)

def on_close(ws, code, reason):
    # On connection close stop the event loop.
    # Reconnection will not happen after executing `ws.stop()`
    ws.stop()

def on_order_update(ws, data):
    logging.info('Ticker: order update %s', data)

# Callback when connection closed with error.
def on_error(ws, code, reason):
    logging.info("Connection error: {code} - {reason}".format(code=code, reason=reason))


# Callback when reconnect is on progress
def on_reconnect(ws, attempts_count):
    logging.info("Reconnecting: {}".format(attempts_count))


# Callback when all reconnect failed (exhausted max retries)
def on_noreconnect(ws):
    logging.info("Reconnect failed.")
    
def stop_ticker():
    logging.info('Ticker: stopping..')
    kws.close(1000, "Manual close")

def on_max_reconnect_attempts(ws):
    logging.error('Ticker max auto reconnects attempted and giving up..')

def GetToken(exchange,tradingsymbol):
    global df_instrument
    
    Token = df_instrument[(df_instrument.tradingsymbol == tradingsymbol) & (df_instrument.exchange == exchange)].iloc[0]['instrument_token']
    
    return Token

def order_status (orderid):
    #print(f"I am at order_status function with order id {orderid}")
    filled_quantity = 0
    AverageExecutedPrice = 0
    global kite
    try:
        order_history = kite.order_history(orderid)
        filled_quantity = order_history[-1].get('filled_quantity')
        AverageExecutedPrice = order_history[-1].get('average_price')
        status = order_history[-1].get('status')
    except Exception as e:
        Message = str(e) + " : Exception occur in order_status"
        #print(Message)
        pass
    return filled_quantity, AverageExecutedPrice, status
    
def place_trade(tradingsymbol_exchange,quantity,transaction_type , order_type = None, price = None):
    
    global kite
    global Product_type
    try:
        exchange = tradingsymbol_exchange[:3]
        tradingsymbol = tradingsymbol_exchange[4:]
              
        if(Product_type == 'MIS'):
            product = kite.PRODUCT_MIS
        else:
            if(exchange == 'NSE'):
                product = kite.PRODUCT_CNC
            else:
                product = kite.PRODUCT_NRML
        
            
        variety =  kite.VARIETY_REGULAR
        
        
        if order_type == 'MARKET':
            price = 0
            trigger_price = None
        elif order_type == 'LIMIT':
            trigger_price = None
        elif order_type == 'SL-M':
            order_type = 'SL'
            trigger_price = price
                
            if transaction_type == 'BUY':
                price = price * (100 + Market_Safety) / 100
                if exchange == 'CDS':
                    round_to = .0025
                    result = ( int(price / round_to) + 1) * round_to
                    price = float( "{:.4f}".format(result) )
                else:
                    round_to = .1
                    result = ( int(price / round_to) + 1) * round_to
                    price = float( "{:.1f}".format(result) )
            else:
                price = price * (100 - Market_Safety) / 100
                if exchange == 'CDS':
                    round_to = .0025
                    result = ( int(price / round_to) ) * round_to
                    price = float( "{:.4f}".format(result) )
                else:
                    round_to = .1
                    result = ( int(price / round_to) ) * round_to
                    price = float( "{:.1f}".format(result) )
            
        
        Message = "Order placed for " + str (tradingsymbol) + " " + str(quantity) + " quantity " + str(transaction_type)
        print(Message)
        Telegram_Message.append(Message)
        Voice_Message.append(Message)
    
        order_id = None
        order_id = kite.place_order(tradingsymbol = tradingsymbol,
                                exchange = exchange,
                                transaction_type = transaction_type,
                                quantity = int(quantity),
                                price = price,
                                trigger_price=trigger_price,
                                variety = variety,
                                order_type = order_type,
                                product = product)
        print(f"Order id = {order_id}")
        
       
    except Exception as e:
        Message = "Order rejected with error : " + str(e)
        print(Message)
        Telegram_Message.append(Message)
    return order_id

def GetMarginDetail(segment  = 'equity'):
    global kite
    Fund_Detail = kite.margins()
    Available_Fund = Fund_Detail[segment]['net']
    Exposure_Margin = Fund_Detail[segment]['utilised']['exposure']
    SPAN_Margin = Fund_Detail[segment]['utilised']['span']
    
    return float(Available_Fund),float(Exposure_Margin),float(SPAN_Margin)

df_orderbook = pd.DataFrame()
df_openPosition = pd.DataFrame()
def start_Open_Position():
    
    excel_op = xw.Book(TradeTerminalFileName)
    op_op = excel_op.sheets("OpenPosition")
    op_tt = excel_op.sheets("Trade_Terminal")
    op_config = excel_op.sheets("Config")
    
    isTelegramEnable = False
    if op_config.range("b3").value == True:
        isTelegramEnable = True
    
    isVoiceEnable = False
    if op_config.range("b6").value == True:
        isVoiceEnable = True
        
    op_tt.range(f"r2").value = 0
    op_op.range(f"a2").value = 0
    op_hold = excel_op.sheets("Holdings")
    op_op.range("b3:k500").value  = None
    op_hold.range("a1:w500").value  = None
    op_ob = excel_op.sheets("OrderBook")
    op_ob.range("b1:p500").value = None
    op_ob.range("a2:a500").value = None
    op_op.range("a4:a100").value = None
    global LimitOrderBook
    global df_orderbook, df_openPosition
    global kite
    global Telegram_Message, Voice_Message
    
    while True:
        try:
            #print("start_Open_Position thread running")
            Available_Fund,Exposure_Margin,SPAN_Margin = GetMarginDetail()
                
            op_tt.range("a2").value = Available_Fund
            op_tt.range("b2").value = Exposure_Margin
            op_tt.range("c2").value = SPAN_Margin
            
            Available_Fund,Exposure_Margin,SPAN_Margin = GetMarginDetail('commodity')
            
            op_tt.range("e2").value = Available_Fund
            op_tt.range("f2").value = Exposure_Margin
            op_tt.range("g2").value = SPAN_Margin
                
                
            df_openPosition = get_position()
            #print(df_openPosition)
            if(len(df_openPosition) > 0):
                OverAllPnL = GetOverAllPnL()
                op_tt.range(f"r2").value = OverAllPnL
                op_op.range(f"a2").value = OverAllPnL
                if excel_op.sheets.active.name == "OpenPosition":
                    df_openPosition = RemoveUnwantedColumn(df_openPosition)
                    op_op.range("b3").options(index=False,header=True).value = df_openPosition
                    
                    KillSwitch = op_op.range(f"d2").value
                    Reconfirm = int(op_op.range(f"e2").value)
                    if(KillSwitch == 'Execute' and Reconfirm == 1):
                        isClosed = CloseTrade()        
                        op_op.range(f"d2").value = False
                        op_op.range(f"e2").value = 0
                    else:
                        #print("check if any open order needs to be cancel")
                        UserAction = op_op.range(f"a{4}:a{3 + len(df_openPosition)}").value
                        #print(f"UserAction = {UserAction}")
                        for i in range (0,len(df_openPosition)):
                            #print(f"{i} : {UserAction[i]}")
                            if UserAction[i] == 'Square_Off' or UserAction[i] == 'S':
                                exchange = df_openPosition['exchange'][i]
                                tradingsymbol = df_openPosition['tradingsymbol'][i]
                                product = df_openPosition['product'][i]
                                if df_openPosition['quantity'][i] < 0:
                                    transaction_type = 'BUY'
                                else:
                                    transaction_type = 'SELL'
                                quantity = abs(df_openPosition['quantity'][i])
                                tag = "PT user Exit"
                                #print(f"squareoff {tradingsymbol}  {exchange} {product} {quantity}{transaction_type}")
                                try:
                                    kite.place_order(variety = 'regular',
                                        exchange = exchange,
                                        tradingsymbol = tradingsymbol,
                                        transaction_type = transaction_type,
                                        quantity = quantity,
                                        product = product,
                                        order_type = 'MARKET',
                                        tag = tag)
                                except Exception as e:
                                    #print(f"squareoff can't be done for {df_openPosition['tradingsymbol'][i]} with reason : {e}")
                                    pass
                                op_op.range(f"a{4+i}").value = None
                                
            if excel_op.sheets.active.name == "Holdings":
                df_holdings = getholdings()
                if(len(df_holdings) > 0):
                    op_hold.range("a1").options(index=False,header=True).value = df_holdings
            
            
            if excel_op.sheets.active.name == "OrderBook":
                df_orderbook = get_order_book()
                #print(df_orderbook)
                if(len(df_orderbook) > 0):
                    op_ob.range("b1").options(index=False).value = df_orderbook
                    #check if any open order needs to be cancel
                    UserAction = op_ob.range(f"a{2}:a{1 + len(df_orderbook)}").value
                    #print(f"UserAction = {UserAction}")
                    if UserAction is not None:
                        for i in range (0,len(df_orderbook)):
                            #print(f"{i} : {UserAction[i]}")
                            if UserAction[i] == 'CANCEL' or UserAction[i] == 'C':
                                #print(f"Cancel order id {df_orderbook['order_id'][i]} variety {df_orderbook['variety'][i]}")
                                try:
                                    kite.cancel_order(variety=df_orderbook['variety'][i], order_id=df_orderbook['order_id'][i])
                                except Exception as e:
                                    print(f"Order can't be cancelled with reason : {e}")
                                    pass
                                op_ob.range(f"a{2+i}").value = None

        except Exception as e:
            #print(f"Exception in open position {e}")
            pass
        #sleep(5)
        try:
            
            for key, value in LimitOrderBook.items():
                #print(f"check status of {key} , having current status {value['status']}")
                if value['status'] == 'PENDING':
                    filled_quantity, Executed_price, status = order_status (key)
                    #print(f"filled_quantity = {filled_quantity},Executed_price={Executed_price}, status={status} ")
                    if status == 'COMPLETE':
                        value['status'] = 'COMPLETE'
                        value['Executed_price'] = Executed_price
                    elif status == 'CANCELLED':
                        value['status'] = 'CANCELLED'
                    value['Remarks'] = status
                        
                #print(f"current status of {key} , with updated values {value['status']}")        
                
        except Exception as e:
            #print(f"Exception in open position during LimitOrderBook checking : {e}")
            pass
        
        #print(f"Telegram_Message queue = {Telegram_Message}")
        try:
            if isTelegramEnable:
                if len(Telegram_Message) > 0:
                    SendMessageToTelegram(Telegram_Message[0])
                    del Telegram_Message[0]
        
        
        except Exception as e:
            #print(f"Exception in telegram Send Message : {e}")
            pass
            
        try:
            if isVoiceEnable:
                if len(Voice_Message) > 0:
                    Text2Speech(Voice_Message[0])
                    del Voice_Message[0]
        
        
        except Exception as e:
            #print(f"Exception in voice Message : {e}")
            pass
        
def getholdings():      
    global kite
    df_holdings = pd.DataFrame()
    try:
        holding = kite.holdings()
        
        df_holdings = pd.DataFrame(holding)
        df_holdings = df_holdings.drop(['authorisation'],axis=1)
    except Exception as e:
        #print(f"Exception in getholdings : {e}")
        pass
    return df_holdings
    
def CloseTrade():
    global kite

    position = kite.positions()
    df_position_net = pd.DataFrame(position['net'])
    
    df_open_position_net = df_position_net[df_position_net.quantity != 0 ]
    
    if(len(df_open_position_net) > 0):
        
        df_position_net_short = df_position_net[df_position_net.quantity < 0]
        
        if(len(df_position_net_short) > 0 ):
            for ind in df_position_net_short.index:

                try:
                
                    kite.place_order(variety = 'regular',
                    exchange = df_position_net_short['exchange'][ind],
                    tradingsymbol = df_position_net_short['tradingsymbol'][ind],
                    transaction_type = 'BUY',
                    quantity = abs(df_position_net_short['quantity'][ind]),
                    product = df_position_net_short['product'][ind],
                    order_type = 'MARKET',
                    tag = "short_exit")
                
                except Exception as e:
                    Message =  str(e) + " : Exception occur in zerodha order placement"
                    print(Message) 
        
        df_position_net_long = df_position_net[df_position_net.quantity > 0]
        
        if(len(df_position_net_long) > 0 ):
            for ind in df_position_net_long.index:
                
                try:
                    kite.place_order(variety = 'regular',
                    exchange = df_position_net_long['exchange'][ind],
                    tradingsymbol = df_position_net_long['tradingsymbol'][ind],
                    transaction_type = 'SELL',
                    quantity = abs(df_position_net_long['quantity'][ind]),
                    product = df_position_net_long['product'][ind],
                    order_type = 'MARKET',
                    tag = "long_exit")
                    
                except Exception as e:
                    Message =  str(e) + " : Exception occur in zerodha order placement"
                    print(Message)

    else:
        Message = "No Open position found"

LimitOrderBook = {}
def start_Trade_Terminal():
    global excel_TT
    excel_TT = xw.Book(TradeTerminalFileName)
    global live_data , kws
    global SYMBOLDICT
    global Token_yet_to_subscribe
    global Product_type
    global Trade_Mode
    global Telegram_Message, Voice_Message
    tt = excel_TT.sheets("Trade_Terminal")
    tt.range("a2:c2").value  = 0
    tt.range("q4:s1000").value  = None
    tt.range("u4:w1000").value  = None
    tt.range("aa4:ac1000").value  = None
    tt.range(f"a3:ac3").value = [ "Symbol", "Open", "High", "Low", "Close", "VWAP", "Best Buy Price",
                                "Best Sell Price","Volume","OI", "LTP","Percentage change", "Qty", "BUY/SELL", "Entry Signal","Entry Limit Price", "Entry Done @","Entry Order ID", "Entry Remarks","Exit Signal","Exit Done @","Exit Order ID","Exit Remarks", "Target","SL" ,"Trail Enable",    "Latest SL","Trade Status","PnL"]
      
    tt.range('k1').value =  'PYTHON TRADER'
    tt.range('k1').color = (46,132,198)      
    Trade_Mode = str(tt.range('s2').value).upper()
    
    AlertMessage = Trade_Mode + " trade mode enabled"
    Telegram_Message.append(AlertMessage)
    Voice_Message.append(AlertMessage)
    
    subs_lst = []
    Symbol_Token = {}
    global LimitOrderBook
    #run a parallel thread to update the status
    while True:
        try:
            #sleep(.5)
            
            Product_type = tt.range(f"P2").value
            
            
            #print(live_data)
            symbols = tt.range(f"a{4}:a{1000}").value
            trading_info = tt.range(f"m{4}:ac{1000}").value
            main_list = []
            
            idx = 0
            for i in symbols:
                lst = [None, None, None, None,None, None, None, None, None,None,None]
                if i:
                    if i not in subs_lst:
                        subs_lst.append(i)
                        try:
                            exchange = i[:3]
                            tradingsymbol = i[4:]
                            Token = GetToken(exchange,tradingsymbol)
                            Symbol_Token[i] =  int(Token)
                            Token_yet_to_subscribe.append(int(Token))
                            print(f"Symbol = {i}, Token={Token} subscribed")
                            
                        except Exception as e:
                            print(f"Subscribe error {i} : {e}")
                    if i in subs_lst:
                        try:
                            TokenKey = Symbol_Token[i]
                            #print(live_data)
                            lst = [live_data[TokenKey].get("Open", "-"),
                                   live_data[TokenKey].get("High", "-"),
                                   live_data[TokenKey].get("Low", "-"),
                                   live_data[TokenKey].get("Close", "-"),
                                   live_data[TokenKey].get("Vwap", "-"),
                                   live_data[TokenKey].get("bp1", "-"),
                                   live_data[TokenKey].get("sp1", "-"),
                                   live_data[TokenKey].get("Volume", "-"),
                                   live_data[TokenKey].get("OI", "-"),
                                   live_data[TokenKey].get("LTP", "-"),
                                   round(live_data[TokenKey].get("change", "0"),2)]
                            try:
                                trade_info = trading_info[idx]
                                idx_location = idx + 2
                                #print(f" {i} : {trade_info}")
                                if trade_info[0] is not None and trade_info[1] is not None:
                                    if type(trade_info[0]) is float and type(trade_info[1]) is str:
                                        
                                        LTP = live_data[TokenKey].get("LTP", 0)
                                        
                                        if Trade_Mode == 'REAL':
                                            #Real trade mode handling will handle here
                                            if trade_info[1].upper() == "BUY" and LTP != 0:
                                                if trade_info[2] in ['True_Market' ,'True_Limit_LTP', 'Limit_Below', 'Limit_Above']:
                                                    
                                                   
                                                    
                                                    #0:Qty    1:BUY/SELL    2:Entry_Signal  3:Entry_Limit_Price  4:Entry_Done@  5:Entry_Order_ID 6: Entry_Remarks  7:Exit_Signal 8:Exit_Done @ 9:Exit_Order_ID   10:Target    11:SL  12:Trail Enable 13: Latest_SL 14:Trade_status 15: PnL
                                                    
                                                    #0:Qty    1:BUY/SELL    2:Entry_Signal  3:Entry_Limit_Price  4:Entry_Done@  5:Entry_Order_ID 6: Entry_Remarks  7:Exit_Signal 8:Exit_Done @ 9:Exit_Order_ID  10: Exit_Remarks 11:Target    12:SL  13:Trail Enable 14: Latest_SL 15:Trade_status 16: PnL
                                                    
            
                                                    if trade_info[15] != 'Active' and trade_info[15] != 'Entry_Pending' and trade_info[15] != 'Exit_Pending' and trade_info[15] != 'Closed' and (trade_info[15] is None or trade_info[15] == ''):
                                                        
                                                        
                                                        if trade_info[2] == 'True_Market':
                                                            #Entry buy trade immediately
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "BUY","MARKET")
                                                            
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else:    
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"q{idx_location + 2}").value = None
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                            
                                                        elif trade_info[2] == 'True_Limit_LTP':    
                                                            #Entry buy trade immediately at ltp price
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "BUY","LIMIT",LTP)
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else: 
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"q{idx_location + 2}").value = None
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                            
                                                        elif trade_info[2] == 'Limit_Below' and trade_info[3] is not None:
                                                            #print(f"LTP={LTP}, trade_info[3] ={trade_info[3]}")
                                                            
                                                            #handling real Limit_Below based trade
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "BUY","LIMIT",trade_info[3])
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else: 
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"q{idx_location + 2}").value = None
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                           
                                                            
                                                        elif trade_info[2] == 'Limit_Above' and trade_info[3] is not None:
                                                            #print(f"LTP={LTP}, trade_info[3] ={trade_info[3]}")
                                                            
                                                            #handling real Limit_Above based trade
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "BUY","SL-M",trade_info[3])
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else: 
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"q{idx_location + 2}").value = None
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                            
                                                    elif trade_info[15] == 'Entry_Pending' :
                                                        Order_id = str(int(trade_info[5]))
                                                        #print(f"Order_id={Order_id}")
                                                        status = LimitOrderBook[Order_id]['status']
                                                        if status == 'COMPLETE':
                                                            Executed_price = LimitOrderBook[Order_id]['Executed_price']
                                                            tt.range(f"q{idx_location + 2}").value = Executed_price
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Active'
                                                        
                                                            Message = str(int(trade_info[0])) + " quantity " + str(i[4:]) + " Bought at " + str(Executed_price)
                                                            
                                                            Telegram_Message.append(Message)
                                                            Voice_Message.append(Message)
                        
                        
                                                        tt.range(f"S{idx_location + 2}").value = LimitOrderBook[Order_id]['Remarks']
                                                        
                                                    elif trade_info[15] == 'Active':
                                                        
                                                        
                                                        PnL = (float(LTP) - float(trade_info[4])) * int(trade_info[0])
                                                        tt.range(f"AC{idx_location + 2}").value = PnL
                                                        #print(f"PnL = {PnL}")
                                                        
                                                        TSL = 0
                                                        if type(trade_info[12]) is float:
                                                            if trade_info[13] == True:
                                                                if(LTP >= trade_info[4]):
                                                                    CUR_TSL = LTP - trade_info[4] + trade_info[12]
                                                                else:
                                                                    CUR_TSL = trade_info[12]
                                                                
                                                                OLD_TSL = trade_info[14]
                                                                if OLD_TSL is not None:
                                                                    TSL = max(CUR_TSL,OLD_TSL)
                                                                else:
                                                                    TSL = CUR_TSL 
                                                            else:
                                                                TSL = trade_info[12]
                                                            
                                                            tt.range(f"AA{idx_location + 2}").value = TSL
                                                        
                                                        if trade_info[7] == 'True_Market':
                                                            #exit buy order immediately
                                                            order_id = place_trade(i, int(trade_info[0]), "SELL","MARKET")
                                                            if order_id is None:
                                                                tt.range(f"T{idx_location + 2}").value = None
                                                            else:  
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"v{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Exit_Pending'
                                                                
                                                        elif trade_info[7] == 'True_Limit_LTP':
                                                            #exit buy at ltp limit 
                                                            order_id = place_trade(i, int(trade_info[0]), "SELL","LIMIT",LTP)
                                                            if order_id is None:
                                                                tt.range(f"T{idx_location + 2}").value = None
                                                            else:  
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"v{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Exit_Pending'
                                                            
                                                        elif type(trade_info[11]) is float and trade_info[11] <= LTP:
                                                            #target meets, so exit the buy order
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "SELL","MARKET")
                                                            
                                                            LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Exit_Pending'
                                                        
                                                        elif TSL >= LTP and type(trade_info[12]) is float:
                                                            #sl meets, so exiting the buy order
                                                            order_id = place_trade(i, int(trade_info[0]), "SELL","MARKET")
                                                            
                                                            LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                            
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Exit_Pending'
                                                            
                                                    
                                                    elif trade_info[15] ==  'Exit_Pending':
                                                        Order_id = str(int(trade_info[9]))
                                                        #print(f"Order_id={Order_id}")
                                                        status = LimitOrderBook[Order_id]['status']
                                                        if status == 'COMPLETE':
                                                            Executed_price = LimitOrderBook[Order_id]['Executed_price']
                                                            tt.range(f"u{idx_location + 2}").value = Executed_price
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Closed'
                                                            
                                                            PnL = (float(Executed_price) - float(trade_info[4])) * int(trade_info[0])
                                                            tt.range(f"AC{idx_location + 2}").value = PnL
                                                            
                                                            Message = str(int(trade_info[0])) + " quantity " + str(i[4:]) + " Sold at " + str(Executed_price) + ", Trade profit or Loss = " + str(round(PnL,2))
                                                            
                                                            Telegram_Message.append(Message)
                                                            Voice_Message.append(Message)
                                                            
                                                        tt.range(f"W{idx_location + 2}").value = LimitOrderBook[Order_id]['Remarks']
                                                        
                                            if trade_info[1].upper() == "SELL" and LTP != 0:
                                                
                                                if trade_info[2] in ['True_Market' , 'True_Limit_LTP' , 'Limit_Below' , 'Limit_Above']:
                                                    #0:Qty    1:BUY/SELL    2:Entry_Signal  3:Entry_Limit_Price  4:Entry_Done@  5:Entry_Order_ID 6: Entry_Remarks  7:Exit_Signal 8:Exit_Done @ 9:Exit_Order_ID   10:Target    11:SL  12:Trail Enable 13: Latest_SL 14:Trade_status 15: PnL
                                                    
                                                    
                                                    if trade_info[15] != 'Active' and trade_info[15] != 'Entry_Pending' and trade_info[15] != 'Exit_Pending' and trade_info[15] != 'Closed' and (trade_info[15] is None or trade_info[15] == '' ):
                                                
                                                        if trade_info[2] == 'True_Market':
                                                            #entry sell order immediately
                                                            order_id = place_trade(i, int(trade_info[0]), "SELL","MARKET")
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else: 
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                            
                                                                tt.range(f"q{idx_location + 2}").value = None
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                                #print("b")
                                                            
                                                        elif trade_info[2] == 'True_Limit_LTP':    
                                                            #Entry buy trade immediately at ltp price
                                                            
                                                            order_id = place_trade(i, int(trade_info[0]), "SELL","LIMIT",LTP)
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else: 
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"q{idx_location + 2}").value = None
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                                
                                                        elif trade_info[2] == 'Limit_Above' and trade_info[3] is not None:
                                                            
                                                            #handling real Limit_Above based trade
                                                            order_id = place_trade(i, int(trade_info[0]), "SELL","LIMIT",trade_info[3])
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else: 
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"q{idx_location + 2}").value = None
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                            
                                                                    
                                                        elif trade_info[2] == 'Limit_Below' and trade_info[3] is not None:
                                                           
                                                            #handling real Limit_Above based trade
                                                            order_id = place_trade(i, int(trade_info[0]), "SELL","SL-M",trade_info[3])
                                                            
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else: 
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"q{idx_location + 2}").value = None
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                            
                                                        
                                                    elif trade_info[15] == 'Entry_Pending' :
                                                        Order_id = str(int(trade_info[5]))
                                                        #print(f"Order_id={Order_id}")
                                                        status = LimitOrderBook[Order_id]['status']
                                                        if status == 'COMPLETE':
                                                            Executed_price = LimitOrderBook[Order_id]['Executed_price']
                                                            tt.range(f"q{idx_location + 2}").value = Executed_price
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Active'
                                                            
                                                            Message = str(int(trade_info[0])) + " quantity " + str(i[4:]) + " Sold at " + str(Executed_price) 
                                                            
                                                            Telegram_Message.append(Message)
                                                            Voice_Message.append(Message)
                                                            
                                                        tt.range(f"S{idx_location + 2}").value = LimitOrderBook[Order_id]['Remarks']
                                                        
                                                    elif trade_info[15] == 'Active':
                                                        
                                                        PnL = (float(trade_info[4]) - float(LTP) ) * int(trade_info[0])
                                                        tt.range(f"AC{idx_location + 2}").value = PnL
                                                        
                                                        TSL = 9999999
                                                        
                                                        if type(trade_info[12]) is float:
                                                            if trade_info[13] == True :
                                                                if(LTP <= trade_info[4]):
                                                                    CUR_TSL = trade_info[12] - (trade_info[4] - LTP )
                                                                else:
                                                                    CUR_TSL = trade_info[12]
                                                                
                                                                OLD_TSL = trade_info[14]
                                                                if OLD_TSL is not None:
                                                                    TSL = min(CUR_TSL,OLD_TSL)
                                                                else:
                                                                    TSL = CUR_TSL 
                                                            else:
                                                                TSL = trade_info[12]
                                                            
                                                            tt.range(f"AA{idx_location + 2}").value = TSL
                                                            
                                                        
                                                        if trade_info[7] == 'True_Market':
                                                            #exit sell order immediately
                                                            order_id = place_trade(i, int(trade_info[0]), "BUY","MARKET")
                                                            if order_id is None:
                                                                tt.range(f"T{idx_location + 2}").value = None
                                                            else:  
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                    
                                                                tt.range(f"v{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = "Exit_Pending"
                                                                
                                                        elif trade_info[7] == 'True_Limit_LTP':
                                                            #exit SELL at ltp limit 
                                                            order_id = place_trade(i, int(trade_info[0]), "BUY","LIMIT",LTP)
                                                            
                                                            if order_id is None:
                                                                tt.range(f"T{idx_location + 2}").value = None
                                                            else:  
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                tt.range(f"v{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Exit_Pending'    
                                                                
                                                        elif type(trade_info[11]) is float and trade_info[11] >= LTP:
                                                            #target meets, so exiting the sell order
                                                            order_id = place_trade(i, int(trade_info[0]), "BUY","MARKET")
                                                            
                                                            LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                            
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Exit_Pending'
                                                            
                                                        
                                                        elif TSL <= LTP and type(trade_info[12]) is float:
                                                            #sl hit, so exiting the sell order
                                                            order_id = place_trade(i, int(trade_info[0]), "BUY","MARKET")
                                                            
                                                            if order_id is None:
                                                                tt.range(f"O{idx_location + 2}").value = None
                                                            else: 
                                                            
                                                                LimitOrderBook.update({str(order_id): {'status': 'PENDING', 'Remarks': None, 'Executed_price': None}})
                                                                
                                                                
                                                                tt.range(f"v{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Exit_Pending'
                                                            
                                                    elif trade_info[15] ==  'Exit_Pending':
                                                        Order_id = str(int(trade_info[9]))
                                                        #print(f"Order_id={Order_id}")
                                                        status = LimitOrderBook[Order_id]['status']
                                                        if status == 'COMPLETE':
                                                            Executed_price = LimitOrderBook[Order_id]['Executed_price']
                                                            tt.range(f"u{idx_location + 2}").value = Executed_price
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Closed'
                                                            
                                                            PnL = (float(trade_info[4]) - float(Executed_price)) * int(trade_info[0])
                                                            tt.range(f"AC{idx_location + 2}").value = PnL
                                                            
                                                            Message = str(int(trade_info[0])) + " quantity " + str(i[4:]) + " buyback at " + str(Executed_price) + ", Trade profit or Loss = " + str(round(PnL,2))
                                                            
                                                            Telegram_Message.append(Message)
                                                            Voice_Message.append(Message)
                                                            
                                                        tt.range(f"W{idx_location + 2}").value = LimitOrderBook[Order_id]['Remarks']
                                        else:
                                            #paper trade mode handling will handle here
                                            if trade_info[1].upper() == "BUY" and LTP != 0:
                                                if trade_info[2]  in ['True_Market' ,'True_Limit_LTP' , 'Limit_Below','Limit_Above']:
                                                    
                                                   
                                                    
                                                    #0:Qty    1:BUY/SELL    2:Entry_Signal  3:Entry_Limit_Price  4:Entry_Done@  5:Entry_Order_ID 6: Entry_Remarks  7:Exit_Signal 8:Exit_Done @ 9:Exit_Order_ID   10:Target    11:SL  12:Trail Enable 13: Latest_SL 14:Trade_status 15: PnL
                                                    
            
                                                    if trade_info[15] != 'Active' and trade_info[15] != 'Exit_Pending' and trade_info[15] != 'Closed' and (trade_info[15] is None or trade_info[15] == ''or trade_info[15] == 'Entry_Pending' ):
                                                        
                                                        
                                                        if trade_info[2] in[ 'True_Market', 'True_Limit_LTP']:
                                                            #Entry buy trade immediately
                                                            order_id = 'PAPER'
                                                            Executed_price = LTP
                                                                
                                                            tt.range(f"q{idx_location + 2}").value = Executed_price
                                                            tt.range(f"r{idx_location + 2}").value = order_id
                                                            tt.range(f"u{idx_location + 2}").value = None
                                                            tt.range(f"v{idx_location + 2}").value = None
                                                            tt.range(f"AA{idx_location + 2}").value = None
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Active'
                                                            tt.range(f"AC{idx_location + 2}").value = None
                                                        
                                                        elif trade_info[2] == 'Limit_Below' and trade_info[3] is not None:
                                                            #print(f"LTP={LTP}, trade_info[3] ={trade_info[3]}")
                                                            
                                                            #handling paper Limit_Below based trade
                                                            if LTP <= trade_info[3]:
                                                                #Entry limit below buy
                                                                
                                                                order_id = 'PAPER'
                                                                Executed_price = LTP
                                                                    
                                                                tt.range(f"q{idx_location + 2}").value = Executed_price
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Active'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None

                                                            elif trade_info[15] != 'Entry_Pending':
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                        
                                                        elif trade_info[2] == 'Limit_Above' and trade_info[3] is not None:
                                                            #print(f"LTP={LTP}, trade_info[3] ={trade_info[3]}")
                                                            
                                                            if LTP >= trade_info[3]:
                                                                #Entry limit above buy
                                                                order_id = 'PAPER'
                                                                Executed_price = LTP
                                                                    
                                                                tt.range(f"q{idx_location + 2}").value = Executed_price
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Active'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None

                                                            elif trade_info[15] != 'Entry_Pending':
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                                                                            
                                                                
                                                    elif trade_info[15] == 'Active' or trade_info[15] == 'Exit_Pending':
                                                        
                                                        
                                                        PnL = (float(LTP) - float(trade_info[4])) * int(trade_info[0])
                                                        tt.range(f"AC{idx_location + 2}").value = PnL
                                                        #print(f"PnL = {PnL}")
                                                        
                                                        TSL = 0
                                                        if type(trade_info[12]) is float:
                                                            if trade_info[13] == True:
                                                                if(LTP >= trade_info[4]):
                                                                    CUR_TSL = LTP - trade_info[4] + trade_info[12]
                                                                else:
                                                                    CUR_TSL = trade_info[12]
                                                                
                                                                OLD_TSL = trade_info[14]
                                                                if OLD_TSL is not None:
                                                                    TSL = max(CUR_TSL,OLD_TSL)
                                                                else:
                                                                    TSL = CUR_TSL 
                                                            else:
                                                                TSL = trade_info[12]
                                                            
                                                            tt.range(f"AA{idx_location + 2}").value = TSL
                                                        
                                                        if trade_info[7] in  ['True_Market','True_Limit_LTP']:
                                                            #exit buy order immediately
                                                            order_id = 'PAPER'
                                                            Executed_price = LTP
                                                                
                                                            tt.range(f"u{idx_location + 2}").value = Executed_price
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Closed'
                                                            
                                                            PnL = (float(Executed_price) - float(trade_info[4])) * int(trade_info[0])
                                                            tt.range(f"AC{idx_location + 2}").value = PnL
                                                        
                                                        
                                                        elif type(trade_info[11]) is float and trade_info[11] <= LTP:
                                                            #target meets, so exit the buy order
                                                            order_id = 'PAPER'
                                                            Executed_price = LTP
                                                                
                                                            tt.range(f"u{idx_location + 2}").value = Executed_price
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Closed'
                                                            
                                                            PnL = (float(Executed_price) - float(trade_info[4])) * int(trade_info[0])
                                                            tt.range(f"AC{idx_location + 2}").value = PnL
                                                        
                                                        elif TSL >= LTP and type(trade_info[12]) is float:
                                                            #sl meets, so exiting the buy order
                                                            order_id = 'PAPER'
                                                            Executed_price = LTP
                                                                
                                                            tt.range(f"u{idx_location + 2}").value = Executed_price
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Closed'
                                                            
                                            if trade_info[1].upper() == "SELL" and LTP != 0:
                                                
                                                if trade_info[2] in ['True_Market','True_Limit_LTP',  'Limit_Below', 'Limit_Above']:
                                                    #0:Qty    1:BUY/SELL    2:Entry_Signal  3:Entry_Limit_Price  4:Entry_Done@  5:Entry_Order_ID 6: Entry_Remarks  7:Exit_Signal 8:Exit_Done @ 9:Exit_Order_ID   10:Target    11:SL  12:Trail Enable 13: Latest_SL 14:Trade_status 15: PnL
                                                    
                                                    
                                                    if trade_info[15] != 'Active' and trade_info[15] != 'Exit_Pending' and trade_info[15] != 'Closed' and (trade_info[15] is None or trade_info[15] == '' or trade_info[15] == 'Entry_Pending'):
                                                
                                                        if trade_info[2] in ['True_Market', 'True_Limit_LTP']:
                                                            #entry sell order immediately
                                                            order_id = 'PAPER'
                                                            Executed_price = LTP
                                                                
                                                            tt.range(f"q{idx_location + 2}").value = Executed_price
                                                            tt.range(f"r{idx_location + 2}").value = order_id
                                                            tt.range(f"u{idx_location + 2}").value = None
                                                            tt.range(f"v{idx_location + 2}").value = None
                                                            tt.range(f"AA{idx_location + 2}").value = None
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Active'
                                                            tt.range(f"AC{idx_location + 2}").value = None
                                                            #print("b")
                                                            
                                                        elif trade_info[2] == 'Limit_Above' and trade_info[3] is not None:
                                                            
                                                            
                                                            if LTP >= trade_info[3]:
                                                                #entry sell order with Limit_Above
                                                                order_id = 'PAPER'
                                                                Executed_price = LTP
                                                                    
                                                                tt.range(f"q{idx_location + 2}").value = Executed_price
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Active'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                                
                                                            elif trade_info[15] != 'Entry_Pending':
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None 
                                                                
                                                        elif trade_info[2] == 'Limit_Below' and trade_info[3] is not None:
                                                            
                                                            
                                                            if LTP <= trade_info[3]:
                                                                #entry sell order with Limit_Below
                                                                
                                                                order_id = 'PAPER'
                                                                Executed_price = LTP
                                                                    
                                                                tt.range(f"q{idx_location + 2}").value = Executed_price
                                                                tt.range(f"r{idx_location + 2}").value = order_id
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Active'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None
                                                                
                                                            elif trade_info[15] != 'Entry_Pending':
                                                                tt.range(f"Ab{idx_location + 2}").value = 'Entry_Pending'
                                                                tt.range(f"u{idx_location + 2}").value = None
                                                                tt.range(f"v{idx_location + 2}").value = None
                                                                tt.range(f"AA{idx_location + 2}").value = None
                                                                tt.range(f"AC{idx_location + 2}").value = None    
                                                        
                                                    elif trade_info[15] == 'Active' or trade_info[15] == 'Exit_Pending':
                                                        
                                                        PnL = (float(trade_info[4]) - float(LTP) ) * int(trade_info[0])
                                                        tt.range(f"AC{idx_location + 2}").value = PnL
                                                        
                                                        TSL = 9999999
                                                        
                                                        if type(trade_info[12]) is float:
                                                            if trade_info[13] == True :
                                                                if(LTP <= trade_info[4]):
                                                                    CUR_TSL = trade_info[12] - (trade_info[4] - LTP )
                                                                else:
                                                                    CUR_TSL = trade_info[12]
                                                                
                                                                OLD_TSL = trade_info[14]
                                                                if OLD_TSL is not None:
                                                                    TSL = min(CUR_TSL,OLD_TSL)
                                                                else:
                                                                    TSL = CUR_TSL 
                                                            else:
                                                                TSL = trade_info[12]
                                                            
                                                            tt.range(f"AA{idx_location + 2}").value = TSL
                                                            
                                                        
                                                        if trade_info[7] in  ['True_Market','True_Limit_LTP']:
                                                            #exit sell order immediately
                                                            order_id = 'PAPER'
                                                            Executed_price = LTP
                                                                
                                                            tt.range(f"u{idx_location + 2}").value = Executed_price
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = "Closed"
                                                            
                                                            PnL = (float(trade_info[4]) - float(Executed_price) ) * int(trade_info[0])
                                                            tt.range(f"AC{idx_location + 2}").value = PnL
                                                        
                                                            
                                                        elif type(trade_info[11]) is float and trade_info[11] >= LTP:
                                                            #target meets, so exiting the sell order
                                                            order_id = 'PAPER'
                                                            Executed_price = LTP
                                                                
                                                            tt.range(f"u{idx_location + 2}").value = Executed_price
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Closed'
                                                            
                                                            PnL = (float(trade_info[4]) - float(Executed_price) ) * int(trade_info[0])
                                                            tt.range(f"AC{idx_location + 2}").value = PnL
                                                        
                                                        elif TSL <= LTP and type(trade_info[12]) is float:
                                                            #sl hit, so exiting the sell order
                                                            order_id = 'PAPER'
                                                            Executed_price = LTP    
                                                                
                                                            tt.range(f"u{idx_location + 2}").value = Executed_price
                                                            tt.range(f"v{idx_location + 2}").value = order_id
                                                            tt.range(f"Ab{idx_location + 2}").value = 'Closed'
                                                            
                                                            PnL = (float(trade_info[4]) - float(Executed_price) ) * int(trade_info[0])
                                                            tt.range(f"AC{idx_location + 2}").value = PnL
                                        
                            
                            except Exception as e:
                                #print('Exception occur in core order book management:' + str(e))
                                pass
                        except Exception as e:
                            #print('Exception occur in trade terminal:' + str(e))
                            pass
                main_list.append(lst)
                
                idx += 1

            tt.range("b4:l1000").value = main_list
                
        except Exception as e:
            #print('Exception occur in while :' + str(e))
            pass

def GetOverAllPnL():
    global kite
    position = kite.positions()
            
    df_position_net = pd.DataFrame(position['net'])
    Running_Algo_M2M = 0
    if(len(df_position_net) > 0 ):

        df_position_net['LTP'] = None 
        df_position_net['Algo_realised'] = None 
        df_position_net['Algo_non_realised'] = None 
        df_position_net['Algo_M2M'] = None

        for ind in df_position_net.index: 
            Exchange_Symbol = str(df_position_net['exchange'][ind]) + ':' + str(df_position_net['tradingsymbol'][ind]) 
            LTP = float(kite.quote([Exchange_Symbol])[Exchange_Symbol]['last_price']) 
            df_position_net['LTP'][ind] = LTP

            quantity = int(df_position_net['quantity'][ind])
            buy_quantity = int(df_position_net['buy_quantity'][ind])
            buy_price = float(df_position_net['buy_price'][ind])
            Sell_quantity = int(df_position_net['sell_quantity'][ind]) 
            sell_price = float(df_position_net['sell_price'][ind])
            multiplier = int(df_position_net['multiplier'][ind])

            if(quantity == 0 ): #no open position found
                df_position_net['Algo_realised'][ind]  = (sell_price - buy_price) *  buy_quantity * multiplier
                df_position_net['Algo_non_realised'][ind] = 0
            elif(quantity > 0): #few longs position r available
                if(Sell_quantity == 0):
                    df_position_net['Algo_realised'][ind]  = 0
                    df_position_net['Algo_non_realised'][ind] = (LTP - buy_price) *  buy_quantity * multiplier
                else:
                    df_position_net['Algo_realised'][ind]  = (sell_price - buy_price) *  Sell_quantity * multiplier
                    df_position_net['Algo_non_realised'][ind] = (LTP - buy_price) *  quantity * multiplier
            elif(quantity < 0): #few shorts position r available
                if(buy_quantity == 0):
                    df_position_net['Algo_realised'][ind]  = 0
                    df_position_net['Algo_non_realised'][ind] = (sell_price - LTP) *  Sell_quantity * multiplier
                else:
                    df_position_net['Algo_realised'][ind]  = (sell_price - buy_price) *  buy_quantity * multiplier
                    df_position_net['Algo_non_realised'][ind] = (sell_price - LTP) *  abs(quantity) * multiplier

            df_position_net['Algo_M2M'][ind] = df_position_net['Algo_realised'][ind]  + df_position_net['Algo_non_realised'][ind] 
        
        Running_Algo_M2M = df_position_net['Algo_M2M'].sum()
        
    
    return Running_Algo_M2M
       
def RemoveUnwantedColumn(df_openPosition):
    try:
        df_openPosition = df_openPosition.drop(columns =['overnight_quantity','multiplier','close_price','value','m2m','buy_quantity','buy_price','buy_value','buy_m2m','sell_quantity','sell_price','sell_value','sell_m2m','day_buy_quantity','day_buy_price','day_buy_value','day_sell_quantity','day_sell_price','day_sell_value'], axis=1)
    except Exception as e:
        pass
    return df_openPosition
    
def get_position():
    #print(f"I am inside get_position")
    global kite
    position = kite.positions()
    df_position_net = pd.DataFrame(position['net'])
    #print(df_position_net)
    return df_position_net
    
def get_order_book():
    #print(f"I am inside get_order_book")
    global kite
    
    order_book = pd.DataFrame()
    order_book = kite.orders()
    try:
        if(len(order_book) > 0):
            order_book = pd.DataFrame(order_book)
            
            order_book = order_book.drop(['meta','placed_by','validity','instrument_token','disclosed_quantity','modified','exchange_order_id','parent_order_id','status_message','status_message_raw','exchange_update_timestamp','exchange_timestamp','validity_ttl','market_protection','tag','guid'], axis=1)
            order_book.sort_values(by = 'order_id',inplace = True)
            order_book = order_book.reset_index(drop = True)
            #print(order_book)
            #order_book.to_csv("abc.csv")
    except Exception as e:
        #print(f"Exception in get_order_book: {e}")
        pass
    return order_book
      
prev_day_oi = {} 
prev_day_oi_pro = {} 
stop_get_oi_thread = False
stop_get_oi_pro_thread = False

Symbol_spot = {"NIFTY":{"Exchange":"NSE","Name":"NIFTY 50"},"BANKNIFTY":{"Exchange":"NSE","Name":"NIFTY BANK"},"FINNIFTY":{"Exchange":"NSE","Name":"NIFTY FIN SERVICE"},"MIDCPNIFTY":{"Exchange":"NSE","Name":"NIFTY MID SELECT"},"SENSEX":{"Exchange":"BSE","Name":"SENSEX"},"BANKEX":{"Exchange":"BSE","Name":"BANKEX"}}
        
def get_oi(data):
    global prev_day_oi, kite, stop_get_oi_thread 
    
    for symbol, v in data.items(): 
        if stop_get_oi_thread:
            break 
        while True: 
            try:
                prev_day_oi[symbol]
                break 
            except Exception as e: 
                try: 
                    pre_day_data = kite.historical_data(v["token"], (dt.now() - timedelta(days=5)).date(),(dt.now() - timedelta(days=1)).date(), "day", oi=True) 
                    try:
                        prev_day_oi[symbol] = pre_day_data[-1]["oi"] 
                    except Exception as e:
                        #print("previous day oi data not found, exception :" + str(e))
                        prev_day_oi[symbol] = 0 
                    sleep(0.5)
                    break 
                except Exception as e:
                    #print("Exception occur while downloading previous day oi :" + str(e))
                    sleep(0.5)
    stop_get_oi_thread = True
    
def start_optionchain():
    global excel_OC, TradeTerminalFileName
    excel_OC = xw.Book(TradeTerminalFileName)
    global df_instrument
    global prev_day_oi
    pre_selected_segment = pre_selected_symbol = pre_selected_expiry = "" 
    pre_selected_NoOfStrike = 1000
    instrument_dict = {}
    global stop_get_oi_thread
    
    oci = excel_OC.sheets("Option_Chain_Input")
    oco = excel_OC.sheets("Option_Chain_Output") 
    
    
    oci.range("d2").value = "Segment==>>"
    oci.range("d3").value, oci.range("d4").value = "Symbol==>>", "Expiry==>>",
    oci.range("d5").value, oci.range("d6").value = "RefreshRate==>>", "NoOfStrike==>>",
    oci.range("d7").value, oci.range("d8").value = "ExpiryType==>>" , "GreekMatch==>>"
    
    print("Excel option chain : Started") 
    #print(f"pro 003:{dt.now()}")
    while True:
        while True: #excel_OC_pro.sheets.active.name in ["Option_Chain_Pro_Input","Option_Chain_Pro_Output"]:
            
            UserInput = oci.range(f"e{2}:e{8}").value
            selected_segment , selected_symbol, selected_expiry, selected_refreshrate, NoOfStrike, ExpiryType, GreekMatch = UserInput[0],UserInput[1], UserInput[2] , UserInput[3] , UserInput[4], UserInput[5] , UserInput[6] 
            
            if selected_refreshrate is None:
                selected_refreshrate = 5
            
            if NoOfStrike is None:
                NoOfStrike = 100
            Exchange = selected_segment[:3]
            if Exchange not in ['NFO','CDS','MCX','BCD','BFO']:
                Exchange = 'NFO'
                
            if pre_selected_segment != selected_segment:
                oci.range("a2:c500").value = None
                oci.range("i3:j26").value = None
                oco.range("a3:AE500").value = None
                instrument_dict = {} 
                df_exp = pd.DataFrame()
                stop_get_oi_thread = True
                
                df_instrument_temp = df_instrument[ (df_instrument["segment"] == selected_segment)] 
                if len(df_instrument_temp) == 0 :
                    oci.range("f2").value = "Please enter correct symbol"
                else:
                    oci.range("f2").value = None
                df_instrument_temp = df_instrument_temp.drop_duplicates( "name" , keep='first')
                df_instrument_temp =df_instrument_temp[['name']]
                df_instrument_temp.sort_values(by = 'name',inplace = True)
                oci.range("a2").options(index=False, header=False).value = df_instrument_temp
                
            elif pre_selected_symbol != selected_symbol:
                #if symbol only changed
                
                oci.range("b2:c500").value = None
                oci.range("i3:j26").value = None
                oco.range("a3:AE500").value = None
                instrument_dict = {} 
                stop_get_oi_thread = True
                df_exp = pd.DataFrame()
               
            elif pre_selected_expiry != selected_expiry:
                #only expiry changed
                oci.range("i3:j26").value = None
                oco.range("a3:AE500").value = None
                instrument_dict = {} 
                stop_get_oi_thread = True 
                
            pre_selected_segment = selected_segment
            pre_selected_symbol = selected_symbol
            pre_selected_expiry = selected_expiry
            
            if selected_symbol is not None: 
                try: 
                    if len(df_exp)== 0:
                        df_instrument_temp =  df_instrument  
                        df_instrument_temp = df_instrument_temp[ (df_instrument_temp["name"] == selected_symbol) & (df_instrument_temp["segment"] == selected_segment)] 
                        if len(df_instrument_temp) == 0 :
                            oci.range("f3").value = "Please enter correct symbol"
                        else:
                            oci.range("f3").value = None
                        df_exp = df_instrument_temp.drop_duplicates( "expiry" , keep='first')
                        df_exp =df_exp[['expiry','lot_size']]
                        df_exp.sort_values(by = 'expiry',inplace = True)
                        oci.range("b2").options(index=False, header=False).value = df_exp
    
                    if not instrument_dict and selected_expiry is not None:
                        df_instrument_temp = df_instrument
                        df_instrument_temp = df_instrument_temp[(df_instrument_temp["name"] == selected_symbol) & (df_instrument_temp["segment"] == selected_segment) ] 
                        if len(df_instrument_temp) == 0 :
                            oci.range("f3").value = "Please enter correct symbol"
                        else:
                            oci.range("f3").value = None
                            df_instrument_temp = df_instrument_temp[df_instrument_temp["expiry"] == selected_expiry.date()]
                            if len(df_instrument_temp) == 0 :
                                oci.range("f4").value = "Please enter correct expiry in dd-mm-YYYY (date format)"
                            else:
                                oci.range("f4").value = None
                        lot_size = list(df_instrument_temp [ "lot_size"])[0]
                        for i in df_instrument_temp.index: 
                            instrument_dict[f'{Exchange}{":"}{df_instrument_temp["tradingsymbol"][i]}'] = {"strike": float(df_instrument_temp["strike"][i]),
                                                                "instrumentType": df_instrument_temp["instrument_type"][i],
                                                                "token": df_instrument_temp [ "instrument_token"][i]}         
                        stop_get_oi_thread = False 
                        thread = threading.Thread(target=get_oi, args=(instrument_dict,))
                        thread.start() 
                    option_data = {} 
                    
                    SpotPrice = None
                    spot_open = None
                    spot_high = None
                    spot_low = None
                    spot_close = None
                    
                    if Exchange == 'NFO':
                        try:
                            spot_instrument = Symbol_spot[selected_symbol]["Exchange"] + ":"+ Symbol_spot[selected_symbol]["Name"]
                        except Exception as e:
                            #print(f"Exception occur : {e}")
                            spot_instrument = "NSE:" + str(selected_symbol)
                            pass

                    elif Exchange == 'BFO':
                        try:
                            spot_instrument = Symbol_spot[selected_symbol]["Exchange"] + ":"+ Symbol_spot[selected_symbol]["Name"]
                        except Exception as e:
                            #print(f"Exception occur : {e}")
                            spot_instrument = "BSE:" + str(selected_symbol)
                            pass
                    else:
                        spot_instrument = None
                    try:
                        spot_data = kite.quote(spot_instrument)[spot_instrument]
                        SpotPrice = spot_data["last_price"]
                        spot_open = spot_data['ohlc']["open"]
                        spot_high = spot_data['ohlc']["high"]
                        spot_low = spot_data['ohlc']["low"]
                        spot_close = spot_data['ohlc']["close"]
                    except :
                        pass
                
                    
                    df_instrument_temp = df_instrument
                    #get the future instrument
                    df_instrument_temp = df_instrument_temp[ (df_instrument_temp["name"] == selected_symbol) & (df_instrument_temp["segment"] == Exchange + str("-FUT"))]
                    #sort and find latest expiry
                    df_instrument_temp.sort_values(by = 'expiry',inplace = True)
                    #get the intrument
                    instrument_for_future = Exchange + ":" + df_instrument_temp.iloc[0]['tradingsymbol']

                    underlying_future_quote = kite.quote(instrument_for_future)[instrument_for_future]
                    #print(f"underlying_future_quote : {instrument_for_future} : {underlying_future_quote}")
                    underlying_future_price = underlying_future_quote["last_price"]
            
                    #print(underlying_future_price)
                    for symbol, values in kite.quote(list(instrument_dict.keys())).items():
                        try: 
                            #print(f"pro 800:{dt.now()}")
                            try:
                                option_data[symbol] 
                            except Exception as e:
                                option_data[symbol] = {} 

                            option_data[symbol]["strike"] = instrument_dict[symbol]["strike"]
                            option_data[symbol]["instrumentType"] = instrument_dict[symbol]["instrumentType"] 
                            option_data[symbol]["lastPrice"] = values["last_price"]
                            option_data[symbol]["totalTradedvolume"] = int(int(values["volume"])/lot_size)
                            option_data[symbol]["openInterest"] = int(values ["oi"]/lot_size)
                            
                            option_data[symbol]["BIDQTY"] = values['depth']['buy'][0]['quantity']
                            option_data[symbol]["BIDPRICE"] = values['depth']['buy'][0]['price']
                            option_data[symbol]["ASKPRICE"] = values['depth']['sell'][0]['price']
                            option_data[symbol]["ASKQTY"] = values['depth']['sell'][0]['quantity']    
                            
                            option_data[symbol]["change"] = values["last_price"] - values["ohlc"]["close"] if         values["last_price"] != 0 else 0
                            try:
                                option_data[symbol]["changeinopeninterest"] = int((values["oi"] - prev_day_oi[symbol])/lot_size)
                            except Exception as e:
                                #print("Exception occur in changeinopeninterest: " + str(e))
                                option_data[symbol]["changeinopeninterest"] = None
                                
                        except Exception as e:
                            #print("Exception occur in for loop: " + str(e))
                            pass
                    
                    df_oc = pd.DataFrame(option_data).transpose() 
                    #print(df_oc)
                    df_oc_ce = df_oc[df_oc["instrumentType"] == "CE"] 
                    #print(df_oc_ce)
                    df_oc_ce = df_oc_ce [["totalTradedvolume", "change", "lastPrice", "changeinopeninterest", "openInterest", "strike","BIDQTY","BIDPRICE","ASKPRICE","ASKQTY"]]
                    #print(df_oc_ce)            
                    df_oc_ce = df_oc_ce.rename(columns={"openInterest": "CE_OI", "changeinopeninterest": "CE Change in OI","lastPrice": "CE LTP", "change": "CE LTP Change", "totalTradedvolume": "CE Volume" ,"BIDQTY" : "CE_BIDQTY","BIDPRICE":"CE_BIDPRICE","ASKPRICE":"CE_ASKPRICE","ASKQTY":"CE_ASKQTY"}) 
                    #print(df_oc_ce)
                    df_oc_ce.index = df_oc_ce["strike"]
                    #print(df_oc_ce)            
                    df_oc_ce = df_oc_ce.drop(["strike"], axis=1)
                    #df_oc_ce["strike"] = df_oc_ce.index 
                    #print(df_oc_ce)
                    df_oc_pe = df_oc[df_oc["instrumentType"] == "PE"] 
                    df_oc_pe = df_oc_pe[["strike", "openInterest", "changeinopeninterest", "lastPrice", "change", "totalTradedvolume","BIDQTY","BIDPRICE","ASKPRICE","ASKQTY"]] 
                    df_oc_pe = df_oc_pe.rename(columns={"openInterest": "PE_OI", "changeinopeninterest": "PE Change in OI","lastPrice": "PE LTP", "change": "PE LTP Change", "totalTradedvolume" : "PE Volume" ,"BIDQTY" : "PE_BIDQTY","BIDPRICE":"PE_BIDPRICE","ASKPRICE":"PE_ASKPRICE","ASKQTY":"PE_ASKQTY"})
                    
                    df_oc_pe.index = df_oc_pe["strike"] 
                    df_oc_pe = df_oc_pe.drop("strike", axis=1) 
                    #print(df_oc_pe)
                    df_oc_pro = pd.concat([df_oc_ce, df_oc_pe], axis=1).sort_index() 
                    df_oc_pro = df_oc_pro.replace(np.nan, 0) 
                    df_oc_pro["Strike"] = df_oc_pro.index 
                    
                    df_oc_pro['strike_gap'] = abs(df_oc_pro['Strike'] - underlying_future_price)
                    
                    Min_gap = df_oc_pro['strike_gap'].min()
                    ATM_strike = df_oc_pro[df_oc_pro.strike_gap == Min_gap].iloc[0]['Strike']
                    
                    ATM_pos = df_oc_pro.index.get_loc(ATM_strike)
                    
                    df_oc_pro['OI_SUM'] = df_oc_pro["CE_OI"] + df_oc_pro["PE_OI"]
                    
                    Future_LTP = underlying_future_price
                    Max_Pain_at_Strike = df_oc_pro[df_oc_pro.OI_SUM == df_oc_pro["OI_SUM"].max()].iloc[0]['Strike']
                    Ltp_at_Max_Pain_CE = df_oc_pro[df_oc_pro.OI_SUM == df_oc_pro["OI_SUM"].max()].iloc[0]['CE LTP']
                    Ltp_at_Max_Pain_PE = df_oc_pro[df_oc_pro.OI_SUM == df_oc_pro["OI_SUM"].max()].iloc[0]['PE LTP']
                    #ATM_Strike = 
                    LTP_at_ATM_CE = df_oc_pro[df_oc_pro.Strike == ATM_strike].iloc[0]['CE LTP']
                    LTP_at_ATM_PE = df_oc_pro[df_oc_pro.Strike == ATM_strike].iloc[0]['PE LTP']
                    Total_OI_CE = df_oc_pro["CE_OI"].sum()
                    Total_OI_PE = df_oc_pro["PE_OI"].sum()
                    
                    Max_OI_CE = df_oc_pro["CE_OI"].max()
                    Max_OI_PE = df_oc_pro["PE_OI"].max()
                    Max_OI_at_Strike_CE = df_oc_pro[df_oc_pro.CE_OI == df_oc_pro["CE_OI"].max()].iloc[0]['Strike']
                    Max_OI_at_Strike_PE = df_oc_pro[df_oc_pro.PE_OI == df_oc_pro["PE_OI"].max()].iloc[0]['Strike']
                    LTP_of_Max_OI_Strike_CE = df_oc_pro[df_oc_pro.CE_OI == df_oc_pro["CE_OI"].max()].iloc[0]['CE LTP']
                    LTP_of_Max_OI_Strike_PE = df_oc_pro[df_oc_pro.PE_OI == df_oc_pro["PE_OI"].max()].iloc[0]['PE LTP']

                    Total_Volume_CE = df_oc_pro["CE Volume"].sum()
                    Total_Volume_PE = df_oc_pro["PE Volume"].sum()
                    Max_Vol_at_Strike_CE = df_oc_pro[df_oc_pro['CE Volume'] == df_oc_pro["CE Volume"].max()].iloc[0]['Strike']
                    Max_Vol_at_Strike_PE = df_oc_pro[df_oc_pro['PE Volume'] == df_oc_pro["PE Volume"].max()].iloc[0]['Strike']
                    LTP_of_Max_Vol_Strike_CE = df_oc_pro[df_oc_pro['CE Volume'] == df_oc_pro["CE Volume"].max()].iloc[0]['CE LTP']
                    LTP_of_Max_Vol_Strike_PE = df_oc_pro[df_oc_pro['PE Volume'] == df_oc_pro["PE Volume"].max()].iloc[0]['PE LTP']
                    
                    if stop_get_oi_thread == True:
                        Total_OI_Change_CE = df_oc_pro["CE Change in OI"].sum()
                        Total_OI_Change_PE = df_oc_pro["PE Change in OI"].sum()
                        
                        Max_Change_in_OI_addition_CE = df_oc_pro["CE Change in OI"].max()
                        Max_Change_in_OI_addition_PE = df_oc_pro["PE Change in OI"].max()
                        Max_OI_addition_at_Srike_CE = df_oc_pro[df_oc_pro["CE Change in OI"] == df_oc_pro["CE Change in OI"].max()].iloc[0]['Strike']
                        Max_OI_addition_at_Srike_PE = df_oc_pro[df_oc_pro["PE Change in OI"] == df_oc_pro["PE Change in OI"].max()].iloc[0]['Strike']
                        LTP_of_Max_OI_addition_Strike_CE = df_oc_pro[df_oc_pro["CE Change in OI"] == df_oc_pro["CE Change in OI"].max()].iloc[0]['CE LTP']
                        LTP_of_Max_OI_addition_Strike_PE = df_oc_pro[df_oc_pro["PE Change in OI"] == df_oc_pro["PE Change in OI"].max()].iloc[0]['PE LTP']
                        Max_Change_in_OI_unwinding_CE = -1 * int(df_oc_pro["CE Change in OI"].min())
                        Max_Change_in_OI_unwinding_PE = -1 * int(df_oc_pro["PE Change in OI"].min())
                        Max_OI_unwinding_at_Srike_CE = df_oc_pro[df_oc_pro["CE Change in OI"] == df_oc_pro["CE Change in OI"].min()].iloc[0]['Strike']
                        Max_OI_unwinding_at_Srike_PE = df_oc_pro[df_oc_pro["PE Change in OI"] == df_oc_pro["PE Change in OI"].min()].iloc[0]['Strike']
                        LTP_of_Max_OI_unwinding_Strike_CE = df_oc_pro[df_oc_pro["CE Change in OI"] == df_oc_pro["CE Change in OI"].min()].iloc[0]['CE LTP']
                        LTP_of_Max_OI_unwinding_Strike_PE = df_oc_pro[df_oc_pro["PE Change in OI"] == df_oc_pro["PE Change in OI"].min()].iloc[0]['PE LTP']
                    else:
                        Total_OI_Change_CE = None
                        Total_OI_Change_PE = None
                        
                        Max_Change_in_OI_addition_CE = None
                        Max_Change_in_OI_addition_PE = None
                        Max_OI_addition_at_Srike_CE = None
                        Max_OI_addition_at_Srike_PE = None
                        LTP_of_Max_OI_addition_Strike_CE = None
                        LTP_of_Max_OI_addition_Strike_PE = None

                        Max_Change_in_OI_unwinding_CE = None
                        Max_Change_in_OI_unwinding_PE = None
                        Max_OI_unwinding_at_Srike_CE = None
                        Max_OI_unwinding_at_Srike_PE = None
                        LTP_of_Max_OI_unwinding_Strike_CE = None
                        LTP_of_Max_OI_unwinding_Strike_PE = None
                        
                    
                    df_additional_detail = pd.DataFrame(columns = ['CE','PE'])
                    
                    dic_data = {'CE': SpotPrice, 'PE':Future_LTP}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE': spot_open,'PE':underlying_future_quote["ohlc"]["open"]}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE': spot_high,'PE':underlying_future_quote["ohlc"]["high"]}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE': spot_low,'PE':underlying_future_quote["ohlc"]["low"]}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE': spot_close,'PE':underlying_future_quote["ohlc"]["close"]}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':underlying_future_quote["oi"]/lot_size}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Max_Pain_at_Strike}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Ltp_at_Max_Pain_CE,'PE': Ltp_at_Max_Pain_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':ATM_strike}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':LTP_at_ATM_CE,'PE':LTP_at_ATM_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Total_OI_CE, 'PE':Total_OI_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Total_OI_Change_CE, 'PE':Total_OI_Change_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Max_OI_CE,  'PE':Max_OI_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Max_OI_at_Strike_CE,'PE':Max_OI_at_Strike_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':LTP_of_Max_OI_Strike_CE,'PE':LTP_of_Max_OI_Strike_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Max_Change_in_OI_addition_CE,'PE':Max_Change_in_OI_addition_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Max_OI_addition_at_Srike_CE,'PE':Max_OI_addition_at_Srike_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':LTP_of_Max_OI_addition_Strike_CE, 'PE':LTP_of_Max_OI_addition_Strike_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Max_Change_in_OI_unwinding_CE, 'PE':Max_Change_in_OI_unwinding_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Max_OI_unwinding_at_Srike_CE, 'PE':Max_OI_unwinding_at_Srike_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':LTP_of_Max_OI_unwinding_Strike_CE,'PE':LTP_of_Max_OI_unwinding_Strike_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Total_Volume_CE,'PE':Total_Volume_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    
                    dic_data = {'CE':Max_Vol_at_Strike_CE,'PE':Max_Vol_at_Strike_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':LTP_of_Max_Vol_Strike_CE,'PE':LTP_of_Max_Vol_Strike_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    oci.range("i3").options(index=False, header=False).value = df_additional_detail
                    
                    df_oc_pro['CE_Delta'] = None
                    df_oc_pro['CE_Gamma'] = None
                    df_oc_pro['CE_Theta'] = None
                    df_oc_pro['CE_Vega'] = None
                    df_oc_pro['CE_Rho'] = None
                    df_oc_pro['CE_IV'] = None
                    
                    df_oc_pro['PE_Delta'] = None
                    df_oc_pro['PE_Gamma'] = None
                    df_oc_pro['PE_Theta'] = None
                    df_oc_pro['PE_Vega'] = None
                    df_oc_pro['PE_Rho'] = None
                    df_oc_pro['PE_IV'] = None
                    
                    df_oc_pro = df_oc_pro.loc[:,['CE_Delta','CE_Gamma','CE_Theta','CE_Vega','CE_Rho','CE_OI','CE Change in OI','CE Volume','CE_IV','CE LTP','CE LTP Change','CE_BIDQTY','CE_BIDPRICE','CE_ASKPRICE','CE_ASKQTY','Strike','PE_BIDQTY','PE_BIDPRICE','PE_ASKPRICE','PE_ASKQTY','PE LTP Change','PE LTP','PE_IV','PE Volume','PE Change in OI','PE_OI','PE_Rho','PE_Vega','PE_Theta','PE_Gamma','PE_Delta']]
                    
                    if pre_selected_NoOfStrike != NoOfStrike:
                        oco.range("a3:AE500").value = None
                        
                        oco.range(f"a3:AE500").color = (255,255,255)
                        oco.range(f"p3:p{2 * int(NoOfStrike) + 3}").color = (46,132,198)                
                            
                        pre_selected_NoOfStrike = NoOfStrike
                    
                    if NoOfStrike * 2 < len(df_oc_pro):
                        df_oc_pro = df_oc_pro.iloc[ATM_pos - int(NoOfStrike) : ATM_pos + int(NoOfStrike) + 1]
                        ATM_Row = int(NoOfStrike) + 3
                        oco.range(f"a{ATM_Row}:AE{ATM_Row}").color = (46,132,198) 
                    
                    
                    if Exchange == 'NFO':
                        try:
                            instrument_for_ltp = Symbol_spot[selected_symbol]["Exchange"] + ":"+ Symbol_spot[selected_symbol]["Name"]
                        except Exception as e:
                            #print(f"Exception occur : {e}")
                            instrument_for_ltp = "NSE:" + str(selected_symbol)
                            pass
                        #print(f"@@@@ {instrument_for_ltp}")
                        SpotPrice = kite.quote(instrument_for_ltp)[instrument_for_ltp]["last_price"]
                    elif Exchange == 'BFO':
                        try:
                            instrument_for_ltp = Symbol_spot[selected_symbol]["Exchange"] + ":"+ Symbol_spot[selected_symbol]["Name"]
                            SpotPrice = kite.quote(instrument_for_ltp)[instrument_for_ltp]["last_price"]
                        except Exception as e:
                            SpotPrice = float(underlying_future_price)
                            pass
                        
                    else:
                        SpotPrice = float(underlying_future_price)
                        
                    FuturePrice = float(underlying_future_price)
                    AtmStrike = float(ATM_strike)
                    AtmStrikeCallPrice = float(LTP_at_ATM_CE)
                    AtmStrikePutPrice = float(LTP_at_ATM_PE)
                    ExpiryDateTime = dt(selected_expiry.date().year, selected_expiry.date().month, selected_expiry.date().day, 0, 0, 0)
                    
                    
                    if ExpiryType == 'WEEKLY':
                        ExpiryDateType = ExpType.WEEKLY
                    else:
                        ExpiryDateType = ExpType.MONTHLY
                    
                    
                    if dt.now().time() < time(15, 30, 0):
                        FromDateTime = dt.now() 
                    else:
                        FromDateTime = dt(dt.now().year, dt.now().month,dt.now().day, 15, 30, 0)
                
                    if GreekMatch == "SENSIBULL":
                        tryMatchWith=TryMatchWith.SENSIBULL
                    else:
                        tryMatchWith=TryMatchWith.NSE
                        
                    dayCountType = DayCountType.CALENDARDAYS

                    IvGreeks = CalcIvGreeks( SpotPrice = SpotPrice,  FuturePrice = FuturePrice, AtmStrike = AtmStrike, AtmStrikeCallPrice = AtmStrikeCallPrice, AtmStrikePutPrice = AtmStrikePutPrice, ExpiryDateTime = ExpiryDateTime, ExpiryDateType = ExpiryDateType, FromDateTime = FromDateTime, tryMatchWith = tryMatchWith, dayCountType = dayCountType)
    
                    #print(f"SpotPrice={SpotPrice}, FuturePrice={FuturePrice},  AtmStrike={AtmStrike}, AtmStrikeCallPrice={AtmStrikeCallPrice}, AtmStrikePutPrice={AtmStrikePutPrice}, ExpiryDateTime={ExpiryDateTime},  ExpiryDateType={ExpiryDateType}, FromDateTime={FromDateTime}, tryMatchWith={tryMatchWith}")
                    
                    df_oc_pro.round({"CE LTP":2, 'PE LTP':2})
                    
                    for ind in df_oc_pro.index:
                        
                        StrikePrice= float(df_oc_pro['Strike'][ind])
                        StrikeCallPrice= float(df_oc_pro['CE LTP'][ind])
                        StrikePutPrice= float(df_oc_pro['PE LTP'][ind])
                        #print(f"StrikePrice={StrikePrice}, StrikeCallPrice={StrikeCallPrice}, StrikePutPrice={StrikePutPrice}")
                        Greeks = IvGreeks.GetImpVolAndGreeks( StrikePrice = StrikePrice, StrikeCallPrice = StrikeCallPrice, StrikePutPrice = StrikePutPrice)
                        #print(Greeks)
                        
                        df_oc_pro['CE_Delta'][ind] = round(Greeks["CallDelta"],2)
                        df_oc_pro['CE_Gamma'][ind] = round(Greeks["Gamma"],4)
                        df_oc_pro['CE_Theta'][ind] = round(Greeks["Theta"],2)
                        df_oc_pro['CE_Vega'][ind] = round(Greeks["Vega"],2)
                        df_oc_pro['CE_Rho'][ind] = round(Greeks["RhoCall"],4)
                        
                        
                        df_oc_pro['PE_Delta'][ind] = round(Greeks["PutDelta"],2)
                        df_oc_pro['PE_Gamma'][ind] = round(Greeks["Gamma"],4)
                        df_oc_pro['PE_Theta'][ind] = round(Greeks["Theta"],2)
                        df_oc_pro['PE_Vega'][ind] = round(Greeks["Vega"],2)
                        df_oc_pro['PE_Rho'][ind] = round(Greeks["RhoPut"],4)
                        
                        if GreekMatch == "NSE":
                            df_oc_pro['CE_IV'][ind] = round(Greeks["CallIV"],2)
                            df_oc_pro['PE_IV'][ind] = round(Greeks["PutIV"],2)
                        else:
                            df_oc_pro['CE_IV'][ind] = round(Greeks["ImplVol"],2)
                            df_oc_pro['PE_IV'][ind] = round(Greeks["ImplVol"],2)
                    
                    del IvGreeks
                    oco.range("a3").options(index=False, header=False).value = df_oc_pro
                    sleep(selected_refreshrate) 
                    
                except Exception as e:
                    #print("Exception occur in parent loop: " + str(e))
                    pass
        
        sleep(5)
        
def get_oi_pro(data):
    global prev_day_oi_pro, kite, stop_get_oi_pro_thread 
    
    for symbol, v in data.items(): 
        if stop_get_oi_pro_thread:
            break 
        while True: 
            try:
                prev_day_oi_pro[symbol]
                break 
            except Exception as e: 
                try: 
                    pre_day_data = kite.historical_data(v["token"], (dt.now() - timedelta(days=5)).date(),(dt.now() - timedelta(days=1)).date(), "day", oi=True) 
                    try:
                        prev_day_oi_pro[symbol] = pre_day_data[-1]["oi"] 
                    except Exception as e:
                        #print("previous day oi data not found, exception :" + str(e))
                        prev_day_oi_pro[symbol] = 0 
                    sleep(0.5)
                    break 
                except Exception as e:
                    #print("Exception occur while downloading previous day oi :" + str(e))
                    sleep(0.5)
    stop_get_oi_pro_thread = True
    
def start_optionchain_Pro():
    global excel_OC_pro, TradeTerminalFileName
    excel_OC_pro = xw.Book(TradeTerminalFileName)
    global df_instrument
    global prev_day_oi_pro
    pre_selected_segment = pre_selected_symbol = pre_selected_expiry = "" 
    pre_selected_NoOfStrike = 1000
    instrument_dict = {}
    global stop_get_oi_pro_thread
    
    oci_pro = excel_OC_pro.sheets("Option_Chain_Pro_Input")
    oco_pro = excel_OC_pro.sheets("Option_Chain_Pro_Output") 
    
    
    oci_pro.range("d2").value = "Segment==>>"
    oci_pro.range("d3").value, oci_pro.range("d4").value = "Symbol==>>", "Expiry==>>",
    oci_pro.range("d5").value, oci_pro.range("d6").value = "RefreshRate==>>", "NoOfStrike==>>",
    oci_pro.range("d7").value, oci_pro.range("d8").value = "ExpiryType==>>" , "GreekMatch==>>"
    
    print("Excel option chain Pro: Started") 
    #print(f"pro 003:{dt.now()}")
    while True:
        while True: #excel_OC_pro.sheets.active.name in ["Option_Chain_Pro_Input","Option_Chain_Pro_Output"]:
            
            UserInput = oci_pro.range(f"e{2}:e{8}").value
            selected_segment , selected_symbol, selected_expiry, selected_refreshrate, NoOfStrike, ExpiryType, GreekMatch = UserInput[0],UserInput[1], UserInput[2] , UserInput[3] , UserInput[4], UserInput[5] , UserInput[6] 
            
            if selected_refreshrate is None:
                selected_refreshrate = 5
            
            if NoOfStrike is None:
                NoOfStrike = 100
            Exchange = selected_segment[:3]
            if Exchange not in ['NFO','CDS','MCX','BCD','BFO']:
                Exchange = 'NFO'
                
            if pre_selected_segment != selected_segment:
                oci_pro.range("a2:c500").value = None
                oci_pro.range("i3:j26").value = None
                oco_pro.range("a3:AE500").value = None
                instrument_dict = {} 
                df_exp = pd.DataFrame()
                stop_get_oi_pro_thread = True
                
                df_instrument_temp = df_instrument[ (df_instrument["segment"] == selected_segment)] 
                if len(df_instrument_temp) == 0 :
                    oci_pro.range("f2").value = "Please enter correct symbol"
                else:
                    oci_pro.range("f2").value = None
                df_instrument_temp = df_instrument_temp.drop_duplicates( "name" , keep='first')
                df_instrument_temp =df_instrument_temp[['name']]
                df_instrument_temp.sort_values(by = 'name',inplace = True)
                oci_pro.range("a2").options(index=False, header=False).value = df_instrument_temp
                
            elif pre_selected_symbol != selected_symbol:
                #if symbol only changed
                
                oci_pro.range("b2:c500").value = None
                oci_pro.range("i3:j26").value = None
                oco_pro.range("a3:AE500").value = None
                instrument_dict = {} 
                stop_get_oi_pro_thread = True
                df_exp = pd.DataFrame()
               
            elif pre_selected_expiry != selected_expiry:
                #only expiry changed
                oci_pro.range("i3:j26").value = None
                oco_pro.range("a3:AE500").value = None
                instrument_dict = {} 
                stop_get_oi_pro_thread = True 
                
            pre_selected_segment = selected_segment
            pre_selected_symbol = selected_symbol
            pre_selected_expiry = selected_expiry
            
            if selected_symbol is not None: 
                try: 
                    if len(df_exp)== 0:
                        df_instrument_temp =  df_instrument 
                        df_instrument_temp = df_instrument_temp[ (df_instrument_temp["name"] == selected_symbol) & (df_instrument_temp["segment"] == selected_segment)] 
                        if len(df_instrument_temp) == 0 :
                            oci_pro.range("f3").value = "Please enter correct symbol"
                        else:
                            oci_pro.range("f3").value = None
                        df_exp = df_instrument_temp.drop_duplicates( "expiry" , keep='first')
                        df_exp =df_exp[['expiry','lot_size']]
                        df_exp.sort_values(by = 'expiry',inplace = True)
                        oci_pro.range("b2").options(index=False, header=False).value = df_exp
    
                    if not instrument_dict and selected_expiry is not None:
                        df_instrument_temp = df_instrument
                        df_instrument_temp = df_instrument_temp[(df_instrument_temp["name"] == selected_symbol) & (df_instrument_temp["segment"] == selected_segment) ] 
                        if len(df_instrument_temp) == 0 :
                            oci_pro.range("f3").value = "Please enter correct symbol"
                        else:
                            oci_pro.range("f3").value = None
                            df_instrument_temp = df_instrument_temp[df_instrument_temp["expiry"] == selected_expiry.date()]
                            if len(df_instrument_temp) == 0 :
                                oci_pro.range("f4").value = "Please enter correct expiry in dd-mm-YYYY (date format)"
                            else:
                                oci_pro.range("f4").value = None
                        lot_size = list(df_instrument_temp [ "lot_size"])[0]
                        for i in df_instrument_temp.index: 
                            instrument_dict[f'{Exchange}{":"}{df_instrument_temp["tradingsymbol"][i]}'] = {"strike": float(df_instrument_temp["strike"][i]),
                                                                "instrumentType": df_instrument_temp["instrument_type"][i],
                                                                "token": df_instrument_temp [ "instrument_token"][i]}         
                        stop_get_oi_pro_thread = False 
                        thread = threading.Thread(target=get_oi_pro, args=(instrument_dict,))
                        thread.start() 
                    option_data = {} 
                        
                    SpotPrice = None
                    spot_open = None
                    spot_high = None
                    spot_low = None
                    spot_close = None
                    
                    if Exchange == 'NFO':
                        try:
                            spot_instrument = Symbol_spot[selected_symbol]["Exchange"] + ":"+ Symbol_spot[selected_symbol]["Name"]
                        except Exception as e:
                            #print(f"Exception occur : {e}")
                            spot_instrument = "NSE:" + str(selected_symbol)
                            pass

                    elif Exchange == 'BFO':
                        try:
                            spot_instrument = Symbol_spot[selected_symbol]["Exchange"] + ":"+ Symbol_spot[selected_symbol]["Name"]
                        except Exception as e:
                            #print(f"Exception occur : {e}")
                            spot_instrument = "BSE:" + str(selected_symbol)
                            pass
                    else:
                        spot_instrument = None
                    try:
                        spot_data = kite.quote(spot_instrument)[spot_instrument]
                        SpotPrice = spot_data["last_price"]
                        spot_open = spot_data['ohlc']["open"]
                        spot_high = spot_data['ohlc']["high"]
                        spot_low = spot_data['ohlc']["low"]
                        spot_close = spot_data['ohlc']["close"]
                    except :
                        pass
                
                    
                    df_instrument_temp = df_instrument
                    #get the future instrument
                    df_instrument_temp = df_instrument_temp[ (df_instrument_temp["name"] == selected_symbol) & (df_instrument_temp["segment"] == Exchange + str("-FUT"))]
                    #sort and find latest expiry
                    df_instrument_temp.sort_values(by = 'expiry',inplace = True)
                    #get the intrument
                    instrument_for_future = Exchange + ":" + df_instrument_temp.iloc[0]['tradingsymbol']

                    underlying_future_quote = kite.quote(instrument_for_future)[instrument_for_future]
                    #print(f"underlying_future_quote : {instrument_for_future} : {underlying_future_quote}")
                    underlying_future_price = underlying_future_quote["last_price"]
            
                    #print(underlying_future_price)
                    for symbol, values in kite.quote(list(instrument_dict.keys())).items():
                        try: 
                            #print(f"pro 800:{dt.now()}")
                            try:
                                option_data[symbol] 
                            except Exception as e:
                                option_data[symbol] = {} 

                            option_data[symbol]["strike"] = instrument_dict[symbol]["strike"]
                            option_data[symbol]["instrumentType"] = instrument_dict[symbol]["instrumentType"] 
                            option_data[symbol]["lastPrice"] = values["last_price"]
                            option_data[symbol]["totalTradedvolume"] = int(int(values["volume"])/lot_size)
                            option_data[symbol]["openInterest"] = int(values ["oi"]/lot_size)
                            
                            option_data[symbol]["BIDQTY"] = values['depth']['buy'][0]['quantity']
                            option_data[symbol]["BIDPRICE"] = values['depth']['buy'][0]['price']
                            option_data[symbol]["ASKPRICE"] = values['depth']['sell'][0]['price']
                            option_data[symbol]["ASKQTY"] = values['depth']['sell'][0]['quantity']    
                            
                            option_data[symbol]["change"] = values["last_price"] - values["ohlc"]["close"] if         values["last_price"] != 0 else 0
                            try:
                                option_data[symbol]["changeinopeninterest"] = int((values["oi"] - prev_day_oi_pro[symbol])/lot_size)
                            except Exception as e:
                                #print("Exception occur in changeinopeninterest: " + str(e))
                                option_data[symbol]["changeinopeninterest"] = None
                                
                        except Exception as e:
                            #print("Exception occur in for loop: " + str(e))
                            pass
                    
                    df_oc = pd.DataFrame(option_data).transpose() 
                    #print(df_oc)
                    df_oc_ce = df_oc[df_oc["instrumentType"] == "CE"] 
                    #print(df_oc_ce)
                    df_oc_ce = df_oc_ce [["totalTradedvolume", "change", "lastPrice", "changeinopeninterest", "openInterest", "strike","BIDQTY","BIDPRICE","ASKPRICE","ASKQTY"]]
                    #print(df_oc_ce)            
                    df_oc_ce = df_oc_ce.rename(columns={"openInterest": "CE_OI", "changeinopeninterest": "CE Change in OI","lastPrice": "CE LTP", "change": "CE LTP Change", "totalTradedvolume": "CE Volume" ,"BIDQTY" : "CE_BIDQTY","BIDPRICE":"CE_BIDPRICE","ASKPRICE":"CE_ASKPRICE","ASKQTY":"CE_ASKQTY"}) 
                    #print(df_oc_ce)
                    df_oc_ce.index = df_oc_ce["strike"]
                    #print(df_oc_ce)            
                    df_oc_ce = df_oc_ce.drop(["strike"], axis=1)
                    #df_oc_ce["strike"] = df_oc_ce.index 
                    #print(df_oc_ce)
                    df_oc_pe = df_oc[df_oc["instrumentType"] == "PE"] 
                    df_oc_pe = df_oc_pe[["strike", "openInterest", "changeinopeninterest", "lastPrice", "change", "totalTradedvolume","BIDQTY","BIDPRICE","ASKPRICE","ASKQTY"]] 
                    df_oc_pe = df_oc_pe.rename(columns={"openInterest": "PE_OI", "changeinopeninterest": "PE Change in OI","lastPrice": "PE LTP", "change": "PE LTP Change", "totalTradedvolume" : "PE Volume" ,"BIDQTY" : "PE_BIDQTY","BIDPRICE":"PE_BIDPRICE","ASKPRICE":"PE_ASKPRICE","ASKQTY":"PE_ASKQTY"})
                    
                    df_oc_pe.index = df_oc_pe["strike"] 
                    df_oc_pe = df_oc_pe.drop("strike", axis=1) 
                    #print(df_oc_pe)
                    df_oc_pro = pd.concat([df_oc_ce, df_oc_pe], axis=1).sort_index() 
                    df_oc_pro = df_oc_pro.replace(np.nan, 0) 
                    df_oc_pro["Strike"] = df_oc_pro.index 
                    
                    df_oc_pro['strike_gap'] = abs(df_oc_pro['Strike'] - underlying_future_price)
                    
                    Min_gap = df_oc_pro['strike_gap'].min()
                    ATM_strike = df_oc_pro[df_oc_pro.strike_gap == Min_gap].iloc[0]['Strike']
                    
                    ATM_pos = df_oc_pro.index.get_loc(ATM_strike)
                    
                    df_oc_pro['OI_SUM'] = df_oc_pro["CE_OI"] + df_oc_pro["PE_OI"]
                    
                    Future_LTP = underlying_future_price
                    Max_Pain_at_Strike = df_oc_pro[df_oc_pro.OI_SUM == df_oc_pro["OI_SUM"].max()].iloc[0]['Strike']
                    Ltp_at_Max_Pain_CE = df_oc_pro[df_oc_pro.OI_SUM == df_oc_pro["OI_SUM"].max()].iloc[0]['CE LTP']
                    Ltp_at_Max_Pain_PE = df_oc_pro[df_oc_pro.OI_SUM == df_oc_pro["OI_SUM"].max()].iloc[0]['PE LTP']
                    #ATM_Strike = 
                    LTP_at_ATM_CE = df_oc_pro[df_oc_pro.Strike == ATM_strike].iloc[0]['CE LTP']
                    LTP_at_ATM_PE = df_oc_pro[df_oc_pro.Strike == ATM_strike].iloc[0]['PE LTP']
                    Total_OI_CE = df_oc_pro["CE_OI"].sum()
                    Total_OI_PE = df_oc_pro["PE_OI"].sum()
                    
                    Max_OI_CE = df_oc_pro["CE_OI"].max()
                    Max_OI_PE = df_oc_pro["PE_OI"].max()
                    Max_OI_at_Strike_CE = df_oc_pro[df_oc_pro.CE_OI == df_oc_pro["CE_OI"].max()].iloc[0]['Strike']
                    Max_OI_at_Strike_PE = df_oc_pro[df_oc_pro.PE_OI == df_oc_pro["PE_OI"].max()].iloc[0]['Strike']
                    LTP_of_Max_OI_Strike_CE = df_oc_pro[df_oc_pro.CE_OI == df_oc_pro["CE_OI"].max()].iloc[0]['CE LTP']
                    LTP_of_Max_OI_Strike_PE = df_oc_pro[df_oc_pro.PE_OI == df_oc_pro["PE_OI"].max()].iloc[0]['PE LTP']
                    
                    Total_Volume_CE = df_oc_pro["CE Volume"].sum()
                    Total_Volume_PE = df_oc_pro["PE Volume"].sum()
                    Max_Vol_at_Strike_CE = df_oc_pro[df_oc_pro['CE Volume'] == df_oc_pro["CE Volume"].max()].iloc[0]['Strike']
                    Max_Vol_at_Strike_PE = df_oc_pro[df_oc_pro['PE Volume'] == df_oc_pro["PE Volume"].max()].iloc[0]['Strike']
                    LTP_of_Max_Vol_Strike_CE = df_oc_pro[df_oc_pro['CE Volume'] == df_oc_pro["CE Volume"].max()].iloc[0]['CE LTP']
                    LTP_of_Max_Vol_Strike_PE = df_oc_pro[df_oc_pro['PE Volume'] == df_oc_pro["PE Volume"].max()].iloc[0]['PE LTP']
                    
                    if stop_get_oi_pro_thread == True:
                        Total_OI_Change_CE = df_oc_pro["CE Change in OI"].sum()
                        Total_OI_Change_PE = df_oc_pro["PE Change in OI"].sum()
                        
                        Max_Change_in_OI_addition_CE = df_oc_pro["CE Change in OI"].max()
                        Max_Change_in_OI_addition_PE = df_oc_pro["PE Change in OI"].max()
                        Max_OI_addition_at_Srike_CE = df_oc_pro[df_oc_pro["CE Change in OI"] == df_oc_pro["CE Change in OI"].max()].iloc[0]['Strike']
                        Max_OI_addition_at_Srike_PE = df_oc_pro[df_oc_pro["PE Change in OI"] == df_oc_pro["PE Change in OI"].max()].iloc[0]['Strike']
                        LTP_of_Max_OI_addition_Strike_CE = df_oc_pro[df_oc_pro["CE Change in OI"] == df_oc_pro["CE Change in OI"].max()].iloc[0]['CE LTP']
                        LTP_of_Max_OI_addition_Strike_PE = df_oc_pro[df_oc_pro["PE Change in OI"] == df_oc_pro["PE Change in OI"].max()].iloc[0]['PE LTP']
                        Max_Change_in_OI_unwinding_CE = -1 * int(df_oc_pro["CE Change in OI"].min())
                        Max_Change_in_OI_unwinding_PE = -1 * int(df_oc_pro["PE Change in OI"].min())
                        Max_OI_unwinding_at_Srike_CE = df_oc_pro[df_oc_pro["CE Change in OI"] == df_oc_pro["CE Change in OI"].min()].iloc[0]['Strike']
                        Max_OI_unwinding_at_Srike_PE = df_oc_pro[df_oc_pro["PE Change in OI"] == df_oc_pro["PE Change in OI"].min()].iloc[0]['Strike']
                        LTP_of_Max_OI_unwinding_Strike_CE = df_oc_pro[df_oc_pro["CE Change in OI"] == df_oc_pro["CE Change in OI"].min()].iloc[0]['CE LTP']
                        LTP_of_Max_OI_unwinding_Strike_PE = df_oc_pro[df_oc_pro["PE Change in OI"] == df_oc_pro["PE Change in OI"].min()].iloc[0]['PE LTP']
                    else:
                        Total_OI_Change_CE = None
                        Total_OI_Change_PE = None
                        
                        Max_Change_in_OI_addition_CE = None
                        Max_Change_in_OI_addition_PE = None
                        Max_OI_addition_at_Srike_CE = None
                        Max_OI_addition_at_Srike_PE = None
                        LTP_of_Max_OI_addition_Strike_CE = None
                        LTP_of_Max_OI_addition_Strike_PE = None

                        Max_Change_in_OI_unwinding_CE = None
                        Max_Change_in_OI_unwinding_PE = None
                        Max_OI_unwinding_at_Srike_CE = None
                        Max_OI_unwinding_at_Srike_PE = None
                        LTP_of_Max_OI_unwinding_Strike_CE = None
                        LTP_of_Max_OI_unwinding_Strike_PE = None
                        
                    
                    df_additional_detail = pd.DataFrame(columns = ['CE','PE'])
                    
                    dic_data = {'CE': SpotPrice, 'PE':Future_LTP}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE': spot_open,'PE':underlying_future_quote["ohlc"]["open"]}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE': spot_high,'PE':underlying_future_quote["ohlc"]["high"]}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE': spot_low,'PE':underlying_future_quote["ohlc"]["low"]}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE': spot_close,'PE':underlying_future_quote["ohlc"]["close"]}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':underlying_future_quote["oi"]/lot_size}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Max_Pain_at_Strike}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Ltp_at_Max_Pain_CE,'PE': Ltp_at_Max_Pain_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':ATM_strike}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':LTP_at_ATM_CE,'PE':LTP_at_ATM_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Total_OI_CE, 'PE':Total_OI_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Total_OI_Change_CE, 'PE':Total_OI_Change_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Max_OI_CE,  'PE':Max_OI_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Max_OI_at_Strike_CE,'PE':Max_OI_at_Strike_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':LTP_of_Max_OI_Strike_CE,'PE':LTP_of_Max_OI_Strike_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Max_Change_in_OI_addition_CE,'PE':Max_Change_in_OI_addition_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Max_OI_addition_at_Srike_CE,'PE':Max_OI_addition_at_Srike_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':LTP_of_Max_OI_addition_Strike_CE, 'PE':LTP_of_Max_OI_addition_Strike_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Max_Change_in_OI_unwinding_CE, 'PE':Max_Change_in_OI_unwinding_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Max_OI_unwinding_at_Srike_CE, 'PE':Max_OI_unwinding_at_Srike_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':LTP_of_Max_OI_unwinding_Strike_CE,'PE':LTP_of_Max_OI_unwinding_Strike_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Total_Volume_CE,'PE':Total_Volume_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':Max_Vol_at_Strike_CE,'PE':Max_Vol_at_Strike_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    dic_data = {'CE':LTP_of_Max_Vol_Strike_CE,'PE':LTP_of_Max_Vol_Strike_PE}
                    df_additional_detail = pd.concat([df_additional_detail, pd.DataFrame.from_dict(dic_data,orient='index').T],ignore_index = True)
                    
                    oci_pro.range("i3").options(index=False, header=False).value = df_additional_detail
                    
                    df_oc_pro['CE_Delta'] = None
                    df_oc_pro['CE_Gamma'] = None
                    df_oc_pro['CE_Theta'] = None
                    df_oc_pro['CE_Vega'] = None
                    df_oc_pro['CE_Rho'] = None
                    df_oc_pro['CE_IV'] = None
                    
                    df_oc_pro['PE_Delta'] = None
                    df_oc_pro['PE_Gamma'] = None
                    df_oc_pro['PE_Theta'] = None
                    df_oc_pro['PE_Vega'] = None
                    df_oc_pro['PE_Rho'] = None
                    df_oc_pro['PE_IV'] = None
                    
                    df_oc_pro = df_oc_pro.loc[:,['CE_Delta','CE_Gamma','CE_Theta','CE_Vega','CE_Rho','CE_OI','CE Change in OI','CE Volume','CE_IV','CE LTP','CE LTP Change','CE_BIDQTY','CE_BIDPRICE','CE_ASKPRICE','CE_ASKQTY','Strike','PE_BIDQTY','PE_BIDPRICE','PE_ASKPRICE','PE_ASKQTY','PE LTP Change','PE LTP','PE_IV','PE Volume','PE Change in OI','PE_OI','PE_Rho','PE_Vega','PE_Theta','PE_Gamma','PE_Delta']]
                    
                    if pre_selected_NoOfStrike != NoOfStrike:
                        oco_pro.range("a3:AE500").value = None
                        
                        oco_pro.range(f"a3:AE500").color = (255,255,255)
                        oco_pro.range(f"p3:p{2 * int(NoOfStrike) + 3}").color = (46,132,198)                
                            
                        pre_selected_NoOfStrike = NoOfStrike
                    
                    if NoOfStrike * 2 < len(df_oc_pro):
                        df_oc_pro = df_oc_pro.iloc[ATM_pos - int(NoOfStrike) : ATM_pos + int(NoOfStrike) + 1]
                        ATM_Row = int(NoOfStrike) + 3
                        oco_pro.range(f"a{ATM_Row}:AE{ATM_Row}").color = (46,132,198) 
                    
                    
                    if Exchange == 'NFO':
                        try:
                            instrument_for_ltp = Symbol_spot[selected_symbol]["Exchange"] + ":"+ Symbol_spot[selected_symbol]["Name"]
                        except Exception as e:
                            #print(f"Exception occur : {e}")
                            instrument_for_ltp = "NSE:" + str(selected_symbol)
                            pass
                        #print(f"@@@@ {instrument_for_ltp}")
                        SpotPrice = kite.quote(instrument_for_ltp)[instrument_for_ltp]["last_price"]
                    elif Exchange == 'BFO':
                        try:
                            instrument_for_ltp = Symbol_spot[selected_symbol]["Exchange"] + ":"+ Symbol_spot[selected_symbol]["Name"]
                            SpotPrice = kite.quote(instrument_for_ltp)[instrument_for_ltp]["last_price"]
                        except Exception as e:
                            SpotPrice = float(underlying_future_price)
                            pass
                        
                    else:
                        SpotPrice = float(underlying_future_price)
                        
                    FuturePrice = float(underlying_future_price)
                    AtmStrike = float(ATM_strike)
                    AtmStrikeCallPrice = float(LTP_at_ATM_CE)
                    AtmStrikePutPrice = float(LTP_at_ATM_PE)
                    ExpiryDateTime = dt(selected_expiry.date().year, selected_expiry.date().month, selected_expiry.date().day, 0, 0, 0)
                    
                    
                    if ExpiryType == 'WEEKLY':
                        ExpiryDateType = ExpType.WEEKLY
                    else:
                        ExpiryDateType = ExpType.MONTHLY
                    
                
                    if dt.now().time() < time(15, 30, 0):
                        FromDateTime = dt.now() 
                    else:
                        FromDateTime = dt(dt.now().year, dt.now().month,dt.now().day, 15, 30, 0)
                    
                    if GreekMatch == "SENSIBULL":
                        tryMatchWith=TryMatchWith.SENSIBULL
                    else:
                        tryMatchWith=TryMatchWith.NSE
                        
                    dayCountType = DayCountType.CALENDARDAYS

                    IvGreeks = CalcIvGreeks( SpotPrice = SpotPrice,  FuturePrice = FuturePrice, AtmStrike = AtmStrike, AtmStrikeCallPrice = AtmStrikeCallPrice, AtmStrikePutPrice = AtmStrikePutPrice, ExpiryDateTime = ExpiryDateTime, ExpiryDateType = ExpiryDateType, FromDateTime = FromDateTime, tryMatchWith = tryMatchWith, dayCountType = dayCountType)
    
                    #print(f"SpotPrice={SpotPrice}, FuturePrice={FuturePrice},  AtmStrike={AtmStrike}, AtmStrikeCallPrice={AtmStrikeCallPrice}, AtmStrikePutPrice={AtmStrikePutPrice}, ExpiryDateTime={ExpiryDateTime},  ExpiryDateType={ExpiryDateType}, FromDateTime={FromDateTime}, tryMatchWith={tryMatchWith}")
                    
                    df_oc_pro.round({"CE LTP":2, 'PE LTP':2})
                    
                    for ind in df_oc_pro.index:
                        
                        StrikePrice= float(df_oc_pro['Strike'][ind])
                        StrikeCallPrice= float(df_oc_pro['CE LTP'][ind])
                        StrikePutPrice= float(df_oc_pro['PE LTP'][ind])
                        #print(f"StrikePrice={StrikePrice}, StrikeCallPrice={StrikeCallPrice}, StrikePutPrice={StrikePutPrice}")
                        Greeks = IvGreeks.GetImpVolAndGreeks( StrikePrice = StrikePrice, StrikeCallPrice = StrikeCallPrice, StrikePutPrice = StrikePutPrice)
                        #print(Greeks)
                        
                        df_oc_pro['CE_Delta'][ind] = round(Greeks["CallDelta"],2)
                        df_oc_pro['CE_Gamma'][ind] = round(Greeks["Gamma"],4)
                        df_oc_pro['CE_Theta'][ind] = round(Greeks["Theta"],2)
                        df_oc_pro['CE_Vega'][ind] = round(Greeks["Vega"],2)
                        df_oc_pro['CE_Rho'][ind] = round(Greeks["RhoCall"],4)
                        
                        
                        df_oc_pro['PE_Delta'][ind] = round(Greeks["PutDelta"],2)
                        df_oc_pro['PE_Gamma'][ind] = round(Greeks["Gamma"],4)
                        df_oc_pro['PE_Theta'][ind] = round(Greeks["Theta"],2)
                        df_oc_pro['PE_Vega'][ind] = round(Greeks["Vega"],2)
                        df_oc_pro['PE_Rho'][ind] = round(Greeks["RhoPut"],4)
                        
                        if GreekMatch == "NSE":
                            df_oc_pro['CE_IV'][ind] = round(Greeks["CallIV"],2)
                            df_oc_pro['PE_IV'][ind] = round(Greeks["PutIV"],2)
                        else:
                            df_oc_pro['CE_IV'][ind] = round(Greeks["ImplVol"],2)
                            df_oc_pro['PE_IV'][ind] = round(Greeks["ImplVol"],2)
                    
                    del IvGreeks
                    oco_pro.range("a3").options(index=False, header=False).value = df_oc_pro
                    sleep(selected_refreshrate) 
                    
                except Exception as e:
                    #print("Exception occur in parent loop: " + str(e))
                    pass
        
        sleep(5)

def Zerodha_Token():
    global kite, df_instrument
    global Initial_Subscribr_TokenList 
    Initial_Subscribr_TokenList = [256265] #subscribe Nifty 50
    print("Zerodha intrument token download started, may take upto 2-3 minutes ..")
    try:
        instruments = kite.instruments()
        df_instrument = pd.DataFrame(instruments, index=None)
        
        #subscribe active mcx symbol
        df_subscribe_token = df_instrument[(df_instrument.segment == 'MCX-FUT') & (df_instrument.name.isin(['CRUDEOIL','GOLDPETAL','NATURALGAS','SILVERM']))]

        Initial_Subscribr_TokenList.extend(df_subscribe_token['instrument_token'].tolist())
        
        #print(Initial_Subscribr_TokenList)
        try:
            df_instrument.to_csv("Instrument.csv",index=False)       
        except:
            pass
        print("Zerodha intrument token download completed")
    except Exception as e:
        #print(f"Exception in Zerodha_Token : {e}")
        pass
    
def StartThread():
    try:
        global excel_master
    
        Config_sheet = excel_master.sheets['Config']
        
        
        # Define the threads and put them in an array
        threads = []
        
        if Config_sheet.range("b2").value == True:
            threads.append(Thread(target=start_Trade_Terminal))
            threads.append(Thread(target=start_Open_Position))      
           
        if Config_sheet.range("b4").value == True:
            threads.append(Thread(target=start_optionchain))
        
        if Config_sheet.range("b5").value == True:
            threads.append(Thread(target=start_optionchain_Pro))
 
        
        if len(threads) != 0 :       
            # Func1 and Func2 run in separate threads
            for thread in threads:
                thread.start()

            # Wait until both Func1 and Func2 have finished
            for thread in threads:
                thread.join()
        else:
            print("Please select atlease one feature in config and restart the algo")
        
    except Exception as e:
        Message = str(e) + " : Exception occur"
        print(Message)
 
print(f"Python Trader Excel Based Terminal program initialised")
global excel_master
excel_master = xw.Book(TradeTerminalFileName)
if(Zerodha_login() == 1 ):   
    try:
        Zerodha_Token()
        
        kws = kite.kws()

        # Assign the callbacks.
        kws.on_ticks = on_ticks
        kws.on_order_update = on_order_update
        kws.on_connect = on_connect
        #kws.on_close = on_close

        kws.on_error = on_error
        kws.on_reconnect = on_reconnect
        kws.on_noreconnect = on_noreconnect

        kws.connect(threaded=True)
    
        StartThread()
        print("Done")
    except Exception as e:
        print("Exception : " + str(e))
else:
    print("Credential is not correct")
Zerodha/Zerodha_Core_V3_003.py
Indian-Algorithmic-Trading-Community/PythonTraderExcelTradeTerminal
import sys, os

try:
    Version = sys.version[:4]
except Exception as e:
    print(f"{e}")
    pass
    
if Version != "3.11":
    Message = "This trade tool will work with python latest version 3.11 only, so please upgrade python from installed version " + str(Version) + "to python 3.11"
    print(Message)
    sys.exit()
    
try:
    from kiteconnect import KiteConnect, KiteTicker
except (ModuleNotFoundError, ImportError):
    print("KiteConnect module not found")
    os.system(f"{sys.executable} -m pip install -U kiteconnect")
finally:
    from kiteconnect import KiteConnect, KiteTicker 

try:
    from tzlocal import get_localzone
except (ModuleNotFoundError, ImportError):
    print("tzlocal module not found")
    os.system(f"{sys.executable} -m pip install -U tzlocal")
finally:
    from tzlocal import get_localzone
	
try:
    import psutil
except (ModuleNotFoundError, ImportError):
    print("psutil module not found")
    os.system(f"{sys.executable} -m pip install -U psutil")
finally:
    import psutil
    
try:
    import numpy as np 
except (ModuleNotFoundError, ImportError):
    print("numpy module not found")
    os.system(f"{sys.executable} -m pip install -U numpy")
finally:
    import numpy as np 
    
try:
    import requests
except (ModuleNotFoundError, ImportError):
    print("requests module not found")
    os.system(f"{sys.executable} -m pip install -U requests")
finally:
    import requests

try:
    import pyotp
except (ModuleNotFoundError, ImportError):
    print("pyotp module not found")
    os.system(f"{sys.executable} -m pip install -U pyotp")
finally:
    import pyotp
    
try:
    import xlwings as xw
except (ModuleNotFoundError, ImportError):
    print("xlwings module not found")
    os.system(f"{sys.executable} -m pip install -U xlwings")
finally:
    import xlwings as xw

try:
    import pyttsx3
except (ModuleNotFoundError, ImportError):
    #print("pyttsx3 module not found")
    os.system(f"{sys.executable} -m pip install -U pyttsx3")
finally:
    import pyttsx3
    
try:
    import pandas as pd
except (ModuleNotFoundError, ImportError):
    print("pandas module not found")
    os.system(f"{sys.executable} -m pip install -U pandas")
finally:
    import pandas as pd

try:
    import scipy
except (ModuleNotFoundError, ImportError):
    print("scipy module not found")
    os.system(f"{sys.executable} -m pip install -U scipy")
finally:
    import scipy
    
try:
    import sourcedefender
except (ModuleNotFoundError, ImportError):
    print("sourcedefender module not found")
    os.system(f"{sys.executable} -m pip install -U sourcedefender")
finally:
    import sourcedefender


try:    
    import Zerodha_Core_V3_003
except Exception as e:
    print(f"Zerodha_Core_V3_003.pye file not found/corrupted, please download the latest file from tinyurl.com/pythontrader : {e}")

Zerodha/Zerodha_Trade_Terminal_V3_003.py
Indian-Algorithmic-Trading-Community/PythonTraderExcelTradeTerminal
import urllib.parse
import json
import kiteconnect.exceptions as ex
import logging
import six
from six.moves.urllib.parse import urljoin
import requests
from os import path

from kiteconnect import KiteConnect, KiteTicker

log = logging.getLogger(__name__)


class KiteExt(KiteConnect):

    def login_with_credentials(self, userid, password, twofa):
        self.headers = {
            'x-kite-version': '3',
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.128 Safari/537.36'
        }
        self.user_id = userid
        self.password = password
        self.twofa = twofa
        self.reqsession = requests.Session()
        r = self.reqsession.post(self.root + self._routes['api.login'], data={
            'user_id': self.user_id,
            'password': self.password
        })
       
        r2 = self.reqsession.post(self.root + self._routes['api.twofa'], data={
            'request_id': r.json()['data']['request_id'],
            'twofa_value': self.twofa,
            'user_id': r.json()['data']['user_id']
        })
        

        login_cookies = [{'domain': "kite.zerodha.com",'name': c.name, 'path': c.path, 'secure': True, 'value': c.value} for c in r2.cookies]
        print(str(json.dumps(login_cookies)))
        print("\n\n\n")
        
        cookie_data = r2.cookies.get_dict()
        cookies_array = [{"name": c.name, "value": c.value,"domain": c.domain, "path": c.path,"expires/max-age": "Session",} for c in r2.cookies]
        cookies_array = json.dumps(cookies_array, indent=4, ensure_ascii=False)
        print(cookies_array)
        print("\n\n\n")
        self.enctoken = r2.cookies.get('enctoken')
        self.public_token = r2.cookies.get('public_token')
        self.user_id = r2.cookies.get('user_id')

        self.headers['Authorization'] = 'enctoken {}'.format(self.enctoken)
        return self.enctoken, self.public_token
    
    def login_using_enctoken(self, userid, enctoken, public_token):
        self.headers = {
            'x-kite-version': '3',
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.128 Safari/537.36'
        }
        self.user_id = userid
        self.reqsession = requests.Session()

        self.enctoken = enctoken
        self.public_token = public_token
        #self.user_id = r.cookies.get('user_id')

        self.headers['Authorization'] = 'enctoken {}'.format(self.enctoken)

    def __init__(self, api_key='kitefront', userid=None, *args, **kw):
        KiteConnect.__init__(self, api_key=api_key,
                             *args, **kw)

        if userid is not None:
            self.user_id = userid

        self._routes.update({
            'api.login': '/api/login',
            'api.twofa': '/api/twofa',
            'api.misdata': '/margins/equity'
        })

    def set_headers(self, enctoken, user_id=None):
        self.public_token = enctoken
        self.enctoken = enctoken
        if user_id is not None:
            self.user_id = user_id
        if self.user_id is None:
            raise Exception(
                f'user_id cannot be none, either login with credentials first or set user_id here')
        self.headers = {
            'x-kite-version': '3',
            'Authorization': 'enctoken {}'.format(self.enctoken)
        }

    def kws(self, api_key='kitefront'):
        return KiteExtTicker(api_key=api_key, access_token='&user_id='+self.user_id+'&enctoken='+urllib.parse.quote(self.enctoken), root='wss://ws.zerodha.com')
    
    def ticker(self, api_key='kitefront', enctoken=None, userid=None):
        if enctoken is not None:
            self.enctoken = enctoken
        if userid is not None:
            self.user_id = userid
        if self.user_id is None:
            raise Exception(
                f'userid cannot be none, either login with credentials first or set userid here')
        return KiteExtTicker(api_key=api_key, access_token='&user_id='+self.user_id+'&enctoken='+urllib.parse.quote(self.enctoken), root='wss://ws.zerodha.com',reconnect_max_tries=300)


# NOTE NEW
    def _request(self, route, method, url_args=None, params=None, is_json=False, query_params=None):
        '''Make an HTTP request.'''
        # Form a restful URL
        if url_args:
            uri = self._routes[route].format(**url_args)
        else:
            uri = self._routes[route]

        url = urljoin(self.root, uri)

        headers = self.headers

        # Custom headers
        # headers = {
        #     'X-Kite-Version': '3',  # For version 3
        #     'User-Agent': self._user_agent()
        # }

        # if self.api_key and self.access_token:
        #     # set authorization header
        #     auth_header = self.api_key + ':' + self.access_token
        #     headers['Authorization'] = 'token {}'.format(auth_header)

        if self.debug:
            log.debug('Request: {method} {url} {params} {headers}'.format(
                method=method, url=url, params=params, headers=headers))

        # prepare url query params
        if method in ["GET", "DELETE"]:
            query_params = params
        
        try:
            r = self.reqsession.request(method,
                                        url,
                                        json=params if (
                                            method in ['POST', 'PUT'] and is_json) else None,
                                        data=params if (
                                            method in ['POST', 'PUT'] and not is_json) else None,
                                        params=params if method in [
                                            'GET', 'DELETE'] else None,
                                        headers=headers,
                                        verify=not self.disable_ssl,
                                        allow_redirects=True,
                                        timeout=self.timeout,
                                        proxies=self.proxies)
        # Any requests lib related exceptions are raised here - http://docs.python-requests.org/en/master/_modules/requests/exceptions/
        except Exception as e:
            raise e

        if self.debug:
            log.debug('Response: {code} {content}'.format(
                code=r.status_code, content=r.content))

        # Validate the content type.
        if 'json' in r.headers['content-type']:
            try:
                data = json.loads(r.content.decode('utf8'))
            except ValueError:
                raise ex.DataException('Could not parse the JSON response received from the server: {content}'.format(
                    content=r.content))

            # api error
            if data.get('error_type'):
                # Call session hook if its registered and TokenException is raised
                if self.session_expiry_hook and r.status_code == 403 and data['error_type'] == 'TokenException':
                    self.session_expiry_hook()

                # native Kite errors
                exp = getattr(ex, data['error_type'], ex.GeneralException)
                raise exp(data['message'], code=r.status_code)

            return data['data']
        elif 'csv' in r.headers['content-type']:
            return r.content
        else:
            raise ex.DataException('Unknown Content-Type ({content_type}) with response: ({content})'.format(
                content_type=r.headers['content-type'],
                content=r.content))

class KiteExtTicker(KiteTicker):
    def __init__(self, *args, **kwargs):
        KiteTicker.__init__(self, *args, **kwargs)

    def _parse_text_message(self, payload):
        """Parse text message."""
        # Decode unicode data
        if not six.PY2 and type(payload) == bytes:
            payload = payload.decode("utf-8")

        try:
            data = json.loads(payload)
        except ValueError:
            return

        # Order update callback
        if self.on_order_update and data.get("type") == "order":
            self.on_order_update(self, data)

        # Custom error with websocket error code 0
        if data.get("type") == "error":
            self._on_error(self, 0, data)


Zerodha/kiteext.py
Indian-Algorithmic-Trading-Community/PythonTraderExcelTradeTerminal