----- Original Message
-----
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 |