Loop between Specific Dates

Hello,
How to do “FOR” loop between specific dates.

for( i = startDate; i <= endDate; i++)
{
– - – - - ;

}

My problem is how to convert specific date to number.

In Help look for “Lookup” function (read the example) use it to retrieve barindex of start date and last date to use in for statement

datenum()
also returns the array with mumbers

@Night19 this is beyond my knowledge of AmiBroker but am curious.

@PanoS I am wondering if he uses DateNum() might that cause a problem in his for loop? When there is a new month or a weekend the numbers "jump" and are not continuous increments of one. I am too much of a beginner to know what that will do with his loop counter?

image

So perhaps what @awilson was suggesting was something like this?

StartDate = "2011-04-05";
FinishDate = "2017-11-10";

StartBar = Lookup( BarIndex(), _DT( StartDate ), 0 );
FinishBar = Lookup( BarIndex(), _DT( FinishDate ), 0 );

Produces number sequence like this with increments of one per bar,
image

IMO, you guys are over-complicating things.

startdate = ParamDate( "Start date", "2017-01-31");
enddate = ParamDate( "End date", Now(1) );

dn = datenum();
datewindow = dn >= startdate and dn <= enddate;

for( i = 0; i < BarCount; i++ ) {
   if( datewindow[i] ) {
       // then do something...
   }
}

But the OP should ask himself whether looping is actually required. What is he trying to achieve?

4 Likes

@Night19

without additional context is not easy to properly answer. What is the purpose of the loop?

AmiBroker offers a lot of functions to manipulate dates and numbers - see the “Date/Time” section of the:

AFL Function Reference - Categorized list of functions

Look at the suggestion by @awilson if you need dates related to bars that works with the ubiquous AmiBroker arrays.
Another useful one maybe the DaysSince1900() - check its example code.

If on the other hand, you want to write some code without any reference to the chart/bars (like processing external data, log files, etc), and you just need a numeric (corresponding to a continuous count of days from a certain start date) to do a generic loop, take a look at the following sample code and in particular to the MDYtoXLSerialDays( m, d, y ) function and to its mate XLSerialDaysToDateNum( xlSerialDays, returnType ).

This (quite “fancy” IMHO) function - that I copied almost verbatim from the registered users’ AFL library - returns a numeric (not an array) from any date.
It was written to comply with Excel and as you can see running my sample code, looking at the _TRACE (log) output, it generates a single “gap” at March 1, 1900 (this is a documented bug in Excel that incorrectly assumes that 1900 was a leap year…). (This is also done in the DaysSince1900 function to be compliant to a Windows OLEDATE and Excel’s DATEVALUE function).

In any case, if your working dates are after that point in time, the above function could be used to get a numeric from any date.

Example code:

function isLeapYear( y )
{
    return ( ( y % 4 == 0 ) AND( y % 100 != 0 ) ) OR( y % 400 == 0 );
}

function getLastDayOfMonth( m, y )
{
    switch( m )
    {
        case  2: // Feb
            if( isLeapYear( y ) )
                days = 29;
            else
                days = 28;

            break;

        case  4: // Apr
        case  6: // Jun
        case  9: // Sep
        case 11: // Nov
            days = 30;
            break;

        default: // Jan, Mar, May, Jul, Aug, Oct, Dec
            days = 31;
            break;
    }

    return days;
}

function MDYtoXLSerialDays( m, d, y )
{
    /* From AmiBroker AFL Library - Coded by Paul D.
    Returns Microsoft Excel Serial Days since 1/1/1900 given a Month number,
    Day of Month number, AND 4 digit Year
    REMEMBER: this matches Excel EXACTLY including the
    Microsoft Excel / Lotus123 2/29/1900 bug */
    local extra, xlDays;
    extra = 100 * y + m - 190002.5;
    xlDays = 367 * y;
    xlDays -= int( 7.0 * ( y + int( ( m + 9.0 ) / 12.0 ) ) / 4.0 );
    xlDays += int( 275.0 * m / 9.0 );
    xlDays += d;
    xlDays += 1721013.5;
    xlDays -= 0.5 * extra / abs( extra );
    xlDays += 0.5;
    xlDays -= 2415020.5;
    xlDays += IIf( xlDays == 59 AND d == 29, 1, IIf( xlDays >= 59, 2, 1 ) );
    return xlDays;
}

function XLSerialDaysToDateNum( xlSerialDays, returnType )
{
    /* From AmiBroker AFL Library - Coded by Paul D.
    Returns DateNum in format of YYYMMDD when fed the Excel
    equilvalent Serial Days of a Date since 1/1/1900
    REMEMBER: this matches Excel EXACTLY including the
    Microsoft Excel / Lotus123 2/29/1900 bug*/
    local realDays, x, n, y, j, tDay, tMonth, tYear, tDN;
    realDays = IIf( xlSerialDays < 60, xlSerialDays + 1, xlSerialDays );
    x = realDays + 68569 + 2415019;
    n = int( ( 4 * x ) / 146097 );
    x = x - int( ( 146097 * n + 3 ) / 4 );
    y = int( ( 4000 * ( x + 1 ) ) / 1461001 );
    x = x - int( ( 1461 * y ) / 4 ) + 31;
    j = int( ( 80 * x ) / 2447 );
    tDay = x - int( ( 2447 * j ) / 80 );
    x = int( j / 11 );
    tMonth = j + 2 - ( 12 * x );
    tYear = 100 * ( n - 49 ) + y + x;
    tDN = 10000 * ( tYear - 1900 ) + 100 * tMonth + tDay;	//compute datenum integer/number

    switch( returnType )
    {
        case 0:
            returnValue = tDN;
            break; 		//return datenum format

        case 1:
            returnValue = tMonth;
            break;	//return month number

        case 2:
            returnValue = tDay;
            break;	//return day of month number

        case 3:
            returnValue = tYear;
            break; 	//return year number

        default:
            returnValue = tDN;
    }

    return returnValue;
}



// Testing the above functions
startYear = 1900;
endYear = 2017;
lastX = -1;

_TRACE( "!CLEAR!" ); // Clear the trace output
GetPerformanceCounter( True ); // Reset counter to zero

for( y = startYear; y <= endYear; y++ )
{
    for( m = 1; m <= 12; m++ )
    {
        ldom = getlastDayOfMonth( m, y );

        for( d = 1; d <= ldom; d++ )
        {
            x = MDYtoXLSerialDays( m, d, y );

            if( lastX == -1 )
                lastX = x; // Set once the last returned value
            else
            {
                // Report as ERROR any non sequential number
                // Should not happen except March 1, 1900 (was not leap)
                if( x != ( lastX + 1 ) )
                    _TRACE( "ERROR AT VALUE: " + StrFormat( "%12.0f", x ) + " - " + StrFormat( "%4.0f", Y ) + "/" + StrFormat( "%02.0f", m ) + "/" + StrFormat( "%02.0f", d ) );

                lastX = x;

                if( ( m == 2 ) AND( d == ldom ) )
                    _TRACE( "Check leap years : " + StrFormat( "%12.0f", x ) + " - " + StrFormat( "%4.0f", Y ) + "/" + StrFormat( "%02.0f", m ) + "/" + StrFormat( "%02.0f", d ) );
            }
        }
    }
}

elapsed = GetPerformanceCounter();
totYears = endYear - startYear + 1;
_TRACE( "Looped over " + StrFormat( "%3.0f", totYears ) + " years - Processed in " + StrFormat( "%7.0f", elapsed ) + " milliseconds." );

// Using the function to calculate date differences
startDate = MDYtoXLSerialDays( 1, 1, 2017); 
endDate = MDYtoXLSerialDays( 6, 30, 2017); 
numberOfDayOfHalfYear = endDate-startDate+1;
_TRACE("This year (2017) from Jan 1st to Jun 30th there are " + StrFormat("%3.0f", numberOfDayOfHalfYear) + " days.");

// Looping over a date range
startDate = MDYtoXLSerialDays(11, 1, 2017); 
endDate = MDYtoXLSerialDays( 11, 11, 2017); 
for(i = startDate; i <= endDate; i++) {
   // your code here....
   dn = XLSerialDaysToDateNum( i, 0); // DateNum format
   m = XLSerialDaysToDateNum( i, 1);  // Month 
   d = XLSerialDaysToDateNum( i, 2);  // Day
   y = XLSerialDaysToDateNum( i, 3);  // Year
   _TRACE("Looping over date : (Excel: " + 
	   StrFormat( "%8.0f", i ) + ") - Date YMD: " + 
	   StrFormat( "%04.0f", y ) + "/" + 
	   StrFormat( "%02.0f", m ) + "/" + 
	   StrFormat( "%02.0f", d ) + " - AmiBroker DateNum: " + 
	   StrFormat( "%12.0f", dn));   
}

May be useful in some specific cases.

(By the way Amibroker is quite fast doing math calculations as you can see checking the performance counter result looping over almost 118 years)

6 Likes

Please note that these function were written before DaysSince1900 was available. Now it is much faster to use DaysSince1900()

Hi,

Thanks all of you for your fast reply.

With due respect, beppe
**

> without additional context is not easy to properly answer. What is the purpose of the loop?

**

I need to export data between specific dates using following code.

fmkdir( "C:\\OHLC" );
fh = fopen( "c:\\OHLC\\" + Name() + ".txt", "w" );

if( fh )
{
    fputs( "<ticker>,<per>,<date>,<open>,<high>,<low>,<close>,<vol>\n", fh);
    y = Year();
    m = Month();
    d = Day();

    for( i = 0; i < BarCount; i++ )
    {
        fputs( Name() + "," , fh );
        ds = StrFormat("%02.0f%02.0f%02.0f,", y[ i ], m[ i ], d[ i ] ); 
        fputs( ds, fh );



        qs = StrFormat( "%s,%.4f,%.4f,%.4f,%.4f,%.0f\n","D", O[ i ], H[ i ], L[ i ], C[ i ], V[ i ] );
        fputs( qs, fh );
    }

    fclose( fh );
}

Buy = 0;

When I am using this snippet, data of all dates are exported.

A proper simple solution has been posted already… just use a “datewindow” variable.

Since you are applying in analysis it works even simpler than using datenum() condition… you just need Status() function and then you select date range in analysis toolbar -> range: from-to…

Anyway here is modified (simplified) code:

EDIT:
I have additionally used DateTimeFormat() function for simpler code

DateTimeFormat("%Y%m%d,", dt[i] );

results in YYYYMMDD

Full code:

/// simplified code modification by fxshrat posted to issue asked by Night19 been discussed in this thread
/// @link http://forum.amibroker.com/t/loop-between-specific-dates/3120/9
/// code execution via Analysis - Scan
/// Select date range in Range - FromTo of analysis toolbar
/// Set periodicity in Analysis settings 'General' tab
/// Then hit 'Scan' button
dir = "C:\\OHLC\\";

Version( 6.20 );

nm = Name();
dt = DateTime();
datewindow = Status("barinrange");

if( Status( "action" ) == actionScan ) {
	fmkdir( dir );
	
	fh = fopen( dir + nm + ".txt", "w" );

	if( fh ) {
	
		fputs( "<ticker>,<per>,<date>,<open>,<high>,<low>,<close>,<vol>\n", fh);
		
		for( i = 0; i < BarCount; i++ )	{
			// if date range of analysis tool bar
			if( datewindow[i] ) { 
				ds = DateTimeFormat("%Y%m%d,", dt[i] );
				qs = StrFormat( "%s,%.4f,%.4f,%.4f,%.4f,%.0f\n","D", O[ i ], H[ i ], L[ i ], C[ i ], V[ i ] );
				fputs( nm + "," + ds + qs, fh );
			}
		}

		fclose( fh );
	}

	Buy = 0;
}
6 Likes

Excellent code dear fxshrat,
It gives result as I needed.

Thanks

1 Like

I keep my Amibroker updated by acquiring all the upgrades although I continue to use Multicharts for my own coding and trading. This is one more attempt to use Amibroker.

My question is about Looping between specific dates, so I am using this thread.

Somewhere in the forum, Tomasz explained the use of the equity() function and confirmed that the following code works. (I am sorry, I missed the link to that thread).

ma1 = MA(C,12);
ma2 = MA(C,24);
ma3 = MA(C,48);


Buy  = Ref(C>ma1,-1);
Sell = Ref(C<ma1,-1);
Sig1 = Equity();
Plot(sig1,"sig1",colorBlue);

Buy  = Ref(C>ma2,-1);
Sell = Ref(C<ma2,-1);
Plot(sig2,"sig2",colorGreen);

Buy  = Ref(C>ma3,-1);
Sell = Ref(C<ma3,-1);
Plot(sig3,"sig3",colorRed);

This is impressive.

I wanted to get the equity curve between a range of dates. I checked out the Equity() function and also a code segment by Rajendran R (http://www.amibroker.com/members/library/detail.php?id=1441) where a similar start and last date range is used in Equity. Then, my revised code is:

SetPositionSize(1, spsShares);

startdate = ParamDate( "Start date", "01-01-2020",0);
enddate = ParamDate( "End date", "31-12-2029",0);

ma1 = MA(C,12);
ma2 = MA(C,24);
ma3 = MA(C,48);


Buy  = Ref(C>ma1,-1);
Sell = Ref(C<ma1,-1);
sig1 = Equity(1,3,startdate,enddate);
//Sig1 = Equity();
Plot(sig1,"sig1",colorBlue);

Buy  = Ref(C>ma2,-1);
Sell = Ref(C<ma2,-1);
sig2 = Equity(1,3,startdate,enddate);
Plot(sig2,"sig2",colorGreen);

Buy  = Ref(C>ma3,-1);
Sell = Ref(C<ma3,-1);
sig3 = Equity(1,3,startdate,enddate);
Plot(sig3,"sig3",colorRed);

My question is:
How do I set a range of dates in AFL. Suppose I wish to run a strategy between a start date and an end date. I tried searching but remain confused on the method.

In Multicharts I would say:
Condition1 = Date >= Startdate AND Date <= Lastdate;
If Condition1 and some buy condition then buy at market; // or whatever

I just need some pointers to get going, not a full code segment.

Thanks.

@charttrader, how much about Dates have you read in the manual? There are lots of Date and Time functions. If you review those, you can learn how to manipulate Text "31-12-2029" to a DateTime format that the computer understands.

BTW - Dates are often one of the more difficult things to understand.

As you (should) already know, the best way to get help is to show your work. I am glad to see that you posted the code you have, but you need to show your effort on the task you are requesting help on.

Or, use Search to get some potential shortcuts to see what others have done with dates (and time).