Delete Hundreds of stocks from my Data

[raymondo]

Downloaded AMEX which has many stocks below 5, which I want to delete all at once, rather than one at a time. AS of Nov 30. Maybe over several hundred.

Filter=Close>0;
AddTextColumn(FullName(), “Company Name”);

AddColumn( Close, “Close”,1.2 );// 1.2=two decimal places.

[Dionysos]

you could modify TJ's cleanup script, located here:

[beppe]

@raymondo, you can also use an exploration to list all the tickers that you want to drop from the database.

From your post, I think that you'll need to define your filter criteria based on the close price below a certain amount, maybe on light volume, and if you have already classified your tickers in different markets/groups or watchlists you can use also that criteria to refine the resulting list (AMEX only).

A) Run the exploration (on all the symbols, 1 recent day).

B) Double check the exploration resulting list of tickers. If everything is as expected go ahead. Otherwise, revise the filter logic until you are satisfied.

C) Once done, click with the right mouse button on the exploration results list: a popup menu will show you a list of menu items:

Select the "Replace watchlist with the results...".

D) Select a destination watchlist: choose one of your empty watchlists (otherwise you'll overwrite the existing content).

E) Then go to the watchlists list and select the one that now contains all the tickers you want to drop. (Here if needed, you can still decide to remove some symbols from the watchlist, using the right mouse click contextual menu choice "Watch List -> Remove selected symbol(s)").

F) Select with the mouse the first symbol in the actual watchlist. Then press the Shift key and also the End key to select ALL the symbols in the watchlist (or use Shift and the Arrow Down key until the end of the watchlist)

G) When all the symbols are selected (highlighted), press the right mouse button to open the contextual menu. From this menu select the "Delete" item.

H) A confirmation dialog will ask you what you want to do. You should select the option to perform the operation on: "Multiple Symbols Selected".

I) A further confirmation dialog (a very wide one... here I posted one for few symbols) will ask you, for the last time, to confirm your choice "Are you sure to delete....".

Select: "Yes, Delete Permanently"

J) DONE

[raymondo]

Thanks BEPPE
Worked great. I knew most of the routine, however at G, that what I didn’t know.
I was at 5622 stocks and after the delete went to 4995, 627 deleted.
Now will work on volume and add it to my routine.

I started by adding Russell3000,2000,1000,Nas100,Dow30 and a bunch of other items,
Now I release it was much too many, I have been using AMI since 2005 using IB and trading Futures, then had a health problem and stopped everything, now after 12 years later decided to start day trading again, now 87, going over a lot of routines I developed earlier, Having fun to keep my mind active.
Thanks again.

[beppe]

@raymondo I’m glad that my explanation was helpful. If you think it was the proper way to do it, you can mark my post as a “Solution”, so other forum users will immediately see it.

87 and going: congratulations! Since you are a long-standing user of AmiBroker, please, don’t forget, from time to time, to share with us some of your best routines!

[portfoliobuilder]

I started by adding Russell3000,2000,1000,Nas100,Dow30

@raymondo I just wanted to remind you that if you have a proper data source and your watchlist are accurately organized that the Russell 3000 includes all members of those other Indexes that you listed. i.e. All members of the Russell 2000, 1000, Nas 100, and Dow 30 (as well as the S&P 500) should be members of the Russell 3000.

If you are looking for even more stocks you could consider the Wilshire 5000 Index constituents.

Good luck!

[raymondo]

Thanks Larry
I also added the AMEX where all the low value close came from. I had forgotten about the Wilshire 5000.
I had a lot info on AMI in 2005 , just getting started again, something to do in my last years.
Any idea where I can get a list W5000 that can be imported ?
Found http://financemainpage.com/Listing_of_All_Wilshire_5000_Stocks.html will take some effort on Excel to sort out.

[aron]

Using JScript:

var AB = new ActiveXObject( "Broker.Application" );  
var Stocks = AB.Stocks; 
var stqty = Stocks.Count;

var price = 5;  // stocks with last close below this price will be deleted

for ( i = 0 ; i < stqty ; i ++ )
{
	var Stock = Stocks.Item (i); 
	var Quotations = Stock.Quotations
	var lastquote =  Quotations.item ( Quotations.Count-1);
	var lastclose = lastquote.close;

	if ( lastclose < price )
	{
		Stocks.Remove(Stock);
	}
}

AB.RefreshAll(); 
WScript.Echo ( "Done." ); 

[portfoliobuilder]

@raymondo here is a site with an Excel downloadable file.

WILSHIRE 5000 Stock Tickers List

Interestingly over at the Wilshire site (also have a pdf list) they discuss how they are down to around 3600 stocks in the Index, so not that different from the Russell 3000,
https://wilshire.com/indexes/wilshire-5000-family/wilshire-5000-total-market-index

[Dionysos]

Here is the modified cleanup script. It will delete data without warning. Handle with care

/* Will delete by default
** AmiBroker/Win32 scripting Example
**
** File:		Cleanup.js
** Created:		Tomasz Janeczko, December 2th, 2000, updated Matthias 04/2015
** Purpose:		Cleanup the database from not traded stocks
** Language: 	JScript (Windows Scripting Host)
*/

/* detection threshold (in days)  */
var Threshold = 30; // one month for example 
/* by default do not delete */
var DeleteByDefault = true;
/* ask the user for the decision */
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();
}
oAB.RefreshAll();
WScript.Echo("Cleanup script finished" );

[raymondo]

Thanks Found it, will look at is soon, guess not much different from the R3000

[raymondo]

I tried it , many errors, I am not a programmer, so could not fix it. Don’t know Jscript.

[aron]

Hello @raymondo,

1. In AB open the database you want to cleanup.
2. Open Notepad
3. Copy the code into a new document
4. Save as type "All Files" with a ".js" extension

5. If you right- click on the file you just saved, selecting Properties you see that the file opens with Windows Based Scripting Host, you can execute the script by double-clicking on the file.

6. alternatively you can use command line to execute :

[raymondo]

Thanks for the method of deleting tickers. I will try it out next time I screw up my data, Ok at the moment, now have 4978 after deleting stocks under 4. I may down load the W5000, and If I screw it up, will try your method.
Thanks for the complete reply.