Dynamic Excel DDE link to TOS (ThinkOrSwim)

Discussion in 'Trading Software' started by mxpelite, Jul 5, 2011.

  1. sgfee123

    sgfee123

    This is how I do it (a Document is also attached):

    Sub MakeDDE()

    Dim ExpDt As Date

    For CurRow = 2 To 11

    DDERoot = "=TOS|MARK!'" 'Single then Double Quote at the end

    StkSym = "." & Cells(CurRow, 1) '''Period is needed for Options
    ExpDt = Cells(CurRow, 2)
    StrkPr = Cells(CurRow, 3)
    CallPut = Cells(CurRow, 4)

    YYMMDD = Right(ExpDt, 2) & _
    WorksheetFunction.Text(Month(ExpDt), "00") & _
    WorksheetFunction.Text(Day(ExpDt), "00")

    DDEFormula = DDERoot & _
    StkSym & _
    YYMMDD & _
    CallPut & StrkPr & "'" '''Dbl, Single, Dbl Quotes

    Cells(CurRow, 5) = DDEFormula

    Selection.Replace What:="=", Replacement:="="

    Next

    End Sub
     
    #11     Oct 1, 2012
  2. sgfee123

    sgfee123

    I know this doesn't sound logical, but I've found doing a replace on the equal sign can activate DDE formulas after they are copied in Excel.

    The VBA statement is:

    Selection.Replace What:="=", Replacement:="="
     
    #12     Oct 2, 2012
  3. sgfee123

    sgfee123

    Turning Dynamic Data Exchange DDE on and off

    Click here to see what Microsoft says about turning DDE on/off

    According to Microsoft: “When a new workbook is created, the default value for the UpdateRemoteReferences
    property is True and dynamic data exchange (DDE) links and OLE links update automatically. If the value is False,
    DDE links and OLE links do not update automatically or during recalculation.”

    I have not been able to get UpdateRemoteReferences to do what it is advertised to do. You may have better luck.

    Code:
    [b]Sub DDE_OnOffSwitch()[/b]
    
        ActiveWorkbook.UpdateRemoteReferences = Not ActiveWorkbook.UpdateRemoteReferences
    
    [b]End Sub[/b]
    
    -------------------------------------------------------------------------------------------------------------------------------------------------------

    If you don’t mind turning ALL spreadsheet calculations off, this will turn ALL spreadsheet calculations on/off – including DDE.

    Code:
    [b]Sub Calc_OnOffSwitch()[/b]
    
        If Application.Calculation = xlManual Then
        
            Application.Calculation = xlAutomatic
            
        Else
        
            Application.Calculation = xlManual
            
        End If
    
    [b]End Sub[/b]
    
    ----------------------------------------------------------------------------------------------------------------------------------------------------------

    A workaround way to turn just DDE off would be to run the On/Off Subs below. Comment out the DDE formulas
    then change the DDE formulas font color to hide the formulas:

    Code:
      [b][size=3]ROW                      COLUMN A[/size][/b]
    
       1               =TOS|MARK!'.SPY121117C142'
       2               =TOS|MARK!'.SPY121117C143'
       3               =TOS|MARK!'.SPY121117C144'
       4               =TOS|MARK!'.SPY121117C145'
       5               =TOS|MARK!'.SPY121117C146'
       6               =TOS|MARK!'.SPY121117C147'
       7               =TOS|MARK!'.SPY121117C148'
       8               =TOS|MARK!'.SPY121117C149'
       9               =TOS|MARK!'.SPY121117C160'
        10             =TOS|MARK!'.SPY121117C161'
    
    
    [b]Sub StopDDE()[/b]
    
        Range("A1:A10").Select
        
        With Selection.Font
            .ThemeColor = xlThemeColorDark1                '''Turn Font White
            .TintAndShade = 0
        End With
        
        Selection.Replace What:="=", Replacement:="'="     '''Change the Formula to a Comment
        
        Range("A1").Select
    
    [b]End Sub[/b]
    
    
    [b]Sub StartDDE()[/b]
         
        Dim i As Integer
         
        Range("A1:A10").Select
        
        Selection.Copy
        
        Range("A1").Select
        
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        
        Application.CutCopyMode = False
         
        For i = 1 To 10
             
            Cells(i, 1).Select
             
            ActiveCell.Formula = Cells(i, 1).Value  '''Activate the DDE Formula
             
        Next
        
        Range("A1:A10").Select
        
        With Selection.Font
            .ColorIndex = xlAutomatic       '''Change Font Color to Black
            .TintAndShade = 0
        End With
    
        Range("A1").Select
        
    [b]End Sub[/b]
    
     
    #13     Oct 3, 2012
  4. sgfee123

    sgfee123

    Dynamic Data Exchange (DDE) with Thinkorswim and Excel

    See the attached spreadsheet for a Quick and easy way to use DDE with Thinkorswim and Excel.
     
    #14     Oct 4, 2012
  5. sgfee123

    sgfee123

    Excel-DDE-V2.xls handles both Stock and Option Quotes.
     
    #15     Oct 6, 2012
  6. sgfee123

    sgfee123

    Excel Macros to turn formulas on/off start/stop label/formula enable/disable
    Code:
    [b]Sub FormulaON()[/b]
    
        Application.ScreenUpdating = False          '''Makes the code run a lot faster
        Application.Calculation = xlManual
        
            Range("A1:A5").Select                   '''Cells containing the formulas
        
            With Selection
                .Font.ColorIndex = xlAutomatic      '''Optional - Show the formula
                .Formula = .Formula
            End With
        
        Application.ScreenUpdating = True
        Application.Calculation = xlAutomatic
        
        Range("A1").Select
    
    [b]End Sub[/b]
    
    
    [b]Sub FormulaOFF()[/b]
    
        Application.ScreenUpdating = False
        Application.Calculation = xlManual
        
            Range("A1:A5").Select
    
            With Selection
                .Font.ColorIndex = 2                       '''Optional - Hide the formula (white font)
                .Replace What:="=", Replacement:="'="      '’’Replacement:=double quote, single quote, equal sign, double quote
            End With
            
        Application.ScreenUpdating = True
        Application.Calculation = xlAutomatic
        
        Range("A1").Select
    
    [b]End Sub[/b]
    
     
    #16     Oct 13, 2012
  7. sgfee123

    sgfee123

    Remove apostrophe from beginning of formula

    The attached spreadsheet might help if your DDE shows formulas instead of results.
     
    #17     Oct 14, 2012
  8. millerd1

    millerd1

    I would like to thank all prior postersfor bringing this content forward, this is some quality content.

    It should be possible to add text to column steps to any of these macros.

    Millerd1
     
    #18     Nov 5, 2012
  9. Is there a way to add a column for Deltas? What would that code be?
     
    #19     Nov 13, 2012
  10. sgfee123

    sgfee123

    Delta has been added.
     
    #20     Nov 14, 2012