Import/Link IB quotes into Excel using DDE

Eldredge,

THAT is what i'm trying to do too!! (and i thought it would be as easy as you are saying it is with the other services you are using)

I'm heading out of town for a few days but i suspect it's a matter of digging out the code from the VB Editor (in Excel) and copying it to our own spreadsheets. As i'm new to IB, i just wanted to make sure i could do what i needed to do so i can begin trading when i get back. So today's task was to focus on getting up and running, which i did, although it involved having the "sample spreadsheet" open, which i can live with for now.

When i return, i'm going to try to go thru the VB code and try some other things to see if i can figure out how to get everything into my own spreadsheet without having to keep the sample spreadsheet open.

So hopefully (<-- read "maybe") i'll have some sort of a solution next week.

Since i'll be out of town, please feel free to PM me regarding this next week in case my mind gets bogged down with something else between now and then.

Of course, if either of us (or any lurkers out there) have a solution, please feel free to post it.
 
Just try the spreadsheet IB provides. Here is the cell example for last:
=edemo|tik!id0?last

and BID:
=edemo|tik!id0?bid

I'm using the edemo account.

Isn't this what you're looking for?
 
To clarify further:

IB's DDE works a little different than others in that you have to first tell it you want to get quotes for a particular instrument, and associate a user-defined ID with that instrument. Once this formula is entered, you can then use that ID to get the various quote values for that instrument.

This makes sense because of the wide variety of instruments that IB supports, and the number of parameters required to define some of them (e.g. options). It would be silly to have to repeat all those parameters in the formulas for the bid, ask, last, etc. This way, you just map them to an ID, and then use the ID to get the quote data.

In practical terms, assuming you have one symbol per row, and columns for bid, ask, last, etc., you will also have a "Request" column in which you will enter this "contract definition" formula.

So, to get quotes for MSFT stock on the Instinet ECN, you'd enter the following formula in the Request column:

=xxxxxx|tik!id1234567?req?MSFT_STK_INSTINET

where xxxxxx is your username and 1234567 is the request ID that you want to use. This ID should be different for every new instrument that you want to display, and you should not re-use them. I just use a cell on the spreadsheet to keep track of the last value used.

Once you've entered this formula, you can then enter formulas in other columns of that row to get the quote data, like:

=xxxxxx|tik!id1234567?bid
=xxxxxx|tik!id1234567?bidSize
=xxxxxx|tik!id1234567?last

etc.


Basically, this is what the requestMarketData() macro does in the sample spreadsheet. It creates and enters the Request formula and then enters the related formulas in the other columns.
 
AlanM
Thanks for the explanation, that cleared up a few things as far as the "process" of how IB's links work. Like i said above, i'm away from home right now, so i'll play around with it at the beginning of next week.

One question though, do you have to change the id# each session? Using the above example, will id1234567 always be assigned to MSFT for my username? If i set things up like that on Monday and then log out at the end of the day, on Tuesday, will 1234567 still be assigned to MSFT?

Also, i saw on IB's website about the formats to use for various instruments, but can you give examples of the formats for futures and indexes? It's not clear when it's necessary to simply omit a parameter that doesn't apply to your instrument and when it's necessary to use a value of "0".


Vikana
As you can see from AlanM's post, it's not as simple as doing what you describe, as nowhere in your line of code does it dictate what symbol to use. I think THAT is where most people get confused in creating their own spreadsheet from scratch, as it had never been described to me as AlanM has just described it. Hopefully this is the problem i was having, and it'll all be working for me smoothly next week.
 
Originally posted by thereuare
Also, i saw on IB's website about the formats to use for various instruments, but can you give examples of the formats for futures and indexes? It's not clear when it's necessary to simply omit a parameter that doesn't apply to your instrument and when it's necessary to use a value of "0".
More...

You should never use a 0 value - each type of instrument has its own parameters, all of which are required.

September S&P E-mini: ES_FUT_200209_GLOBEX
Nasdaq 100 index: NDX_IND_NASDAQ

Note that the exchange to be used is not always obvious (like "CBOE" for the SPX). The best way to find out what exchange to use is to first enter it through TWS and then look to see what it used.

Also note that there is currently a bug that prevents you from monitoring stock symbols that contain a space (like VIA B).

The sample DDE spreadsheet (TWSDDE.xls in the Excel subdirectory of your TWS installation directory) is a good way to find some of this stuff out, too. If you select the first cell of a row in the Tickers sheet, and then click the Request Market Data button, it calls the requestMarketData() macro. You can then look at the formulas in the "Ctrl" column to see how it constructed the definitions.
 
AlanM,

Thanks, i'll try variations of those 2 formulas above when i return home!

Would you mind also addressing this part as well, so i know if i can used a fixed value for the id or if i need to set up a n=n+1 formula:

One question though, do you have to change the id# each session? Using the above example, will id1234567 always be assigned to MSFT for my username? If i set things up like that on Monday and then log out at the end of the day, on Tuesday, will 1234567 still be assigned to MSFT?

Thanks again for your help and detailed explaination.
 
Originally posted by thereuare

Vikana
As you can see from AlanM's post, it's not as simple as doing what you describe, as nowhere in your line of code does it dictate what symbol to use. I think THAT is where most people get confused in creating their own spreadsheet from scratch, as it had never been described to me as AlanM has just described it. Hopefully this is the problem i was having, and it'll all be working for me smoothly next week.
More...

You're obviously right. My attempt at answering wasn't clear. It was meant as a followup to a prior post where I talked about the need to look at/copy the VBA code into your own spreadsheet.

If you can afford it, I'd recommend that you get a real quote provider, such as eSignal (which is what I use). I find IB quotes off at times.
 
Originally posted by thereuare

One question though, do you have to change the id# each session? Using the above example, will id1234567 always be assigned to MSFT for my username? If i set things up like that on Monday and then log out at the end of the day, on Tuesday, will 1234567 still be assigned to MSFT?
More...

I meant to answer that one :-)

You do not have to change the ID number each session for quotes. I assign the IDs when I add a symbol to my spreadsheet. Bringing up the spreadsheet with existing formulas in it will receive quotes for those formulas just as it did when the spreadsheet was closed. TWS doesn't actually remember that MSFT is id1234567 - it knows this when you bring up the spreadsheet and it again processes the contract definition formula. This is why the quote values are briefly invalid when opening an existing spreadsheet - because it takes a few seconds to process all the contract definition formulas.
 
You must log in or register to reply here.
Back
Top