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