I want data from DDE to pandas dataframe without using excel, i am getting data like below

Question

Symbol Name XCH LTP Qty.    Chg % Chg   Bid Qty Bid Ask Ask Qty Total Bid   Total Ask   Open    P.Close Low High    T.Volume    Strike Price    Exp. Date <br>
NIFTY23JUN18500CE   NSE 
=DARTSCRDE|SYMBOL_TPRICE!'2060225_0'
=DARTSCRDE|SYMBOL_VOLUME!'2060225_0'
=DARTSCRDE|SYMBOL_CHANGE!'2060225_0'
=DARTSCRDE|SYMBOL_CHANGEPC!'2060225_0'
=DARTSCRDE|SYMBOL_BIDQTY!'2060225_0'
=DARTSCRDE|SYMBOL_BID!'2060225_0'
=DARTSCRDE|SYMBOL_ASK!'2060225_0'
=DARTSCRDE|SYMBOL_ASKQTY!'2060225_0'
=DARTSCRDE|SYMBOL_TOTBID!'2060225_0'
=DARTSCRDE|SYMBOL_TOTASK!'2060225_0'
=DARTSCRDE|SYMBOL_OPEN!'2060225_0'
=DARTSCRDE|SYMBOL_CLOSE!'2060225_0'
=DARTSCRDE|SYMBOL_LOW!'2060225_0'   
=DARTSCRDE|SYMBOL_HIGH!'2060225_0'
=DARTSCRDE|SYMBOL_TOTVOL!'2060225_0'
=DARTSCRDE|StrikePrice!'2060225_0'
=DARTSCRDE|ExpDate!'2060225_0'

NIFTY23JUN18500PE   NSE
=DARTSCRDE|SYMBOL_TPRICE!'2060226_0'
=DARTSCRDE|SYMBOL_VOLUME!'2060226_0'
=DARTSCRDE|SYMBOL_CHANGE!'2060226_0'
=DARTSCRDE|SYMBOL_CHANGEPC!'2060226_0'
=DARTSCRDE|SYMBOL_BIDQTY!'2060226_0'
=DARTSCRDE|SYMBOL_BID!'2060226_0'
=DARTSCRDE|SYMBOL_ASK!'2060226_0'
=DARTSCRDE|SYMBOL_ASKQTY!'2060226_0'
=DARTSCRDE|SYMBOL_TOTBID!'2060226_0'
=DARTSCRDE|SYMBOL_TOTASK!'2060226_0'
=DARTSCRDE|SYMBOL_OPEN!'2060226_0'
=DARTSCRDE|SYMBOL_CLOSE!'2060226_0'
=DARTSCRDE|SYMBOL_LOW!'2060226_0'
=DARTSCRDE|SYMBOL_HIGH!'2060226_0'
=DARTSCRDE|SYMBOL_TOTVOL!'2060226_0'
=DARTSCRDE|StrikePrice!'2060226_0'
=DARTSCRDE|ExpDate!'2060226_0'

This is how I am getting data using DDE in Excel. Any suggession how can get it in DataFrame in Python?

Answer 1

import pandas as pd
import xlwings as xw


app = xw.App(visible=False)
wb = app.books.open('A.xlsx')  


symbol_names = [
    "NIFTY23JUN18600CE",
    "NIFTY23JUN18600PE",
    "NIFTY23JUN18700CE",
    "NIFTY23JUN18700PE"
]
dde_item_names = [
    "SYMBOL_TPRICE",
    "SYMBOL_VOLUME",
    "SYMBOL_CHANGE",
    "SYMBOL_CHANGEPC",
    "SYMBOL_BIDQTY",
    "SYMBOL_BID",
    "SYMBOL_ASK",
    "SYMBOL_ASKQTY",
    "SYMBOL_TOTBID",
    "SYMBOL_TOTASK",
    "SYMBOL_OPEN",
    "SYMBOL_CLOSE",
    "SYMBOL_LOW",
    "SYMBOL_HIGH",
    "SYMBOL_AVG",
    "SYMBOL_TOTVOL",
    "SYMBOL_TOTVAL",
    "SYMBOL_OI",
    "NO_CONTRACT",
    "StrikePrice",
    "ExpDate"
]


columns = ["Symbol"] + dde_item_names
df = pd.DataFrame(columns=columns)


for symbol in symbol_names:
    row = [symbol]
    for item in dde_item_names:
        dde_topic = f"=DARTSCRDE|{item}!'2060229_0'"
        value = wb.sheets[0].range(dde_topic).value
        row.append(value)
    df = df.append(pd.Series(row, index=columns), ignore_index=True)


app.quit()


print(df)

Tried this but it's not efficient.
[and changed to import pandas as pd]