[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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



PureBytes Links

Trading Reference Links

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

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
Investment management software Real estate investment software Investment property software
Software support Real estate investment analysis software Investment software


YAHOO! GROUPS LINKS