Bob,
I wish I could agree with you. I got
the same results as Patrick.
I deleted the symbols below and then
retrieved Common Stocks, Indices and Sector/Industry groups from QP. The bad
tickers reappeared. The problem is that the DO have quotes, but the
quotes are stale.
I ran a scan to find all symbols whose
prices have not changed in the last year. I got the tickers below.
Regards,
Dan.
BMY P
CTN K
GAB RW
HBX
HNZ P
IKAN
LC02K
LE10K
LE11K
LE12K
LE1YK
LE1ZK
LE2 K
LE2AK
LE2BK
LE2CK
LE2DK
LE2EK
LE2FK
LE2GK
LE2HK
LE2IK
LE2JK
LE2KK
LE2LK
LE2MK
LE2NK
LE2OK
LE2PK
LE2QK
LE2RK
LE2SK
LE2TK
LE2UK
LE2WK
LE2YK
LE2ZK
LE3 K
LE3AK
LE3BK
LE3CK
LE3DK
LE3EK
LE3FK
LE3GK
LE3HK
LE3IK
LE8QK
LE8RK
LE8SK
LE8TK
LE8UK
LE8VK
LE8WK
LE8XK
LE8YK
LE8ZK
LE9 K
LE9AK
LE9BK
LE9CK
LE9DK
LE9EK
LE9FK
LE9GK
LE9HK
LE9IK
LE9JK
LE9KK
LE9LK
LE9MK
LE9NK
LE9OK
LE9PK
LE9QK
LE9RK
LE9SK
LE9TK
LE9UK
LE9VK
LE9WK
LE9XK
LE9YK
LE9ZK
LF5TK
LF5UK
LF5VK
LF5WK
LF5XK
LF5YK
LF5ZK
LF8AK
LF8BK
LF8CK
LF8DK
LF8EK
LF8FK
LF9RK
LF9SK
LF9TK
LF9UK
LF9VK
LF9WK
LF9XK
LF9YK
LF9ZK
NBI K
PLR
From: amibroker@xxxxxxxxxxxxxxx [mailto:amibroker@xxxxxxxxxxxxxxx] On Behalf Of Bob Jagow
Sent: Sunday, September 25, 2005
10:15 AM
To: amibroker@xxxxxxxxxxxxxxx
Subject: RE: [amibroker] 47
Missing QuotesPlus Index Symbols and Auditing QP Data
Patrick,
Looks like Gary purged that series of tickers.
I deleted them before retrieval and
they didn’t reappear.
Bob
-----Original
Message-----
From: amibroker@xxxxxxxxxxxxxxx
[mailto:amibroker@xxxxxxxxxxxxxxx]On Behalf Of NW Trader
Sent: Saturday, September 24, 2005
10:54 PM
To: amibroker@xxxxxxxxxxxxxxx
Subject: Re: [amibroker] 47
Missing QuotesPlus Index Symbols and Auditing QP Data
Hi Dan,
All symbols
may have quotes, but they aren't necessarily good. And the
EZ-Cleanup script doesn't pick up everything unfortunately. See if you
have a whole series of bad tickers (under the Market-- Undefined listing)
starting with LC02K thru LF9ZK. There are others too like CYX K
and NBI K and some I don't recall just off the top of my head.
I just downloaded the latest from QP, retrieved to AB (using latest dll)
and ran EZ-Cleanup (got 1 ticker to delete), however the symbols I
just referenced are present and 'dead' (unchanging OHLC values and 0
volume for years) . As you are in touch with Gary, perhaps you can get him to delete these
as well (or set their issue status to 1)? Presently I delete them
manually, and just don't bother with the others that are scattered
around.
Still and
all, it is progress so I am thankful for your efforts and pleased that Gary is working on
it. The last response I got from QP was so unconcerned (and
discouraging), I resolved to stop wasting time on it. But then, I'm only
a trader, not a DataBase Guru <VBG>. Thanks again, and thank Gary too.
Peace and Justice
--- Patrick
----- Original Message
-----
To: amibroker@xxxxxxxxxxxxxxx
Sent: Saturday, September 24, 2005 10:03 PM
Subject: RE: [amibroker] 47 Missing QuotesPlus Index Symbols and Auditing
QP Data
Hi,
Here’s
a status on the Index Symbol issue…
I got an
e-mail from Gary
at Quotes Plus. He said that he believed the index data issues had been
resolved and that symbols with no quotes had been removed from the
database.
I just
ran a check to confirm this.
The news
is very good, but not quite perfect. 43 of 47 index symbols have been cleaned
up and now load into AmiBroker. “!AM-T”,
“!AM-UV”, “!NY-T”, and “!NY-UV” still
need to be cleaned up. (I sent him an e-mail about this.)
Also, I
just ran the _javascript_ EZ-Cleanup script to check for any Common Stocks and
Index QuotesPlus symbols in the AmiBroker database with no data. ALL symbols had quotes!!!
Frankly,
I’m pleased with the response.
Regards,
Dan.
From: Dan Clark
[mailto:dan_public@xxxxxxxxxxx]
Sent: Saturday, September 24, 2005
9:55 AM
To: 'amibroker@xxxxxxxxxxxxxxx'
Subject: RE: [amibroker] 47
Missing QuotesPlus Index Symbols and Auditing QP Data
Patrick
I got an
e-mail from Gary
at Quotes Plus. He said that there was an error in their processing that
caused the invalid status on some index symbols. He said that he
will be correcting the IssueStatus this weekend.
It would
have been better to not have the issue in the first place, but at least they
are fixing the problem. That’s good.
I like
QP. They have several features (like good industry/sector mapping
and lots of Extra Data) that fit my trading plan well. I’m willing
to live with the occasional data issue as long as we can detect them and QP
resolves them.
IMO,
good auditing tools and regular auditing of the QP data is the best way to
ensure that the data stays clean.
Best
regards,
Dan.
From: amibroker@xxxxxxxxxxxxxxx
[mailto:amibroker@xxxxxxxxxxxxxxx] On Behalf Of NW Trader
Sent: Friday, September 23, 2005
8:17 PM
To: amibroker@xxxxxxxxxxxxxxx
Subject: Re: [amibroker] 47
Missing QuotesPlus Index Symbols and Auditing QP Data
Hi Steve,
Your wish is
(was) my command (anticipated your question by a couple minutes).
<G> But I again point out, it's not my code, only my tweaks to
Tomasz's script. Did you get it as an attachment to the post just before
yours (TA 4 Charts is another nom de
net <VBG>). If not I can post it in a message
or send it to you directly if you wish.
Still around,
just been very busy and didn't have much of value to contribute by the time I
was reading things (generally well after original posting). Today I
happened to tune in just as the QP discussion hit a point I could
contribute.
I
refresh/retrieve/update my AB database every weekend at a minimum to keep
tickers and changes up to date, hence the desire to purge all those dead
tickers without having to click on each one. I once tried to do it
manually and it was a pain. I've considered building a list of the known
dead issues and a routine that would then purge them, but the EZ-Cleanup is too
easy. On the slower of my 2 machines with QP it takes about 3 to
5 minutes to run. I usually go get a cup of coffee or work
on one of the other computers while it does it's thing. I
think there were something like 1800 stock symbols to delete running it on last
weekend's database (I don't download mutual funds or commodities so I don't
know about them). Even so, the series LC02K thru LF9ZK plus a few others
don't get deleted because there is an unchanging value carried in each
day. I need to add a routine to check for that somehow. Presently I
manually delete the blocks that I know of if I feel energetic.
I've called,
bargained, begged, and bitched to QP about this for several years. I
gave up about 6 months ago when Jeanne left and her replacement kept insisting
that he couldn't see any problems. I referred him to the Van
Kempen series of tickers (NAVK18 or later numbered series), the
Lehman Emerging Markets group (LA7TK et seq.), or the tickers in the
LE10K thru LE9ZK series (a variety of different indexes supposedly), or
the IMC43 to IMIT62 series of tickers as well as a bunch of others and his
response was (almost verbatim) "Oh, well yeah but they won't show up in a
scan." I said that I don't use their scan engine any more, I use AB,
that QP is only a data feed and they get most certainly do get imported
into AB because they are in the database taking up space. He assured me
that there were no plans to clean them up because they were invisible to QP
users. I considered not renewing my subscription a couple months ago when
it was up because I also use TC2005 as a data source (which does a better job
at keeping it's database clean but has other issues), but in the end signed up
for another 2 years and crossed my fingers. While we little guys can
complain, I think it would either take a mass emailing by a lot of us or Tomasz
getting on their case or both to see any change. Gary used to be quite responsive, but ......
Peace and
Justice --- Patrick
-----
Original Message -----
To: amibroker@xxxxxxxxxxxxxxx
Sent: Friday, September 23,
2005 2:27 PM
Subject: Re:
[amibroker] 47 Missing QuotesPlus Index Symbols and Auditing QP Data
Dan,
Another
problem, as I see it, is that we will need to update our database periodically
for name changes, new issues, etc. Because so many tickers
are miscategorized, I can't help feeling that I need to download *all*
tickers when I do this each week, otherwise who knows what will *not*
be retrieved because, for example, maybe they are coded as
non-trading when they are really trading? So for me, I think the code you
mentioned below would also have to be run every week after updating, since the
update process will just retrieve all the bad tickers I deleted the week
before. I find it easier to just leave the tickers there and filter them out.
But....
8 - )
Having
said that, I would be interested in having a look at Patrick's code if I
could. I have not seen him posting here lately - do you know if he is still
around, or if he would mind you posting his code? Thank you!
Steve
-----
Original Message -----
To: amibroker@xxxxxxxxxxxxxxx
Sent: Friday, September 23,
2005 2:18 PM
Subject: Re: [amibroker]
47 Missing QuotesPlus Index Symbols and Auditing QP Data
Steve,
Hi. I
understand now what you are doing with DateNum().
NWTrader
developed some code that removes bad symbols from the QP database. I
used it for a while and it worked nicely. When TJ added the new code to
filter out data, I though that I wouldn't need it anymore. Now, I'm
afraid I'm wrong.
When I get
home, I'll look at the code again. I think NWTraders approach is
better. If we can identify and simply remove the useless symbols, and
find workarounds for the good one with some bad flags, I think that's a better
approach.
NWTrader's
code is a menu option from the AB Tools menu. Pretty much of a no
brainer to run. I'll look at it again tonight.
Best regards,
Dan.
--------------
Original message --------------
Hi Dan,
Why
filter for last market date?
Well, I
am not a developer, and not in the same league as you and TJ when it comes to
writing my own solutions, so it looked to me like the only way I was going to
get all the tickers I needed (indices, industry groups, etc ) was to just
go ahead and retrieve *all* the tickers. Once I did that, my AB database
had 3 kinds of tickers:
1. good
tickers with current data ( I like these 8 - )
2.
tickers with no data ( AB automatically filters these out )
3. tickers
with data, but the data isn't current ( last date could be years ago, there are
lots of them )
#3 was
the problem, since I didn't want to waste my time testing hundreds or
thousands of securities that no longer trade. Plus, I was actually about ready
to try trading a couple of them, only to discover they didn't exist anymore!
They were slowing down my testing, and I had to remember to always sort the
results list by date so I could weed out the defunct tickers. So I just added a
datenum() condition to my initial scan. Now, when I update the DB each week to
get new issues, etc, I open the initial scan, change the date check to the
last market day, and it only returns *current* tickers that pass my
price/volume rules. It was the easiest way I could think of to eliminate these
tickers. Maybe you guys will be able to come up with something better
- good luck!
Steve
-----
Original Message -----
To: amibroker@xxxxxxxxxxxxxxx
Sent: Friday, September 23,
2005 12:15 PM
Subject: RE:
[amibroker] 47 Missing QuotesPlus Index Symbols and Auditing QP Data
Steve,
Please
take a look my response to Tomasz.
Regarding,
“…it doesn't hurt anything to have them there…”, I
agree with you IF you are aware of the data problems. However
I’m concerned about data problems that you are NOT aware of. Also,
I think we need to push QP to clean up the source data.
In any
case, we need a solution for this that is more widely and easily usable for all
users. I’m open to anything that works.
Currently
I filter by price and volume for my list of “Tradable
Symbols”. Question: Why filter for “last market
date”?
Best
regards,
Dan.
From: amibroker@xxxxxxxxxxxxxxx [mailto:amibroker@xxxxxxxxxxxxxxx] On Behalf Of Steve Dugas
Sent: Thursday, September 22, 2005
10:54 PM
To: amibroker@xxxxxxxxxxxxxxx
Subject: Re: [amibroker] 47
Missing QuotesPlus Index Symbols and Auditing QP Data
Hi Dan,
The QP
plugin obviously does not work as expected. It is not TJ's fault - QP's data is
just too screwed up. That's why I was pushing for the plugin to check all
tickers against the last market date - I can't think of any other way to get
around the messed up data classifications. But I guess TJ decided against this
method. Now, I have gone back to just downloading *all* tickers. Except for the
mess, it doesn't hurt anything to have them there, and my initial scan (
to filter out my "tradable" universe ) has checks for price, volume
*and* last market date. All my other work is done against this list, so I
don't think the obsolete tickers even slows it down. And all indices, etc.
are there. I ( and I know others too ) have sent lists to QP several
times, but the data never gets corrected. Dont hold your
breath 8 - )
Steve
-----
Original Message -----
To: amibroker@xxxxxxxxxxxxxxx
Sent: Thursday, September 22,
2005 10:51 PM
Subject:
[amibroker] 47 Missing QuotesPlus Index Symbols and Auditing QP Data
Hi,
For Quotes Plus users (like me) - there are data
issues. I learned this when I tried to plot the “!NYA”
(NYSE Composite) symbol. It was missing in AB! After
discussing this with Tomasz, it turns out that the “!NYA”
IssueStatus is not valid and is therefore being excluded from AB!
Below is a list of 47 QP3 Index symbols that appear to be corrupt
and cannot be loaded into AmiBroker. These include major indices
like the NYSE Composite (“!NYA”), many iShares indices and several
HOLDRS indices.
I strongly urge QuotesPlus data users to audit AmiBroker versus
QuotesPlus data to ensure that your data is correct. There are multiple
methods that can be used, but here’s the method that I used:
1. Using Quotes Plus SPP, retrieve and save all symbols listed as
“Indexes”.
2. Open an Excel workbook and load the symbols. They should be
in column “A”, with the first symbol in Column A2.
3. Using AB, scan for all symbols all symbols that were in the group
“Index”. Here’s the Exploration:
if(GroupID(1)== "Index")
Buy = 1;
else
Buy = 0;
Filter=Buy;
SetOption("NoDefaultColumns",
True );
AddTextColumn(Name(), "Symbol");
AddTextColumn(FullName(), "Company");
4. Select and copy the results of the scan.
5. Paste into symbols into the open Excel workbook in Column
“C” so that the first symbol is in Column “C2”.
6. Paste this formula in Column “B2” (between the two
lists of symbols:
=IF(NOT(ISERROR(VLOOKUP(A2,$C$2:$C$228,1,FALSE))),
"", A2)”
Ensure that the lookup range (“$C$2:$C$228” in the
example) matches the entire range of AB symbols. For example, it you have
240 symbols, the range would be something like “$C$2:$C$242”.
7. Copy the formula from B2 to the bottom of range of symbols in
column A. I.e., just ensure that all of the QuotesPlus symbols are
referenced.
8. What you should end up with is a list of symbols in the
“B” column which are in SPP, but NOT in AB.
9. Copy the “B” column and pasted the VALUES only (Edit à Paste
Special à Values) to a convenient blank worksheet.
10. Sort
this list of symbols. This gives you a nice ordered list of symbols.
11. Select
the ordered list of symbols and paste transpose (Edit à Paste
Special à Transpose) the symbols so that they are in a single row.
12. Assuming
the row of symbols starts in cell “A1”, type the formula
=A1 & "
,"
13. The
result is a comma-delimited row of symbols with suitable for adding to AB.
14. Copy
the cells containing the comma-delimited symbols.
15. In AB,
select Symbol à New.
16. In the
text box in the “Enter a new symbol name” paste your
comma-delimited list of symbols. Then click OK.
17. The
symbols have now been added to AB. However, they will NOT have a
company name. I.e., FullName() will return blank. (This is useful.)
18. Run the
following Exploration:
if(FullName()==
"")
Buy = 1;
else
Buy = 0;
IssueType = GetExtraData("IssueType");
IssueStatus = GetExtraData("IssueStatus");
Filter=Buy;
SetOption("NoDefaultColumns",
True );
AddTextColumn(Name(),
"Symbol");
AddTextColumn(FullName(),
"Company");
AddTextColumn(IssueType(),
"IssueType");
AddTextColumn(FullName(),
"IssueStatus");
I found
the data issues in Index symbols. There are probably other bad symbols
that we are not aware of. Send
your list of bad symbols to QuotesPlus and copy AmiBroker support.
This should help get the data cleaned up.
Regards,
Dan.
|
Symbol
|
Company
|
IssueType
|
IssueStatus
|
|
!AM-T
|
|
A
|
|
|
!AM-UV
|
|
A
|
|
|
!BUX
|
|
A
|
N
|
|
!EMX
|
|
A
|
N
|
|
!EUX
|
|
A
|
P
|
|
!FFX
|
|
A
|
C
|
|
!GLI
|
|
A
|
P
|
|
!IPH
|
|
A
|
P
|
|
!IXBT
|
|
A
|
C
|
|
!IZN
|
|
A
|
N
|
|
!KGI
|
|
A
|
N
|
|
!NAG
|
|
A
|
N
|
|
!NBF
|
|
A
|
N
|
|
!NHG
|
|
A
|
N
|
|
!NIJ
|
|
A
|
D
|
|
!NIR
|
|
A
|
N
|
|
!NJB
|
|
A
|
N
|
|
!NJG
|
|
A
|
N
|
|
!NJH
|
|
A
|
N
|
|
!NJM
|
|
A
|
N
|
|
!NJU
|
|
A
|
N
|
|
!NJW
|
|
A
|
N
|
|
!NJZ
|
|
A
|
N
|
|
!NLA
|
|
A
|
N
|
|
!NLB
|
|
A
|
N
|
|
!NLE
|
|
A
|
N
|
|
!NLF
|
|
A
|
N
|
|
!NLL
|
|
A
|
N
|
|
!NLR
|
|
A
|
N
|
|
!NLU
|
|
A
|
N
|
|
!NME
|
|
A
|
N
|
|
!NMJ
|
|
A
|
N
|
|
!NMV
|
|
A
|
N
|
|
!NNV
|
|
A
|
N
|
|
!NY-AV
|
|
A
|
C
|
|
!NY-DV
|
|
A
|
C
|
|
!NY-T
|
|
A
|
|
|
!NY-UV
|
|
A
|
|
|
!NYA
|
|
A
|
C
|
|
!TOP
|
|
A
|
N
|
|
!VIO
|
|
A
|
N
|
|
!XAH
|
|
A
|
N
|
|
!XEU
|
|
A
|
P
|
|
!XRH
|
|
A
|
P
|
|
!XSH
|
|
A
|
P
|
|
!XUH
|
|
A
|
P
|
|
!YIH
|
|
A
|
N
|