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()
happy days…