Here are the routines which will finally obtain the data from the TWS:

Sub fetchHistoricalData()

    'This variable will store the incoming data

    Dim TheArray() As Variant

    'Fetch the data from the TWS...

    '(Replace sample123 with your own TWS username!)

    TheArray = getData("Ssample123", "hist", "id4?result")

    '... and pass the result into another function which will populate the sheet

    Call populate(TheArray)

End Sub

'This function triggers a DDE request and returns its response

Function getData(serverName, topic, request)

    Dim chan As Integer

    'Initiate the DDE channel

    chan = Application.DDEInitiate(serverName, topic)

    'Perform the request

    getData = Application.DDERequest(chan, request)

    'Terminate the channel

    Application.DDETerminate chan

End Function

'Populate our blank sheet with the incoming data

Sub populate(ByRef TheArray() As Variant)

'Watch out for empty possible errors and handle properly.

On Error GoTo ErrHandler

    For i = 1 To UBound(TheArray)

        Range("F" & i + 1).Value = TheArray(i, 1)

        Range("G" & i + 1).Value = TheArray(i, 2)

        Range("H" & i + 1).Value = TheArray(i, 3)

        Range("I" & i + 1).Value = TheArray(i, 4)

        Range("J" & i + 1).Value = TheArray(i, 5)

        Range("K" & i + 1).Value = TheArray(i, 6)

        Range("L" & i + 1).Value = TheArray(i, 7)

        Range("M" & i + 1).Value = TheArray(i, 8)

        Range("N" & i + 1).Value = TheArray(i, 9)

    Next

ErrHandler:

    Exit Sub

End Sub

The fetchHistoricalData method invokes the getData function passing in:

The DDE server name, which is your TWS username prefixed with a capital S
The DDE “topic” for historical data: “hist”
A third parameter which is just the remaining fragment of the DDE link: id[requestId]?result

The third parameter contains the request ID you used in the requesting formula (4). Remember this same procedure from the previous tutorial when you requested real time data. Your request/retrieve formulas both need to include the exact same ID.

If you correctly entered the code into your macro in the VBA editor as shown above, your Excel worksheet should look very similar to the image below. (Note that we have changed the button label to Historical from its default value).

Just after the data is retrieved from TWS, the requesting formula will change its output to FINISHED.

It is very important for to wait until the request formula’s output changes from PROCESSING to RECEIVED before you try to pull the actual data from TWS. If the cell displays PROCESSING for too long, then it is very likely there was an error in your request. If this happens, make use of the error retrieval formulas explained in Obtain the Last Available Error.

The next step is to Understand the Formula.