How to Periodically Store/Save Excel DDE Values

Discussion in 'Strategy Development' started by CPTrader, Aug 9, 2007.

  1. I have an Excel DDE file that updates periodically with quotes, portfolio values, etc.

    I want to periodically collect a snapshot of the DDE values and store the data for review.

    So for example let's say the day's high is dynamically updated into cell B1, I would like to have look at B1 at specified times, retrieve the value of B1 at that time and store it in say cell B2.

    I can’t seem to find an Excel function or formula tat would achieve this.

    Any ideas or tips?

    Many Thanks!
  2. GoodGame


    I have exactly the same problem... haven't figured out the solution yet... :(
  3. Only one way I was able to solve this problem...

    A macro that will copy the values, and paste into another sheet every x minutes. I made a separate workbook that only does that b/c you have to leave the cursor on one cell and don't want to interfere with it. Also if you have it copying every one minute, you will find that you may copy some text in a word document, wait a little while, and CTRL+V and you will get the data from the workbook (if your macro triggers in the time between you pasted your text). Gets annoying, but get used to it. PM me if you want some help, I can probably email you the workbook I have and you can manipulate it to your liking.
  4. Example Code:
    2 sheets - one named "Charts" the other named "Raw"

    -should include DDE input
    -row A is title headers
    -row B is the DDE data that will be recorded
    -make sure to save the file with the cursor on cell B1 (I suggest putting a time function in this cell like =now()

    -paste the headers from "Charts" into row A
    -make sure to save with cursor in cell B1

    Some pointers:
    Open a totally new Excel not run within the same Excel as other workbooks and leave it minimized.
    This macro will only start when you hit Ctrl+a, and will ONLY END when you close that excel program completely (cannot just close the workbook and leave excel will re-open this workbook automatically if you do that).

    MACRO CODE [Time value can be changed will re-record a minute from the time you start the macro with Ctrl+a]

    Sub record()
    Application.OnTime Now + TimeValue("00:01:00"), "record"

    ' record Macro
    ' Macro recorded 8/18/2006 by TradeNYSE
    ' Keyboard Shortcut: Ctrl+a
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    ActiveCell.Offset(1, 0).Range("A1").Select
    End Sub
  5. well read what they said
  6. dont


    Have a look at this spreadsheet it basically saves the dde data in a time interval you specify. Have a look at the code in VB.

    Right click the start timer and end timer buttons and say edit code/macro.
  7. unable to download attachment
  8. dont


    Heres the relevant code

    Public Declare Function SetTimer Lib "user32" (ByVal HWnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Public Declare Function KillTimer Lib "user32" (ByVal HWnd As Long, ByVal nIDEvent As Long) As Long
    Public TimerID As Long
    Public TimerSeconds As Single

    Sub StartTimer()

    TimerSeconds = 600 'how often to "pop" the timer.
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
    End Sub

    Sub EndTimer()
    ' On Error Resume Next
    KillTimer 0&, TimerID

    End Sub

    Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)
    Static time10 As Double
    Static j10 As Long
    'On Error GoTo ErrorHandler
    j10 = j10 + 1
    time10 = time10 + 1

    realtime = 10 * time10

    Range(Cells(10, j10 + 22), Cells(10, j10 + 22)).Value = realtime

    For i = 0 To 27
    Range(Cells(i + 11, j10 + 22), Cells(i + 11, j10 + 22)).Value = Range(Cells(i + 16, 14), Cells(i + 16, 14)).Value
    Next i

    End Sub

    Basically it uses a timer to copy and paste the data.
    Each time it shifts the data over a few columns so as not to overwrite what it saved already.