Historical Data with Excel
In the previous tutorial, we showed you how to request real time quotes from TWS using the DDE TWS API. In this tutorial, we will show you how to request historical data from TWS, although the process for doing so is slightly more complicated. You will need to add some simple Visual Basic (VBA) code to your Excel worksheet to obtain the data.
Please make sure you have already acquired What You Will Need before going forward in this tutorial.
Preparing the request
Just as with real time data, historical data requests need first to ask the TWS to “prepare” the data we are interested in. The TWS needs to know not only the specific instrument but also:
- The ending date and time from which we want to collect the data, formatted as: yyyymmdd hh:mm:ss.
- The time duration comprising the data from the ending date going back in time.
- The bar size LYNX provides historical data in open, high, low and close bar data format).
- The type of data (i.e. MIDPOINT, TRADES, etc.).
- Whether we want data generated during regular trading session or not.
- The date format in which each bar’s time and date will be presented.
The formula to be used for historical data requests is:
=[twsuser]|hist!'id[requestId]?req?[symbol]_[type]_[exchange]_[currency]_~/[yyyymmdd]singleSpace[HH]singleColon[mm]singleColon[ss]_[duration amount]singleSpace[duration unit]_[bar size]_[rth only?]_[what to show]_[date format]'
|twsuser||The username with which you logged into TWS.|
|requestId||The request’s unique identifier (any positive integer).|
|symbol||The instrument’s symbol.|
|type||The type of instrument.|
|exchange||The instrument’s exchange.|
|currency||The instrument’s currency (USD).|
|yyyymmdd hh:mm:ss||End date for the historical data query.|
|duration amount||The number of time units for the duration time.|
|duration unit||The duration's time unit.|
|bar size||The bar size.|
|rth only||Set to 1 to obtain only data generated during regular trading hours (RTH), or set to 0 to get all data generated during and outside of of RTH.|
|what to show||The type of data: MIDPOINT, TRADES, BID, ASK, etc.|
|data format||Set to 1 to format the resulting bars’ date as yyyymmss hh:mm:ss. Set to 2 to express the resulting bars’ time as the number of seconds since 1970.|
How to Handle Spaces and Colons in the Formula
Our DDE links cannot contain certain special characters such as spaces or colons, but you will need to use these characters in your DDE formula. To overcome this limitation, we have provided keywords that you can use in place of the actual special character: singleSpace and singleColon. For example, if you want to specify an end date and time such as March 2, 2015 at 23:59:59 in the format specified above, you would then enter:
This translates into:
This applies to all cases in which you need spaces or colons in the DDE formula. This is particularly important when describing futures or options contracts because you can then use their local symbols, which often include spaces. For example, the DBK futures contract expiring on May 2015 has a local symbol DBKG MAY 15 which you would provide as:
Enter the Historical Data Request
Let's continue with our historical data request. As an example, try to pull MIDPOINT historical data for the EUR.USD currency pair prior to February 27th 2015 at 23:59:59 in thirty minutes bars (9), for a duration of one day (1 D). The correct formula for this request is:
Copy the above formula into an empty cell in your Excel worksheet. Notice that the cell displays PROCESSING, which, if everything proceeds without error, will change into RECEIVED”:
At this point, you have just told TWS that you want our EUR.USD historical data and TWS replied that the data has been received from the server and is ready to be viewed.
This is where the process becomes slightly complicated because, unlike real time market data, where each incoming price is obtained using a very specific formula, you will not fetch each bar one by one with a formula (this is quite fortunate since we could be expecting hundreds of bars!). Instead, you will read all the bars together using a single DDE request and then display them in your worksheet with the help of some VBA code. For purposes of simplicity, we will keep the coding to minimum.
In the next steps, we will briefly describe how to add a button to a spreadsheet for the sake of completeness but remember that it is out of the scope of LYNX's support to provide any assistance on using Excel.
Receiving the data
Receiving the Data - Add a Button
In this step, you will add a button to your blank worksheet which, once the TWS has replied to your historical data request with the “RECEIVED” status, will help you manually invoke the VBA routines which pull the historical data from TWS.
First, open the Developer tab in Excel and click on the Button form control:
Next, click anywhere in your spreadsheet to place the button. The Assign Macro dialog opens; this is where you associate a VBA macro with your button. Name your function fetchHistoricalData and then click the New button in the dialog.
Excel automatically opens the VBA editor, which displays the skeleton of the newly-created macro.
In the next step, you will add the code to the macro you just created.
Receiving the Data - Add the Code
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.
Understanding the Formula
To understand the formulas' syntax, please refer to the Historical Data section from the DDE Formula Reference page.
For more contract definition samples via DDE, please refer to How to Find the Definition of a Contract .