Trying to get a snapshot of realtime pricing of 600+ equities

Discussion in 'Programming' started by HotTip, Jan 31, 2013.

  1. HotTip


    ET Community,

    I'm testing an equities strategy that requires that I get a snapshot of the OHLC + vol of potentially >600 tickers a few minutes before the close. I tried to use the IB tws excel file connected to the IB api, but it chokes on that many tickers (I suspect because it's constantly refreshing, but even set at the longest refresh time it still chokes). I use YLoader for historical, but it doesn't query today's prices. I'm currently using Yahoo finance, but the max number of tickers I can put in a Yahoo portfolio is 200, so it requires I do multiple queries in order to get all the tickers on my list. I'd like to be able to do it with a single query.

    Does anyone know of a service or software that would allow me to initiate a snapshot of 600-1000 tickers OHLC prices (+vol) that I can then copy/paste to an excel file?

    Any help would be appreciated!
  2. Eyez


    Make a custom application with the IB API... problem solved
  3. Bob111


    really? how so? explain it here. with Ib's limit of 100 tickers and limited messages how the f** you going to accomplish that? no worse advise than a wrong one.

    OP-yahoo real time is your best,cheapest and easier bet.
    imo-yahoo multiple requests isn't a problem cause it's super fast. it's only 5 requests for 1000 tickers. it would take 2 secs max.

    another option would be IQ Feed(they have 400 tickers limit) but they are not going to be happy,if you made a lot of requests. talk to them
    i remember year ago or so i've ask them about this feature. they said that they are working on it.
  4. Eyez is right, it's possible. If you set the tick request snapshot value to true (1) then you can have as many tickers as you want. It just replies with the bid/ask/last/etc values and closes the ticker. Also, you can have more non-snapshot tickers if you pay a little $ and set it up in account mgmt.

    I do it all the time, but i don't think it can be done with Excel. I actually coded it and use the API as it should be used. Excel is really bad, don't use it, so learn to code.

    As far as i know there is also no message limit when you are querying tick request snapshots, so you can set it up to do a lot really quickly. I limit them at 1 per second just so IB doesn't freak out, but it's very likely you can do it much faster.

    You send the request with reqMktDataEx() and set the last (4th) parameter to 1.

    A few pitfalls: make sure you are sending the right conID of the contract, or just send symbol and primaryExchange correctly. Also, when getting a response, check that prices are not 0.0, it happened to me a few times.
  5. Craig66


    It's 500.
  6. HotTip


    Hi braincell. So, to be clear, I can do a larger number of tickers if I turn snapshot on, but I can't get OHLC + Vol?
  7. Eyez


    Sorry I said anything, I've just only developed HFT/algo strategies most of my adult career.

  8. Eyez


    You're right, it just can't be done with excel because excel is just clunky and not meant to handle that many requests. You're best bet is a C++ app that outputs a csv or excel file, or takes a snapshot of the data in a interval and saves to webserver which then u can import as a data table into excel. Prolly would be your best bet, trying this kinda analysis and data pulls on excel alone must kill your cpu load.

  9. Right. You can get the bid/ask and last, and i'm pretty sure that the tickSize will give you daily volume, but i don't use it. For OHLC you have to download historical bars, and they have request limits (60 per 10 minutes or so).

    If anybody knows how to pull today's OHLC from IB without using historical bars, I'd be really interested too.
  10. Bob111


    ok..just tried that..see it yourself :
    #10     Jan 31, 2013