----- 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
|
Please note that this group is for discussion between users only.
To get support from AmiBroker please send an e-mail directly to
SUPPORT {at} amibroker.com
For other support material please check also:
http://www.amibroker.com/support.html
SPONSORED LINKS
YAHOO! GROUPS LINKS