How to get live data from Interactive Broker to Excel | Forex Factory

Options

Permalinks
Print Thread

Similar Threads

MT4 to Excel through Excel RTD (Finally!) 612 replies

Live historical data to excel 2 replies

Importing live data into excel? 19 replies

Can MT4 interface to Excel/Java/.NET/??? for interactive GUI control? 2 replies

Any Live news service offer data stream to Excel? 5 replies

How to get live data from Interactive Broker to Excel

  • #1
  • First Post: Mar 5, 2012 4:53am Mar 5, 2012 4:53am
Hello

I want to receive live forex data on my Excel sheet. My broker is Interactive Broker.

Any idea of tool or software to install/ buy

MT
  • #2
  • Mar 5, 2012 5:16am Mar 5, 2012 5:16am
Not sure how many use IB here on FF. You might try asking your question on a more focused list such as TWSAPI.

http://finance.groups.yahoo.com/group/TWSAPI/
  • #3
  • Mar 5, 2012 5:53am Mar 5, 2012 5:53am
If you are using MT4, there are a couple of ways of transferring price information:-

1) live price feed can be transferred by the DDE connection. Firstly, in MT4 CTRL+O to open the options box, click the "server" tab and make sure "Enable DDE server" is checked. This will export live price data for the pairs with an open price chart. There is a MS excel file in your MT4 directory called "DDE Sample.xls", use this as a starting point for your excel spreadsheet.

2) historical price data can be transferred by saving it into a .csv file that can be viewed in MS excel. Just do CTRL+S and rename the file or change the directory and the price data is saved (date, time, tick volume, O, H, L, C).

Good luck!
  • #4
  • Mar 5, 2012 1:45pm Mar 5, 2012 1:45pm
IB provides a spreadsheet that does that
  • #5
  • Apr 11, 2013 11:14am Apr 11, 2013 11:14am
Hi All - I'm classfied as a junior member, so the forum won't allow me to post a new thread. My apologies to anyone for replying to this thread.

I'm not a rookie - this is not my first rodeo. I've been trading (mainly US equities/AAPL (lately) and S&P futures) for over 20 years.

I've just (last 2 months) started Forex.

I have a completely running trading system (scratch built in VBA) to integreate with Interactive Broker's API.

My problem is that IB is rather ridiculous in only allowing data request real time about every 10 secs.

I tried the MetaTrader DDE Sample, connected to MetaTrader and received the updates very nicely. However, when I wrote code on the Worksheet_Change event it didn't work. However, when I brought up the DDE Sample and didn't connect and manually updated a cell (which was usually updated by DDE Sample), the Worksheet_Change event worked perfectly.

My problem is that I need to export the data to a "flat" ASCII text file to import into my IB API. Re-writing about 15,000 lines of VBA code/learning MQ4/5 are not real options at the moment.

Is there anyone out there that can enlighten me?

Thanks!
  • #6
  • Apr 11, 2013 11:34am Apr 11, 2013 11:34am
can you attach your excel file?
  • #7
  • Apr 11, 2013 11:46am Apr 11, 2013 11:46am
sangmane - THX for the quick reply - really appreciated!

By "attach file", do you mean does the DDE Sample "work" and return tick-data into the spreadsheet?

Yes it does.

"All" I want to do is get Forex tick data (probably via test file) into my IB API. I can (and do) build my own time intervals in VBA/Excel.

Thanks again for any help.
  • #8
  • Apr 11, 2013 11:53am Apr 11, 2013 11:53am
The pacing problem occurs only when pulling historic data from IB, no?

Why can't you maintain your own history and operate off of it?
  • #9
  • Apr 11, 2013 11:54am Apr 11, 2013 11:54am
hi loulu, i'm talking about Worksheet_Change not working.
i want to check why the above event doesn't get trigger by DDE
please attach DDE sample that contain the above VBA code, so that i can check myself.

btw, why not use SetLinkOnData?
  • #10
  • Apr 11, 2013 12:30pm Apr 11, 2013 12:30pm
sangmane & m.m.m. - once again, thx for the quick and thougtful replies!

m.m.m. - I can (and do) build my own history from IB data - I "build" my own 4h Forex intervals (using IB 1h intervals) starting at 5:15 pm New York time because IB builds their 4h on some convoluted time only they understand. But every time there's a data request they (IB) get their bowels in an uproar. My only real option is to really scale back the data requests - something I'm very relucatant to do (but may wind up doing anyway). I really needed the 10 sec updates day-trading AAPL on 5 min intervals - that price really moves. I may be able to get away with far less in 4h Forex. You may be right.

sangmane - wow, never even heard of SetLinkOnData. Researched it and found that it's not available on the version of Excel I'm running (2002). I have 2 newer versions sitting in the box and never installed. Without getting in a long-winded discussion, 2002 is where I "have" to be right now. I could update, but it would be painful.

2002 could probably why the event update on the link is probably not working.

Anyway, here's my code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("H6")) Is Nothing Then
Debug.Print "Hit H6"
End If
End Sub

If I manually update Range("H6") Worksheet_Change works properly.

Again, any and all help is greatly appreciated. And you've alreadly pointed out why (possibly) what I'm doing is not working.
  • #11
  • Apr 11, 2013 12:55pm Apr 11, 2013 12:55pm
Quoting Loulu
Hi All - I'm classfied as a junior member, so the forum won't allow me to post a new thread. My apologies to anyone for replying to this thread. I'm not a rookie - this is not my first rodeo. I've been trading (mainly US equities/AAPL (lately) and S&P futures) for over 20 years. I've just (last 2 months) started Forex. I have a completely running trading system (scratch built in VBA) to integreate with Interactive Broker's API. My problem is that IB is rather ridiculous in only allowing data request real time about every 10 secs. I tried the MetaTrader...
Question: Why do you need data request?

You could receive your data on your chart and using an Excel timer read it every 10 sec if you want or request a 10 seconds bar from ib. Every 10 secnds you will have your info. Just read a cell modification. When value as change, do your process

I'm not receiving my quotes on Excel. I decided to forget that option. I'm receiving end of a bar data from Multicharts ( up to 8 charts with 2 instruments in each) Processing is done in Excel. Orders are sent to IB via TwsLink api function.

It could be interesting to see the difference between the way that your are sending your orders via IB api and me using TwsLink function

Martin

Martin
  • #12
  • Apr 11, 2013 1:10pm Apr 11, 2013 1:10pm
S O L V E D !

Thanks all for any help.

Further research showed that Worksheet_Change DOESN't work with 2002.

BUT ... Worksheet_Calculate does:
Private Sub Worksheet_Calculate()
If Range("I5") <> Range("J5") Then
Debug.Print Time & "USDJPY m/p: New(" & Range("I5") & ") old(" & Range("J5") & ")"
Range("J5") = Range("I5")
End If
End Sub

Sample output:
2:03:09 PM USDJPY m/p: New(99.8945) old(99.8905)
2:03:11 PM USDJPY m/p: New(99.9115) old(99.8945)
2:03:13 PM USDJPY m/p: New(99.913) old(99.9115)
2:03:14 PM USDJPY m/p: New(99.919) old(99.913)
2:03:15 PM USDJPY m/p: New(99.9235) old(99.919)
2:03:17 PM USDJPY m/p: New(99.9135) old(99.9235)

I realize I have to be careful using Worksheet_Calculate, but this will be the ONLY task the DDE will be performing.
  • #13
  • Apr 11, 2013 1:20pm Apr 11, 2013 1:20pm
Quoting arjfca
Question: Why do you need data request? You could receive your data on your chart and using an Excel timer read it every 10 sec if you want or request a 10 seconds bar from ib. Every 10 secnds you will have your info. Just read a cell modification. When value as change, do your process I'm not receiving my quotes on Excel. I decided to forget that option. I'm receiving end of a bar data from Multicharts ( up to 8 charts with 2 instruments in each) Processing is done in Excel. Orders are sent to IB via TwsLink api function. It could be interesting...
Hi Martin - yes, I am currently using Application.OnTime, and it works well.

But ... IB "Counts" every data request (and I'm probably tracking 3-4 Forex pairs at a time). That would put me back to maybe a data request every minute or so.

Remember, MetaData DDE Sample is TICK data, not interval data, so you have to build your own. Haven't had the need to try TwsLink api, but it looks interesting.

I already have code in place to read a text file for input and build my own intervals, so I'll just use the output of DDE Sample and write to a text file to build it.

Once again, thanks.

p.s.: BTW, I have found the IB API to be reliable "enough" (once you understand the varagies of Excel, etc). If I had to do it again, I'd probably use pure VB/database, but I'm too far down the road to re-do the whole thing.
  • #14
  • Apr 11, 2013 1:31pm Apr 11, 2013 1:31pm
Good!


I have only briefly worked with the IBAPI to understand the principle.
However, I have used C++.
There I'd register callback functions, such as for ticks, realtime bars and historic bars.
Then I'd subscribe to data that I'm interested in (i.e. tick data for various instruments).
These callback functions are then called automatically when subscribed data is available (i.e. on every tick).
Meaning, there was no need to constantly pull data, because these were pushed.
But this in Excel/VBA probably works differently.
  • #15
  • Apr 11, 2013 1:42pm Apr 11, 2013 1:42pm
Quoting m.m.m.
Good! I have only briefly worked with the IBAPI to understand the principle. However, I have used C++. There I'd register callback functions, such as for ticks, realtime bars and historic bars. Then I'd subscribe to data that I'm interested in (i.e. tick data for various instruments). These callback functions are then called automatically when subscribed data is available (i.e. on every tick). Meaning, there was no need to constantly pull data, because these were pushed. But this in Excel/VBA probably works differently.
m.m.m/astro - thanks again. Will keep your thoughts in mind for "next time".

Yes, VBA/Excel is not the most elegant soluiton, especially if you are conversant in C++ (or some other "Real" programming language)!

I've got 3 years/15K lines of VBA code in the app, successfully day traded AAPL for 2 years with the app. It's too late to turn back now ...
  • #16
  • Apr 11, 2013 2:22pm Apr 11, 2013 2:22pm
Quoting Loulu
Hi Martin - yes, I am currently using Application.OnTime, and it works well. But ... IB "Counts" every data request (and I'm probably tracking 3-4 Forex pairs at a time). That would put me back to maybe a data request every minute or so. Remember, MetaData DDE Sample is TICK data, not interval data, so you have to build your own. Haven't had the need to try TwsLink api, but it looks interesting. I already have code in place to read a text file for input and build my own intervals, so I'll just use the output of DDE Sample and write to a text file...

Don't know if could help

Here is a tutorial on how to receive data from IB api to an Excel sheel. Remove the quote

" http://www.interactivebrokers.com/im...API/index.html "

Using IB api, you should be able to receive 10 seconds bar data

Good luck

Martin
  • #17
  • Apr 11, 2013 3:22pm Apr 11, 2013 3:22pm
Quoting arjfca
Don't know if could help Here is a tutorial on how to receive data from IB api to an Excel sheel. Remove the quote " http://www.interactivebrokers.com/im...API/index.html " Using IB api, you should be able to receive 10 seconds bar data Good luck Martin
Martin - thx, I will look into it.
  • #18
  • Mar 8, 2015 8:49am Mar 8, 2015 8:49am
Quoting arjfca
{quote} Don't know if could help Here is a tutorial on how to receive data from IB api to an Excel sheel. Remove the quote " http://www.interactivebrokers.com/im...API/index.html " Using IB api, you should be able to receive 10 seconds bar data Good luck Martin
I have actually tried the IB API for historical data. When I click on the request historical data, it just hang at processing and no spreadsheet is created.
  • #19
  • Last Post: Jun 22, 2017 11:36pm Jun 22, 2017 11:36pm
Is there any software or tools available to get live feeds to my excel
0 traders viewing now