Mixed Intraday and EOD database

Hello,

I’m rather new to AFL, please pardon me for my lack of knowledge. I use mixed database of 1-minute intraday and EOD data, with a Net Buy or Net Sell in the OI field. Now I am trying to get cumulative value of this NetBuySell starting from the first to the last bar of data. Here is my attempt so far :

 TimeFrameSet(in1Minute);
 CumNetBS = Cum( OI );
 TimeFrameRestore();
 CumNetBS = TimeFrameCompress(CumNetBS,in1Minute,compressVolume);
 Filter = Cum(1) == LastValue(Cum(1));
 AddColumn( cumnetBS, "CumNetBS" );

However, using all quotes the exploration gives different result in different interval of backtesting setting.

  • Can anyone please point out the correct way to fix the code so it give consistent value for any interval?
  • Does Cum() be able to distinguish between 1-min data and EOD data in one database? What is the easiest way to mark/dismiss EOD data from 1-min data within AFL?

I tried to search the forum but can’t find any similar solution. Any help is very much appreciated.

1 Like

So I guess your base time interval is 1-minute.

And you are apparently trying to call TimeFrameSet(in1Minute) when you select Periodicity to higher interval than 1-minute ( such as hourly or EOD)

If the above is true, then you are doing things the other way round

As written
http://www.amibroker.com/guide/h_timeframe.html

Please note that you can only compress data from shorter interval to longer interval. So when working with 1-minute data you can compress to 2, 3, 4, 5, 6, …N-minute data. But when working with 15 minute data you can not get 1-minute data bars. In a similar way if you have only EOD data you can not access intraday time frames.

Thanks Tomasz for your help. You are correct, my base time interval is 1-minute and when I select periodicity other than 1-minute, it gives different result. After I read the link you gave again again until A-ha moment, now I get it. Plus I get to know about daily time compression and aux1,2 aggregation mode means now, I think. :slight_smile:

Now if you or anyone would be very kind to help me on the following questions next :

  • Is there away to set OI aggregation mode to Sum as well? Say, I want to try to get the cumulative value of this NetBuySell in the OI field of mine, then plot it on hourly chart. The result is not desirable since OI field is not in Sum aggregation mode. It is not a problem when I plot it on 1-min or daily chart. I have SetBarsRequired(sbrAll); in my AFL already, fyi.
  • Is there away to refer to an EOD bar in a mixed database within AFL? For example, when I tried the following code :
TotalVol=cum(V);
Filter = Cum(1) == LastValue(Cum(1));
AddColumn( TotalVol, "Total Volume" );

using current symbol, all quotations, exploration give same result for any periodicity same or lower than daily, but when I set the periodicity to weekly or longer, it give twice the value as result. I guess this is because it add both intraday and EOD value when the periodicity is longer than daily data. How to overcome this?

Can anyone please help me on the problem above?

  • How to get an Sum of OI field data in lower interval (eg. 1-min) and plot it to higher interval (e.g hourly)? Does it require matrix processing and/or staticvars using batch processing?
  • I notice there is last column in Quote Editor for EOD reference. Is there an easier way to refer this EOD bar within AFL instead of datetime manipulation?

Thank you in advance for your kind help.

I always advise to do the other way round:
use 1-minute interval and plot hourly chart using time frame functions

http://www.amibroker.com/guide/h_timeframe.html

That is easier method for newbies. Going in opposite direction is like trying to reverse the river - possible but requires lots of resources, knowledge and work.

Besides OI field does not use “sum” semantics. It only makes sense to sum “volume”. OI field is like “close” represents current open interest count.

Summing up finer interval data would require doing the sum in this finer interval and storing sums into static variables and then reading from static variables in coarser interval.

Go with the flow instead of trying to reverse the river.

1 Like

Dear Tomasz,

Thanks again for your quick response and suggestion. I purchased AB about one year ago and learnt about AFL since then (albeit in very slow pace). While I feel myself rather lack of extensive AFL programming knowledge and am new compared to the experts in this forum, I’m not a complete newbie either. Previously I used EOD database for my learning curve, and only playing around with daily bar, not putting so much attention on timeframe or intraday. Just recently I got myself an access to mixed 1-min intraday+EOD stocks database, with the benefit of having NetBuy/Sell information (unfortunately in OI field instead of aux1 or aux2 field).

Summing up finer interval data would require doing the sum in this finer interval and storing sums into static variables and then reading from static variables in coarser interval.

Thanks for the direction. I’ll step up my game and look into it. Even If I eventually fail, at least I still can learn something from it.

I forgot to mention I do use 1-min periodicity for trading system development, TJ. It’s just sometime I wish to grasp some ideas by looking to different interval chart. Particularly for this “NetBuy/Sell” in OI field of my database, plotting it on chart other than its base time interval will give misleading information, as the aggregation for it should be “Sum”, similar with “Volume” as you mentioned earlier. It is just how I need to deal with my database.

Thanks again for your direction, for your dedication and hard work on keep improving AB, and this great forum, TJ. Amibroker’s support is second to none!

@Sallre,

One way:

you might use below sample to create a clone of your symbol being stored
via ATC (AddToComposite).
The clone will have OI stored to Aux1 field in addition.
So in File-Database settings-Intraday settings you could set Aux1
aggregation mode to Sum.

Apply the below 1st code in Scan or Indicator pane (updated via
ParamTrigger) and interval being set to base time interval.

/// code example for issue been discussed here
/// @link http://forum.amibroker.com/t/mixed-intraday-and-eod-database/1140/7

indexname = "~Clone_Of_" + Name();

SetOption( "RefreshWhenCompleted", True );

// execute sample code in BASE time interval
// either apply via Scan in analysis (check analysis periodicity in Backtest settings-General)
// or apply in indicator pane and press CTRL+R then click button "CLICK HERE"
if ( Status( "action" ) == actionScan || ParamTrigger( "CREATE CLONE", "CLICK HERE" ) ) {
    Buy = 0;

    atcmode = atcFlagDefaults | atcFlagEnableInIndicator;

    AddToComposite( Open, indexname, "O", atcmode );
    AddToComposite( High, indexname, "H", atcmode );
    AddToComposite( Low, indexname, "L", atcmode );
    AddToComposite( Close, indexname, "C", atcmode );
    AddToComposite( Volume, indexname, "V", atcmode );
    AddToComposite( OI, indexname, "I", atcmode );

    // store OI to Aux1 and set Aux1 to Sum in Database settings-Intraday settings-Aggregation mode
    AddToComposite( OI, indexname, "1", atcmode );
}

Then for plotting of sum of OI in any interval you might use following
example.

// for plot of sum of OI (stored via ATC to AUX1 filed) in any other
interval + base time interval
indexname = "~Clone_Of_" + Name();
foreignOI = Foreign( indexname, "1" );
Plot( foreignOI, "SUM of OI", colorRed, styleHistogram, Null, Null, 0, 0, -60 );
4 Likes

AddToComposite/Foreign is much slower than Static variables.

1 Like

Dear @fxshrat, oh it works so well. I’m amazed how you think out everything in details. It is so simple yet so flexible that I don’t have to worry to sum the “OI” to any other time intervals, eg. each static variables for 5mins interval, 10mins interval, hourly interval, etc, and call the appropriate static variables in different time interval chart. Thank you so much!

I don’t mind it is much slower than Static variables, as I use it only for chart glancing at the moment, thanks for the reminder, TJ. I will seek into Static variables if I ever require BT/Optimization/WF involving processing of finer interval into coarser interval as per your suggestion before.

@Sallre,
I think you are on the wrong track as far as your way of using StaticVar is concerned because IMO it is not required to set static var in every Interval as you have described.

I think you just need to call StaticVarAdd in Base Time Interval. Same way as via ATC.

https://www.amibroker.com/guide/afl/staticvaradd.html
Example code below (Untested! I have typed it in this editor only)

// Apply via Scan or
// apply in indicator pane and press CTRL+R then click button "CLICK HERE"
if ( Status( "action" ) == actionScan || ParamTrigger( "Create Sum of OI", "CLICK HERE" ) ) {
    SetbarsRequired(-2,-2);
    
    Buy = 0;

    // store OI Sum to static var in BASE TIME INTERVAL
    StaticVarAdd( "SumOfOI" + Name(), Cum( Nz(OI) )  );
}

It may be the case that Nz in upper code is not required because of automatic conversion of Null to 0. I have only added it because I have not tested it.

Then for plotting you may use

OIsum = StaticVarGet("SumOfOI" + Name());
Plot(OIsum, "Sum of OI", colorRed, styleHistogram, Null, Null, 0, 0, -60 );

Report back if it works or not.

Hello @fxshrat,

While I’m also interested in cumulative of the NetBuy/Sell since the beginning of the data (I plot using cum(foreignOI) from your previous code), but what I’m really interested is NetBuy/sell of each bar in higher interval. In other word, I’m more interested in Sum(OI,1periodicity), where periodicity is either 5-min, 10-min, hourly, etc. showed as histogram. Since I frequently change from one chart interval to many others, eg. 5-min, 10-min, hourly, etc, if I’d to use Static Var method, then as well I have to prepare/anticipate it for that many intervals.

But it is not necessary if I use ATC as per your suggestion. By using the readily available aux1/aux2 aggregation mode in database setting I don’t have to bother of such many different interval. :slight_smile:

I understand what TJ meant though. If I have concern about performance, eg. during BT/Optimization/WF, then I better use Static Var for a sum of specific pre-determined interval. It won’t require as many as if I’d use it for charting.

Thanks for the heads up, @fxshrat!

1 Like

@Sallre,

yes, I figured that I have misunderstood.

Here are two StaticVar versions for per bar sum using TimeFrame functions.

Side note, I could never really get TimeFrameMode(…) to work for base time interval being tick and doing calculations for lower granularity such as 20R (and other range bars intervals) or 200T (and other tick intervals) etc… So for those ones the upper ATC version could be better to sum up something per bar, IMO. Maybe Tomasz has a working Static Var version example for non-timebased intervals.

Anyway, here are two sum-up-per-bar versions for time based intervals using StaticVars…

First one, non-loop version:

// Apply via Scan 
if ( Status( "action" ) == actionScan  ) {
 
	Buy = 0;
	array = OI;
    
	tmfrm = 60*60;// choose Interval here
		
	expandmode = expandPoint;
	TimeFrameSet( tmfrm );
		bi = BarIndex();
	TimeFrameRestore();
	bi = Nz(TimeFrameExpand( bi, tmfrm, expandmode ));

	newbar = Ref( bi > 0, -1 );
	sumOI = SumSince( newbar, array ) + ValueWhen( newbar, array );

	// store OI Sum to static var in BASE TIME INTERVAL
	StaticVarSet( "SumOfOI_" + Name(), sumOI );
		
	// store Interval
	StaticVarSet( "SumOfOI_" + Name() + "_Interval", tmfrm );
}

For plotting you may use

OIsum = StaticVarGet( "SumOfOI_" + Name() ); 
staticint = Nz(StaticVarGet( "SumOfOI_" + Name() + "_Interval" ));

if( Interval() > staticint )
	Error( "Set to an interval being of higher or equal granularity than " + staticint/60 + "-Min" );
else
	Plot(OIsum, "Sum of OI - " + staticint/ 60 + "-Min", colorRed, styleHistogram, Null, Null, 0, 0, -60 );

//StaticVarRemove( "SumOfOI_" + Name() + "*" );

2nd one, loop version (slower):

// Apply via Scan 
if ( Status( "action" ) == actionScan  ) {
 
    Buy = 0;
    array = OI;
    expandmode = expandPoint;

    for( i = 5; i <= 60; i += 5 ) {
		tmfrm = 60 * i;		
		
		TimeFrameSet( tmfrm );
			bi = BarIndex();
		TimeFrameRestore();
		bi = Nz(TimeFrameExpand( bi, tmfrm, expandmode ));

		newbar = Ref( bi > 0, -1 );	
		sumOI = SumSince( newbar, array ) + ValueWhen( newbar, array );

		// store OI Sum to static var in BASE TIME INTERVAL
		StaticVarSet( "SumOfOI_" + Name() + tmfrm, sumOI );
		
		// store Interval
		StaticVarSet( "SumOfOI_" + Name() + "_Interval_" + tmfrm, tmfrm );
	}
}

For plotting you may use

OIsum = StaticVarGet( "SumOfOI_" + Name() + Interval() );
staticint = Nz(StaticVarGet( "SumOfOI_" + Name() + "_Interval_" + Interval() ));

if( Interval() > staticint )
	Error( "Set to an interval being of higher or equal granularity than " + staticint/60 + "-Min" );
else
	Plot(OIsum, "Sum of OI - " + staticint/ 60 + "-Min", colorRed, styleHistogram, Null, Null, 0, 0, -60 );

//StaticVarRemove( "SumOfOI_" + Name() + "*" );
4 Likes

Hi again @fxshrat,

Thank you very much for showing me how to use Static Var method. It works flawlessly. What a brilliant way to use TimeFrameSet and TimeFrameExpand to mark new bar in higher interval. It makes it so much simpler than I originally imagine. Hats off to you!

Now, I’m trying to understand how it works. Am I right to assume, when we save a Static Var in a base time interval, then call+plot it on higher interval, the Static Var will still have the same Barcount of original time interval, the PLOT function plot it in accordance to current interval using “Last”/“Point” value of each bar?

1 Like

Please ignore my previous question, @fxshrat. I found the answer in http://www.amibroker.com/guide/afl/staticvarget.html.

OIsum = StaticVarGet( “SumOfOI_” + Name() );
a=OIsum[BarCount-1];
b=OIsum[BarCount-2];
printf(NumToStr(a)+"\n");
printf(NumToStr(b));

I observe the last value of OIsum is equal to the second last value of OIsum when plotting in higher interval, although I check the value of sumOI = SumSince( newbar, array ) + ValueWhen( newbar, array ); is correct till the last value (in base time interval) . I try to understand why it is so only at the last bar? It is not happening when using ATC/Foreign method. It is the only difference between ATC and Static Var result.

@Tomasz, @fxshrat, can you please help me to understand this?