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