IB Historical Data via Excel DDE

B

blackstormcap

hello guys, in the sample DDE workbook, historical data will be downloaded onto a new worksheet. I want the data to be downloaded onto the same worksheet. In other word, i wanna manupulate the data download area. Question is How?

I looked at the VBA below but cant find my way.

Sub requestHistoricalData()
Dim server As String, req As String, reqType As String, id As String
server = util.getServerStr(serverCell)
If server = "" Then Exit Sub
If Not util.composeContractReq(ActiveCell, req, reqType, True) Then Exit Sub
id = util.getIDpost(genId)

Dim endDateTime As String, duration As String, barSize As String, whatToShow As String, _
rthOnly As String, dateFormatStyle As String, allowExpired As String
endDateTime = UCase(ActiveCell.offset(0, reqOffset + 1).value)
duration = UCase(ActiveCell.offset(0, reqOffset + 2).value)
barSize = UCase(ActiveCell.offset(0, reqOffset + 3).value)
whatToShow = UCase(ActiveCell.offset(0, reqOffset + 4).value)
rthOnly = UCase(ActiveCell.offset(0, reqOffset + 5).value)
dateFormatStyle = UCase(ActiveCell.offset(0, reqOffset + 6).value)
allowExpired = UCase(ActiveCell.offset(0, reqOffset + 8).value)
If allowExpired <> "" Then
allowExpired = util.UNDERSCORE & allowExpired
End If

If endDateTime = "" Or duration = "" Or barSize = "" Or whatToShow = "" Or rthOnly = "" Or dateFormatStyle = "" Then
MsgBox ("You must enter all of endDateTime, duration type, barSize, whatToShow, rthOnly, and dateFormatStyle.")
Exit Sub
End If

req = req & util.UNDERSCORE & endDateTime & util.UNDERSCORE & duration & util.UNDERSCORE & _
barSize & util.UNDERSCORE & whatToShow & util.UNDERSCORE & rthOnly & util.UNDERSCORE & _
dateFormatStyle & allowExpired

Cells(ActiveCell.row, controlCol).Formula = util.composeControlLink(server, topic, id, reqType, req)
ActiveCell.offset(1, 0).Activate ' move cursor down
End Sub

Function composeControlLink(server, topic, id, reqType, rawReq) As String
composeControlLink = composeLink(server, topic, TICK_CHAR & id, reqType & QMARK & rawReq & TICK_CHAR)
End Function
 
Quote from blackstormcap: I want the data to be downloaded onto the same worksheet.
More...
It is unclear to me what you really mean by that.
If you give the same name into cell R11 and after (column Page Name) it will load the data one after the other into this page. If you have multiple request the data will be overwritten.
If you want to keep all the data what I do is that i send all the download into the same sheet but after each download I just copy paste the data where I want it to be into another sheet.
 
Thanks for the reply.
I assume that you have used that sample excel file before?

i am trying to download historical data for multiple names (close to 100+) for backtesting reason, and you are saying:

-type the same worksheet name in the R column so every name will be downloaded there.

-use a loop or something to automate the data request one name after another

-when the data for a certain name is downloaded, copy and paste the data somewhere else so that they wont be overwritten

is that right? for the copy and paste step, that sounds very cumbersome. the default code downloads data onto cell B2, if we can change that download area, it will be easier.
 
Yes I do use the sample spreadsheet.
Carefull on the pacing errors if you have many symbols. Look into the documentation for details. They are very frustrating. You may have to add some Sleep() here and there.
I believe you do not have access to all the functions in the code so you cannot change de destination cell from B3 to H3 for example.
So yes I do a loop that use the Request function with a little wait in between each request and I also copy the data into another page. It is a lot of complications to download data but when it's done once youre good.
It is not very fast because you have to wait for the data to come and insert some waits so with 100 stocks you can't really use it intraday. But it is good for backtesting, but IB only keeps intraday data 100 days back I think.
Good luck
 
it seems that IB is only an execution/trading platform, with almost no backtesting functions given that simply getting historical data in the right format is such a hassle.
 
You must log in or register to reply here.
Back
Top