How can I export explore to csv using AFL code?

#1
As title, I know how to do it in amibroker by clicking "File -> Export HTML/CSV". However, if I want to perform the action in afl, what should I do?
Here is the code that I output the daily return in explore,

Filter=1;
NumColumns=1;
rp = ROC(C);

Column0=rp;
Column0Name="Daily Return";

How can I write the code such that I can output the daily return to csv? Thanks.
 

mastermind007

Well-Known Member
#3
As title, I know how to do it in amibroker by clicking "File -> Export HTML/CSV". However, if I want to perform the action in afl, what should I do?
Here is the code that I output the daily return in explore,

Filter=1;
NumColumns=1;
rp = ROC(C);

Column0=rp;
Column0Name="Daily Return";

How can I write the code such that I can output the daily return to csv? Thanks.
You will have to invoke COM script from AFL
 

trash

Well-Known Member
#4
Just simple AFL is needed

Code:
// export of <TICKER>,<YYYY-MM-DD>,<hh:mm:ss>,<ROC> to CSV file
// trash, Jan 2014
// Usage:
// 1. open AFL in Analysis,
// 2. choose Apply to:
// 3. choose Range
// 4. choose Periodicity in backtest settings
// 5. click Explore
// 6. be happy and smile

destination = "C:\\#ABExport\\"; // choose export folder
fmkdir( destination ); // creates export folder

bir     = Status( "BarInRange" );
lbr     = Status( "LastBarInRange" );
ss      = Second();
mm      = Minute();
Hh      = Hour();
dd      = Day();
mth     = Month();
yr      = Year();
nm      = Name();
_ROC    = Nz( ROC( C, 1 ) );

Filter  = lbr;

SetOption( "NoDefaultColumns", True );
AddTextColumn( nm, "Ticker", 1, colorWhite, colorDarkGrey, 90 );
AddTextColumn( "Export finished", "Status", 1, colorWhite, colorDarkGreen, 90 );

if ( Status( "action" ) == actionExplore )
{
    fh = fopen( destination + nm + ".csv", "w" );

    if ( fh )
    {
        fputs( "<Ticker>,<YYYY-MM-DD>,<hh:mm:ss>,<ROC>\n", fh );

        for ( i = 0; i < BarCount; i++ )
        {
            if ( bir[i] )
            {
                // <TICKER>,<YYYY-MM-DD>,<hh:mm:ss>,<ROC>
                fputs( nm + ",", fh );

                dnex = StrFormat( "%02.0f-%02.0f-%02.0f,", yr[i], mth[i], dd[i] );
                fputs( dnex, fh );

                tnex = StrFormat( "%02.0f:%02.0f:%02.0f,", hh[i], mm[i], ss[i] );
                fputs( tnex, fh );

                priceex = StrFormat( "%g\n", _ROC[i] );

                fputs( priceex, fh );
            }
        }

        fclose( fh );
    }
}
 

trash

Well-Known Member
#6
Thanks for your code.

However, is it possible to output multiple ticker in one tab in one excel file?
Yes it is. Instead of "w" (write) you use "a" (append). But if you use an AB version 5.50+ you have to add critical section to prevent
multiple threads from writing to the very same file at the very same time! Because all versions higher or equal AB 5.50 are using multi-threaded analysis environment.
 
#7
Thanks.

One more question is that if I want to output ( "<Ticker>,<YYYY-MM-DD>,<ROC>\n", fh ); once in the excel file in the first row when outputing multiple ticker, what should I do?

Below is my modified code from the above.

Code:
destination = "C:\\TEMP\\"; // choose export folder
fmkdir( destination ); // creates export folder

fh = fopen( destination + "today.csv", "a" );
if ( fh )
{
fputs( "<Ticker>,<YYYY-MM-DD>,<ROC>\n", fh );
}
fclose( fh );


bir     = Status( "BarInRange" );
lbr     = Status( "LastBarInRange" );
ss      = Second();
mm      = Minute();
Hh      = Hour();
dd      = Day();
mth     = Month();
yr      = Year();
nm      = Name();
_ROC    = Nz( ROC( C, 1 ) );

Filter  = lbr;

SetOption( "NoDefaultColumns", True );
AddTextColumn( nm, "Ticker", 1, colorWhite, colorDarkGrey, 90 );
AddTextColumn( "Export finished", "Status", 1, colorWhite, colorDarkGreen, 90 );

fh = fopen( destination + "today.csv", "a" );

if ( fh )
{

if ( Status( "action" ) == actionExplore )
{

        //fputs( "<Ticker>,<YYYY-MM-DD>,<ROC>\n", fh );

        for ( i = 0; i < BarCount; i++ )
        {
            if ( bir[i] )
            {
                // <TICKER>,<YYYY-MM-DD>,<hh:mm:ss>,<ROC>
                fputs( nm + ",", fh );

                dnex = StrFormat( "%02.0f-%02.0f-%02.0f,", yr[i], mth[i], dd[i] );
                fputs( dnex, fh );

                //tnex = StrFormat( "%02.0f:%02.0f:%02.0f,", hh[i], mm[i], ss[i] );
                //fputs( tnex, fh );

                priceex = StrFormat( "%g\n", _ROC[i] );

                fputs( priceex, fh );
            }
        }

}

}
 

trash

Well-Known Member
#8
Thanks.

One more question is that if I want to output ( "<Ticker>,<YYYY-MM-DD>,<ROC>\n", fh ); once in the excel file in the first row when outputing multiple ticker, what should I do?

Keep in mind that you need to add critical section if writing to one file and using New Analysis introduced since 5.50! If you don't you will get a big disaster in your file.

YOU HAVE BEEN WARNED!

Here is an example how to use critical section http://www.amibroker.com/guide/afl/staticvarcompareexchange.html‎



Code:
// export of <TICKER>,<YYYY-MM-DD>,<hh:mm:ss>,<ROC> to CSV file
// trash, Jan 2014
// Usage:
// 1. open AFL in Analysis,
// 2. choose Apply to:
// 3. choose Range
// 4. choose Periodicity in backtest settings
// 5. click Explore
// 6. be happy and smile

destination = "C:\\#ABExport\\"; // choose export folder
fmkdir( destination ); // creates export folder

bir     = Status( "BarInRange" );
lbr     = Status( "LastBarInRange" );
ss      = Second();
mm      = Minute();
Hh      = Hour();
dd      = Day();
mth     = Month();
yr      = Year();
nm      = Name();
_ROC    = Nz( ROC( C, 1 ) );

Filter  = lbr;

SetOption( "NoDefaultColumns", True );
AddTextColumn( nm, "Ticker", 1, colorWhite, colorDarkGrey, 90 );
AddTextColumn( "Export finished", "Status", 1, colorWhite, colorDarkGreen, 90 );

if ( Status( "action" ) == actionExplore ) 
{
    filename = destination + nm + ".csv";

    fh = fopen( filename, "r" );

    if ( NOT fh )
    {
        _TRACE( "file does not exist" );

        fw = fopen( filename, "w" );

        if ( fw )
        {
            fputs( "<Ticker>,<YYYY-MM-DD>,<hh:mm:ss>,<ROC>\n", fw );

            for ( i = 0; i < BarCount; i++ )
            {
                if ( bir[i] )
                {
                    // <TICKER>,<YYYY-MM-DD>,<hh:mm:ss>,<ROC>
                    fputs( nm + ",", fw );

                    dnex = StrFormat( "%02.0f-%02.0f-%02.0f,", yr[i], mth[i], dd[i] );
                    fputs( dnex, fw );

                    tnex = StrFormat( "%02.0f:%02.0f:%02.0f,", hh[i], mm[i], ss[i] );
                    fputs( tnex, fw );

                    priceex = StrFormat( "%g\n", _ROC[i] );
                    fputs( priceex, fw );
                }
            }
            fclose( fw );            
        }
    }
    else  
    {
        _TRACE( "file exists" );

        fclose( fh );

        fw = fopen( filename, "a" );

        if ( fw )
        {
            for ( i = 0; i < BarCount; i++ )
            {
                if ( bir[i] )
                {
                    // <TICKER>,<YYYY-MM-DD>,<hh:mm:ss>,<ROC>
                    fputs( nm + ",", fw );

                    dnex = StrFormat( "%02.0f-%02.0f-%02.0f,", yr[i], mth[i], dd[i] );
                    fputs( dnex, fw );

                    tnex = StrFormat( "%02.0f:%02.0f:%02.0f,", hh[i], mm[i], ss[i] );
                    fputs( tnex, fw );

                    priceex = StrFormat( "%g\n", _ROC[i] );
                    fputs( priceex, fw );
                }
            }
            fclose( fw );
        }
    }
}
UPDATE: fix - changed to _TRACE( "file does not exist" );
 
Last edited:
#9
Thanks Trash for your time and efforts here, much appreciated.

But looks like the Date Range part tends to elude most of the coders or probably this is how it is in Amibroker as no matter what you specify it considers the date range of the data (entire data) available for that symbol.

~Cheers
GK