Mike
Yes lots of people know how to collect tick by tick data from the API (including me - I collect tick data all day 5 days a week). I told you as much in the very first reply to your post.
I tried to make clear that you have to do this with a program, using the TWS API (which is what this group is all about) What you haven¡¯t told us is whether you¡¯re a programmer: if not, there¡¯s not much point someone telling you how to do it using the API, because it won¡¯t mean anything to you. But I did say that in that case you could ask for help creating a program, but it would probably involve paying someone to do it, because this stuff is not trivial.
Personally, I¡¯ve found the various replies you¡¯ve had quite interesting, and I¡¯ve certainly surprised myself (once again!) with how performant Excel is.
So please tell us what sort of help you¡¯re looking for.
Richard
From: TWSAPI@... [mailto:TWSAPI@...]
Sent: 02 June 2016 18:46
To: TWSAPI@...
Subject: RE: [TWS API] Re: I am new to this group and post my first request
Richard,
Please advize the group of my PRIMARY request and question: Is there anyone out there who knows HOW to record/collect tick-for tick data on any stock. I'm not interested in emails and people putting their two cents into why this is a stupid idea. Either one knows how to collect data from the TWS platform or not. I DO NOT KNOW, hence my original post, as I am asking for HELP or leads.
If not, I'm NOT interested in any periferal comments as they DO NOT address my original post. TO THOSE, Please leave me alone. Getting reAl frustrating on my end. Not mad at the people who must know how to do this. Just very aggravated about people trying to tell me of the impracticalty of what some THINK I want to do with this data.
Thanks for your cooperation.
Mike
--------------------------------------------
On Wed, 6/1/16, 'Richard L King' rlking@... [TWSAPI] <TWSAPI@...> wrote:
Subject: RE: [TWS API] Re: I am new to this group and post my first request
To: TWSAPI@...
Date: Wednesday, June 1, 2016, 9:30 AM
Joe
You¡¯re even more out of touch with Excel than I am!
In my first reply, I mentioned that I imported a day¡¯s
worth of ES tick data into Excel and it barely noticed.
I¡¯ve increased that to 5 days and again, it¡¯s not even
breaking a sweat on 943,403 rows (yes, that¡¯s nearly a
million). It¡¯s using 250Mb memory, but that¡¯s peanuts on
a modern computer.
Writing data to an Excel cell is nothing like as slow as you
seem to think it is. It can easily stay current with the
realtime data stream from the TWS API. Somewhere I have an
old workbook that I created years ago that gets realtime
data for up to 40 simultaneous tickers from the ActiveX API,
and displays it in a worksheet realtime. It has no
difficulty keeping up. If you like I can bring it up to date
with API 9.71 and send it to you. Of course this is writing
to the same set of 300 or so cells all the time, and I¡¯d
expect writing each tick to a new row to be somewhat slower,
but probably not much. Also I assume the original poster¡¯s
intention was to collect the data for, say, a day, and then
analyse it later, rather than do the analysis in realtime as
it arrives: the latter would also slow things down of
course, but I doubt it would hinder it much on a handful of
tickers.
The TWS API is perfectly capable of giving you tick data in
near-real-time: I¡¯ve been collecting this data for 13
years and have never had much reason to complain about it,
within its known limitations. The main limitation is that it
splits time into roughly 300 millisecond intervals (though
much shorter for Forex data), and at the end of each
interval it sends you the changes since the end of the
previous interval. So if there were say three trades during
an interval, you would only get to hear of one of them, and
then only if the price and/or size was different from the
last one sent. In particular if the price moves up and then
down again during an interval, you may not get the ¡®up¡¯
price, and so highs and lows of bars that you create from
this data may occasionally be out by a tick or two. In
practise this isn¡¯t nearly as bad as it might sound, since
most contracts don¡¯t actually trade very quickly most of
the time. This description isn¡¯t the full picture, but
it¡¯s close enough.
But anyway I agree with your conclusion that it¡¯s better
to write the data out to a file (one per day for example,
though I actually do one per week). Then it can be easily
imported into Excel and analysed ad nauseam and the original
data is still preserved in the file.
Richard
From: TWSAPI@... [mailto:TWSAPI@...]
Sent: 01 June 2016 12:52
To: TWSAPI@...
Subject: [TWS API] Re: I am new to this group and post my
first request
Hi, Mike.
I would like to start downloading data on stocks or
futures on a tick-by-tick basis. I would like to RECORD and
import (export from wherever is required from IB) this data
to an MS Excel format. From there I will know how to program
the spreadsheet and analyse the data as I see fit (don't
want to confuse any readers with too much ultimate detail at
this point).
As I understand that sentence, it is not a workable idea.
First, the amount of data you are describing would quickly
fill the limits of Excel rows or columns.
Second, writing data to an Excel cell takes time - more time
than simply storing it in a variable, even if the
spreadsheet is set to not visibly update cells to the user
(but FAR MORE TIME if it does try to stay current).
You'll be bogging down the program with updates, keeping
it from processing the information into decisions.
Finally, I'm not sure - someone else can verify or
refute this - but I don't believe the TWS connection can
reliably give you tick-by-tick data in near-real-time.
(Please correct me if I'm wrong here, folks - I
don't look at that level of data).
Therefore, it would be best to send the data to a text file,
and keep only the last N data points (or S seconds worth of
data, or whatever) handy in memory. When the program loads,
it can easily load the tail end of the last data output to a
file, and use that to fill the array of most-recent data
points.
--Joe
#yiv2855639115 #yiv2855639115 --
#yiv2855639115ygrp-mkp {
border:1px solid #d8d8d8;font-family:Arial;margin:10px
0;padding:0 10px;}
#yiv2855639115 #yiv2855639115ygrp-mkp hr {
border:1px solid #d8d8d8;}
#yiv2855639115 #yiv2855639115ygrp-mkp #yiv2855639115hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}
#yiv2855639115 #yiv2855639115ygrp-mkp #yiv2855639115ads {
margin-bottom:10px;}
#yiv2855639115 #yiv2855639115ygrp-mkp .yiv2855639115ad {
padding:0 0;}
#yiv2855639115 #yiv2855639115ygrp-mkp .yiv2855639115ad p {
margin:0;}
#yiv2855639115 #yiv2855639115ygrp-mkp .yiv2855639115ad a {
color:#0000ff;text-decoration:none;}
#yiv2855639115 #yiv2855639115ygrp-sponsor
#yiv2855639115ygrp-lc {
font-family:Arial;}
#yiv2855639115 #yiv2855639115ygrp-sponsor
#yiv2855639115ygrp-lc #yiv2855639115hd {
margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}
#yiv2855639115 #yiv2855639115ygrp-sponsor
#yiv2855639115ygrp-lc .yiv2855639115ad {
margin-bottom:10px;padding:0 0;}
#yiv2855639115 #yiv2855639115actions {
font-family:Verdana;font-size:11px;padding:10px 0;}
#yiv2855639115 #yiv2855639115activity {
background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}
#yiv2855639115 #yiv2855639115activity span {
font-weight:700;}
#yiv2855639115 #yiv2855639115activity span:first-child {
text-transform:uppercase;}
#yiv2855639115 #yiv2855639115activity span a {
color:#5085b6;text-decoration:none;}
#yiv2855639115 #yiv2855639115activity span span {
color:#ff7900;}
#yiv2855639115 #yiv2855639115activity span
.yiv2855639115underline {
text-decoration:underline;}
#yiv2855639115 .yiv2855639115attach {
clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}
#yiv2855639115 .yiv2855639115attach div a {
text-decoration:none;}
#yiv2855639115 .yiv2855639115attach img {
border:none;padding-right:5px;}
#yiv2855639115 .yiv2855639115attach label {
display:block;margin-bottom:5px;}
#yiv2855639115 .yiv2855639115attach label a {
text-decoration:none;}
#yiv2855639115 blockquote {
margin:0 0 0 4px;}
#yiv2855639115 .yiv2855639115bold {
font-family:Arial;font-size:13px;font-weight:700;}
#yiv2855639115 .yiv2855639115bold a {
text-decoration:none;}
#yiv2855639115 dd.yiv2855639115last p a {
font-family:Verdana;font-weight:700;}
#yiv2855639115 dd.yiv2855639115last p span {
margin-right:10px;font-family:Verdana;font-weight:700;}
#yiv2855639115 dd.yiv2855639115last p
span.yiv2855639115yshortcuts {
margin-right:0;}
#yiv2855639115 div.yiv2855639115attach-table div div a {
text-decoration:none;}
#yiv2855639115 div.yiv2855639115attach-table {
width:400px;}
#yiv2855639115 div.yiv2855639115file-title a, #yiv2855639115
div.yiv2855639115file-title a:active, #yiv2855639115
div.yiv2855639115file-title a:hover, #yiv2855639115
div.yiv2855639115file-title a:visited {
text-decoration:none;}
#yiv2855639115 div.yiv2855639115photo-title a,
#yiv2855639115 div.yiv2855639115photo-title a:active,
#yiv2855639115 div.yiv2855639115photo-title a:hover,
#yiv2855639115 div.yiv2855639115photo-title a:visited {
text-decoration:none;}
#yiv2855639115 div#yiv2855639115ygrp-mlmsg
#yiv2855639115ygrp-msg p a span.yiv2855639115yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}
#yiv2855639115 .yiv2855639115green {
color:#628c2a;}
#yiv2855639115 .yiv2855639115MsoNormal {
margin:0 0 0 0;}
#yiv2855639115 o {
font-size:0;}
#yiv2855639115 #yiv2855639115photos div {
float:left;width:72px;}
#yiv2855639115 #yiv2855639115photos div div {
border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}
#yiv2855639115 #yiv2855639115photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}
#yiv2855639115 #yiv2855639115reco-category {
font-size:77%;}
#yiv2855639115 #yiv2855639115reco-desc {
font-size:77%;}
#yiv2855639115 .yiv2855639115replbq {
margin:4px;}
#yiv2855639115 #yiv2855639115ygrp-actbar div a:first-child {
margin-right:2px;padding-right:5px;}
#yiv2855639115 #yiv2855639115ygrp-mlmsg {
font-size:13px;font-family:Arial, helvetica, clean,
sans-serif;}
#yiv2855639115 #yiv2855639115ygrp-mlmsg table {
font-size:inherit;font:100%;}
#yiv2855639115 #yiv2855639115ygrp-mlmsg select,
#yiv2855639115 input, #yiv2855639115 textarea {
font:99% Arial, Helvetica, clean, sans-serif;}
#yiv2855639115 #yiv2855639115ygrp-mlmsg pre, #yiv2855639115
code {
font:115% monospace;}
#yiv2855639115 #yiv2855639115ygrp-mlmsg * {
line-height:1.22em;}
#yiv2855639115 #yiv2855639115ygrp-mlmsg #yiv2855639115logo {
padding-bottom:10px;}
#yiv2855639115 #yiv2855639115ygrp-msg p a {
font-family:Verdana;}
#yiv2855639115 #yiv2855639115ygrp-msg
p#yiv2855639115attach-count span {
color:#1E66AE;font-weight:700;}
#yiv2855639115 #yiv2855639115ygrp-reco
#yiv2855639115reco-head {
color:#ff7900;font-weight:700;}
#yiv2855639115 #yiv2855639115ygrp-reco {
margin-bottom:20px;padding:0px;}
#yiv2855639115 #yiv2855639115ygrp-sponsor #yiv2855639115ov
li a {
font-size:130%;text-decoration:none;}
#yiv2855639115 #yiv2855639115ygrp-sponsor #yiv2855639115ov
li {
font-size:77%;list-style-type:square;padding:6px 0;}
#yiv2855639115 #yiv2855639115ygrp-sponsor #yiv2855639115ov
ul {
margin:0;padding:0 0 0 8px;}
#yiv2855639115 #yiv2855639115ygrp-text {
font-family:Georgia;}
#yiv2855639115 #yiv2855639115ygrp-text p {
margin:0 0 1em 0;}
#yiv2855639115 #yiv2855639115ygrp-text tt {
font-size:120%;}
#yiv2855639115 #yiv2855639115ygrp-vital ul li:last-child {
border-right:none !important;
}
#yiv2855639115
