Import csv files from AFL

empottasch · November 7, 2022, 2:41pm

hi, I want to import CSV files using AFL. I searched and found solutions using scripting but I always get dizzy when I look at that, i think I am too dumb to use that. There is an example here:

http://www.amibroker.com/newsletter/02-2001.html

I understand the format needs to be known of the data to import. Anyone know how to do this from AFL. So I have the code below. As you can see I defined the file names. The format for all files is the same. Can this be done from AFL? Or do I need external scripting? Thanks

dataPath = "D:\\AmibrokerDataTest\\";

// date format %m-%d-%Y
startDate1 = ParamDate( "Start Date", "10/4/2022", 2 );
endDate1 = ParamDate( "End Date", "11/4/2022", 2 );
importprocessed1mindata = ParamTrigger( "Import 1Min Data in Amibroker Research DB", "Press Here" );

ndays = DateTimeDiff( endDate1, startDate1 ) / inDaily;

// set your symbol list here
symbollist = "[@ES#,@NQ#]";

if( importprocessed1mindata )
{
    Say( "import 1 minute data" );

    for( i = 0; ( sym = StrExtract( symbollist, i ) ) != ""; i++ )
    {
        for( i = 0; i < ndays; i++ )
        {
            n1 = DateTimeAdd( startDate1, i, inDaily );
            n2 = DateTimeToStr( n1, 1 );

            n2a = StrReplace( n2, "/", "," );

            mm = StrExtract( n2a, 0 );
            dd = StrExtract( n2a, 1 );
            yyyy = StrExtract( n2a, 2 );

            if( StrLen( mm ) == 1 )
            {
                mm = "0" + mm;
            }

            if( StrLen( dd ) == 1 )
            {
                dd = "0" + dd;
            }

            fdate = yyyy + mm + dd;

            fn1 = dataPath + sym + "\\" + "1minMKTLMT\\" + sym + "_" + fdate + "_" + "1min" + ".csv";
            fn2 = dataPath + sym + "\\" + "1minMKTLMT\\" + sym + "_" + fdate + "_Delta_" + "1min" + ".csv";
			fn3 = dataPath + sym + "\\" + "1minMKTLMT\\" + sym + "_" + fdate + "_LMTDelta_" + "1min" + ".csv";
			fn4 = dataPath + sym + "\\" + "1minMKTLMT\\" + sym + "_" + fdate + "_MKTDelta_" + "1min" + ".csv";
            printf( "" + fn4 + "\n" );
        }
    }

    Say( "finished" );
}

SetChartOptions( 1, chartShowDates, chartGridMiddle, 0, 0, 0 );
Plot( C, "Data", ColorRGB( 3, 157, 252 ), styleDots, Null, Null, 0, 0, 1 );

2DD · November 7, 2022, 3:02pm

Hi, I don't know if I've understand wrongly, but maybe could be useful the part of code I've used to load a CSV file into a matrix. Take a look here: https://forum.amibroker.com/t/time-consuming-loading-of-a-csv-file-into-a-matrix/32383

empottasch · November 7, 2022, 3:09pm

thanks. The files I am importing are tickers. Although artificial they all have the format, see below. So I am trying to import csv ticker files from AFL. I think you are doing something different.

# Format definition file generated automatically
# by AmiBroker's ASCII Import Wizard
$FORMAT Ticker, Date_DMY, Time, Open, High, Low, Close, Volume, Aux1, Aux2
$SKIPLINES 1
$SEPARATOR ,
$CONT 1
$GROUP 255
$AUTOADD 1
$DEBUG 1
$ALLOWNEG 1

nsm51 · November 7, 2022, 3:16pm

AFL Function Reference - DATETIMEFORMAT (amibroker.com)

See this Function. it will do the string formatting the way you want.
There is quite a lot wrestling with DateTimeToStr, StrReplace, StrExtract...

empottasch · November 7, 2022, 3:19pm

ok thanks, yes I overlooked that function. Will replace my code with that. But it does not do the import of the data from AFL

mradtke · November 7, 2022, 3:23pm

The AFL function AddToComposite() will allow you to add symbols and their data to your database.

nsm51 · November 7, 2022, 3:23pm

Cant import data from ASCII file by using JS - OLE - AFL Programming - AmiBroker Community Forum

i found this. There is a Batch option suggested by Tomasz

empottasch · November 7, 2022, 3:31pm

ok thanks guys, will try out your suggestion and get back on it later

beppe · November 7, 2022, 6:04pm

@empottasch, try this (I used _TRACEF instead of printf):

dataPath = "D:\\AmibrokerDataTest\\";

// date format %m-%d-%Y
startDate1 = ParamDate( "Start Date", "10/4/2022", 2 );
endDate1 = ParamDate( "End Date", "11/4/2022", 2 );
importprocessed1mindata = ParamTrigger( "Import 1Min Data in Amibroker Research DB", "Press Here" );

ndays = DateTimeDiff( endDate1, startDate1 ) / inDaily;

// set your symbol list here
symbollist = "@ES#,@NQ#";

if( importprocessed1mindata )
{
    Say( "import 1 minute data" );

    // creating OLE object instance
    AB = CreateObject( "Broker.Application" );

    for( j = 0; ( sym = StrExtract( symbollist, j ) ) != ""; j++ )
    {
        _TRACEF( "Processing %s", sym );
        for( i = 0; i < ndays; i++ )
        {
            n1 = DateTimeAdd( startDate1, i, inDaily );
            n2 = DateTimeToStr( n1, 1 );

            n2a = StrReplace( n2, "/", "," );

            mm = StrExtract( n2a, 0 );
            dd = StrExtract( n2a, 1 );
            yyyy = StrExtract( n2a, 2 );

            if( StrLen( mm ) == 1 )
            {
                mm = "0" + mm;
            }

            if( StrLen( dd ) == 1 )
            {
                dd = "0" + dd;
            }

            fdate = yyyy + mm + dd;

            fn1 = dataPath + sym + "\\" + "1minMKTLMT\\" + sym + "_" + fdate + "_" + "1min" + ".csv";
            fn2 = dataPath + sym + "\\" + "1minMKTLMT\\" + sym + "_" + fdate + "_Delta_" + "1min" + ".csv";
            fn3 = dataPath + sym + "\\" + "1minMKTLMT\\" + sym + "_" + fdate + "_LMTDelta_" + "1min" + ".csv";
            fn4 = dataPath + sym + "\\" + "1minMKTLMT\\" + sym + "_" + fdate + "_MKTDelta_" + "1min" + ".csv";
            // Importing data via OLE
            for (n = 1; n <= 4; n++) {
				file = VarGet("fn" + n);
				if( fGetStatus( file, 3 ) > 0 ) {
					_TRACEF( "Importing [%s]", file );
					AB.import( 0, file, "emp.format" );
				} else {
					_TRACEF( "File [%s] does not exist...", file );
				}
			}
        }
    }

    Say( "finished" );
}

SetChartOptions( 1, chartShowDates, chartGridMiddle, 0, 0, 0 );
Plot( C, "Data", ColorRGB( 3, 157, 252 ), styleDots, Null, Null, 0, 0, 1 );

Be sure to have already saved your "import format" via the import wizard (as you posted above) assigning to it the name "emp.format" (or whatever you prefer if you change it in the "AB.import" line).

Data (here below fake) to be imported are expected to be as:

Ticker,Date_DMY,Time,Open,High,Low,Close,Volume,Aux1, Aux2
@ES_DELTA#,04-10-2022,09:16:00,283,283,283,283,14724,1,1
@ES_DELTA#,04-10-2022,09:17:00,283,283,283,283,14725,1,1

Obviously, the four files should have a different ticker symbol...
Fixed a couple of issues (j in the outer loop instead of i and the list of the symbols: I removed the square brackets... AFL is not Python!).

I did not check the rest of the logic. Hope it works (it did in my limited tests).

empottasch · November 7, 2022, 6:15pm

thanks beppe, that looks very good. I will test in the morning. Yes you are right about the j-loop and the brackets. I already changed that. It is indeed part of code that also communicates with Python. That list I also send to Python with the Amibroker Python plugin. That is why I added brackets. I download tick files via Python and process them with Python and then want to import the processed files as 1min files. I will post code that downloads tick files using Python later (for those with a IQFeed subscription)

Will get back when I tested it thanks very much

nsm51 · November 8, 2022, 4:36am

If you are using Python, then you could directly import with OLE calls from there.

from win32com.client import Dispatch

oab = Dispatch('Broker.Application')

## Loop code for all files
## make sure to use windows style \ and not python style / slashes
oab.Import( 0, "path\to\file.csv", "path\to\import.format" )        



## lastly, save, refresh etc
oab.SaveDatabase()

empottasch · November 8, 2022, 6:02am

yes it works great, thanks a lot. Tested the code below. Works good and also pretty fast

dataPath = "D:\\AmibrokerDataTest\\";

// date format %m-%d-%Y
startDate1 = ParamDate( "Start Date", "10/4/2022", 2 );
endDate1 = ParamDate( "End Date", "11/4/2022", 2 );
importprocessed1mindata = ParamTrigger( "Import 1Min Data in Amibroker Research DB", "Press Here" );

ndays = 1 + DateTimeDiff( endDate1, startDate1 ) / inDaily;

// set your symbol list here
symbollist = "[@ES#,@NQ#]";

if( importprocessed1mindata )
{
    Say( "import 1 minute data" );
    symbollist1 = StrReplace( symbollist, "[", "" );
    symbollist1 = StrReplace( symbollist1, "]", "" );
    AB = CreateObject( "Broker.Application" );

    for( s = 0; ( sym = StrExtract( symbollist1, s ) ) != ""; s++ )
    {
        for( i = 0; i < ndays; i++ )
        {
            n1 = DateTimeAdd( startDate1, i, inDaily );
            n2 = DateTimeToStr( n1, 1 );

            n2a = StrReplace( n2, "/", "," );

            mm = StrExtract( n2a, 0 );
            dd = StrExtract( n2a, 1 );
            yyyy = StrExtract( n2a, 2 );

            if( StrLen( mm ) == 1 )
            {
                mm = "0" + mm;
            }

            if( StrLen( dd ) == 1 )
            {
                dd = "0" + dd;
            }

            fdate = yyyy + mm + dd;

            fn1 = dataPath + sym + "\\" + "1minMKTLMT\\" + sym + "_" + fdate + "_" + "1min" + ".csv";
            fn2 = dataPath + sym + "\\" + "1minMKTLMT\\" + sym + "_" + fdate + "_Delta_" + "1min" + ".csv";
            fn3 = dataPath + sym + "\\" + "1minMKTLMT\\" + sym + "_" + fdate + "_LMTDelta_" + "1min" + ".csv";
            fn4 = dataPath + sym + "\\" + "1minMKTLMT\\" + sym + "_" + fdate + "_MKTDelta_" + "1min" + ".csv";
            //printf( "" + fn1 + "\n" );

            // Importing data via OLE
            for( n = 1; n <= 4; n++ )
            {
                file = VarGet( "fn" + n );

                if( fGetStatus( file, 3 ) > 0 )
                {
                    //_TRACEF( "Importing [%s]", file );
                    AB.import( 0, file, "EMP2.format" );
                }
                else
                {
                    //_TRACEF( "File [%s] does not exist...", file );
                }
            }
        }
    }

    Say( "finished" );
}

SetChartOptions( 1, chartShowDates, chartGridMiddle, 0, 0, 0 );
Plot( C, "Data", ColorRGB( 3, 157, 252 ), styleDots, Null, Null, 0, 0, 1 );

empottasch · November 8, 2022, 6:09am

hi nsm,

thanks. So this Python code you can call from Amibroker and then it will also update the database in Amibroker? Thanks, I will also try this later. I have now 1 solution that works but what you are suggesting also seems like a good solution. Will try it out later and let you know if it worked.

empottasch · November 8, 2022, 10:06am

is anyone here also using the Python plugin? I was wondering if one can get a progress indicator back in Amibroker while some process is running in Python.

For instance when I download a bunch of tick files from IQFeed using Python code then when I run the Python code from a CMD window I use print() to show progress. When I run the code from Amibroker I use AmiPy.Print() but that only shows the output once the entire Python process is finished.

So I can see the progress looking inside the folder where I store these tick files using explorer but I wonder if I can get some output back in Amibroker while the Python code is running, thanks

Tomasz · November 8, 2022, 11:51am

When you are INSIDE the plugin, the plugin has control and AFL calling thread waits for plugin to RETURN control. Therefore, you can't display anything from AFL until plugin returns control. What you can and should do is to divide the job into smaller pieces (like processing one file at a time) and then you call plugin multiple times and display "progress" as calls return.

empottasch · November 8, 2022, 1:26pm

ok understood, thanks for your reply

empottasch · November 8, 2022, 2:20pm

yes of course, I was using the Python code from the CMD window. And now I ran the basically unchanged code from AFL. But the iteration part I should now do from AFL of course

system · February 16, 2023, 2:20pm

This topic was automatically closed 100 days after the last reply. New replies are no longer allowed.