DDE Output Object with Multiple Symbols

[pa-kay]

I don't know if this should work, but when I try to link DDE output from scripts running in multiple charts with different symbols, the values do not automatically update in Excel. In order to fill all of the spreadsheet cells for each of the different symbols, I end up going back and forth between the two programs repeatedly to update the DDE links. In eSignal, I select a chart and reload the data, then I must go to Excel, double-click on the linked cell and hit return to update that link. This is the only way I have been able to transfer the data. Is this normal, or am I overlooking something?

[eSignal_JasonK]

Hello Pa-Kay,

In Excel, go to the "Calculations" tab under the Tools-->Options menu. Do you have the Automatic option checked?

[pa-kay]

Yes, this option is checked.

[eSignal_JasonK]

Hello Pa-Kay,

Can you post some of your code here so I can test this on my end?

[pa-kay]

Here is my script. If you see the same thing that I do with this, as you add open charts with different symbols corresponding to links set up in Excel, those links will not update automatically. However, if you close and re-open the Excel file, some or all of the links will update with varying results. There may also be some relationship to running this during market hours versus after. As I mentioned before, double-clicking the link and hitting the Enter key will update the link, but sometimes I need to reload the chart data for that specific symbol first.

My intention with this script was to retrieve historical data for portfolio tracking and be able to easily back fill missing data for any given day. The DDE Hot Link in the SigTools add-in works very well for current data, and I was trying to create a similar function for collecting past data.

[eSignal_JasonK]

Hello Pay-Kay,

I understand your problem now. Excel only checks for a valid DDE connection on the initial load of the spreadsheet. Excel doesn't have a method that I know of that will periodically re-check for the DDE connections that weren't present at the initial load time. Before you open your spreadsheet, you have to have eSignal running with all your required charts active so that all the DDE connections you need are available for Excel at load time of the spreadsheet.

I think the only way for you to have Excel update a DDE link when you open a new chart symbol after you're opened your spreadsheet is to do it manually, or close/reopen the spreadsheet. If you're interested in coding a VBA macro in Excel, you could create a button to run some VBA code that runs through a loop and activates a list of cells to force the update of the DDE links. You would still have to manually click a button, but it would be much faster than manually activating the list of cells one at a time. Hope this helps.

[pa-kay]

JasonK,
I think I understand what you are saying, and if it would work as you say, that would satisfy my needs. However, even though I have all charts open and running before opening the spreadsheet, not all of the links will update. Also, if try to update the links through the Excel pull-down menu Edit/Links/Update Now, they will not update. Wouldn't this be the same command that you are referring to in the VBA loop? Please see if you get the same results as me using that script with several charts open. If you can consistently update all of the links upon starting the spreadsheet, then hopefully I will find something else wrong on my end.

Thanks.

[eSignal_JasonK]

Hello Pa-Kay,

Just to clarify, the charts running in eSignal also have to have your DDE formula running, which establishes the DDE string for each symbol in your spreadsheet. You will also need to go into Edit Studies and enter the Year, Month and Day parameters for each formula on each chart, otherwise it won't be creating a DDE string for that symbol that doesn't have these parameters set. This may be the problem you're having. Everything is updating on load of the spreadsheet just fine on my end. I did make a few modifications to your formula however, just to improve performance a little. You might want to try again with the version below. One thing I should point out is that you should declare your DDE objects outside of main. You only need to do this once. By putting them inside main, you're essentially reinitializing the object each time the formula runs, which isn't necessary.

pa-kay_dde_efs_sample.efs

For the VBA routine, if you can find the method for calling the update for links, that would definitely be better than creating the loop I mentioned previously. If it exists, you should be able to find some info on this at Microsoft’s knowledgebase.

[pa-kay]

JasonK,
I appreciate your help. I think the problem was fixed by moving the DDE outside of main. I was a little confused by the date issue until I noticed the return you added to the conditional test of the input parameters. My script was written to load the current date by default. Can I use a global variable in some way to avoid entering the date on each chart seperately? I am planning to read the date from a text file, but perhaps there is a better way.

[eSignal_JasonK]

Hello Pa-Kay,

Using File I/O would be a good way to handle it. Try that first. You could alternatively use a setGlobalValue()/getGlobalValue() routine. Give it a shot and let me know if you need any help.