I am digging into some old butterfly scanning and pricing tools in Excel that folks worked on a few years ago and am trying to update them to use RTD instead of DDE. I have a VB script that I found to convert all DDE formulas to RTD by doing a string replacement, but it is not working quite right yet. I've racked my brain on this for long enough and have not gotten it to work. It is time to ask for help. I need help from someone who knows VBA better than I do right now. I'm working on improving my VBA skills but haven't had the time to take a full course on it yet. If anyone has already solved this automated formulas conversion problem, I would appreciate you sharing it with me and others. I will attach the BF pricing worksheet with embedded converter code.
There are a lot of cool tools available from the DDE days of TOS, so the issue of converting TOS RTD formula text automatically is still important. I am loath to convert these worksheets by hand since some have dozens or hundreds of DDE formulas. That's what programming is for. I appreciate help from someone currently more VBA savvy than me right now.
The macro code for ConvertWorksheet is in the VBA code module Convert_DDE_2_RTD.
Here's the original post for the conversion code. I get errors in the ConvertFormulas sub. These instructions are not complete.
Jack Webster Says:
April 13, 2014 at 3:04 pm
Here is some interesting information from TOS.
Thanks for the e-mail and kind words. In regards to your inquiry about DDE, we now support the Microsoft Excel function RTD(Real Time Data) which works in the same way.
I could not attach the PDF file. I copied and pasted it. Contact TOS support for a copy.
RTD on thinkorswim
thinkorswim has the ability to use the Mircosoft Excel function RTD (Real Time Data.) This allows you to connect your Excel spreadsheet to thinkorswim for real time quote updates. To use this feature on thinkorswim bring up a watchlist with your desired symbols and columns. Then simply click on the printer icon in the top right of the list and choose Export to Excel.
Pasting Visual Basic script to update to RTD
If you have previously saved spreadsheets that use DDE you can update your spreadsheet to RTD. The following steps will show you how to run a Visual Basic script to perform this update. You may want to save your old DDE spreadsheet as a new document in case you want to reference the original document.
Start by copying this script as we will need to paste it in Step 3
Dim ws As Worksheet
Dim rng As Range
For Each Sheet In Worksheets
Function ConvertWorksheet(ByVal sht As Worksheet)
On Error Resume Next
For Each rng In sht.Cells.SpecialCells(xlCellTypeFormulas)
Dim text As String
Dim price As String
Dim symbol As String
Dim tosPrefix As String
Dim separatorPosition As Integer
Dim exclaimPos As Integer
Dim isTOS As String
text = rng.Formula
isTOS = InStr(text, “=TOS”)
separatorPosition = InStr(text, “|”)
exclaimPos = InStr(text, “!”)
If (separatorPosition > 0 And exclaimPos > 0 And isTOS > 0) Then
tosPrefix = Left(text, InStr(text, “|”))
price = Mid(text, InStr(text, “|”) + 1, exclaimPos – separatorPosition – 1)
symbol = Right(text, Len(text) – exclaimPos)
Dim result As String
result = “=RTD(“”TOS.RTD””,,””” + Ucase(price) + “””,””” + Ucase(symbol) + “””)”
rng.Formula = result
You will need the Developer Tab to perform this task. To show the Developer tab click on File and choose options. From the Excel Options1 window click on Customize Ribbon2 and check Developer3 in the Main Tabs section.
1. Now you need to paste the Visual Basic script into a VBA Project. To start this click on the newly added Developer Tab1 and then click the Visual Basic button2.
2. You need to paste the script into a specific location in Visual basic. Click on the ThisWorkbook Project1 in the right hand panel. This will bring up new VB (code) box2 where you want to paste your script. Select all of the default code in this box3 and paste your copied script.
Now run this script to update your DDE functions for the entire workbook. Click on the Run Menu1 at the top and chose Run. This will bring you to the Macros2 window where you will click on ThisWorkbook.ConvertFormulas and choose Run3.
You can close down the Visual Basic window and you will see your new updated spreadsheet. From here you can save the spreadsheet and use it as you have used DDE in the past.
Still need help with this? I've converted a couple spreadsheets from DDE to RTD so have some experience with that.