Re: [amibroker] 47 Missing QuotesPlus Index Symbols and Auditing QP Data, AmiBroker Email List Archive

Steve,

Please take a look my response to Tomasz.

Regarding, “…it doesn't hurt anything to have them there…”, I agree with you IF you are aware of the data problems. However I’m concerned about data problems that you are NOT aware of. Also, I think we need to push QP to clean up the source data.

In any case, we need a solution for this that is more widely and easily usable for all users. I’m open to anything that works.

Currently I filter by price and volume for my list of “Tradable Symbols”. Question: Why filter for “last market date”?

Best regards,

Dan.

From: amibroker@xxxxxxxxxxxxxxx [mailto:amibroker@xxxxxxxxxxxxxxx] On Behalf Of Steve Dugas
Sent: Thursday, September 22, 2005 10:54 PM
To: amibroker@xxxxxxxxxxxxxxx
Subject: Re: [amibroker] 47 Missing QuotesPlus Index Symbols and Auditing QP Data

Hi Dan,

The QP plugin obviously does not work as expected. It is not TJ's fault - QP's data is just too screwed up. That's why I was pushing for the plugin to check all tickers against the last market date - I can't think of any other way to get around the messed up data classifications. But I guess TJ decided against this method. Now, I have gone back to just downloading *all* tickers. Except for the mess, it doesn't hurt anything to have them there, and my initial scan ( to filter out my "tradable" universe ) has checks for price, volume *and* last market date. All my other work is done against this list, so I don't think the obsolete tickers even slows it down. And all indices, etc. are there. I ( and I know others too ) have sent lists to QP several times, but the data never gets corrected. Dont hold your breath 8 - )

Steve

----- Original Message -----

From: Dan Clark

To: amibroker@xxxxxxxxxxxxxxx

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