[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,
 
I wish I were sufficiently versed to have written the original code, but the credit goes to Tomasz (as usual, <VBG>).  I believe it was originally provided with AB, and in the original may still be.  All I did was tweak it so that one didn't get repetitive stress injury constantly confirming the delete for each individual security. 
 
For anyone else that wants to use it, I've attached the code to this message as a TXT file (some ISPs block js scripts), to use it if they wish.  To do so, save it to the scripts folder in your AB directory with an extension of .js (i.e., replace txt with js).  It can then be added to the tools menu (Tools-->Customize-->New and specify the file and folder and give it a name like EZ_Cleanup).  For those who want to make changes, there are comments at points where changes can be made.   I found that there are massive numbers of dead tickers, I didn't need them so I just deleted them. 
 
BTW, if you are running one of my "tradable stock" explorations, they use an average volume rather than a last day volume threshold parameter which can help avoid the one day of missing data problem.  But you are right, we need to get QP to realize that there is a problem with their data, not try to figure out band aide solutions.
 
Got to run (well hobble, as I'm on crutches at the moment). 
 
Peace and Justice   ---   Patrick
----- Original Message -----
Sent: Friday, September 23, 2005 11:18 AM
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
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 



/*
** AmiBroker/Win32 scripting Example
**
** File:		Cleanup.js
** Created:		Tomasz Janeczko, December 2th, 2000  
** Modified:		Patrick Hargus, Oct 22, 2003 
** Purpose:		Cleanup the database from not traded stocks
** Language: 		JScript (Windows Scripting Host)
*/

/* detection threshold (in days)  */
	var Threshold = 20; // one month for example 
/* by default do not delete = FALSE , EDITED TO TRUE so automaticly deletes*/
	var DeleteByDefault = true;
/* ask the user for the decision - default is true  EDITED TO FALSE so no intervention required*/
	var AskUser = false                        
/* a timeout to wait until default route (no deletion) is taken */
	var Timeout = 5;

	var oAB = new ActiveXObject("Broker.Application");
	var fso = new ActiveXObject("Scripting.FileSystemObject");
	var Shell = new ActiveXObject("WScript.Shell");


	var oStocks = oAB.Stocks;

	var MiliSecInDay = 24 * 60 * 60 * 1000;

	var Continue = true;

	var StockQty = oStocks.Count;

	var oStocksToDelete = new Array;
	var oStocksNotTraded = new Array;

if( ! AskUser ) WScript.Echo("Cleanup script started" );

for( i = 0; i < StockQty && Continue; i++ )
{
	oStock = oStocks( i );

	var Qty = oStock.Quotations.Count; 

	var response = 0;

	if( Qty > 0 )
	{
		oQuote = oStock.Quotations( Qty - 1 );

		var oDate = new Date( oQuote.Date );

		var Today = new Date();

		DaysNotTraded = Math.floor( ( Today - oDate )/MiliSecInDay );

		if( DaysNotTraded > Threshold )
		{
			if( AskUser ) response = Shell.popup( oStock.Ticker + " is not traded since " + oDate.toLocaleString() + " (" + DaysNotTraded + " days).\nDo you wish to delete it?\n(Press Cancel to end the process)", Timeout, "Confirm delete", 3 + 256 );
			else          response = -1; /* default */
		}
	}
	else
	{
		if( AskUser ) response = Shell.popup( oStock.Ticker + " has no quotes. Do you wish to delete it?",  Timeout, "Confirm delete", 3 + 256 );
		else          response = -1; /* default */
	}

	/* change default route if needed */
	if( response == -1 && DeleteByDefault ) response = 6;

	switch( response )
	{
		case -1:/* Timeout - fallback to no */
		case 7: /* No */
				oStocksNotTraded[ oStocksNotTraded.length ] = oStock.Ticker;
				break;
		case 6: /* Yes */
				oStocksToDelete[ oStocksToDelete.length ] = oStock.Ticker;
				break;
		case 2: /* Cancel */
				Continue = false;
				break;
		default: break;
	}
}

if( oStocksToDelete.length > 0 && Shell.popup( "You are now about to delete " + oStocksToDelete.length + " stock(s).\nDo you wish to proceed?" , 0, "Confirm delete", 4 + 256 ) == 6 )
{
	for( i = 0; i < oStocksToDelete.length; i++ )
	{
		oStocks.Remove( oStocksToDelete[ i ]  );
	}

	oAB.RefreshAll();
}

if( oStocksNotTraded.length > 0 && Shell.popup( "There are " + oStocksNotTraded.length + " not traded stock(s) detected but not deleted by your choice.\nDo you wish to save their tickers to \"nottraded.txt\" file?" , 0, "Confirm save", 4 + 256 ) == 6 )
{
	f = fso.OpenTextFile( "nottraded.txt", 2, true );	

	for( i = 0; i < oStocksNotTraded.length; i++ )
	{
		f.WriteLine( oStocksNotTraded[ i ]  );
	}

	f.Close();
}

WScript.Echo("Cleanup script finished" );