Using JavaScript to count/loop/read/write symbols in a watchlist - OLE

How to go with this?

I went through your link but unable to understand the bit manipulation for getting hold of a index of a watchlist or a watchlist.

Goal is to able to remove quotes from a specific date to another for certain number of symbols in a watchlist.

Appreciate your kind help!

Use thise one as starting point. And this one showing how to iterate watchlists.

1 Like

Watchlists are plain text files in the Watchlists subfolder of the database. You don't need any OLE to access files.

1 Like

Thank you for replying,

What is meant by ( Stk.WatchListBits & ( 1 << iWatchList ) ) ?

@Lennon, essentially it checks that the currently selected stock (Stk) is INCLUDED in the watchlist whose number is defined at the top of the code sample.

This code line operates on a 32 bits number (the Stk property WatchListBits) using its binary representation as a sort of mask:

00000000000000000000000000000000 // Not present in any watchlists
11111111111111111111111111111111 // Present in all the 32 watchlists
01100110010001100101011001000110  // Present in some of the watchlists

Each of 32 bits could be 0 or 1 to indicate that a certain stock is included in the corresponding watchlist #0-31 (counting from the less significant bit at the right).

Using the & operator you are performing a 'Bit-wise "And" operation between the value at the left of the operator (Stk.WatchListBits) and the expression at the right of the operator ( 1 << iWatchList ).
This expression uses another Bit-wise operator: the << (Left Shift).

To understand what happens exactly in this operation you need to grasp what happens at bits level as explained here and additionally here.

Check also this other document titled Bitwise operators that seems to offer a pretty good explanation.

In any case, as stated by @Tomasz, you can safely ignore all of this and use the plain text files!

4 Likes

These bits are legacy fields provided only for backward-compatibility with some very old scripts and new code should not use them as they only can access first 64 lists, while now there is no limit on number of watch lists.

1 Like

Thanks a lot for explaining it vividly!

Arithmetic shift - got it!

Not so long back @irek_smaczny posted an email (link).

This code works, but it loops through all the Tickers in a database and removes quotes for x number of days delaying the execution. However, as my need is limited to watchlist(s) only, just added few lines to his original:

//Before running the script, backup of the database is necessary to avoid any unexpected dataloss
//Here OnField.tls is the name of the watchlist and OnField happens to be the name of the database too

filespec = "C:/Program Files/AmiBroker/OnField/WatchLists/OnField.tls";
DataDir = "C:\\Program Files\\AmiBroker\\OnField";

var fso, watchlist, s, ForReading;
ForReading = 1, ForWriting = 2, s = "";
fso = new ActiveXObject( "Scripting.FileSystemObject" );
watchlist = fso.OpenTextFile( filespec, ForReading, false );

var oAB = new ActiveXObject( "Broker.Application" );
oAB.LoadDatabase( DataDir );

Shell = new ActiveXObject("WScript.Shell");


var oStocks = oAB.Stocks;
var Qty = oStocks.Count;
var MiliSecInDay = 24 * 60 * 60 * 1000;

var DeleteFrom = new Date("April 11, 2018 00:00:00");
var DeleteTo = new Date("April 11, 2018 23:59:59");

//add a day to the date
DeleteFrom.setDate(DeleteFrom.getDate() + 1);
DeleteTo.setDate(DeleteTo.getDate() + 1);

// make date with time 00:00:00        
var DayDeleteFromNum = (Math.floor(DeleteFrom / MiliSecInDay)) * MiliSecInDay;
var DayDeleteToNum = (Math.floor(DeleteTo / MiliSecInDay)) * MiliSecInDay;

file = fso.OpenTextFile( "_remowe_xdays.log", ForWriting, true );
file.WriteLine( "Starting delete quotes from date:" + DeleteFrom);
file.WriteLine( "" );

while( !watchlist.AtEndOfStream )
{
	 s = watchlist.ReadLine( );
	 for( i = 0; i < Qty; i++ )
	 {
		 oStock = oStocks( i );
		 if( s == oStock.Ticker )
		 {
			 file.Write( i + ". " + oStock.Ticker + "=" );
			 
			 for ( j = oStock.Quotations.Count - 1; j >= 0; j-- )
			 {
				 tmpDateNum = oStock.Quotations( j ).Date ;
                 if (tmpDateNum >= DayDeleteFromNum) 
                 {
					 if (tmpDateNum <= DayDeleteToNum) 
					 {
						 oStock.Quotations.Remove(j);
					 }
                 }
                 else 
                 {
                     break;
                 }
			 }
			 file.WriteLine( "OK" ); 
		 }
	 }
}
oAB.RefreshAll();
oAB.SaveDatabase( );
watchlist.Close( );

Shell.Popup( "Done!", 1.5 );

Modifications are most welcome.

Thank you irek_smaczny and others for your kind and timely help!

You don't need script for that. Deletion of range of quotes is built-in feature, see my post in Export tick data

Thanks @Tomasz for your efforts to make AmiBroker work better day by day!

Missed that post!

It would be stupid of me to ask, isn't 6.26 a beta? I have the official 6.20 x64 release installed.

1 Like

Absolutely concur with Tomasz. In light of the new features, this script method is now obsolete but just going by the old school, made slight modifications as recently discovered Date.parse in JS.

Quotes_Remover.js

//Before running the script, backup of the database is necessary to avoid any unexpected dataloss
//Here OnField.tls is the name of the watchlist and OnField happens to be the name of the database too

filespec = "C:/Program Files/AmiBroker/OnField/WatchLists/OnField.tls";
DataDir = "C:\\Program Files\\AmiBroker\\OnField";

var fso, watchlist, s, ForReading;
ForReading = 1, ForWriting = 2, s = "";
fso = new ActiveXObject( "Scripting.FileSystemObject" );
watchlist = fso.OpenTextFile( filespec, ForReading, false );

var oAB = new ActiveXObject( "Broker.Application" );
oAB.LoadDatabase( DataDir );

Shell = new ActiveXObject("WScript.Shell");

var oStocks = oAB.Stocks;
var Qty = oStocks.Count;
//var MiliSecInDay = 24 * 60 * 60 * 1000;

var DeleteFrom = new Date("April 27, 2018 00:00:00");
var DeleteTo = new Date("April 27, 2018 23:59:59");

//add a day to the date
//DeleteFrom.setDate(DeleteFrom.getDate() + 1);
//DeleteTo.setDate(DeleteTo.getDate() + 1);

// make date with time 00:00:00
//var DayDeleteFromNum = (Math.floor(DeleteFrom / MiliSecInDay)) * MiliSecInDay;
//var DayDeleteToNum = (Math.floor(DeleteTo / MiliSecInDay)) * MiliSecInDay;

// make date as DateNum()
var DayDeleteFromNum = Date.parse(DeleteFrom);
var DayDeleteToNum = Date.parse(DeleteTo);

file = fso.OpenTextFile( "_remowe_xdays.log", ForWriting, true );
file.WriteLine( "Starting delete quotes from date:" + DeleteFrom);
file.WriteLine( "" );

while( !watchlist.AtEndOfStream )
{
	 s = watchlist.ReadLine( );
	 for( i = 0; i < Qty; i++ )
	 {
		 oStock = oStocks( i );
		 if( s == oStock.Ticker )
		 {
			 file.Write( i + ". " + oStock.Ticker + "=" );
			 
			 for ( j = oStock.Quotations.Count - 1; j >= 0; j-- )
			 {
				//tmpDateNum = (Math.floor(oStock.Quotations( j ).Date / MiliSecInDay)) * MiliSecInDay;
				tmpDateNum = Date.parse( oStock.Quotations( j ).Date );
                 		if(tmpDateNum >= DayDeleteFromNum)
                 		{
					 if (tmpDateNum <= DayDeleteToNum)
					 {
						 oStock.Quotations.Remove(j);
					 }
                 		}
                 		else
                 		{
                     			 break;
                 		}
			 }
			 file.WriteLine( "OK" );
		 }
	 }
}
watchlist.Close( );
oAB.RefreshAll();
oAB.SaveDatabase( );

Shell.Popup( "Done!", 1.5 );
2 Likes

Thank you for sharing this script. My data had a LOT of invalid quotes records interspersed between valid quotes for almost all symbols ~1300. The invalid quotes weren't in any date range and the OHLCV values for it were all 0 which were causing a lot of problems.

With a slight modification to your script, I was able to clean up database.

The only change I did was to change within the loop replacing these lines

if(tmpDateNum >= DayDeleteFromNum)
{
        if (tmpDateNum <= DayDeleteToNum)
	{
	        oStock.Quotations.Remove(j);
	}
}

with these

if (tmpDateNum <= DayDeleteToNum) 
{
        if(oStock.Quotations( j ).Volume == 0)
        {
	        oStock.Quotations.Remove(j);
	}
}

There might be a way to do this in AB itself, but I couldn't figure it out and your script worked like a charm.