Home > Tools of the Trade > Data Sets and Feeds > Pulling Data into Excel from IB

Pulling Data into Excel from IB

  1. Hi folks,

    I was wondering if anyone can do me the favor of uploading a sample Excel spreadsheet that would bring in the following data in real time from IB's TWS:

    TICK-NYSE
    TRIN-NYSE

    Advancing Issues
    Declining Issues
    Up Volume
    Down Volume

    I have messed with this for a while and am finding out that I'm pretty bad at accessing the data thru Excel. I read some of the stuff they have on their website, but can't seem to pull these Market Statistics up.

    Your help is appreciated.
     
  2. Wow... that is a great app. Works really well with stock symbols, etc. Nice tool to tinker with. I figured out the symbol for the tick to be $TICK-NYSE.NYSE since they consider the market stats to be an index.

    I appreciate your help. I will post a spreadsheet with the data for those who might be interested once I get it figured out.
     
  3. Cool, glad it worked out.
     
  4. Weird website. Looks good on the surface, but it seems to lack real info about the product. Many links lead to pages that say "0 pages found".
     
  5. Yo 71'

    Since you are a reality tape reader trader and do DDE why not build a SP500: TRIN, ADD/DECL yourself and use NeoTicker to build your own SP500 Tick index

    then you getting the direct changes in the SP500 (for reading the ES futures) verus detecting them out of the NYSE indicators which hold 4 times as many stocks and have a great many lags and stale readings aplenty...

    you can take the DDE TRIN and ADD formulas created in your DDE and throw them into Sierra Chart to Chart them in real time ($15 a month)... and have them update every 1 second... so they are very real time and ahead of NYSE indicators and more accurate...

    cj...

    :)
    __________________
    HAVE STOP - WILL TRADE

    If You Have The Vision We Have The Code
     
  6. You are absolutely right. This is something I have considered for the FDAX, YM and ES contracts. I have the fast cash for those products, so pulling the tick, AD/Dec and TRIN for those should be a piece of cake. It is an awful lot of data and processing to do in Excel or to bog down my CQG machine with, but I will give that a shot. The CQG Tick/Trin/breadth are far too slow. The tick is updated every 15 seconds, Trin is 40 seconds and breadth every minute. I noticed IB bringing that data in sooner (every 2 seconds?). The reality of the matter is, I can gain a lot by having them in realtime.

    Thanks for planting that thought back into my head. :D
     
  7. Well, you could use eSig DDE with 500 or more symbols on <b>a separate PC</b> and do the DDE on that to save CPU and freezes from occuring on Important screens on your main PC...

    then throw the DDE update data across a short patch cable/network to your charting app and off you go with no CPU embarrassment...

    OR also chart it on the same alternate PC that is doing the DDE and just do a Terminal services window tap into it on an extra monitor on your MAIN PC...

    i do it with 600 stocks on my DDE with eSig and it takes 30% of CPU only every few seconds....

    i am moving to a dual core chip soon and assigning the excel dde exe to one core and my main trading app to the other core..

    I do the Fast Cash for the SP500 for its ADD, TRIN, VOLD, NET Change, LAST, etc... gives a better read...

    i even chart the 15 second price against the Fast Cash price to see when they jump against each other... interesting...

    cj...

    ps. creating the tick is a bit tricky...

    :)
    ____________
    HAVE STOP - WILL TRADE

    If You Have The Vision We Have The Code
     
  8. I have attempted in the past to pass data from one PC to another across a network and couldn't get it to work. I tried to do that with the fast cash being computed on the CQG PC and then passing the last fair value price to the TT (trading) machine to display the fair value on the market depth. I guess I'm just not versed enough in IT and programming to be able to do that. I'm good with a spreadsheet, but that's the last stop.

    It would be great if I can get in touch with you to find out how you are able to pass data across PC's. That would be a huge asset for what I'm doing.

    Also, why would the TICK be difficult? It is basically the number of stocks upticking minus the number of stocks downticking. Is it a sych issue with the price feed?
     
  9. 71'

    My account info has my website and my website has my phone number or you can send me an email...

    Basically you use a VBA timer event inside of excel Visual Basic IDE and it fires off every 1 or 2 seconds and during THAT execution of code the code collects the data from the formula fields that total up the SP500 ADD, TRIN, LAST, etc and then INSERTS those values (across a network share) into a database file that is charted from a Charting Software package automatically...

    pretty straight forward once you have the timer event in Excel firing off at whatever interval it is set to fire off at....

    The TICK is tricky TO CODE because you always have to hold an initial value and compare it to the next changed value and then create a 1 0r 0 boolean flag in the next row to show wether it was an up or down tick and then if the value was not changed from the inital value you have to decide how you are going to handle that or ignore it... then the last formula field also has to total all the 1's and 0's to see what your tick reading is...

    really CPU intensive... at least for what we tried and gave up on... we do the ADD, LAST, TRIN, VOLD, etc... but not the TICK...

    The NeoTicker software does the best job of this... they take all the symbols (500) and put them in a Ram Cache and then run the algo thru them to get the Tick for the SP500... but they go one step further in that they don't score a tick move as happening unless 16 UP ticks have occured or 16 down ticks have occurred thereby ruling out the false and stale tick moves you have in the NYSE Tick...

    anyway - they call their indicator Tick16... check out their docs... they explain it better than me...

    HTHelps

    cj...

    :)

    __________________
    HAVE STOP - WILL TRADE

    If You Have The Vision We Have The Code
     
  10. If you're using eSignal DDE, I think one of the fields actually reflects whether the last tick is an up or down tick so there is no need to hold onto previous values. It may save CPU cycles but may not as there may be some string parsing to do.
     
  11. Hi Bernouli,

    excellant point...

    i was not aware of that DDE field - the 5cent solution is always the best if it works...

    Now that i look at it I think it shows the last four ticks... but i will have to check...

    but your right parsing it might be a bit of a challenge... need to take a gander at the little sucker...

    let me know if you have any other thoughts...

    cj...

    :)

    _________________
    HAVE STOP - WILL TRADE

    If You Have The Vision We Have The Code
     
  12. My recollection is that it does show the last 4 changes, so you can use the right() function. You can wrap the return value into an if statement that returns +1 if '+' and -1 if '-' so you can sum all the values and get a TICk or you can use countif to add all + values and to subtract all - values (=COUNTIF(J1:J500, "+") - COUNTIF(J1:J500, "-")). My guess is that countif is faster (fewer calls to get the final value).
     
  13. Yes SumIf or CountIf will work fine but i was wondering how <b>stale the updates can get for a number of stocks that do not update for awhile</b>

    since i am polling the final sum/count IF formula at the bottom of the TICK column by my timer event every 2 to 3 seconds - if a tick value does not change on a bunch of stocks but just sits there i would be picking up the same value from it and RE-ADDING IT - to the new event total which would throw off my real time accuracy...

    I could INCLUDE a new cross check variable - TIME - that would help...

    i could use the...

    TIMEREALTIME Time (hours, minutes, seconds) of the last real-time activity

    field to test for the time update on each tick value on each field after my each timer event fires off and the next one begins to filter stale ticks but now we are getting back to CPU rag out cost again... sigh...

    still tricky... to be accurate...

    kinda why i like the ram Cache solution worked out by NeoTicker...

    cj...

    :)

    __________________
    HAVE STOP - WILL TRADE

    If You Have The Vision We Have The Code
     
  14. Hi!



    Would anyone please tell me the IB NYSE total volume symbol and NASDAQ volume symol.

    I know it is $TVOL and $TVOLQ on other places.

    I have been tryin to download the 5m intraday data.


    Thanks for your help.
     
  15. Per IB web site (http://www.interactivebrokers.co.uk/contract_info/index.php) it is supposed to be VOL-NYSE. But I am not getting any values in TWS. Maybe it's available through the API.