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

Patrick

I got an e-mail from Gary at Quotes Plus. He said that there was an error in their processing that caused the invalid status on some index symbols. He said that he will be correcting the IssueStatus this weekend.

It would have been better to not have the issue in the first place, but at least they are fixing the problem. That’s good.

I like QP. They have several features (like good industry/sector mapping and lots of Extra Data) that fit my trading plan well. I’m willing to live with the occasional data issue as long as we can detect them and QP resolves them.

IMO, good auditing tools and regular auditing of the QP data is the best way to ensure that the data stays clean.

Best regards,

Dan.

From: amibroker@xxxxxxxxxxxxxxx [mailto:amibroker@xxxxxxxxxxxxxxx] On Behalf Of NW Trader
Sent: Friday, September 23, 2005 8:17 PM
To: amibroker@xxxxxxxxxxxxxxx
Subject: Re: [amibroker] 47 Missing QuotesPlus Index Symbols and Auditing QP Data

Hi Steve,

Your wish is (was) my command (anticipated your question by a couple minutes). <G> But I again point out, it's not my code, only my tweaks to Tomasz's script. Did you get it as an attachment to the post just before yours (TA 4 Charts is another nom de net <VBG>). If not I can post it in a message or send it to you directly if you wish.

Still around, just been very busy and didn't have much of value to contribute by the time I was reading things (generally well after original posting). Today I happened to tune in just as the QP discussion hit a point I could contribute.

I refresh/retrieve/update my AB database every weekend at a minimum to keep tickers and changes up to date, hence the desire to purge all those dead tickers without having to click on each one. I once tried to do it manually and it was a pain. I've considered building a list of the known dead issues and a routine that would then purge them, but the EZ-Cleanup is too easy. On the slower of my 2 machines with QP it takes about 3 to 5 minutes to run. I usually go get a cup of coffee or work on one of the other computers while it does it's thing. I think there were something like 1800 stock symbols to delete running it on last weekend's database (I don't download mutual funds or commodities so I don't know about them). Even so, the series LC02K thru LF9ZK plus a few others don't get deleted because there is an unchanging value carried in each day. I need to add a routine to check for that somehow. Presently I manually delete the blocks that I know of if I feel energetic.

I've called, bargained, begged, and bitched to QP about this for several years. I gave up about 6 months ago when Jeanne left and her replacement kept insisting that he couldn't see any problems. I referred him to the Van Kempen series of tickers (NAVK18 or later numbered series), the Lehman Emerging Markets group (LA7TK et seq.), or the tickers in the LE10K thru LE9ZK series (a variety of different indexes supposedly), or the IMC43 to IMIT62 series of tickers as well as a bunch of others and his response was (almost verbatim) "Oh, well yeah but they won't show up in a scan." I said that I don't use their scan engine any more, I use AB, that QP is only a data feed and they get most certainly do get imported into AB because they are in the database taking up space. He assured me that there were no plans to clean them up because they were invisible to QP users. I considered not renewing my subscription a couple months ago when it was up because I also use TC2005 as a data source (which does a better job at keeping it's database clean but has other issues), but in the end signed up for another 2 years and crossed my fingers. While we little guys can complain, I think it would either take a mass emailing by a lot of us or Tomasz getting on their case or both to see any change. Gary used to be quite responsive, but ......

Peace and Justice --- Patrick

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

From: Steve Dugas

To: amibroker@xxxxxxxxxxxxxxx

Sent: Friday, September 23, 2005 2:27 PM

Subject: Re: [amibroker] 47 Missing QuotesPlus Index Symbols and Auditing QP Data

Dan,

Another problem, as I see it, is that we will need to update our database periodically for name changes, new issues, etc. Because so many tickers are miscategorized, I can't help feeling that I need to download *all* tickers when I do this each week, otherwise who knows what will *not* be retrieved because, for example, maybe they are coded as non-trading when they are really trading? So for me, I think the code you mentioned below would also have to be run every week after updating, since the update process will just retrieve all the bad tickers I deleted the week before. I find it easier to just leave the tickers there and filter them out.

But....     8 - )

Having said that, I would be interested in having a look at Patrick's code if I could. I have not seen him posting here lately - do you know if he is still around, or if he would mind you posting his code? Thank you!

Steve

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

From: dan_public@xxxxxxxxxxx

To: amibroker@xxxxxxxxxxxxxxx

Sent: Friday, September 23, 2005 2:18 PM

Subject: Re: [amibroker] 47 Missing QuotesPlus Index Symbols and Auditing QP Data

Steve,

Hi. I understand now what you are doing with DateNum().

NWTrader developed some code that removes bad symbols from the QP database.   I used it for a while and it worked nicely. When TJ added the new code to filter out data, I though that I wouldn't need it anymore. Now, I'm afraid I'm wrong.

When I get home, I'll look at the code again. I think NWTraders approach is better. If we can identify and simply remove the useless symbols, and find workarounds for the good one with some bad flags, I think that's a better approach.

NWTrader's code is a menu option from the AB Tools menu.   Pretty much of a no brainer to run.   I'll look at it again tonight.

Best regards,

Dan.

-------------- Original message --------------

Hi Dan,

Why filter for last market date?

Well, I am not a developer, and not in the same league as you and TJ when it comes to writing my own solutions, so it looked to me like the only way I was going to get all the tickers I needed (indices, industry groups, etc ) was to just go ahead and retrieve *all* the tickers. Once I did that, my AB database had 3 kinds of tickers:

1. good tickers with current data ( I like these   8 - )

2. tickers with no data ( AB automatically filters these out )

3. tickers with data, but the data isn't current ( last date could be years ago, there are lots of them )

#3 was the problem, since I didn't want to waste my time testing hundreds or thousands of securities that no longer trade. Plus, I was actually about ready to try trading a couple of them, only to discover they didn't exist anymore! They were slowing down my testing, and I had to remember to always sort the results list by date so I could weed out the defunct tickers. So I just added a datenum() condition to my initial scan. Now, when I update the DB each week to get new issues, etc, I open the initial scan, change the date check to the last market day, and it only returns *current* tickers that pass my price/volume rules. It was the easiest way I could think of to eliminate these tickers. Maybe you guys will be able to come up with something better - good luck!

Steve

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

From: Dan Clark

To: amibroker@xxxxxxxxxxxxxxx

Sent: Friday, September 23, 2005 12:15 PM

Subject: RE: [amibroker] 47 Missing QuotesPlus Index Symbols and Auditing QP Data

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