Linking python to excel to create a database of data

I’m really new to this but I’ve managed to cobble together some code to download data from interactive brokers (you need an account - sorry).

When I start the download it is super quick, however after 100 ish days of data it slows dramatically, I know its something to do with the excel file size getting bigger but can’t figure out what. I’ve tried moving the open and save lines but then the data overwrites each time so I don’t gather the data…

Any help greatly appreciated, thanks. Here is the code:

#  Notes
# Use to get one instrument at a time and put the data into an excel file RandonRawData.xlsx (which must already exist)
# Its slow and will crash if the RandomRawData.xlsx file is open
# It also has major problems if you enter the wrong data - sometimes it hangs excel and corrupts RRD excel file
#     If this happens recreate a new RRD.xlsx file (I will work on these things)
#
#   SPX IND CBOE
#   COMP IND NASDAQ
#   INDU IND CME


from ibapi.client import EClient
from ibapi.wrapper import EWrapper
from ibapi.client import Contract
from ibapi.common import *
import time
import openpyxl

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

    def error(self, reqId: TickerId, errorCode: int, errorString: str):
        super().error(reqId, errorCode, errorString)
        print("Error. Id: ", reqId, " Code: ", errorCode, " Msg: ", errorString)

    def historicalData(self, reqId: int, bar: BarData):
        print("HistoricalData. ", reqId, " Date:", bar.date, "Open:", bar.open,
              "High:", bar.high, "Low:", bar.low, "Close:", bar.close, "Volume:", bar.volume,
              "Count:", bar.barCount, "WAP:", bar.average)
        rowData = (bar.date, bar.open, bar.high, bar.low, bar.close, bar.volume, bar.barCount, bar.average)

        # append data to file (but we have erased the sheet and
        # entered a new one so the data will always be in the same place)
        r = wb[instrument].max_row
        r += 1
        for i, header in enumerate(rowData):
            wb[instrument].cell(row=r, column=i+1).value = header
            wb.save('RandomRawData.xlsx')

    def historicalDataEnd(self, reqId: int, start: str, end: str):
        # finish getting data
        self.done = True


def main():

    app = TestApp()
    app.connect("127.0.0.1", 7497, 2)  # socket port is set in TWS or IB Gateway settings

    time.sleep(1)  # short sleep to allow connection

    contract = Contract()
    contract.symbol = instrument
    contract.secType = securitytype
    contract.exchange = exchange
    contract.currency = "USD"
    contract.lastTradeDateOrContractMonth = "201809"
    # contract.localSymbol = "GCQ8"

    app.reqHistoricalData(1, contract, "", length, barSize, "TRADES", 1, 1, False, [])
    print(instrument)
    app.run()


#global program
instrument = input("Enter the Instrument>")
securitytype = input("Enter the type of security (STK, FUT ...) >")
exchange = input("Enter the exchange >")
length = input("Enter the length for the Data (1 D, 1800, S...) >")
barSize = input("Enter the bar size for the Data (30 mins, 1 day...) >")


wb = openpyxl.load_workbook('RandomRawData.xlsx')
all_sheets = wb.sheetnames
# print(all_sheets)


# TRY getting the instruments from the workbook (note for Ruth)


if instrument in all_sheets:
    wb.remove(wb[instrument])
    ws = wb.create_sheet(instrument)
    print(f"yes {instrument} is here")
else:
    print(f"no {instrument} is not here")
    ws = wb.create_sheet(instrument)


if __name__ == "__main__":
    main()

holy smokes, got inspiration just putting this out there - moved line 39 to 64 and it runs in seconds vs 30 mins before :slight_smile: happy days…

This looks cool. I’d like to learn about working with sockets. Is that built into the EClient class? It looks as though that ibapi module isn’t in the standard library. How did you come across it? Do all these brokers use the same API, or does ibapi handle that?

Tip for future posts: enclose your code samples in triple backticks like this ```
eg:
```

print("Just gimme money!")

```

That formatting is way easier to read and copy-paste.

Thanks, if you use IB you can download the ibapi code from their github area - its actually pretty cool but I’ve only been using it for a month or so… haven’t automated any trading yet but that is the ultimate goal !

1 Like

That’s actually really common. You’ll post or send out a question or ask about your code, and doing that describes your problem in a new way, giving you new perspective on your problem. Then you suddenly get a solution. It’s so common some people buy little rubber ducky dolls or bobble heads, put them on their monitors, and talk to it to solve a problem.

I also added the [code] .. [/code] around your code so that it’s readable.

Hi Chipper!
I’m also trying to use the IBKR API but I find the documentation really superficial and confusing.

By any chance, would you be able to help me sharing the code that you were able to put together.

Basically I am trying to pull historical data for a set of stocks (like past 180 days), as well as other information available via IBKR (Price Target etc.).

Would be great if we could work together and team up in the effort to make the most of the IBKR api.

Best regards from Madrid!

Edoardo

Hey @edoardodg, you should start a new topic and see if more people have answers. Many times finding an old dead thread to post on doesn’t get results as much as a new post.

A free service run by Zed A. Shaw for learncodethehardway.org.