DDE and Excel VBA

inspired by the thinkorswim DDE thread that was recently bumped:

can anyone explain why DDE links continue to fire rapidly even when the real-time data isn't visibly updated? no matter which data i bring into a cell (open, close, last), pretty much anytime volume goes through on that ticker, the DDE link is fired.

i am using worksheet_calculate to run a hefty loop; thus, it is being triggered nonstop. there are a number of safeguards in place to kill the loop before it starts getting intensive in case the data hasn't registered a change in a cell, but i am sure there has to be a better way.

i have a similar version that is using application.ontime to run every 1sec, but then of course i have latency issues.

i tried setlinkondata, but once again, the linked macro continually updates.

any thoughts as to why they update so actively?

 

In order to optimize real time data loading into excel you want to utilize VB setlinkondata function. Using this feature you can have real time data update faster than the 1 second timer you are using.

Excel out of the box will not update real time data links faster than 100ms. Setlinkondata can be set to trigger a macro to run whenever new data is present.

I use this function to update and transfer the entire block into the worksheet. As a result my real time data update excel at 10ms resolution. We also write the tick data out to SQLite DB at the same time.

Try rewriting using setlinkondata and control the calculations in the macro that it triggers. ie. Turn calculations off, load data into worksheet, turn calculations back on. The triggered macro will update all tick data in a single calculation cycle.

Our DDE cell formula calls a defined name which contains the text syntax for the DDE Call:

DDE_Command
=OEC_Account&"|"&DDE_TOPIC&"!'"&Symbol&"?"&DDE_COL&"'"

DDE_TOPIC
=INDEX(T_1_H,1,oCol)

DDE_COL
=INDEX(T_1_H,2,oCol)



Sub Start_Links()
' Check for OEC Trader and Initialize SQLite DB
Check_OEC
If Range("g_oec_running") = 1 Then
initialize_db
Reset_DDE
Range("G_Process_Ticks") = 1

' Get all DDE Links and set trigger for just ask/bid/last price updates

Dim aLinks As Variant
Dim i As Long

aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
If UCase(Right(aLinks(i), 4)) = "?ASK" Or UCase(Right(aLinks(i), 4)) = "?BID" Or UCase(Right(aLinks(i), 4)) = "LAST" Then
ActiveWorkbook.SetLinkOnData aLinks(i), "get_ticks"
End If
Next i
End If
End If
End Sub


Sub get_ticks()

''' Open DDE Channel to any known piece of data. This forces all pending DDE updates into the worksheet cells in one shot.
Dim ddechan, F1 As Variant
ddechan = Application.DDEInitiate(app:=m_oec, topic:="quote")
F1 = Application.DDERequest(ddechan, "ESH1?Symbol")
Application.DDETerminate ddechan

' Bail out of processing worksheet if no new price data is present
' Serial_Update_range compares the current price data range with the last update set. (Table T1 to Table T2)
; If there is a change in any bid/ask/last the calculation will be >0

m_count = WorksheetFunction.CountIf(Range("Serial_Update_Range"), ">0")
If m_count = 0 Then
Exit Sub
End If

'Update T2 Workbook Range and write new records out to DB
Application.Calculation = xlCalculationManual
Dim AllCells As Range, Cell As Range, xv As String
Set AllCells = Range("Serial_Update_Range")
DB.cnnbegin
For Each Cell In AllCells
If Cell.Value2 > 0 And Cell.Value2 < 1000 Then
Range("index(T_2," & Cell.Value2 & ",)") = Range("index(T_1," & Cell.Value2 & ",)").Value2
DB.do_cmd2 Range("index(T_1," & Cell.Value2 & ",)").Value2
End If
Next Cell
DB.cnncommit
Exit Sub
End Sub


Sub Stop_Links()
' Code to stop updates'
Range("G_Process_Ticks") = 0
Clear_DDE
On Error Resume Next
Dim aLinks As Variant
Dim i As Long

aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
ActiveWorkbook.SetLinkOnData aLinks(i), ""
Next i
End If

End Sub


Sub Clear_DDE()
Range("t_1_dde_quote").ClearContents
End Sub

Sub Reset_DDE()
'Reset and rebuild DDE Formulas:
Dim i, j, up_q, up_c, up_c1, up_ticks, up_set
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

i = WorksheetFunction.CountIf(Range("Index(T_1, , 7)"), "<>-1")
Range("t_1_dde_quote").ClearContents
For j = 1 To i
up_set = Range("index(t_1," & j & ",6)")
If up_set = 1 Then
up_q = "index(t_1_dde_quote," & j & ",)"
Range(up_q) = Range("formulas_dde_quote").Formula
Range(up_q) = Range(up_q).Value2
End If
Next j
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

with your sub:

"
Dim ddechan, F1 As Variant
ddechan = Application.DDEInitiate(app:=m_oec, topic:="quote")
F1 = Application.DDERequest(ddechan, "ESH1?Symbol")
Application.DDETerminate ddechan
"

you state that just one piece of data forces ALL dde links to update (including data for symbols not = ESH1). is this correct? my implementation was confused here.

thanks.

 

very sorry to respond so late, i cross-posted this on another forum and forgot i posted it here as well.

thank you very much for the code PocketChange. setlinkondata is definitely faster...however, it might be too fast. my loop executes an order based on certain conditions being met, and then it waits for the corresponding order message to be received in order to make another decision. it does this for each stock in a range, and i am finding that the messages aren't loading fast enough and sometimes populating my loop with the wrong data. i have a few fail-safes in there to check and make sure the correct conditions are met, but i am getting bug after bug every time i test it.

also, in order to speed up the collection of messages, i turn off calculations once the message query has been called, and resume them after the messages have collected. these messages occur in the middle of the loop...does that mean my calculations are turned off across the entire workbook as well? if that's the case, i will probably have to go with what you said and kill calculations before i trigger the loop (with setlinkondata), so that my loop isn't trying to calculate while the message query dictates that calculations are off.

fyi, i am using activex/vba, not dde (on most of the code). Redi is my software if that makes a difference. much thanks in advance

 

1. Make sure iterative calculations are turned off.
2. Take Control of the Calculations.
3. Look at optimizing your loops and try to minimize data transfer between VBA and your worksheet.

There is a lot of misinformation online stating writing entire arrays to a range is fastest. Generally true but not when every millisecond counts. Each piece of data has overhead approx 10 microseconds.

If your updating a range of 500 symbols with 5 columns of data by overwriting the entire range the transfer will take approx 250ms. If you loop through and only update the rows of symbols with changes it will drastically improve your performance. Turn off calculations otherwise your worksheet will try to recalculate slowing down the transfer.

4. Checkout the Free Rich Client Toolset available at thecommon.net:
Will allow you to use sqlite DB from Excel without the MS stack overhead.
Very fast.

The toolset is a feature-rich vbRuntime-enhancement, which aims to lower the dependencies into the COM-based MS-ToolStack (it contains replacements for e.g. DAO/ADO/JET, MS-XML, DCOM, the Scripting-Dictionary, etc. ...

It also offers regfree Loading/Instancing-capabilities for ActiveX-Dlls and it can also make ActiveX-Exes obsolete, due to support for a new, easy to use Threading-approach, which works with Named-Pipes under the hood and works therefore also cross-process.

Links to a few demos:
http://www.datenhaus.de/Downloads/MetaStock-Demo.zip
http://www.datenhaus.de/Downloads/VBA-NWind-StarSchema.zip

 

I'm confused here too. Could you spell it out please. Is this what happens:
1. SetLinkOnData triggers a macro
2. This macro uses DDERequest to request data for the link that triggered the macro
3. This DDERequest forces all links, including those that did not trigger the macro and hence were not requested for using DDERequest, to be updated.

Is this right? What’s the delay between the DDERequest and all cells getting updates value? Is it quicker to loop through all links using DDERequest to get the data? If all links in cells have macros assigned via SetLinkOnData method, does this then not cause unnecessary firing of these macros if we know that DDERequest just from one of the links forces all to be updated? Also, does the placement of DDE links into cells not open a DDE channel? If it does (as it surely must), then why use DDEInitiate method to open another channel instead of using the already open one? And how does one find out the channel number for the one opened by placing DDE links into cells?

Any other info and details would be greatly appreciated.

Many thanks

 

This call merely requests the symbol for ESH1... just a known static piece of information. When this call is made any other dde link with new data also simultaneously loads into the worksheet.

For performance purposes I load the real time data into a range (T1) where the only calculation is to identify the symbols that contain updates to bid/ask or last. (countif comparison between T1 and T2)

If there are no updates: Exit Sub
If there are updates:
- turn off calculations.
- update the symbols in data range (T2) which is used in calculations to further processes the price data.
- write the tick updates out to an in memory sqlite db.
- update counters, stats and flags
- turn on calculations (triggers all further calculation processes against the updated t2 range and not directly against the cells with the dde links)

For my purposes this method provides granular control and optimizes real time data feed processing.

 

PocketChange,

Thanks for coming back on this. However, I'm still not quite clear on a few issues and would greatly appreciate if you could share your knowledge and experince on them. Many thanks.

First, if a single call with DDERequest on one symbol results in any other dde link with new data to also simultaneously load into the worksheet, does this then not trigger SetLinkOnData macros for all these other links? And since we now that all these other links get updated with a single DDERequest call, is this then not a waste of time and resources? Is it not possible and would it not be better to perhaps have only one link trigger SetLinkOnData method? Or is there some other better way? How do you handle this situation?

Also, there is still this question about the use of DDEInitiate to open a channel before making a DDERequest call – does not having DDE links in a worksheet open a DDE channel in a first place, as it surely must? Can we then not use this channel in our DDERequest call instead of opening a new one, thus saving time? And how would we go about finding what this channel number is? Does it default to something?

And there is more. Would it not be better, i.e. faster, to loop through DDERequest in VBA and store the received values in a VBA array and do comparison with another array containing old values to determine if there were updates instead of waiting for Excel to load the values into cells (you say this happens “simultaneously” – do you have some time figures for this?) and then also perform count-if checks on them in a worksheet? I thought that it’s better to minimise data transfer between VBA and Excel worksheet and to perform all logic operations in VBA, is it not?

I know there are many questions here, but I’m trying to get a crystal clear picture in my head of what actually is going on and how to best deal with this. I greatly appreciate any and all info that you could share on this.

Many thanks.

PS Would you happen to have some Excel files that test all of this? Thanks

 

To be honest this code is a few years old, solved my issues and has worked reliably for my purposes. I recall trying many iterations and was also under the impression minimizing data transfers between vba and the worksheet would increase performance.

It takes approx 10 microseconds to copy each cell of data. 100 updated cells per ms. Updating an array of 50 symbols x 100 columns takes approx 50ms each loop. I found this to be too slow but if your process is something like 100 symbols x bid/ask/last (or minimum fields) 300 pieces of data = 3ms it may work for your purposes.

setlinkondata in my case all call the same routine and the routine is optimized to identify symbols with price updates or it exits. the excessive calls to setlinkondata doesn't seem to impact performance and keeps a fast heartbeat on the data feed.

you should try different dde initiate / request scenarios. ddeinitiate call (I believe uses the same channel) is what forces all updates into the range in one shot. I don't recall the reasoning... might be sloppy code that works.

 

Thanks for the feedback. Not sure if I’ve got everything clear in my head on this yet, but here is my planned implementation and I would be grateful if you were to give any comments on this (good or bad):

1) Populate worksheet cells with DDE links;
2) Assign the same macro “CheckForUpdates” to all these links using SetLinkOnData method;
3) In CheckForUpdates macro use DDERequest method to pull in data from a server for the DDE link that triggered this macro;
4) Compare the value received via DDERequest with an old value for this DDE link stored in a VBA array: if the values are the same – Exit Sub, if not – use a loop with DDERequest to pull in data for all other links into a VBA array and then perform calculations on it.

I think this involves unnecessary calls to CheckForUpdates macro that uses DDERequest, but I don’t see a better way at the mo. Any suggestions?

Many thanks