Storing API Data Into A DataFrame

Question

I am running a Python Script to collect financial market data from Interactive Brokers API. After connecting to the API the terminal prints out requested historical data. How do I have the data saved into a dataframe rather than streamed in terminal?

from ibapi.wrapper import EWrapper
from ibapi.client import EClient 
from ibapi.contract import Contract
from ibapi.ticktype import TickTypeEnum


class TestApp(EWrapper, EClient):
    def __init__(self):
        EClient.__init__(self,self)

    def error(self, reqId, errorCode, errorString):
        print("Error: ", reqId, " ", errorCode, " ", errorString)

    def historicalData(self, reqId, bar):
        print("historicalData. ", reqId, "Data:", bar.date, "Open:", bar.open, "High:", bar.high, "low:", bar.low, "close:", bar.close, "Volume:", bar.volume, "WAP:", bar.average)


def main():
    app = TestApp()
    app.connect("127.0.0.1", 7497, 0)

    contract = Contract()
    contract.symbol = "EUR"
    contract.secType = "CASH"
    contract.exchange = "IDEALPRO"
    contract.currency = "USD"

    app.reqHistoricalData(1, contract, "", "1 D", "1 min", "MIDPOINT", 0, 1, False, [])

    app.run()


if __name__ == "__main__":
    main()

The output of the code prints a feed of historical data, such as:

historicalData.  1 Data: 20200616  11:53:00 Open: 1.125985 High: 1.12601 low: 1.12592 close: 1.12592 Volume: -1 WAP: -1.0
historicalData.  1 Data: 20200616  11:54:00 Open: 1.12592 High: 1.125925 low: 1.12583 close: 1.125885 Volume: -1 WAP: -1.0
historicalData.  1 Data: 20200616  11:55:00 Open: 1.125885 High: 1.126045 low: 1.125865 close: 1.126045 Volume: -1 WAP: -1.0

How do I have this information stored into a dataframe rather than just printed in terminal?

score 1 · Accepted Answer · answered Jul 06 '20 at 20:28

1

You could create a dataframe in the TestApp object then add a row to it every time historicalData() is called.

def __init__(self):
    ...
    self.df = pd.DataFrame(columns=['date', 'open', 'high', 'low', 'close', 'volume'])


def historicalData(self, reqId, bar):
    self.df.loc[len(self.df)] = [bar.date, bar.open, bar.high, bar.low, bar.close, bar.volume]

answered Jul 06 '20 at 20:28

Josh

706
3
8

Josh - thank you. Python noob here: how do I take this code and actually output the dataframe produced? If trying to print or access df it does not exist. I want to output df then store the data in sql. – quantionaire Sep 02 '20 at 14:22
#Create sql and table conn = sqlite3.connect('BB', check_same_thread = False) c = conn.cursor() c.execute('CREATE TABLE test6 (Dates text, Open number)') conn.commit() #Interactive Brokers Test app class IBapi(EWrapper, EClient): def __init__(self): EClient.__init__(self, self) self.cols = ['dates', 'open'] self.df = pd.DataFrame(columns=self.cols) #Historical Data def historicalData(self, reqId, bar): self.df.loc[len(self.df)] = [bar.date, bar.open] self.df.to_sql('test6', conn, if_exists='replace', index = False) – quantionaire Sep 02 '20 at 21:18

score 0 · Answer 2 · answered Feb 04 '21 at 13:34

0

You can use DataFrame.append() function too.

After all historical data is returned, write the whole DataFrame into database, and then clear up the original dataframe to reuse for the next cycle.

answered Feb 04 '21 at 13:34

data2wealth

423
3
9

Storing API Data Into A DataFrame

2 Answers2

Linked