Export tick data

Bisto · August 31, 2017, 4:57pm

Hello,

I would like to extract a short period of tick data, i.e. two days, from a big tick database of circa 4 millions bar

I tried in two ways but failed both:

Deleting the not needed bars in the Quotations Editor fails because AB crashes in doing that. Maybe the deleting of millions of bars is considered “not normal”
Exporting the data in the desidered period of time and import again as a new symbol. It usually works with the following code but I am not able to manage the timestamp of tick data that cointains microseconds like 12.45.47.844.003

Of course exporting/re-importing is not needed at all, it’s just an idea I had to have a new symbol with a manageable number of bars

Symbol = Name();

fmkdir( "C:\\DataCompressed" );
fh = fopen( "C:\\DataCompressed\\DataCompressed_" + Symbol + "_" + Interval( 2 ) + ".csv", "w" );


if( fh )
{
    fputs( "Symbol,Date,Time,Open,High,Low,Close,OpenInt,Volume,Aux1,Aux2\n", fh );

    dn = DateNum();
    tn = TimeNum();

    startdata = ParamDate( "Data start", "31/05/2017" );
    enddata = ParamDate( "Data end", "01/06/2017" );

    checkdata = DateNum() >= startdata  AND DateNum() <= enddata   ;
    
    exportedbars = 0 ;

    for( i = 0; i < BarCount; i++ )
    {
        if( checkdata[i] )
        {
            Line = Symbol/* + "_" + Interval( 2 ) */ +
                   StrFormat( ",%06.0f,%06.6f,%g,%g,%g,%g,%g,%g,%g,%g\n",
                              dn[ i ] % 1000000,
                              tn[ i ],
                              Open[ i ],
                              High[ i ],
                              Low[ i ],
                              Close[ i ],
                              OpenInt[ i ],
                              Volume[ i ] ,
                              Aux1[ i ] ,
                              Aux2[ i ] );

            fputs( Line, fh );
            
            exportedbars++ ;
        }
    }

    fclose( fh );
}

Buy = Sell = 0; // for scan

Filter = Status( "lastbarinrange" );
AddTextColumn( "Export done " + NumToStr(exportedbars,1.0) + "bars", "Status",1.2,colorBlack,colorDefault,200 );
AddTextColumn( Interval( 2 ), "Timeframe",1.2,colorBlack,colorDefault,200 );

Any idea of how to manage microsecond timestamp in exporting data?

Any other idea about how to have a reduced copy of the symbol with the data just in a short period of time? I need to reduce the number of bars because I don’t like to work with millions tick bars loaded in memory for some months history when I need to work just in one or two days in the middle of the data period

thanks in advance

fxshrat · August 31, 2017, 5:24pm

Scrap all the paramdate “junk” and OHL arrays. If you are exporting 1-tick then you just need close array.
And if using barinrange then you can set date range via From-to of Range setting of analysis toolbar so no paramdate required.

Here is sample

(if re-importing don’t forget to set $TICKMODE command of import format to 1 if you want all timestamps)

// Export tick data
// sample code by fxshrat, 
/// @link http://forum.amibroker.com/t/export-tick-data/1939/2

nm      = Name();
dt      = DateTime();
millsec = MilliSec();
micsec  = MicroSec();
bir     = Status( "BarInRange" );
fbr     = Status( "FirstBarInRange" );
lbr     = Status( "LastBarInRange" );
bars    = Cum( bir );

folder  = "C:\\DataCompressed\\";
filename = "DataCompressed_" + nm + "_" + Interval( 2 ) + ".csv";

if ( Status( "action" ) == actionExplore ) { 
	if( Interval(2) == "1-tick" ) {
		fmkdir( folder );
		fh = fopen( folder + filename, "w" );
		
		if( fh ) {
			fputs( "Symbol,DateTime,Tick,Volume,OpenInt,Aux1,Aux2\n", fh );
			
			nmstr = nm + ","; 
			for ( i = 0; i < BarCount; i++ ) {
				if ( bir[i] ) { // export of set date range
					dtstring = DateTimeToStr( dt[i] ) + StrFormat( ".%g.%g", millsec[i], micsec[i] ); // Datetime to string conversion   
					Line = nmstr + dtstring + StrFormat( ",%g,%g,%g,%g,%g\n", C[i], V[i], OI[i], Aux1[i], Aux2[i] );						
					fputs( Line, fh );                    
				}
			} 
			
			fclose( fh );
		}

		// Exploration info output ############################
		Filter = lbr;  
		SetOption( "NoDefaultColumns", True );  
		AddTextColumn( "Tick Export finished", "Status", 1, colorWhite, colorDarkGreen, 120 );
		AddTextColumn( nm, "Ticker", 1, colorWhite, colorDarkGrey, 80 );
		AddColumn( ValueWhen( fbr, dt ), "Start Date/Time", formatDateTime, colorWhite, colorDarkGrey, 120 );
		AddColumn( ValueWhen( lbr, dt ), "End Date/Time", formatDateTime, colorWhite, colorDarkGrey, 120 );
		AddColumn( bars, "Bars in Range", 1.0, colorWhite, colorDarkGrey, 80 );
	} else
		Error( "Set periodicity to 1-tick interval!" );
}

Bisto · August 31, 2017, 6:36pm

thank you fxshrat! It solved. Maybe export/re-import is not the most elegant way to cut undesidered data but it solved indeed

I had just to slightly modify the code at the dtstring declaration because data and time had to be separated by a comma to be re-imported

//Filter = Status( "lastbarinrange" );
//AddTextColumn( "Export done " + NumToStr(exportedbars,1.0) + "bars", "Status",1.2,colorBlack,colorDefault,200 );
//AddTextColumn( Interval( 2 ), "Timeframe",1.2,colorBlack,colorDefault,200 );


// Export tick data
// sample code by fxshrat, 
/// @link http://forum.amibroker.com/t/export-tick-data/1939/2

nm      = Name();
dt      = DateTime();
millsec = MilliSec();
micsec  = MicroSec();
bir     = Status( "BarInRange" );
fbr     = Status( "FirstBarInRange" );
lbr     = Status( "LastBarInRange" );
bars    = Cum( bir );

folder  = "C:\\DataCompressed\\";
filename = "DataCompressed_" + nm + "_" + Interval( 2 ) + ".csv";

if ( Status( "action" ) == actionExplore ) { 
	if( Interval(2) == "1-tick" ) {
		fmkdir( folder );
		fh = fopen( folder + filename, "w" );
		
		if( fh ) {
			fputs( "Symbol,Date,Time,Tick,Volume,OpenInt,Aux1,Aux2\n", fh );
			
			nmstr = nm + "-reduced,"; 
			for ( i = 0; i < BarCount; i++ ) {
				if ( bir[i] ) { // export of set date range
					dtstring = DateTimeToStr( dt[i],1 ) +","+ DateTimeToStr( dt[i],2 )+ StrFormat( ".%g.%g", millsec[i], micsec[i] ); // Datetime to string conversion   
					Line = nmstr + dtstring + StrFormat( ",%g,%g,%g,%g,%g\n", C[i], V[i], OI[i], Aux1[i], Aux2[i] );						
					fputs( Line, fh );                    
				}
			} 
			
			fclose( fh );
		}

		// Exploration info output ############################
		Filter = lbr;  
		SetOption( "NoDefaultColumns", True );  
		AddTextColumn( "Tick Export finished", "Status", 1, colorWhite, colorDarkGreen, 120 );
		AddTextColumn( nm, "Ticker", 1, colorWhite, colorDarkGrey, 80 );
		AddColumn( ValueWhen( fbr, dt ), "Start Date/Time", formatDateTime, colorWhite, colorDarkGrey, 120 );
		AddColumn( ValueWhen( lbr, dt ), "End Date/Time", formatDateTime, colorWhite, colorDarkGrey, 120 );
		AddColumn( bars, "Bars in Range", 1.0, colorWhite, colorDarkGrey, 80 );
	} else
		Error( "Set periodicity to 1-tick interval!" );
}

thanks again for fast and effective help

fxshrat · August 31, 2017, 7:18pm

No, that statement of yours is incorrect. You don't need to separate datetime as it is a waste of code and time. Just look carefully at all import wizard's separator options! You can set to comma or space setting in order to deal with single datetime column being separated by space.

Bisto · August 31, 2017, 7:21pm

wow

thanks for the lesson, very appreciated

fxshrat · August 31, 2017, 7:37pm

No problem.

BTW, you may scrap one StrFormat of upper sample code. I only put it in that way so that it is clear where milli and micro sec belong to (column wise).

But actually I like it this way more. It is cleaner.

//
dtstring = DateTimeToStr( dt[i] ); // Datetime to string conversion   
Line = nmstr + dtstring + StrFormat( ".%g.%g,%g,%g,%g,%g,%g\n", millsec[i], micsec[i], C[i], V[i], OI[i], Aux1[i], Aux2[i] );
//

So you might simply replace those two lines if you like to since it has sunk in now.

Yes, to best of my knowledge there is no better way than export and re-import of base time interval if you want to shorten data history of a symbol.

On the other hand if you want to create intervals with less granularity (so for example 1-min base interval out of 1-tick bars) then you may use AddToComposite function.

Tomasz · September 7, 2017, 9:26am

Not true. You can limit the history length automatically. Definable from UI. Keep in mind that the limit is applied when you SAVE the database, so it will be saved only once it is modified or when you do Save Database As..

And please be careful with the numbers you enter there because the operation is irreversible (once shorter data are saved there is no way to get old data back).

fxshrat · September 7, 2017, 9:56am

Tomasz, I didn’t say “there is no other way”.

I could be mistaken but I think the OP wants to keep old long data history together with new shortened history. Also it might be the case that he may intend to shorten just one or a few symbols of a DB. So that in mind I wrote “to best of my knowledge there is no better way than export and re-import”.

So if that should be the case in order to achieve the same with limiting number of quotations setting of UI he would have to copy the entire original DB before and applying that setting in copied DB. Correct?

Another way using that UI setting and if keeping old long history data would be creating a new empty DB, then setting just the symbol names to be shortened there before, closing that new DB and then simply copying the required symbols from old DB folder to new DB folder having same folder letter (overwriting the small 2KB place holding files) and then applying that UI setting in new DB after reopening it.

Bisto · September 7, 2017, 10:26am

Hi TJ,

just limit the length is not ok in my case

and the two days could also be in the middle of the history, not just at the end

so I suppose nothing better to do as described that also permit to have also a not cut symbol for future reference

Tomasz · September 7, 2017, 10:58am

Guys why do you read something else that it was written?
I was responding DIRECTLY to quoted text which was:

I responded to QUOTED TEXT, not to what you have imagined.

I have specifically provided way to SHORTEN DATA HISTORY. Nothing more nothing less. Not to other question, not about the weather, not about political situation. I was specifically pointing QUOTED text.

I did so to avoid situation that happens all the time that certain sentence (like the one given in quoted text in bold) is taken out of context, posted somewhere and suddenly everybody instead of using simpler and faster ways start to do export-import dance because somebody wrote somewhere that “there is no better way”. I can not fix nonsense posted on other sites, but at least on my own forum I need to correct misinformation.

What I write is 100% precise. There is no blue sky and no room for “free interpretation”.

Besides: does Quote Editor really crash (do you get crash recovery) or just appear to “hang” due to time required to process that and all you really need is to wait. Keep in mind that deleting millions of records from Windows List view takes time as Windows List view is not really performance king and it chokes with large number of records even though we use “virtual” mode which is supposed to handle “any” number of records as per Microsoft, but “any” in MS has different meaning It might be better idea to allow deletion of ranges from the chart as charts do not rely on MS technology.

Bisto · September 7, 2017, 11:48am

first of all thanks for spending your time helping us

I didn’t get crash recovery, AB appeared to be hanged and Windows showed the popup about not responding application. After that I waited some minutes more, maybe 5, and then I believed to Windows killing AB process. Maybe waiting for a long time would have solved

Your idea to delete ranges from the chart could solve the very slow MS process but at the end of the day I suppose that this procedure will be used by a small minority of AB users to delete milions bars not at the end of the history and NOT having a copy of the full length history

Tomasz · September 7, 2017, 11:59am

Maybe it is used by minority but deleting range from chart (skipping MS tech) is pretty easy & quick to implement

Bisto · September 7, 2017, 12:06pm

that’s nice

I was wondering that It could be useful if it’s also possible to do a copy of a symbol without the export/re-import trick. Up to now I am not able to do a copy of a symbol by the UI but, to be honest, I never deeply investigated on this

Deleting range of a copy, having the original long history not touched, could be useful. As you can understand to delete quotations without having a bakcup copy is not nice

Tomasz · September 7, 2017, 12:08pm

Copying is possible via Symbol->Merge. Add new symbol, select it, then use Symbol->Merge menu.

Bisto · September 7, 2017, 12:11pm

cool, at this point please do the deleting range from chart thing!

you are always the best

PS merging A with null = copying A is brilliant

Tomasz · September 7, 2017, 1:09pm

6.26 will have delete range from chart feature.

bigalgator · September 7, 2017, 4:21pm

Thanks! This will be very useful to isolate an area of interest within a large data set.

Bisto · September 7, 2017, 5:17pm

if easy you could also implement deleting of inverted range to delete all the other data than the selected range. It is what I do and what also bigalgator is talking about

just an idea

Tomasz · October 3, 2017, 12:30pm

And AmiBroker 6.26 comes with Edit->Delete Range feature now. Somehow I forgot to mention it in the read me.

So here it comes, how to use delete range feature:

Select begin and end of the range (double click to mark “begin” then double click to mark “end”) - green (begin) and red (end) vertical lines will appear
Select Edit->Delete Range menu
Confirmation box will appear asking you if you are sure to delete (irreversibly) the quotes from given range

The operation is pretty much instant regardless of how many quotes you have.

Lennon · April 14, 2018, 7:44am

Hi @Tomasz,

How to use this feature for multiple tickers?
Do I need to go with the same process manually for each tickers?

Many Thanks....