import openpyxl as xl
from openpyxl.utils import column_index_from_string
import requests
from bs4 import BeautifulSoup

"""
codes = ["INE470A01017", "INE117A01022", "INE012A01025", "INE212H01026", "INE702C01019", "INE949L01017", "INE769A01020",
         "INE216P01012", "INE358A01014", "INE399L01023", "INE364U01010", "INE742F01042", "INE814H01011", "INE931S01010",
         "INE674K01013", "INE647O01011", "INE837H01020", "INE208C01025", "INE00WC01019", "INE031B01049", "INE133A01011",
         "INE901L01018", "INE540L01014", "INE150B01021", "INE418H01029", "INE885A01032", "INE371P01015", "INE079A01024",
         "INE437A01024", "INE438A01022", "INE955V01021", "INE439A01020", "INE208A01029", "INE442H01029", "INE021A01026",
         "INE914M01019", "INE203A01020", "INE006I01046", "INE100A01010", "INE406A01037", "INE871C01038", "INE192R01011",
         "INE238A01034", "INE373A01013", "INE258A01016", "INE118H01025", "INE917I01010", "INE933K01021", "INE193E01025",
         "INE296A01024", "INE918I01018", "INE118A01012", "INE787D01026", "INE164A01016", "INE119A01028", "INE545U01014",
         "INE028A01039", "INE084A01016", "INE457A01014", "INE176A01028", "INE462A01022", "INE463A01038", "INE171Z01018",
         "INE263A01024", "INE465A01025", "INE257A01026", "INE029A01011", "INE838B01013", "INE397D01024", "INE121J01017",
         "INE376G01013", "INE340A01012", "INE836A01035", "INE416D01022", "INE233B01017", "INE472A01039", "INE050A01025",
         "INE032A01023", "INE323A01026", "INE791I01019", "INE216A01030", "INE752H01013", "INE421D01022", "INE486A01013",
         "INE007A01025", "INE679A01013", "INE010B01027", "INE477A01020", "INE476A01014", "INE475E01026", "INE180C01026",
         "INE120A01034", "INE172A01027", "INE482A01020", "INE483A01010", "INE736A01011", "INE348B01021", "INE055A01016",
         "INE739E01017", "INE085A01013", "INE178A01016", "INE149A01033", "INE121A01024", "INE059A01026", "INE491A01021",
         "INE522F01014", "INE704P01017", "INE591G01017", "INE259A01022", "INE111A01025", "INE169A01031", "INE741K01010",
         "INE299U01018", "INE298A01020", "INE136B01020", "INE950I01011", "INE503A01015", "INE499A01024", "INE271C01023",
         "INE016A01026", "INE00R701025", "INE288B01029", "INE124G01033", "INE435G01025", "INE917M01012", "INE836F01026",
         "INE385W01011", "INE361B01024", "INE935N01012", "INE600L01024", "INE089A01023", "INE126A01031", "INE230A01023",
         "INE284A01012", "INE532F01054", "INE066A01021", "INE285A01027", "INE548C01032", "INE913H01037", "INE510A01028",
         "INE988K01017", "INE406M01024", "INE042A01014", "INE255A01020", "INE302A01020", "INE258B01022", "INE171A01029",
         "INE686Y01026", "INE235A01022", "INE183A01016", "INE684F01012", "INE061F01013", "INE220J01025", "INE752P01024",
         "INE129A01019", "INE878A01011", "INE200A01026", "INE539A01019", "INE541A01023", "INE776C01039", "INE600K01018",
         "INE382Z01011", "INE276A01018", "INE481Y01014", "INE322A01010", "INE159A01016", "INE935A01035", "INE260B01028",
         "INE850D01014", "INE102D01028", "INE233A01035", "INE484J01027", "INE101D01020", "INE371A01025", "INE047A01021",
         "INE017A01032", "INE224A01026", "INE536A01023", "INE186A01019", "INE09N301011", "INE844O01030", "INE131A01031",
         "INE113A01013", "INE517F01014", "INE026A01025", "INE246F01010", "INE635Q01029", "INE545A01016", "INE860A01027",
         "INE127D01025", "INE040A01034", "INE795G01014", "INE548A01028", "INE982F01036", "INE473B01035", "INE176B01034",
         "INE578A01017", "INE978A01027", "INE158A01026", "INE093A01033", "INE019C01026", "INE049A01027", "INE038A01020",
         "INE066F01012", "INE531E01026", "INE094A01015", "INE030A01027", "INE267A01025", "INE671A01010", "INE031A01017",
         "INE001A01036", "INE090A01021", "INE765G01017", "INE726G01019", "INE763G01038", "INE725G01011", "INE008A01015",
         "INE092T01019", "INE043D01016", "INE559A01017", "INE039A01010", "INE530B01024", "INE466L01020", "INE821I01014",
         "INE962Y01021", "INE154A01025", "INE248A01017", "INE383A01012", "INE353K01014", "INE148I01020", "INE069I01010",
         "INE274G01010", "INE933S01016", "INE562A01011", "INE022Q01020", "INE053A01029", "INE242A01010", "INE565A01014",
         "INE335Y01012", "INE896L01010", "INE873D01024", "INE203G01027", "INE095A01012", "INE483S01020", "INE663F01024",
         "INE009A01021", "INE177A01018", "INE312H01016", "INE306R01017", "INE646L01027", "INE571A01020", "INE572A01028",
         "INE823G01014", "INE786A01032", "INE789E01012", "INE573A01042", "INE780C01023", "INE121E01018", "INE019A01038",
         "INE199G01027", "INE070D01027", "INE168A01041", "INE039C01032", "INE324A01024", "INE455T01018", "INE220G01021",
         "INE749A01030", "INE782A01015", "INE797F01012", "INE700A01033", "INE599M01018", "INE668F01031", "INE930H01023",
         "INE878B01027", "INE634I01029", "INE04I401011", "INE001B01026", "INE999A01015", "INE217B01036", "INE220B01022",
         "INE531A01024", "INE614B01018", "INE036D01028", "INE455I01029", "INE389H01022", "INE094I01018", "INE237A01028",
         "INE498L01015", "INE010V01017", "INE115A01026", "INE059D01020", "INE269B01029", "INE214T01019", "INE018A01030",
         "INE947Q01010", "INE970X01018", "INE473A01011", "INE326A01037", "INE150G01020", "INE348L01012", "INE123F01029",
         "INE490G01020", "INE883A01011", "INE002S01010", "INE288A01013", "INE271B01025", "INE774D01024", "INE101A01026",
         "INE536H01010", "INE998I01010", "INE766P01016", "INE522D01027", "INE103A01014", "INE196A01026", "INE585B01010",
         "INE180A01020", "INE112L01020", "INE018I01017", "INE842C01021", "INE405E01023", "INE099Z01011", "INE775A01035",
         "INE338I01027", "INE356A01018", "INE745G01035", "INE414G01012", "INE987B01026", "INE095N01031", "INE868B01028",
         "INE317F01035", "INE848E01016", "INE589A01014", "INE584A01023", "INE733E01010", "INE410P01011", "INE139A01034",
         "INE870D01012", "INE725A01022", "INE048G01026", "INE239A01016", "INE310A01015", "INE298J01013", "INE093I01010",
         "INE213A01029", "INE274J01014", "INE800H01010", "INE881D01027", "INE876N01018", "INE142Z01019", "INE743M01012",
         "INE603J01030", "INE572E01012", "INE195J01029", "INE877F01012", "INE191H01014", "INE761H01022", "INE262H01013",
         "INE347G01014", "INE182A01018", "INE602A01023", "INE211B01039", "INE318A01026", "INE140A01024", "INE205C01021",
         "INE455K01017", "INE134E01011", "INE752E01010", "INE074A01025", "INE811K01011", "INE010A01011", "INE199A01012",
         "INE179A01014", "INE160A01022", "INE615P01015", "INE976G01028", "INE020B01018", "INE320J01015", "INE944F01028",
         "INE415G01027", "INE855B01025", "INE343B01030", "INE613A01020", "INE027A01015", "INE703B01027", "INE301A01014",
         "INE891D01026", "INE131B01039", "INE002A01018", "INE612J01015", "INE018E01016", "INE123W01016", "INE002L01015",
         "INE640A01023", "INE647A01010", "INE226H01026", "INE058A01010", "INE513A01014", "INE839M01018", "INE285J01028",
         "INE807F01027", "INE916U01025", "INE109A01011", "INE498B01024", "INE070A01015", "INE087H01022", "INE722A01011",
         "INE721A01013", "INE003A01024", "INE671H01015", "INE343H01029", "INE269A01021", "INE683A01023", "INE572J01011",
         "INE285B01017", "INE460H01021", "INE062A01020", "INE114A01011", "INE00M201021", "INE089C01029", "INE939A01011",
         "INE659A01023", "INE258G01013", "INE232I01014", "INE044A01036", "INE424H01027", "INE660A01013", "INE387A01021",
         "INE805D01034", "INE399C01030", "INE195A01028", "INE040H01021", "INE665A01038", "INE225D01027", "INE398R01022",
         "INE586V01016", "INE778U01029", "INE690A01010", "INE038F01029", "INE886H01027", "INE494B01023", "INE142I01023",
         "INE488B01017", "INE151A01013", "INE467B01029", "INE192A01025", "INE670A01012", "INE672A01018", "IN9155A01020",
         "INE155A01022", "INE245A01021", "INE824B01021", "INE081A01012", "INE985S01024", "INE669C01036", "INE470Y01017",
         "INE331A01037", "INE152A01029", "INE594H01019", "INE508G01029", "INE325A01013", "INE280A01028", "INE685A01028",
         "INE813H01021", "INE849A01020", "INE064C01022", "INE974X01010", "INE691A01018", "INE516A01017", "INE628A01036",
         "INE334L01012", "INE551W01018", "INE481G01011", "INE692A01016", "INE686F01025", "INE854D01024", "INE951I01027",
         "INE665J01013", "INE054A01027", "INE366I01010", "INE710A01016", "INE884A01019", "INE051B01021", "INE825A01012",
         "INE665L01035", "INE200M01013", "INE398A01010", "INE386A01015", "INE410B01037", "INE669E01016", "INE226A01021",
         "INE342J01019", "INE191B01025", "INE192B01031", "INE274F01020", "INE716A01013", "INE075A01022", "INE049B01025",
         "INE256A01028", "INE520A01027", "INE768C01010", "INE738I01010"]

#starting frm 350
codes = ["INE142Z01019", "INE743M01012", "INE603J01030", "INE572E01012", "INE195J01029", "INE877F01012", "INE191H01014",
         "INE761H01022", "INE262H01013", "INE347G01014", "INE182A01018", "INE602A01023", "INE211B01039", "INE318A01026",
         "INE140A01024", "INE205C01021", "INE455K01017", "INE134E01011", "INE752E01010", "INE074A01025", "INE811K01011",
         "INE010A01011", "INE199A01012", "INE179A01014", "INE160A01022", "INE615P01015", "INE976G01028", "INE020B01018",
         "INE320J01015", "INE944F01028", "INE415G01027", "INE855B01025", "INE343B01030", "INE613A01020", "INE027A01015",
         "INE703B01027", "INE301A01014", "INE891D01026", "INE131B01039", "INE002A01018", "INE612J01015", "INE018E01016",
         "INE123W01016", "INE002L01015", "INE640A01023", "INE647A01010", "INE226H01026", "INE058A01010", "INE513A01014",
         "INE839M01018", "INE285J01028", "INE807F01027", "INE916U01025", "INE109A01011", "INE498B01024", "INE070A01015",
         "INE087H01022", "INE722A01011", "INE721A01013", "INE003A01024", "INE671H01015", "INE343H01029", "INE269A01021",
         "INE683A01023", "INE572J01011", "INE285B01017", "INE460H01021", "INE062A01020", "INE114A01011", "INE00M201021",
         "INE089C01029", "INE939A01011", "INE659A01023", "INE258G01013", "INE232I01014", "INE044A01036", "INE424H01027",
         "INE660A01013", "INE387A01021", "INE805D01034", "INE399C01030", "INE195A01028", "INE040H01021", "INE665A01038",
         "INE225D01027", "INE398R01022", "INE586V01016", "INE778U01029", "INE690A01010", "INE038F01029", "INE886H01027",
         "INE494B01023", "INE142I01023", "INE488B01017", "INE151A01013", "INE467B01029", "INE192A01025", "INE670A01012",
         "INE672A01018", "IN9155A01020", "INE155A01022", "INE245A01021", "INE824B01021", "INE081A01012", "INE985S01024",
         "INE669C01036", "INE470Y01017", "INE331A01037", "INE152A01029", "INE594H01019", "INE508G01029", "INE325A01013",
         "INE280A01028", "INE685A01028", "INE813H01021", "INE849A01020", "INE064C01022", "INE974X01010", "INE691A01018",
         "INE516A01017", "INE628A01036", "INE334L01012", "INE551W01018", "INE481G01011", "INE692A01016", "INE686F01025",
         "INE854D01024", "INE951I01027", "INE665J01013", "INE054A01027", "INE366I01010", "INE710A01016", "INE884A01019",
         "INE051B01021", "INE825A01012", "INE665L01035", "INE200M01013", "INE398A01010", "INE386A01015", "INE410B01037",
         "INE669E01016", "INE226A01021", "INE342J01019", "INE191B01025", "INE192B01031", "INE274F01020", "INE716A01013",
         "INE075A01022", "INE049B01025", "INE256A01028", "INE520A01027", "INE768C01010", "INE738I01010"]
"""
nifty = ["3MINDIA", "ABB", "ACC", "AIAENG", "APLAPOLLO", "AUBANK", "AARTIIND", "AAVAS", "ABBOTINDIA", "ADANIGAS",
         "ADANIGREEN", "ADANIPORTS", "ADANIPOWER", "ADANITRANS", "ABCAPITAL", "ABFRL", "ADVENZYMES", "AEGISCHEM",
         "AFFLE", "AJANTPHARM", "AKZOINDIA", "APLLTD", "ALKEM", "ALKYLAMINE", "ALLCARGO", "AMARAJABAT", "AMBER",
         "AMBUJACEM", "APOLLOHOSP", "APOLLOTYRE", "ARVINDFASN", "ASAHIINDIA", "ASHOKLEY", "ASHOKA", "ASIANPAINT",
         "ASTERDM", "ASTRAZEN", "ASTRAL", "ATUL", "AUROPHARMA", "AVANTIFEED", "DMART", "AXISBANK", "BASF", "BEML",
         "BSE", "BAJAJ-AUTO", "BAJAJCON", "BAJAJELEC", "BAJFINANCE", "BAJAJFINSV", "BAJAJHLDNG", "BALKRISIND",
         "BALMLAWRIE", "BALRAMCHIN", "BANDHANBNK", "BANKBARODA", "BANKINDIA", "MAHABANK", "BATAINDIA", "BAYERCROP",
         "BERGEPAINT", "BDL", "BEL", "BHARATFORG", "BHEL", "BPCL", "BHARATRAS", "BHARTIARTL", "INFRATEL", "BIOCON",
         "BIRLACORPN", "BSOFT", "BLISSGVS", "BLUEDART", "BLUESTARCO", "BBTC", "BOMDYEING", "BOSCHLTD", "BRIGADE",
         "BRITANNIA", "CARERATING", "CCL", "CESC", "CRISIL", "CSBBANK", "CADILAHC", "CANFINHOME", "CANBK", "CAPLIPOINT",
         "CGCL", "CARBORUNIV", "CASTROLIND", "CEATLTD", "CENTRALBK", "CDSL", "CENTURYPLY", "CENTURYTEX", "CERA",
         "CHAMBLFERT", "CHENNPETRO", "CHOLAHLDNG", "CHOLAFIN", "CIPLA", "CUB", "COALINDIA", "COCHINSHIP", "COLPAL",
         "CONCOR", "COROMANDEL", "CREDITACC", "CROMPTON", "CUMMINSIND", "CYIENT", "DBCORP", "DCBBANK", "DCMSHRIRAM",
         "DLF", "DABUR", "DALBHARAT", "DEEPAKNTR", "DELTACORP", "DHANUKA", "DBL", "DISHTV", "DCAL", "DIVISLAB", "DIXON",
         "LALPATHLAB", "DRREDDY", "EIDPARRY", "EIHOTEL", "ESABINDIA", "EDELWEISS", "EICHERMOT", "ELGIEQUIP", "EMAMILTD",
         "ENDURANCE", "ENGINERSIN", "EQUITAS", "ERIS", "ESCORTS", "ESSELPACK", "EXIDEIND", "FDC", "FEDERALBNK",
         "FINEORG", "FINCABLES", "FINPIPE", "FSL", "FORTIS", "FCONSUMER", "FRETAIL", "GAIL", "GEPIL", "GET&D", "GHCL",
         "GMMPFAUDLR", "GMRINFRA", "GALAXYSURF", "GRSE", "GARFIBRES", "GICRE", "GILLETTE", "GLAXO", "GLENMARK",
         "GODFRYPHLP", "GODREJAGRO", "GODREJCP", "GODREJIND", "GODREJPROP", "GRANULES", "GRAPHITE", "GRASIM", "GESHIP",
         "GREAVESCOT", "GRINDWELL", "GUJALKALI", "FLUOROCHEM", "GUJGASLTD", "GMDCLTD", "GNFC", "GPPL", "GSFC", "GSPL",
         "GULFOILLUB", "HEG", "HCLTECH", "HDFCAMC", "HDFCBANK", "HDFCLIFE", "HFCL", "HATHWAY", "HATSUN", "HAVELLS",
         "HEIDELBERG", "HERITGFOOD", "HEROMOTOCO", "HEXAWARE", "HSCL", "HIMATSEIDE", "HINDALCO", "HAL", "HINDCOPPER",
         "HINDPETRO", "HINDUNILVR", "HINDZINC", "HONAUT", "HUDCO", "HDFC", "ICICIBANK", "ICICIGI", "ICICIPRULI", "ISEC",
         "ICRA", "IDBI", "IDFCFIRSTB", "IDFC", "IFBIND", "IFCI", "IIFL", "IIFLWAM", "IRB", "IRCON", "ITC", "ITI",
         "INDIACEM", "ITDC", "IBULHSGFIN", "IBREALEST", "IBVENTURES", "INDIAMART", "INDIANB", "IEX", "INDHOTEL", "IOC",
         "IOB", "IRCTC", "INDOSTAR", "INDOCO", "IGL", "INDUSINDBK", "INFIBEAM", "NAUKRI", "INFY", "INGERRAND",
         "INOXLEISUR", "INTELLECT", "INDIGO", "IPCALAB", "JBCHEPHARM", "JKCEMENT", "JKLAKSHMI", "JKPAPER", "JKTYRE",
         "JMFINANCIL", "JSWENERGY", "JSWSTEEL", "JAGRAN", "JAICORPLTD", "J&KBANK", "JAMNAAUTO", "JINDALSAW", "JSLHISAR",
         "JSL", "JINDALSTEL", "JCHAC", "JUBLFOOD", "JUBILANT", "JUSTDIAL", "JYOTHYLAB", "KPRMILL", "KEI", "KNRCON",
         "KPITTECH", "KRBL", "KSB", "KAJARIACER", "KALPATPOWR", "KANSAINER", "KTKBANK", "KARURVYSYA", "KSCL", "KEC",
         "KOLTEPATIL", "KOTAKBANK", "L&TFH", "LTTS", "LICHSGFIN", "LAOPALA", "LAXMIMACH", "LTI", "LT", "LAURUSLABS",
         "LEMONTREE", "LINDEINDIA", "LUPIN", "LUXIND", "MASFIN", "MMTC", "MOIL", "MRF", "MGL", "MAHSCOOTER",
         "MAHSEAMLES", "M&MFIN", "M&M", "MAHINDCIE", "MHRIL", "MAHLOG", "MANAPPURAM", "MRPL", "MARICO", "MARUTI",
         "MFSL", "METROPOLIS", "MINDTREE", "MINDACORP", "MINDAIND", "MIDHANI", "MOTHERSUMI", "MOTILALOFS", "MPHASIS",
         "MCX", "MUTHOOTFIN", "NATCOPHARM", "NBCC", "NCC", "NESCO", "NHPC", "NIITTECH", "NLCINDIA", "NMDC", "NTPC",
         "NH", "NATIONALUM", "NFL", "NBVENTURES", "NAVINFLUOR", "NESTLEIND", "NILKAMAL", "NAM-INDIA", "OBEROIRLTY",
         "ONGC", "OIL", "OMAXE", "OFSS", "ORIENTCEM", "ORIENTELEC", "ORIENTREF", "PIIND", "PNBHOUSING", "PNCINFRA",
         "PTC", "PVR", "PAGEIND", "PERSISTENT", "PETRONET", "PFIZER", "PHILIPCARB", "PHOENIXLTD", "PIDILITIND", "PEL",
         "POLYMED", "POLYCAB", "PFC", "POWERGRID", "PRAJIND", "PRESTIGE", "PRSMJOHNSN", "PGHL", "PGHH", "PNB", "QUESS",
         "RBLBANK", "RECLTD", "RITES", "RADICO", "RVNL", "RAIN", "RAJESHEXPO", "RALLIS", "RCF", "RATNAMANI", "RAYMOND",
         "REDINGTON", "RELAXO", "RELIANCE", "REPCOHOME", "SBICARD", "SBILIFE", "SJVN", "SKFINDIA", "SRF", "SADBHAV",
         "SANOFI", "SCHAEFFLER", "SCHNEIDER", "SIS", "SEQUENT", "SFL", "SCI", "SHOPERSTOP", "SHREECEM", "RENUKA",
         "SHRIRAMCIT", "SRTRANSFIN", "SIEMENS", "SOBHA", "SOLARINDS", "SONATSOFTW", "SOUTHBANK", "SPANDANA", "SPICEJET",
         "STARCEMENT", "SBIN", "SAIL", "SWSOLAR", "STRTECH", "STAR", "SUDARSCHEM", "SUMICHEM", "SPARC", "SUNPHARMA",
         "SUNTV", "SUNDARMFIN", "SUNDRMFAST", "SUNTECK", "SUPRAJIT", "SUPREMEIND", "SUZLON", "SWANENERGY", "SYMPHONY",
         "SYNGENE", "TCIEXP", "TCNSBRANDS", "TTKPRESTIG", "TVTODAY", "TV18BRDCST", "TVSMOTOR", "TAKE", "TASTYBITE",
         "TATACOMM", "TCS", "TATACONSUM", "TATAELXSI", "TATAINVEST", "TATAMTRDVR", "TATAMOTORS", "TATAPOWER",
         "TATASTLBSL", "TATASTEEL", "TEAMLEASE", "TECHM", "NIACL", "RAMCOCEM", "THERMAX", "THYROCARE", "TIMETECHNO",
         "TIMKEN", "TITAN", "TORNTPHARM", "TORNTPOWER", "TRENT", "TRIDENT", "TIINDIA", "UCOBANK", "UFLEX", "UPL",
         "UJJIVAN", "UJJIVANSFB", "ULTRACEMCO", "UNIONBANK", "UBL", "MCDOWELL-N", "VGUARD", "VMART", "VIPIND", "VRLLOG",
         "VSTIND", "VAIBHAVGBL", "VAKRANGEE", "VTL", "VARROC", "VBL", "VENKEYS", "VESUVIUS", "VINATIORGA", "IDEA",
         "VOLTAS", "WABCOINDIA", "WELCORP", "WELSPUNIND", "WESTLIFE", "WHIRLPOOL", "WIPRO", "WOCKPHARMA", "ZEEL",
         "ZENSARTECH", "ZYDUSWELL", "ECLERX"]


def get_url_name(ticker):
    for i in range(2, source.max_row + 1):
        if(source.cell(i,3).value == ticker):
            return source.cell(i,1).value, source.cell(i,2).value
    return "invalid","invalid"

def dismiss_alert():
    try:
        obj = browser.switch_to.alert
        obj.dismiss()
        return "yes"
    except:
        return "no"


wb = xl.load_workbook('peers_pd.xlsx')
wb_source = xl.load_workbook("source_urls.xlsx")
source = wb_source["sheet"]

names = []
values = []
row_no = 2
sheet = wb["Sheet1"]
def formatted_str(arr):
    new_list = []
    for a in arr:
        if(a != '' and a != ' '):
            new_list.append(a)
    return new_list
for j in range(len(nifty)):
    url,name = get_url_name(nifty[j])
    if(url != "invalid"):
        try:
            page = requests.get(url)
            browser = BeautifulSoup(page.content, 'html.parser')
            standalone = browser.find(id = "standalone_valuation")
            str = standalone.get_text().split("\n")
           # print(standalone.get_text())
            for i in range(3, int((len(str) - 1))):
                if (i % 2 != 0):
                    names.append(str[i])
                else:
                    values.append(str[i])
            # for i in range(len(names)):
            #     for k in range(column_index_from_string("F"), column_index_from_string("Q") + 1):
            #         if (sheet.cell(1, k).value == names[i]):
            #             sheet.cell(row_no, k).value = values[i]
            names = formatted_str(names)
            values = formatted_str(values)
            for i in range(len(names)-4):
                for k in range(column_index_from_string("F"), column_index_from_string("P") + 1):
                     if (sheet.cell(1, k).value == names[i]):
                         sheet.cell(row_no, k).value = values[i]
            sheet.cell(row_no, column_index_from_string("Q")).value = values[ len(values)-2 ]
            names = []
            values = []
            wb.save("peers_pd.xlsx")
            row_no = row_no + 1
        except:
            print(nifty[j] + " : " + sheet.cell(row_no, 1).value)
            row_no = row_no + 1
        print(j)
