IB : basic excel formula question

TraDaToR

TraDaToR

Hello,

I am trying a new way to create DDE commands without using much VBA macros...

Let's say you want to request datas for ES , you type =myname|tik!id1?req?ES_FUT_200906_GLOBEX in a cell.

If the instrument( ES )is in cell A1 and expiry( 200906 ) is in B1, why doesn't it work if I use the following formula in the cell :

= "=myname|tik!id1?req?" & A1 & "_FUT_" & B1 & "_GLOBEX". The code is created fine but it seems no DDE link are created. I should have a "0" in the cell instead of =myname|tik!id1?req?ES_FUT_200906_GLOBEX. If I use VBA to create the same code, it works. ???

How can I work around that? I know it's more of an excel problem than TWS API but I was thinking some here had the same problem.

Thanks a lot for your help
 
I hope I understood correctly, and that I won't make you waste your time. I am a beginner as a vba programmer, but I came across similar problems before.

On that workbook you should have a "Tickers" sheet, to request quotes which, once requested via the button "request market data", keep on updating themselves automatically.

The DDE strings that you are mentioning ("=myname|tik!id1?req?ES_FUT_200906_GLOBEX ") won't update automatically because that string is created and updated by excel only if you push the "request market data" button. You can't just make them up yourself and expect them to get data from TWS.

You can then copy DDE strings to other sheets but only the "=myname|tik!id4?last" strings and similar, and they will update but - I think - only if they are updating already on the Tickers sheet, after you activated the "requestMarketData" macro.

I may have written a lot of imprecisions as vba is not my mother tongue.
 
Thanks a lot Travis,

I don't think it is the problem here. In fact, i don't use IB sample worksheet but know the tickers page you're referring to.

I've done my own worksheet for my production systems with VBA macros. As soon as the datas are requested one time, bid, ask, askprice... keeps updating without intervention.

If I place "=myname|tik!id1?req?ES_FUT_200906_GLOBEX
in a cell and press enter, it will request datas and update until DDE link is broken( closing excel or TWS ). If I use a macro that will send in cell C1 the code with :

Worksheets( "Sheet1).Cells( 1,3).Formula = "=myname|tik!id1?req?" & Worksheets("Sheet13).Cells( 1,1).value & "_FUT_" & Worksheets("Sheet1").Cells(1,2).Value & "_GLOBEX"

it works perfectly too.

It's just when I try to use excel formulas directly between cells that it doesn't work. Does anybody have an idea?

Thanks again.
 
Quote from travis:

Yeah, I was wrong about the problem, and I was wrong about how DDE links work as well. Thanks for the info.
More...

travis -- You have put your ego aside which seems to a positive trait to achieve true objectivity -- I expect you'll do well.

Tradator -- there is no way to do that, that I've discovered, except via VBA, and I've done a fair bit of research on that topic.

Excel's "indirect" is the closest that seems to come, but it will not work correctly with DDE and returns a #REF error.
 
OK thanks Big Dave,

That's what I was thinking since I tried several things yesterday night which didn't work either.
 
You must log in or register to reply here.
Back
Top