Live Prices into Excel from IB

[RazzaDazzla]

I have a spread sheet with my current open positions on it.

At the moment I manually update the "Last Price" field at the end of each day to then calculate my % returns etc etc.

I am aware that I can do this automatically by using the IB TWS API. I found a video on youtube (http://www.youtube.com/watch?v=S1KL-sGdgxs) that explains how to set it up.

It involves using a third party Excel plugin. Interestingly, this plugin is no longer listed on the IB software page. This, I find odd.

This Third Party Excel plugin is called IB-Excel Adapter by cyberxpert (http://www.cyberxpert.com/downloads.html)

Anyway; is there any better Excel plugins I should be using? Or a better way to do what I am trying to do?

 

[MS+Tradesim]

If you are using the TWS app on your computer rather than logging in via internet...

1) Make sure you have installed the IB API. If not, download it...
2) while TWS is not running, install the API
3) login to TWS and follow the menu: Configure -> API then tick the boxes to enable ActiveX and DDE clients.
4) PM me an email address and I will send you a 0.8mb Excel spreadsheet which was offered by IB. Not sure if they still have it on their site. It still works, I just tested it. You can reverse engineer whatever you need to do off this spreadsheet.

 

[RazzaDazzla]

Thanks for the spreadsheet MS+tradesim.

I had a poke around the IB website, but couldn't find any documentation on the sheet.

The sheet is a bit more in depth than what I required. It also isn't running when you try to refresh prices in Excel 2007.

I basically just need some form of an Add-In for Excel which will connect to TWS and get live prices.

 

[MS+Tradesim]

I have no trouble using it to get live updating FX data.

If you tell me exactly what you want to achieve, I can have a go at it and maybe create you a template that you can then adapt. But I do have limited time at the moment so can't make any promises.

 

[RazzaDazzla]

I just want to import live prices from IB into a current spreadsheet I have for ASX prices. Either on a time interval or on clicking a refresh button.

So the required excel plugin and code for the required cell is all i believe I need.

 

[MS+Tradesim]

Ok. If you have TWS running and the API installed, all you'll need is some code in your spreadsheet - you would only need some other plugin if you are not using their API. Shouldn't be hard to make a template which you can then just copy and adapt for each stock. I have some time tomorrow and will have a go at it.

 

[kr1zh]

is this free or i have to become member of interactivebroker?

 

[MS+Tradesim]

is this free or i have to become member of interactivebroker?

You have to be an IB customer to use their system.

 

[MS+Tradesim]

Razza,

You can find the API reference guide here. It's about a 9mb file. The only part in it you might want to look at is the DDE section. http://individuals.interactivebrokers.com/download/newMark/PDFs/APIprintable.pdf

The following will only update live while TWS is logged in and the relevant market is open. Also as you probably know, TWS doesn't work over the weekend. But you can save the data that's already fed into your spreadsheet. Here's an example you can adapt to your own needs (you'll find all the references and syntax in the above guide). As I said earlier, the API also needs to be installed, and enabled in TWS or this won't work.

In a spreadsheet, copy and paste each of the following lines into separate cells.

=tryme123|tik!'id0?req?RIO_STK_ASX_AUD_{}'
=tryme123|tik!id0?bidSize
=tryme123|tik!id0?bid
=tryme123|tik!id0?ask
=tryme123|tik!id0?askSize
=tryme123|tik!id0?last
=tryme123|tik!id0?lastSize
=tryme123|tik!id0?volume

Replace "tryme123" with your IB username. If you are logged into Paper Trader you'll need to use that name, and if logged into your live account you'll need to use that name.

Basically, the first line assigns "ID 0" to the ticker which must be in the following syntax: Symbol_Type_Exchange_Currency. In the above example you have:

Symbol: Rio
Type: Stock
Exchange: ASX
Currency: AUD

Then each of the following lines above grabs the relevant info for the defined ticker.

For each new symbol you create, you will need to remember to have the matching assigned ID in each line. For instance:

=tryme123|tik!'id1?req?CBA_STK_ASX_AUD_{}'
=tryme123|tik!id1?bidSize
=tryme123|tik!id1?bid
=tryme123|tik!id1?ask
=tryme123|tik!id1?askSize
=tryme123|tik!id1?last
=tryme123|tik!id1?lastSize
=tryme123|tik!id1?volume

=tryme123|tik!'id2?req?BHP_STK_ASX_AUD_{}'
=tryme123|tik!id2?bidSize
=tryme123|tik!id2?bid
=tryme123|tik!id2?ask
=tryme123|tik!id2?askSize
=tryme123|tik!id2?last
=tryme123|tik!id2?lastSize
=tryme123|tik!id2?volume

...and so on. Now remember the above are just examples. You can grab as many or as few data points as you need. If all you want is the last price then all you would need is:

=tryme123|tik!'id2?req?BHP_STK_ASX_AUD_{}'
=tryme123|tik!id2?last

Obviously you can only access data for the market data subscriptions that you hold. If you want to try a demo tonite, then subscribe to FX data if you don't already have it (it's free) and try this example:

=tryme123|tik!'id4?req?EUR_CASH_IDEALPRO_JPY_{}'
=tryme123|tik!id4?bidSize
=tryme123|tik!id4?bid
=tryme123|tik!id4?ask
=tryme123|tik!id4?askSize

The IB demo spreadsheet I sent you has all of the above mapped to macros which makes life a lot easier.

 

[Trembling Hand]

MS the TWS does work now on the weekend.

 

[MS+Tradesim]

MS the TWS does work now on the weekend.

Oops.

That's twice I've been wrong today. Unheard of.

 

[RazzaDazzla]

Wow, thanks tradesim!

Any idea on how to view the code behind the IW spreadsheet so I can see how they've got there macro's setup etc?

I'd like to add a few features from there sheet to mine. Such as a username field etc so then I could potentially switch between paper account and real account.

One issue I am having; It works great on my laptop (Vista x86, Office 2007) but not on my desktop; (Vista x64, Office 2007). Only difference being desktop is 64bit.

When I enter the code in Excel on my desktop, Excel just freezes and the little blue circle just spins.
The spredsheet works fine on laptop; but the exact same spreadsheet will open on the desktop, but will then just freeze.

Any ideas?

 

[MS+Tradesim]

Hi Razza,

You can access the Visual Basic Editor by pressing ALT-F11. From there you can view and edit all the VBA code.

I don't have any 64bit programs or OS so I have no idea about that.

 

[energetic]

hi, MS+Tradesim
I appreciate if u can also send me a copy of such excel file

If you are using the TWS app on your computer rather than logging in via internet...

1) Make sure you have installed the IB API. If not, download it...
2) while TWS is not running, install the API
3) login to TWS and follow the menu: Configure -> API then tick the boxes to enable ActiveX and DDE clients.
4) PM me an email address and I will send you a 0.8mb Excel spreadsheet which was offered by IB. Not sure if they still have it on their site. It still works, I just tested it. You can reverse engineer whatever you need to do off this spreadsheet.