Help, simple excel...

Discussion in 'Automated Trading' started by Don Bright, Dec 29, 2005.

  1. Sorry to ask, but how do I pick up a cell value' at a specified time of day. I want to know the Emini value at, say 12:30 and I already have the Emini DDE in a cell..


  2. depends on a lot of things. If the spreadsheet updates fairly frequently you can put some VBA code in the .calculate subroutine that checks the value against the time (either in a cell with =now() or against the windows API and system clock) and then copy it to another cell when you like. Alternatively, if you need more accurate results or your sheet doesn't update on it's own, you'll have to use the windows API to run a timer function that checks the time every n seconds (milliseconds) and then copies it when it's done. If you give me a bit more info, I might be able to find some old code you can "plug and play" with.


    - The New Guy

    EDIT: I'm not sure if you edited that last post, of if I just can't read. If it's DDE and it's the SPU's it should probably be going at a pretty fast rate. Would it work if it basically went like this:

    copy the spu price at the first update after 12:30?


    - The New Guy
  3. I have a dde link into excel that has all the market data, auto update tick by tick. I have the emini's in a cell continually updating. I want another cell to "grab" the emini cell's value at a specific time of day. I can either use the computers clock or a "now" function if it updates.

    Yes, you have the idea ....what could I put into the formula bar to grab that number at various times of day.??

    You can IM me at donbright@yahoo or donbright@aol if that's easier..


    Don :confused:
  4. gonna send an email shortly....
  5. Hey Don. I have a couple of formulas to do that somewhere around here. If you don't get what you're lookin' for, let me know. I had a couple of programming guys code several formulas along those lines a year ago. I have it in another computer at the moment.

  6. Sure, glad for all the help.

  7. Arnie


  8. lescor


    I could use that vba code too if you guys are passing it out. I want to do the same thing Don is asking. Thanks.
  9. Raver


    Hi Don,

    Put the next code in 'ThisWorkbook', this code activates the macro to copy paste at 12:30 when you open the sheet.

    Private Sub Workbook_Open()
    Call AutoUpdate
    MsgBox "Macro started"
    End Sub

    this is the code you should put in Module1.

    Sub AutoUpdate()
    Application.OnTime TimeValue("12:30:00"), "CopyData"
    End Sub

    Sub CopyData()
    Sheets(2).Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    End Sub

    If your DDE link is in cell A1 on sheet1 this macro will copy it at 12:30 and paste in sheet2 in A1. Everyday it will paste the price below the previous day, so day 1 sheet2 cell A1, day 2 sheet2 cell A2 etc.

    Kind regards,

    #10     Dec 30, 2005