Removing non-trading bars

Hi all,

I have a database of all Chinese A-Shares traded stocks with EOD downloaded from Yahoo. But the yahoo data records all the non-traded days as fake bars which has High=Low=Close=Open=a constant value. This really throw my indicators out of shape. I try to write a program to remove all those bars. But searching all over the user docs and this forum, I do not see any function that allows me to remove bars in the database. Can someone please point me to where I can find those functions or sample codes?

I have looked at Database Purify, but it seems do not allow me to do this easily. There are too many stocks to do this manually also. Any pointer by the veterans would be great!

image

This screenshot of the period between the time markers illustrades the issue.

Thanks in advance!
Ken

1 Like

@kzliao you can remove quotations using the OLE interface via a script.

This post has a very basic example - see the OLE documentation to learn more.

This other example shows how to loop between all stocks and how to access a single quotation to decide what to do.

2 Likes

@kzliao You can follow @beppe's advice or you can "hide" those periods without deleting them from the database, using SparseCompress() and SparseExpand().

https://www.amibroker.com/guide/afl/sparsecompress.html

https://forum.amibroker.com/search?q=SparseCompress

1 Like

@beppe @Milosz Thank you both for the info. I did look at SparseCompress but it could not work for me because it requires me to change too many indicators including the official indicators. It is easier if I can delete those fake bars. That way, no indictor codes need to be changed.

In addition to what has been already suggested here, could you also try what Tomasz mentioned or the JS from the below post.

Warning: Do backup your database if you choose to run the JS.

1 Like

@Lennon Thanks for your suggestons. I will look into it. All these methods seem to be more complicated than I thought. I was hoping to get a few AFL functions that I can use which seems not the case :slightly_smiling_face:

@Milosz @beppe @Lennon @Tomasz Thinking about this problem a bit more, hand clean the database is not a good solution either. Because with every update, the fake bars could come back to the system again. It is the best if AFL provides a compand to compress the bar arrays sort of like SparseCompress and SparseExpand but change directly on all the system bar arrays (i.e. C, H, L, etc.). This way none of the system indicator will need to be changed to work correctly. The current problem with SparseCompress is that it requires the system to take a diff bar array which is imposible to pass into for many system provided indicators (I know some do have variant forms like CCI/CCIa). I hope this suggestion make sense and worth looking into. I have cc'ed Tomasz on this post as well.

@kzliao "..because with every update, the fake bars could come back to the system again."

IMO, after the initial "bulk" cleanup, this could easily be avoided if you use a daily scheduled DOS batch to invoke both AmiQuote download and the remove quotes script, using some logic to download/clean only the most recent data (from date to date - ideally only one day).

Anyway, if your alternative solution works well, this is something you can safely ignore! :smile:

2 Likes

@Tomasz @beppe I can never trust the DOS/Windows's batch capability if one exists. But your suggestion reminds me another potential good solution is the following: AmiQuote allows an user option to ignore any bar with trading volume is zero and the bar's open==high==low==close. This seems the characteristics of all fake bars. We can not simply judge by volume alone because many FX and Index symbols have no volume either. I would be very much satisfied if this option is available on AmiQuote.

Thanks for the wonderful idea :slightly_smiling_face:

This takes 2 examples found on this site, resolves the error in one and combines them into a script to do what you (I also need) done.

// THIS IS NOT AFL
// This is Windows script to be run from the outside of AmiBroker
function RemoveQuotesWithZeroVolume( Null )
{
     AB = new ActiveXObject("Broker.Application");
     Stk = AB.Stocks;
     stk_count = Stk.Count -1
     cnt = 0;
     for ( n = stk_count ; n >=0; n --)
     {
     	Quotes = Stk(n).Quotations;
     	iQty = Quotes.Count;
     	for( i = iQty - 1; i >= 0; i-- )
     	{
        	qt = Quotes.Item( i );
        	if( qt.Volume == 0 ) 
        	{ 
           		cnt++;
           		Quotes.Remove( i );
        	}
     	}
     }
     AB.RefreshAll();

     return cnt;
}
WScript.Echo ( "Running quote cleanup" );
n = RemoveQuotesWithZeroVolume();
WScript.Echo ( "Removed " + n + " quotes with zero volume" );
3 Likes