Forums (http://www.elitetrader.com/vb/index.php)
- Trading Software (http://www.elitetrader.com/vb/forumdisplay.php?forumid=3)
-- Dynamic Excel DDE link to TOS (ThinkOrSwim) (http://www.elitetrader.com/vb/showthread.php?threadid=223105)

Posted by mxpelite on 07-05-11 04:52 PM:

Dynamic Excel DDE link to TOS (ThinkOrSwim)

I've seen many posts looking for the ability to dynamically copy and paste Excel DDE formulas containing cell references.

This is not possible in Excel because the DDE formulas can only be static.

But, here you have a quite convenient workaround which wll allow you to save a lot of time an effort.

All the effort has been applied to link to the ThinkOrSwim thinkDesktop client, but it could be taken as a model to do a similar thing with other DDE servers.

To use this piece of software you need to copy the following code to the VBA code window (you need to open the Visual Basic editor) of your Excel 2010 workbook (it could work in Excel 2003 and 2007 but I have not tested it there)

code:

Public Function TOSDDE(symbol As String, tosfield As String)

ddeformula = "=TOS|" & UCase(tosfield) & "!'" & UCase(symbol) & "'"
'TOSDDE = Application.Evaluate(ddeformula)    'if you don't need to refresh the link this simple solution is good enough
'TOSDDE.Formula = ddeformula                  'if this was possible that would be the perfect solution (try find a way to do that!)
TOSDDE = ddeformula                           'this allows the use of Activate/Deactivate subs to activate/deactivate the DDE links

End Function
Sub ActivateTOSDDElinks()
Dim fullRange As range
Dim formulaRange As range
Dim cel As range
Set fullRange = ActiveSheet.Cells
Set formulaRange = fullRange.SpecialCells(xlCellTypeFormulas)
For Each cel In formulaRange
If InStr(1, cel.Formula, "=TOSDDE(", vbTextCompare) = 1 Then
cel.ClearComments
cel.AddComment (cel.Formula)
cel.Formula = cel.Value
End If
Next
End Sub
Sub DeActivateTOSDDElinks()
Dim fullRange As range
Dim formulaRange As range
Dim cel As range
Dim comm As String
Set fullRange = ActiveSheet.Cells
Set formulaRange = fullRange.SpecialCells(xlCellTypeFormulas)
For Each cel In formulaRange
comm = ""
On Error Resume Next
comm = cel.Comment.Text
If InStr(1, comm, "=TOSDDE(", vbTextCompare) = 1 Then
cel.Value = cel.Formula
cel.Formula = comm
cel.ClearComments
End If
Next
End Sub


once copied you can inmediately use it in your Excel worksheet.

As you can see, there are one function and two subs.

The first thing to do is to code some DDE formulas using the provided TOSDDE() function. The syntax is:

=TOSDDE(symbol, DDE_field)

examples:

=TOSDDE("AAPL", "BID")

Having
cell A2: "AAPL"
cell B1: "BID"

=TOSDDE($A2, B$1)

You will see that the cell is populated with the static DDE expression:

=TOS|BID!AAPL

You can use complex symbols like ".AAPL110716P330" (AAPL July 16, 2011 330 put), this will generate things like =TOS|DELTA!'.AAPL110716P330'

Now you can drag, copy and paste the TOSDDE formulas and the cell references will adjust.

When you decide to, you can execute the sub "ActivateTOSDDElinks" and the DDE data will start to flow through the existing formulas of the active sheet.

You will notice that the cells containing DDE formulas have a small red corner. This is an indication that there is a comment in that cell. The comment contains the original TOSDDE function that was introduced to allow the functionality that I explain in the next paragraph.

If you need to, you may also use the opposite function, "DeActivateTOSDDElinks" and the DDE access will stop and the original formulas will revert back to its original form. This can help if problems arise with the DDE feed or if you want to save CPU resources stopping not needed links.

Both this subs act at the sheet level, if you want to apply them to different sheets you need to go sheet by sheet.

And that's it, I would appreciate any comments and if any of you can find a 1-step way to activate the DDE feed when introducing the TOSDDE function (I couldn't), please, let me know. This would avoid the need to use the subs.

I hope you will enjoy this...

Posted by kigol on 08-20-11 02:34 AM:

Just wanted to say thanks. Nice clean workaround.

Posted by mxpelite on 08-25-11 08:49 PM:

I appreciate your comments. You are welcome.

Posted by tangpd on 08-13-12 09:56 AM:

Hi mxpelite,

The function and macro works great!

Thank you so much, now I can ditch my OpenOffice DDE spreadsheet



Cheers,
tangpd

Posted by Kevin Schmit on 08-13-12 08:05 PM:

Re: Dynamic Excel DDE link to TOS (ThinkOrSwim)

Quote from mxpelite:

here you have a quite convenient workaround which wll allow you to save a lot of time an effort.


Thanks. Useful.

Good way to start your ET posting career!

Posted by Squilly_D on 09-24-12 08:52 AM:

Unfamiliar with VBA code/coding

I am unfamiliar with VBA code/coding and was unsuccessful getting this code to run. I pasted the code to Sheet1(Code) area (In the “Microsoft Visual Basic for Applications” window), saved the file, then went to Sheet1 and input:

=TOSDDE(“AAPL”, “BID”)

when I press enter, all I get is “#NAME?”. After that, I tried executing the macro:

Sheet1.ActivateTOSDDElinks

but nothing happens/changes. Is there something else that needs to be done in order for this code to work that I haven’t done? I would greatly appreciate any help on this!
I am using Office 2012.

Posted by Squilly_D on 09-24-12 06:52 PM:

Unfamiliar with VBA code/coding

I am unfamiliar with VBA code/coding and was unsuccessful getting this code to run. I pasted the code to Sheet1(Code) area (In the “Microsoft Visual Basic for Applications” window), saved the file, then went to Sheet1 and input:

=TOSDDE(“AAPL”, “BID”)

When I press enter, all I get is “#NAME?”. After that, I tried executing the macro:

Sheet1.ActivateTOSDDElinks

But nothing happens/changes. Is there something else that needs to be done in order for this code to work that I haven’t done? I would greatly appreciate any help on this!

I am using Office 2012.

Posted by kigol on 09-24-12 08:45 PM:

Re: Unfamiliar with VBA code/coding

Quote from Squilly_D:

I am unfamiliar with VBA code/coding and was unsuccessful getting this code to run. I pasted the code to Sheet1(Code) area (In the “Microsoft Visual Basic for Applications” window), saved the file, then went to Sheet1 and input:

=TOSDDE(“AAPL”, “BID”)

when I press enter, all I get is “#NAME?”. After that, I tried executing the macro:

Sheet1.ActivateTOSDDElinks

but nothing happens/changes. Is there something else that needs to be done in order for this code to work that I haven’t done? I would greatly appreciate any help on this!
I am using Office 2012.



You need to create Module and put the code in there. These are written as subroutines not a spreadsheet formulas.

Posted by Squilly_D on 09-25-12 09:45 AM:

I appreciate the help! This was the fix I needed.

Posted by kigol on 09-25-12 03:54 PM:

Quote from Squilly_D:

I appreciate the help! This was the fix I needed.


No problem glad you got it working!

Posted by sgfee123 on 10-02-12 03:27 AM:

Using DDE with Excel to get Option Prices

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

__________________
Steve

Posted by sgfee123 on 10-02-12 04:07 PM:

Activating dynamic Data Exchange (DDE) formulas

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:="="

__________________
Steve

Posted by sgfee123 on 10-03-12 05:08 PM:

Turning Dynamic Data Exchange DDE on and off

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:

Sub DDE_OnOffSwitch()

    ActiveWorkbook.UpdateRemoteReferences = Not ActiveWorkbook.UpdateRemoteReferences

End Sub

-------------------------------------------------------------------------------------------------------------------------------------------------------

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

code:

Sub Calc_OnOffSwitch()

    If Application.Calculation = xlManual Then
    
        Application.Calculation = xlAutomatic
        
    Else
    
        Application.Calculation = xlManual
        
    End If

End Sub

----------------------------------------------------------------------------------------------------------------------------------------------------------

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:

  ROW                      COLUMN A

   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'


Sub StopDDE()

    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

End Sub


Sub StartDDE()
     
    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
    
End Sub

__________________
Steve

Posted by sgfee123 on 10-04-12 07:08 PM:

Dynamic Data Exchange (DDE) with Thinkorswim and Excel

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.

__________________
Steve

Posted by sgfee123 on 10-06-12 08:57 PM:

Dynamic Data Exchange (DDE) with Thinkorswim and Excel

Excel-DDE-V2.xls handles both Stock and Option Quotes.

__________________
Steve

Posted by sgfee123 on 10-14-12 12:43 AM:

Turn any Excel Formula ON/OFF - Including DDE formulas

Excel Macros to turn formulas on/off start/stop label/formula enable/disable

code:

Sub FormulaON()

    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

End Sub


Sub FormulaOFF()

    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

End Sub

Posted by sgfee123 on 10-14-12 05:35 AM:

Remove Apostrophe from formula

Remove apostrophe from beginning of formula

The attached spreadsheet might help if your DDE shows formulas instead of results.

__________________
Steve

Posted by millerd1 on 11-05-12 06:12 AM:

removing

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

Posted by cowtowner on 11-14-12 03:13 AM:

Is there a way to add a column for Deltas? What would that code be?

Posted by sgfee123 on 11-14-12 05:34 AM:

Delta Added

Delta has been added.

__________________
Steve

Posted by sgfee123 on 11-14-12 03:02 PM:

Corrected "Position Delta" for Stock positions.

__________________
Steve

Posted by cowtowner on 11-22-12 12:28 AM:

Thanks so much! Sorry it took so long, but I didn't get an email to say you had responded.

How about a curveball? A routine that sends an email/text when a specified cell gets to a certain number.

I monitor certain Butterflys and Condors based on the deltas of the entire position.

So, day the short calls/put and long calls/puts and posibly an outmonth long call are all added up and they reach greater than 16 or less than -16 it triggers an email to sms alert.

You can do Delta alerts in TOS (when they are working), but complex position delta monitoring is out.

Posted by sgfee123 on 11-26-12 01:40 PM:

Cowtowner,

You have an interesting idea. It can be done.

Unfortunately, it would take a little more than my available time allows.

Hopefully someone will see your post and be able to help.

__________________
Steve

Posted by cowtowner on 11-28-12 02:54 AM:

Going to try to tackle it myself. I used to program VBA back in the day, but have forgotten nearly everything.

Posted by nancy7505 on 03-13-13 09:16 PM:

All I see is =TOS|BID!'APPL' after I enter the formula =TOSDDE("AAPL", "BID").

I tried the following, but no joy.

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

Posted by cowtowner on 03-13-13 10:00 PM:

It's a windows issue:

From another website, you have to run excel in admin mode:

a) In Vista or Windows 7, type "Excel.exe" in the "search programs and files" box in the start menu. An Excel icon will appear in the list, right button on it and select "Run as administrator".

b) Locate where Excel.exe is on your C drive and create an icon for it on the desktop. You may have to search for it on your C drive, the locations are different depending on where Office was installed, or by following step (a) select "Send to", and "Desktop - create shortcut". then you can right button select the icon from your desktop and choose "Run as administrator".

OR

2. Uninstall the Windows update that caused this problem. This isnt recommended, but you can go into Windows Update, click on "View Update History" then "Installed Updates", find the following updates in the list and uninstall them. The two updates I have found so far that each can create this problem are

KB2778930 and
KB2778344

Once you unselect them from the next windows update I believe it wont re-install these.

Microsoft will probably release future updates that cause this problem again, so in the long run we are going to lose this method of fixing it.

Posted by nancy7505 on 03-14-13 03:54 PM:

It was running in admin mode.
Maybe I missed something. I noticed if I manually ran Sub ActivateTOSDDElinks() it worked.

All times are GMT. The time now is 05:56 AM.

Copyright © 2012 Elite Trader.