Excel stream (DDE) data capture

I use DDE streaming with both Interactivebrokers and through ORC, and I wanted to be able to store option inside bid/offer volumes every few seconds in Excel. This is so I can create my own time series for more analysis later on or just to have something to reference in real time.



So here's a cool, simple Excel macro to do just this:

Let's name this macro DDEcapture,

"
Dim I As Integer
Sub DDEcapture()
    If I = 0 Then I = 1
    Sheets("Sheet2").Cells(1, I) = Sheets("Sheet1").Range("A1")
    I = I + 1
    Application.OnTime Now + TimeValue("00:05:00"), "DDEcapture"
End Sub
"

In this example, the streamed data is in Sheet1, cell A1. I'm storing them in Sheet2, starting in cell (1,1). So when I run the macro, every 5 minutes, it records the streamed value into the next row (i.e. I + 1) of column 1. Pretty neat huh!