[Solved] Need expert on DDE to fix problems with ThinkorSwim

[CapCrockett]

Is there anyone here who is an expert on DDE and what causes it to break?

I have a spreadsheet with many DDE links to TD AMeritrade's Think Or Swim platform. The spreadsheet retrieves and manipulates option data. It was working when I closed it. Then the next time I open it, it won't retrieve prices for May 19. However, I can open a new spreadsheet and enter:

=(DDE("TOS";"MARK";".SPXW210519C4130"))

It will retrieve the correct price for the SPW Weekly Call expiring on May 19. But If I paste that into the "broken" spreadsheet, it will not retrieve it. It returns #N/A. However, if I change it to (change 19 to 21):

=(DDE("TOS";"MARK";".SPXW210521C4130"))

it will correctly retrieve the correct price for the SPW Weekly Call expiring on May 21.

What can cause that?

UPDATE: Each time I make changes to a spreadsheet I save it with a new name. I opened an older version of this spreadsheet, and it retrieves the May 19 options data as it should. However, it's odd the new "broken" version previously retrieved the May 19 data, but then it didn't when next opened.

[RoryOF]

What happens if you insert 18 in place of 19? Does it retrieve that data? If not, then perhaps the retrieval time is later than the time of the data to be downloaded, and the DDE mechanism recognises that the data has expired.

[CapCrockett]

What happens if you insert 18 in place of 19? Does it retrieve that data? If not, then perhaps the retrieval time is later than the time of the data to be downloaded, and the DDE mechanism recognises that the data has expired.

May 18 options are expired, so there's no data for them.

May 19 options are not expired, and are trading. And those prices can be retrieved on a new spreadsheet.

[RoryOF]

If you insert 19th May, as a new DDE, does that work for the new instance but not for the old?

[CapCrockett]

If you insert 19th May, as a new DDE, does that work for the new instance but not for the old?

I changed it to 18 just for grins - the #N/A changed to:

#VALUE!

[CapCrockett]

If you insert 19th May, as a new DDE, does that work for the new instance but not for the old?

As a matter of fact, it does - for the new one but not the existing! So, what does that mean?

[RoryOF]

You should look in the innards of the altered document to see what difference in internal code is between the old 19th instance and the new 19th instance.

[CapCrockett]

You should look in the innards of the altered document to see what difference in internal code is between the old 19th instance and the new 19th instance.

Innards? Can you explain what you mean by that and how that's done?

[MrProgrammer]

I have a spreadsheet with many DDE links to TD AMeritrade's Think Or Swim platform.

I do not use the Think or Swim service, but I will offer some advice based on those links.

• As I understand it, to use TD Ameritrade's DDE or RTD interfaces one installs software they provide. If you have problems with DDE, those problems might be in the TD Ameritrade software which you've installed, or they might be in the TD Ameritrade servers which their software accesses to obtain data for you. No one except TD Ameritrade can help you in those cases.
• I believe that TD Ameritrade designed their interface, the one you install, to work with Microsnot Excel. OpenOffice Calc is not Excel. My advice is to comply with TD Ameritrade's requirement and use Excel if you are having difficulties with their Think or Swim service.

As their customer, you could request TD Ameritrade to provide support for a Calc interface. Until that is available, I doubt if anyone here at a user-to-user forum can help you get Calc to work with a service which wasn't designed for Calc.

[CapCrockett]

MrProgrammer, what is the basis of your understanding?

[Villeroy]

https://en.wikipedia.org/wiki/Dynamic_D ... chnologies

Relationship to modern automation technologies
The common use of DDE is for custom-developed applications to control off-the-shelf software. For example, a custom in-house application might use DDE to open a Microsoft Excel spreadsheet and fill it with data, by opening a DDE conversation with Excel and sending it DDE commands. This feature has mainly been superseded by the Component Object Model suite of technologies. Microsoft has not used DDE internally since the release of Windows 95.[3] For example, Excel now exposes an extensive OLE Automation object model, which is the recommended method for communicating with Excel. The technique is, however, still in use, particularly for distribution of financial data.[4] Because DDE does not require more operating system components than a conventional Windows GUI program, it is also a [little-used] alternative for programs seeking to minimize their dependencies.

Open/LibreOffice implemented some kind of DDE surrogate which also works on non-Windows platforms. It works perfectly well for data exchange between Writer and Calc on Windows, Mac and Linux. If it works with other applications on MS Windows this is pure luck. Possibly the developers of your application developed their DDE server with no client in mind other than MS Excel.

[Villeroy]

The entry on DDE on the German Wikipedia says that DDE exchanges data in clipboard format. From this forum I know that you can not copy/paste between Excel and Calc. You have to open both spreadsheet documents in any of the two spreadsheet applications if you want to copy data between a Calc document and an Excel document. If your application sends excelish clipboard data, it is very unlikely to be fully compatible with Calc.

[CapCrockett]

The entry on DDE on the German Wikipedia says that DDE exchanges data in clipboard format. From this forum I know that you can not copy/paste between Excel and Calc. You have to open both spreadsheet documents in any of the two spreadsheet applications if you want to copy data between a Calc document and an Excel document. If your application sends excelish clipboard data, it is very unlikely to be fully compatible with Calc.

Thank you for your reply.

[Villeroy]

What I wrote may be complete bullshit. It's a self made explanation.

[CapCrockett]

What I wrote may be complete bullshit. It's a self made explanation.

I still thank you for replying.

It's odd that today the spreadsheet is "working" as it should.

[Shemp]

Think or Swim DDE #N/A in OpenOffice Calc

Once a DDE link is established in a file, it remains in the file permanently. Over time, the number of links in that file build up and affect performance, causing links not to establish when opening the file. The result is #N/A where there should be a working link. You can see all the links by going to

Edit > Links...

Element is the ticker symbol
Source file is the type of data, i.e. Description, Mark, Yield, etc.
Status shows whether the link is active or inactive. “Not available” results in #N/A.

There are two ways to delete old links.

1. Select the link and click “Break link.” You can delete only one at a time, hope you’ve got all day.

2. Select all rows and columns in the file, copy, then paste into a new file. Save the new file, close it, then re-open it enabling the links. The only links in the new file are the ones in the sheet. All the other hidden ones are gone, and no more #N/A.

Note if you “Save as” to a new file, all the hidden links go with it, and #N/A doesn’t go away.

[CapCrockett]

Think or Swim DDE #N/A in OpenOffice Calc

Once a DDE link is established in a file, it remains in the file permanently. Over time, the number of links in that file build up and affect performance, causing links not to establish when opening the file. The result is #N/A where there should be a working link. You can see all the links by going to

Edit > Links...

Element is the ticker symbol
Source file is the type of data, i.e. Description, Mark, Yield, etc.
Status shows whether the link is active or inactive. “Not available” results in #N/A.

There are two ways to delete old links.

1. Select the link and click “Break link.” You can delete only one at a time, hope you’ve got all day.

2. Select all rows and columns in the file, copy, then paste into a new file. Save the new file, close it, then re-open it enabling the links. The only links in the new file are the ones in the sheet. All the other hidden ones are gone, and no more #N/A.

Note if you “Save as” to a new file, all the hidden links go with it, and #N/A doesn’t go away.

Shemp, thank you - this worked!

However, my Spreadsheet has formulas that reference other Sheets. Therefore, I must recreate them similarly. I might need to consider redesigning my Spreadsheet without references to other Sheets.

Also lost Drop-Down Lists, however, they aren't essential.

[CapCrockett]

Think or Swim DDE #N/A in OpenOffice Calc

Once a DDE link is established in a file, it remains in the file permanently. Over time, the number of links in that file build up and affect performance, causing links not to establish when opening the file. The result is #N/A where there should be a working link.

Shemp, did you discover this on your own or learn of it elsewhere?

[Shemp]

I figured this out (thank goodness) on my own. I’ve had this problem for years, and it slowly got worse and worse.

Saturday I did a search on openoffice DDE to see what i could find. I ran across an article that showed how to look at all the links in a file (Edit > Links), along with this thread. I took a look at the links and found well over 1000, with active links to ticker symbols I haven’t looked at in close to 10 years. Strangely, the newer ones in my file were inactive, and I couldn’t activate them. This made me wonder what would happen if I deleted all the old links I wasn’t using. Unfortunately, you can only delete them one at a time, and “Save as” keeps it all intact. So I created a new file and copy/pasted into it. Bang! Problem solved.

This problem was such an annoyance and for so long, I came back to this thread, registered, and posted what I discovered. I hope others can find it when searching on this problem.

[Villeroy]

The listbox showing all the links is a multi-selection listbox. Ctrl+A selects them all. (unfortunately not). You can delete them all manually. Since the DDE formulas are still there, the next calculation cascade will fill up the list with current links.

[CapCrockett]

I figured this out (thank goodness) on my own. I’ve had this problem for years, and it slowly got worse and worse.

Thanks!

[CapCrockett]

The listbox showing all the links is a multi-selection listbox. Ctrl+A selects them all. Then you can delete them all. Since the DDE formulas are still there, the next calculation cascade will fill up the list with current links.

Do you mean Break Link? Delete is not a choice.

Thanks!

[Villeroy]

Sorry, I was wrong about the multi-selection listbox. But you can break them all one by one. When the [Break] button has the focus, hit the space bar and confirm with the Enter key.
The DDE formulas remain on the sheets and when you save-reload the file, the existing links appear in the list again. I just tried with OpenOffice and a test document with 3 DDE links.