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 |